B-tree vs GIN vs GiST-indexen: een praktische PostgreSQL-gids

Wat je eigenlijk kiest wanneer je een index kiest

De meeste PostgreSQL-indexproblemen beginnen op dezelfde manier: een lijstweergave voelt snel bij 1.000 rijen en wordt traag bij 1.000.000. Of een zoekveld dat in testen werkte verandert in een seconde-lange pauze in productie. Als dat gebeurt is het verleidelijk om te vragen: “Welke index is het beste?” Een betere vraag is: “Wat vraagt dit scherm de database eigenlijk te doen?”

Dezelfde tabel kan verschillende indextypes nodig hebben omdat verschillende schermen er op verschillende manieren naar lezen. Eén weergave filtert op een enkele status en sorteert op created_at. Een andere doet full-text search. Een andere controleert of een JSON-veld een sleutel bevat. Een andere vindt items in de buurt van een punt op een kaart. Dat zijn verschillende toegangspatronen, dus één indextype wint niet overal.

Dat is wat je kiest als je een index kiest: hoe de app de data benadert. Doe je voornamelijk exacte matches, ranges en sorteringen? Zoek je in documenten of arrays? Vraag je “wat is dichtbij deze locatie” of “wat overlapt dit bereik”? Het antwoord bepaalt of B-tree, GIN of GiST het beste past.

B-tree, GIN en GiST in gewone taal

Index kiezen gaat minder over het kolomtype en meer over wat je queries ermee doen. PostgreSQL kiest indexen op basis van operatoren zoals =, <, @> of @@, niet op basis van of de kolom “text” of “json” is. Daarom kan hetzelfde veld op verschillende schermen verschillende indexen nodig hebben.

B-tree: snel voor geordende opzoekingen

B-tree is de standaard en de meest voorkomende keuze. Het blinkt uit wanneer je filtert op exacte waarde, op een range, of resultaten in een specifieke volgorde nodig hebt.

Een typisch voorbeeld is een admin-lijst gefilterd op status en gesorteerd op created_at. Een B-tree-index op (status, created_at) kan zowel filter als sort ondersteunen. B-tree is ook het gebruikelijke hulpmiddel voor unique constraints.

GIN: snel wanneer elke rij veel doorzoekbare sleutels bevat

GIN is gebouwd voor “bevat deze rij deze term/waarde?”-vragen, waarbij één rij veel sleutels kan matchen. Veelvoorkomende voorbeelden zijn full-text search (een document bevat woorden) en JSONB/array membership (JSON bevat een sleutel/waarde).

Denk aan een klantrecord met een JSONB preferences object, en een scherm dat filtert op gebruikers waar preferences {\"newsletter\": true} bevat. Dat is een GIN-stijl lookup.

GiST: flexibel voor ranges, geo en similariteit

GiST is een algemeen raamwerk dat gebruikt wordt voor datatypes die niet in eenvoudige ordening passen. Het is een natuurlijke match voor ranges (overlap, contains), geometrische en geografische queries (dichtbij, binnen), en sommige similarity-zoekopdrachten.

Wanneer je kiest tussen B-tree vs GIN vs GiST, begin met het opschrijven van de operatoren die je drukste schermen gebruiken. De juiste index wordt dan meestal duidelijker.

Beslissingstabel voor veelvoorkomende schermen (filters, zoeken, JSON, geo)

De meeste apps hebben maar een paar indexpatronen nodig. De truc is het gedrag van het scherm te matchen met de operatoren die je queries gebruiken.

Twee indexen kunnen naast elkaar bestaan als eindpunten verschillen. Bijvoorbeeld: een admin-lijst kan een B-tree op (tenant_id, created_at) nodig hebben voor snel sorteren, terwijl een zoekpagina een GIN-index nodig heeft voor @@. Houd beide alleen als beide queryvormen vaak voorkomen.

Als je het niet zeker weet, kijk eerst naar de operator. Indexen helpen wanneer de database ze kan gebruiken om grote delen van de tabel over te slaan.

Filters en sorteren: waar B-tree meestal wint

Voor de meeste alledaagse schermen is B-tree de saaie keuze die werkt. Als je query eruitziet als “pak rijen waar een kolom gelijk is aan een waarde, misschien sorteer ze, en toon pagina 1,” dan is B-tree meestal het eerste wat je probeert.

Gelijkheidfilters zijn het klassieke geval. Kolommen zoals status, user_id, account_id, type of tenant_id verschijnen constant in dashboards en adminpanelen. Een B-tree-index kan direct naar matching waarden springen.

Range-filters passen ook goed bij B-tree. Wanneer je filtert op tijd of numerieke ranges helpt de geordende structuur: created_at >= ..., price BETWEEN ..., id > .... Als je UI “Laatste 7 dagen” of “€50 tot €100” aanbiedt, doet B-tree precies wat je nodig hebt.

Sorteren en paginering besparen vaak het meest werk met B-tree. Als de indexvolgorde overeenkomt met je ORDER BY, kan PostgreSQL vaak rijen al gesorteerd teruggeven in plaats van een grote set in geheugen te sorteren.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Samengestelde indexen volgen één eenvoudige regel: PostgreSQL kan alleen het linker- of voorste deel van de index efficiënt gebruiken. Denk “van links naar rechts.” Met (user_id, status, created_at) profiteren queries die filteren op user_id (en optioneel status). Een query die alleen op status filtert zal meestal niet profiteren.

Partial indexes zijn een sterke verbetering wanneer je scherm maar naar een deel van de data kijkt. Veelvoorkomende slices zijn “alleen actieve rijen”, “niet soft-deleted”, of “recente activiteit”. Ze houden de index kleiner en sneller.

High-cardinality kolommen en de kosten van extra indexen

High-cardinality kolommen hebben veel unieke waarden, zoals user_id, order_id, email of created_at tot op de seconde. Indexen schitteren hier vaak omdat een filter snel naar een klein deel van de tabel brengt.

Low-cardinality kolommen zijn het tegenovergestelde: booleans en kleine enums zoals is_active, status IN ('open','closed') of plan IN ('free','pro'). Een index op deze kolommen teleurstelt vaak omdat elke waarde veel rijen matcht. PostgreSQL kiest dan soms terecht een sequential scan omdat het gebruik van de index nog steeds veel tabelpagina's moet lezen.

Een subtiele extra kost is het ophalen van rijen. Zelfs als een index snel matching IDs vindt, moet de database mogelijk toch de tabel bezoeken voor de overige kolommen. Als je query maar een paar velden nodig heeft, kan een covering index helpen, maar die maakt de index ook groter en duurder om te onderhouden.

Elke extra index heeft een prijs bij schrijven. Inserts moeten in elke index geschreven worden. Updates die geïndexeerde kolommen veranderen moeten ook die index-entries bijwerken. Indexen “voor het geval” toevoegen kan de hele app vertragen, niet alleen één scherm.

Praktische richtlijnen:

• Begin met 1–2 werkpaard-indexen per drukke tabel, gebaseerd op echte filters en sorteringen.
• Geef de voorkeur aan high-cardinality kolommen die in WHERE en ORDER BY gebruikt worden.
• Wees voorzichtig met indexeren van booleans en kleine enums tenzij ze gecombineerd worden met een andere selectieve kolom.
• Voeg een nieuwe index alleen toe nadat je de exacte query kunt noemen die erdoor versneld wordt.

Voorbeeld: een support-ticketlijst gefilterd op assignee_id (high-cardinality) profiteert van een index, terwijl is_archived = false op zichzelf vaak niet veel uitricht.

Zoekschermen: full-text, prefixes en "contains"

Zoekvelden lijken eenvoudig, maar gebruikers verwachten veel: meerdere woorden, verschillende woordvormen en redelijke ranking. In PostgreSQL is dat meestal full-text search: je slaat een tsvector (voorbewerkte tekst) op en vraagt er een tsquery op (wat de gebruiker typt, geparsed naar termen).

Voor full-text search is GIN de gebruikelijke standaard omdat het snel antwoordt op “bevat dit document deze termen?” over veel rijen. De trade-off is zwaardere schrijfbewerkingen: inserts en updates kosten meer.

GiST kan ook werken voor full-text search. Het is vaak kleiner en goedkoper om bij te werken, maar meestal langzamer voor reads dan GIN. Als je data constant verandert (bijv. event-achtige tabellen) kan die read-write balans belangrijk zijn.

Prefix-zoeken is geen full-text

Prefix-zoeken betekent “begint met”, zoals zoeken klanten op een e-mailprefix. Dat is niet waar full-text voor gemaakt is. Voor prefixpatronen kan een B-tree index helpen (vaak met de juiste operator class) omdat het aansluit bij hoe strings geordend zijn.

Voor “contains”-zoekopdrachten zoals ILIKE '%error%' kan B-tree meestal niet helpen. Daar komt trigram-indexering of een andere zoekaanpak om de hoek kijken.

Wanneer gebruikers filters én tekstzoek willen

De meeste echte schermen combineren zoeken met filters: status, assignee, datumbereik, tenant, enz. Een praktische opzet is:

• Een GIN (of soms GiST) index voor de tsvector-kolom.
• B-tree indexen voor de meest selectieve filters (bijvoorbeeld account_id, status, created_at).
• Een strikte “houd het minimaal” regel, omdat te veel indexen schrijfbewerkingen langzamer maken.

Voorbeeld: een supportticketscherm dat zoekt “refund delayed” en filtert op status = 'open' en een specifieke account_id. Full-text geeft je relevante rijen, terwijl B-tree PostgreSQL helpt snel naar de juiste account en status te gaan.

JSONB-velden: kiezen tussen GIN en gerichte B-tree indexen

JSONB is geweldig voor flexibele data, maar het kan tot trage queries leiden als je het als gewone kolom behandelt. De kernbeslissing is simpel: zoek je “overal in deze JSON”, of filter je op een paar specifieke paths heel vaak?

Voor containment-queries zoals metadata @> '{"plan":"pro"}' is een GIN-index meestal de eerste keuze. Hij is gemaakt voor “bevat dit document deze vorm?” en ondersteunt ook key-existence checks zoals ?, ?| en ?&.

Als je app vooral op één of twee JSON-velden filtert, is een gerichte B-tree expression-index vaak sneller en kleiner. Het helpt ook wanneer je wilt sorteren of numerieke vergelijkingen doet op geëxtraheerde waarden.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Een goede vuistregel:

• Gebruik GIN wanneer gebruikers meerdere sleutels, tags of geneste structuren doorzoeken.
• Gebruik B-tree expressie-indexen wanneer gebruikers herhaaldelijk filteren op specifieke paden.
• Indexeer wat op echte schermen voorkomt, niet alles.
• Als performance afhankelijk is van een paar JSON-sleutels die je altijd gebruikt, overweeg ze tot echte kolommen te promoveren.

Voorbeeld: een supportscherm filtert tickets op metadata->>'priority' en sorteert op created_at. Indexeer het JSON-prioritypad en de normale created_at-kolom. Sla een brede GIN-index over tenzij gebruikers ook tags of geneste attributen doorzoeken.

Geo- en range-queries: waar GiST het beste past

Geo- en range-schermen zijn waar GiST vaak de voor de hand liggende keuze wordt. GiST is gebouwd voor “overlap, bevat of zit dichtbij” in plaats van “is deze waarde gelijk aan die waarde?”.

Geo-data betekent meestal punten (een winkel), lijnen (een route) of polygonen (een bezorggebied). Veelvoorkomende schermen: “winkels in mijn buurt”, “banen binnen 10 km”, “toon items binnen dit kaartvak” of “ligt dit adres binnen ons servicegebied?” Een GiST-index (meestal via PostGIS geometry of geography types) versnelt deze ruimtelijke operatoren zodat de database de meeste rijen kan overslaan in plaats van elke shape te controleren.

Ranges zijn vergelijkbaar. PostgreSQL heeft range-typen zoals daterange en int4range, en de typische vraag is overlap: “botst deze boeking met een bestaande?” of “toon abonnementen actief tijdens deze week.” GiST ondersteunt overlap- en containment-operatoren efficiënt, daarom zie je het vaak in kalenders, planning en beschikbaarheidschecks.

B-tree kan nog steeds een rol spelen op geo-achtige schermen. Veel pagina's filteren eerst op tenant, status of tijd en passen dan een ruimtelijke conditie toe en sorteren. Bijvoorbeeld: “alleen leveringen van mijn bedrijf, van de laatste 7 dagen, dichtstbij eerst.” GiST handelt het ruimtelijke deel, maar B-tree helpt bij selectieve filters en sortering.

Hoe kies je stap voor stap een index

Indexkeuze gaat vooral over de operator, niet over de kolomnaam. Dezelfde kolom kan verschillende indexen nodig hebben afhankelijk van of je =, >, LIKE 'prefix%', full-text, JSON-containment of geo-afstand gebruikt.

Lees de query als een checklist: WHERE bepaalt welke rijen kwalificeren, JOIN bepaalt hoe tabellen verbonden zijn, ORDER BY bepaalt de uitvoerorde en LIMIT bepaalt hoeveel rijen je echt nodig hebt. De beste index is vaak degene die helpt om de eerste 20 rijen snel te vinden.

Een simpel proces dat voor de meeste appschermen werkt:

1. Schrijf de exacte operatoren op die je scherm gebruikt (bijv.: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Begin met een index die past bij het meest selectieve filter of de default sort. Als het scherm sorteert op created_at DESC, begin daar.
3. Voeg een samengestelde index alleen toe wanneer je steeds dezelfde filters samen ziet. Zet gelijkheidskolommen eerst, dan range-kolommen, dan de sort-key.
4. Gebruik een partial index wanneer je altijd naar een subset filtert (bijv.: alleen status = 'open'). Gebruik een expressie-index wanneer je een berekende waarde queryt (bijv.: lower(email) voor case-insensitive lookups).
5. Valideer met EXPLAIN ANALYZE. Houd het als het de uitvoeringstijd en gelezen rijen sterk reduceert.

Concreet voorbeeld: een supportdashboard filtert tickets op status en sorteert op nieuwste. Een B-tree op (status, created_at DESC) is een sterke eerste poging. Als hetzelfde scherm ook filtert op een JSONB-flag zoals meta @> '{"vip": true}', is dat een andere operator en heeft meestal een aparte JSON-gerichte index nodig.

Veelgemaakte fouten die tijd verspillen (en schrijfbewerkingen vertragen)

Een veelvoorkomende oorzaak van teleurstelling is het kiezen van het “juiste” indextype voor de verkeerde operator. PostgreSQL kan een index alleen gebruiken wanneer de query overeenkomt met waar de index voor is gebouwd. Als je app ILIKE '%term%' gebruikt, zal een gewone B-tree op die tekstkolom ongebruikt blijven en nog steeds de tabel scannen.

Een andere valkuil is het bouwen van gigantische multi-column indexen “voor het geval dat”. Ze lijken veilig, maar zijn duur om te onderhouden en passen vaak niet op echte querypatronen. Als de meest linkse kolommen niet in het filter gebruikt worden, helpt de rest van de index misschien niet.

Low-selectivity kolommen zijn ook gemakkelijk te over-indexeren. Een B-tree op een boolean zoals is_active of een status met weinig waarden kan bijna nutteloos zijn tenzij je er een partial index van maakt die de exacte slice dekt die je queryt.

JSONB brengt zijn eigen valkuilen. Een brede GIN-index kan geweldig zijn voor flexibele filters, maar veel JSONB-path checks zijn sneller met een expressie-index op de geëxtraheerde waarde. Als je scherm altijd filtert op payload->>'customer_id', is het indexeren van die expressie vaak kleiner en sneller dan het indexeren van het hele document.

Tot slot kost elke extra index bij veel schrijfactiviteit. Op vaak geüpdate tabellen (denk tickets of orders) moet elke insert en update elke index bijwerken.

Voordat je een index toevoegt, pauzeer en controleer:

• Past de index bij de exacte operator die je query gebruikt?
• Kun je een brede multi-column index vervangen door één of twee gerichte indexen?
• Moet dit een partial index zijn om lage selectiviteit te vermijden?
• Voor JSONB: zou een expressie-index het scherm beter passen?
• Is de tabel schrijfintensief genoeg dat de indexkosten de leeswinst niet waard zijn?

Snelle checks voordat je een index toevoegt (of behoudt)

Voordat je een nieuwe index aanmaakt, wees specifiek over wat de app daadwerkelijk doet. Een “nice to have”-index verandert vaak in tragere schrijfbewerkingen en meer opslag met weinig voordeel.

Begin met je top drie schermen (of API-eindpunten) en schrijf de exacte queryvorm op: filters, sort order en wat de gebruiker invoert. Veel “indexproblemen” zijn eigenlijk “onduidelijke query-problemen”, vooral wanneer mensen B-tree vs GIN vs GiST bespreken zonder de operator te noemen.

Een simpele checklist:

• Kies 3 echte schermen en noteer hun exacte WHERE en ORDER BY patronen (inclusief richting en NULL-afhandeling).
• Bevestig het type operator: gelijkheid (=), range (>, BETWEEN), prefix, contains, overlap of afstand.
• Kies één index per veelvoorkomend schermpatroon, test en behoud alleen wat meetbaar tijd of gelezen rijen reduceert.
• Als de tabel schrijfintensief is, wees streng: extra indexen vermenigvuldigen schrijfkosten en kunnen vacuum-druk geven.
• Herbekijk na featurewijzigingen. Een nieuwe filter, een nieuwe default sort of het veranderen van “starts with” naar “contains” kan een oude index irrelevant maken.

Voorbeeld: een dashboard voegt een nieuwe default sort last_activity DESC toe. Als je alleen status had geïndexeerd, kan de filter nog steeds snel zijn, maar de sort kan nu extra werk forceren.

Voorbeeld: echte appschermen mappen naar de juiste index

Een beslissingstabel helpt alleen als je het kunt mappen naar echte schermen die je uitrolt. Hier zijn drie veelvoorkomende schermen en hoe ze meestal bij indexkeuzes passen.

Een praktische manier om dit toe te passen is te beginnen bij de UI:

• Som elke controle op die resultaten verkleint (filters).
• Noteer de default sortorde.
• Wees specifiek over zoekgedrag (full-text vs starts-with vs contains).
• Benoem “speciale” velden (JSONB, geo, ranges).

Volgende stappen: maak indexering onderdeel van je buildproces

Goede indexen volgen je schermen: de filters die mensen klikken, de sortorde die ze verwachten en het zoekveld dat ze echt gebruiken. Behandel indexering als een gewoonte tijdens ontwikkeling en je voorkomt de meeste prestatieverrassingen later.

Houd het herhaalbaar: identificeer de 1–3 queries die een scherm draait, voeg de kleinste index toe die erbij past, test met realistische data en verwijder wat zijn gewicht niet draagt.

Als je een intern hulpmiddel of klantenportaal bouwt, plan indexbehoeften vroeg omdat deze apps vaak groeien door meer filters en meer lijstschermen toe te voegen. Als je bouwt met AppMaster (appmaster.io), helpt het om elke schermfilter- en sortconfiguratie als een concreet querypatroon te behandelen en alleen indexen toe te voegen die bij die echte klikken passen.