03 - Indexing Avanzato: Partial, Covering, BRIN, GIN

Indexing Avanzato PostgreSQL: Partial, Covering, BRIN e GIN

Un indice B-Tree su una colonna e la soluzione per l'80% dei problemi di performance. Ma il 20% rimanente richiede strumenti piu sofisticati: un partial index che indicizza solo le righe "attive" riduce l'indice del 95%, un covering index elimina completamente gli accessi alla tabella heap, un BRIN su una tabella time-series occupa 100 volte meno spazio di un B-Tree, e un GIN abilita ricerche full-text e su array che nessun B-Tree puo fare. Questa guida copre tutti e quattro i tipi.

Partial Index: Indicizzare Solo Cio che Conta

Un partial index indicizza solo le righe che soddisfano una condizione WHERE. E la tecnica piu potente per scenari dove una piccola percentuale di righe riceve la grande maggioranza delle query: ordini "pendenti", messaggi "non letti", task "in progress".

-- 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!)

Partial Index per Valori Unici Parziali

-- 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

Covering Index con INCLUDE: Eliminare gli Heap Access

Un index-only scan legge i dati direttamente dall'indice, senza accedere alla tabella heap. E il tipo di scan piu veloce possibile. Per abilitarlo, l'indice deve contenere tutte le colonne che appaiono nella SELECT. La clausola INCLUDE aggiunge colonne "extra" nell'indice senza includerle nella struttura B-Tree (quindi senza impattare le performance di INSERT/UPDATE sul indice stesso).

-- 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!

Quando INCLUDE Non Aiuta

-- 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: Per Tabelle Time-Series

BRIN (Block Range INdex) e progettato per tabelle dove i dati sono fisicamente ordinati per una colonna (tipicamente un timestamp di inserimento). Invece di indicizzare ogni valore, BRIN memorizza solo il min/max per ogni blocco di pagine. E enormemente piu piccolo di un B-Tree ma meno preciso: ideale per scansioni su range di 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: Quando Scegliere

-- 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: Full-Text Search, Array e JSONB

GIN (Generalized Inverted INdex) e il tipo di indice piu versatile di PostgreSQL. Permette di indicizzare contenuti composti - array, vettori tsvector, JSONB - dove ogni elemento del contenuto e indicizzato separatamente. Essenziale per full-text search e query su array.

GIN per Full-Text Search

-- 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 per 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 per 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)

Mappa Decisionale: Quale Indice Scegliere

Manutenzione degli Indici

-- 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;

Conclusioni

La scelta del tipo di indice giusto puo fare la differenza tra una query che dura 10 secondi e una che dura 10 millisecondi. Il partial index e il covering index sono le ottimizzazioni piu impattanti per le query operative. BRIN e rivoluzionario per log e dati time-series. GIN apre possibilita di ricerca che nessun altro tipo di indice permette.

Il prossimo articolo della serie affronta il table partitioning: la tecnica per gestire tabelle con miliardi di righe senza perdere performance, con partition pruning e DROP PARTITION istantaneo per la gestione del ciclo di vita dei dati.