Logging & Audit Trails Explained
PostgreSQL logging and auditing look like one topic from the outside, but they are actually three distinct mechanisms that answer three distinct investigative questions.
//
Search across all documentation pages
PostgreSQL logging and auditing look like one topic from the outside, but they are actually three distinct mechanisms that answer three distinct investigative questions.
This page builds the mental model that separates those mechanisms, server logs, statement-level auditing via pgaudit, and row-level history via audit triggers, so you can choose the right tool instead of reaching for whichever one is already configured.
The server log is PostgreSQL's general-purpose flight recorder, capturing connections, disconnections, errors, checkpoints, and optionally slow queries.
It answers operational questions like "did this connection succeed" or "which query ran past the latency threshold," and it exists on every cluster by default in some minimal form.
pgaudit is a purpose-built extension that layers structured, statement-level access logging on top of the server log, answering "who ran what kind of SQL against which objects."
Audit triggers are a different mechanism entirely: application-level triggers that write before-and-after row values into dedicated audit tables, answering "what did this specific row look like before and after it changed."
The distinction that matters most is granularity: server logs and pgaudit describe statements and connections, while audit triggers describe row-level data changes with old and new values captured.
A useful analogy is a retail store: the server log is the door's entry counter, pgaudit is the security camera recording who walked down which aisle, and audit triggers are the itemized receipt showing exactly what changed hands.
None of the three replaces another, because a receipt tells you nothing about who was browsing without buying, and a door counter tells you nothing about what was in anyone's basket.
PostgreSQL 18.4 defaults to minimal logging, so almost every one of these capabilities requires deliberate configuration before it produces useful evidence.
These mechanisms sit at different points in the request lifecycle, which explains both their strengths and their blind spots.
The server log is populated as a side effect of connection and query execution, controlled by settings like log_connections, log_statement, and log_min_duration_statement.
pgaudit hooks into the same execution path but classifies activity into logical categories (read, write, ddl, role), letting teams scope logging to exactly the classes a compliance program requires rather than an all-or-nothing switch.
Audit triggers execute inside the same transaction as the data change itself, firing on INSERT, UPDATE, or DELETE and writing a row into an audit table before the original transaction commits.
That transactional coupling is the key mechanical difference: an audit trigger's record either commits with the change it describes or rolls back with it, while a log line written to the server log is not transactional in that same sense.
-- One mechanism made concrete: a minimal audit trigger captures
-- old/new row state transactionally, tied to the change it describes.
CREATE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
INSERT INTO audit.orders_history(order_id, old_row, new_row, changed_at)
VALUES (COALESCE(NEW.id, OLD.id), to_jsonb(OLD), to_jsonb(NEW), now());
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;Volume is the other mechanical concern that connects all three: logging every statement on a busy OLTP cluster can fill disk within hours, so both server logs and pgaudit are designed to be scoped narrowly (slow queries only, DDL only, specific roles) rather than run at maximum verbosity by default.
A frequent reasoning mistake is treating log volume as purely an infrastructure problem, when it is really an evidence-design problem: logging everything produces so much noise that finding the one relevant line during an incident becomes its own investigation.
At scale, logging and auditing become a retention and pipeline problem as much as a capture problem.
Centralizing logs into a SIEM or log-shipping pipeline decouples retention from local disk, which matters because compliance programs often require 90 days to multiple years of retained evidence that a database host was never designed to store locally.
Structured formats like csvlog or PostgreSQL's JSON log output exist specifically to make that pipeline reliable, since parsing unstructured stderr output at scale is fragile compared to parsing well-defined fields.
Regulated data introduces a specific tension: audit evidence must be captured, but the same evidence (query text, row values) can itself contain the sensitive data being protected, so redaction and access control on the audit trail matter as much as on the source tables.
Audit triggers and pgaudit are frequently combined rather than chosen exclusively, with pgaudit providing the broad access trail required by frameworks like PCI DSS and triggers providing row-level history for the specific tables where "what was the old value" is a real regulatory question.
Tamper resistance is a design detail worth deliberate attention: audit tables should typically revoke UPDATE and DELETE from application roles so that only the trigger itself can append rows, preserving the trail's evidentiary value.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
Server log (log_min_duration_statement, log_statement) | Zero extra infrastructure, always available | Coarse, not queryable as structured data | Operational troubleshooting, slow query capture |
| pgaudit | Structured, classified, role-scoped access trail | No old/new row values, log pipeline required | Statement-level compliance evidence (SOC2, PCI) |
| Audit triggers | Row-level before/after values, queryable in SQL | Write overhead, per-table implementation effort | Regulator-mandated row history, in-app change UI |
Server logs record operational events, pgaudit adds a structured statement-level access trail, and audit triggers capture row-level before/after values transactionally.
It is unstructured and event-oriented, lacking the classified, queryable access trail that frameworks like SOC2 or PCI typically require.
No, pgaudit logs statement text and access classification, not row data; capturing old/new values requires an audit trigger writing to a history table.
The trigger's audit row is written inside the same transaction as the data change, so it commits or rolls back together with it, unlike a server log line.
It can fill disk within hours on a busy workload and buries the genuinely useful log lines in noise during an incident.
Often yes, with pgaudit providing broad access evidence across many tables and triggers providing row-level history on the specific tables where old values matter.
It makes log shipping into a SIEM or pipeline reliable, since parsing well-defined fields is far more robust than parsing unstructured stderr text.
Query text and captured row values can contain the same sensitive data the audit trail exists to protect, so the trail needs its own access controls.
Revoking UPDATE and DELETE from application roles so only the trigger function can append new rows preserves the trail's evidentiary value.
Usually yes, because compliance retention windows often exceed what a database host was designed to store locally, and centralization decouples retention from local disk.
They typically provide log delivery infrastructure (CloudWatch, Cloud Logging), but the logging configuration itself, and any audit trigger design, remains the customer's responsibility.
Discovering during an incident or compliance audit that the specific evidence needed, whether access trail or row history, was never being captured.
Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17).
Reviewed by Chris St. John·Last updated Jul 15, 2026