PGHist
History and audit of table changes in PostgreSQL

Description

Design and working principle

Main functions and view

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

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)

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.

Architectural Comparison

Feature Comparison

Summary

• Highly specialized alternatives excel at only one specific task: pgAudit writes reliable security logs, temporal_tables delivers snapshots quickly, and JSONB triggers offer easy out-of-the-box portability
• PGHist perfectly generates a ready-to-use change log for user display, performs all other tasks at a solid average level, and covers all audit and versioning needs at once: it links data to transactions and SQL statements, saves storage space, handles bulk workloads efficiently, and automatically supports DDL
• History protection: in pgAudit, logs cannot be modified from within the database; in PGHist, historical data can only be altered by a superuser; temporal_tables requires precise permission tuning; with JSONB triggers, regular users can tamper with historical data