What is a trigger-based Postgres audit log and when should you use one?

A trigger-based audit log is a single Postgres audit log table, plus a PL/pgSQL function attached as a trigger to every table you want to track. Whenever a row is inserted, updated or deleted, the trigger writes a record of the change. That record holds the table name, the operation, the old and new values, a timestamp and the identity of whoever made the change. The trigger runs inside the same transaction as the statement that fired it.

That transactional coupling is why this approach suits a monolith on one database. The audit row commits or rolls back together with the data change. You never get a recorded change that didn't happen, and you never get a change with no record. There is no queue, no change-data-capture pipeline and no separate service to run or monitor.

This pattern fits backend and platform teams that own a single Postgres database and need change history for debugging production issues, answering support questions such as "who set this order to cancelled?", or meeting compliance requirements. Choose it when your writes go through one database and your audit volume is modest compared with your write throughput.

Capturing row changes is the easy part. The hard parts are carrying application identity into the database, because Postgres sees a pooled connection role and not your logged-in user, and keeping the table usable as it grows.

The development cost is mostly a one-time setup: one table, one function and a trigger per audited table. You don't write audit code in every write path. New endpoints, background jobs and admin scripts are covered automatically, so no one has to remember to add logging when they ship a feature.

How should you design the audit log table schema?

One audit table serves every audited table, so its columns describe the change rather than the business data.

Column Type Purpose
id bigint identity Insertion order and row identity
table_name text Schema-qualified source table
operation text INSERT, UPDATE or DELETE
row_pk jsonb Primary key of the changed row, including composite keys
old_row / new_row jsonb Row images before and after (null where not applicable)
changed_keys text[] Columns whose values differ
actor text Application user responsible
txid / request_id bigint / text Groups rows from one logical action
created_at timestamptz When the change happened

Storing row images as jsonb decouples the audit table from the schemas it records. Adding an audited table, or a column to one, needs no audit migration. Old entries keep the shape the row had when they were written, which is what history should show.

For created_at, choose deliberately. now() returns the transaction start time, so every row written by one transaction shares a timestamp. clock_timestamp() returns the actual wall-clock time at each call, which orders statements inside a long transaction. The cost is that one action appears spread over several moments.

txid_current() returns the transaction ID. An application request ID can span several transactions, such as a request followed by its background job. Either value lets you reconstruct everything one action touched.

Plan for time-based partitioning now. Postgres requires a partitioned table's primary key to include the partition key. Make the primary key (id, created_at) from the start, not id alone.

-- One audit table for every audited table. Columns describe the change, not the business data.
CREATE TABLE audit_log (
    id           bigint GENERATED ALWAYS AS IDENTITY,
    table_name   text        NOT NULL,  -- schema-qualified, e.g. 'public.orders'
    operation    text        NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    row_pk       jsonb       NOT NULL,  -- e.g. {"order_id": 42} or {"tenant_id": 7, "sku": "A-1"}
    old_row      jsonb,                 -- null for INSERT
    new_row      jsonb,                 -- null for DELETE
    changed_keys text[],                -- columns whose values differ (UPDATE only)
    actor        text,                  -- application user, not the database role
    txid         bigint      NOT NULL DEFAULT txid_current(),  -- groups rows from one transaction
    request_id   text,                  -- groups rows across transactions (request + background job)
    -- now() = transaction start time: all rows from one transaction share a timestamp.
    -- Swap in clock_timestamp() if you need per-statement ordering inside long transactions.
    created_at   timestamptz NOT NULL DEFAULT now(),
    -- Include created_at now so the table can later be converted to
    -- PARTITION BY RANGE (created_at) without changing the primary key.
    PRIMARY KEY (id, created_at)
);

CREATE INDEX audit_log_table_pk_idx ON audit_log (table_name, row_pk);
CREATE INDEX audit_log_txid_idx     ON audit_log (txid);
CREATE INDEX audit_log_request_idx  ON audit_log (request_id) WHERE request_id IS NOT NULL;

-- Reconstruct everything one logical action touched.
SELECT table_name, operation, row_pk, changed_keys, created_at
FROM audit_log
WHERE request_id = 'req-7f3a9c'
ORDER BY id;

An audit log table with jsonb row images, transaction and request grouping columns, a deliberate created_at default, and a composite (id, created_at) primary key that is ready for time-based partitioning.

How does a generic PL/pgSQL audit trigger work?

One trigger function can serve every audited table, because Postgres gives the function enough context to describe the change itself. TG_OP reports the operation: INSERT, UPDATE or DELETE. TG_TABLE_SCHEMA and TG_TABLE_NAME identify the table. Record both, since orders in two schemas are different tables. The function branches on TG_OP. An insert stores only to_jsonb(NEW), a delete stores only to_jsonb(OLD), and an update stores both. The function then inserts one row into the audit table and returns NULL, because an AFTER row trigger's return value is ignored.

Attach the function to each table as AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW. AFTER matters here. A BEFORE trigger can still be followed by other BEFORE triggers that modify the row, so only an AFTER trigger sees the values that were actually stored. FOR EACH ROW means a bulk UPDATE that touches 500 rows produces 500 audit rows, each with its own before and after state.

The audit insert runs in the same transaction as the change it records:

  1. Application request opens a transaction.
  2. Row change (UPDATE orders ...) is written.
  3. Trigger fires after the row is written and converts OLD and NEW to JSONB.
  4. Audit insert writes a row to the audit table.
  5. Single commit makes both rows durable, or a rollback discards both.

Because of this, you can't end up with a change that has no audit row, or an audit row for a change that was rolled back.

TRUNCATE does not fire row-level triggers, so it bypasses this trigger completely. You have two options. You can add a separate FOR EACH STATEMENT trigger on TRUNCATE that logs the event without row data. Or you can revoke TRUNCATE from application roles and make that an explicit policy.

-- One audit table for every audited table.
CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE audit.log (
  id          bigserial PRIMARY KEY,
  schema_name text        NOT NULL,  -- same table name can exist in two schemas
  table_name  text        NOT NULL,
  operation   text        NOT NULL,  -- INSERT, UPDATE, DELETE or TRUNCATE
  old_row     jsonb,                 -- NULL for INSERT and TRUNCATE
  new_row     jsonb,                 -- NULL for DELETE and TRUNCATE
  changed_by  text        NOT NULL DEFAULT current_user,
  changed_at  timestamptz NOT NULL DEFAULT now()
);

-- Generic function: Postgres tells it which table and which operation fired it.
CREATE OR REPLACE FUNCTION audit.log_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit.log (schema_name, table_name, operation, new_row)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit.log (schema_name, table_name, operation, old_row, new_row)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit.log (schema_name, table_name, operation, old_row)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
  ELSE  -- TRUNCATE (statement-level): record the event, no row data exists
    INSERT INTO audit.log (schema_name, table_name, operation)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP);
  END IF;
  RETURN NULL;  -- ignored for AFTER triggers
END;
$$;

-- Attach per audited table: AFTER so we see the stored values, per row for bulk changes.
CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON public.orders
  FOR EACH ROW EXECUTE FUNCTION audit.log_change();

-- TRUNCATE skips row triggers, so log it with a statement-level trigger.
CREATE TRIGGER orders_audit_truncate
  AFTER TRUNCATE ON public.orders
  FOR EACH STATEMENT EXECUTE FUNCTION audit.log_change();

A single PL/pgSQL trigger function that branches on TG_OP, stores OLD/NEW as JSONB with the schema and table name, and is attached to public.orders as an AFTER row trigger plus a statement-level TRUNCATE trigger.

How do you pass the application user into Postgres safely?

The trigger only sees what the database knows, which by default is the connected role. To record the person or service behind a change, the application has to pass that identity in explicitly. At the start of each transaction, call set_config('app.user_id', '42', true), and optionally set_config('app.request_id', ...) so audit rows can be correlated with application logs. The third argument, true, makes the setting transaction-local, which is the same behaviour as SET LOCAL. It disappears at COMMIT or ROLLBACK.

That scope matters behind a connection pooler. A session-level setting stays on the physical connection after your request finishes. The next request that borrows the connection inherits it, so if that request forgets to set its own value, the audit row names the wrong user.

In the trigger, read the value with current_setting('app.user_id', true). The second argument returns NULL instead of raising an error when the setting was never defined. After a transaction-local value has been set once on a session, later reads may return an empty string instead of NULL, so wrap the call in NULLIF(..., ''). Then COALESCE it with session_user. That fallback identifies a database role such as app_rw, not a human, but it is better than an empty actor.

Migrations, manual psql sessions and background jobs should set an explicit actor, such as 'migration:0042' or 'job:invoice-sweeper', rather than rely on the fallback.

The core limitation is that Postgres records whatever identity the application claims and cannot verify it. If the application forgets the setting, the trail becomes silently anonymous. If it sets the wrong value, the trail is confidently wrong.

-- Application: first statement of every transaction (values bound by the driver)
BEGIN;
SELECT set_config('app.user_id', '42', true),          -- true = transaction-local, like SET LOCAL
       set_config('app.request_id', 'req-7f3a9c', true); -- correlates with application logs
UPDATE invoices SET status = 'paid' WHERE id = 1001;
COMMIT;  -- both settings are discarded here, so a pooled connection carries nothing forward

-- Migrations, psql sessions and jobs name themselves instead of using the fallback
BEGIN;
SELECT set_config('app.user_id', 'migration:0042', true);
UPDATE invoices SET currency = 'EUR' WHERE currency IS NULL;
COMMIT;

-- Trigger side: read the claimed identity, never error if it is missing
CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, actor, request_id, row_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    -- missing_ok = true returns NULL if never set; NULLIF covers the '' seen
    -- after a transaction-local value was set earlier on this session.
    -- session_user is a database role (e.g. app_rw), not a person.
    COALESCE(NULLIF(current_setting('app.user_id', true), ''), session_user::text),
    NULLIF(current_setting('app.request_id', true), ''),
    CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END
  );
  RETURN NULL;  -- return value is ignored for AFTER ... FOR EACH ROW triggers
END;
$$;

-- Postgres stores whatever actor the application claims; it cannot verify it.

An application transaction that sets a transaction-local user and request id with set_config, a migration that sets an explicit actor, and a trigger function that reads both values with current_setting and falls back to session_user when the user id is missing.

Recording only meaningful changes: no-op updates, diffs and sensitive columns

An UPDATE that rewrites a row with identical values still fires a row-level trigger. ORMs that save every field on each request generate a lot of these. The cheapest filter is on the trigger itself: WHEN (OLD IS DISTINCT FROM NEW) stops Postgres from calling the function at all. IS DISTINCT FROM treats two NULLs as equal, which plain <> does not.

That clause compares whole rows, so it cannot ignore specific columns. The function should strip sensitive and noisy columns from both images first. The jsonb - operator removes keys, so applying - 'password_hash' - 'api_token' - 'updated_at' to both to_jsonb(OLD) and to_jsonb(NEW) keeps secrets out of the audit table entirely. Masking after the row is stored is too late, because backups and replicas already have the value.

Next, compare the stripped images key by key with jsonb_each and keep only the keys whose values differ. If nothing remains, the change touched only excluded columns, and the function should return without inserting.

Concern Full row images Changed keys only
Storage cost Grows with row width on every change Grows with the number of changed columns
Point-in-time reconstruction Read one record Replay diffs from an insert or snapshot
Schema change tolerance Old images keep their original columns Old diffs keep their original keys

Neither layout survives a schema change cleanly. Renamed or dropped columns leave their old keys in historical records, and new columns are missing from them. Queries and history UIs should treat any key as optional and map known legacy names, rather than assuming the current table definition.

CREATE TABLE audit_log (
  id         bigserial PRIMARY KEY,
  table_name text        NOT NULL,
  row_id     bigint      NOT NULL,
  changed    jsonb       NOT NULL,  -- {"col": {"old": ..., "new": ...}}
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_users_update() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  -- Strip secrets and noisy columns before anything is compared or stored
  old_img jsonb := to_jsonb(OLD) - 'password_hash' - 'api_token' - 'updated_at';
  new_img jsonb := to_jsonb(NEW) - 'password_hash' - 'api_token' - 'updated_at';
  diff    jsonb;
BEGIN
  -- Keep only keys whose values differ (IS DISTINCT FROM handles JSON nulls)
  SELECT jsonb_object_agg(n.key, jsonb_build_object('old', o.value, 'new', n.value))
    INTO diff
    FROM jsonb_each(new_img) AS n
    JOIN jsonb_each(old_img) AS o ON o.key = n.key
   WHERE n.value IS DISTINCT FROM o.value;

  -- jsonb_object_agg over zero rows is NULL: only excluded columns changed
  IF diff IS NOT NULL THEN
    INSERT INTO audit_log (table_name, row_id, changed)
    VALUES (TG_TABLE_NAME, NEW.id, diff);
  END IF;
  RETURN NULL;  -- return value is ignored for AFTER triggers
END;
$$;

-- Whole-row no-op updates never call the function at all
CREATE TRIGGER users_audit_update
  AFTER UPDATE ON users
  FOR EACH ROW
  WHEN (OLD IS DISTINCT FROM NEW)
  EXECUTE FUNCTION audit_users_update();

An AFTER UPDATE trigger that skips no-op updates with a WHEN clause, removes password_hash, api_token and updated_at from both row images, and stores only the old and new values of the keys that actually changed.

How do you query change history and which indexes support it?

Most audit queries answer one of three questions, and each one needs a different index.

A single row's timeline. Filter on table_name and record_pk, then order by changed_at (and the audit row's own id as a tiebreaker, because several changes can share a timestamp inside one transaction). A composite index on (table_name, record_pk, changed_at) serves this directly. Postgres walks one index range and returns rows already sorted, with no separate sort step.

What one actor did in a window. Filter on actor_id and a changed_at range. An index on (actor_id, changed_at) lets the planner seek to the actor and scan only the requested interval. Put the equality column first and the range column second. In the reverse order, the index cannot narrow the scan to one actor.

Everything from one transaction or request. Filter on the stored transaction id or request id. Add this index only if incident review or support tooling actually runs these lookups. Otherwise it is one more index that every audited write has to maintain.

A GIN index on the jsonb payload pays off only when you routinely search by values inside the data, such as "who set status to refunded". Each insert must update many GIN entries, which makes it the most expensive index on an append-heavy table. If you need one, jsonb_path_ops builds a smaller index that supports containment (@>) queries.

If you partition by changed_at, Postgres creates each index separately on every partition. Actor and time-window queries benefit from partition pruning. Timeline queries without a time bound probe every partition's index. Add a date bound in the history UI whenever you can.

How do you protect the audit trail and control its growth?

Revoke UPDATE, DELETE and TRUNCATE on the audit table from the application role. Grant it nothing beyond what the trigger path needs. Have a dedicated role, not the application role, own both the table and the trigger function. An owner can always alter, truncate or disable triggers on its own objects, so the application must never be the owner.

If the trigger function is SECURITY DEFINER, pin its search path with SET search_path = pg_catalog, pg_temp and schema-qualify every table reference. Otherwise a caller can put a schema of their own earlier in the path. Their objects then shadow the ones the function expects, and that code runs with the owner's privileges.

This setup is tamper-resistant, not tamper-proof:

  • Superusers can do anything.
  • A session that sets session_replication_role = replica skips ordinary triggers entirely.
  • Anyone who can drop or disable the trigger can make changes that leave no record.

For evidence-grade integrity, ship rows to a separate system as well.

Growth and retention

Partition the audit table by time, for example monthly. When a partition ages out, detach it, archive it, then drop it. That is a metadata operation. A bulk DELETE instead creates dead tuples, vacuum load and WAL volume. Set the retention period with whoever owns compliance rather than choosing one yourself.

Erasure requests for personal data conflict with an append-only log. You have three options:

  • Mask at write time: store hashes or omit sensitive columns, so there is nothing to erase.
  • Separate retention for sensitive tables: route their changes to shorter-lived partitions.
  • Controlled redaction: a privileged, separately logged procedure overwrites specific values.

When should you avoid trigger-based auditing and what are the alternatives?

Trigger-based auditing is a poor fit for write-heavy tables. Every audited write becomes at least two writes plus the trigger's JSON work, and that latency lands on the user's request. It also struggles with frequent bulk updates and backfills: a single UPDATE touching ten million rows produces ten million audit rows, bloating the audit table and its indexes.

For an occasional backfill, disable auditing through a controlled procedure. Run the change in one transaction that inserts a summary audit row (operator, ticket, predicate, row count), disables the trigger, applies the change and re-enables it. ALTER TABLE … DISABLE TRIGGER takes a lock that blocks concurrent writes to the table, so schedule it accordingly.

Keeping audit data in the same database also creates shared fate. A restore, corruption or a compromised superuser affects the data and its history together.

Alternative Reliability Identity capture Operational cost Same-transaction guarantee
Application-level logging Misses migrations, consoles, direct SQL Excellent Low; code in every write path Yes, if written in the same transaction
CDC via logical decoding Sees every committed change Only if stored in row data High; replication slots retain WAL while consumers lag Atomic with commit, delivered asynchronously
Statement-level logging Records statements, not row values Database role only Moderate; log volume and parsing No; rolled-back statements appear too
Shipping to a separate store Depends on the relay Inherits from trigger Moderate; relay to run Captured in-transaction, stored later

Triggers are cheapest to build and hardest to scale. CDC and a separate store shift that cost to ongoing operations. Application-level logging spreads it across every feature your team writes.

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 postgres audit log table, 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 .env by 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.