Creating a Soft Delete Archive Table with PostgreSQL

Streaming from Postgres’ Logical replication log is the most efficient means of capturing changes with the least amount of overhead to your database. However, in some environments (i.e., unsupported versions, Heroku Postgres), you’re left with polling the database to monitor changes.

Typically when polling PostgreSQL to capture data changes, you can track the max value of a primary key (id) to know when an INSERT operation occurred. Additionally, if your database has an updateAt column, you can look at timestamp changes to capture UPDATE operations, but it’s much harder to capture DELETE operations.

Postgres Triggers and Functions are powerful features of Postgres that allow you to listen for DELETE operations that occur within a table and insert the deleted row in a separate archive table. You can consider this a method of performing soft deletes, and this model is helpful to maintain the records for historical or analytical purposes or data recovery purposes.

In the commands below, we capture deletes from a table called User, and the trigger will insert the deleted row into a table called Deleted_User.

Step One: Create a new table

If you don’t have a table yet, you’ll need to create one. To help, you can easily copy an origin table:

CREATE TABLE “Deleted_User” AS TABLE “User” WITH NO DATA;

Note : WITH NO DATA allows you to copy a table’s structure without data.

Step Two: Create a new Postgres Function

Next, we can create a new function named moveDeleted():

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$

  BEGIN

    INSERT INTO "Deleted_User" VALUES((OLD).*);

    RETURN OLD;

  END;

$$ LANGUAGE plpgsql;

Here we are using VALUES((OLD).*) to send every column to the archive table, but you may update this to omit or even add new columns.

Step Three: Create a new Postgres Trigger

Lastly, we can create a Postgres Trigger named moveDeleted that calls the moveDeleted() function:

CREATE TRIGGER moveDeleted

BEFORE DELETE ON "User"

FOR EACH ROW

EXECUTE PROCEDURE moveDeleted();

That’s it.

If you perform a DELETE operation on the User table, a new row with the deleted data will move to theDeleted_User table.

Now your archive table will begin to populate, data won’t be lost, and you can now monitor the archive table to capture DELETE operations within your application.