Database Auditing: Methods, Trade-offs, and Best Practices

Database auditing records who changed which data, when, and how, so you can reconstruct history, satisfy compliance requirements, and investigate incidents. The practical question is not whether to audit but which mechanism to use, because each one trades performance, query convenience, and maintenance burden differently. The main approaches are soft-delete flags, statement logging, the pgaudit extension, trigger-based audit tables, and system-versioned temporal tables. This guide compares them on PostgreSQL and notes where other engines differ.

How the Auditing Methods Compare

The five common approaches answer different questions. Logging and pgaudit answer "what statements ran" and feed compliance and security tooling. Trigger-based audit tables and temporal tables answer "how did this row change over time" and stay queryable with regular SQL. Soft-delete flags only answer "is this row still active" and are not a full audit trail on their own.

Soft Deletes and Statement Logging

A soft delete replaces a physical DELETE with an is_deleted = true flag (or a deleted_at timestamp), keeping the row for recovery, legal retention, or accidental-deletion protection. The cost is paid on every read: queries must filter WHERE is_deleted = false, unique constraints need a partial index like CREATE UNIQUE INDEX ... WHERE is_deleted = false, and the table grows with dead rows that the planner still scans. A soft delete is a recoverability pattern, not an audit trail - it records that a row is gone, not who removed it or what it used to contain.

Statement logging is the lowest-effort trail. In PostgreSQL, set log_statement = mod to log every data-modifying statement (INSERT, UPDATE, DELETE, and DDL), or log_statement = all to capture reads too. It needs no schema changes and is handled entirely by the engine. The weakness is readability: logs store statement text, so a dynamic command such as EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)' records the literal expression rather than the resulting important_table, and reconstructing per-row before/after values from text is impractical. Log volume also grows quickly under write-heavy load.

pgaudit for Compliance Logging

The `pgaudit` extension is the standard PostgreSQL auditing tool and produces detailed, structured audit records that map cleanly to controls like PCI DSS, HIPAA, and SOC 2. It writes through the normal logging pipeline but classifies events and emits a consistent AUDIT: log line per operation, which is far easier to parse than raw log_statement output.

pgaudit offers two modes. Session auditing logs statements by class through pgaudit.log, set to a comma-separated list such as READ, WRITE, DDL, FUNCTION, ROLE, or MISC. Object auditing logs only operations touching specific relations, controlled by granting privileges to a role named in pgaudit.role, which keeps log volume down on busy systems where you only care about sensitive tables.

-- Install and configure pgaudit (in postgresql.conf, then reload):
--   shared_preload_libraries = 'pgaudit'
CREATE EXTENSION pgaudit;

-- Session auditing: log all writes and schema changes
SET pgaudit.log = 'write, ddl';

-- Object auditing: audit only what an 'auditor' role can see
SET pgaudit.role = 'auditor';
GRANT SELECT, INSERT, UPDATE, DELETE ON accounts TO auditor;

The trade-off is that pgaudit produces log records, not database rows. It is excellent for proving who did what, but you cannot join its output to your data or run SELECT against it without first shipping the logs to a separate store. Managed services expose it differently: Amazon RDS and Aurora support pgaudit as a parameter-group option, and Cloud SQL and Azure expose it as a flag.

Trigger-Based Audit Tables

When you need a change history you can query with plain SQL, an AFTER trigger that writes old and new values into an audit table is the most direct option in PostgreSQL. The trigger fires per affected row, captures OLD and NEW, and inserts a record you can later filter, join, and aggregate like any other table.

-- Audit table holding before/after values for the accounts table
CREATE TABLE accounts_audit (
  audit_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id  int NOT NULL,
  operation   text NOT NULL,
  old_value   text,
  new_value   text,
  changed_by  text NOT NULL DEFAULT current_user,
  changed_at  timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE FUNCTION audit_accounts() RETURNS trigger
  LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO accounts_audit (account_id, operation, old_value, new_value)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,                          -- 'INSERT', 'UPDATE', or 'DELETE'
    CASE WHEN TG_OP <> 'INSERT' THEN OLD.value END,
    CASE WHEN TG_OP <> 'DELETE' THEN NEW.value END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Fire on every row-level change to accounts
CREATE TRIGGER accounts_audit_trg
  AFTER INSERT OR UPDATE OR DELETE ON accounts
  FOR EACH ROW EXECUTE FUNCTION audit_accounts();

Triggers are flexible: you choose the shape of the audit table, can store full rows as jsonb for schema-agnostic auditing, and can attach a WHEN clause to skip no-op updates. The costs are real. A row-level trigger runs for every modified row, so bulk updates pay per-row overhead, and the audit table competes for write throughput and disk. Triggers are also easy to forget - they run invisibly, and when several are defined on one table they fire in alphabetical name order, which surprises people during migrations. Avoid query rewrite RULEs for this: they are hard to reason about with volatile functions and the PostgreSQL project has long discouraged them.

Temporal Tables and Other Databases

System-versioned temporal tables, defined in the SQL:2011 standard, keep every historical version of a row with valid-from and valid-to timestamps and let you query the table as of any past instant. PostgreSQL does not have built-in system-versioned temporal tables. You approximate them with an extension such as `temporal_tables` or with the trigger pattern above writing into a history table.

SQL Server, MariaDB (10.3+), and IBM Db2 implement system-versioning natively. In SQL Server you declare a PERIOD FOR SYSTEM_TIME and a history table:

-- SQL Server (not PostgreSQL): native system-versioned table
CREATE TABLE dbo.accounts (
    id int NOT NULL PRIMARY KEY CLUSTERED,
    value nvarchar(100) NOT NULL,
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
    ValidTo   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.accounts_history));

Native temporal tables need one-time configuration and almost no ongoing maintenance, and the history is just another queryable table. Their limits: you generally cannot fabricate historical rows, you cannot run arbitrary logic on each change the way triggers allow, and once enabled the table is audited unconditionally.

For most PostgreSQL teams the recommendation is pragmatic. Use pgaudit when the goal is compliance and security logging - it is the standard, low-friction, structured choice. Use trigger-based audit tables when you need a change history you can query, diff, and join inside the database. If you are on SQL Server, MariaDB, or Db2 and need point-in-time reconstruction, native temporal tables are the lowest-maintenance path.

Whichever method you pick, the audit machinery itself becomes part of your write path and can degrade silently. Pulse continuously watches write latency, trigger overhead, and table growth, so when an audit trigger starts dominating an UPDATE or an audit table outgrows its tablespace, it surfaces the regression and points to the cause instead of leaving you to find it after the slowdown.

Frequently Asked Questions

Q: What is the difference between database auditing and a soft delete?
A: Database auditing records the full history of changes - who modified which data, when, and the before and after values. A soft delete only marks a row as inactive with a flag like is_deleted, preserving it for recovery. A soft delete tells you a row is gone but not who removed it or what it contained, so it is not a substitute for an audit trail.

Q: Should I use pgaudit or trigger-based audit tables in PostgreSQL?
A: Use pgaudit for compliance and security logging, where you need a structured, tamper-evident record of statements and access. Use trigger-based audit tables when you need a change history you can query with SQL - diffing values, joining to other tables, or building point-in-time views. Many teams run both: pgaudit for the security log and triggers for queryable history on sensitive tables.

Q: Does PostgreSQL have system-versioned temporal tables?
A: No. PostgreSQL has no built-in SQL:2011 system-versioned temporal tables. You implement equivalent behavior with the temporal_tables extension or with an AFTER trigger that writes row versions into a history table. SQL Server, MariaDB 10.3 and later, and IBM Db2 do support system-versioning natively.

Q: How much do audit triggers slow down writes?
A: A row-level AFTER trigger runs once per modified row, so the overhead scales with rows changed, not statements. For single-row OLTP writes the cost is usually small, but bulk UPDATE or DELETE statements can slow noticeably because each row pays the trigger and the extra audit-table insert. Use a WHEN clause to skip no-op updates and monitor write latency after deploying triggers.

Q: How do I enable statement logging for auditing in PostgreSQL?
A: Set log_statement = mod to log all data-modifying statements and DDL, or log_statement = all to include reads. It requires no schema changes, but the output is statement text rather than per-row values, and log volume grows quickly under heavy writes. For structured, parseable audit records, use the pgaudit extension instead.

Q: Can I audit only specific tables instead of the whole database?
A: Yes. With pgaudit, object auditing logs only operations on relations accessible to the role named in pgaudit.role, so you scope auditing to sensitive tables. With triggers, you simply attach a trigger to the tables you care about. Both keep audit volume far lower than database-wide statement logging.

Q: Where should audit logs be stored for security?
A: Store audit logs separately from the audited data so an attacker who compromises the database cannot also erase the trail. Ship pgaudit output to a centralized log store or SIEM, and for audit tables consider write-only permissions for application roles plus shipping copies off-host. Keeping audit records in the same database under the same privileges weakens their integrity.

Related Reading

• PostgreSQL Security Best Practices: Roles, privileges, and hardening that underpin a trustworthy audit trail.
• PostgreSQL Slow Query Log: Configure logging to capture and analyze problematic statements.
• Database Monitoring Best Practices: Track the write latency and table growth that auditing adds.
• PostgreSQL Row-Level Security: Restrict which rows each role can see, complementing audit logging.
• UPDATE in PostgreSQL: How updates work, including the OLD and NEW values triggers capture.
• Creating Tables in PostgreSQL: Schema design for audit and history tables.