Postgres event tables: partition-swap in plaats van DELETE

Het Slack-bericht kwam binnen om 06:47 op woensdag. De infra-lead bij een data-consultancy van 28 mensen in Zwolle was gepingd door Pingdom: hun hoofd-API liep tegen timeouts aan, de gemeente-dashboards waren leeg, en de Postgres-CPU stond al sinds vlak na middernacht op 100%. De CEO had om 09:00 een call met een gemeente-klant. De standby replica liep vier uur achter. Niemand had de database in productie zes weken aangeraakt. Op vrijdagmiddag was de events table per maand gepartitioneerd en bestond de retentie-cron uit één regel DROP.

De cron die alles brak was twaalf tekens intentie en één puntkomma:

DELETE FROM events
WHERE created_at < now() - interval '90 days';

Dat statement vrat zich door 41 miljoen rows heen. Het begon om 02:00, knalde frontaal op de autovacuum-worker die om 02:30 afgaat, en zette de table op een manier op slot waar geen normale query omheen kon. Om 06:47 was de delete klaar, maar de table had uren aan bloat om op te ruimen, de index was een kerkhof, de WAL was voorbij het punt waarop replicatie nog kon bijbenen, en elke leesquery wachtte op een cleanup-proces dat niet wilde loslaten.

Dit is het verhaal van waarom wij überhaupt gestopt zijn met DELETEs schrijven tegen event tables.

De vorm van de storing

Er liepen tegelijk drie storingen, en maar één daarvan was de voor de hand liggende.

De eerste was de cron zelf. Eenenveertig miljoen rows is geen query, dat is een workload. Postgres gebruikt MVCC, dus een DELETE maakt geen ruimte vrij, maar markeert rijen als niet-meer-zichtbaar. Eenenveertig miljoen rijversies moesten gemarkeerd worden, de index-entries moesten blijven staan zodat vacuum ze later kon opruimen, en elke schrijfactie op de table moest nu over de dode tuples heen stappen. Het disk-gebruik groeide tijdens de delete zelfs, omdat de WAL elke wijziging logde.

De tweede was de botsing met autovacuum. Autovacuum zou om 02:30 zijn geplande scan van events doen. In plaats daarvan trof het de table aan onder een row-exclusive lock van de delete. Autovacuum wacht netjes. Terwijl het wacht, blijft de dead-tuple counter op de table oplopen. Tegen de tijd dat de delete klaar was, had autovacuum uren werk in de wacht staan, en het draaide in single-threaded brokken tegen een table waar elke API-request van las.

De derde was replicatie. De standby streamde WAL sneller binnen dan het kon toepassen. Eenenveertig miljoen rijdeletes betekent eenenveertig miljoen WAL-records, plus opschoning van index-entries, plus uiteindelijk VACUUM-records. De standby zakte verder achterop, en de read replica waar de BI-tool van afhing was niet meer bruikbaar.

De schade naar klanten was groter dan de database-log deed vermoeden. Elke gemeente-medewerker die tussen 06:50 en 11:20 een dashboard opende kreeg een spinner te zien. De BI-tool die raadsvergadering-rapportages draaide stond op de achterlopende replica, dus de cijfers die het teruggaf waren vier uur oud zonder dat aan te geven. Twee gemeenten hebben een supportticket aangemaakt. Eén ervan refereerde later in de verlengingsonderhandeling aan dit incident.

Waarom dit blijft gebeuren

Het patroon zit overal omdat de syntax onschuldig oogt. DELETE FROM events WHERE created_at < ... ziet eruit als opruimen. Elk team schrijft er één. De meesten lopen niet tegen de muur op omdat hun event table klein is, of de cron op een rustig moment draait, of autovacuum toevallig niet botst. Het team in Zwolle draaide deze query al twee jaar elke nacht. Het werkte prima, totdat het dat niet meer deed.

Wat je raakt: de kost van een DELETE op een grote table is niet evenredig met het aantal rijen dat je verwijdert. Het is evenredig met het werk dat de database doet om de verwijdering te verwerken. Dat werk gebeurt later, asynchroon, tegen hetzelfde hete pad dat live traffic bedient. Een delete van 41M is niet "verwijder 41M rijen". Het is "verwijder 41M rijen, markeer 41M index-entries als dood, genereer ongeveer 6GB WAL, stuur die WAL naar de standby, zet autovacuum-werk in de wacht, houd een uur lang een row-exclusive lock vast, en blaas de table met 30% op tot de volgende aggressieve vacuum".

De conclusie waar we op uitkwamen, na de postmortem op het whiteboard gekrabbeld, is dezelfde waar elke Postgres-performance-uitleg uiteindelijk op uitkomt. Wil je rijen snel weghalen, zonder bloat, zonder autovacuum-drama, zonder lock op een hete table, dan is de enige operatie die dat allemaal doet een DROP. Al het andere ruilt de ene kost in voor de andere.

Het patroon dat wij nu draaien

Voor elke langlopende agent die we deployen, is de event table per maand gepartitioneerd met native declaratieve partitionering. Verwijderen is nooit een DELETE. Het is een DROP van de oudste partitie.

De base table ziet er zo uit:

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    agent_id    uuid          NOT NULL,
    kind        text          NOT NULL,
    payload     jsonb         NOT NULL,
    created_at  timestamptz   NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Twee dingen om op te merken. De primary key bevat created_at omdat Postgres eist dat de partition key onderdeel is van elke unique constraint op een gepartitioneerde table. En er is geen foreign key vanuit events naar iets zwaars; events zijn append-only en bij ontwerp wegwerpbaar.

Elke maand krijgt zijn eigen partitie:

CREATE TABLE events_2026_06
    PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE INDEX events_2026_06_agent_idx
    ON events_2026_06 (agent_id, created_at DESC);

De agent schrijft naar events en Postgres routeert de rij naar de juiste child. Leesqueries tegen events gebruiken partition pruning, dus een query die filtert op created_at >= now() - interval '24 hours' raakt alleen de partities van de huidige en vorige maand.

Negentig dagen aan data verwijderen is nu dit:

DROP TABLE events_2026_03;

Dat is de hele klus. De operatie pakt een ACCESS EXCLUSIVE lock voor de duur van de metadata-wijziging (milliseconden), dropt het bestand op disk, geeft de lock weer vrij. Geen rij-scans, geen WAL per rij, geen dode tuples, geen botsing met autovacuum. Replicatie stuurt één DDL-record door.

Partities vooraf klaarzetten

De valkuil bij handmatig partitioneren: vergeten om de partitie voor volgende maand aan te maken. Als juni voorbij is en events_2026_07 bestaat niet, dan loopt de volgende insert vast. Wij gebruiken pg_partman om partities op schema aan te maken en af te voeren:

CREATE EXTENSION IF NOT EXISTS pg_partman;

SELECT partman.create_parent(
    p_parent_table  => 'public.events',
    p_control       => 'created_at',
    p_interval      => '1 month',
    p_premake       => 4
);

UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false
WHERE parent_table = 'public.events';

p_premake => 4 houdt vier maanden aan toekomstige partities klaarstaan. retention_keep_table = false vertelt pg_partman om partities ouder dan negentig dagen daadwerkelijk te DROPpen wanneer de onderhoudsjob draait, in plaats van ze enkel los te koppelen. Een nachtelijke aanroep van SELECT partman.run_maintenance() doet de rollover en de retentie-sweep in één keer. Het klaart de klus ruim binnen een seconde op een table met honderden miljoenen rijen, omdat het nooit een rij leest.

Een live table migreren

De Zwolle-opruiming moest gebeuren op een table die al twee jaar data bevatte en waar elke seconde naartoe werd geschreven. Wij hebben de writes niet gepauzeerd. De migratie liep in deze volgorde:

1. Maak de nieuwe gepartitioneerde table aan als events_partitioned, met dezelfde kolommen en indexen, met RANGE als parent.
2. Maak partities aan die het historische bereik dekken plus vier maanden vooruit.
3. Kopieer historische rijen in batches van 500k, oudste eerst, met INSERT INTO events_partitioned SELECT ... FROM events WHERE created_at BETWEEN ... AND .... Draai dit tegen de WAL-positie van de standby, zodat de live table writes blijft accepteren.
4. Zodra de kopie bij "gisteren" is, pak een schrijflock van zestig seconden, kopieer de staart, wissel de tabelnamen, wijs de applicatie naar de nieuwe table, laat los.
5. Drop de oude table een dag later, nadat een back-up dat bevestigt.

ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;

De applicatie-code is niet aangepast. De agent bleef schrijven. Het venster waarin writes geblokkeerd waren bleef onder een minuut. We hebben het op zondag om 03:00 Amsterdamse tijd gedraaid.

Wat het kostte, wat het scheelde

Vóór de partitionering was de events table 78GB op disk, genereerde de nachtelijke delete ongeveer 6GB WAL, draaide autovacuum twee tot drie uur na elke delete, en piekte de replicatie-lag op veertig minuten in een normale nacht en vier uur in een slechte.

Na de partitionering is de table even groot op disk, minus de afgerolde maand. Retentie draait in 180 milliseconden. WAL-verkeer voor de retentie-job bestaat uit twee DDL-records. Autovacuum op de table raakt alleen de partitie van de huidige maand, die klein genoeg is om binnen dertig seconden klaar te zijn. Replicatie-lag tijdens retentie is niet meer te meten.

Eén ding hadden we niet voorzien. Na de swap koos de query planner voor partition pruning plus een per-partitie index-scan op het hetste leesendpoint, waar de single-table-layout een sequential scan met date filter achter een te brede statistics target deed. De mediane leeslatency op dat endpoint zakte van 23ms naar 6ms, zonder dat wij een query aanraakten. De kosten-gebaseerde planner doet zijn huiswerk zodra hij de partitiegrenzen heeft om mee te werken.

Het incident van 06:47 is niet teruggekomen. Geen ander lock-event over de hele table op die database trouwens ook niet.

Het bredere patroon

Dit is dezelfde logica waar de Postgres-documentatie naar hint wanneer hij waarschuwt voor VACUUM-kosten op grote tables. MVCC is een prachtig concurrency-model en een meedogenloos cleanup-model. De truc is om opruimen nooit op rij-granulariteit te laten gebeuren als dat te vermijden valt. Partities maken van opruimen een filesysteem-operatie. Indexen op de parent table worden lokaal per child, dus blijven ze klein en snel. Back-ups kunnen koude partities overslaan. Leesqueries krijgen partition pruning erbij voor niks.

Je hebt geen event table van 41M rijen nodig om hier baat bij te hebben. Een table van 5M rijen die lineair groeit, knalt over achttien maanden tegen dezelfde muur. Partitioneer vroeg.

Waar dit patroon overkill is

Partitioneren is niet gratis. Elke partitie voegt catalog-overhead toe, query planning wordt een paar milliseconden trager zodra je voorbij de tientallen partities gaat, en de applicatie moet ermee om kunnen gaan dat er onder de motorkap DDL op schema draait. Wij partitioneren niet elke table. De vuistregel: is de table grotendeels append, tijdsgeordend, en is retentie een functie van leeftijd, dan partitioneer je. Is het een langzaam muterende referentietable, een snapshot die je maandelijks vanaf nul opnieuw bouwt, of een join-target aan de verkeerde kant van een foreign key, dan weegt de overhead niet op tegen de winst. Een accounts-table van 80.000 rijen heeft dit niet nodig. De events tables, audit logs, message queues en analytische firehoses die AI-agents produceren wel, want ze delen dezelfde vorm: tijds-geïndexeerd, append-only, retentie-begrensd, en nooit het doel van een join met FK-constraints die naar binnen wijzen.

Toen wij de agent-infrastructuur bouwden voor de archiverings-service van de Zwolse consultancy, was de event table de dragende muur die wij pas zagen toen hij scheurde. Elke agent in onze AI-agents-stack krijgt vanaf dag één maandelijkse partities en een pg_partman-config mee, want de stand van de database telt net zo zwaar als de model-laag.

Wat je vandaag kunt doen

Open psql tegen je grootste schrijf-intensieve table. Draai SELECT pg_size_pretty(pg_total_relation_size('your_table')) en SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'your_table'. Is de table groter dan 10GB of zijn dode tuples meer dan 20% van de levende, dan is je volgende retentie-cron degene die pijn gaat doen. Schets de partitiegrens die jij zou trekken, en zet de migratie in de agenda voordat de table groter wordt dan de omvang waarbij het swap-venster nog comfortabel is.