PostgreSQL Performans İyileştirme: İndeksler, EXPLAIN ANALYZE ve Sorgu Optimizasyonu

Yavaş Sorgu mu? Asıl Sorun Muhtemelen Burada

Uygulaman iyi çalışıyor, ancak kullanıcı sayısı arttıkça API yanıtları yavaşlıyor. Veritabanı loglarına bakınca bazı sorgular 300–500ms sürüyor. Bu yazıda PostgreSQL'de performans sorunlarını sistematik olarak nasıl tespit edip çözeceğini, gerçek SQL örnekleriyle ele alacağız.

1. EXPLAIN ANALYZE: Sorguyu X-Ray ile Gör

PostgreSQL'in en güçlü araçlarından biri EXPLAIN ANALYZE. Bir sorguyu sadece çalıştırmakla kalmaz; sorgu planını, hangi adımın ne kadar sürdüğünü ve kaç satır işlendiğini gösterir.

-- Kötü sorgu: email ile kullanıcı arama
EXPLAIN ANALYZE
SELECT id, ad, email FROM kullanicilar
WHERE email = 'test@example.com';

-- Çıktı (indeks YOK iken):
-- Seq Scan on kullanicilar  (cost=0.00..2841.00 rows=1)
--   Filter: (email = 'test@example.com')
--   Rows Removed by Filter: 99999
-- Planning Time: 0.1 ms
-- Execution Time: 312.4 ms  ← Berbat!

-- Çözüm: İndeks oluştur
CREATE INDEX CONCURRENTLY idx_kullanicilar_email
  ON kullanicilar(email);

-- EXPLAIN ANALYZE tekrar çalıştır:
-- Index Scan using idx_kullanicilar_email  (cost=0.29..8.31 rows=1)
--   Index Cond: (email = 'test@example.com')
-- Execution Time: 0.08 ms  ← 4000x hızlandı! 🚀

Önemli: CONCURRENTLY anahtar kelimesi, indeks oluşturulurken tablonun kilitlenmemesini sağlar. Production ortamında her zaman kullanın.

2. Doğru İndeks Türünü Seç

PostgreSQL'de tek tip indeks yoktur. Kullanım senaryosuna göre doğru tipi seçmek kritiktir:

-- B-Tree (varsayılan): Eşitlik ve aralık sorguları için
CREATE INDEX idx_siparisler_tarih ON siparisler(olusturma_tarihi);
-- WHERE olusturma_tarihi > '2026-01-01'  ✓

-- GIN: Array ve JSONB sütunları için
CREATE INDEX idx_urunler_etiketler ON urunler USING GIN(etiketler);
-- WHERE etiketler @> ARRAY['indirim']  ✓

-- GiST: Tam metin arama ve coğrafi veriler için
CREATE INDEX idx_makaleler_icerik ON makaleler USING GiST(to_tsvector('turkish', icerik));

-- Kısmi (Partial) İndeks: Sadece belirli kayıtlar için — çok küçük, çok hızlı
CREATE INDEX idx_aktif_kullanicilar ON kullanicilar(email)
  WHERE aktif = true AND silinme_tarihi IS NULL;
-- Sadece aktif kullanıcıları indeksler, boyut %80 küçülür

-- Kapsayan (Covering) İndeks: Tabloyu hiç okumadan sonuç döner
CREATE INDEX idx_siparis_ozet ON siparisler(kullanici_id)
  INCLUDE (toplam_tutar, durum, olusturma_tarihi);
-- SELECT kullanici_id, toplam_tutar, durum FROM siparisler
-- WHERE kullanici_id = 42;  → Sadece indeks okunur, tablo okunmaz ✓

3. N+1 Sorgu Problemi ve JOIN Optimizasyonu

ORM kullananların en sık düştüğü tuzak N+1 problemidir. 100 sipariş için 101 sorgu atılır: 1 kez siparişler, sonra her sipariş için ayrı ayrı müşteri bilgisi.

-- ❌ Kötü: N+1 sorgu (100 sipariş = 101 DB çağrısı)
SELECT * FROM siparisler WHERE ay = '2026-09';
-- Sonra her satır için ayrı sorgu:
-- SELECT * FROM kullanicilar WHERE id = 1;
-- SELECT * FROM kullanicilar WHERE id = 2; ...

-- ✓ İyi: Tek sorguda JOIN ile çek
SELECT
  s.id          AS siparis_id,
  s.toplam_tutar,
  s.durum,
  k.ad          AS musteri_adi,
  k.email       AS musteri_email
FROM siparisler s
INNER JOIN kullanicilar k ON k.id = s.kullanici_id
WHERE DATE_TRUNC('month', s.olusturma_tarihi) = '2026-09-01'
ORDER BY s.olusturma_tarihi DESC;

-- ✓ Daha da iyi: CTE ile okunabilirlik + performans
WITH aylik_siparisler AS (
  SELECT * FROM siparisler
  WHERE olusturma_tarihi >= '2026-09-01'
    AND olusturma_tarihi < '2026-10-01'
)
SELECT
  as2.id,
  k.ad,
  SUM(as2.toplam_tutar) OVER (PARTITION BY k.id) AS musteri_toplami
FROM aylik_siparisler as2
JOIN kullanicilar k ON k.id = as2.kullanici_id;

4. VACUUM ve Tablo Şişmesi (Bloat)

PostgreSQL, güncellenen ve silinen satırları hemen diskten silmez. Bu "ölü satırlar" zamanla tablonun boyutunu şişirir. VACUUM bu ölü satırları temizler.

-- Tablo istatistiklerini gör (şişme var mı?)
SELECT
  schemaname,
  relname AS tablo,
  n_live_tup AS canli_satir,
  n_dead_tup AS olu_satir,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS olu_oran_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Manuel vacuum (production'da CONCURRENTLY kullan)
VACUUM ANALYZE kullanicilar;

-- Tam temizlik (tablo kilitlenir, dikkatli kullan)
VACUUM FULL siparisler;

-- Autovacuum ayarlarını hassas tablolar için özelleştir
ALTER TABLE yuksek_trafik_tablo SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- %1 ölü satırda tetikle (varsayılan %20)
  autovacuum_analyze_scale_factor = 0.005
);

5. Connection Pooling: PgBouncer

Her API isteği için yeni bir veritabanı bağlantısı açmak pahalıdır. PostgreSQL'de bir bağlantı ~5–10MB RAM tüketir. 200 eş zamanlı istek = 1–2GB sadece bağlantı için! Çözüm: PgBouncer ile bağlantı havuzu.

# pgbouncer.ini — Transaction pooling modu (en verimli)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction      # Her transaction'dan sonra bağlantıyı havuza iade et
max_client_conn = 1000       # Toplam istemci bağlantısı
default_pool_size = 20       # Gerçek DB bağlantı sayısı
reserve_pool_size = 5        # Acil durum havuzu
listen_port = 6432