Audit Log in a Monolith Database Without Slowing Writes

What an audit log in a monolith database must record, and who needs one

Every audit record answers the same questions: who acted, on what, how, and when. In practice that means the actor identity (user ID, service account, or the admin impersonating someone), the entity type and primary key, the operation (insert, update, delete), a timestamp taken from the database clock, the changed fields as old/new values or a jsonb diff, and a request or correlation ID that links the row back to logs and traces. Add a retention rule, because a record of unknown lifespan becomes a storage or legal problem later.

Two different needs share the name "audit log." A compliance-grade audit trail serves security, legal, and auditors working under SOX, HIPAA, or internal access policies. It must be complete, tamper-evident, and hard for the application to skip. A debugging change history serves support and product engineers asking "why does this order show the wrong address?" It can tolerate gaps, needs fewer guarantees, and can expire after weeks.

The requirement determines the mechanism. If a missed record is a finding in an audit, capture has to be impossible to bypass. If a missed record is only an inconvenience, an application hook is usually enough.

A full audit trail is often overkill. Internal admin tools with no regulatory exposure rarely need it. The same goes for high-churn tables such as session heartbeats, counters, and cache timestamps, where every update costs write amplification and none of the history is worth reading.

Choosing a capture point: application hooks, triggers, or change data capture

Where you capture changes determines which writes you can see at all. There are three common places to do it.

Application hooks know the authenticated user, request ID, and intent. They only see writes that go through the model layer, though. A raw UPDATE in a backfill job, a data-fix migration, or a teammate's psql session bypasses them, and none of those writes appear in the log. Use hooks when every write goes through one codebase and you can enforce that rule. Avoid them when compliance requires complete coverage.

Triggers run inside the writing transaction, so no row change escapes them, whatever client issued it. A trigger knows nothing about the end user unless the application passes that information to the database session. Row-level triggers also do not fire on TRUNCATE. For most monoliths this is the right default.

Change data capture reads the WAL through a replication slot and adds no work to the write path. The cost moves to operations. You run a consumer, manage the slot, and accept that the log lags behind the data. If the consumer stalls, the slot keeps WAL on the primary until its disk fills. Choose CDC when you already run streaming infrastructure and have alerting on slot lag. Avoid it when no team owns that infrastructure.

How it works: same-transaction capture with asynchronous fan-out

The pattern this guide recommends splits audit work into two stages with different guarantees. The first stage is synchronous and small. Inside the same transaction as the business write, a trigger inserts one narrow row into the audit table: table name, primary key, operation, actor, transaction ID, timestamp, and the changed columns. If downstream systems need to hear about the change, the same transaction can also insert an outbox row. All three commit together or roll back together. That rules out a committed change with no audit record, and an audit record for a change that never happened.

The second stage is asynchronous. A relay process reads committed outbox rows and delivers them to consumers. Typical consumers resolve actor IDs to names, index changes for search, load analytics tables, and send notifications. None of that work happens in the write path. A slow search cluster or an unreachable webhook endpoint can't add latency to an UPDATE or hold its locks longer. The write pays for two extra local inserts and nothing more.

This design has two consequences you need to plan for:

• Delivery is at-least-once. The relay can send a row and then crash before it marks the row done. On restart it sends that row again. Consumers must be idempotent. In practice, each consumer records the outbox row's ID and skips IDs it has already seen.
• Derived views lag reality. The audit table is correct the moment the transaction commits. The search index and dashboards are not, and stay behind for as long as the relay is behind. Show users a "last updated" time on those views, and send compliance queries to the audit table itself.

Copy  Application: UPDATE ... (business write)
        |
        v
+------------------- ONE DATABASE TRANSACTION -------------------+
|  +----------------+   trigger   +---------------------------+  |
|  | business row   |------------>| audit_log row (narrow)    |  |
|  | (UPDATE)       |             | table, pk, op, actor,     |  |
|  +----------------+             | txid, ts, changed cols    |  |
|          |                      +---------------------------+  |
|          |  optional                                           |
|          v                                                     |
|  +----------------+                                            |
|  | outbox row     |   all three COMMIT or ROLLBACK together   |
|  +----------------+                                            |
+----------------------------------------------------------------+
           |
           | reads committed rows (async, outside the write path)
           v
    +--------------+   at-least-once delivery
    | outbox relay |-----------+-------------+-------------+
    +--------------+           |             |             |
                               v             v             v
                         +-----------+ +-----------+ +-----------+
                         | enrich    | | search    | | analytics,|
                         | actor IDs | | index     | | notify    |
                         +-----------+ +-----------+ +-----------+
                         idempotent: skip already-seen outbox IDs
                         derived views can lag the audit table

The business write, a narrow audit row, and an optional outbox row commit in one transaction, while a relay delivers outbox rows at-least-once to idempotent consumers outside the write path.

Implementing PostgreSQL triggers with transaction-scoped actor context

The application opens each transaction by setting app.actor_id and app.request_id with SET LOCAL or set_config(name, value, true). The trigger reads them with current_setting(name, true), which returns NULL instead of raising an error when the setting is absent. Session-level SET is unsafe behind a transaction-mode pooler. The server connection is handed to another client after commit, so the next request inherits the previous request's actor. Transaction-scoped settings are cleared at commit or rollback.

The triggers

The row-level AFTER UPDATE function builds to_jsonb(OLD) and to_jsonb(NEW). It keeps only the keys whose values differ, then strips excluded columns with jsonb - text[]. Use this to drop password hashes, tokens and raw PII, or to replace their values with a fixed marker. If every changed column is excluded, skip the insert.

For bulk updates, a FOR EACH STATEMENT trigger declared with REFERENCING OLD TABLE AS ... NEW TABLE AS ... joins the two transition tables on the primary key. It writes all audit rows in one INSERT ... SELECT instead of firing once per row.

Missing actor

You have two options when the actor is missing:

• Reject: raise an exception. This guarantees attribution, but every migration, cron job and manual psql fix must now set an actor.
• Flag: write the row with actor_unknown = true and alert on it.

Pick one deliberately.

Ownership and failure

• Have a dedicated role own the function, and declare it SECURITY DEFINER with a pinned search_path. The application role can then trigger audit inserts without holding INSERT, UPDATE or DELETE grants on the audit table.
• Triggers on the same event fire in alphabetical order by name. Name them so the audit trigger runs last.
• Any error inside the trigger aborts the statement and rolls back the valid business write with it.

Copy-- Owned by a dedicated role; SECURITY DEFINER lets the app role write audit rows without grants on audit.log.
CREATE FUNCTION audit.log_update() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp  -- pinned so callers cannot shadow objects
AS $$
DECLARE
  old_j jsonb := to_jsonb(OLD);
  new_j jsonb := to_jsonb(NEW);
  -- NULLIF: after a SET LOCAL ends, a custom setting can read back as '' rather than NULL
  actor text  := NULLIF(current_setting('app.actor_id', true), '');
  diff  jsonb;
BEGIN
  -- Keep only the columns whose values actually changed
  SELECT jsonb_object_agg(n.key, jsonb_build_object('old', old_j -> n.key, 'new', n.value))
    INTO diff
    FROM jsonb_each(new_j) AS n
   WHERE n.value IS DISTINCT FROM old_j -> n.key;
  -- Drop excluded columns (secrets, PII) passed as trigger arguments
  diff := diff - TG_ARGV::text[];
  IF diff IS NULL OR diff = '{}'::jsonb THEN
    RETURN NULL;  -- nothing auditable changed
  END IF;
  -- Flag policy: record the change and mark it; swap for RAISE EXCEPTION to reject instead
  INSERT INTO audit.log (table_name, row_pk, actor_id, actor_unknown, request_id, changes)
  VALUES (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, old_j ->> 'id', actor, actor IS NULL,
          NULLIF(current_setting('app.request_id', true), ''), diff);
  RETURN NULL;  -- return value is ignored for AFTER row triggers
END $$;
ALTER FUNCTION audit.log_update() OWNER TO audit_owner;

-- 'zz_' prefix: same-event triggers fire in name order, so this one runs last
CREATE TRIGGER zz_audit_update AFTER UPDATE ON public.customers
  FOR EACH ROW EXECUTE FUNCTION audit.log_update('password_hash', 'api_token');

-- Application side: transaction-scoped context, cleared at COMMIT/ROLLBACK
BEGIN;
SELECT set_config('app.actor_id', 'user:4821', true), set_config('app.request_id', 'req-7f3a9c', true);
UPDATE public.customers SET email = 'new@example.com' WHERE id = 42;
COMMIT;

A SECURITY DEFINER row-level AFTER UPDATE trigger records a jsonb diff of only the changed columns. It reads transaction-scoped actor and request IDs, strips excluded columns passed as trigger arguments, flags writes that have no actor, and uses a name that makes it fire last.

Designing the audit table for append-heavy writes and integrity

Every index on the audit table is paid for on every audited write, so keep only what investigations use. That is usually one composite index on entity type, entity id and time, and nothing on the payload. Partition by time, monthly or weekly depending on volume. New rows then land in a small, hot partition, and retention becomes detaching or dropping an old partition rather than running a large DELETE.

Do not add foreign keys back to the audited tables. They add a lookup to every insert, and they would block deleting the very rows whose history you need to keep. Revoke UPDATE and DELETE on the audit table from the application role. Because the trigger function runs with its owner's privileges (SECURITY DEFINER), the application can append rows but cannot rewrite them.

Revoked privileges do not stop a superuser. If you need tamper evidence, add a hash chain in which each row stores a hash of its own content plus the previous row's hash. Compute it in a single background exporter, not in the trigger. Chaining inside the trigger would serialize every concurrent writer on the chain's tail. Alternatively, ship rows to write-once storage.

Jsonb diffs survive added and dropped columns, but readers must expect older row shapes. Migrations and backfills should run under a distinct actor so they do not look like user activity.

Diffs are compact, but reconstructing a row at a point in time means replaying them from the start. Periodic full snapshots bound that replay at the cost of more storage.

Copy-- Append-only audit table: monthly partitions, one investigation index, no FKs.
CREATE SEQUENCE audit_log_id_seq;
CREATE TABLE audit_log (
  id          bigint NOT NULL DEFAULT nextval('audit_log_id_seq'),
  entity_type text NOT NULL,
  entity_id   bigint NOT NULL,       -- plain value, no FK to the audited table
  action      text NOT NULL,
  actor       text NOT NULL,
  changed_at  timestamptz NOT NULL DEFAULT now(),
  diff        jsonb                  -- not indexed
) PARTITION BY RANGE (changed_at);
CREATE TABLE audit_log_2026_11 PARTITION OF audit_log
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE INDEX ON audit_log (entity_type, entity_id, changed_at);

-- Runs as its owner, so the app role needs no write grants on audit_log.
CREATE FUNCTION audit_row() RETURNS trigger LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public, pg_temp AS $$
DECLARE o jsonb; n jsonb;
BEGIN
  IF TG_OP <> 'INSERT' THEN o := to_jsonb(OLD); END IF;
  IF TG_OP <> 'DELETE' THEN n := to_jsonb(NEW); END IF;
  INSERT INTO audit_log (entity_type, entity_id, action, actor, diff)
  VALUES (TG_TABLE_NAME, (coalesce(n, o)->>'id')::bigint, TG_OP,
    -- Migrations: SET LOCAL app.actor = 'migration:add_tax_code'
    coalesce(nullif(current_setting('app.actor', true), ''), session_user),
    CASE WHEN TG_OP = 'UPDATE' THEN
      (SELECT jsonb_object_agg(key, value) FROM jsonb_each(n)
        WHERE o -> key IS DISTINCT FROM value)  -- changed keys only
    ELSE coalesce(n, o) END);
  RETURN NULL;
END $$;

CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_row();
REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON audit_log FROM app_role;
GRANT SELECT ON audit_log TO app_role;

A PostgreSQL audit table partitioned by month with one composite index and no foreign keys, filled by a SECURITY DEFINER trigger that records jsonb diffs and a distinct actor for migrations, while the application role is denied any direct writes.

Where write cost comes from and how to measure it on your workload

Each audited write pays four costs, and they show up in different places.

To measure the cost, replay representative traffic against a staging copy twice: once with the trigger enabled and once with ALTER TABLE ... DISABLE TRIGGER. Record pg_current_wal_lsn() before and after each run. pg_wal_lsn_diff() between the two values gives the WAL bytes generated. Compare p50 and p99 commit latency rather than averages. Run each pass long enough to span several checkpoints, because full-page writes otherwise skew the comparison.

Illustrative estimate (assumed numbers, not a benchmark). Assume 400 bytes per audit row including indexes, 50 changes per second, and 365-day retention. Storage = row size × rate × 86,400 × days = 400 × 50 × 86,400 × 365 ≈ 631 GB. Daily growth is about 1.7 GB, so a monthly partition holds roughly 52 GB. That size is manageable to vacuum, back up and drop as a unit.

Dropping a partition is cheap, but it removes every record in it. When a legal hold applies, detach the partition and keep it instead of dropping it. Deletion requests conflict with append-only history. Store personal fields under a per-subject encryption key, and destroy that key when a deletion request arrives. The history stays intact and the personal data becomes unreadable.

Copy#!/usr/bin/env bash
# Compare WAL volume and commit latency with the audit trigger on vs off.
# Run against an isolated staging copy so other sessions don't add WAL.
set -euo pipefail
DB=staging_app
TABLE=orders
TRIGGER=orders_audit_trg

run_pass() {
  local label=$1
  psql -d "$DB" -qc "CHECKPOINT"   # start each pass from the same checkpoint state
  local start end
  start=$(psql -d "$DB" -Atc "SELECT pg_current_wal_lsn()")
  # 15 minutes should span several checkpoints. -l logs per-transaction latency.
  pgbench -n -c 16 -j 4 -T 900 -f workload.sql -l --log-prefix="lat_$label" "$DB" > /dev/null
  end=$(psql -d "$DB" -Atc "SELECT pg_current_wal_lsn()")
  echo "$label WAL bytes: $(psql -d "$DB" -Atc "SELECT pg_wal_lsn_diff('$end', '$start')")"
  # Field 3 of the pgbench log is the transaction latency in microseconds.
  cat lat_"$label".* | awk '{print $3}' | sort -n | awk '
    { v[NR] = $1 }
    END { printf "  p50=%.1fms p99=%.1fms\n", v[int(NR*0.50)]/1000, v[int(NR*0.99)]/1000 }'
}

psql -d "$DB" -qc "ALTER TABLE $TABLE ENABLE TRIGGER $TRIGGER"
run_pass on

psql -d "$DB" -qc "ALTER TABLE $TABLE DISABLE TRIGGER $TRIGGER"
run_pass off

# Restore the trigger so staging matches production afterwards.
psql -d "$DB" -qc "ALTER TABLE $TABLE ENABLE TRIGGER $TRIGGER"

A bash script that replays the same pgbench workload with the audit trigger enabled and then disabled. For each pass it reports WAL bytes from pg_wal_lsn_diff and p50/p99 commit latency from the pgbench per-transaction log.

Alternatives and impact on development cost and engineering time

Each alternative moves the cost somewhere else rather than removing it.

The trigger approach has its own running costs. A per-table trigger breaks or goes stale when columns are renamed. A generic row-to-JSON trigger avoids that, but its diffs become harder to query. Triggers also hide behaviour. An engineer profiling a slow UPDATE may not know an insert is happening too, so list audit triggers in schema review and name them consistently. The outbox relay is one more process to monitor for lag and stuck rows. Storage grows with write volume.

Illustrative estimate (assumed numbers, not a benchmark): daily growth = audited writes per day × average audit row size. With 2,000,000 writes and 1 KB per row, that is about 2 GB per day, or about 730 GB per year before compression or partition pruning.

The savings come during incidents and audits. "Who changed this invoice?" becomes one query by row key that returns the actor, request ID and before/after values. You don't have to piece it together from rotated application logs. Compliance requests become queries instead of projects. Bespoke per-feature history tables, each with its own schema and bugs, can be retired in favour of one shared trail.

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 monolith database, 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.