pgMemento uses triggers to track the changes. The OLD and the NEW version of a tuple are accessable inside the corresponding trigger procedures. By default, pgMemento only takes the OLD version as the recent state can be queried from the table. It goes even further in a way that only values from changed columns are logged, e.g. a delta between OLD and NEW in case of a UPDATE event. This saves up disk space and makes rollbacks easier. Since, v0.7 it is also possible to enable logging of deltas with newly introduced values, if somebody is primarily interested to inspect the changes with only one query against the log tables.

Logging only fragments can produce sparsely filled history/audit tables. Using a semistructured data type like JSONB can make the data logs more compact. In general, using JSONB for auditing has another big advantage: The audit mechanism (triggers and audit tables) does not need to adapt to schema changes. You do not need history tables for each audited table (sometimes also called 'shadow tables'). All logs can be written to one central table with a JSONB field.

alt text

To trace different versions of a tuple in the log table a surrogate key is created in each audited table called pgmemento_audit_id by default. It is easier than relying on a table's primary key which can be defined on multiple columns and for different data types. Audit_ids are unique in a (single node) database.