Soft Deletion with Triggers in Postgres

I’m working on a new version of the Pyrra app, using Postgres as our primary database. I wanted to get soft-deletion in from day one, because it’s one of those things that you don’t wish you had until you need it and don’t have it - poking around in database snapshots isn’t my idea of a fun way to recover from accidental deletions.

I had seen a post that used Postgres rules to perform soft deletes but when I asked about it online I was pointed where the Postgres wiki says don’t use rules. I was a little surprised at the wiki, as I thought using rules here was a nice approach. But, given the officialness of the wiki, I decided to rule out (😏) that option.

I also decided not to use tombstones, because they break cascades and they’re contagious: if you aren’t going to let the database clean up after you (because when you soft-delete you don’t get to use ON DELETE CASCADE) then you end up having to make every query filter out soft-deleted items. Also, whenever you’re migrating your database you have to carry all your soft-deleted stuff forward even though it’s filtered out by all your queries. Note: if you’re going to let users undelete stuff then you might need tombstones - that’s not the case here, where soft-deletion is just a safety net.

I went with a single deleted_items table to hold all soft-deletions, and a trigger to make sure that before a row is deleted from any other table it gets serialized to JSON and copied into the soft-deletion table.

The table is simple, having just four columns

| column    | type      |
|-----------|-----------|
| id        | uuid      |
| createdAt | timestamp |
| tableName | text      |
| data      | jsonb     |

Now all we need is a trigger that can insert a row into the table before it gets deleted

CREATE FUNCTION soft_delete_row() RETURNS TRIGGER AS $result$
    BEGIN
        INSERT INTO "deleted_items" ("tableName", "rowData") SELECT TG_TABLE_NAME, row_to_json(OLD);
        return old;
    END;
$result$ LANGUAGE plpgsql;

And then it needs to be applied to every table we want to have soft-deletion on

CREATE TRIGGER soft_delete_[tableName] BEFORE DELETE ON "[tableName]"
    FOR EACH ROW EXECUTE PROCEDURE soft_delete_row();

Needing a trigger on every table is a potential gotcha, because the database isn’t going to complain if you forgot one - it’ll just go ahead and delete rows without making a copy first. So I created a unit test that checks every table for a soft_delete_[tablename] trigger, and another that creates and deletes a row for each table, verfiying that the deleted row shows up in deleted_items.

If someone deletes data that they need back it’ll be a manual process, potentially requiring that the deleted data is reconciled with subsequent changes to the schema. But this is envisioned as a way to help customers out when they accidentally delete something, so for any request it’s most likely that the data in question will be recent, and not too many schema changes will need to be navigated. If we build the UI well then hopefully we can eliminate accidental deletions, especially painful ones - like how Github makes you type the repo name before you can delete it.

Craig Glennie

Soft Deletion with Triggers in Postgres