POSTGRESQL PERFORMANS: INDEX, VACUUM/ANALYZE VE AUTOVACUUM AYARLARI

PostgreSQL’de “yavaşladı” hissi çoğu zaman tek bir sebepten değil, küçük birikimlerin aynı anda görünür olmasından kaynaklanır: yanlış indeks seçimi, güncel olmayan istatistikler, büyüyen bloat ve autovacuum’un iş yüküne uyumsuz kalması. İyi haber şu: Bu başlıkların her biri ölçülebilir ve yönetilebilir.

Bu rehberde indeks stratejisini nasıl kuracağınızı, VACUUM/ANALYZE ile istatistikleri nasıl doğru zamanda güncelleyeceğinizi ve autovacuum parametrelerini gerçekçi senaryolara göre nasıl ayarlayacağınızı adım adım ele alacağız. Hedef, sadece “hızlandı” demek değil; neden hızlandığını izleyebilmek.

Okurken aklınızda tutmanız gereken iki temel prensip var: (1) PostgreSQL performansı, planlayıcının doğru karar vermesine dayanır; (2) tablo büyüdükçe bakım yükü de artar, bu yüzden bakım ayarları da büyümeye göre evrilmelidir. Daha kapsamlı uygulamalar için PostgreSQL eğitimi içeriğine de göz atabilirsiniz.

Performansın Temeli: Ölçüm, Plan ve Hedef Metrikler

Optimizasyon, ölçmeden yapıldığında şansa kalır. PostgreSQL’de ilk hedef; sorgu süresi, I/O davranışı ve kilit beklemeleri gibi metrikleri birlikte izlemek olmalı. Özellikle üretimde “hangi sorgular pahalı” sorusunun cevabı, doğru yere dokunmanızı sağlar.

pg_stat_statements uzantısı, toplam süre, çağrı sayısı ve ortalama süre gibi metriklerle en maliyetli sorguları bulmanızda çok etkilidir. Bunun üzerine EXPLAIN (ANALYZE, BUFFERS) çıktılarıyla planın nerede zaman harcadığını görürsünüz. Burada amaç, tek bir sorguyu değil; iş yükünün genelini iyileştirmektir.

Hızlı Teşhis İçin İki Soru

• Yavaşlık CPU odaklı mı (karmaşık hesaplama, büyük join), yoksa I/O odaklı mı (diskten okuma, cache kaçırma)?
• Yavaşlık sürekli mi, yoksa belirli saatlerde mi (autovacuum, yoğun yazma, raporlama işleri)?

Bu iki sorunun cevabı, indeks mi yoksa vacuum/analyze mı öncelikli olacağını belirler. Çoğu sistemde ikisi birlikte ele alınır; çünkü yanlış indeks seçimi daha çok I/O üretir, bloat ise I/O maliyetini büyütür.

Doğru İndeks Tasarımı: B-Tree, Çok Kolonlu, Partial ve Covering

İndeks, PostgreSQL performans optimizasyonunun en görünür parçasıdır; fakat her indeks bir maliyet getirir: yazma sırasında ek iş, daha fazla disk kullanımı ve vacuum yükünde artış. Bu yüzden “çok indeks” değil, “doğru indeks” yaklaşımı gerekir.

B-Tree İndekslerde Sıralama ve Seçicilik

En yaygın indeks türü B-Tree’dir. Filtrelerde kullanılan kolonlar, eşitlik koşulları ve aralık sorguları için uygundur. Çok kolonlu indekslerde kolon sırası kritik olur: genellikle önce eşitlik koşulları, sonra aralık koşulları gelir. “Seçicilik” (distinct değer oranı) yüksek kolonlar, planlayıcıya daha iyi seçenek sunar.

Örneğin, e-ticaret siparişlerinde hem müşteri hem tarih filtreleniyorsa, sorgu desenine göre indeks sırası farklılaştırılabilir. Ayrıca ORDER BY ile uyumlu bir indeks, ayrı bir sort maliyetini ortadan kaldırabilir.

Partial Index ile Yükü Azaltma

Partial index, tablonun tamamına değil, belirli bir koşulu sağlayan satırlara indeks tutar. Bu sayede indeks küçük kalır ve yazma maliyeti düşer. “Aktif” kayıtların çoğunlukla sorgulandığı senaryolarda çok etkilidir.

-- Sık sorgulanan aktif siparişler için partial index
CREATE INDEX CONCURRENTLY idx_orders_active_customer_date
ON orders (customer_id, created_at DESC)
WHERE status = 'ACTIVE';

-- Sorgu deseni
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE status = 'ACTIVE'
  AND customer_id = 42
  AND created_at > now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Burada CONCURRENTLY kullanımı, üretimde tabloya yazma devam ederken indeks oluşturmanıza yardımcı olur; ancak daha uzun sürebilir ve daha fazla kaynak tüketebilir. Bu yüzden bakım penceresi planı önemlidir.

Covering Index ve INCLUDE ile Daha Az Heap Erişimi

PostgreSQL’de INCLUDE ile “covering index” yaklaşımı uygulanabilir. Filtre ve sıralama için gereken kolonlar indeks anahtarında, sadece seçilecek kolonlar INCLUDE tarafında tutulur. Böylece “index-only scan” olasılığı artar, heap erişimi azalır. Bunun için görünürlük haritasının (visibility map) vacuum tarafından iyi beslenmesi gerektiğini unutmayın.

-- Filtre/ORDER BY için anahtar, SELECT için ek kolonlar INCLUDE ile
CREATE INDEX CONCURRENTLY idx_orders_customer_date_inc
ON orders (customer_id, created_at DESC)
INCLUDE (total_amount, status);

-- Uygun koşullarda index-only scan mümkün olur
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 100;

Bloat Nedir, Neden Olur, Nasıl Fark Edilir?

PostgreSQL, MVCC nedeniyle UPDATE/DELETE sonrasında eski satır sürümlerini hemen silmez. Bu sürümler, vacuum ile temizlenene kadar disk alanı tüketir ve tablo/indeks şişmesine (bloat) yol açar. Bloat, sadece disk maliyeti değildir: daha fazla sayfa okunur, cache verimliliği düşer, indeks taramaları uzar.

Bloat Belirtileri: I/O Artışı ve Beklenmeyen Planlar

Belirtiler genellikle şunlardır: tablo boyutu büyürken satır sayısı aynı kalır, indeks boyutları hızla artar, aynı sorgu daha çok buffer okur. Ayrıca planlayıcı, yüksek maliyet nedeniyle beklenmeyen planlar seçebilir. Özellikle yoğun güncelleme alan tablolar (event log, status tabloları, kuyruk tabloları) bloat’a yatkındır.

Fillfactor ile UPDATE Maliyetini Dengeleme

UPDATE işlemleri, satırın aynı sayfada güncellenebilmesi için boş alana ihtiyaç duyar. fillfactor düşürülerek sayfalarda boşluk bırakılır; bu, bazı iş yüklerinde bloat’ı azaltabilir. Bedeli, başlangıçta daha fazla disk kullanımıdır; ancak uzun vadede daha stabil performans sağlar.

-- Sık UPDATE alan tabloda fillfactor düşürme
ALTER TABLE orders SET (fillfactor = 80);
VACUUM (ANALYZE) orders;

-- İndeksler için de fillfactor ayarlanabilir
ALTER INDEX idx_orders_customer_date_inc SET (fillfactor = 90);
REINDEX INDEX CONCURRENTLY idx_orders_customer_date_inc;

VACUUM ve ANALYZE: Ne Zaman, Neden, Nasıl?

VACUUM, ölü satır sürümlerini temizler ve visibility map’i güncelleyerek index-only scan ihtimalini artırır. ANALYZE ise istatistikleri günceller; planlayıcı, tablo dağılımını bu istatistiklerden öğrenir. Performans iyileştirmede çoğu zaman “VACUUM mu ANALYZE mı?” sorusunun cevabı “ikisi de ama doğru sırada ve doğru kapsamda” olur.

ANALYZE Olmadan İndeks Yetmez

İndeksiniz doğru olsa bile istatistikler eskiyse planlayıcı yanlış kardinalite tahminleri yapabilir. Bu, yanlış join yöntemi (nested loop yerine hash join gibi) veya yanlış tarama seçimi (seq scan yerine index scan gibi) doğurabilir. Büyük veri ekledikten sonra, özellikle dağılımı değişen kolonlarda ANALYZE etkisi belirgindir.

VACUUM Türleri: Standart, FULL ve FREEZE

• VACUUM: Ölü satır sürümlerini temizler, çoğu durumda çevrimiçi çalışır.
• VACUUM FULL: Tabloyu yeniden yazar, bloat’ı agresif biçimde azaltır ancak ağır kilit alır; üretimde dikkatli kullanılır.
• VACUUM (FREEZE): Transaction ID yaşlanmasını yönetir, wraparound riskini azaltır.

Genellikle üretimde hedef; VACUUM FULL’a mecbur kalmayacak bir autovacuum ve bakım stratejisi kurmaktır. FULL, istisnai durumlarda ve planlı kesintide düşünülür.

Autovacuum Mantığı: Eşikler, Ölçek Faktörleri ve İş Yüküne Uyum

Autovacuum, PostgreSQL’in kendi kendine bakım mekanizmasıdır; ancak varsayılan ayarlar her iş yüküne uygun değildir. Özellikle büyük tablolar ve yoğun yazma altında, autovacuum ya geç kalır ya da yanlış zamanda agresif çalışıp gecikmeyi artırır. Buradaki amaç, autovacuum’u “sürpriz” olmaktan çıkarıp “öngörülebilir” hale getirmektir.

Temel Parametreler: threshold ve scale factor

Autovacuum tetiklenmesi, kabaca “ölü satır sayısı”nın bir eşiği geçmesine bağlıdır. Bu eşik, sabit bir threshold ile tablo büyüklüğüne göre artan scale factor bileşimidir. Büyük tabloda scale factor yüksekse, autovacuum çok geç devreye girer ve bloat büyür.

-- Örnek hesap: vacuum tetiklenme eşiği
-- autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

-- Büyük tablolar için scale factor genellikle düşürülür:
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 20000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 10000
);

Burada kritik nokta, ayarı “her tabloya aynı” uygulamamak. Yoğun yazma alan kritik tablolar için daha agresif; nadiren değişen tablolar için daha sakin ayarlar tercih edilir.

Autovacuum Workers ve Maliyet Ayarları

Autovacuum, sistem kaynaklarını kullanır. Çok az worker, yetişememeye; çok fazla worker ise aynı anda I/O baskısına yol açabilir. Ayrıca cost-based ayarlar, vacuum’un “ne kadar hızlı” çalışacağını belirler. Burada hedef, üretim trafiğini boğmadan bakımın sürekliliğini sağlamaktır.

Pratik bir yaklaşım: gecikmenin düşük olduğu saatlerde daha hızlı vacuum, yoğun saatlerde daha kısıtlı vacuum. Bunu global ayarlarda yapmak mümkündür; fakat kritik tablolar için tablo bazında ayar çoğu zaman daha güvenlidir.

İstatistik Hassasiyeti: n_distinct, histogram ve extended statistics

ANALYZE, planlayıcıya veri dağılımı hakkında bilgi sağlar; ancak bazı durumlarda varsayılan istatistik seviyesi yetersiz kalır. Özellikle filtrelenen kolonlarda belirli değerler çok sık geçiyorsa veya kolonlar arasında korelasyon varsa, tahmin hatası büyür.

statistics_target ile Daha İyi Tahmin

İstatistik hedefini artırmak, daha ayrıntılı histogram ve daha iyi seçicilik tahmini sağlar. Bedeli, analyze süresinin ve istatistik boyutunun artmasıdır. Çok büyük tabloda tüm kolonlara uygulamak yerine, en kritik filtre kolonlarına odaklanmak daha iyi sonuç verir.

-- Sık filtrelenen kolonlarda istatistik hedefini yükseltme
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;

ANALYZE orders;

Extended Statistics ile Kolon Korelasyonunu Yakalamak

Birden fazla kolon birlikte filtreleniyorsa ve aralarında ilişki varsa, extended statistics planlayıcıyı ciddi ölçüde iyileştirir. Örneğin status ve region birlikte seçiliyorsa, bağımsız varsayımı yanlış olabilir. Bu durumda bağımlılık istatistiği, kardinalite tahminini daha gerçekçi yapar.

Bakım Stratejisi: Reindex, Partition, Lock ve Planlı Operasyonlar

İndeksler de zamanla şişebilir veya parçalanabilir. Her durumda reindex gerekmez; fakat yoğun güncelleme ve uzun süre vacuum yetişememe durumlarında indeks bloat’ı belirgin olur. Bunun yanında partitioning, büyük tabloları yönetilebilir parçalara bölerek hem vacuum/analyze maliyetini hem de sorgu taramasını azaltabilir.

REINDEX CONCURRENTLY Ne Zaman?

REINDEX CONCURRENTLY, üretimde kilit etkisini azaltır; yine de I/O tüketebilir ve uzun sürebilir. Reindex kararı için indeks boyutu, kullanım oranı ve sorgu maliyeti birlikte değerlendirilmelidir. Sadece “indeks büyüdü” diye reindex yapmak, gereksiz bakım yükü yaratabilir.

Partitioning ile Vacuum Yükünü Bölmek

Özellikle zaman serisi tablolarında, aylık veya günlük partition’lar vacuum/analyze işlemlerini bölerek daha öngörülebilir hale getirir. Sıcak partition’lar daha sık bakım alırken, soğuk partition’lar daha seyrek bakım alabilir. Bu yaklaşım, büyük tabloda tek seferde yüksek bakım maliyetinin önüne geçer.

Pratik Kontrol Listesi: Hızlı Kazanımlar ve Yaygın Hatalar

Aşağıdaki liste, sahada en sık performans kazancı üreten adımları özetler. Her maddeyi uygularken ölçüm almayı ihmal etmeyin; çünkü aynı ayar farklı sistemlerde farklı sonuç verir.

1. En pahalı sorguları bulun: pg_stat_statements + EXPLAIN (ANALYZE, BUFFERS) ile başlayın.
2. İndeksleri sorgu desenine göre tasarlayın: Çok kolonlu indeks sırası, partial index ve INCLUDE kullanımı fark yaratır.
3. ANALYZE’i ciddiye alın: Büyük veri değişiminden sonra istatistik güncellemesi olmadan plan iyileşmez.
4. Autovacuum’u tablo bazında ayarlayın: Büyük ve yoğun yazılan tablolar için scale factor düşürün.
5. Bloat’ı izleyin: Tablo/indeks boyutu ile satır sayısı arasındaki anomaliyi takip edin; fillfactor ve bakım planı kurun.

Yaygın hatalardan biri, üretimde VACUUM FULL ile “hemen küçültme”ye gitmek ve beklenmedik kilitlenme yaşamaktır. Daha sürdürülebilir yaklaşım; autovacuum eşiğini iyileştirip düzenli bakım akışıyla bloat’ı büyümeden yönetmektir.

Örnek Senaryo: Yoğun Yazma Alan Sipariş Tablosunu Stabil Hale Getirmek

Diyelim ki orders tablonuz günde milyonlarca INSERT/UPDATE alıyor, raporlama sorguları da aynı tabloyu tarıyor. Belirli saatlerde gecikme artıyor ve I/O tavan yapıyor. Bu durumda tipik çözüm paketi şunları içerir:

• Aktif kayıtlar için partial index ile indeks boyutunu düşürmek
• Önemli filtre kolonlarında statistics_target artırmak
• orders için autovacuum scale factor ve threshold değerlerini düşürmek
• Bakım penceresinde hedefli VACUUM (ANALYZE) çalıştırmak

Bu adımların her biri tek başına faydalıdır; birlikte uygulandığında ise plan kararlılığı artar ve performans dalgalanması azalır. Sonrasında hedef, izleme panellerinde vacuum gecikmesi, dead tuple oranı ve sorgu gecikmesini birlikte takip ederek ayarları kademeli iyileştirmektir.

Sonuç: İndeks + İstatistik + Vacuum Üçlüsü Birlikte Çalışır

PostgreSQL performansı; doğru indeksle erişimi hızlandırmak, ANALYZE ile planlayıcıyı doğru beslemek ve vacuum/autovacuum ile MVCC’nin yan etkilerini yönetmek üzerine kurulur. Bu üçlüden biri eksik kaldığında, diğerleri tek başına mucize yaratmaz.

İyi bir bakım stratejisi kurduğunuzda, sistem büyüdükçe bile performansınız daha öngörülebilir hale gelir. Önce ölçün, sonra hedefli değişiklik yapın, ardından tekrar ölçün. Böylece performans iyileştirme, “tek seferlik operasyon” değil, sürdürülebilir bir süreç olur.