Indexare avansată PostgreSQL: parțială, acoperire, BRIN și GIN
Un index B-Tree cu o singură coloană este soluția la 80% dintre problemele de performanță. Dar 20% rămas necesită instrumente mai sofisticate: un index parțial care indexează doar rândurile „active” reduce indicele cu 95%, un indice de acoperire elimină complet accesul la tabelul heap, un BRIN pe un tabel cu serii de timp ocupă de 100 de ori mai puțin spațiu decât un B-Tree, iar un GIN permite căutări full-text și matrice pe care niciun B-Tree nu le poate face. Acest ghid acoperă toate cele patru tipuri.
Ce vei învăța
- Index parțial: indexați numai rândurile care contează pentru cele mai frecvente interogări
- Acoperirea indexului cu INCLUDE: activați scanarea numai pentru index pentru interogări SELECT
- Index BRIN: pentru tabele cu serii temporale cu milioane de rânduri
- Index GIN: căutare full-text, reținere matrice și interogări JSONB
- Cum să alegeți tipul de index corect pentru fiecare scenariu
Index parțial: Indexați numai ceea ce contează
Un index parțial indexează numai rândurile care îndeplinesc o condiție WHERE. Și tehnica cel mai puternic pentru scenariile în care un procent mic de rânduri primește marea majoritate de interogări: comenzi „în așteptare”, mesaje „necitite”, sarcini „în curs”.
-- 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!)
Indicele parțial pentru valorile unice parțiale
-- 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
Index de acoperire cu INCLUDE: Eliminați accesul la heap
O scanare numai cu index citește datele direct din index, fără a accesa tabelul heap.
Este cel mai rapid tip de scanare posibil. Pentru a-l activa, indexul trebuie să conțină toate elementele
coloane care apar în SELECT. Clauza INCLUDE adăugați coloane „în plus”.
în index fără a le include în structura B-Tree (prin urmare, fără a afecta performanța
de INSERT/UPDATE pe index însuși).
-- 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!
Când INCLUDE, nu ajută
-- 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
Index BRIN: Pentru tabelele cu serii temporale
BRIN (Block Range INdex) este conceput pentru tabele în care datele sunt sortate fizic după o coloană (de obicei un marcaj de timp de inserare). În loc să indexați fiecare valoare, BRIN stochează doar min/max pentru fiecare bloc de pagini. Este enorm mai mic decât a B-Tree, dar mai puțin precis: ideal pentru scanarea intervalului de date.
-- 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 vs B-Tree: Când să alegi
-- 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
Index GIN: Căutare full-text, Array și JSONB
GIN (Generalized Inverted INdex) este cel mai versatil tip de index al PostgreSQL. Îți permite index conținut compus — matrice, tsvectors, JSONB — unde fiecare element al conținutului și indexate separat. Esențial pentru căutarea full-text și interogările matrice.
GIN pentru căutarea integrală a textului
-- 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 pentru 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 pentru 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)
Harta de decizie: ce index să alegeți
| Scenariu | Tip de index | Motiv |
|---|---|---|
| Interogare pe PK sau FK (egalitate) | B-Tree (implicit) | Cel mai bun pentru =, <, >, BETWEEN |
| Doar câteva rânduri „active” (de exemplu, în așteptare) | Arbore B parțial | Index de 100 de ori mai mic |
| SELECT cu toate coloanele din index | B-Tree cu INCLUDE | Scanare numai pentru index, fără acces la heap |
| Tabel numai pentru atașare cu marcaj de timp | BRIN | De 1000 de ori mai mic decât B-Tree |
| Căutare text integral în italiană/engleză | GIN pe tsvector | Singura cale pentru operatorul @@ |
| Conținut de matrice (@>, &&) | GIN pe TEXT[] | Singura modalitate pentru operatorii de matrice |
| Reținere JSONB (@>) | GIN jsonb_path_ops | Cu 30% mai compact decât implicit |
| LIKE „pattern%” pe text | B-Tree (varchar_pattern_ops) | Doar prefix LIKE |
| LIKE „%pattern%” pe text | GIN cu pg_trgm | Singura modalitate de a LIKE pe mediu |
Întreținere index
-- 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;
Concluzii
Alegerea tipului de index potrivit poate face diferența între o interogare care durează 10 secunde și unul care durează 10 milisecunde. Indicele parțial și indicele de acoperire sunt optimizările mai de impact pentru interogările operaționale. BRIN este revoluționar pentru jurnalele și datele serii de timp. GIN deschide posibilități de căutare pe care niciun alt tip de index nu le permite.
Următorul articol din serie abordează partiționarea tabelelor: tehnica de management tabele cu miliarde de rânduri fără a pierde performanța, cu tăierea partiției și DROP PARTIȚIE instantanee pentru gestionarea ciclului de viață al datelor.
Seria: Inginerie și optimizare a bazelor de date
- Articolul 1: PostgreSQL 17/18 - Știri și performanță
- Articolul 2: EXPLICAȚI ANALIZA - Citiți planurile de interogare
- Articolul 3 (acest): Indexare avansată - Parțială, Acoperire, BRIN, GIN
- Articolul 4: Partiționarea tabelelor - Gestionarea miliardelor de rânduri
- Articolul 5: Pooling de conexiuni - PgBouncer vs Pgpool-II