Audit log per tenant in PostgreSQL: what a trigger costs

Sooner or later a customer asks who changed a credit limit, and when. In a multi-tenant SaaS the answer has to come from an audit log that every tenant can read for their own rows and nobody can edit. A trigger is the usual way to fill it, because it also catches the change made by a script or a migration that never went through your application.

We measured what that trigger costs. On 20,000 single-row updates it added 32 microseconds per update. On one bulk UPDATE of 100,000 rows, the kind a migration or backfill produces, it made the statement 4.4 times slower as a row trigger and 3.6 times as a statement trigger. Storing only the changed columns made the log five times smaller and the trigger slower.

The setup

PostgreSQL 17.10 in a throwaway container, default settings, median of three runs. A customers table with 200,000 rows spread over 100 tenants, seven columns including 200 characters of notes. One generic audit table for every tenant:

create table audit_log (
  id bigint generated always as identity primary key,
  tenant_id int not null,
  table_name text not null,
  row_id bigint not null,
  action text not null,
  changed_at timestamptz not null default clock_timestamp(),
  changed_by text,
  old_row jsonb,
  new_row jsonb
);
create index on audit_log (tenant_id, changed_at);

The trigger takes the tenant from the row itself and the user from the request, current_setting('app.user_id', true). Every run happened inside a transaction that we rolled back afterwards, with a VACUUM before each run, so each variant started from the same data. These timings ran as superuser, before we put row-level security on the audit table. The single-row updates ran in a server-side loop, so the numbers contain no network time and no commits — in a real request those come on top, and the relative cost of the trigger is smaller.

How much slower does an audit trigger make an UPDATE?

2.1 to 2.6 times as slow for single-row updates in this setup, and 3.6 to 6.4 times for a bulk update:

The absolute numbers say more than the ratios. The full-row trigger added (1,237 − 595) / 20,000 = 32 microseconds to each single-row update. A web request that changes one customer does not notice that. The bulk update is where it shows: an import of 100,000 rows went from 0.6 to 2.6 seconds, and wrote 94 MB of audit log for a change to one column.

Row trigger or statement trigger?

For bulk changes, the statement trigger. A row trigger runs once per row; a statement trigger runs once per statement and sees every changed row at once through transition tables — “row sets that include all of the rows inserted, deleted, or modified by the current SQL statement”, in the words of the documentation:

create trigger customers_audit
  after update on customers
  referencing old table as old_rows new table as new_rows
  for each statement execute function audit_stmt();

The function then writes the whole audit batch in one insert … select from new_rows joined to old_rows. On the 100,000-row update that took 2,104 ms against 2,579 ms for the row trigger, a fifth less. On single-row updates it was slightly slower, 1,288 against 1,237 ms, because it builds the transition tables for one row every time.

Two restrictions from the documentation to know before you choose it: transition tables only work on an AFTER trigger, and an UPDATE trigger that uses them may not list columns, so you cannot limit it to update of credit_limit.

Full row or only the changed columns?

That is a trade between storage and CPU. The version that stores only what changed compares the old and new row key by key:

select jsonb_object_agg(key, o -> key), jsonb_object_agg(key, value)
  into d_old, d_new
from jsonb_each(n)
where o -> key is distinct from value;
if d_new is null then return null; end if;  -- nothing changed

That brought the jsonb per change from 750 to 48 bytes, and the audit log after the bulk update from 93.6 to 18.8 MB — five times smaller. It also made every update more expensive: 3,747 against 2,579 ms for the bulk update, because the comparison runs for every row. Our version also skips updates that changed nothing; a WHEN (old.* is distinct from new.*) clause on the trigger does the same for the full-row version.

Which one fits depends on what you do with the log. A customer who wants to see what changed is served by the difference; restoring a row as it was on a given date is easier with full rows. Wide rows with one volatile column — a last_seen_at, a counter — favour the difference strongly.

How do you keep one tenant out of another tenant’s audit log?

With the same row-level security as the rest of the data, and without UPDATE or DELETE rights. We gave the application role only SELECT and INSERT on the audit table, and two policies:

alter table audit_log enable row level security;
alter table audit_log force row level security;
create policy audit_read on audit_log for select
  using (tenant_id = nullif(current_setting('app.tenant', true), '')::int);
create policy audit_write on audit_log for insert
  with check (tenant_id = nullif(current_setting('app.tenant', true), '')::int);

The trigger runs as the user who made the change, so its insert goes through the same check. With tenant 1 as context, an update of three of tenant 1’s customers wrote three audit rows with changed_by filled from the request. Tenant 2 counted 0 rows in the audit log. An UPDATE or DELETE on the log as the application role failed with permission denied for table audit_log. The log is then append-only for the application — a superuser can still change it and the table owner can switch the protection off, so for anything that has to hold up in a dispute, copy it somewhere the application’s database role cannot reach.

A migration or script that works across tenants needs either the tenant context per tenant or a role with BYPASSRLS; such a role passes the audit policy as well, and the trigger keeps logging its changes — our timings above ran that way. The insert check has one more effect. If code ever writes an audit row for a tenant other than the current context, the whole change fails instead of being logged under the wrong tenant. That is the same guarantee we measured for the data itself in row-level security in PostgreSQL: where tenants leak.

What we did not measure

Inserts and deletes, a partitioned audit table (monthly partitions make retention a DROP instead of a DELETE, as we measured for tenant data in the indexes article), a cold cache, the cost of reading the log back per tenant, and extension-based auditing such as pgAudit, which logs statements to the server log rather than rows to a table. All timings are from one machine with a warm cache; read them as orders of magnitude.

Sources: the PostgreSQL 17 documentation on CREATE TRIGGER (transition relations, statement and row triggers), read on 1 October 2026. All measurements are our own, on PostgreSQL 17.10, on 1 October 2026.