PostgreSQL Advanced Indexing: Partial, Covering, BRIN and GIN
A one-column B-Tree index is the solution to 80% of performance problems. But 20% remaining requires more sophisticated tools: a partial index that indexes only the "active" rows reduces the index by 95%, a covering index completely eliminates accesses to the heap table, a BRIN on a time-series table takes up 100 times less space than a B-Tree, and a GIN enables full-text and array searches that no B-Tree can do. This guide covers all four types.
What You Will Learn
- Partial Index: Index only the rows that matter for the most frequent queries
- Covering Index with INCLUDE: enable index-only scan on SELECT queries
- BRIN Index: for time-series tables with millions of rows
- GIN Index: full-text search, array containment and JSONB queries
- How to choose the correct index type for each scenario
Partial Index: Index Only What Matters
A partial index indexes only rows that satisfy a WHERE condition. And the technique most powerful for scenarios where a small percentage of rows receive the large majority of queries: "pending" orders, "unread" messages, "in progress" tasks.
-- 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 for Partial Unique Values
-- 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 with INCLUDE: Eliminate Heap Access
An index-only scan reads data directly from the index, without accessing the heap table.
It is the fastest type of scan possible. To enable it, the index must contain all the
columns that appear in the SELECT. The clause INCLUDE add "extra" columns
in the index without including them in the B-Tree structure (therefore without impacting performance
of INSERT/UPDATE on the index itself).
-- 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!
When it INCLUDES it doesn't help
-- 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: For Time-Series Tables
BRIN (Block Range INdex) is designed for tables where data is physically sorted by a column (typically an insertion timestamp). Instead of indexing each value, BRIN only stores the min/max for each block of pages. It is enormously smaller than a B-Tree but less precise: ideal for date range scans.
-- 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: When to Choose
-- 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 and JSONB
GIN (Generalized Inverted INdex) is PostgreSQL's most versatile index type. It allows you to index composite content — arrays, tsvectors, JSONB — where each element of the content and indexed separately. Essential for full-text search and array queries.
GIN for 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 for 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 for 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)
Decision Map: Which Index to Choose
| Scenario | Index Type | Reason |
|---|---|---|
| Query on PK or FK (equality) | B-Tree (default) | Best for =, <, >, BETWEEN |
| Only a few "active" lines (e.g. pending) | Partial B-Tree | Index 100x smaller |
| SELECT with all columns in the index | B-Tree with INCLUDES | Index-Only Scan, no heap access |
| Append-only table with timestamp | BRIN | 1000x smaller than B-Tree |
| Full-text search in Italian/English | GIN on tsvector | Only way for @@ operator |
| Array containment (@>, &&) | GIN on TEXT[] | Only way for array operators |
| JSONB containment (@>) | GIN jsonb_path_ops | 30% more compact than default |
| LIKE 'pattern%' on text | B-Tree (varchar_pattern_ops) | LIKE prefix only |
| LIKE '%pattern%' on text | GIN with pg_trgm | Only way to LIKE on medium |
Index Maintenance
-- 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;
Conclusions
Choosing the right index type can make the difference between a query lasting 10 seconds and one that lasts 10 milliseconds. The partial index and covering index are the optimizations more impactful for operational queries. BRIN is revolutionary for logs and time-series data. GIN opens up search possibilities that no other type of index allows.
The next article in the series addresses table partitioning: the management technique tables with billions of rows without losing performance, with partition pruning and DROP Instant PARTITION for data lifecycle management.
Series: Database Engineering and Optimization
- Article 1: PostgreSQL 17/18 - News and Performance
- Article 2: EXPLAIN ANALYZE - Read the Query Plans
- Article 3 (this): Advanced Indexing - Partial, Covering, BRIN, GIN
- Article 4: Table Partitioning - Managing Billions of Rows
- Article 5: Connection Pooling - PgBouncer vs Pgpool-II