Postgres indexes voor multi-tenant SaaS: de korte lijst

Het is dinsdag, 23:14. De slow query log op de staging replica heeft tachtig rijen op één enkele SELECT tegen de events-tabel. De query is prima. Het plan is een sequential scan van 4,6 miljoen rijen, omdat iemand zes weken geleden een partial index heeft uitgerold die de planner niet wil gebruiken. Je tenants merken het. Ze sturen Slack-berichten met het woord "weer" erin.

Dit is de cheatsheet die ik graag had gehad toen we voor het eerst een multi-tenant Postgres database op echte schaal draaiden. Met "echte schaal" bedoel ik het saaie middensegment: 50 tot 500 tenants, 100k tot 500k rijen per tenant op de hoofdtabellen, gemengde OLTP plus een paar reporting queries die binnen twee seconden terug moeten komen. Eén database, één schema, tenant_id op elke rij. De versie die de meesten van jullie ook echt draaien.

Het werkpaard: composite B-tree die begint met tenant_id

Als je één ding onthoudt uit deze post, onthoud dan dit. In een multi-tenant database met tenant_id op elke rij moet de eerste kolom van elke functionele index tenant_id zijn. Niet "meestal moet". Móet.

De planner kan niet weten dat tenant 42 maar 1% van de rijen in je invoices-tabel bezit. De statistieken die hij bijhoudt zijn globaal. Een B-tree op (status, created_at) ziet er redelijk uit in EXPLAIN, totdat je hem in productie voor een echte tenant draait en Postgres besluit dat een bitmap scan plus een filter goedkoper is dan de index walk. Dat is hij niet.

CREATE INDEX invoices_tenant_status_created_idx
  ON invoices (tenant_id, status, created_at DESC);

Deze ene index bedient drie query-vormen tegelijk: lijst de invoices van een tenant, lijst de invoices van een tenant op status, en lijst de meest recente invoices van een tenant. Postgres kan een prefix van een B-tree gebruiken, dus alle drie de plannen komen schoon uit.

Partial indexes voor de hete 5%

De meeste SaaS-tabellen hebben een kleine hete subset (actieve jobs, openstaande taken, ongelezen notificaties) en een grote koude staart. Een partial index is de goedkoopste, snelste manier om die hete subset te laten vliegen.

CREATE INDEX jobs_active_idx
  ON jobs (tenant_id, scheduled_for)
  WHERE status = 'active';

De afweging is dat het predicaat exact moet matchen met de query. WHERE status = 'active' op de index wordt niet gebruikt als jouw query WHERE status IN ('active', 'queued') luidt. De planner is voorzichtig met partial-index matching en zal niet "bijna matchen". Krijg je het predicaat verkeerd, dan heb je een nutteloze index die niemand gebruikt, terwijl Postgres hem nog wel bij elke write moet bijhouden.

Covering indexes met INCLUDE voor lees-zware paden

Postgres 11 voegde INCLUDE toe, waarmee je non-key kolommen aan een B-tree kunt plakken zodat de planner een index-only scan kan doen. Voor multi-tenant SaaS telt dit het meest op de tabel die door het dashboard bij elke pageload wordt geraakt.

CREATE INDEX projects_tenant_idx
  ON projects (tenant_id, archived_at)
  INCLUDE (name, owner_id, updated_at);

Nu raakt de dashboard-query (lijst project-naam, eigenaar, laatste update voor één tenant) de heap niet meer aan. Op een projects-tabel met 200k rijen per tenant hebben we dit van een 28ms-query naar 4ms zien gaan. De kostprijs is index-grootte: die INCLUDE-clausule maakt de index ongeveer twee keer zo groot. Waard voor het leespad. Niet waard voor tabellen waar je constant naartoe schrijft.

BRIN voor append-only tijdlijnen

BRIN (Block Range INdex) is het indextype dat de meeste SaaS-teams onderbenutten. Heb je een tabel die alleen groeit door append (events, audit_logs, webhook_deliveries) en queryt je er bijna uitsluitend op tijdsbereik plus tenant, dan is een BRIN een tiende van de grootte van een B-tree en net zo snel voor het toegangspatroon dat je werkelijk hebt.

CREATE INDEX events_brin_idx
  ON events USING BRIN (tenant_id, occurred_at)
  WITH (pages_per_range = 32);

De catch: BRIN helpt alleen als de fysieke volgorde van de rijen op disk overeenkomt met de geïndexeerde kolommen. Voor een append-only tabel is dat van nature zo (rijen komen aan in tijdsvolgorde). Als je delete en re-insert, of als je een zware backfill doet, valt de BRIN-performance om. Gebruik CLUSTER één keer na de backfill, en laat hem daarna append-only blijven. De Postgres-docs leggen dit helder uit als je het volledige plaatje wilt.

GIN voor jsonb en search

Elke SaaS-database eindigt ergens met een jsonb-kolom: custom fields, webhook payloads, feature flags. Zodra je binnen die jsonb gaat queryen (WHERE payload ->> 'event_type' = 'paid'), is de sequential scan terug. GIN is het antwoord.

CREATE INDEX events_payload_gin_idx
  ON events USING GIN (payload jsonb_path_ops);

Gebruik jsonb_path_ops, tenzij je oprecht de volledige operator class nodig hebt. Hij is twee tot drie keer zo klein en sneller voor de containment-queries (@>) die 90% van de jsonb-queries daadwerkelijk gebruiken. De jsonb-indexing docs hebben de operator-class tabel als je 'm op moet zoeken.

Hetzelfde indextype voor full-text search, maar bouw eerst een generated tsvector-kolom. Een expressie indexeren op query-tijd ondergraaft het hele punt.

Drie indextypes die we niet pakken

Hash indexes

Postgres hash indexes zijn crash-safe sinds versie 10, en mensen blijven blogposts schrijven over hoe ze "nu production-ready" zijn. Dat zijn ze. Ze zijn ook nog steeds langzamer dan een B-tree op equality lookups voor elke kolom die je werkelijk zou indexeren. Hash geeft je exact-match en niets anders: geen range, geen ORDER BY, geen prefix. Een B-tree op dezelfde kolom doet equality lookups in ongeveer dezelfde tijd en geeft je de andere operaties gratis. We hebben nooit een query gemeten waar hash won.

Bloom indexes

De bloom-extensie ziet er op papier magisch uit: index N kolommen tegelijk, query op elke subset. In de praktijk vreet de false-positive-rate de winst op, is de index groot, en schat de planner hem slecht in. De use case (brede tabellen met willekeurige multi-column filters en geen voor de hand liggende leidende kolom) is reëel maar zeldzaam. Als je denkt dat je een Bloom index nodig hebt, moet je waarschijnlijk eerst naar je query-patronen kijken en ontdekken dat 3 van de 12 kolommen 95% van de WHERE-clauses voor hun rekening nemen.

SP-GiST voor de dingen die B-tree al doet

SP-GiST is echt nuttig voor spatial data en sommige tekstpatronen. Het is niet nuttig voor het soort equality en range lookups waar multi-tenant SaaS-workloads hun tijd aan besteden. Als je geen geometrie indexeert of geen telefoon-prefix matching doet, wint B-tree op elke dimensie die telt.

De vijfminuten-audit

Voordat je één nieuwe index aanmaakt, draai dit op je productie-replica. Het vertelt je welke indexes niets doen en je writes kosten.

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint WHERE conindid > 0
  )
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 25;

De output is je takenlijst. Alles ouder dan twee weken met nul scans is een kandidaat voor DROP INDEX CONCURRENTLY. Alles groots met een lage idx_scan ten opzichte van het lees-verkeer van de tabel is een kandidaat voor heroverweging.

Toen we de reporting-backend bouwden voor een SaaS-klant waarvan de grootste tenant 1,4M invoices had, liepen we steeds tegen het feit aan dat elke "voor de hand liggende" index die iemand in het eerste jaar had toegevoegd, door de planner werd overgeslagen omdat tenant_id niet de leidende kolom was. We hebben de index-set in één migratie vanaf nul opnieuw opgebouwd (11 gedropt, 6 toegevoegd, p95 dashboard-query van 1,9s naar 180ms). Dat is grotendeels wat onze backend- en database-rescue opdrachten blijken te zijn.

Open één trage query van vorige week. Draai er EXPLAIN (ANALYZE, BUFFERS) op. Kijk naar de eerste sequential scan. Heeft de tabel een tenant_id en heeft de query een tenant_id-predicaat, dan weet je al welke index ontbreekt.