Audit Triggers
Audit triggers capture who changed what. Design for write throughput: skinny audit rows, async export, or statement-level transition tables for bulk.
How to Use This Checklist
- Run checklist before enabling audit on hot tables.
- Measure INSERT p95 with trigger on in staging.
- Prefer column-level WHEN to reduce noise.
Schema
- Audit table is append-only. No UPDATE/DELETE on audit rows.
- Store actor, action, pk, changed_at. Optional old/new jsonb diff.
- Index audit by table_name, pk, changed_at. Queryable investigations.
Trigger Design
- BEFORE/AFTER choice documented. AFTER for immutable audit insert.
- Statement trigger for bulk loads. Transition tables avoid per-row cost.
- WHEN clause skips unchanged updates. IS DISTINCT FROM on tracked columns.
Operations
- Partition or archive audit by month. Prevent unbounded growth.
- Monitor audit table bloat. Autovacuum tuning.
- Compare to pgaudit for compliance. Triggers for row detail; pgaudit for session capture.
FAQs
Triggers vs logical replication for audit?
Replication captures changes downstream; triggers capture in-DB with business context.
Related
- Transition Tables - bulk capture
- Audit Triggers vs pgaudit - compliance choice
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.