PostgreSQL Gelişmiş İndeksleme: Kısmi, Kaplama, BRIN ve GIN
Tek sütunlu bir B-Tree dizini performans sorunlarının %80'inin çözümüdür. Ama %20 geri kalanı daha karmaşık araçlar gerektirir: yalnızca "aktif" satırları indeksleyen kısmi bir indeks endeksi %95 oranında azaltır, kaplama endeksi yığın tablosuna erişimi tamamen ortadan kaldırır, Zaman serisi tablosundaki bir BRIN, B-Tree'den 100 kat daha az yer kaplar ve bir GIN şunları sağlar: hiçbir B-Tree'in yapamayacağı tam metin ve dizi aramaları. Bu kılavuz dört türün tamamını kapsamaktadır.
Ne Öğreneceksiniz
- Kısmi Dizin: Yalnızca en sık yapılan sorgular için önemli olan satırları dizine ekleyin
- Dizini INCLUDE ile Kaplama: SELECT sorgularında yalnızca dizin taramasını etkinleştir
- BRIN Endeksi: milyonlarca satır içeren zaman serisi tabloları için
- GIN Dizini: tam metin araması, dizi sınırlaması ve JSONB sorguları
- Her senaryo için doğru dizin türü nasıl seçilir?
Kısmi Endeks: Yalnızca Önemli Olan Endeks
Kısmi dizin yalnızca WHERE koşulunu karşılayan satırları dizine ekler. Ve teknik Satırların küçük bir yüzdesinin büyük çoğunluğu aldığı senaryolar için en güçlüsü sorguların sayısı: "bekleyen" siparişler, "okunmamış" mesajlar, "devam eden" görevler.
-- 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!)
Kısmi Benzersiz Değerler için Kısmi Dizin
-- 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
INCLUDE ile Kapsama Dizini: Yığın Erişimini Ortadan Kaldırın
Yalnızca dizin taraması, yığın tablosuna erişmeden verileri doğrudan dizinden okur.
Mümkün olan en hızlı tarama türüdür. Bunu etkinleştirmek için dizinin tüm bilgileri içermesi gerekir.
SELECT'te görünen sütunlar. Madde INCLUDE "ekstra" sütunlar ekle
B-Tree yapısına dahil etmeden dizine ekleyin (bu nedenle performansı etkilemeden)
dizinin kendisinde INSERT/UPDATE).
-- 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!
DAHİL olduğunda faydası olmuyor
-- 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 Endeksi: Zaman Serisi Tabloları İçin
BRIN (Blok Aralığı INdex), verilerin fiziksel olarak sıralandığı tablolar için tasarlanmıştır. bir sütun (genellikle bir ekleme zaman damgası). Her değeri indekslemek yerine, BRIN yalnızca her sayfa bloğu için min/maks değerlerini saklar. Birinden çok daha küçüktür B-Tree ancak daha az hassas: tarih aralığı taramaları için idealdir.
-- 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: Ne Zaman Seçilmeli
-- 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 Dizini: Tam Metin Arama, Dizi ve JSONB
GIN (Genelleştirilmiş Ters Çevrilmiş INdex), PostgreSQL'in en çok yönlü dizin türüdür. Şunları yapmanızı sağlar: bileşik içeriği indeksle - diziler, tsvektörler, JSONB - burada içeriğin her bir öğesi ve ayrı ayrı indekslenir. Tam metin araması ve dizi sorguları için gereklidir.
Tam Metin Arama için GIN
-- 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;
Dizi Sınırlaması için GIN
-- 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"
JSONB için GIN
-- 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)
Karar Haritası: Hangi Endeksi Seçmeli
| Senaryo | Dizin Türü | Sebep |
|---|---|---|
| PK veya FK ile ilgili sorgulama (eşitlik) | B-Ağacı (varsayılan) | =, <, >, ARASI için en iyisi |
| Yalnızca birkaç "aktif" satır (ör. beklemede) | Kısmi B Ağacı | Dizin 100 kat daha küçük |
| Dizindeki tüm sütunlarla birlikte SEÇ | DAHİL OLAN B-Tree | Yalnızca Dizin Taraması, yığın erişimi yok |
| Zaman damgasını içeren salt ekleme tablosu | BRİN | B-Tree'den 1000 kat daha küçük |
| İtalyanca/İngilizce tam metin araması | Tsvector'da GIN | @@ operatörü için tek yol |
| Dizi koruması (@>, &&) | TEXT'te GIN[] | Dizi operatörleri için tek yol |
| JSONB koruması (@>) | GIN jsonb_path_ops | Varsayılandan %30 daha kompakt |
| Metindeki 'desen%' BEĞEN | B Ağacı (varchar_pattern_ops) | Yalnızca LIKE öneki |
| Metindeki '%pattern%' BEĞEN | pg_trgm ile GIN | Medium'da BEĞENmenin tek yolu |
Dizin Bakımı
-- 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;
Sonuçlar
Doğru dizin türünü seçmek, 10 saniye süren bir sorgu arasındaki farkı yaratabilir ve 10 milisaniye süren bir tane. Kısmi indeks ve kaplama indeksi optimizasyonlardır operasyonel sorgular için daha etkilidir. BRIN, günlükler ve zaman serisi verileri için devrim niteliğindedir. GIN, başka hiçbir indeks türünün izin vermediği arama olanaklarını açar.
Serideki bir sonraki makale tablo bölümlendirmeyi ele alıyor: yönetim tekniği bölüm budama ve DROP ile performansı kaybetmeden milyarlarca satır içeren tablolar Veri yaşam döngüsü yönetimi için Anında BÖLÜM.
Seri: Veritabanı Mühendisliği ve Optimizasyonu
- Makale 1: PostgreSQL 17/18 - Haberler ve Performans
- Madde 2: ANALİZİ AÇIKLAMAK - Sorgu Planlarını Okuyun
- Madde 3 (bu): Gelişmiş İndeksleme - Kısmi, Kaplama, BRIN, GIN
- Madde 4: Tablo Bölümleme - Milyarlarca Satırın Yönetilmesi
- Madde 5: Bağlantı Havuzu Oluşturma - PgBouncer ve Pgpool-II