SaaS architecture
Audit log per tenant in PostgreSQL: what a trigger costs
A trigger-based audit log in PostgreSQL 17, measured: row against statement triggers, full rows against changed columns, and row-level security on the log.
Now-Next builds SaaS products and takes over existing ones. What we build
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 — with jsonb. With hstore, computing the difference was the fastest of the six triggers we measured, as depesz found in his own test and we confirmed with the full script further down.
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:
| Trigger | 20,000 single-row updates | One UPDATE of 100,000 rows | Audit log after the bulk update | jsonb per change |
|---|---|---|---|---|
| none | — | — | ||
| row trigger, full old and new row | 93.6 MB | 750 bytes | ||
| row trigger, changed columns only | 18.8 MB | 48 bytes | ||
| statement trigger with transition tables, full rows | 93.6 MB | 750 bytes |
The line under each number shows its size against the largest number in the two time columns.
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.
Is hstore faster than jsonb for an audit trigger?
Yes, for the version that stores only the changed columns. After this article appeared, Hubert “depesz” Lubaczewski built his own version of the test with hstore — a five-column table of 100,000 rows, a WHEN (old.* is distinct from new.*) clause on every trigger, best of three runs — and found that with hstore, the difference was faster to log than the full row: the opposite of our jsonb result. His conclusion: not a better choice than jsonb for everything, “but for logging changes in tables, it’s simpler and faster.” He also pointed out that we had published neither the customers table nor the updates, so he had to build his own. We have now written our setup out as a script, added his two hstore expressions and two variants of our own, and ran all six on PostgreSQL 17.11 on 18 October 2026. Our diff triggers have no WHEN clause; like (b), they skip unchanged rows inside the function.
| Trigger | 20,000 single-row updates | One UPDATE of 100,000 rows | Audit log after the bulk update | Bytes per change |
|---|---|---|---|---|
| none | — | — | ||
(a) jsonb, full old and new row | 109.5 MB | 838 | ||
(b) jsonb, changed columns via jsonb_each | 22.8 MB | 73 | ||
(c) hstore, full old and new row | 98.1 MB | 796 | ||
(d) hstore, changed columns via - | 21.9 MB | 66 | ||
(e) as (d), stored as jsonb with hstore_to_jsonb | 21.9 MB | 66 | ||
(f) as (d), stored with hstore_to_jsonb_loose | 22.8 MB | 73 |
Median of three interleaved runs per variant, each in a rolled-back transaction; MB is a million bytes, the size of the audit table with its indexes; bytes per change is the average pg_column_size of old_row plus new_row in the bulk update. The numbers differ from the table at the top of this article, which came from an earlier version of the setup that we did not keep: a full-row change takes 838 bytes here instead of 750, and the loop without a trigger took 209 instead of 595 ms. The bulk ratios came out the same, 4.4 and 6.4 times; the single-row ratios are higher here, 3.1 and 4.4 times.
On our data, depesz’s result holds. The hstore difference (d) was the fastest trigger of all six, 1,802 ms for the bulk update against 3,454 ms for the jsonb difference (b) and 2,358 ms for jsonb full rows (a). Rows (d) and (e) also show where the cost of (b) sits: in working out the difference, not in storing it. Variant (e) computes the difference with hstore and stores it as jsonb, and took 1,858 ms — 56 ms more than (d), 1,596 ms less than (b). The jsonb version converts both rows with to_jsonb and runs a query with jsonb_each and two aggregates for every updated row; we did not profile which part costs most. The hstore version applies one operator twice, hstore(new) - hstore(old) and the reverse, which the documentation describes as deleting “pairs from left operand that match pairs in the right operand”.
So you can keep a jsonb audit log and still get the fast difference, provided you can install the hstore extension and every session that writes uses an ISO date style (see the next section). The documentation marks it as trusted, which means a non-superuser with CREATE privilege on the database can install it.
What does hstore lose in an audit log?
The types. The hstore documentation says it plainly: “Keys and values are simply text strings.” We wrote the same seven updates through (b), (e) and (f) and compared what ended up in the log:
| Change | (b) jsonb difference | (e) hstore_to_jsonb | (f) hstore_to_jsonb_loose |
|---|---|---|---|
credit_limit (numeric) 4102.66 → 4103.66 | 4103.66 | "4103.66" | 4103.66 |
updated_at (timestamptz) | "2026-10-18T12:34:56.789012+00:00" | "2026-10-18 12:34:56.789012+00" | "2026-10-18 12:34:56.789012+00" |
notes (text) set to '1e5' | "1e5" | "1e5" | 100000 |
notes (text) set to 't' | "t" | "t" | true |
name (text) set to '00123' | "00123" | "00123" | "00123" |
email set to null, and back | null | null | null |
All three stored the same keys for every change, and none wrote a row for name = name. The loose function turns anything that looks like a number or a boolean into one, whatever the column type was: a text column that happens to contain 1e5 arrives in the log as the number 100000. Plain hstore_to_jsonb keeps everything as a string, so max(new_row -> 'credit_limit') compares strings. The same happens in a pure hstore log: over the hstore form of nine customers, as (c) stores them, max(h -> 'credit_limit') returned 6541.16; cast to numeric first, 46830.24. That is why we would choose (e) over (f): numbers that became strings can be cast back on read, if the reader knows each column’s type, while loose changes text values that merely look like numbers or booleans.
One of these losses changes data silently. An hstore value stores a timestamp in the text form of the session that wrote it. We repeated the updates in a session with DateStyle = 'SQL, DMY' and TimeZone = 'Europe/Amsterdam'. The jsonb difference still stored ISO, "2026-10-18T14:34:56.789012+02:00". Both variants that compute the difference with hstore, (e) and (f), stored "18/10/2026 14:34:56.789012 CEST". Read back in a session with the default ISO, MDY and cast to timestamptz, that value failed with date/time field value out of range. The old value of the same change, "07/09/2025 07:59:57.508674 CEST", did not fail: it came back as 9 July 2025 instead of 7 September. If any client writes with a DateStyle whose output style is not ISO — SQL, Postgres or German — a timestamp in an audit log built with hstore is ambiguous, whether it is stored as hstore or converted to jsonb afterwards. A jsonb built with to_jsonb does not have this problem: since PostgreSQL 9.4 it writes dates and timestamps in ISO 8601 regardless of DateStyle, and only the offset follows TimeZone. So the hstore difference is only safe when every session that writes has an ISO output style; if you cannot guarantee that, (b) is the slower but safe choice. A swapped day and month cannot be repaired afterwards.
How hstore and jsonb compare outside a trigger — storage, reads, updates and GIN indexes on a million documents — is in hstore vs jsonb performance in PostgreSQL, measured.
Two more differences to know. In a pure hstore log, a key with a null value and a missing key both return null for h -> 'email'; only exist(h, 'email') tells them apart. And jsonb has no timestamp type either: every variant here stores the timestamp as a string.
Reproduce the benchmark
Three scripts: setup.sql creates the data, both audit tables and all six trigger functions, run.sh runs the three workloads against every variant, and verify.sql records the seven updates behind the second table. Start a container with docker run -d --name audit-bench -e POSTGRES_PASSWORD=x postgres:17, then run ./run.sh; afterwards docker exec -i audit-bench psql -U postgres < verify.sql. On our machine the whole run took under two minutes. The data and the updates, for reading here:
create table customers (
id bigint primary key,
tenant_id int not null,
name text not null,
email text,
credit_limit numeric(12,2) not null,
notes text,
updated_at timestamptz not null
);
select setseed(0.42);
insert into customers (id, tenant_id, name, email, credit_limit, notes, updated_at)
select g,
1 + (g % 100),
'Customer ' || g,
'customer' || g || '@tenant' || (1 + (g % 100)) || '.example',
round((random() * 50000)::numeric, 2),
left(repeat(md5(g::text), 7), 200),
timestamptz '2026-01-01 00:00:00+00' - random() * interval '365 days'
from generate_series(1, 200000) g;
create index on customers (tenant_id);
-- the bulk update
update customers set credit_limit = credit_limit + 1 where id <= 100000;
-- the 20,000 single-row updates
do $$ begin
for i in 1..20000 loop
update customers set credit_limit = credit_limit + 1 where id = i;
end loop;
end $$;
Each run starts a fresh psql session, attaches one trigger, runs TRUNCATE on both audit tables and then VACUUM, and times the statement inside a transaction that it rolls back afterwards. The TRUNCATE is there for a reason we found the hard way. Without it, the empty audit table stayed at 14 MB after a rollback — most likely index pages, which VACUUM makes reusable but does not return — and every size after the first run was too large. A third workload, update customers set updated_at = now() where id <= 100000, gave the same order as the bulk update for (d) 1,800 ms, (e) 1,858, (a) 2,243 and (b) 3,454; there, (f) at 1,844 ms came just before (e).
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, and on hstore, read on 18 October 2026; Hubert Lubaczewski, Old tech is sometimes better, 16 October 2026. All measurements are our own: the first table on PostgreSQL 17.10 on 1 October 2026, the hstore comparison on PostgreSQL 17.11 on 17 and 18 October 2026. On to_jsonb and ISO 8601: the PostgreSQL 9.4 release notes, read on 18 October 2026.