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 whenpolling PostgreSQL to capture data changes, you can track the max value of a primary key (id) to know when anINSERT operation occurred. Additionally, if your database has anupdateAt column, you can look at timestamp changes to captureUPDATE operations, but it’s much harder to captureDELETE operations.

PostgresTriggers andFunctions are powerful features of Postgres that allow you to listen forDELETE operations that occur within a table and insert the deleted row in a separate archive table. You can consider this a method of performingsoft 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 calledUser, and the trigger will insert the deleted row into a table calledDeleted_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 namedmoveDeleted():

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
	BEGIN
		INSERT INTO "Deleted_User" VALUES((OLD).*);
		RETURN OLD;
	END;
$$ LANGUAGE plpgsql;

Here we are usingVALUES((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 namedmoveDeletedthat calls themoveDeleted() function:

CREATE TRIGGER moveDeleted
BEFORE DELETE ON "User"
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();

That’s it.

If you perform aDELETE operation on theUser 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 captureDELETE operations within your application.