Postgres-audit: wat we checken voor een analytics-agent

De offerte zegt "analytics-agent: haalt metrics uit je Postgres, post een dagelijkse digest in de Slack van de founder." Voor we het bedrag versturen, ssh'en we naar een read replica en draaien zes queries. Dat duurt ongeveer twintig minuten. Meestal vertellen ze ons dat de agent prima zal werken. Bij ruwweg één op de vier klanten vertellen ze ons dat we een feature zouden factureren bovenop een database die al twee jaar stilletjes kapot is.

Dit is de checklist. Ben je founder of ops lead bij een SaaS onder de €5M, en staat iemand op het punt een agent op je Postgres aan te sluiten, dan kun je deze zelf draaien voor je iets tekent. Niets ervan vraagt om application-code. Alles is read-only.

Hoe "kapot" er van buiten uitziet

De agent die we voor een typische analytics-vraag opleveren, doet drie dingen: hij draait een handvol aggregate queries op een schema, hij materialiseert resultaten in een kleine reporting-tabel, en hij post een samenvatting ergens waar een mens 'm leest. De queries zelf zijn meestal niet verrassend. Counts, sums, joins over twee of drie tabellen, een window function of twee.

De agent is goedkoop. De database eronder is de variabele. We hebben dezelfde SQL bij de ene klant in 80 ms zien draaien en bij de andere in 14 seconden, op tabellen van vergelijkbare omvang. Die 14 seconden was geen query-probleem. Het was een onderhoudsprobleem dat het team had geërfd en waarvan ze nooit te horen hadden gekregen dat ze ernaar moesten kijken.

Dus we doen een audit. Zes queries, één psql-sessie, een half uur. De lijst hieronder is wat we daadwerkelijk draaien.

Table bloat, de eerste verrassing

Postgres gebruikt MVCC, wat betekent dat een UPDATE een rij niet overschrijft. Hij schrijft een nieuwe versie en markeert de oude als dood. Autovacuum hoort die dode rijen op te ruimen. Als hij het tempo niet bij kan houden, of nooit goed is afgesteld, groeien tabellen vol spookdata. Sequential scans worden trager. Index scans worden trager. Back-ups zwellen op. Niemand merkt het, want de applicatie werkt nog steeds.

Draai dit:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

Staan bovenaan tabellen boven de 30% dode tuples, dan erft de agent die rem elke keer dat hij die tabellen scant. We hebben een events-tabel van 9 GB gezien waarvan 6 GB dood was. De fix is niet exotisch. Een VACUUM (VERBOSE, ANALYZE) op de ergste gevallen, daarna een gesprek over waarom autovacuum niet agressief genoeg meer was. Dat brengt ons bij:

Autovacuum op instellingen die niemand heeft afgesteld

Postgres komt out-of-the-box met autovacuum_vacuum_scale_factor op 0,2. Vertaling: autovacuum slaat aan zodra 20% van de rijen in een tabel is gewijzigd. Op een users-tabel van 50.000 rijen is dat 10.000 rijen aan churn, prima. Op een events-tabel van 200 miljoen rijen is dat 40 miljoen rijen aan churn voor vacuum één keer draait. Tegen die tijd zijn de index pages een puinhoop.

Check de cluster-instellingen, en check daarna welke tabellen ze overschrijven:

SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_cost_limit;

SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL;

De eerste vier laten de cluster-brede defaults zien. De laatste laat zien welke tabellen per-table overrides hebben. Bij drie van de vier databases die we auditen, geeft die laatste query niets terug. Dat betekent dat elke tabel op dezelfde default staat die in 2009 redelijk was.

De fix op een hot table doe je per tabel:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Dat is "vacuum zodra 2% van de rijen is gewijzigd", wat op een tabel van 200M rijen elke 4M rijen aan churn betekent in plaats van elke 40M. De officiële Postgres-documentatie over routine vacuuming is de referentie. Hij is lang, maar het stuk over per-table overrides lees je als eerste.

De foreign keys die er niet zijn

Dit is de klassieker waar applicatie-teams door verrast worden. Ze hebben het schema vijf jaar geleden geschreven, de ORM dwong de relatie op applicatie-niveau af, en op een gegeven moment heeft een migratie een kolom organization_id toegevoegd zonder foreign key-constraint. De data is consistent genoeg. Tot ze het niet is.

Voor een analytics-agent zijn missende FKs om twee redenen belangrijk. Ten eerste gebruikt de planner ze. Postgres kan joins overslaan, cardinality afleiden en betere plannen kiezen als hij weet dat twee kolommen aan elkaar gekoppeld zijn. Ten tweede vertekenen losse rijen je aggregaten. Verwijst 0,3% van je invoices naar een verwijderde customer, dan telt je rapport "omzet per klant" stilletjes te laag uit.

Deze query vindt de verdachten:

SELECT c.table_schema, c.table_name, c.column_name
FROM information_schema.columns c
WHERE c.column_name LIKE '%\_id' ESCAPE '\'
  AND c.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.key_column_usage kcu
    JOIN information_schema.table_constraints tc
      ON tc.constraint_name = kcu.constraint_name
     AND tc.table_schema    = kcu.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
      AND kcu.table_schema = c.table_schema
      AND kcu.table_name   = c.table_name
      AND kcu.column_name  = c.column_name
  )
ORDER BY c.table_schema, c.table_name;

Het is een heuristiek. Alles wat *_id heet en niet onder een FK-constraint valt. Je krijgt false positives (een stripe_payment_intent_id, een legacy_external_id). Loop de lijst door met de engineer die het schema kent. De echte gevallen zijn binnen tien minuten duidelijk.

Voor je de missende constraint toevoegt, check op orphans:

SELECT COUNT(*)
FROM invoices i
LEFT JOIN customers c ON c.id = i.customer_id
WHERE i.customer_id IS NOT NULL
  AND c.id IS NULL;

Is het aantal nul, dan kun je de FK in één transactie toevoegen. Is het dat niet, dan heb je een data-opschoning in te plannen. En dat is een nuttig moment om dat te ontdekken: voor het analytics-rapport live gaat, niet na een board-meeting waar de cijfers niet kloppen.

Verouderde planner-statistics

Het ANALYZE-commando ververst de statistics die de query planner gebruikt. Zijn de stats verouderd, dan bouwt Postgres plannen voor een tabel die al zes maanden niet meer die vorm heeft. De view pg_stat_user_tables toont last_analyze en last_autoanalyze, en op drukke tabellen met default-autovacuum-instellingen kunnen die timestamps weken oud zijn.

SELECT
  schemaname, relname,
  last_analyze, last_autoanalyze,
  n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY GREATEST(
  COALESCE(last_analyze,     '1970-01-01'::timestamp),
  COALESCE(last_autoanalyze, '1970-01-01'::timestamp)
) ASC
LIMIT 20;

De fix is meestal een one-liner: ANALYZE schema.table;. De diepere vraag is waarom autovacuum niet vaak genoeg draait, en dat is dezelfde vraag als in de autovacuum-sectie hierboven. Lees de documentatie over monitoring stats als je precies wilt begrijpen wat elke kolom betekent.

Indexen die niets indexeren

Laatste query, de minste impact, het makkelijkst om iets aan te doen. Ongebruikte indexen zijn dood gewicht. Ze nemen disk in beslag, ze vertragen writes, en ze maken elke VACUUM langer. Bij een typische audit vinden we er tussen de vier en twintig, vaak overgebleven van migraties die iemand in 2021 heeft gedaan en nooit heeft opgevolgd.

SELECT
  schemaname,
  relname     AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

De drempel idx_scan < 50 is zacht. Pas hem aan naar smaak. Alles wat al een jaar op de tabel staat en minder dan vijftig keer is gescand, is een sterke kandidaat om weg te gooien. Stem het eerst af met het team. Soms bestaat een index omdat hij een UNIQUE-constraint backt, en dan is het aantal scans irrelevant.

Wat we voor de offerte teruggeven

Een document van twee pagina's. Pagina één: de zes queries hierboven, gedraaid op de database van de klant, met de echte cijfers samengevat. Pagina twee: welke bevindingen blocker zijn ("we kunnen de agent niet betrouwbaar draaien tot X"), welke aanbevolen fixes binnen het project vallen, en welke buiten scope blijven maar goed zijn om te weten.

In ongeveer een kwart van de gevallen verandert de audit de vorm van de deal. De analytics-agent krijgt een kleinere scope. Er komt een database-hygiëne-traject van twee weken voor. Of de klant besluit het opruimen eerst zelf te doen en over een maand terug te komen. Elke van die uitkomsten is beter dan dezelfde problemen pas drie weken in de bouw ontdekken.

Toen we eerder dit jaar de daily-digest-agent bouwden voor een logistiek SaaS-bedrijf in Rotterdam, vond de audit 6 GB aan bloat op hun shipments-tabel en drie missende FKs op de tabel waar de digest tegenaan moest joinen. We hebben het opruimen in dezelfde opdrachtbevestiging meegenomen als de bouw van de AI-agent, en de digest draaide in 1,2 seconde in plaats van de 11 waarmee we anders live waren gegaan.

Het kleinste dat je vandaag kunt doen: open psql tegen een replica, plak de bloat-query bovenaan deze post erin, en lees de bovenste vijf rijen. Staat er iets boven de 30%, dan heb je een gesprek in te plannen voor je er iets nieuws aan koppelt.