PostgreSQL İndeksleme ve Sorgu Optimizasyonu

PostgreSQL performans sorunları genellikle veri büyüdükten sonra görünür olur. Küçük tabloda hızlı çalışan sorgu, milyonlarca satırda sequential scan yüzünden API yanıt süresini artırabilir. Bu nedenle indeksleme, sorgu kalıbını ve veri dağılımını anlamadan yapılmamalıdır.

B-Tree indeks

B-Tree varsayılan indeks türüdür ve eşitlik, sıralama ve aralık filtrelerinde çoğu zaman yeterlidir. Kullanıcı e-postası, sipariş numarası veya tarih alanı gibi sorgularda iyi sonuç verir. Ancak her kolona indeks eklemek yazma maliyetini ve disk kullanımını artırır.

Composite ve partial index

Birden fazla kolonla filtrelenen sorgularda kolon sırası önemlidir. Sorgu çoğunlukla customer_id, status ve created_at üzerinden ilerliyorsa composite index buna göre tasarlanmalıdır. Yalnızca aktif kayıtlar sık sorgulanıyorsa partial index daha küçük ve hedefli çözüm sunar.

GIN indeks

JSONB, array ve full-text search senaryolarında GIN indeks kullanılabilir. Fakat yazma maliyetini artırabileceği için gerçekten sorgulanan alanlarda tercih edilmelidir. Arama kalitesi için dil ve sıralama beklentisi ayrıca değerlendirilmelidir.

EXPLAIN ANALYZE

İndeksin işe yarayıp yaramadığını anlamanın yolu EXPLAIN ANALYZE çıktısını okumaktır. Sequential Scan her zaman kötü değildir; küçük tablolarda en hızlı seçenek olabilir. Önemli olan gerçek süre, satır tahmini ve kullanılan planın sorguyla uyumudur.

Sonuç

Doğru indeks, sorgunun niyetini ve veri dağılımını yansıtan indekstir. Ölçmeden indeks eklemek yerine plan okuyarak ilerlemek PostgreSQL performansında daha kalıcı sonuç verir.

Pratik uygulama notu

İndeks eklemeden önce yavaş sorgunun tam halini ve parametre dağılımını görmek gerekir. Development ortamındaki küçük veriyle karar vermek yanıltıcıdır. Production kopyası veya benzer hacimde test verisi üzerinde EXPLAIN ANALYZE almak, değişiklik öncesi ve sonrası süreleri karşılaştırmak daha güvenilir sonuç verir.

Önce Gerçek Sorguyu ve Planı Kaydedin

İndeks eklemeden önce yavaş sorguyu uygulamanın gönderdiği gerçek parametrelerle inceleyin. PostgreSQL planlayıcısı veri dağılımına göre farklı plan seçebilir; geliştirme ortamındaki küçük ve homojen veri yanıltıcıdır. EXPLAIN (ANALYZE, BUFFERS, VERBOSE) sorguyu gerçekten çalıştırır ve tahmini satır sayısıyla gerçekleşen satır sayısını, disk/bellek bloklarını ve her düğümün süresini gösterir. Yazma yapan sorgularda önce güvenli bir test veritabanı kullanın.

Tahmin edilen satır sayısı ile gerçek sayı arasında büyük fark varsa istatistikler eski veya veri dağılımı düzensiz olabilir. ANALYZE çalıştırmak, ilgili kolonda statistics target artırmak ya da ilişkili kolonlar için extended statistics oluşturmak planlayıcının daha doğru karar vermesini sağlar. Her kötü planın çözümü yeni indeks değildir.

Birleşik İndekste Kolon Sırası

WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50 sorgusu için (tenant_id, status, created_at DESC) indeksi filtreleme ve sıralamayı birlikte destekleyebilir. Kolon sırası sorgu biçimine göre seçilir. Yalnızca created_at indekslemek tenant filtresinden sonra çok fazla satır taratabilir; her kolon için ayrı indeks de çoğu zaman birleşik indeksin yerini tutmaz.

Az sayıda değeri olan boolean veya status kolonunu tek başına indekslemek genellikle seçici değildir. Ancak yalnızca aktif kayıtlar okunuyorsa partial index daha küçük ve ucuz olabilir:

CREATE INDEX CONCURRENTLY idx_orders_open_by_tenant
ON orders (tenant_id, created_at DESC)
WHERE status = 'OPEN';

CONCURRENTLY üretimde uzun tablo kilidini azaltır fakat daha uzun sürer, ek kaynak tüketir ve transaction bloğu içinde çalışmaz. Yayından önce disk alanı ile başarısız indeks kalıntılarını izlemeniz gerekir.

İndeksin Yazma Maliyeti

Her indeks INSERT, UPDATE ve DELETE işlemlerinde güncellenir. Fazla indeks yazma gecikmesini, WAL hacmini, replikasyon trafiğini ve vacuum yükünü artırır. Kullanılmayan indeksleri yalnızca pg_stat_user_indexes.idx_scan sıfır göründüğü için hemen silmeyin; istatistiklerin reset tarihi, aylık rapor sorguları ve constraint gereksinimleri kontrol edilmelidir.

Güncellenen kolonlar indeks içinde yer alıyorsa HOT update olasılığı düşer ve tablo/indeks şişmesi artabilir. Autovacuum'un büyük ve yoğun güncellenen tablolara yetişip yetişmediğini izleyin. Sorun şişmeyse sorgu metnini değiştirmek geçici rahatlama sağlasa da bakım ayarları ve veri yaşam döngüsü çözülmeden problem geri döner.

Sayfalama ve Büyük Sonuç Kümeleri

OFFSET 100000 LIMIT 50 veritabanının ilk yüz bin satırı bulup atmasını gerektirir. Sıralama kararlıysa keyset pagination kullanın:

SELECT id, created_at, total
FROM orders
WHERE tenant_id = :tenant
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Bu yaklaşım uygun (tenant_id, created_at DESC, id DESC) indeksiyle sayfa derinliğinden bağımsız daha tutarlı süre verir. Kullanıcı doğrudan belirli sayfa numarasına gitmek zorundaysa offset gerekebilir; ürün kararını teknik maliyetle birlikte değerlendirin.

Değişikliği Güvenle Yayınlama

• Yavaş sorgunun başlangıç planını ve p95 süresini kaydedin.
• İndeks boyutunu, oluşturma süresini ve yazma yüküne etkisini test edin.
• Migration için timeout ve geri dönüş planı hazırlayın.
• Yayından sonra buffer hit oranı yerine doğrudan sorgu süresi ve kaynak tüketimini karşılaştırın.
• İndeks beklenen planı sağlamıyorsa istatistik, parametreli plan ve veri dağılımını yeniden inceleyin.

Sorgu optimizasyonu tek seferlik bir temizlik değildir. Veri büyüdükçe seçicilik ve kullanım biçimi değişir; bu nedenle kritik sorguların planlarını sürüm ve veri hacmiyle birlikte düzenli takip etmek gerekir.