What should an audit log record, and who actually needs one?
Before picking a mechanism, write down what each audit row must answer. A useful spec covers four questions:
- Who: the authenticated user, any impersonator (a support agent acting as a customer), and the service or job that issued the write. Without the impersonator field, support actions look like customer actions.
- What: table, primary key of the row, operation (insert, update, delete), and the columns that actually changed. Storing only the diff keeps rows small and makes them easier to read.
- When: in PostgreSQL,
now()returns the transaction start time, whileclock_timestamp()returns the current wall-clock time. Decide which one you record. Transaction time groups every change from one unit of work under a single timestamp. - Why: a request or correlation ID that links the change to the HTTP request, job run or migration that caused it.
Next, decide which of two different needs you are serving:
| Concern | Compliance-grade audit | Debugging change history |
|---|---|---|
| Durability | Must commit with the data change | Best-effort is acceptable |
| Retention | Set by policy, often years | Weeks or months |
| Access | Restricted, append-only | Engineers and support |
| Tamper resistance | Required | Nice to have |
Monoliths with dozens of tables get the most value from this work. So do regulated domains, and support or ops teams who regularly have to answer "who changed this order?" A written spec also stops each team from adding its own logger.info calls with different fields. Otherwise you end up with several incompatible trails that nobody can join or trust.
Where should you capture changes: application, trigger, or WAL?
There are three places to capture a change, and each one gives up something different.
| Capture point | Sees every write? | Knows the actor? | Cost to the write path |
|---|---|---|---|
| Application (ORM hooks, service layer) | No: misses raw SQL, migrations, bulk UPDATEs |
Yes, full request context | Low to moderate |
| Database trigger | Yes, in the same transaction | Only if the app passes it in | Runs inline on every row |
| WAL / logical decoding / outbox | Yes (outbox: only what the app emits) | Only if it is written into the data or the stream | None inline, but asynchronous |
An application audit trail knows the user, tenant and request ID. It still misses anything that bypasses the code path, such as a hotfix run in psql, a backfill, or an ORM bulk method that skips hooks. Those are the changes auditors ask about most.
Trigger-based audit catches every write because it runs inside the transaction. That is also the coupling. If the audit insert fails or blocks, the business write fails too. You get consistency (no change without a record) and give up some availability.
WAL-based capture removes that coupling, at a cost. It needs replication slots, a consumer to run, and lag to monitor. The actor is not in the WAL unless you put it in the row or emit it explicitly. Events can also arrive late, or go missing if a slot is dropped.
The rest of this article uses triggers for completeness and has the application set the actor in a transaction-local setting. Anything expensive, such as diffing, enrichment or shipping logs to other systems, happens asynchronously outside the write path.
How does trigger-based audit with transaction-local actor context work?
The flow has three steps, and only the first two run inside the write path.
- Attach context. After
BEGIN, the application callsset_config('app.actor_id', ..., true)orSET LOCALfor the actor, any impersonator, and the request ID. Custom setting names need a dotted prefix such asapp.. - Capture the row. The audited
INSERT,UPDATEorDELETEfires anAFTER ... FOR EACH ROWtrigger. It reads each value withcurrent_setting(name, true)and appends one row to the audit table. Because that row commits or rolls back with the business change, the trigger never records a write that didn't happen. - Process asynchronously. Separate consumers read the appended rows later to compute diffs, resolve actor IDs to names, and index for search.
Transaction-local scope matters because of connection pooling. In transaction-pooling mode, each transaction can run on a different server connection. A session-level SET stays on that server connection after the client releases it, so the next client's writes get attributed to the wrong actor without any error. A setting made with is_local = true is discarded at commit or rollback, so it can't outlive the transaction that set it.
Some writes arrive with no context: cron jobs, ad hoc psql sessions, and migrations. Note that after a transaction-local setting has been used once in a session, reading it later can return an empty string rather than NULL, so wrap the read in nullif(..., ''). You then have two options:
- Raise an exception in the trigger. This guarantees attribution but blocks emergency fixes.
- Record NULL or a sentinel such as
system:unknown. Then alert on those rows.
A common split is to fail writes on high-value tables and flag missing context everywhere else.
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name text NOT NULL,
op text NOT NULL,
actor_id text NOT NULL,
impersonator_id text,
request_id text,
old_row jsonb,
new_row jsonb,
logged_at timestamptz NOT NULL DEFAULT now()
);
CREATE FUNCTION audit_capture() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
-- nullif: a previously used transaction-local setting reads back as '' rather than NULL
v_actor text := nullif(current_setting('app.actor_id', true), '');
BEGIN
-- TG_ARGV[0] = 'strict' marks high-value tables: missing context fails the write
IF v_actor IS NULL AND TG_ARGV[0] = 'strict' THEN
RAISE EXCEPTION 'audit: app.actor_id not set for write to %', TG_TABLE_NAME;
END IF;
-- Same transaction as the business change: a rollback discards this row too
INSERT INTO audit_log (table_name, op, actor_id, impersonator_id, request_id, old_row, new_row)
VALUES (TG_TABLE_NAME, TG_OP, coalesce(v_actor, 'system:unknown'),
nullif(current_setting('app.impersonator_id', true), ''),
nullif(current_setting('app.request_id', true), ''),
CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
RETURN NULL; -- return value is ignored for AFTER row triggers
END $$;
CREATE TRIGGER invoices_audit AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW EXECUTE FUNCTION audit_capture('strict');
-- Application side: is_local = true discards the settings at COMMIT/ROLLBACK, so they cannot leak to the next pooled client
BEGIN;
SELECT set_config('app.actor_id', 'user:4821', true),
set_config('app.request_id', 'req-7f3a9c2e', true);
UPDATE invoices SET status = 'void' WHERE id = 1042;
COMMIT;
An AFTER row trigger reads transaction-local actor and request settings with current_setting(name, true). It writes an audit row in the same transaction, rejects writes that lack an actor on tables marked 'strict', and records 'system:unknown' on other tables.
How do you design the audit log table in PostgreSQL?
A single generic table can serve every audited table. Each row holds table_name, row_key as jsonb (so composite keys fit), op (INSERT, UPDATE or DELETE), old_values and new_values as jsonb, the actor columns from the previous section, the transaction ID, and two timestamps. Use now() for transaction start, which groups a transaction's changes together, and clock_timestamp() for the actual write time.
| Concern | Generic jsonb table | Per-table shadow tables |
|---|---|---|
| Flexibility | One trigger function for every table | One table and trigger per source table |
| Typing | Values are jsonb, so you cast when reading | Native column types |
| Indexability | Key and time indexes only | Any column can be indexed |
| Column rename | Old rows keep the old key name | Shadow DDL must change in step |
In a monolith with dozens or hundreds of tables, schema drift is the real cost. Shadow tables double every migration. With the jsonb table, a rename leaves history readable as it was written.
Full-row storage makes reconstruction cheap, because you only read one row, but it multiplies storage for wide rows. Diff-only storage stays small, but rebuilding a past state means replaying every change in order. A practical middle ground is to store the full row on INSERT and DELETE, and only the changed keys on UPDATE.
Partition by changed_at range, for example monthly. Expired data can then be detached or dropped, which avoids the bloat and vacuum load of a large DELETE. Keep indexes minimal, because every index adds write cost:
- A B-tree on
(table_name, row_key, changed_at). - Possibly an index on the actor column.
- BRIN on
changed_at. Insert order tracks time closely, so a BRIN index stays tiny and is cheap to maintain.
Decide on column exclusion and redaction for PII and secrets before the first row is written. Retrofitting it later means rewriting history you meant to keep immutable.
-- One append-only audit table for every audited source table.
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY,
table_name text NOT NULL,
row_key jsonb NOT NULL, -- jsonb so composite keys fit
op text NOT NULL CHECK (op IN ('INSERT', 'UPDATE', 'DELETE')),
old_values jsonb, -- full row on DELETE, changed keys on UPDATE
new_values jsonb, -- full row on INSERT, changed keys on UPDATE
actor_id text, -- who made the change
actor_source text, -- e.g. 'web', 'job', 'console'
txid bigint NOT NULL DEFAULT txid_current(),
tx_started timestamptz NOT NULL DEFAULT now(), -- same for the whole transaction
changed_at timestamptz NOT NULL DEFAULT clock_timestamp(), -- actual write time
PRIMARY KEY (id, changed_at) -- must include the partition key
) PARTITION BY RANGE (changed_at);
-- Monthly partitions. Create upcoming months ahead of time.
CREATE TABLE audit_log_2026_11 PARTITION OF audit_log
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE audit_log_2026_12 PARTITION OF audit_log
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
-- Keep indexes minimal. Each one adds cost to every insert.
CREATE INDEX audit_log_row_idx ON audit_log (table_name, row_key, changed_at);
CREATE INDEX audit_log_actor_idx ON audit_log (actor_id); -- only if you query by actor
CREATE INDEX audit_log_time_brin ON audit_log USING brin (changed_at);
-- Retention: detach the expired month, then drop it. This avoids a large DELETE
-- and the bloat and vacuum work that come with it.
-- ALTER TABLE audit_log DETACH PARTITION audit_log_2025_11;
-- DROP TABLE audit_log_2025_11;
DDL for a generic jsonb audit table partitioned by month on changed_at, with a key-plus-time B-tree, an optional actor index, a BRIN index on the timestamp, and retention done by detaching and dropping partitions.
How do you keep the audit trigger from slowing writes?
Use an AFTER ... FOR EACH ROW trigger, not BEFORE. An AFTER trigger fires only once the row change has succeeded, so failed writes never produce an audit insert, and the trigger cannot accidentally modify the row. Add WHEN (OLD.* IS DISTINCT FROM NEW.*) to the trigger definition. The condition is evaluated before the function is called, so ORM-generated no-op updates cost almost nothing. Inside the function, convert OLD and NEW with to_jsonb and keep only the keys whose values differ. The alternative is to store NEW whole and diff at read time, which keeps the trigger simpler but writes more bytes. In either case the function should do no lookups: read the actor from the transaction-local setting and insert one row.
For bulk UPDATE or DELETE, a statement-level trigger with REFERENCING OLD TABLE ... NEW TABLE performs one set-based INSERT ... SELECT instead of thousands of function calls. TRUNCATE fires no row-level triggers, so add a statement-level AFTER TRUNCATE trigger that records the event, or revoke TRUNCATE from application roles.
Illustrative estimate. Assume a 300-byte row, an update that changes 2 columns, a roughly 150-byte audit row, and a single BRIN index on the audit table. Each update then adds one heap insert of about 150 bytes plus a BRIN update that is usually trivial. This is extra WAL of the same order as the original update. A B-tree on the audit table would add a second index insert. Full-page writes after a checkpoint amplify both.
To measure this yourself, run pgbench custom scripts or your own load tests with the trigger enabled and then disabled. Compare p99 latency, WAL generated (pg_current_wal_lsn() deltas), and replica lag during sustained update-heavy runs.
-- Append-only audit table: no PK, one cheap BRIN index on time.
CREATE TABLE audit_log (
changed_at timestamptz NOT NULL DEFAULT now(),
table_name text NOT NULL,
op text NOT NULL,
actor text,
row_pk bigint,
diff jsonb
);
CREATE INDEX audit_log_changed_at_brin ON audit_log USING brin (changed_at);
-- Store only changed keys. Do no lookups: actor comes from SET LOCAL app.actor.
CREATE FUNCTION audit_row_update() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
o jsonb := to_jsonb(OLD);
n jsonb := to_jsonb(NEW);
BEGIN
INSERT INTO audit_log (table_name, op, actor, row_pk, diff)
SELECT TG_TABLE_NAME, TG_OP, current_setting('app.actor', true),
(n->>'id')::bigint, jsonb_object_agg(k, n->k)
FROM jsonb_object_keys(n) AS k
WHERE n->k IS DISTINCT FROM o->k;
RETURN NULL; -- return value is ignored for AFTER triggers
END $$;
-- WHEN is checked before the function runs, so no-op updates skip the function call.
CREATE TRIGGER orders_audit_upd AFTER UPDATE ON orders
FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION audit_row_update();
-- TRUNCATE fires no row triggers, so record it once per statement.
CREATE FUNCTION audit_truncate() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO audit_log (table_name, op, actor)
VALUES (TG_TABLE_NAME, TG_OP, current_setting('app.actor', true));
RETURN NULL;
END $$;
CREATE TRIGGER orders_audit_truncate AFTER TRUNCATE ON orders
FOR EACH STATEMENT EXECUTE FUNCTION audit_truncate();
An AFTER row trigger that skips no-op updates with a WHEN clause, writes only the changed columns as jsonb to an append-only table that has a single BRIN index, and uses a separate statement-level trigger to record TRUNCATE.
How do you protect audit history from tampering?
Revoke UPDATE, DELETE, and TRUNCATE on the audit table from every application role. Ideally, revoke INSERT as well. Make the trigger function SECURITY DEFINER and have it owned by a dedicated, non-login role that is the only role allowed to insert. That way, a compromised application connection can still cause audited changes, but it cannot write fake audit rows or erase real ones. Pin the function's search_path to pg_catalog plus the audit schema. Without that, a caller could create a same-named table or function earlier in its path and hijack the elevated function.
These controls only constrain application roles. A superuser or the table owner can still disable the trigger, rewrite rows, or drop the table. Database permissions keep application bugs and compromised application credentials away from history. They do not protect it from your DBAs.
When the threat model includes privileged insiders, or an auditor asks you to prove history is complete, add hash chaining or ship rows to external append-only storage. Hash chaining only detects tampering, and computing it inline forces inserts to serialize. Compute it in a batch job instead. External export means operating another pipeline, so take on that cost only when a requirement demands it.
Storing old and new values conflicts with erasure requests. Keep personal data out of the JSON payloads where you can, and record subject IDs instead. Where you can't, encrypt those fields with a per-subject key that you destroy when an erasure request arrives.
For reads, grant SELECT on views that expose only the columns each consumer needs. Use row-level security to scope rows by tenant.
-- Dedicated non-login role: the only role allowed to write audit rows
CREATE ROLE audit_writer NOLOGIN;
GRANT USAGE ON SCHEMA audit TO audit_writer;
-- Application roles get no direct write path to history
REVOKE ALL ON audit.log FROM PUBLIC, app_rw;
GRANT INSERT ON audit.log TO audit_writer;
CREATE OR REPLACE FUNCTION audit.log_change() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
-- Pinned path; pg_temp listed last so temp objects cannot shadow ours
SET search_path = pg_catalog, audit, pg_temp
AS $$
BEGIN
-- session_user is the connecting app role; current_user would be audit_writer here
INSERT INTO audit.log (table_name, op, actor, old_row, new_row)
VALUES (TG_TABLE_NAME, TG_OP, session_user, to_jsonb(OLD), to_jsonb(NEW));
RETURN NULL; -- AFTER trigger: return value is ignored
END;
$$;
ALTER FUNCTION audit.log_change() OWNER TO audit_writer;
REVOKE EXECUTE ON FUNCTION audit.log_change() FROM PUBLIC;
CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON public.orders
FOR EACH ROW EXECUTE FUNCTION audit.log_change();
-- Least-privilege reads: expose only the columns this consumer needs, no payloads
CREATE VIEW audit.log_for_support AS
SELECT id, table_name, op, actor, changed_at
FROM audit.log;
GRANT USAGE ON SCHEMA audit TO support_ro;
GRANT SELECT ON audit.log_for_support TO support_ro;
Locks the audit table so only a non-login SECURITY DEFINER trigger owner with a pinned search_path can insert. Application roles get no direct access, and support staff read through a column-limited view that omits the old and new row payloads.
How do you query change history, roll out auditing, and when should you avoid it?
Querying. To reconstruct a row as of time T, select the most recent audit entry for that primary key with changed_at <= T and read its new_data. If that entry is a DELETE, the row did not exist at T. To answer "who changed this field", filter on old_data->'status' IS DISTINCT FROM new_data->'status' for the key and order by time. Each result carries the actor and transaction ID, so you can also find the other rows changed in the same request. When investigations scan months of history or compliance exports run for a long time, replicate the audit table to a separate store. Those queries should not compete with OLTP traffic for buffer cache and I/O.
Rolling out. Add the trigger one table at a time, starting with tables where disputes actually happen. Because jsonb stores whatever columns exist at write time, added and dropped columns need no audit migration. Renames are different: old entries keep the old key. Record the rename date, or map both names in a view. For history that predates the trigger, either write one synthetic snapshot entry per row with a null actor, or document a clear "audit starts here" timestamp. Do not imply coverage you never had.
Cost. You pay for the initial build, for ongoing discipline (every code path, job, and migration must set actor context), and for storage that grows steadily. In return, incident investigations and compliance requests that once meant reading logs and guessing become a single query.
When to avoid or limit triggers:
- Extremely hot write tables, where every extra insert shows up in commit latency.
- Bulk ETL tables, where the data can be reloaded and per-row history adds little value.
- Tables where WAL-based async capture is sufficient, or where nobody will ever ask who changed the data.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to audit log table design postgresql, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.