PostgreSQL geavanceerde indexering: gedeeltelijk, dekking, BRIN en GIN
Een B-Tree-index met één kolom is de oplossing voor 80% van de prestatieproblemen. Maar 20% voor de overige zijn geavanceerdere hulpmiddelen nodig: een gedeeltelijke index die alleen de "actieve" rijen indexeert vermindert de index met 95%, een dekkende index elimineert volledig de toegang tot de heap-tabel, een BRIN op een tijdreekstabel neemt 100 keer minder ruimte in beslag dan een B-Tree, en een GIN maakt het mogelijk zoekopdrachten in volledige tekst en arrays die geen enkele B-Tree kan uitvoeren. Deze gids behandelt alle vier de typen.
Wat je gaat leren
- Gedeeltelijke index: Indexeer alleen de rijen die van belang zijn voor de meest voorkomende zoekopdrachten
- Index afdekken met INCLUDE: schakel alleen-index-scan in op SELECT-query's
- BRIN Index: voor tijdreekstabellen met miljoenen rijen
- GIN Index: zoeken in volledige tekst, array containment en JSONB-query's
- Hoe u voor elk scenario het juiste indextype kiest
Gedeeltelijke index: indexeer alleen wat er toe doet
Een gedeeltelijke index indexeert alleen rijen die voldoen aan een WHERE-voorwaarde. En de techniek het krachtigst voor scenario's waarin een klein percentage rijen de grote meerderheid krijgt aantal vragen: "in behandeling zijnde" bestellingen, "ongelezen" berichten, "in uitvoering" taken.
-- Scenario: tabella ordini con 50 milioni di righe
-- 99% degli ordini ha stato 'completato' o 'cancellato'
-- Le query operative riguardano solo gli ordini 'pendenti' (50.000 righe)
-- Indice normale: 50 milioni di righe -> grande, lento da mantenere
CREATE INDEX idx_ordini_stato ON ordini (stato);
-- Dimensione: ~2GB, aggiornato ad ogni INSERT/UPDATE/DELETE
-- Partial index: solo gli ordini pendenti (50.000 righe)
CREATE INDEX idx_ordini_pendenti ON ordini (creato_il)
WHERE stato = 'pendente';
-- Dimensione: ~2MB (1000x piu piccolo!)
-- PostgreSQL lo usa automaticamente per query con WHERE stato = 'pendente'
-- Query che usa il partial index
EXPLAIN ANALYZE
SELECT id, utente_id, totale
FROM ordini
WHERE stato = 'pendente'
AND creato_il < NOW() - INTERVAL '1 hour';
-- -> Index Scan using idx_ordini_pendenti (2MB di indice vs 2GB!)
Gedeeltelijke index voor gedeeltelijke unieke waarden
-- Caso d'uso: un utente puo avere al massimo un indirizzo "primario"
-- ma puo avere infiniti indirizzi non primari
-- Indice UNIQUE parziale: vincola l'unicita solo dove e_primario = true
CREATE UNIQUE INDEX idx_indirizzi_primary_unico
ON indirizzi (utente_id)
WHERE e_primario = true;
-- Ora questo INSERT fallisce (utente 42 ha gia un indirizzo primario)
INSERT INTO indirizzi (utente_id, via, e_primario)
VALUES (42, 'Via Roma 1', true); -- ERROR: duplicate key value
-- Ma questo e permesso (indirizzi non primari non hanno il vincolo)
INSERT INTO indirizzi (utente_id, via, e_primario)
VALUES (42, 'Via Milano 5', false); -- OK
-- Partial index per soft delete
CREATE INDEX idx_prodotti_attivi ON prodotti (categoria_id, prezzo)
WHERE deleted_at IS NULL;
-- Il 95% delle query filtra su deleted_at IS NULL
-- L'indice e 20x piu piccolo di uno su tutte le righe
Dekkende index met INCLUDE: Heap-toegang elimineren
Bij een alleen-index-scan worden gegevens rechtstreeks uit de index gelezen, zonder toegang tot de heap-tabel.
Het is het snelste type scan dat mogelijk is. Om dit in te schakelen, moet de index alle
kolommen die verschijnen in de SELECT. De clausule INCLUDE voeg "extra" kolommen toe
in de index zonder ze op te nemen in de B-Tree-structuur (dus zonder de prestaties te beïnvloeden).
van INSERT/UPDATE op de index zelf).
-- Query frequente: lista ordini per utente con totale e data
SELECT id, creato_il, totale
FROM ordini
WHERE utente_id = $1
ORDER BY creato_il DESC;
-- Indice normale: solo la colonna di filtro
CREATE INDEX idx_ordini_utente ON ordini (utente_id, creato_il DESC);
-- Piano: Index Scan -> poi Heap Fetch per ogni riga (per leggere 'totale')
-- Performance: O(k * log N) dove k = righe per utente
-- Covering index: aggiunge 'totale' con INCLUDE
CREATE INDEX idx_ordini_utente_covering ON ordini (utente_id, creato_il DESC)
INCLUDE (totale);
-- Piano: Index Only Scan -> nessun Heap Fetch!
-- Performance: O(log N) + legge solo le pagine dell'indice
-- Verificare che usi Index Only Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creato_il, totale
FROM ordini
WHERE utente_id = 42
ORDER BY creato_il DESC
LIMIT 50;
-- Output: "Index Only Scan using idx_ordini_utente_covering"
-- "Heap Fetches: 0" <- ottimo!
Als het INCLUSIEF is, helpt het niet
-- INCLUDE non aiuta se:
-- 1. La visibility map non e aggiornata (heap fetches necessari per MVCC)
-- Soluzione: VACUUM regolare
VACUUM VERBOSE ordini;
-- Verifica: "Index Only Scans: N" in pg_stat_user_indexes
-- 2. La tabella ha molti UPDATE non vacuumati
SELECT
relname,
n_dead_tup,
n_live_tup,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'ordini';
-- 3. Le colonne in INCLUDE sono troppo grandi (testo lungo, array, JSONB)
-- Il indice diventa enorme, peggio che un heap fetch
-- Regola pratica per INCLUDE:
-- - Colonne numeriche (int, bigint, numeric): OK
-- - Colonne brevi (varchar < 50 char): OK
-- - Colonne lunghe, JSONB, testo: meglio evitare
BRIN-index: voor tijdreekstabellen
BRIN (Block Range INdex) is ontworpen voor tabellen waarop gegevens fysiek worden gesorteerd een kolom (meestal een invoegtijdstempel). In plaats van elke waarde te indexeren, BRIN slaat alleen de min/max op voor elk paginablok. Het is enorm kleiner dan een B-Tree maar minder nauwkeurig: ideaal voor scans van datumbereiken.
-- Tabella eventi IoT: 500 milioni di righe, inserite in ordine temporale
-- B-Tree su ts_evento: 15GB di indice
-- BRIN su ts_evento: 150KB di indice (100.000x piu piccolo!)
CREATE TABLE eventi_iot (
id BIGSERIAL,
device_id INT NOT NULL,
ts_evento TIMESTAMPTZ NOT NULL DEFAULT NOW(),
valore FLOAT8 NOT NULL,
metadata JSONB
) PARTITION BY RANGE (ts_evento);
-- Creare BRIN invece di B-Tree per la colonna temporale
CREATE INDEX idx_eventi_ts_brin ON eventi_iot USING BRIN (ts_evento)
WITH (pages_per_range = 128); -- default: 128 pagine per range
-- Query che sfrutta il BRIN
EXPLAIN ANALYZE
SELECT device_id, AVG(valore)
FROM eventi_iot
WHERE ts_evento BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY device_id;
-- "Bitmap Index Scan on idx_eventi_ts_brin"
-- "Recheck Cond: (ts_evento BETWEEN ...)"
-- BRIN su colonne fisicamente correlate (non necessariamente timestamp)
-- Esempio: log con ID auto-incrementale
CREATE INDEX idx_log_id_brin ON application_logs USING BRIN (log_id)
WITH (pages_per_range = 64);
BRIN versus B-Tree: wanneer te kiezen
-- Confronto pratico su tabella da 500M righe:
--
-- | B-Tree | BRIN
-- ---------|--------------|--------
-- Dimensione| 15 GB | 150 KB
-- Build time| 45 min | 2 min
-- Scan (1 settimana su 1 anno) | 0.1s | 0.8s
-- INSERT overhead | alto | minimo
-- UPDATE overhead | alto | minimo
-- BRIN e ottimo quando:
-- 1. La tabella cresce per append (log, eventi, transazioni finanziarie)
-- 2. I dati sono fisicamente correlati con la colonna indicizzata
-- 3. Lo spazio disco e un vincolo
-- 4. Le query sono sempre su range (non su valori singoli)
-- BRIN NON funziona bene quando:
-- 1. Gli INSERT non sono in ordine (rows sparse = BRIN inutile)
-- 2. Le query cercano valori singoli precisi
-- 3. La tabella ha molti UPDATE/DELETE che rovinano la correlazione fisica
-- Misurare la correlazione fisica (valore tra -1 e 1, vicino a 1 = BRIN ottimo)
SELECT
attname,
correlation
FROM pg_stats
WHERE tablename = 'eventi_iot'
AND attname = 'ts_evento';
-- Se correlation > 0.9: BRIN e molto efficiente
-- Se correlation < 0.5: BRIN poco utile, meglio B-Tree
GIN-index: zoeken in volledige tekst, array en JSONB
GIN (Generalized Inverted INDex) is het meest veelzijdige indextype van PostgreSQL. Het staat je toe index samengestelde inhoud — arrays, tsvectors, JSONB — waar elk element van de inhoud en afzonderlijk geïndexeerd. Essentieel voor zoeken in volledige tekst en arrayquery's.
GIN voor zoeken in volledige tekst
-- Full-text search su una tabella di articoli
-- Aggiungere una colonna tsvector calcolata (PostgreSQL 12+)
ALTER TABLE articoli ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('italian',
coalesce(titolo, '') || ' ' ||
coalesce(contenuto, '') || ' ' ||
coalesce(tags::text, '')
)
) STORED;
-- Creare l'indice GIN sulla colonna search_vector
CREATE INDEX idx_articoli_fts ON articoli USING GIN (search_vector);
-- Query full-text che usa il GIN
SELECT id, titolo, ts_rank(search_vector, query) AS rank
FROM articoli,
to_tsquery('italian', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
-- Con highlighting
SELECT
id,
titolo,
ts_headline('italian', contenuto, query,
'MaxFragments=3, MaxWords=30, MinWords=10') AS snippet
FROM articoli,
to_tsquery('italian', 'postgresql:* & (performance | ottimizzazione)') AS query
WHERE search_vector @@ query;
GIN voor Array Containment
-- Tabella prodotti con array di tag
CREATE TABLE prodotti (
id BIGSERIAL PRIMARY KEY,
nome TEXT NOT NULL,
tags TEXT[] NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_prodotti_tags ON prodotti USING GIN (tags);
-- Query con operatori array su GIN (molto piu veloci senza GIN)
-- @>: "contiene tutti questi tag"
SELECT id, nome
FROM prodotti
WHERE tags @> ARRAY['postgresql', 'performance'];
-- &&: "ha almeno uno di questi tag"
SELECT id, nome
FROM prodotti
WHERE tags && ARRAY['database', 'sql', 'nosql'];
-- Verifica che il GIN sia usato
EXPLAIN ANALYZE
SELECT id, nome FROM prodotti
WHERE tags @> ARRAY['postgresql'];
-- "Bitmap Index Scan on idx_prodotti_tags"
GIN voor JSONB
-- Indice GIN su colonna JSONB (indicizza ogni chiave/valore)
CREATE TABLE eventi (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL
);
-- GIN con gin_trgm_ops (trigrammi): per LIKE e ricerche simili
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN con jsonb_path_ops (piu compatto ma solo per @> e @?)
CREATE INDEX idx_eventi_payload_path ON eventi
USING GIN (payload jsonb_path_ops);
-- GIN standard (supporta tutti gli operatori JSONB)
CREATE INDEX idx_eventi_payload ON eventi USING GIN (payload);
-- Query che usa il GIN JSONB
-- Trovare eventi con specifico tipo
EXPLAIN ANALYZE
SELECT id, payload->>'timestamp'
FROM eventi
WHERE payload @> '{"tipo": "acquisto", "canale": "mobile"}';
-- Differenza tra i due tipi GIN per JSONB:
-- jsonb_path_ops: 30-40% piu compatto, solo @> e jsonpath
-- default: supporta anche ?, ?|, ?&, @?, @@
-- Scegliere default se usi operatori di existence (?)
-- Scegliere jsonb_path_ops se usi solo @> (containment)
Beslissingskaart: welke index u moet kiezen
| Scenario | Indextype | Reden |
|---|---|---|
| Vraag over PK of FK (gelijkheid) | B-boom (standaard) | Beste voor =, <, >, TUSSEN |
| Slechts enkele "actieve" regels (bijvoorbeeld in behandeling) | Gedeeltelijke B-boom | Index 100x kleiner |
| SELECT met alle kolommen in de index | B-boom met INCLUSIEF | Alleen indexscan, geen heap-toegang |
| Alleen-toevoegen-tabel met tijdstempel | BRIN | 1000x kleiner dan B-Tree |
| Zoeken in volledige tekst in het Italiaans/Engels | GIN op tsvector | Enige manier voor @@-operator |
| Array-insluiting (@>, &&) | GIN op TEKST[] | De enige manier voor array-operators |
| JSONB-insluiting (@>) | GIN jsonb_path_ops | 30% compacter dan standaard |
| LIKE 'patroon%' op tekst | B-boom (varchar_pattern_ops) | Alleen het voorvoegsel LIKE |
| LIKE '%pattern%' op tekst | GIN met pg_trgm | De enige manier om te LIKEn op medium |
Indexonderhoud
-- Identificare indici non usati (candidati alla rimozione)
SELECT
schemaname,
tablename,
indexname,
idx_scan AS scansioni,
pg_size_pretty(pg_relation_size(indexrelid)) AS dimensione
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Identificare indici duplicati (stesso set di colonne)
SELECT
t.relname AS tabella,
array_agg(i.relname) AS indici_duplicati,
pg_size_pretty(sum(pg_relation_size(ix.indexrelid))) AS spazio_totale
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
GROUP BY t.relname, ix.indkey
HAVING count(*) > 1;
-- Rebuild di un indice senza bloccare (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_ordini_stato;
-- Monitorare lo spazio degli indici
SELECT
relname,
pg_size_pretty(pg_total_relation_size(oid)) AS totale,
pg_size_pretty(pg_relation_size(oid)) AS tabella,
pg_size_pretty(pg_indexes_size(oid)) AS indici
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;
Conclusies
Het kiezen van het juiste indextype kan het verschil maken tussen een zoekopdracht van 10 seconden en eentje die 10 milliseconden duurt. De gedeeltelijke index en dekkende index zijn de optimalisaties meer impact voor operationele vragen. BRIN is revolutionair voor logs en tijdreeksgegevens. GIN opent zoekmogelijkheden die geen enkel ander type index toestaat.
Het volgende artikel in de serie gaat over tabelpartitionering: de beheertechniek tabellen met miljarden rijen zonder prestatieverlies, met partitie-pruning en DROP Instant PARTITION voor beheer van de gegevenslevenscyclus.
Serie: Database-engineering en -optimalisatie
- Artikel 1: PostgreSQL 17/18 - Nieuws en prestaties
- Artikel 2: UITLEG ANALYSE - Lees de Queryplannen
- Artikel 3 (dit): Geavanceerde indexering - Gedeeltelijk, Covering, BRIN, GIN
- Artikel 4: Tabelpartitionering - Miljarden rijen beheren
- Artikel 5: Verbindingspooling - PgBouncer versus Pgpool-II