راقب اتجاهات التقنية العالمية، وجمع موارد المطورين

Hello community!

I have developed PGHist — an open-source utility designed for saving, auditing, and viewing table change history in PostgreSQL.

🎯 Target audience: PGHist is specifically tailored for medium-sized projects and low-load tables within larger systems.
Fully handles change auditing (log + temporal tables) but focuses primarily on displaying modifications to users.

Setting up a trigger-based audit involves creating a schema from one SQL file.

Unlike other trigger-based tools that dump massive JSONB deltas (like Logidze or supa_audit), it stores history in a structured, normalized format (row → query → transaction), making it much more convenient to analyze and query data.
Also, unlike Debezium, it requires no binary components — it installs as a pure SQL schema, so all history is saved within your standard database dumps and backups.

GitHub: https://github.com/PGSuite/PGHist/
Website: https://pghist.org/

Working principle

When history tracking is enabled for a target table, the tool automatically sets up statement-level triggers (for INSERT, UPDATE, DELETE, TRUNCATE) along with a set of stored procedures and views for data retrieval.

Data capture: When a table is modified, triggers fire and efficiently copy only the modified fields to the history schema.
DDL autocorrection: Special event triggers detect DDL operations (like ALTER TABLE), automatically rebuilding the history tables and recreating the corresponding stored procedures.

Simple example

-- Create table create table example( id int primary key, name varchar(20), number numeric(10,2), date date ); -- Enable keeping history call pghist.hist_enable('example'); -- Change data insert into example values (1, 'Example', 10, current_date); update example set number=20, date=date-1; -- View change log by row select * from example_hist; -- View changes by field select * from example_changes() order by 1,2,3; -- View table at timestamp  select * from example_at_timestamp(now()-interval '10 second');

Key features

Storage optimization: saves only old values of changed fields and primary key. The Transaction-Expression-Row storage structure matches the operation of the DBMS and minimizes redundancy
Statement-level triggers: DML triggers are implemented with the for each statement activation level and use transition tables for batch inserting of changes. This completely removes row-by-row processing and minimizes DBMS overhead during bulk operations
Versatility: possible to get change log, list of only changed data from several tables and table at a point in time
Descriptions: for each table and its columns, it is possible to define SQL expressions to describe changed rows and field values. By default, descriptions are created for foreign key fields and table rows
Inheritance: stored procedures have parameter "cascade" that allows you to get data with or without inheritance
Transaction and SQL statements: end-to-end hierarchical tracking (Change→Query→Transaction) that delivers a complete, structured picture of all modifications. You can quickly retrieve every single edit made within a specific transaction or fetch all rows updated by a particular SQL statement
Condition (optional): when getting a list of changes, can specify a condition on the primary key or foreign key referencing the master table
Autocorrection: when performing DDL operations on a table (such as alter table, create index, etc.), an event trigger fires to automatically adjust history tracking. When a table is dropped, its history is automatically deleted

Main restrictions (technical trade-offs)

Update primary key values: updating the values of primary key columns is not supported (exception will be raised)
Significant table changes: when the table is significantly changed (for example, reassigning the primary key to another field), the history table may be incorrectly rebuilt, recommended to remove and create history again (pghist.hist_disable and pghist.hist_enable)
Write overhead: write latency will increase (roughly ~2x based on pgbench tasks) because it captures changes using triggers and splits them dynamically across structured tables