PostgreSQL İndeks Seçimi: B-tree, GIN ve BRIN Karşılaştırması

Mimari ve çalışma modeli

B-tree sıralı karşılaştırma yapan eşitlik, aralık, ORDER BY ve prefix desenleri için genel amaçlıdır. GIN, bir satırdaki birden çok anahtarı ters indeksleyerek array, JSONB ve full-text üyelik sorgularını hızlandırır. BRIN fiziksel blok aralıklarının özetini tutar; zaman gibi tablo yerleşimiyle korelasyonu yüksek, çok büyük tablolarda küçük indeksle geniş aralık elemesi yapar.

İndeks seçimi yalnız SELECT süresine göre yapılmaz. Her indeks INSERT/UPDATE/DELETE sırasında bakım, WAL, cache ve vacuum maliyeti ekler. GIN pending list ve fastupdate davranışı yazma gecikmesini toplu bakım anlarına kaydırabilir. BRIN küçük kalır fakat korelasyon bozulduğunda çok sayıda heap page recheck eder. B-tree daha kesin olsa da dev tabloda depolama ve cache baskısı büyür.

Çok sütunlu B-tree'de öndeki sütunlar kullanım etkinliğini belirler. GIN birden çok anahtar alanında farklı davranır; BRIN pages_per_range çözünürlük ile indeks boyutu arasında değiş tokuştur. EXPLAIN (ANALYZE, BUFFERS, WAL) planı, gerçek satır tahminini, heap block okumasını ve yazma etkisini birlikte ölçmek için kullanılmalıdır.

Tasarım parametreleri

Sorgu operatörü

Eşitlik/aralık/sıralama, üyelik/containment veya fiziksel aralık elemesini gerçek SQL ile sınıflandırın.

Korelasyon

BRIN için sütun değeri ile fiziksel satır sırası korelasyonunu ölçün.

Yazma oranı

İndeks sayısı, GIN pending list ve WAL artışını pik ingest altında ölçün.

Cache bütçesi

İndeks boyutunu shared buffers ve işletim sistemi cache'iyle birlikte değerlendirin.

Platformlardaki uygulama karşılıkları

Servis adları teknik karşılıklardır. Kapsam, varsayılanlar, bölge kullanılabilirliği ve işletim koşulları farklıdır; birebir aynı garanti anlamına gelmez.

Hesap ve uygulama örneği

2 TB olay tablosunda created_at fiziksel sırayla 0,98 korelasyonluysa 128 sayfalık BRIN aralıkları küçük indeksle tarih filtresini daraltabilir. Sorgu son 15 dakikayı seçiyorsa BRIN ve B-tree'yi aynı veri snapshot'ında buffers ve p95 süreyle karşılaştırın; yalnız indeks boyutuna bakmayın.

JSONB içinde etiket üyeliği arayan sorgu için GIN adaydır. Saniyede 5.000 insert altında fastupdate açık/kapalı pilotunda p95 insert, WAL MB/s, pending list temizleme süresi ve sorgu p95'i ölçülür. Okuma hızlanırken ingest SLA bozuluyorsa tasarım kabul edilmez.

Örnek komutlar: laboratuvar değerlerini değiştirin; kullanmadan önce yetkiyi ve yazılım sürümünü doğrulayın.

SELECT attname, n_distinct, correlation FROM pg_stats WHERE schemaname='public' AND tablename='events';
EXPLAIN (ANALYZE, BUFFERS, WAL) SELECT * FROM events WHERE created_at >= now()-interval '15 min';
CREATE INDEX CONCURRENTLY events_created_brin ON events USING brin(created_at) WITH (pages_per_range=128);
CREATE INDEX CONCURRENTLY events_tags_gin ON events USING gin(tags jsonb_path_ops);
SELECT relname,pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;

Hata teşhisi

Kabul ve doğrulama kontrolleri

1. En yüksek maliyetli sorguları operatör türüne göre sınıflandırın.
2. Sütun seçiciliği ve fiziksel korelasyonu ölçün.
3. Aday indeks boyutunu üretim ölçeğine projekte edin.
4. EXPLAIN ANALYZE BUFFERS ile gerçek heap erişimini kaydedin.
5. Pik yazmada p95 latency ve WAL MB/s etkisini ölçün.
6. Kullanılmayan veya yinelenen indeksleri kaldırmadan önce tam iş döngüsü gözlemleyin.

İlgili kavramlar

Birincil teknik dokümantasyon

• PostgreSQL current documentation — Index types
• PostgreSQL current documentation — Multicolumn indexes
• PostgreSQL current documentation — Examining index usage