SaaS-architectuur
Auditlog per klant in PostgreSQL: wat een trigger kost
Een auditlog met triggers in PostgreSQL 17, nagemeten: rij- tegen statementtriggers, hele rijen tegen gewijzigde kolommen, en row-level security op het log.
Now-Next bouwt SaaS-producten en neemt bestaande over. Wat we bouwen
Vroeg of laat vraagt een klant wie een kredietlimiet heeft gewijzigd, en wanneer. In een multi-tenant SaaS moet dat antwoord uit een auditlog komen dat elke klant voor zijn eigen rijen kan lezen en dat niemand kan bewerken. Een trigger is de gebruikelijke manier om het te vullen, omdat die ook de wijziging vangt van een script of een migratie die nooit door je applicatie ging.
We hebben gemeten wat die trigger kost. Bij 20.000 wijzigingen van één rij kwam er 32 microseconde per wijziging bij. Bij één bulk-UPDATE van 100.000 rijen, zoals een migratie of een backfill die oplevert, werd de opdracht 4,4 keer zo traag met een rijtrigger en 3,6 keer met een statementtrigger. Alleen de gewijzigde kolommen opslaan maakte het log vijf keer kleiner en de trigger trager — met jsonb. Met hstore was het verschil uitrekenen de snelste van de zes triggers die we maten, zoals depesz in zijn eigen test vond en wij met het volledige script verderop bevestigden.
De opzet
PostgreSQL 17.10 in een wegwerpcontainer, standaardinstellingen, mediaan van drie metingen. Een tabel customers met 200.000 rijen verdeeld over 100 klanten, zeven kolommen, waaronder 200 tekens aan notities. Eén generieke audittabel voor alle klanten:
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);
De trigger haalt de klant uit de rij zelf en de gebruiker uit het verzoek, current_setting('app.user_id', true). Elke meting draaide in een transactie die we daarna terugdraaiden, met een VACUUM vooraf, zodat elke variant met dezelfde gegevens begon. Deze tijden zijn gemeten als superuser, voordat we row-level security op de audittabel zetten. De wijzigingen van één rij draaiden in een lus op de server, dus de getallen bevatten geen netwerktijd en geen commits — in een echt verzoek komen die erbij, en is het aandeel van de trigger kleiner.
Hoeveel trager maakt een audittrigger een UPDATE?
2,1 tot 2,6 keer zo traag voor wijzigingen van één rij in deze opzet, en 3,6 tot 6,4 keer voor een bulkwijziging:
| Trigger | 20.000 wijzigingen van één rij | Eén UPDATE van 100.000 rijen | Auditlog na de bulkwijziging | jsonb per wijziging |
|---|---|---|---|---|
| geen | — | — | ||
| rijtrigger, volledige oude en nieuwe rij | 93,6 MB | 750 bytes | ||
| rijtrigger, alleen gewijzigde kolommen | 18,8 MB | 48 bytes | ||
| statementtrigger met transitietabellen, volledige rijen | 93,6 MB | 750 bytes |
De lijn onder elk getal geeft de grootte ten opzichte van het grootste getal in de twee tijdkolommen.
De absolute getallen zeggen meer dan de verhoudingen. De trigger met volledige rijen voegde (1.237 − 595) / 20.000 = 32 microseconde toe aan elke wijziging van één rij. Een webverzoek dat één klant wijzigt, merkt dat niet. Bij de bulkwijziging wordt het zichtbaar: een import van 100.000 rijen ging van 0,6 naar 2,6 seconde, en schreef 94 MB auditlog voor een wijziging van één kolom.
Rijtrigger of statementtrigger?
Voor bulkwijzigingen de statementtrigger. Een rijtrigger draait één keer per rij; een statementtrigger draait één keer per opdracht en ziet alle gewijzigde rijen tegelijk via transitietabellen — “row sets that include all of the rows inserted, deleted, or modified by the current SQL statement”, in de woorden van de documentatie:
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();
De functie schrijft dan alle auditrijen in één insert … select uit new_rows gekoppeld aan old_rows. Bij de wijziging van 100.000 rijen kostte dat 2.104 ms tegen 2.579 ms voor de rijtrigger, een vijfde minder. Bij wijzigingen van één rij was hij iets trager, 1.288 tegen 1.237 ms, omdat hij elke keer de transitietabellen voor één rij opbouwt.
Twee beperkingen uit de documentatie om te kennen voordat je hem kiest: transitietabellen werken alleen bij een AFTER-trigger, en een UPDATE-trigger die ze gebruikt mag geen kolommen opsommen, dus je kunt hem niet beperken tot update of credit_limit.
Volledige rij of alleen de gewijzigde kolommen?
Dat is een ruil tussen opslag en rekenwerk. De versie die alleen opslaat wat veranderde, vergelijkt de oude en nieuwe rij sleutel voor sleutel:
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; -- niets veranderd
Dat bracht de omvang van de jsonb per wijziging van 750 naar 48 bytes, en het auditlog na de bulkwijziging van 93,6 naar 18,8 MB — vijf keer kleiner. Het maakte ook elke wijziging duurder: 3.747 tegen 2.579 ms voor de bulkwijziging, omdat de vergelijking voor elke rij draait. Onze versie slaat ook wijzigingen over die niets veranderden; een WHEN (old.* is distinct from new.*)-clausule op de trigger doet hetzelfde voor de versie met volledige rijen.
Welke past, hangt af van wat je met het log doet. Een klant die wil zien wat er veranderde, heeft genoeg aan het verschil; een rij terugzetten zoals hij op een bepaalde datum was, gaat makkelijker met volledige rijen. Brede rijen met één vaak wijzigende kolom — een last_seen_at, een teller — pleiten sterk voor het verschil.
Is hstore sneller dan jsonb voor een audittrigger?
Ja, voor de versie die alleen de gewijzigde kolommen opslaat. Na dit artikel bouwde Hubert “depesz” Lubaczewski zijn eigen versie van de test met hstore — een tabel van vijf kolommen en 100.000 rijen, een WHEN (old.* is distinct from new.*)-clausule op elke trigger, de beste van drie runs — en vond dat met hstore het verschil sneller vast te leggen was dan de hele rij: het omgekeerde van onze jsonb-uitkomst. Niet beter dan jsonb voor alles, schreef hij, maar voor het vastleggen van wijzigingen eenvoudiger en sneller. Hij merkte ook op dat we de customers-tabel en de wijzigingen niet hadden gepubliceerd. Dat hebben we nu als script gedaan, met zijn twee hstore-expressies en twee varianten van onszelf erbij, en alle zes gedraaid in PostgreSQL 17.11 op 18 oktober 2026. Onze difftriggers hebben geen WHEN-clausule; net als (b) slaan ze ongewijzigde rijen in de functie over:
| Trigger | 20.000 wijzigingen van één rij | Eén UPDATE van 100.000 rijen | Auditlog na de bulkwijziging | Bytes per wijziging |
|---|---|---|---|---|
| geen | — | — | ||
(a) jsonb, hele oude en nieuwe rij | 109,5 MB | 838 | ||
(b) jsonb, gewijzigde kolommen via jsonb_each | 22,8 MB | 73 | ||
(c) hstore, hele oude en nieuwe rij | 98,1 MB | 796 | ||
(d) hstore, gewijzigde kolommen via - | 21,9 MB | 66 | ||
(e) als (d), opgeslagen als jsonb met hstore_to_jsonb | 21,9 MB | 66 | ||
(f) als (d), opgeslagen met hstore_to_jsonb_loose | 22,8 MB | 73 |
Mediaan van drie verweven runs per variant, elk in een teruggedraaide transactie; MB is een miljoen bytes, de omvang van de audittabel met indexen; bytes per wijziging is het gemiddelde pg_column_size van old_row plus new_row bij de bulkwijziging. De getallen wijken af van de tabel bovenaan, die uit een eerdere versie van de opzet kwam die we niet hebben bewaard: een wijziging met hele rijen kost hier 838 bytes in plaats van 750, en de lus zonder trigger 209 in plaats van 595 ms. De verhoudingen bij de bulkwijziging kwamen gelijk uit, 4,4 en 6,4 keer; bij losse wijzigingen zijn ze hier hoger, 3,1 en 4,4 keer.
Op onze gegevens houdt de uitkomst van depesz stand. Het hstore-verschil (d) was de snelste trigger van de zes: 1.802 ms voor de bulkwijziging, tegen 3.454 ms voor het jsonb-verschil (b). Variant (e) laat zien waar de kosten van (b) zitten: in het uitrekenen van het verschil, niet in het opslaan. Die rekent het verschil uit met hstore en slaat het op als jsonb, en kostte 1.858 ms. Welk deel van (b) het meest kost — twee keer to_jsonb, of de query met jsonb_each en twee aggregaten per bijgewerkte rij — hebben we niet uitgesplitst. Je kunt dus een jsonb-auditlog houden en toch het snelle verschil krijgen, als je de extensie hstore mag installeren en elke schrijvende sessie een ISO-datumstijl heeft (zie hieronder); de documentatie noemt hem trusted, zodat een gebruiker zonder superuserrechten met CREATE-recht op de database het kan.
Wat verliest hstore in een auditlog?
De types: in hstore is elke waarde tekst. Met hstore_to_jsonb (e) komt een numeric als string in het log, "4103.66" in plaats van 4103.66. Met hstore_to_jsonb_loose (f) wordt alles wat op een getal of boolean lijkt er een, ongeacht het kolomtype: een tekstkolom met 1e5 werd het getal 100000, en 't' werd true. Daarom zouden wij (e) verkiezen boven (f): een getal dat een string werd, zet een cast bij het lezen terug, als de lezer het kolomtype kent; loose verandert tekst die alleen op een getal lijkt.
Eén verlies verandert gegevens zonder melding. Een tijdstip in hstore krijgt de tekstvorm van de sessie die schrijft. In een sessie met DateStyle = 'SQL, DMY' en TimeZone = 'Europe/Amsterdam' sloegen de twee varianten die het verschil met hstore uitrekenen, (e) en (f), "18/10/2026 14:34:56.789012 CEST" op; het jsonb-verschil (b) bleef ISO, "2026-10-18T14:34:56.789012+02:00". Teruggelezen in een sessie met de standaard ISO, MDY en omgezet naar timestamptz faalde die waarde met date/time field value out of range. De oude waarde van dezelfde wijziging, "07/09/2025 07:59:57.508674 CEST", faalde niet: die kwam terug als 9 juli 2025 in plaats van 7 september. Schrijft een sessie met een DateStyle waarvan de uitvoerstijl niet ISO is (SQL, Postgres of German), dan is een tijdstip in een auditlog dat met hstore is opgebouwd dubbelzinnig, ook als het daarna als jsonb is opgeslagen. to_jsonb heeft dat probleem niet: sinds PostgreSQL 9.4 schrijft het datums en tijdstippen in ISO 8601, ongeacht DateStyle; alleen de offset volgt TimeZone. Het hstore-verschil is dus alleen veilig als elke schrijvende sessie een ISO-uitvoerstijl heeft. Kun je dat niet garanderen, dan is (b) de tragere maar veilige keuze; een verwisselde dag en maand herstel je achteraf niet.
De meting zelf draaien
Drie scripts: setup.sql maakt de gegevens, beide audittabellen en alle zes triggerfuncties, run.sh draait de drie workloads tegen elke variant in een Docker-container, en verify.sql legt de wijzigingen achter de typevergelijking vast. Start eerst een container met docker run -d --name audit-bench -e POSTGRES_PASSWORD=x postgres:17, draai dan ./run.sh, en daarna docker exec -i audit-bench psql -U postgres < verify.sql. Het commentaar in de scripts is Engels; de opzet in het kort: 200.000 klanten over 100 tenants met setseed(0.42), de bulkwijziging update customers set credit_limit = credit_limit + 1 where id <= 100000, en 20.000 losse wijzigingen in een DO-lus. Elke run begint met een nieuwe psql-sessie, TRUNCATE op beide audittabellen en VACUUM. Die TRUNCATE staat er niet voor niets: zonder bleef de lege audittabel na een rollback 14 MB groot — vermoedelijk indexpagina’s, die VACUUM herbruikbaar maakt maar niet teruggeeft.
Hoe houd je de ene klant uit het auditlog van de andere?
Met dezelfde row-level security als de rest van de gegevens, en zonder UPDATE- of DELETE-rechten. We gaven de applicatierol alleen SELECT en INSERT op de audittabel, en twee beleidsregels:
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);
De trigger draait als de gebruiker die de wijziging deed, dus zijn insert gaat door dezelfde controle. Met klant 1 als context schreef een wijziging van drie rijen in customers van klant 1 drie auditrijen, met changed_by gevuld vanuit het verzoek. Klant 2 zag 0 rijen in het auditlog. Een UPDATE of DELETE op het log als applicatierol faalde met permission denied for table audit_log. Het log is dan voor de applicatie alleen aan te vullen — een superuser kan het nog steeds wijzigen en de eigenaar van de tabel kan de bescherming uitzetten, dus kopieer het voor alles wat in een geschil overeind moet blijven naar een plek waar de databaserol van de applicatie niet bij kan.
Een migratie of script dat over klanten heen werkt, heeft per klant de klantcontext nodig of een rol met BYPASSRLS; zo’n rol komt ook langs het auditbeleid, en de trigger blijft zijn wijzigingen vastleggen — zo liepen onze metingen hierboven. De controle bij het invoegen heeft nog een gevolg. Schrijft code ooit een auditrij voor een andere klant dan de huidige context, dan faalt de hele wijziging in plaats van dat hij onder de verkeerde klant wordt vastgelegd. Dat is dezelfde garantie die we voor de gegevens zelf hebben gemeten in row-level security in PostgreSQL: waar tenants lekken.
Wat we niet hebben gemeten
Inserts en deletes, een gepartitioneerde audittabel (maandpartities maken van opschonen een DROP in plaats van een DELETE, zoals we voor klantgegevens maten in het indexenartikel), een koude cache, de kosten van het log per klant teruglezen, en auditing via een extensie zoals pgAudit, die opdrachten naar het serverlog schrijft in plaats van rijen naar een tabel. Alle tijden komen van één machine met een warme cache; lees ze als ordes van grootte.
Bronnen: de PostgreSQL 17-documentatie over CREATE TRIGGER (transitierelaties, statement- en rijtriggers), gelezen op 1 oktober 2026, en over hstore, gelezen op 18 oktober 2026; Hubert Lubaczewski, Old tech is sometimes better, 16 oktober 2026. Alle metingen zijn van onszelf: de eerste tabel in PostgreSQL 17.10 op 1 oktober 2026, de hstore-vergelijking in PostgreSQL 17.11 op 17 en 18 oktober 2026. Over to_jsonb en ISO 8601: de release notes van PostgreSQL 9.4, gelezen op 18 oktober 2026.