PostgreSQL Index Optimizasyonu: 7 Adımda Hızlan

Giriş

Uygulamanız büyüdükçe “dün hızlıydı, bugün neden yavaş?” sorusu kaçınılmaz olur. Çoğu zaman sorun sunucuda değil; sorguların yanlış index kullanması (ya da hiç kullanamaması) yüzünden veritabanı gereksiz yere tablo taraması (seq scan) yapıyordur.

Bu yazıda PostgreSQL index optimizasyonu için pratik, production’a uygun ve adım adım ilerleyen bir yöntem paylaşacağım. Amaç: rastgele index eklemek değil; ölçerek, doğru tipte index kullanarak ve bakım maliyetini yöneterek kalıcı hız kazanmak.

---

PostgreSQL Index Optimizasyonu Nedir, Neden Yapmalıyım?

PostgreSQL index optimizasyonu, sorguların en düşük maliyetle çalışmasını sağlayacak index stratejisini kurmak ve sürdürülebilir şekilde yönetmektir.

Bunu neden yapmalıyım?

• Sayfa açılışları ve API yanıt süreleri düşer (özellikle filtreleme/sıralama yapılan ekranlarda).
• CPU ve disk I/O azalır; aynı altyapı ile daha fazla kullanıcı taşınır.
• “Trafik arttı, DB kilitlendi” gibi krizlerin önemli bir kısmı önlenir.

Not: Index eklemek her zaman iyi değildir. Yazma (INSERT/UPDATE/DELETE) maliyeti artar ve disk büyür. Bu yüzden hedefe yönelik index gerekir.

---

1) Yavaş Sorguyu Bul: pg_stat_statements ile Görünür Kıl

Önce nerede kaybettiğinizi ölçün. PostgreSQL’de bunun en pratik yolu pg_stat_statements.

Kurulum / Aktivasyon

postgresql.conf içine:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Ardından extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

En çok zaman harcayan sorgular

SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Gerçek hayat örneği: Bir e-ticaret admin panelinde “sipariş arama” ekranı genelde en pahalı sorgudur: tarih aralığı + durum + müşteri + sıralama… Bu ekranlar doğru index yoksa aniden yavaşlar.

---

2) EXPLAIN (ANALYZE, BUFFERS) ile Asıl Sebebi Gör

Bulduğunuz sorguyu çalıştırmadan önce tahmin, çalıştırdıktan sonra gerçek sonuç görmek için:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Neye bakacaksınız?

• Seq Scan görüyorsanız tablo taranıyor olabilir.
• Rows Removed by Filter yüksekse index hedefi yanlış.
• BUFFERS altında shared read artıyorsa diskten okuma çok.

Kritik soru: Sorgu filtreleme mi yapıyor, sıralama mı yapıyor, ikisini birden mi? Index buna göre seçilir.

---

3) Doğru Index Türünü Seç: B-Tree, GIN, BRIN

PostgreSQL’de index türü seçiminde en çok kazandıran şey, veri tipine ve sorgu desenine göre doğru türü kullanmaktır.

Index Türü	Ne zaman?	Örnek kullanım
B-Tree	Eşitlik, aralık, ORDER BY	user_id, created_at, status
GIN	JSONB, array, full text, çoklu değer	jsonb @>, tags &&
BRIN	Çok büyük tabloda sıralı insert (zaman serisi)	log, event tabloları

LSI anahtar kelimeler: sorgu planı, index seçimi, query performance, seq scan, explain analyze.

---

4) Composite Index: Sıra Her Şeydir (Sol Öncelik Kuralı)

En sık hata: “Sorguda geçen her kolonu indexleyeyim.” Bunun yerine sorgu desenine uygun birleşik (composite) index kullanın.

Örnek sorgu:

SELECT id
FROM orders
WHERE user_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Bu sorgu için iyi bir index adayı:

CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

Neden bu sıra?

• WHERE user_id = ... en seçici olabilir (çoğu sistemde).
• status ikinci filtre.
• created_at DESC ORDER BY’ı destekler; PostgreSQL sıralama maliyetini azaltır.

İpucu: Composite index’te soldan başlayarak kullanılır. WHERE status='paid' tek başına çok çalışıyorsa ayrı index gerekebilir.

---

5) Partial Index: Sadece Gerekli Satırları Indexle

Bazı durumlarda tablonun küçük bir kısmı sürekli sorgulanır. Örneğin “aktif kullanıcılar”, “paid siparişler”, “silinmemiş kayıtlar”. Bu durumda partial index inanılmaz verimlidir.

Örnek: Soft delete kullanan tablo

CREATE INDEX CONCURRENTLY idx_customers_active_email
ON customers (email)
WHERE deleted_at IS NULL;

Bunu neden yapmalıyım?

• Index boyutu küçülür
• Yazma maliyeti düşer
• Sorgu daha hızlı doğru index’e yönlenir

Gerçek hayat örneği: CRM sisteminde kullanıcılar silinmez, deleted_at set edilir. Aramalar %99 “silinmemiş” kayıtlarda döner. Partial index ile aramalar gözle görülür hızlanır.

---

6) JSONB ve Arama: GIN Index ile Akıllı Hızlandırma

JSONB kolonlar modern sistemlerde çok yaygın. Ama JSONB filtreleri B-Tree ile hızlanmaz; çoğu zaman GIN gerekir.

Örnek tablo: events(payload jsonb)

Sorgu:

SELECT id
FROM events
WHERE payload @> '{"type": "payment", "provider": "iyzico"}';

GIN index:

CREATE INDEX CONCURRENTLY idx_events_payload_gin
ON events USING GIN (payload jsonb_path_ops);

jsonb_path_ops çoğu “@>” containment sorgusunda daha küçük ve hızlı index üretebilir.

---

7) Production’da Güvenli Uygulama: CONCURRENTLY, VACUUM, İstatistik

Index eklemek kadar doğru şekilde yayınlamak da önemli.

CONCURRENTLY ile kilit riskini azaltın

Büyük tabloda normal CREATE INDEX yazmaları uzun süre kilitleyebilir. Production’da genelde:

CREATE INDEX CONCURRENTLY idx_name ON table_name (col);

İstatistikleri güncel tutun

Planner doğru karar versin diye:

ANALYZE orders;

Şişen indexleri izleyin

• Çok update alan tablolarda bloat oluşabilir.
• Autovacuum ayarları yetersiz kalabilir.

Pratik kontrol soruları:

• En pahalı 10 sorgu değişti mi?
• Yeni index gerçekten kullanılıyor mu? (EXPLAIN ile)
• Yazma performansı düştü mü? (insert/update latency)

---

Hızlı Kontrol Listesi (Özet Tablo)

Belirti	Muhtemel sebep	Çözüm
Seq Scan ve yüksek süre	Uygun index yok	B-Tree / composite index
Filtre var ama index seçmiyor	Düşük selectivity / yanlış sıra	Composite sırasını düzelt
Sadece “aktif” kayıtlar sorgulanıyor	Gereksiz büyük index	Partial index
JSONB aramaları yavaş	Yanlış index türü	GIN + doğru opsiyon
Büyük tabloda index eklerken sistem duruyor	Kilitlenme	CREATE INDEX CONCURRENTLY

---

Sık Sorulan Sorular (FAQ)

1) PostgreSQL index optimizasyonu ne kadar kazandırır?

Sorguya göre değişir; doğru index ile 10x ve üzeri hızlanma görülebilir, özellikle filtre+order by kombinasyonlarında.

2) Çok index eklemek her zaman iyi mi?

Hayır. Her index yazma maliyetini ve disk kullanımını artırır. Kullanılmayan indexleri düzenli izlemek gerekir.

3) Composite index mi, ayrı ayrı index mi?

Sorgu sürekli aynı kolon kombinasyonuyla çalışıyorsa composite index genelde daha iyidir. Farklı kombinasyonlar varsa ayrı indexler mantıklı olabilir.

4) Index ekledim ama PostgreSQL kullanmıyor, neden?

İstatistikler eski olabilir (ANALYZE). Ayrıca filtre çok seçici değilse planner seq scan seçebilir. EXPLAIN (ANALYZE, BUFFERS) ile teyit edin.

5) JSONB için hangi index önerilir?

Çoğu @> sorgusu için GIN (jsonb_path_ops) iyi başlangıçtır. Daha karmaşık path sorgularında farklı yaklaşımlar gerekebilir.

---

Sonuç

Bu yazıda PostgreSQL index optimizasyonu için ölçümden başlayıp doğru index türü seçimine, composite/partial index stratejilerine ve production’da güvenli yayınlamaya kadar 7 adımı ele aldık.

Bir sonraki adım: Kendi projenizde en pahalı 5 sorguyu pg_stat_statements ile çıkarın, her biri için EXPLAIN (ANALYZE, BUFFERS) alın ve en az bir sorguda composite ya da partial index deneyin.

Denediğiniz sorguyu ve EXPLAIN çıktısındaki değişimi yorumlara yazın; birlikte daha iyi bir index stratejisi çıkarabiliriz.