PostgreSQL SME Cookbook

/

/

Sql Triggers Rules Event Logic List Audit Triggers

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.

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+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?