Veri tabanı performansı neden “indeks + plan okuma” ile başlar?

Veri tabanı optimizasyonu çoğu zaman tek bir “sihirli ayar” değil; iş yükünüzü ölçmek, yürütme planını anlamak ve doğru indeksi doğru sorguya eklemekten oluşan bir döngüdür. PostgreSQL gibi maliyet-temelli (cost-based) optimizöre sahip sistemlerde optimizör, farklı erişim yollarını (seq scan, index scan, join tipleri vb.) tahmini maliyete göre seçer. Bu yaklaşımın teorik temeli, maliyet-temelli erişim yolu seçimini modelleyen klasik çalışmalara kadar uzanır (Selinger et al., 1979).

Bu kılavuzda iki hedef var:

• İndeks türlerini (B-tree, GIN, GiST, BRIN, Hash, SP-GiST) hangi veri ve sorgu örüntülerinde düşünmeniz gerektiğini netleştirmek (PostgreSQL Index Types).
• EXPLAIN ve EXPLAIN ANALYZE çıktısını okuyup, “neden yavaş?” sorusunu sistematik şekilde yanıtlamak (PostgreSQL EXPLAIN).

1) Önce ölç: Problemi tanımlama kontrol listesi

İndeks eklemeden önce sorunun gerçekten sorgu planından mı, veri modelinden mi, I/O’dan mı, yoksa veri dağılımından mı kaynaklandığını anlamak gerekir. Pratik başlangıç kontrol listesi:

• Hangi sorgu yavaş? Tam SQL’i ve parametreleri (örnek değerlerle) toplayın.
• Ne kadar yavaş ve ne sıklıkta? Tek sefer mi, sürekli mi? P95/P99 gibi uç değerler mi sorun?
• Sonuç kümesi büyüklüğü: 10 satır mı dönüyor, 10 milyon mu?
• Tablo boyutları: satır sayısı ve veri dağılımı (özellikle filtrelenen kolonlar).
• Yazma oranı: INSERT/UPDATE/DELETE yoğun bir tabloda “fazla indeks” maliyetli olabilir.

Not: İndeksler genellikle okuma sorgularını hızlandırır; ancak her ek indeks yazma işlemlerini ve depolama/bakım maliyetini artırabilir. Bu yüzden hedef, “her kolona indeks” değil; iş yüküne göre doğru indeks yaklaşımıdır (genel prensipler için: Use The Index, Luke!).

2) PostgreSQL indeks türleri: Ne zaman hangisi?

PostgreSQL birden fazla indeks erişim yöntemini destekler. Resmi dokümantasyon, hangi indeks türünün hangi veri ve operatör sınıflarıyla daha uygun olduğunu açıklar (Index Types).

2.1 Hızlı karar tablosu (özet)

Kaynak: PostgreSQL — Index Types.

2.2 B-tree: Varsayılan ve çoğu sorgu için ilk aday

B-tree, PostgreSQL’de en genel amaçlı indeks türüdür. Eşitlik ve aralık filtreleri ile sıralama (ORDER BY) kullanımında özellikle yaygındır (Index Types).

Tipik örnek:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

Operasyon notu: Üretimde indeks oluşturma stratejisi (ör. kilitlenme etkisi, ek süre/maliyet) sürümünüze ve komut seçeneklerine göre değişir. Kullandığınız PostgreSQL sürümünün resmi dokümantasyonunu esas alın.

2.3 BRIN: Çok büyük ve “fiziksel olarak sıralı” tablolarda düşük maliyetli seçenek

BRIN (Block Range INdex), özellikle çok büyük tablolarda ve verinin disk üzerindeki fiziksel sıralanmasının sorgu filtreleriyle ilişkili olduğu durumlarda düşük depolama maliyetiyle fayda sağlayabilir. Veri dağılımı rastgele ise veya fiziksel korelasyon zayıfsa, BRIN beklenen faydayı vermeyebilir (Index Types; pratik notlar için: Cybertec PostgreSQL Indexing PDF).

Tipik örnek: Zaman serisi benzeri, ekleme sırası ile tarih kolonunun korelasyonlu olduğu bir tabloda:

CREATE INDEX idx_events_created_at_brin ON events USING brin (created_at);

2.4 GIN ve GiST: “B-tree yetmiyor” dediğiniz yer

Çok-değerli veri tiplerinde (array, jsonb) üyelik aramaları veya full-text arama gibi senaryolarda B-tree genellikle uygun değildir. Bu noktada GIN ve GiST gibi yöntemler devreye girer (Index Types). Gerçek iş yükü davranışı (güncelleme oranı, indeks boyutu, seçicilik) önemli olduğundan, seçimi mutlaka ölçerek doğrulayın (pratik bakış için: Use The Index, Luke!).

GIN örneği (jsonb):

CREATE INDEX idx_docs_payload_gin ON documents USING gin (payload);

GiST örneği (özellikle uzamsal/benzerlik aramaları) kullanılan veri tipi ve operatör sınıfına göre değişir; detaylar ilgili ekosisteme (ör. uzamsal eklentiler) bağlıdır.

2.5 SP-GiST: “space-partitioned” indeks çerçevesi (tablo partitioning değil)

SP-GiST (Space-Partitioned Generalized Search Tree), veriyi uzayda bölerek (non-overlapping partitions) organize eden indeks yapılarını destekleyen bir çerçevedir. Bu, PostgreSQL’in tablo bölümlendirmesi (partitioning) özelliğiyle aynı şey değildir. SP-GiST’in tipik örnekleri; trie (ön ek/prefix yapıları), k-d tree ve quadtree gibi bölümlü ağaçlardır (Index Types).

3) EXPLAIN: PostgreSQL’in sorguyu nasıl çalıştıracağını görme

EXPLAIN, PostgreSQL’in seçtiği yürütme planını gösterir. EXPLAIN ANALYZE ise sorguyu gerçekten çalıştırır ve plan düğümlerine ait gerçekleşen süreleri/gerçek satır sayılarını raporlar (PostgreSQL EXPLAIN).

3.1 Güvenli başlangıç: EXPLAIN ile başla, sonra ANALYZE

Pratik bir sıra:

1. EXPLAIN ile planı görün (sorgu çalışmadan tahmini maliyetleri incelersiniz).
2. Gerekirse EXPLAIN (ANALYZE, BUFFERS) ile gerçek ölçümü alın.

Üretim uyarısı: EXPLAIN ANALYZE ifadenin kendisini çalıştırır. Bu nedenle veri değiştiren komutlarda (INSERT/UPDATE/DELETE) değişiklikler uygulanır; ağır SELECT’ler de üretim kaynaklarını tüketebilir. Detaylar ve seçenekler için: EXPLAIN dokümantasyonu.

3.2 Örnek: EXPLAIN (ANALYZE, BUFFERS) komutu

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;

Bu tür bir sorguda tipik hedefler:

• customer_id filtresi için uygun indeks var mı?
• ORDER BY için indeks sıralaması yardımcı oluyor mu?
• BUFFERS kısmında çok sayıda blok okunuyor mu (I/O baskısı)?

3.3 Plan okuma: En sık görülen düğümler (kural-odaklı değil, ölçüm-odaklı)

• Seq Scan: tabloyu baştan sona okur. Küçük tabloda normal olabilir; büyük tabloda ve seçicilik yüksekse iyileştirme fırsatı olabilir.
• Index Scan: indeks üzerinden erişim.
• Index Only Scan: plan “only” dese bile, gerçekten heap’e gidilip gidilmediğini EXPLAIN ANALYZE çıktısındaki ilgili alanlardan (örn. varsa “Heap Fetches”) ve BUFFERS bölümünden doğrulayın (EXPLAIN).
• Bitmap Index Scan + Bitmap Heap Scan: Pratikte, birçok eşleşmenin tek tek erişiminden ziyade toplu heap erişiminin avantajlı olabildiği bazı durumlarda görülür; hangi eşikte daha iyi olacağı veri dağılımına bağlıdır (kendi verinizle ölçün).
• Nested Loop / Hash Join / Merge Join: join stratejileri. Tahmin–gerçek satır farkı büyüdükçe strateji seçimi değişebilir.

EXPLAIN’de kritik karşılaştırma, her düğümdeki tahmini satır sayısı ile ANALYZE sonrası görülen gerçek satır sayısı arasındaki farktır. Büyük sapmalar genellikle veri dağılımını iyi temsil etmeyen tahminlere işaret edebilir; bu noktada optimizasyonu “indeks ekle” refleksinden önce ölçümle ilerletin.

4) Sorgu optimizasyonu için adım adım “İndeks + EXPLAIN” döngüsü

Adım 1: Hedef sorguyu sabitleyin

Parametreli sorgular için (ör. customer_id) gerçek hayattaki tipik değerleri seçin. Amaç, “tek seferlik en iyi durum” değil; gerçek iş yüküne yakın bir testtir.

Adım 2: EXPLAIN ile mevcut planı çıkarın

Önce ANALYZE olmadan bakın. Ardından gerekiyorsa ANALYZE ve BUFFERS ekleyin (EXPLAIN).

Adım 3: Planın darboğazını bulun

• En çok zaman harcayan düğüm hangisi?
• En büyük satır genişlemesi (row count explosion) nerede?
• Buffer okumaları yoğun mu? (BUFFERS)

Adım 4: İndeks adayını “sorgu biçimine” göre seçin

Genel pratik (kural değil, başlangıç hipotezi):

• WHERE col = ... ise: çoğunlukla B-tree (Index Types).
• Birden çok filtre varsa: uygun bileşik (composite) indeks değerlendirilebilir; kolon sırası sorgu örüntüsüyle uyumlu olmalıdır.
• ORDER BY + LIMIT kombinasyonunda: doğru sırayla indeks bazı iş yüklerinde büyük kazanç sağlayabilir; planla doğrulayın.
• jsonb/array/tsvector içerik aramalarında: GIN sıklıkla başlangıç noktasıdır; yine de iş yüküne göre test gerekir (Index Types).
• çok büyük ve fiziksel olarak korelasyonlu tablo: BRIN değerlendirilebilir (Index Types).

Adım 5: İndeksi ekleyin ve tekrar ölçün

İndeks ekledikten sonra aynı sorguda EXPLAIN ANALYZE ile karşılaştırın. Yalnızca süreye değil, okunan blok sayısına ve gerçek satır sayılarına da bakın (EXPLAIN).

Adım 6: Yazma maliyetini unutmayın

Okuma kazanımı sağlarken yazma gecikmesi artabilir. İş yükünüz yazma ağırlıklıysa, indeks sayısı ve türü daha kritik hale gelir. Bu dengeyi, “neden indeks işe yaramadı?” türü yaygın senaryolarla birlikte düşünmek faydalıdır (Use The Index, Luke!).

5) PostgreSQL 18: Neler sürüme özgü, neler genel?

Genel (çoğu sürümde): İndeks türleri seçimi (B-tree/GIN/GiST/BRIN/SP-GiST), EXPLAIN ve EXPLAIN ANALYZE ile plan okuma yaklaşımı.
PostgreSQL 18 odaklı: Sürüm notlarında yer alan I/O altyapısı ve istatistik/izleme tarafındaki yenilikler. Bu nedenle, PG18’de plan analizi yaparken daha fazla I/O sinyali görebilirsiniz; ancak çıktının alanları sürüme göre farklılık gösterebilir.

PG18’e özgü noktalar için önce sürüm notlarını, ardından EXPLAIN çıktısı ve seçenekleri için ilgili dokümantasyonu kontrol edin: PostgreSQL 18 Release Notes, EXPLAIN (v18).

6) Yaygın tuzaklar ve pratik çözümler

6.1 “İndeks var ama kullanılmıyor”

• Seçicilik düşük olabilir: Çok fazla satır eşleşiyorsa seq scan daha ucuz olabilir (planla doğrulayın).
• Fonksiyon/CAST kullanımı: WHERE koşulunda kolona fonksiyon uyguluyorsanız, normal indeks beklediğiniz gibi kullanılmayabilir; gerekirse farklı indeks stratejileri gerekir.
• Yanlış kolon sırası: Bileşik indekslerde kolon sırası önemlidir; sorgu filtreleri ve sıralama ihtiyacıyla uyumlu olmalıdır.

6.2 “EXPLAIN ANALYZE üretimde riskli mi?”

Evet, olabilir. Çünkü ifade çalışır ve kaynak tüketir; veri değiştiren ifadelerde sonuçlar kalıcıdır. Bu yüzden ağır sorgular için test ortamı veya kontrollü zaman penceresi tercih edilir (EXPLAIN).

6.3 GIN mi GiST mi? (Hızlı karar yaklaşımı)

Tek bir “daima doğru” cevap yoktur; veri tipi, operatörler, güncelleme oranı ve indeks boyutu gibi etkenlerle değişir. Başlangıç için pratik yaklaşım:

• Array/jsonb içeriğinde üyelik aramaları ve full-text aramada önce GIN tarafını araştırın (Index Types).
• Uzamsal/benzerlik gibi özel arama tiplerinde, ilgili operatör sınıfı ile GiST sık görülür (Index Types).
• Mutlaka EXPLAIN (ANALYZE, BUFFERS) ile kendi verinizde ölçün (EXPLAIN).

7) Uygulanabilir mini kontrol listesi (kopyala-kullan)

• Sorguyu ve parametrelerini kaydet.
• EXPLAIN ile planı al.
• EXPLAIN (ANALYZE, BUFFERS) ile ölç (mümkünse testte).
• Tahmin (rows) vs gerçek satır farklarını işaretle.
• İndeks adayını sorgu örüntüsüne göre seç (B-tree/GIN/GiST/BRIN/SP-GiST).
• İndeksi ekle, aynı ölçümü tekrarla.
• Okuma kazanımı yanında yazma maliyetini ve indeks boyutunu da izle.
• Gereksiz indeksleri düzenli gözden geçir.

Sonuç

Veri tabanı performans optimizasyonunda sürdürülebilir yaklaşım, planı görmeden tahmin yürütmemek ve indeks kararlarını iş yüküyle doğrulamaktır. PostgreSQL’in zengin indeks ekosistemi (B-tree, GIN, GiST, BRIN, SP-GiST vb.) doğru problem için doğru seçimi yaptığınızda güçlüdür; ancak yanlış seçim de maliyetli olabilir. EXPLAIN ve EXPLAIN ANALYZE çıktısını düzenli okumak, hem doğru indeksi seçmenize hem de optimizörün neden o planı tercih ettiğini anlamanıza yardımcı olur (EXPLAIN).