Indici PostgreSQL Avanzati per Performance Reali — GIN, GiST, BRIN e Indici Parziali

Se il tuo database PostgreSQL rallenta su ricerche full-text, dati JSON, geometrie o range di date, il problema non è il carico: è l'indice sbagliato. Un B-tree funziona benissimo per uguaglianze e intervalli su numeri e testi corti, ma su array, documenti JSON, coordinate geografiche o colonne con migliaia di valori uguali si incaglia. Noi di Meteora Web lo vediamo ogni giorno nei progetti che ottimizziamo: un indice ben scelto trasforma una query da 10 secondi a 10 millisecondi. In questa guida vediamo come e quando usare GIN, GiST, BRIN e gli indici parziali — senza teoria inutile, con esempi pronti da copiare.

Perché gli indici PostgreSQL avanzati (GIN, GiST, BRIN) sono migliori dei B-tree?

Il B-tree è il cavallo di battaglia, ma ha limiti precisi: non supporta bene la ricerca su array, JSONB, full-text e dati spaziali. Per esempio, su una colonna JSONB con un B-tree puoi solo cercare per uguaglianza esatta del documento intero. Con un indice GIN invece puoi interrogare i singoli campi JSON con @> o ?|. Lo stesso vale per il full-text: to_tsvector() genera un vettore di lessemi che un B-tree non sa indicizzare. GIN e GiST sono progettati per dati compositi e ricerche di similarità; BRIN è perfetto per dati ordinati e fisicamente correlati (es. log temporali). Se non li usi, paghi in performance e spazio su disco.

Quando usare un indice GIN per ricerche full-text e JSONB?

L'indice GIN (Generalized Inverted Index) funziona invertendo l'associazione: invece di mappare riga->valore, mappa valore->righe. È ideale per array, JSONB, full-text e ricerche su componenti. Ecco un esempio concreto su un catalogo prodotti JSON:

-- Tabella prodotti con attributi JSON
CREATE TABLE prodotti (
    id SERIAL PRIMARY KEY,
    nome TEXT,
    attributi JSONB
);

-- Popolamento esempio
INSERT INTO prodotti (nome, attributi) VALUES
('Scarpe Running', '{"colore": "rosso", "taglie": [39,40,41], "materiale": "mesh"}'),
('Zaino Trekking', '{"colore": "verde", "capacita": "40L", "impermeabile": true}');

-- Creazione indice GIN su attributi
CREATE INDEX idx_attributi_gin ON prodotti USING GIN (attributi);

-- Query veloce: trova prodotti con colore rosso
SELECT * FROM prodotti WHERE attributi @> '{"colore": "rosso"}';

-- Full-text: indice GIN su tsvector
CREATE TABLE articoli (
    id SERIAL PRIMARY KEY,
    titolo TEXT,
    corpo TEXT
);

ALTER TABLE articoli ADD COLUMN testo_tsvector TSVECTOR
    GENERATED ALWAYS AS (to_tsvector('italian', titolo || ' ' || corpo)) STORED;

CREATE INDEX idx_testo_gin ON articoli USING GIN (testo_tsvector);

-- Query full-text
SELECT * FROM articoli WHERE testo_tsvector @@ to_tsquery('italian', 'performance & database');

Attenzione: l'indice GIN richiede più tempo di scrittura e spazio su disco rispetto a un B-tree. Usalo solo su colonne con alta cardinalità di valori unici e poche scritture (o quando le letture sono critiche). Per colonne JSONB con poche varianti, un B-tree + un indice su espressione può bastare.

GiST: come gestire ricerche geografiche e full-text con similarità?

L'indice GiST (Generalized Search Tree) supporta operatori di prossimità, intersezione e concordanza. È il re per dati geospaziali (PostGIS), full-text con ranking e ricerche su intervalli di date. Ecco un esempio geografico:

-- Estensione PostGIS (richiesta per geometrie)
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE punti_vendita (
    id SERIAL PRIMARY KEY,
    nome TEXT,
    posizione GEOGRAPHY(Point, 4326)
);

-- Indice GiST sulla colonna geografica
CREATE INDEX idx_posizione_gist ON punti_vendita USING GIST (posizione);

-- Trova punti vendita entro 10 km da un centro
SELECT nome FROM punti_vendita
WHERE ST_DWithin(posizione, ST_MakePoint(13.36, 38.12)::geography, 10000);

-- Full-text con ranking (tsvector + GiST non è standard, ma con extension pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_nome_trgm ON punti_vendita USING GIST (nome gist_trgm_ops);
SELECT *, similarity(nome, 'Scarpe') AS sim
FROM punti_vendita
WHERE nome % 'Scarpe'
ORDER BY sim DESC;

Quando preferire GiST a GIN? GiST supporta operatori di ordinamento (es. <-> per distanza), mentre GIN no. Per full-text puro, GIN è più veloce sull'uguaglianza; GiST è migliore se serve anche il ranking per similarità. Per dati geografici, GiST è l'unica scelta sensata.

BRIN: l'indice dimenticato che risparmia spazio e tempo?

BRIN (Block Range INdex) non indicizza ogni valore, ma gruppi di blocchi fisici. Funziona solo su colonne ordinate fisicamente nel database (es. timestamp di creazione, ID incrementali). Il vantaggio: occupa pochissimo spazio (spesso 0.1% della tabella) e si aggiorna quasi gratis. Ideale per tabelle di log, transazioni, dati temporali.

-- Tabella log con timestamp
CREATE TABLE log_accessi (
    id BIGSERIAL,
    utente_id INT,
    azione TEXT,
    creato_il TIMESTAMP DEFAULT NOW()
);

-- Indice BRIN con 128 blocchi per range
CREATE INDEX idx_log_brin ON log_accessi USING BRIN (creato_il) WITH (pages_per_range = 128);

-- Query su intervallo di date (tipica nei log)
SELECT * FROM log_accessi WHERE creato_il BETWEEN '2026-03-01' AND '2026-03-31';

Attenzione: su colonne non ordinate fisicamente, BRIN è inutile. Funziona bene su SERIAL o TIMESTAMP inseriti in ordine cronologico. Non usarlo su colonne con valori casuali o spesso aggiornati.

Come creare un indice parziale per interrogazioni selettive?

Un indice parziale include solo le righe che soddisfano una condizione. Riduce dimensione e overhead di scrittura. Esempio classico: ordinare prodotti solo in stato 'attivo':

CREATE TABLE prodotti (
    id SERIAL,
    nome TEXT,
    prezzo NUMERIC,
    stato TEXT CHECK (stato IN ('attivo', 'disattivo', 'in_attesa'))
);

-- Indice parziale: solo prodotti attivi
CREATE INDEX idx_prodotti_attivi_prezzo ON prodotti (prezzo)
WHERE stato = 'attivo';

-- Query che usa l'indice
SELECT * FROM prodotti WHERE stato = 'attivo' AND prezzo > 50;

Un altro caso: escludere righe cancellate logicamente (deleted_at IS NULL). Con un indice parziale, le righe cancellate non ingombrano l'indice. Risultato: indice più piccolo, scritture più veloci, letture identiche.

Cosa fare adesso

1. Analizza le tue query lente: abilita pg_stat_statements e individua quelle che fanno full scan su colonne JSONB, full-text o geografiche.
2. Scegli l'indice giusto: per array/JSONB → GIN; per ricerche geografiche o similarità testuale → GiST; per dati temporali ordinati → BRIN; per filtri fissi su sottoinsiemi → parziale.
3. Crea un indice di prova: usa CREATE INDEX CONCURRENTLY per non bloccare le scritture. Verifica con EXPLAIN (ANALYZE, BUFFERS).
4. Monitora lo spazio: SELECT pg_size_pretty(pg_indexes_size('nome_tabella'));.
5. Rimuovi gli indici inutilizzati: pg_stat_user_indexes con idx_scan = 0.

Noi di Meteora Web lo facciamo ogni giorno: ottimizziamo database PostgreSQL per e-commerce e applicazioni web. Se vuoi approfondire, leggi la nostra guida pillar su PostgreSQL Avanzato.