PGHist | History and audit of table changes in PostgreSQL

PGHist is an open-source tool for tracking and viewing change history in PostgreSQL tables. It is designed for medium-sized projects and low-load tables, focusing on displaying modifications rather than logging them. The tool sets up statement-level triggers for INSERT, UPDATE, DELETE, and TRUNCATE operations, as well as stored procedures and views for data retrieval.

When a table is modified, triggers efficiently copy only the changed fields to the history schema. Special event triggers handle DDL operations like ALTER TABLE, automatically rebuilding the history tables and stored procedures. The storage structure is Transaction-Expression-Row, which minimizes redundancy and matches the database management system's operation.

To enable history tracking, users call pghist.hist_enable() on the target table. They can then view change logs by row, changes by field, or tables at a specific timestamp. The tool also allows defining SQL expressions for each table and column to describe changed rows and field values. By default, descriptions are created for foreign key fields and table rows.

PGHist supports inheritance, with stored procedures having a 'cascade' parameter to retrieve data with or without inheritance. It tracks modifications in a hierarchical manner (Change → Query → Transaction), allowing retrieval of all edits within a specific transaction or by a particular SQL statement. Optional conditions can be applied to the primary or foreign key when listing changes.

The tool has some limitations, such as not supporting updates to primary key values and potential issues when the table undergoes significant changes, requiring the history to be rebuilt. There is also a write overhead, as the tool captures changes using triggers and splits them dynamically across structured tables, resulting in roughly twice the write latency compared to standard operations.