News · Issue 17
The audit extension that froze Postgres: how a routine table migration triggered a system-catalog lock chain
A routine table migration hung in production because audit triggers fired per-row inside the same transaction, turning a seconds-long operation into a synchronous write storm.
Production incident
A migration that should have taken seconds
Pydantic's observability platform Logfire tracked every database change using PostgreSQL triggers for audit logging. Each INSERT, UPDATE, or DELETE on a tracked table fired a PL/pgSQL trigger that computed a JSONB diff and wrote a record to an audit_logs table, all within the same transaction as the data change.
A routine table migration that should have completed in seconds ran for an unexpectedly long time in production. The trigger processed every changed row, each one producing a JSONB diff and an audit insert inside the same transaction. The migration effectively became an audit-log bulk-write operation, and the transaction held its locks the entire time.
The trigger architecture
Three triggers, eighty lines, one session variable
Each tracked table carried three statement-level triggers, one for INSERT, UPDATE, and DELETE, using REFERENCING NEW/OLD TABLE to access changed rows. The trigger function computed a JSONB diff between old and new values, filtered noise like updated_at-only changes, redacted sensitive fields such as tokens, and inserted the result into audit_logs.
Tracked table
- INSERT trigger
- UPDATE trigger
- DELETE trigger
Context about who made each change traveled through a PostgreSQL session variable, a #-delimited string carrying organization ID, user ID, project ID, IP address, and more. The trigger function parsed it in PL/pgSQL and grew to over 80 lines of procedural SQL with string parsing, JSONB manipulation, and conditional logic.
pgAudit the extension vs. custom audit triggers
The pgAudit extension hooks into PostgreSQL's standard logging facility to record session and object-level audit events. It must be loaded in shared_preload_libraries and requires a server restart when the pgaudit.log parameter changes. The Pydantic/Logfire system did not use pgAudit. It used custom PL/pgSQL triggers writing to an application-specific audit_logs table, which gave more control over what was logged but introduced synchronous per-row overhead that pgAudit's log-pipe approach avoids.
The mechanism
Why seconds became an eternity
A bulk UPDATE changes thousands of rows. The statement-level trigger fires once and iterates over every changed row, computing a JSONB diff and inserting an audit record for each one, all synchronously inside the same transaction. The transaction holds its locks the entire time, so the migration cannot commit until every audit record is written.
The migration effectively became an audit-log bulk-write operation.
Forgetting to disable triggers around bulk operations produced one of two outcomes. The migration could hang or time out from thousands of synchronous audit writes, or it could complete but generate thousands of system-generated audit entries polluting the audit trail. The immediate fix was wrapping bulk operations in DISABLE TRIGGER and ENABLE TRIGGER, but that put the burden on every engineer to remember every time.
The fix
Out of the database, into the application
The team moved audit logging into the application layer over several weeks, one table at a time, across 15 pull requests. When a service performs an auditable action, it pushes the audit record onto a Redis list with RPUSH and returns immediately. No database write, no trigger overhead.
A worker cron job runs every 30 seconds, pops up to 1,000 records, and batch-inserts them into PostgreSQL in a single transaction. On failure, records go back onto the queue for retry. The tradeoff is an eventual consistency window of up to 30 seconds, acceptable for compliance and debugging, traded for near-zero request latency and no more trigger dance around migrations.
Takeaway
The cost of auditing at the wrong layer
Why it matters
Database-level audit triggers capture every SQL operation automatically, but that automatic coverage becomes a liability during bulk operations. The Pydantic/Logfire migration reflects a broader pattern: teams that start with trigger-based audit logging eventually hit the wall where the database does too much synchronous work per row. Moving audit to the application layer trades automatic capture for control over timing, at the cost of a consistency gap and the risk of missed instrumentation.
The short version
- Three statement-level audit triggers per table turned a bulk migration into thousands of synchronous JSONB diff and audit insert operations inside one transaction.
- The immediate fix was `DISABLE TRIGGER` / `ENABLE TRIGGER` wrappers, but that required every engineer to remember for every bulk operation.
- The team moved audit logging to the application layer with a Redis producer-consumer queue, migrating 15 PRs one table at a time.
- The new architecture batch-inserts up to 1,000 audit records every 30 seconds, trading a 30-second eventual consistency window for near-zero request latency impact.
- The key tradeoff: automatic capture becomes manual instrumentation, so a new endpoint that forgets the audit call goes unaudited.