PGHist: History and audit of table changes in PostgreSQL

Description
Simple example
Important qualities
Restrictions
Data schema
Benchmark
Video

Description

Tool PGHist keeps history of table changes and allows to get a log(audit) of changes by row, list of changes by field indicating user, time of the change, SQL query, transaction, other technical information and table as of date-time in the past (versioning). To display information in user interface, SQL expressions are defined to describe changed table rows and fields. It is possible to override the operation name and username functions.

Design and working principle

PGHist is a schema with procedures and common tables: transactions, SQL expressions. When history is enabled (procedure pghist.hist_enable), for specified table created additional table, triggers for insert,update,delete,truncate, stored procedures and view for obtaining data. When a table is changed, triggers are fired that modify the history table. There are also event triggers that rebuild the history table and recreate the stored procedures.

Main functions and view

pghist.hist_enable([schema],[table])	—	enable history keeping
[schema].[table]_hist	—	log(audit) of changes by row, optimized for analysis
[schema].[table]_changes	—	list of changes by field, optimized for display to the user
[schema].[table]_at_timestamp	—	table at date-time in the past (versioning)

More details on page Documentation

-- 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');

-- drop table example cascade;

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
Indexes
- for a history table, an index is built on the primary key column(s), for a table at a point in time - standard indexes on columns
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
Extended names
- expanded (in double quotes) table and column names are supported

Large projects
- use on large projects is not recommended due to versatility and redundancy. Large projects involve SQL developers and DBA who implement saving only necessary data.
Update primary key values
- updating the values of primary key columns is not supported (exception will be raised)
Recreate view
- when a column is dropped or changed, the view [schema].[table]_hist is dropped and created again (PostgreSQL support only add columns to a view), recommended to create a stored function(wrapper) with a dynamic query
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)

When a single DML operation is executed, a statement-level trigger (for each statement) captures the changes and distributes them across four tables.

Distributing records across tables

Table	Number of records	Data to be written
Transactions pghist.hist_transaction	1	General information about the operation (date-time, user, IP address, etc.)
SQL statements pghist.hist_statement	1	Identifier and SQL statement information
SQL query texts pghist.hist_query	0 or 1	The source code of the SQL query without duplication
History table pghist.data$[schema]_[table]	number of rows in an SQL query	Primary key, foreign key to the master table (optional), and only changed data from rows in the trigger transition table (new table/old table)

Consequently, modifying 1 row in the target table will result in 4 additional rows being written, while modifying 1,000 rows will result in 1,003 additional rows.

Expected Test Results: A significant increase in write latency and zero impact on read operations.
It should be noted in particular that this performance testing isolates database metrics only. These results must be evaluated in relation to network latency and business logic execution time (e.g., complex automated field population).

Bash script for performance benchmarking via pgbench

#!/bin/bash
set -e
set -x

# Test configuration
DB=benchmark
OPTIONS="-j $(nproc) -c $(( $(nproc)*4 )) -t 1000" 

# Recreate DB, init pghist extension
psql -q -c "drop database if exists $DB" -c "create database $DB"
psql $DB -q -f pghist_init.sql
pgbench $DB -i

# Run baseline tests
pgbench $DB $OPTIONS
pgbench $DB $OPTIONS -b select-only

# Enable history tracking on target tables
psql $DB -q -c "call pghist.hist_enable('pgbench_accounts')"
psql $DB -q -c "call pghist.hist_enable('pgbench_branches')"
psql $DB -q -c "call pghist.hist_enable('pgbench_tellers')"

# Run post-pghist tests
pgbench $DB $OPTIONS
pgbench $DB $OPTIONS -b select-only

Benchmark output

postgres@db-debian:~$ ./benchmark.sh
+ DB=benchmark
++ nproc
++ nproc
+ OPTIONS='-j 2 -c 8 -t 1000'
+ psql -q -c 'drop database if exists benchmark' -c 'create database benchmark'
+ psql benchmark -q -f pghist_init.sql
+ pgbench benchmark -i
dropping old tables...
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 1.00 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.57 s, vacuum 0.22 s, primary keys 0.20 s).
+ pgbench benchmark -j 2 -c 8 -t 1000
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 27.442 ms
initial connection time = 34.556 ms
tps = 291.529019 (without initial connection time)
+ pgbench benchmark -j 2 -c 8 -t 1000 -b select-only
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 0.899 ms
initial connection time = 39.618 ms
tps = 8896.985145 (without initial connection time)
+ psql benchmark -q -c 'call pghist.hist_enable('\''pgbench_accounts'\'')'
+ psql benchmark -q -c 'call pghist.hist_enable('\''pgbench_branches'\'')'
+ psql benchmark -q -c 'call pghist.hist_enable('\''pgbench_tellers'\'')'
+ pgbench benchmark -j 2 -c 8 -t 1000
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 51.236 ms
initial connection time = 31.243 ms
tps = 156.139254 (without initial connection time)
+ pgbench benchmark -j 2 -c 8 -t 1000 -b select-only
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 1.025 ms
initial connection time = 32.404 ms
tps = 7801.795388 (without initial connection time)

Write speed dropped by ~2x, read speed remained unchanged, no errors occurred.

Summary:

In most databases, over 95% of queries are read-only select queries, which remain completely unaffected
Users will not notice any difference in the UI, as the ~25 ms increase in average latency (from 27.442 to 51.236 ms) is significantly less than the typical UI↔backend↔database network round-trip time (usually up to ~150–200 ms), even excluding any additional data processing business logic
Execution time for operations modifying large rows without complex subqueries or logic will double. Typically, such tasks run in the background.

Review posted on YouTube

PGHist
History and audit of table changes in PostgreSQL

Description

Design and working principle

Main functions and view

Simple example

Important qualities

Storage optimization

Statement-level triggers

Versatility

Descriptions

Inheritance

Transaction and SQL statements

Indexes

Condition (optional)

Autocorrection

Extended names

Restrictions

Large projects

Update primary key values

Recreate view

Significant table changes

Data schema

Performance benchmark

Bash script for performance benchmarking via pgbench

Benchmark output

Summary:

Video

PGHist History and audit of table changes in PostgreSQL

Description

Design and working principle

Main functions and view

Simple example

Important qualities

Storage optimization

Statement-level triggers

Versatility

Descriptions

Inheritance

Transaction and SQL statements

Indexes

Condition (optional)

Autocorrection

Extended names

Restrictions

Large projects

Update primary key values

Recreate view

Significant table changes

Data schema

Performance benchmark

Bash script for performance benchmarking via pgbench

Benchmark output

Summary:

Video

PGHist
History and audit of table changes in PostgreSQL