Soft delete with Ecto 3 and Postgres

One thing that folks sometimes want or need to do is to be able to “soft delete” records in a database. This means that instead of actually removing the record from a database, you in some way keep it there but make it “invisible” to your application.

There are many different ways of doing this, but I’ve recently settled on the way I think I’m going to do it from now on when I have applications on Ecto 3 and using Postgres. I’ve put together a little example application to show how it works, and that’s what I’m going to walk through today.

The basic plan

The simplist way of describing how this works is that on every table we add a deleted_at column. If that column is null then the record has not been deleted. If that column has a timestamp in it, then that record has been soft deleted. We’re going to configure our application so that by default it only “sees” records that have not been soft deleted, but if we want we can see all records, including those that have been soft deleted. We can also “hard delete” records if we want to, which will fully remove that record from the database.

Two “schema”s

There are two things that are called “schema”s that we’ll be talking about here, so before we move on let’s describe them and give them better names.

First off, Postgres has a concept of a schema, which relates to essentially a namespace for a group of relations. By default, all relations are created in the "public" schema, but you can create other schemas with the CREATE SCHEMA schema_name command. So, you can do CREATE SCHEMA filtered to have a new filtered schema available where you can create relations. After you create those relations, you can query from them like SELECT * from public.users or SELECT * from filtered.users.

From now on, when we’re talking about this schema, we’ll always call it a Postgres schema.

The second schema is the one that we use in Ecto. You’ve probably seen the code use Ecto.Schema before - that’s what we’re talking about. A module that usees Ecto.Schema we’re going to call an Ecto schema.

The tests

Ok, so let’s look at some specifics!

Here are the tests that show how this works. You’ll see at this level it looks just like any other normal test - nothing strange going on here. We just have some well named functions in a module called SoftDelete.Users that do just what they say they do - creating, retrieving, updating and deleting users. But there are some extra functions there that are specific to soft delete, like list_deleted_users, get_deleted_user, undelete_user and hard_delete_user.

The queries

Now, let’s look at that Users module to see what’s going on there.

Most of these functions look just the same as any other normal application without soft delete functionality, which is the goal here. The first thing you might notice that’s different as we scan down that file is the implementation for delete_user. In that, if the deletion is successful, instead of returning the record that was deleted, we actually have to look up another record and return that instead. A little deviation there, but not too bad.

Then we get to those special functions that deal with the soft deleted users. In order to access our soft deleted users, we use the prefix option in Ecto.Query.from/2. This is where the real magic happens! This prefix corresponds to the Postgres schema to make our query in. So, when we look at the implementation for list_deleted_users, we see that we’re telling Ecto to return all users in the public.users relation.

So if soft deleted users are in the "public" Postgres schema, where are the other users?

The Ecto schema

The Ecto schema definition for our User looks just the same as in any other application. We’re defining the fields on our object, and we have two changeset functions - nothing interesting to see here.

However, instead of use Ecto.Schema, we see use SoftDelete.Schema, so let’s check in there to see what’s going on.

And in that file, on line 11, we see the important annotation! We’re setting that Ecto schema’s @schema_prefix to be "filtered". This tells Ecto that, unless we tell it otherwise, all operations for this Ecto schema are to be performed against the filtered.users relation instead of public.users, which is the default.

So, now we know that in filtered.users we have all of the users that haven’t been soft deleted, but in public.users we have all users, including those that have been soft deleted. So, where does filtered.users come from?

The migration

In the migration that creates our users table, we first see what looks like a totally normal create table(:users) call. And indeed it is a totally normal call! But, after that, we get something a little special. We’re executing some custom SQL, so on the up part of the migration we’re calling PERFORM prepare_table_for_soft_delete('users');, and on the down part of the migration we’re calling PERFORM reverse_table_soft_delete('users');. That must be doing something important, so let’s see what’s going on there.

The really important part

So the first migration in this app is where all the really important and interesting stuff happens.

So much SQL, though! Ughhh, how will I ever understand this?!

Let’s start from the beginning and take it step by step.

On line 9 we use CREATE SCHEMA filtered, like I mentioned above. This creates a Postgres schema called filtered for us. Easy enough, right?

The next chunk of SQL on lines 11-21 do the most important thing in this app - actually soft deleting our records instead of deleting them. Here we’re creating a Postgres trigger, which is just a function that’s executed after a certain event happens. This particular trigger says basically “After a record has been deleted, re-insert it back into the table it came from and set its deleted_at field to be the current timestamp”. There are Reasons™ why that has to be an AFTER DELETE trigger instead of a BEFORE DELETE trigger, but they’re not too important, really, so I’ll spare you from having to read all of them.

Then, the next chunk of SQL on lines 23-34 create that prepare_table_for_soft_delete function that we saw in the previous migration. This function accepts a single argument, which is supposed to be the name of a table that you’ve created, and then does four things:

1) Add a deleted_at field to the given table 2) Create an index on that deleted_at field, because it’s going to be used a lot for queries 3) Set the trigger that we created on lines 11-21 to be active on that table 4) Create a Postgres view in the filtered Postgres schema with the same name as the given table, but that only returns records that haven’t been soft deleted.

Once that function has been called, a newly created table has everything it needs to be used in our application and have soft deleted records!

Finally, the chunk of SQL on lines 36-47 gives us the reverse_table_soft_delete function that just undoes the four things listed above.

The Repo

By now we’ve covered just about everything in the app, but the one thing that I didn’t show is how we actually handle hard deletes for when we really want a record to be removed from the database for good. That’s defined in a new function that we added to the Repo called hard_delete.

The great thing about this method of handling soft delete is it minimally affects how Ecto works, and you can see that by looking at that Repo. It’s totally normal with the exception of that one additional function, and that function itself is fairly normal as well.

What’s going on in hard_delete/1 is we have an Ecto.Multi operation, and the first thing we do is to disable the Postgres trigger that we set to do soft deletes for us. Once that’s disabled, we actually delete the record as we normally would, and once that’s done we re-enable the trigger again so we go back to defaulting all delete operations to be soft deletes. And yes, this disabling of the trigger is local to the transaction that the Multi is run in, so you don’t need to worry about messing with a form of global state there and accidentally deleting records you meant to soft delete because of a race condition!


So, that’s it! Not too scary, right? It’s a great soft delete implementation that you barely see any evidence of in your actual application, works 100% with all normal Ecto behavior, and doesn’t require much in the way of hacky SQL to make work.

Yes, there are performance tradeoffs here, but that’s just the nature of the beast with soft deletes. If performance becomes an issue for you there are many ways you can refine this to make it faster, but this shouldn’t add more than a millisecond or two to most operations.