PostgreSQL Performans Optimizasyonu - Index ve Query Tuning

PostgreSQL varsayılan yapılandırması genel amaçlı ve muhafazakar ayarlarla gelir. Production ortamında yüksek performans elde etmek için bellek ayarları, index stratejileri, sorgu optimizasyonu ve bakım görevlerini iş yükünüze göre yapılandırmanız gerekir. Bu rehberde EXPLAIN ANALYZE ile yavaş sorguları tespit etmekten doğru index tipini seçmeye, postgresql.conf tuning'den autovacuum yapılandırmasına kadar somut adımlarla PostgreSQL performansını optimize ediyoruz.

Bellek ve Bağlantı Ayarları (postgresql.conf)

PostgreSQL'in performansını en çok etkileyen ayarlar bellek tahsisi ile ilgilidir. Sunucunuzun RAM miktarına göre aşağıdaki değerleri ayarlayın:

# Paylasimli bellek - toplam RAM'in %25'i
shared_buffers = 4GB

# Sorgu basina kullanilabilecek bellek
work_mem = 64MB

# Bakim islemleri (VACUUM, CREATE INDEX) icin bellek
maintenance_work_mem = 1GB

# OS cache tahmini - toplam RAM'in %75'i
effective_cache_size = 12GB

# WAL ayarlari
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Paralel sorgu
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Rastgele disk okuma maliyeti (SSD icin dusur)
random_page_cost = 1.1   # HDD: 4.0, SSD: 1.1
effective_io_concurrency = 200  # SSD icin

EXPLAIN ANALYZE ile Yavaş Sorgu Tespiti

Performans sorunlarının büyük çoğunluğu yavaş sorgulardan kaynaklanır. PostgreSQL'in EXPLAIN ANALYZE komutu sorgunun gerçek yürütme planını, her adımın süresini ve tahmini ile gerçek satır sayısı farkını gösterir. Optimizasyona başlamadan önce darboğazı doğru tespit etmeniz gerekir.

-- Temel kullanim
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'active'
ORDER BY created_at DESC LIMIT 20;

-- Detayli cikti icin BUFFERS ve TIMING ekleyin
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2025-01-01'
ORDER BY o.total DESC;

EXPLAIN çıktısında dikkat etmeniz gereken kritik noktalar:

• Seq Scan vs Index Scan Seq Scan tablonun tamamını tarar. Büyük tablolarda WHERE koşullu sorgularda Seq Scan görüyorsanız index eksikliği var demektir. Küçük tablolarda (<10.000 satır) Seq Scan normal olabilir.
• Rows (estimated vs actual) Tahmini ve gerçek satır sayısı arasında büyük fark varsa (10x+) istatistikler güncel değildir. ANALYZE tablename; komutu ile istatistikleri güncelleyin.
• Sort Method: external merge Sıralama işlemi belleğe sığmayıp diske taşmış demektir. work_mem değerini artırın veya sıralama için index oluşturun.
• Buffers: shared hit vs read shared hit cache'den okunan, read diskten okunan sayfa sayısıdır. Hit oranı düşükse shared_buffers yetersiz olabilir veya veri seti bellekten büyüktür.

Yavaş sorguları otomatik olarak loglamak için postgresql.conf dosyasına şu ayarları ekleyin:

# 500ms'den uzun suren sorgulari logla
log_min_duration_statement = 500

# pg_stat_statements extension'i etkinlestir
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

pg_stat_statements extension'ı ile en çok kaynak tüketen sorguları bulabilirsiniz:

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Index Stratejileri: B-tree, GIN, GiST ve BRIN

Doğru index tipi seçimi sorgu performansını 100x iyileştirebilir. PostgreSQL dört temel index tipi sunar ve her biri farklı veri yapıları ve sorgu kalıpları için optimize edilmiştir.

B-tree Index Örnekleri

-- Composite index: sik kullanilan WHERE + ORDER BY kombinasyonu
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at DESC);

-- Partial index: sadece aktif kayitlari indeksle (daha kucuk, daha hizli)
CREATE INDEX idx_orders_active
ON orders (customer_id, created_at)
WHERE status = 'active';

-- Covering index: index-only scan icin gerekli sutunlari INCLUDE ile ekle
CREATE INDEX idx_orders_covering
ON orders (customer_id)
INCLUDE (total, status);

GIN Index: JSONB ve Full-Text Search

-- JSONB alani icin GIN index
CREATE INDEX idx_products_metadata
ON products USING GIN (metadata jsonb_path_ops);

-- Sorgu: JSONB icinde arama
SELECT * FROM products
WHERE metadata @> '{"color": "red", "size": "L"}'::jsonb;

-- Full-text search icin GIN index
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX idx_articles_search
ON articles USING GIN (search_vector);

-- Trigger ile otomatik guncelleme
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.turkish', title, content);

BRIN Index: Zaman Serisi ve Log Tabloları

BRIN (Block Range Index) fiziksel olarak sıralı verilerde son derece küçük boyutlu ve etkili index sağlar. Milyarlarca satırlık log tablolarında B-tree index gigabaytlarca yer kaplarken BRIN birkaç megabayt ile aynı işi yapar.

-- Log tablosu icin BRIN index (created_at dogal sirada artar)
CREATE INDEX idx_logs_created
ON access_logs USING BRIN (created_at)
WITH (pages_per_range = 32);

-- Boyut karsilastirmasi (100M satirlik tablo)
-- B-tree: ~2.1 GB
-- BRIN:   ~48 KB

Autovacuum Yapılandırması

PostgreSQL'in MVCC (Multi-Version Concurrency Control) mimarisi UPDATE ve DELETE işlemlerinde eski satır versiyonlarını (dead tuples) hemen silmez. Autovacuum bu ölü satırları temizler, istatistikleri günceller ve transaction ID wraparound'u önler. Varsayılan ayarlar küçük veritabanları için yeterlidir ancak yüksek yazma hacminde yetersiz kalır.

# Autovacuum worker sayisini artir
autovacuum_max_workers = 5          # varsayilan: 3

# Daha sik calissin
autovacuum_naptime = 30s           # varsayilan: 1min

# Daha az dead tuple biriksin
autovacuum_vacuum_scale_factor = 0.05   # varsayilan: 0.2
autovacuum_analyze_scale_factor = 0.025  # varsayilan: 0.1

# I/O throttling - SSD'de daha agresif olabilir
autovacuum_vacuum_cost_limit = 1000  # varsayilan: 200
autovacuum_vacuum_cost_delay = 5ms   # varsayilan: 2ms (PG 15+)

Yoğun yazma yapılan tablolar için tablo bazında özel autovacuum ayarları tanımlayabilirsiniz:

-- Yogun yazilan tablo icin agresif vacuum
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_limit = 2000
);

-- Dead tuple durumunu kontrol et
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 4) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Sorgu Optimizasyon Teknikleri

Index ve bellek ayarları dışında sorgu yazım şekli de performansı doğrudan etkiler. Aşağıdaki teknikler yaygın performans sorunlarını çözer:

SELECT * Yerine Gerekli Sütunları Belirtin

SELECT * tüm sütunları okur, ağ üzerinden taşır ve index-only scan'i engeller. Sadece ihtiyacınız olan sütunları belirtin:

-- KOTU: tum sutunlari okur, index-only scan kullanilamaz
SELECT * FROM orders WHERE customer_id = 42;

-- IYI: sadece gerekli sutunlar, covering index ile index-only scan
SELECT id, total, status FROM orders WHERE customer_id = 42;

-- Pagination: OFFSET buyuk degerlerde yavaslar
-- KOTU: 1M satir atlayip 20 satir okur
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000;

-- IYI: keyset pagination (cursor-based)
SELECT * FROM orders
WHERE id > 1000000
ORDER BY id LIMIT 20;

CTE ve Subquery Optimizasyonu

PostgreSQL 12+ sürümlerinde CTE (Common Table Expressions) varsayılan olarak inline edilir. Ancak karmaşık sorgularda MATERIALIZED hint'i ile CTE'yi zorla materialize edebilir veya NOT MATERIALIZED ile inline edilmesini sağlayabilirsiniz:

-- CTE sonucu birden fazla yerde kullaniliyorsa MATERIALIZED
WITH active_customers AS MATERIALIZED (
  SELECT id, name FROM customers
  WHERE last_login > now() - interval '30 days'
)
SELECT ac.name, count(o.id) AS order_count
FROM active_customers ac
JOIN orders o ON o.customer_id = ac.id
GROUP BY ac.name;

Veritabanı güvenliği için Veritabanı Güvenliği rehberimizi, bağlantı yönetimi için PgBouncer ile Connection Pooling rehberimizi, yedekleme stratejileri için Veritabanı Yedekleme Otomasyonu rehberimizi inceleyin. PostgreSQL Performance Tips ve PostgreSQL Wiki - Tuning ek kaynak olarak faydalıdır.

Sıkça Sorulan Sorular

Sonuç

PostgreSQL performans optimizasyonu tek seferlik bir iş değil, sürekli izleme ve ayarlama gerektiren bir süreçtir. shared_buffers ve work_mem ile bellek tahsisini sunucunuza göre ayarlayın, EXPLAIN ANALYZE ile darboğazları tespit edin, doğru index tipini seçin ve autovacuum'un sağlıklı çalıştığından emin olun. pg_stat_statements ile en yavaş sorguları düzenli olarak izleyin ve keyset pagination, covering index gibi tekniklerle sorgu performansını iyileştirin.