PostgreSQL İndeksleme Stratejileri
Sorgu yavaşladığında ilk şüpheli genelde indekstir. Ya yok, ya yanlış türde, ya da hiç kullanılmıyor. PostgreSQL indeks yapısı zengindir; B-tree dışında da seçenekler var. Doğru olanı seçmek, sorgu süresini saniyelerden milisaniyelere indirebilir.
Aşağıdaki adımları kendi veritabanınızda deneyerek ilerleyin. Test için gerçek verinin bir kopyasını kullanın; boş tabloda indeks ölçmek yanıltır.
Kural: Önce ölç, sonra indeksle. Tahminle eklenen indeks, disk ve yazma maliyeti dışında bir şey getirmez.
İndeks Türlerini Tanımak ve Doğru Olanı Seçmek
PostgreSQL'de birden fazla indeks yöntemi vardır. Her biri farklı operatörleri destekler. Türü sorgunuzun WHERE koşuluna göre seçin.
B-tree: Varsayılan ve Çoğu Zaman Yeterli
- Ne zaman: Eşitlik ve aralık sorguları. =, <, >, BETWEEN, IN.
- Sıralamaya da yardım eder. ORDER BY created_at DESC gibi ifadelerde sıralama adımını atlatabilir.
- LIKE 'abc%' deseninde çalışır. Başta joker olan '%abc' deseninde çalışmaz.
- Sütun sırası önemlidir. Çok sütunlu indekste soldaki sütun kullanılmazsa indeks büyük olasılıkla devre dışı kalır.
Pratik: filtrede en çok kullanılan ve seçiciliği yüksek sütunu başa alın.
GIN: Metin Arama, JSONB ve Diziler
- Ne zaman: Bir satırda birden fazla değer varsa. JSONB alanları, text[] dizileri, tam metin arama vektörleri.
- JSONB içinde anahtar aramak için jsonb_path_ops sınıfı daha küçük indeks üretir.
- pg_trgm uzantısıyla birlikte kullanıldığında ILIKE '%kelime%' aramaları hızlanır.
- Yazma maliyeti B-tree'den yüksektir. Sık güncellenen tablolarda dikkat edin.
BRIN, Hash ve GiST
- BRIN: Çok büyük ve fiziksel olarak sıralı tablolar için. Zaman serisi log tablolarında ideal. İndeks boyutu çok küçüktür.
- Hash: Yalnızca eşitlik. Nadiren B-tree'den belirgin üstün olur; ihtiyaç netse kullanın.
- GiST: Geometrik veri, aralık türleri, çakışma kontrolü. tsrange ile randevu çakışması engellemek klasik örnektir.
| Senaryo | Tür |
|---|---|
| id = 42 | B-tree |
| tarih aralığı, milyarlarca satır | BRIN |
| JSONB anahtar sorgusu | GIN |
| %kelime% arama | GIN + pg_trgm |
| aralık çakışması | GiST |
Uygulama, Doğrulama ve Bakım
İndeks eklemek işin yarısı. Kullanıldığını kanıtlamak ve maliyetini takip etmek gerekir.
EXPLAIN ANALYZE ile Doğrulama
- Sorgunuzu EXPLAIN (ANALYZE, BUFFERS) ile çalıştırın.
- Çıktıda Seq Scan görüyorsanız ve tablo büyükse, indeks fırsatı var.
- Index Scan veya Index Only Scan hedefiniz. İkincisi en hızlısıdır; gereken tüm sütunlar indekste olduğunda oluşur.
- rows tahmini ile gerçek sayı arasında büyük fark varsa istatistikler bayattır. ANALYZE tablo_adi; çalıştırın.
- Aynı sorguyu iki kez çalıştırıp ikinci ölçümü not edin. İlk çalıştırmada önbellek soğuktur.
Kapsayıcı indeks denemesi yapın: CREATE INDEX ... (user_id) INCLUDE (status). Böylece tabloya gitmeden yanıt üretilebilir.
Kısmi ve İfade İndeksleri
- Kısmi indeks: Sadece ilgilendiğiniz satırları indeksler. Örnek: WHERE deleted_at IS NULL. Boyut küçülür, hız artar.
- Durum sütunlarında çok işe yarar. Aktif kayıtlar azsa yalnızca onları indeksleyin.
- İfade indeksi: Sorguda fonksiyon kullanıyorsanız indeks de aynı fonksiyonu içermeli. lower(email) ile arıyorsanız indeks de lower(email) üzerine kurulmalı.
- Aksi halde PostgreSQL indeksi kullanamaz ve tam tarama yapar.
Bakım ve Gereksiz İndeksleri Temizleme
- Üretimde indeks oluştururken CREATE INDEX CONCURRENTLY kullanın. Tabloyu yazmaya kilitlemez, ama daha uzun sürer.
- pg_stat_user_indexes tablosunda idx_scan değeri sıfır kalan indeksler adaydır. Ölçüm penceresinin yeterince uzun olduğundan emin olun.
- Aynı sütunla başlayan çakışan indeksler varsa birini silin. (a) ve (a, b) varsa ilki genelde gereksizdir.
- Şişen indeksleri REINDEX CONCURRENTLY ile yenileyin.
- Her yeni indeks, her INSERT ve UPDATE işlemini biraz yavaşlatır. Yazma ağırlıklı tablolarda sayıyı sınırlı tutun.
Bir uyarı: unique kısıt eklediğinizde arka planda indeks de oluşur. Aynı sütun için ayrıca indeks açmayın.
Kapanış
Sürecin özeti kısa:
- Yavaş sorguyu tespit et.
- EXPLAIN ANALYZE ile plana bak.
- Koşula uygun indeks türünü seç.