PostgreSQL Query-optimalisatie: Van Traag naar Sub-Milliseconde

Vorige maand kreeg ik om 2 uur 's nachts een Slack-bericht: "Het dashboard geeft timeouts." Ik SSH'te naar de productiebox, opende pg_stat_activity, en vond een enkele query die 14 miljoen rijen scande om 12 resultaten terug te geven. De oplossing was een partial index die 30 seconden kostte om aan te maken. De query ging van 4,2 seconden naar 0,3 milliseconden.

Dat is het ding met PostgreSQL-prestaties. De problemen zijn bijna nooit exotisch. Het zijn gemiste indexen, slechte statistieken, queries die logisch waren toen de tabel 10.000 rijen had maar nu heeft hij er 10 miljoen. De oplossingen zijn meestal simpel — zodra je weet waar je moet kijken.

Dit artikel is alles wat ik heb geleerd over PostgreSQL-queryoptimalisatie door het draaien van productiedatabases. Geen theorie zonder praktijk. Elke techniek hier heeft me echte tijd bespaard op echte systemen.

De Debug-Mentaliteit: Raad Niet, Meet#

De grootste fout die ik developers zie maken met trage queries is gokken. "Misschien hebben we een index nodig op die kolom." "Misschien is de JOIN traag." "Misschien moeten we meer RAM toevoegen."

Stop met gokken. PostgreSQL heeft een van de beste query-analyzers van welke database dan ook. Gebruik het.

EXPLAIN — De Blauwdruk#

Gewone EXPLAIN laat zien wat PostgreSQL van plan is te doen, zonder de query daadwerkelijk uit te voeren:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

Dit vertelt je het plan — een index scan — en de geschatte kosten. Maar het is een voorspelling. PostgreSQL kan compleet fout zitten over het aantal rijen. Ik heb schattingen van "1 rij" gezien terwijl het werkelijke resultaat 50.000 rijen was. Dat soort misschattingen werkt door in vreselijke plankeuzes.

Gebruik gewone EXPLAIN wanneer je een snelle blik op het plan wilt zonder de query daadwerkelijk uit te voeren. Dit is belangrijk wanneer je een DELETE analyseert die data zou wijzigen, of een query die 30 minuten duurt om te draaien.

EXPLAIN ANALYZE — De Waarheid#

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Nu zie je de werkelijke uitvoeringstijd en het werkelijke aantal rijen. Hier gebeurt het echte debuggen. Vergelijk rows=1 (geschat) met rows=1 (werkelijk) — ze komen overeen, dus de planner maakte een goede beslissing. Wanneer ze niet overeenkomen, is dat je eerste aanwijzing.

Waarschuwing: EXPLAIN ANALYZE voert de query daadwerkelijk uit. Als je een UPDATE of DELETE analyseert, wrap het in een transactie:

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Het Volledige Plaatje#

Dit is wat ik daadwerkelijk 90% van de tijd gebruik:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

De BUFFERS-output is cruciaal. shared hit=312 betekent dat 312 pagina's uit de buffercache (RAM) kwamen. read=45 betekent dat 45 pagina's van schijf gelezen moesten worden. Als je veel read ziet ten opzichte van hit, is je shared_buffers misschien te klein, of is de tabel te groot om in de cache te blijven.

Ik gebruik ook FORMAT JSON wanneer ik het plan moet plakken in explain.dalibo.com voor visualisatie. De boomweergave maakt complexe plannen veel makkelijker leesbaar:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

EXPLAIN-opties Die Ik Daadwerkelijk Gebruik#

Dit is de volledige diagnostische query die ik als eerste uitvoer bij elk onderzoek naar trage queries:

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

• TIMING — timing per node (standaard aan met ANALYZE, maar ik ben expliciet)
• VERBOSE — toont output-kolomlijsten en schema-gekwalificeerde tabelnamen

Query Plans Lezen als een Detective#

Elk queryplan is een boom. PostgreSQL leest het van onder naar boven: de diepst ingesprongen nodes worden als eerste uitgevoerd en voeden resultaten naar boven. De scantypen begrijpen is de basis van al het andere.

Seq Scan — De Volledige Tabelscan#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

Sequentiële scan leest elke rij in de tabel. Voor een tabel met een miljoen rijen is dat elke pagina op schijf.

Maar hier is de nuance: Seq Scan is niet altijd slecht. Als je 30% of meer van de tabel selecteert, is een sequentiële scan eigenlijk sneller dan een index scan omdat sequentiële I/O veel sneller is dan random I/O. PostgreSQL weet dit. Als het een Seq Scan kiest terwijl je een index hebt, controleer dan hoe selectief je WHERE-clausule werkelijk is.

Wanneer het een probleem is: wanneer je een klein deel van de rijen selecteert uit een grote tabel en er geen index is.

Index Scan — De Gerichte Opzoeking#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Gaat naar de B-tree index, vindt de overeenkomende entries, en haalt vervolgens de werkelijke rijen op uit de tabel-heap. Elke rij-ophaling is een random I/O-operatie naar de heap. Dit is geweldig voor zeer selectieve queries, maar elke heap-fetch heeft een prijs.

Index Only Scan — De Heilige Graal#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

Dit is het snelste scantype. PostgreSQL haalt alles wat het nodig heeft uit de index alleen, zonder de tabel-heap aan te raken. Je ziet dit wanneer alle kolommen die je SELECT en WHERE op zijn in de index zitten.

De valkuil: Heap Fetches: 0 betekent dat de visibility map actueel is. Als je tabel veel dode tuples heeft (niet recent gevacuumd), moet PostgreSQL nog steeds de heap controleren om de zichtbaarheid van rijen te verifiëren. Dit is een van de redenen waarom autovacuum belangrijk is voor prestaties, niet alleen voor schijfruimte.

Bitmap Scan — Het Midden#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap scans zijn PostgreSQL's antwoord op het "te veel rijen voor een index scan, te weinig voor een seq scan" probleem. Het bouwt een bitmap van welke pagina's overeenkomende rijen bevatten, sorteert ze op fysieke locatie en haalt ze op volgorde op. Dit converteert random I/O naar sequentiële I/O.

Je zult bitmap scans vaak zien wanneer twee of meer indexen gecombineerd worden:

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Deze BitmapAnd combineert twee afzonderlijke indexen. PostgreSQL zegt eigenlijk: "Ik heb geen samengestelde index, maar ik kan deze twee single-column indexen combineren." Het werkt, maar een goede samengestelde index zou sneller zijn.

Slechte Schattingen Herkennen#

Het belangrijkste waar ik in een queryplan naar zoek is niet-overeenkomende schattingen:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

De planner schatte 1 rij. Het werkelijke resultaat was 48.753 rijen. Dat is een misschatting van vijf ordes van grootte. De planner koos een Nested Loop omdat hij dacht dat hij tegen 1 rij joinde. Met 48.753 rijen zou een Hash Join ordes van grootte sneller zijn geweest.

Veelvoorkomende oorzaken van slechte schattingen:

• Verouderde statistieken: Voer ANALYZE uit op de tabel
• Gecorreleerde kolommen: De planner neemt aan dat kolomwaarden onafhankelijk zijn. Als status = 'shipped' en created_at > '2026-01-01' gecorreleerd zijn (de meeste recente bestellingen zijn verzonden), onderschat de planner de gecombineerde selectiviteit
• Custom functies in WHERE: De planner gebruikt een default selectiviteitsschatting (meestal 0,5% voor gelijkheid, 33% voor bereik) wanneer hij een functie niet kan analyseren
• Geparametriseerde queries met generieke plannen: Na 5 uitvoeringen kan PostgreSQL overschakelen naar een generiek plan dat geen rekening houdt met de werkelijke parameterwaarde

Wanneer je slechte schattingen ziet, is de oplossing meestal een van: ANALYZE uitvoeren, uitgebreide statistieken aanmaken, de query herschrijven, of een CTE gebruiken als optimalisatiescherm.

-- Maak uitgebreide statistieken aan voor gecorreleerde kolommen
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Indexstrategie: De Juiste Index voor de Juiste Taak#

Indexen zijn niet gratis. Elke index vertraagt schrijfoperaties, verbruikt schijfruimte en moet onderhouden worden. Het doel is niet "alles indexeren" — het is "precies indexeren wat je nodig hebt."

B-tree — Het Standaard Werkpaard#

B-tree is de default en dekt de overgrote meerderheid van gevallen. Het ondersteunt equality- en range-operators (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

CREATE INDEX idx_orders_created_at ON orders (created_at);

Kolomvolgorde in B-tree indexen is enorm belangrijk bij samengestelde indexen. De meest linkse kolom is de primaire sortering, dan de volgende, enzovoort. Een index op (a, b, c) kan efficiënt antwoord geven op:

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND b > 5
• WHERE a = 1 ORDER BY b

Het kan niet efficiënt antwoord geven op:

• WHERE b = 2 (slaat de eerste kolom over)
• WHERE c = 3 (slaat de eerste twee kolommen over)
• WHERE a = 1 AND c = 3 (gat in het midden — a-conditie wordt gebruikt, c-conditie vereist filter)

Denk eraan als een telefoonboek gesorteerd op achternaam, dan voornaam. Je kunt snel alle "Smit" entries vinden, of "Smit, Jan" specifiek. Maar je kunt niet snel alle "Jan" entries vinden zonder het hele boek te scannen.

De regel: zet equality-condities eerst, dan range-condities, dan sorteerkolommen.

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Goed: equality eerst, dan range, dan sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Slecht: range voor equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Covering Indexes met INCLUDE#

PostgreSQL 11+ laat je non-key kolommen toevoegen aan een index met INCLUDE. Deze kolommen worden opgeslagen in de leaf pages maar maken geen deel uit van de B-tree structuur. Ze maken index-only scans mogelijk zonder de boom op te blazen:

-- We voeren regelmatig uit:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Nu kan PostgreSQL de hele query uit de index alleen bedienen. Zonder INCLUDE zou het total en status van de heap moeten ophalen. Het verschil op een koude cache kan dramatisch zijn — ik heb queries van 50ms naar 0,2ms zien gaan door simpelweg INCLUDE-kolommen toe te voegen.

Partial Indexes — Index Alleen Wat Ertoe Doet#

Dit is mijn persoonlijke favoriete PostgreSQL-feature en degene die de meeste developers niet kennen.

-- Slechts 2% van bestellingen is 'pending', maar we querien ze constant
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Deze index is klein vergeleken met een volledige index op created_at. Het bevat alleen rijen waar status = 'pending'. Queries die overeenkomen met de WHERE-clausule gebruiken deze kleine, snelle index. Queries die niet overeenkomen negeren hem.

Echt voorbeeld uit productie: ik had een sessions tabel met 50 miljoen rijen. Slechts 200.000 waren actief (niet verlopen). Een volledige index op user_id was 1,2 GB. Een partial index WHERE expires_at > now() was 8 MB. De query ging van 12ms naar 0,1ms omdat de hele index in de cache paste.

-- Nog een veelvoorkomend patroon: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unieke constraint alleen op actieve records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Die laatste is ongelooflijk nuttig. Het laat je een unieke email-constraint hebben op actieve gebruikers terwijl hetzelfde emailadres in verwijderde records mag voorkomen.

GIN Indexes — Full-Text Search en JSONB#

GIN (Generalized Inverted Index) is het antwoord wanneer je binnen waarden moet zoeken — arrays, JSONB-documenten, of full-text search vectors.

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Nu is dit snel:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

GIN-indexen zijn groot en traag om te bouwen, maar razendsnel om te queryen. Ze zijn het waard voor read-heavy workloads met complexe containment queries.

Voor JSONB is er ook jsonb_path_ops dat kleiner en sneller is voor @>-containment queries, maar geen existence-operators (?, ?|, ?&) ondersteunt:

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

GiST Indexes — Geometrische en Range Types#

GiST (Generalized Search Tree) handelt overlappende datatypes af: geometrische vormen, ranges, full-text search (alternatief voor GIN, kleiner maar trager).

-- IP-range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporele ranges (boekingsconflicten)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Vind overlappende boekingen:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Hash Indexes — Niche maar Nuttig#

Hash-indexen zijn alleen nuttig voor equality-vergelijkingen. Sinds PostgreSQL 10 worden ze WAL-gelogd en zijn ze crash-safe. Ze zijn kleiner dan B-tree voor brede kolommen en iets sneller voor pure equality:

-- Goed voor equality-only lookups op grote tekstkolommen
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- Dit is snel:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- Dit KAN de hash-index NIET gebruiken:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

In de praktijk gebruik ik zelden hash-indexen. B-tree handelt equality prima af, en de onmogelijkheid om range queries of ordering te doen maakt hash-indexen te inflexibel voor de meeste echte use cases.

Wanneer Indexen Pijn Doen#

Elke index heeft een prijs:

• Write amplification: Elke INSERT werkt elke index op de tabel bij. Een tabel met 8 indexen betekent 8 extra schrijfoperaties per INSERT
• HOT updates geblokkeerd: Heap-Only Tuple (HOT) updates zijn een belangrijke optimalisatie waarbij PostgreSQL een rij kan bijwerken zonder indexen bij te werken, maar alleen als geen geïndexeerde kolom is gewijzigd. Meer indexen = meer kans op het blokkeren van HOT updates
• Vacuum overhead: Meer indexen betekent dat vacuum langer duurt
• Planner overhead: Meer indexen betekent dat de planner meer opties moet evalueren

Ik audit regelmatig indexen op productietabellen:

-- Vind ongebruikte indexen
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- sluit primary key en unique constraints uit
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Ik heb indexen van meerdere gigabytes gevonden die waren aangemaakt tijdens een eenmalige migratie en nooit meer gebruikt. Het droppen ervan versnelde writes merkbaar.

-- Vind dubbele indexen (dezelfde kolommen, dezelfde volgorde)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

Het N+1 Query Probleem#

Elke ORM-developer komt dit uiteindelijk tegen. Het is het meest voorkomende prestatieprobleem dat ik debug.

Hoe N+1 Eruitziet#

# Python / SQLAlchemy — de klassieke val
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # Dit triggert een aparte query voor ELKE bestelling
    print(f"Customer: {order.customer.name}")

Dit genereert:

-- Query 1: Haal alle lopende bestellingen op
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Haal klant op voor bestelling 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Haal klant op voor bestelling 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Haal klant op voor bestelling 500
SELECT * FROM customers WHERE id = 600;

501 queries in plaats van 1 of 2. Elke query is individueel snel, misschien 0,5ms. Maar 501 daarvan is samen 250ms aan databasetijd, plus network round-trip latency voor elke query.

N+1 Detecteren in Logs#

De snelste manier om N+1-queries te vangen is om tijdelijk statement-logging in te schakelen:

-- Log alle queries die meer dan 0ms duren (dus alle queries)
SET log_min_duration_statement = 0;
 
-- Beter voor productie: log alleen trage queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms drempel
SELECT pg_reload_conf();

Kijk dan naar de logs. N+1 is onmiskenbaar — je zult honderden identieke queries zien met verschillende parameterwaarden in snelle opeenvolging.

Een meer gerichte aanpak voor development:

-- Schakel auto_explain in voor trage queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Dit logt de volledige EXPLAIN ANALYZE output voor elke query boven de 100ms, inclusief queries binnen functies.

De Oplossing: Eager Loading of JOINs#

ORM-aanpak — vertel de ORM om gerelateerde data vooraf te laden:

# SQLAlchemy — joinedload haalt klanten op in dezelfde query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Ruwe SQL-aanpak — gebruik gewoon een JOIN:

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Eén query. Klaar.

JOINs vs Meerdere Queries#

Hier is een terechte discussie. Soms zijn twee queries beter dan een JOIN:

-- Aanpak 1: Enkele JOIN (kan dubbele data produceren bij one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Aanpak 2: Twee queries (minder datatransfer als orders veel kolommen hebben)
SELECT * FROM orders WHERE status = 'pending';
-- applicatie verzamelt order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

De JOIN-aanpak dupliceert orderdata voor elk item. Als elke bestelling 20 items heeft en de orderrij breed is, is dat 20x datatransfer. De twee-queries-aanpak stuurt elke bestelling precies één keer.

Mijn regel: gebruik JOINs voor one-to-one relaties, overweeg aparte queries voor one-to-many wanneer de "one"-kant breed is. Maar benchmark altijd — de network round-trip van een tweede query kost vaak meer dan de gedupliceerde data.

Veelvoorkomende Query-Herschrijvingen#

Sommige queries zijn traag niet vanwege ontbrekende indexen, maar vanwege hoe ze geschreven zijn. PostgreSQL's planner is goed, maar geen magie.

Subquery vs JOIN vs CTE#

Deze drie benaderingen kunnen zeer verschillende plannen produceren:

-- Subquery in WHERE — vaak prima, soms verschrikkelijk
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — meestal de beste keuze
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — gematerialiseerd in PG 11 en lager, geoptimaliseerd in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

In PostgreSQL 12+ worden CTEs meestal geïnlined (de planner behandelt ze als subqueries), dus de prestaties zijn identiek. Maar in PostgreSQL 11 en lager zijn CTEs optimalisatieschermen — de planner materialiseert ze en kan predicaten er niet doorheen pushen. Als je nog op PG 11 zit, vermijd CTEs voor prestatie-kritieke queries.

EXISTS vs IN vs JOIN#

Dit komt constant voor:

-- EXISTS — stopt bij de eerste match, geweldig voor bestaanscontrole
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN met subquery — bouwt eerst de volledige resultaatset
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — kan duplicaten produceren als returns meerdere rijen per bestelling heeft
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Voor grote tabellen wint EXISTS vaak omdat het kortsluit. De IN-versie moet de volledige lijst van return-order_ids bouwen voordat het filtert. De JOIN-versie kan duplicaten produceren, waarvoor DISTINCT nodig is, wat een sort- of hash-stap toevoegt.

Mijn default: gebruik EXISTS wanneer je controleert op het bestaan van gerelateerde rijen. Het is het meest semantisch duidelijk en meestal het snelst.

Maar er is een tegenvoorbeeld. Als de subquery-resultaatset klein is en je het voor meerdere condities nodig hebt:

-- Als de subquery weinig rijen retourneert, is IN prima
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

SELECT * Vermijden#

Dit is niet alleen een codestijl-voorkeur. Het heeft echte prestatie-implicaties:

-- Slecht: haalt alle 25 kolommen op inclusief een TEXT-kolom met kilobytes aan data
SELECT * FROM products WHERE category = 'electronics';
 
-- Goed: haalt alleen op wat je nodig hebt
SELECT id, name, price FROM products WHERE category = 'electronics';

Met SELECT *:

• Kan geen index-only scans gebruiken (alle kolommen zouden in de index moeten zitten)
• Transporteert meer data over het netwerk
• Gebruikt meer geheugen voor sortering en hashing
• Als iemand later een 10MB BYTEA-kolom toevoegt, worden je bestaande queries stilletjes trager

Window Functions vs Subqueries#

Window functions zijn een van PostgreSQL's krachtigste features en presteren bijna altijd beter dan gecorreleerde subqueries:

-- Traag: gecorreleerde subquery draait eenmaal per rij
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Snel: window functions berekenen in een enkele pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Nog een veelvoorkomend patroon — de laatste rij per groep ophalen:

-- Traag: gecorreleerde subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Snel: DISTINCT ON (PostgreSQL-specifiek, extreem nuttig)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Ook snel: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON is mijn go-to voor dit patroon. Het is beknopt, leesbaar en PostgreSQL optimaliseert het goed met de juiste index:

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Paginering Goed Doen#

OFFSET is een val voor grote datasets:

-- Pagina 1: snel
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Pagina 500: traag — PostgreSQL moet 9.980 rijen scannen en verwerpen
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Pagina 5000: heel traag — scant 99.980 rijen om er 20 terug te geven
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

De oplossing is keyset pagination (ook wel cursor-based pagination genoemd):

-- Eerste pagina
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Volgende pagina: gebruik de laatste id van de vorige pagina
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

Dit is altijd snel omdat het de index gebruikt om direct naar de juiste positie te springen, ongeacht op welke "pagina" je bent. De trade-off is dat je niet naar een willekeurig paginanummer kunt springen, maar voor infinite scroll of "volgende pagina"-UI's is keyset pagination strikt superieur.

Voor complexe sorteervolgorden:

-- Keyset pagination met meerdere sorteerkolommen
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Dit gebruikt row-value comparison, die PostgreSQL efficiënt afhandelt met een samengestelde index op (price, id).

Tabelstatistieken en Vacuuming#

PostgreSQL's queryplanner neemt beslissingen op basis van statistieken over je data. Slechte statistieken leiden tot slechte plannen. Zo simpel is het.

ANALYZE: De Statistieken Bijwerken#

-- Analyseer een enkele tabel
ANALYZE orders;
 
-- Analyseer de hele database
ANALYZE;
 
-- Bekijk huidige statistieken voor een kolom
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

De correlation-waarde is bijzonder interessant. Het varieert van -1 tot 1 en meet hoe goed de fysieke volgorde van rijen overeenkomt met de logische volgorde van de kolom. Een correlatie dicht bij 1 of -1 betekent dat de data fysiek gesorteerd is op die kolom, waardoor range scans zeer efficiënt zijn (sequentiële I/O). Een correlatie rond 0 betekent random I/O voor range queries.

-- Verhoog het statistiekdoel voor kolommen met scheve verdelingen
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Het standaard statistiekdoel is 100, wat betekent dat PostgreSQL 300 * 100 = 30.000 rijen samplet. Voor kolommen met veel verschillende waarden of scheve verdelingen geeft het verhogen naar 500 of 1000 de planner betere data ten koste van iets langere ANALYZE-tijden.

Autovacuum Tuning#

Autovacuum doet twee dingen: ruimte terugwinnen van dode tuples (verwijderde of bijgewerkte rijen) en statistieken bijwerken. Op drukke tabellen zijn de standaard autovacuum-instellingen vaak te conservatief.

-- Controleer autovacuum-status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Als je tabellen ziet met miljoenen dode tuples en de laatste vacuum uren geleden was, loopt je autovacuum achter.

Voor tabellen met veel wijzigingen (zoals sessions, job queues of metrics) stel ik per-tabel autovacuum-instellingen in:

-- Agressievere autovacuum voor write-heavy tabellen
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum bij 1% dode tuples (standaard 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze bij 0,5% wijzigingen (standaard 10%)
  autovacuum_vacuum_cost_delay = 2           -- snellere vacuum (standaard 2ms in nieuwere PG)
);

Tabel-Bloat#

Wanneer PostgreSQL een rij bijwerkt, wijzigt het de rij niet op zijn plek — het maakt een nieuwe versie aan en markeert de oude als dood. Vacuum wint de dode rijen terug, maar de ruimte wordt alleen hergebruikt door die tabel. Het tabelbestand op schijf wordt niet kleiner.

Na verloop van tijd kan een zwaar bijgewerkte tabel significante bloat hebben — de tabel is op schijf veel groter dan de live data vereist. Dit betekent meer pagina's om te scannen, meer I/O, meer cachedruk.

-- Schat tabelbloat in (vereenvoudigde versie)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Voor ernstig opgezwollen tabellen is de nucleaire optie VACUUM FULL — het herschrijft de hele tabel. Maar het neemt een exclusieve lock, dus je kunt het niet doen op een live productietabel zonder downtime. De betere aanpak is pg_repack, dat hetzelfde doet zonder te locken:

pg_repack --table orders --no-kill-backend -d mydb

Connection Pooling#

Hier is iets dat veel developers verrast: PostgreSQL-verbindingen zijn duur. Elke verbinding spawnt een nieuw proces (geen thread), verbruikt ongeveer 5-10 MB geheugen en heeft een niet-triviale fork-overhead.

De standaard max_connections is 100. Als je een applicatieserver hebt met 20 workers die elk 5 verbindingen openen, zit je al op je limiet. Voeg een achtergrond-job processor, een monitoringtool en een migratie die ergens draait toe, en je hebt een probleem.

Waarom Je een Connection Pooler Nodig Hebt#

Zonder een pooler, als je applicatie 500 gelijktijdige verzoeken moet afhandelen, heb je 500 PostgreSQL-verbindingen nodig. Dat is 5 GB geheugen alleen voor verbindingsoverhead, en PostgreSQL's prestaties verslechteren aanzienlijk boven een paar honderd verbindingen vanwege overhead in procesbeheer.

Met PgBouncer voor PostgreSQL worden die 500 applicatieverbindingen omgezet naar misschien 20 werkelijke PostgreSQL-verbindingen. De pooler zet verzoeken in de wachtrij wanneer alle databaseverbindingen bezet zijn.

PgBouncer-configuratie#

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Transaction vs Session Mode#

• Transaction mode (pool_mode = transaction): Verbindingen worden na elke transactie teruggestuurd naar de pool. Dit is wat je 95% van de tijd wilt. Maximale verbindingshergebruik.
• Session mode (pool_mode = session): Verbindingen worden vastgehouden voor de gehele clientsessie. Gebruik dit als je prepared statements, SET-commando's, LISTEN/NOTIFY of andere sessie-niveau features nodig hebt.
• Statement mode (pool_mode = statement): Verbindingen worden na elke statement teruggestuurd. Te restrictief voor de meeste applicaties — je kunt niet eens expliciete transacties gebruiken.

Het addertje bij transaction mode: je kunt geen prepared statements gebruiken (die zijn sessie-niveau state), je kunt SET niet gebruiken voor sessievariabelen, en LISTEN/NOTIFY werkt niet zoals verwacht. De meeste ORMs hebben een manier om prepared statements uit te schakelen.

Voor Node.js met de pg driver:

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // PgBouncer-poort, niet PostgreSQL-poort
  database: 'mydb',
  max: 20,     // komt overeen met PgBouncer default_pool_size
  statement_timeout: 30000,
  // Schakel prepared statements uit voor PgBouncer transaction mode
  prepare: false,
});

Formule voor Pool Sizing#

Er is een bekende formule voor optimale PostgreSQL connection pool grootte:

optimale_verbindingen = (core_count * 2) + effective_spindle_count

Voor een moderne server met 4 cores en een SSD (1 effectieve spindle):

optimaal = (4 * 2) + 1 = 9

Dit lijkt contra-intuïtief laag. Maar PostgreSQL is CPU-bound op de meeste moderne hardware (SSD's zijn snel genoeg dat I/O zelden bottleneckt). Meer verbindingen dan dit leidt tot context switching overhead dat de zaken daadwerkelijk vertraagt.

In de praktijk zet ik default_pool_size meestal op 2-3x dit getal om burst-verkeer aan te kunnen, met het begrip dat bij piekbelasting sommige queries in de PgBouncer-wachtrij wachten in plaats van allemaal tegelijk PostgreSQL te raken.

Praktische Checklist: De Exacte Stappen voor Elke Trage Query#

Dit is mijn werkelijke proces wanneer ik een "deze query is traag"-rapport krijg. Ik volg deze stappen in deze volgorde, elke keer.

Stap 1: Verkrijg de Werkelijke Query#

Niet "het endpoint is traag" — de werkelijke SQL. Als je een ORM gebruikt, schakel query-logging in:

-- Log tijdelijk alle queries boven de 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Of controleer pg_stat_statements voor de top queries op totale tijd:

-- Top 10 queries op totale uitvoeringstijd
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements is de meest waardevolle extensie voor PostgreSQL-prestaties. Als je het niet draait, installeer het nu:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Stap 2: Voer EXPLAIN (ANALYZE, BUFFERS) Uit#

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <de trage query>;

Kijk naar:

1. Slechte rijschattingen — werkelijke rijen sterk afwijkend van geschatte rijen
2. Seq Scans op grote tabellen — mogelijk ontbrekende index
3. Nested Loops met veel rijen — zou een Hash Join of Merge Join moeten zijn
4. Hoge buffer reads — koude cache of tabel te groot
5. Sort-operaties die naar schijf spillen — verhoog work_mem of voeg index toe voor sort

Stap 3: Controleer Tabelstatistieken#

-- Wanneer zijn de statistieken voor het laatst bijgewerkt?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Als last_analyze oud is of n_dead_tup hoog is ten opzichte van n_live_tup, voer dan uit:

ANALYZE orders;

Voer daarna EXPLAIN ANALYZE opnieuw uit. Als het plan verandert, waren verouderde statistieken het probleem.

Stap 4: Controleer Bestaande Indexen#

-- Welke indexen bestaan er op deze tabel?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Misschien bestaat de index maar wordt hij niet gebruikt. Misschien is de kolomvolgorde verkeerd voor je query.

Stap 5: Maak of Wijzig Indexen#

Op basis van het queryplan, maak de juiste index aan. Test met EXPLAIN ANALYZE voor en na.

-- Maak de index concurrent aan (lockt de tabel niet)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verifieer dat de index wordt gebruikt
EXPLAIN (ANALYZE, BUFFERS) <de trage query>;

Gebruik altijd CONCURRENTLY op productietabellen. Een gewone CREATE INDEX neemt een volledige tabellock die alle writes blokkeert.

Stap 6: Overweeg Query-Herschrijvingen#

Als de index bestaat en statistieken vers zijn maar de query nog steeds traag is, kijk dan naar de query zelf:

• Kan een subquery herschreven worden als een JOIN?
• Veroorzaakt OFFSET problemen? Schakel over naar keyset pagination
• Selecteer je meer kolommen dan nodig?
• Kan een gecorreleerde subquery een window function worden?
• Verhindert een CTE dat de planner optimaliseert?

Stap 7: Controleer Serverinstellingen#

Voor specifieke querypatronen zijn serverinstellingen belangrijk:

-- work_mem: geheugen voor sorts en hash joins (per operatie, niet per verbinding)
-- Standaard is 4MB, wat te laag is voor complexe queries
SET work_mem = '64MB';  -- probeer dit en voer EXPLAIN ANALYZE opnieuw uit
 
-- effective_cache_size: vertelt de planner hoeveel schijfcache te verwachten
-- Zet op ~75% van totaal RAM
SHOW effective_cache_size;
 
-- random_page_cost: verhouding van random I/O tot sequentiële I/O
-- Standaard is 4.0, zet op 1.1-1.5 voor SSD's
SHOW random_page_cost;

Als het wijzigen van work_mem een sort-naar-schijf probleem oplost, overweeg het globaal te verhogen. Maar wees voorzichtig — het is per operatie, niet per verbinding. Een complexe query met 10 sort-operaties en work_mem = 256MB kan 2,5 GB gebruiken voor een enkele query.

Stap 8: Monitor Na de Fix#

Fix het niet en vergeet het. Verifieer dat de fix standhoudt:

-- Reset pg_stat_statements om verse data te zien na je wijzigingen
SELECT pg_stat_statements_reset();
 
-- Controleer na een uur/dag
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Bonus: Quick Wins Die Ik op Elke Nieuwe Database Toepas#

Dit zijn instellingen en praktijken die ik toepas op elke PostgreSQL-database die ik opzet, voordat er prestatieproblemen optreden:

-- 1. Installeer pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Schakel slow query logging in (50ms is mijn drempel)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-geschikte instellingen
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Verhoog work_mem van de karige default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Zet effective_cache_size op 75% van RAM
-- Voor een 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Zet shared_buffers op 25% van RAM
-- Voor een 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Verbeter autovacuum-responsiviteit
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Pas wijzigingen toe
SELECT pg_reload_conf();
-- Let op: shared_buffers vereist een herstart

En een monitoringquery die ik wekelijks draai:

-- Tabellen die aandacht nodig hebben
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Slotgedachten#

PostgreSQL-queryoptimalisatie is geen zwarte kunst. Het is een systematisch proces:

1. Meet — raad niet. EXPLAIN (ANALYZE, BUFFERS) is je beste vriend.
2. Begrijp het plan — leer scantypen, jointypen en rijschattingen lezen.
3. Index strategisch — de juiste index op de juiste kolommen, in de juiste volgorde. Partial indexes en covering indexes zijn onderbenutte superkrachten.
4. Schrijf betere queries — EXISTS boven IN voor bestaanscontroles, keyset pagination boven OFFSET, window functions boven gecorreleerde subqueries.
5. Onderhoud de database — autovacuum tuning, statistiek-updates, connection pooling.
6. Monitor continu — pg_stat_statements vertelt je waar je database zijn tijd besteedt. Controleer het regelmatig.

Het verschil tussen een query die 4 seconden duurt en een die 0,3 milliseconden duurt is zelden hardware. Het is bijna altijd kennis — weten waar je moet kijken en wat je moet veranderen. En nu weet je waar je moet kijken.