Storing logical WAL in the same database

Question

I have a simple Postgres setup (one machine, one disk) and would like to record a changelog for some of the tables. As I would like to access that changelog from the same web app that uses the database, it would be ideal to store the row-by-row changes in Postgres as well.

It is critical that the logging doesn't slow the machine down too much during times of high load. I would like to avoid setting up triggers (unless you believe they are the best option here).

I know about Debezium and Kafka, but setting all of that up seems like a dreadful amount of complexity for what I'm trying to achieve. What is the easiest way to accomplish what I'm trying to do? I'm open to other options too.

Edit for clarification: I don't need the log entry to be written alongside the changes in one atomic transaction – I'd rather it's queued up to happen at some unspecified time later. If some timestamps are wrong or (worst case) a log entry is missing, it's not the end of the world in my situation; write performance is more important.

Answer 1

setup a trigger on UPDATE DELETE and INSERT that writes the info you want to a logging table.

Answer 2

If you don't want to setup logging the best option is a trigger for recording changes to tables into an audit log table. It will record the old and new records, the table affected, the user who made the change, and a timestamp for each change follow the below steps.

1. Create a different schema or use your existing one (i.e public) if you don't want to create it e.g

   CREATE SCHEMA AuditTable;
   

2. Now create a table on which records/changelog of your table will be recorded

   CREATE TABLE AuditTable.tbl_LoggedTransactions 
   (
       SchemaName CHARACTER VARYING
       ,TableName CHARACTER VARYING
       ,UserName CHARACTER VARYING    
       ,DMLAction CHARACTER VARYING
       ,OriginalData TEXT
       ,ExecutedNewData TEXT
       ,ExecutedSQL TEXT
       ,RecordDateTime TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
   );
   

3. Revoke all the access on audit table

   REVOKE ALL ON AuditTable.tbl_LoggedTransactions FROM public;  
   

4. Now create trigger function to record DML statement execute on tables

   CREATE OR REPLACE FUNCTION AuditTable.trg_AuditDML() 
   RETURNS TRIGGER 
   AS $BODY$
   DECLARE
   OldData TEXT;
   NewData TEXT;
   BEGIN 
   
   IF (TG_OP = 'UPDATE') THEN
       OldData := ROW(OLD.*);
       NewData := ROW(NEW.*);
       INSERT INTO AuditTable.tbl_LoggedTransactions 
   (
       SchemaName
       ,TableName
       ,UserName
       ,DMLAction
       ,OriginalData
           ,ExecutedNewData
       ,ExecutedSQL
   ) 
       VALUES 
   (
       TG_TABLE_SCHEMA::TEXT
       ,TG_TABLE_NAME::TEXT
       ,session_user::TEXT
       ,substring(TG_OP,1,1)
       ,OldData
       ,NewData
       ,current_query()
   );
       RETURN NEW;
   ELSIF (TG_OP = 'DELETE') THEN
       OldData := ROW(OLD.*);
       INSERT INTO AuditTable.tbl_LoggedTransactions 
   (
       SchemaName
       ,TableName
       ,UserName
       ,DMLAction
       ,OriginalData
       ,ExecutedSQL
   )
   VALUES 
   (
       TG_TABLE_SCHEMA::TEXT
       ,TG_TABLE_NAME::TEXT
       ,session_user::TEXT
       ,substring(TG_OP,1,1)
       ,OldData
       ,current_query()
   );
       RETURN OLD;
   ELSIF (TG_OP = 'INSERT') THEN
       NewData := ROW(NEW.*);
       INSERT INTO AuditTable.tbl_LoggedTransactions 
   (
       SchemaName
       ,TableName
       ,UserName
       ,DMLAction
       ,ExecutedNewData
       ,ExecutedSQL
   )
       VALUES 
   (
       TG_TABLE_SCHEMA::TEXT
       ,TG_TABLE_NAME::TEXT
       ,session_user::TEXT
       ,substring(TG_OP,1,1)
       ,NewData
       ,current_query()
   );
       RETURN NEW;
   ELSE
       RAISE WARNING '[AuditTable.trg_AuditDML] - Other action occurred: %, at %',TG_OP,now();
       RETURN NULL;
   END IF;
   END;$BODY$ LANGUAGE plpgsql;
   

5. After trigger function is created apply to your table i.e assuming you have employees tbl

   CREATE TRIGGER trg_tbl_Employees_INSERT_UPDATE_DELETE
   AFTER INSERT OR UPDATE OR DELETE ON public.tbl_Employees
   FOR EACH ROW EXECUTE PROCEDURE AuditTable.trg_AuditDML();
   

6. Now just do some changes on ur tables and see the changelog on audit table by

   SELECT * FROM AuditTable.tbl_LoggedTransactions;
   

Rather setting up trigger the best option is to go for PostgreSQL logging if you have ample space left on the server :)