Postgres Auditing in 150 lines of SQL

Summary

Traditional PostgreSQL auditing methods duplicate source table structures, creating maintenance overhead when schemas change. To solve this, a compact SQL implementation stores insert, update, and delete events across multiple tables in a single audit table using JSONB columns. Query performance is maintained by using a BRIN index on naturally ordered insertion timestamps, a B-tree index on internal table OIDs, and UUIDv5 identifiers hashed from primary keys. Row-level PL/pgSQL triggers automatically populate the audit log and can be enabled or disabled dynamically with dedicated tracking functions. While trigger-based auditing introduces minimal overhead at rates below 1,000 writes per second, write-heavy workloads may benefit from logging changes outside SQL with tools like pgAudit.

Context

Traditional PostgreSQL auditing systems mirror source table schemas and require database migrations whenever source tables change. A practical auditing solution must track historical changes over time, remain low maintenance across schema alterations, and allow fast querying for both time slices and specific record lifecycles.

Approach / What changed

The implementation creates an audit.record_version table using JSONB to capture row states across multiple tables in one place. It creates a BRIN index on the timestamp column for efficient time-range filtering and indexes table OIDs and UUIDv5 record identifiers derived from primary keys. Row-level PL/pgSQL triggers capture data mutations, managed through idempotent enable_tracking and disable_tracking helper functions.

Takeaways

• Using JSONB allows a single audit table to store change history across multiple entities without requiring schema migrations when source tables change.
• Applying a BRIN index on naturally ascending timestamp columns produces an index hundreds of times smaller than a standard B-tree index while speeding up time-range lookups.
• Trigger-based SQL auditing typically adds negligible overhead for write throughput below 1,000 writes per second, whereas higher volumes may require external tools like pgAudit.

Related reading