Index in PostgreSQL: versnellen zonder te vertragen

Een index verandert dat. In plaats van alles te lezen, gebruikt PostgreSQL de index om direct naar de relevante rijen te springen. De database doorzoekt de indexstructuur — meestal een B-tree — in een paar stappen in plaats van de hele tabel.

Maar een index is geen gratis versnelling. Elke keer dat je gegevens invoegt, bijwerkt of verwijdert, moet PostgreSQL de index ook bijwerken. Hoe meer indexen je aanmaakt, hoe meer schrijfoverhead je creëert. Een tabel met tien indexen en veel `INSERT`- en `UPDATE`-verkeer kan trager worden dan zonder indexen. De officiële documentatie waarschuwt daar expliciet voor.

De kunst is niet om alles te indexeren, maar om te indexeren wat je query's daadwerkelijk filteren. Een index op een kolom die nooit in een `WHERE`-clausule verschijnt, kost alleen maar ruimte en schrijftijd.

Indextypen in PostgreSQL

PostgreSQL ondersteunt meerdere indextypen, elk met een eigen interne structuur en gebruiksscenario. De keuze bepaalt welke operatoren de index kan versnellen en hoeveel ruimte hij inneemt.

B-tree — de standaardkeuze

De B-tree (Balanced Tree) is het meest gebruikte type en de standaard wanneer je geen type opgeeft. Hij ondersteunt vergelijkingen met `<`, `<=`, `=`, `>=` en `>`. Dat maakt hem geschikt voor numerieke en tekstuele gegevens waarop je bereik- of gelijkheidsquery's uitvoert.

CREATE INDEX idx_employees_name ON employees (name);

Deze query maakt een B-tree-index op de kolom `name` van de tabel `employees`. PostgreSQL gebruikt deze index voor query's als `WHERE name = 'Jansen'` of `WHERE name > 'K'`.

Hash — snel maar beperkt

De Hash-index ondersteunt uitsluitend gelijkheidsvergelijkingen (`=`). Voor exacte matches is hij sneller dan een B-tree, maar hij kan geen bereikquery's afhandelen. Bovendien was de Hash-index in oudere versies niet crashbestendig; dat is sinds PostgreSQL 10 verbeterd, maar de beperking tot `=`-operaties blijft.

CREATE INDEX idx_users_email_hash ON users USING hash (email);

GiST en SP-GiST — voor complexe gegevens

GiST (Generalized Search Tree) biedt een flexibele infrastructuur voor het indexeren van niet-traditionele data zoals geometrische objecten, tekst of documenten. SP-GiST (Space-Partitioned GiST) is een variant voor gegevens die je kunt onderverdelen in niet-balancerende ruimtes, zoals prefix-bomen of radix-bomen.

Deze typen zijn relevant wanneer je werkt met PostGIS of andere extensies die ruimtelijke query's mogelijk maken. Voor gewone relationele data heb je ze zelden nodig.

GIN — voor verzamelingen en full-text

GIN (Generalized Inverted Index) gebruik je voor arrays, JSON en full-text zoekopdrachten. Hij indexeert niet de waarde zelf, maar de elementen binnen een verzameling. Een query die zoekt op een specifiek element in een JSON-veld of een woord in een document kan daardoor gebruikmaken van de index.

CREATE INDEX idx_documents_content ON documents USING gin (content);

BRIN — compact voor grote tabellen

BRIN (Block Range INdex) is bedoeld voor zeer grote tabellen waarbij gegevens in een logische volgorde zijn opgeslagen. In plaats van per rij indexeert BRIN per blok van meerdere rijen. Dat neemt veel minder ruimte in dan een B-tree en is efficiënt voor range query's op grote datasets die fysiek gesorteerd zijn.

CREATE INDEX idx_orders_created_at_brin ON orders USING brin (created_at);

Deze index werkt goed als de `created_at`-waarden in de tabel ongeveer in oplopende volgorde staan. Staat de data door elkaar, dan moet BRIN te veel blokken lezen en verlies je het voordeel.

Bloom — via extensie

Via de `bloom`-extensie kun je een Bloom-filterindex aanmaken. Die controleert snel of een waarde mogelijk in een set zit — met een kleine kans op vals-positieve resultaten. PostgreSQL moet na een positieve check de echte rijen nog controleren, maar kan veel rijen snel uitsluiten.

CREATE EXTENSION bloom;
CREATE INDEX idx_orders_bloom ON orders USING bloom (customer_id, order_date, status);

Dit type is nuttig bij query's die filteren op meerdere kolommen tegelijk, waar een enkele B-tree-index niet helpt.

Een index aanmaken

Het aanmaken gebeurt met `CREATE INDEX`. De keuze van welke kolommen je indexeert, welk type je gebruikt en of de index uniek moet zijn, vereist overweging.

Voor samengestelde query's maak je een samengestelde index over meerdere kolommen:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Deze index helpt bij query's die filteren op beide velden, zoals `WHERE customer_id = 42 AND order_date > '2026-01-01'`. Let op: de volgorde van de kolommen in de index moet overeenkomen met de volgorde waarin de query filtert. Een index op `(customer_id, order_date)` helpt niet bij een query die alleen op `order_date` filtert.

Wil je een function-based index omdat je query een functie toepast op de kolom:

CREATE INDEX idx_employees_upper_name ON employees (UPPER(name));

Zonder deze index kan PostgreSQL geen gebruik maken van een gewone index op `name` wanneer je query `WHERE UPPER(name) = 'JAN'` bevat. De database moet dan alsnog de hele tabel scannen.

Een index verwijderen

Een index verwijderen doe je met `DROP INDEX`. Dat is handig wanneer een index niet langer nodig is, slecht presteert of te veel schrijfoverhead veroorzaakt. Het verwijderen heeft geen invloed op de gegevens zelf — alleen op de snelheid van bepaalde query's.

DROP INDEX idx_employees_name;

Deze operatie is permanent. Wil je de index later weer gebruiken, dan moet je hem opnieuw aanmaken.

Onderhoud van indexen

Indexen kunnen na verloop van tijd fragmenteren of zelfs corrupt raken. Twee hulpprogramma's helpen daarbij.

`reindexdb` is een command-line tool die indexen opnieuw opbouwt. Dat kan nodig zijn als een index corrupt is geraakt of als de prestaties zijn verslechterd door veelvuldige wijzigingen in de onderliggende tabel.

reindexdb -d mijn_database -t employees

Dit commando herbouwt alle indexen op de tabel `employees` in de database `mijn_database`. Binnen de database zelf kun je hetzelfde doen met het SQL-commando `REINDEX`:

REINDEX TABLE employees;

`pg_amcheck` detecteert corruptie in indexen en andere databaseobjecten, met name in B-tree-indexen. Draai dit gereedschap voordat je een `REINDEX` uitvoert — dan weet je of herbouwen daadwerkelijk nodig is:

pg_amcheck -d mijn_database

Verificatie

Controleer of een index daadwerkelijk wordt gebruikt met `EXPLAIN`:

EXPLAIN SELECT * FROM employees WHERE name = 'Jansen';

In de output zie je of PostgreSQL een `Index Scan` gebruikt of een `Seq Scan`. Een `Seq Scan` betekent dat de index niet wordt gebruikt — controleer dan of de queryvorm overeenkomt met de indexstructuur.

Om te zien welke indexen op een tabel bestaan:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

Veelgestelde problemen

PostgreSQL gebruikt mijn index niet. De queryvorm moet overeenkomen met de index. Een query met `WHERE UPPER(name) = 'JAN'` gebruikt geen gewone index op `name` — daarvoor heb je een function-based index nodig. Ook een query die filtert op een kolom die niet de eerste kolom van een samengestelde index is, kan de index niet gebruiken.

Mijn schrijfprestaties zijn verslechterd na het toevoegen van indexen. Elke index moet worden bijgewerkt bij `INSERT`, `UPDATE` en `DELETE`. Verwijder indexen die geen query's versnellen, of overweeg een BRIN-index als je met zeer grote tabellen werkt en schrijfprestaties belangrijker zijn dan leessnelheid.

Een index is corrupt. Draai `pg_amcheck` om de corruptie te bevestigen en gebruik daarna `REINDEX` of `reindexdb` om de index opnieuw op te bouwen. Controleer daarna of de corruptie terugkeert — zo ja, dan wijst dat op een probleem met de hardware of het opslagsysteem.

Indexen declaratief beheren met SQLAlchemy

Binnen het ecosysteem van ORM-tools houdt SQLAlchemy 2.0 rekening met PostgreSQL-specifieke indexfunctionaliteit. Via de Schema Definition Language kun je indexen declaratief definiëren in je applicatiecode, zonder rechtstreeks SQL te schrijven:

from sqlalchemy import Column, Integer, String, Index
from sqlalchemy.orm import declarative_base</p><p>Base = declarative_base()</p><p>class Employee(Base):
    __tablename__ = 'employees'
    __table_args__ = (
        Index('idx_employees_name', 'name'),
    )</p><p>id = Column(Integer, primary_key=True)
    name = Column(String)

SQLAlchemy vertaalt deze definitie naar de juiste DDL (Data Definition Language) wanneer je de tabellen aanmaakt. Dit is handig wanneer je indexen onder versiebeheer wilt houden samen met je schema. Het kost wel een abstractielaag: je moet SQLAlchemy's manier van werken kennen om geavanceerde opties zoals `USING gin` of function-based indexen te configureren.

De juiste index is geen kwestie van aanmaken en vergeten. Query-patroon, datavolgorde en schrijfvolume bepalen of een index na een jaar nog steeds de moeite waard is — en alleen `EXPLAIN` vertelt je dat eerlijk.