PostgreSQL Performans Ayarları: Vacuum, Analyze ve Query Planner

PostgreSQL Performans Ayarları

PostgreSQL'in güçlü sorgu planlayıcısı ve MVCC (Multi-Version Concurrency Control) mimarisi, doğru yapılandırmayla olağanüstü performans sağlar.

postgresql.conf Temel Ayarları

# Dosya konumu
find /etc -name "postgresql.conf" 2>/dev/null
# Genellikle: /etc/postgresql/16/main/postgresql.conf

Bellek Ayarları:

# RAM'in %25'i (maksimum 8GB'ı aşmasın)
shared_buffers = 2GB

# Çalışma alanı (her sorgu için, dikkatli kullanın)
work_mem = 64MB

# Bakım işlemleri için
maintenance_work_mem = 512MB

# OS cache tahmini (RAM'in %50-75'i)
effective_cache_size = 6GB

WAL ve Checkpoint:

# WAL buffer
wal_buffers = 64MB

# Checkpoint aralığı
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min

# Max WAL size
max_wal_size = 4GB
min_wal_size = 1GB

Bağlantı:

max_connections = 200
# PgBouncer bağlantı havuzlama kullanıyorsanız azaltın
# max_connections = 100

EXPLAIN ANALYZE Kullanımı

-- Sorgu planını ve gerçek süreci göster
EXPLAIN ANALYZE SELECT u.isim, COUNT(s.id) AS siparis_sayisi
FROM kullanicilar u
LEFT JOIN siparisler s ON u.id = s.kullanici_id
WHERE u.aktif = true
GROUP BY u.id, u.isim
ORDER BY siparis_sayisi DESC;

-- Detaylı çıktı
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

EXPLAIN çıktısı yorumlama:

Seq Scan          → Tüm tabloyu tarıyor (büyük tablolarda kötü)
Index Scan        → Index kullanıyor (iyi)
Index Only Scan   → Sadece index'ten veri alıyor (en iyi)
Bitmap Heap Scan  → Büyük result set için iyi
Hash Join         → Join metodu
Nested Loop       → İç içe döngü join

Autovacuum Ayarları

VACUUM, silinen/güncellenen satırların yer kapladığı alanı geri kazanır:

# Autovacuum etkin (varsayılan)
autovacuum = on
autovacuum_vacuum_scale_factor = 0.05    # %5 değişince vacuum
autovacuum_analyze_scale_factor = 0.02   # %2 değişince analyze

# Büyük tablolar için tablo bazında ayar

-- Tablo bazında autovacuum ayarı
ALTER TABLE buyuk_tablo SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

-- Manuel vacuum
VACUUM ANALYZE siparisler;
VACUUM FULL siparisler;  -- Tüm alanı geri kazanır ama kilit alır

-- Bloat kontrolü
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric/nullif(n_live_tup,0)*100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

pg_stat_statements ile Sorgu İzleme

-- Etkinleştir (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- En yavaş sorgular
SELECT query, calls, total_exec_time/calls AS avg_ms,
       rows/calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- En çok çağrılan
SELECT query, calls FROM pg_stat_statements
ORDER BY calls DESC LIMIT 10;

-- Sıfırla
SELECT pg_stat_statements_reset();

Index Optimizasyonu

-- Kullanılmayan indexler
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%';

-- Eksik index tespiti
SELECT * FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 1000;

-- Partial index (koşullu)
CREATE INDEX idx_aktif_siparisler ON siparisler(tarih)
WHERE durum = 'aktif';

-- Covering index (INCLUDE)
CREATE INDEX idx_kullanici_siparis ON siparisler(kullanici_id)
INCLUDE (tarih, toplam);

-- Concurrent index (kilit almadan)
CREATE INDEX CONCURRENTLY idx_email ON kullanicilar(email);

Bağlantı Havuzlama: PgBouncer

apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini

[databases]
sitem_db = host=127.0.0.1 dbname=sitem_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Monitoring

-- Aktif ve bekleyen sorgular
SELECT pid, usename, application_name, state, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Lock bekleyen sorgular
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.granted;

-- Tablo boyutları
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Büyükweb VDS sunucularında PostgreSQL ile yüksek performanslı veritabanı altyapısı kurun.

MVCC ve Dead Tuple Mekanizması

PostgreSQL MVCC (Multi-Version Concurrency Control) ile concurrent okuma/yazma destekler — okuma yazmayı blok etmez, yazma okumayı blok etmez. Karşılığında dead tuple problemi:

-- UPDATE örneği — eski satır silinmez, yeni satır eklenir
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- Tablo şimdi:
-- ID=1, email='[email protected]', xmin=100, xmax=200  ← dead tuple
-- ID=1, email='[email protected]', xmin=200, xmax=null ← canlı

-- DELETE de aynı — fiziksel silinmez, sadece "dead" işaretlenir

-- Dead tuple'lar diskte yer kaplamaya devam eder

-- Dead tuple miktarı kontrol
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

%20+ dead_pct = bloat sorunu; VACUUM gerekli.

VACUUM Türleri

Türü	Etki	Lock	Disk
VACUUM	dead tuple temizle, alan reuse	SHARE UPDATE EXCLUSIVE (read OK)	recycle
VACUUM FULL	tablo yeniden yaz	ACCESS EXCLUSIVE (downtime)	shrink
VACUUM FREEZE	wraparound önle (tx ID 2B sınırı)	SHARE UPDATE EXCLUSIVE	—
VACUUM ANALYZE	+ planner istatistikleri	SHARE UPDATE EXCLUSIVE	recycle
pg_repack (extension)	online VACUUM FULL alternatifi	minimal	shrink

-- Standart bakım
VACUUM ANALYZE users;

-- Tüm DB
VACUUM (VERBOSE, ANALYZE);

-- Bloat şişti — tablo yeniden yaz (downtime gerek)
VACUUM FULL VERBOSE users;

-- Online alternatif
SELECT * FROM pg_stat_user_tables WHERE relname = 'users';
-- pg_repack extension ile
pg_repack -t users -d mydb

autovacuum — Detaylı Tuning

Default ayarlar küçük DB için. Yüksek-trafik tablolarda yetersiz:

# postgresql.conf
autovacuum = on
log_autovacuum_min_duration = 0          # tüm autovacuum'ları logla

# Threshold'lar
autovacuum_vacuum_threshold = 50         # min dead tuples
autovacuum_vacuum_scale_factor = 0.2     # %20 dead = tetikle (default %20 yüksek!)
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

# Worker
autovacuum_max_workers = 4               # default 3
autovacuum_naptime = 60s

# Cost-based throttling — autovacuum'ı yavaşlatır
autovacuum_vacuum_cost_limit = 1000      # default 200; hızlandır
vacuum_cost_delay = 10ms

-- Tablo bazlı override (yoğun yazılan tablo için)
ALTER TABLE high_traffic_table SET (
  autovacuum_vacuum_scale_factor = 0.05,    -- %5 dead'de tetikle
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 2000
);

Pratik: 100M satırlık tabloda scale_factor = 0.2 = 20M dead tuple gerekli — geç tetikleniyor. 0.05 = 5M ile daha sık ama ufak vacuum.

Query Planner ve Statistics

Query planner istatistiklere göre execution plan seçer:

-- ANALYZE — istatistik güncelle
ANALYZE users;

-- Daha iyi cardinality estimate
ALTER TABLE users ALTER COLUMN email SET STATISTICS 1000;
ANALYZE users;

-- Multi-column statistics (PostgreSQL 10+)
CREATE STATISTICS users_geo_stats (dependencies, ndistinct)
  ON country, city FROM users;
ANALYZE users;

-- Mevcut istatistikleri gör
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'users';

-- EXPLAIN ANALYZE — gerçek execution + buffers
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT u.*, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.country = 'TR' AND u.created_at > '2026-01-01'
GROUP BY u.id;

-- Yorumlama:
-- "Seq Scan" = full table scan (genelde kötü)
-- "Index Scan" = ✓
-- "rows=1 actual rows=10000" = istatistik bozuk → ANALYZE
-- "Buffers: shared hit=200 read=5000" = cache miss çok → buffer pool artır

pg_stat_statements — Slow Query

-- Extension etkinleştir
CREATE EXTENSION pg_stat_statements;

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- En yavaş 20 sorgu (toplam etki)
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric / 1000, 2) AS total_sec,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Cache hit ratio düşük sorgular
SELECT query, shared_blks_read, shared_blks_hit,
  round(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC LIMIT 20;

-- Reset stats (yeniden başlangıç)
SELECT pg_stat_statements_reset();

Index Stratejisi

-- Eksik indeks tespit (yüksek seq scan)
SELECT relname, seq_scan, idx_scan,
  seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_rows
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC LIMIT 10;

-- Kullanılmayan indeks (silinebilir)
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT indexrelid FROM pg_index WHERE indisunique)
ORDER BY pg_relation_size(indexrelid) DESC;

-- CONCURRENT index (tablo locklamadan, production-safe)
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

TOAST ve Bloat Detail

PostgreSQL büyük field'ları (text, bytea, jsonb) TOAST tablolarına ayırır:

-- Tablo boyutu detay
SELECT
  pg_size_pretty(pg_total_relation_size('users')) AS total,
  pg_size_pretty(pg_relation_size('users')) AS heap,
  pg_size_pretty(pg_indexes_size('users')) AS indexes,
  pg_size_pretty(pg_total_relation_size('users') - pg_relation_size('users') - pg_indexes_size('users')) AS toast;

-- Bloat oran tespit (pgstattuple extension)
CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('users');
-- dead_tuple_percent yüksek → VACUUM gerek
-- free_percent yüksek → VACUUM FULL veya pg_repack gerek

Transaction ID Wraparound

PostgreSQL'in transaction ID 32-bit (2 milyar sınır). VACUUM FREEZE periyodik şart:

-- Wraparound risk durumu
SELECT datname,
  age(datfrozenxid) AS xid_age,
  2147483648 - age(datfrozenxid) AS xids_until_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- 1.5B yaklaşırsa autovacuum_freeze_max_age (default 200M) tetiklenir
-- Manuel zorla
VACUUM FREEZE VERBOSE users;

# postgresql.conf — wraparound önleme
autovacuum_freeze_max_age = 150000000   # default 200M; daha erken
vacuum_freeze_min_age = 50000000

Wraparound olursa DB read-only mode'a geçer — felaket. Production'da monitoring zorunlu.

Connection Pooling — Connection Overhead

Her connection ~10 MB RAM. Yüksek concurrent app için PgBouncer:

# /etc/pgbouncer/pgbouncer.ini
[databases]
prod = host=127.0.0.1 port=5432 dbname=prod

[pgbouncer]
pool_mode = transaction               # critical: transaction-level pooling
default_pool_size = 25                # backend conn
max_client_conn = 1000                # client conn
reserve_pool_size = 5

1000 client → 25 backend connection → DB rahat nefes alır.

Checkpoint ve WAL Tuning

# postgresql.conf — write-heavy iş yükü
checkpoint_timeout = 15min            # default 5min
max_wal_size = 8GB                    # default 1GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_compression = on                  # WAL boyutu %50 azaltır
wal_buffers = 64MB

shared_buffers = 8GB                  # RAM/4
effective_cache_size = 24GB           # RAM × 0.75
work_mem = 64MB                       # sort/hash başına; concurrent dikkat
maintenance_work_mem = 2GB            # VACUUM/CREATE INDEX için

Sıkça Sorulan Sorular

"VACUUM çalışıyor ama tablo küçülmüyor" — neden?

VACUUM (FULL değil) alanı disk'ten geri vermez, sadece tablonun içinde "boş slot" olarak işaretler. Yeni veriler eklenince bu slotlar dolar. VACUUM FULL veya pg_repack ile fiziksel shrink yapılır.

autovacuum'u kapatmak güvenli mi?

Hayır — wraparound riski + bloat birikmesi. Eğer "autovacuum hep çalışıyor, sistem yavaş" diyorsa: autovacuum_max_workers artır + cost_limit yükselt → daha hızlı bitsin. Asla kapatma.

EXPLAIN'de "Seq Scan" her zaman kötü mü?

Hayır — küçük tablolarda (1000 satır altı) seq scan index scan'den hızlı. Planner bunu istatistiklere göre seçer. Sorun: 1M satırlı tablo seq scan yapıyorsa → istatistik bozuk veya index yok.

work_mem'i çok yükseltsem?

Tehlikeli — work_mem her sort/hash operasyonu için ayrı tahsis edilir. max_connections=200 × work_mem=256MB × birden fazla sort = 50+ GB RAM patlama riski. work_mem = 32-64MB makul; spesifik query için SET LOCAL work_mem='1GB'.

"PostgreSQL slow" şikayeti, nereden başlayım?

1. pg_stat_statements → en yavaş 20 query 2) EXPLAIN ANALYZE BUFFERS → bottleneck 3) pg_stat_user_tables → bloat oranı 4) shared_buffers ve work_mem ayarları kontrol 5) Kullanılmayan index sil 6) Eksik index ekle.

VACUUM FULL ne kadar sürer?

Tablo boyutuna göre: 1 GB → 1-2 dk; 100 GB → saatler; 1 TB → günler. Bu sürede tablo kilitli (read/write yasak). Production'da pg_repack veya online migration alternatifi.

"out of memory" hatası query'de — çözüm?

work_mem arttır + sorguyu optimize et. temp_file_limit artır (sort disk'e taşar). Veya sorguyu pencereleyerek (LIMIT/OFFSET veya pagination) küçük chunk'larda çalıştır.

Replikasyon lag VACUUM'u etkiler mi?

Hot standby varsa VACUUM ile replica'da "snapshot too old" hatası olabilir. hot_standby_feedback = on aktif et — primary VACUUM'u yavaşlatır ama replica sorguları korumur.

Index neden yavaş çalışıyor olabilir?

1. Index bloat — REINDEX gerek 2) İstatistik bozuk — ANALYZE 3) Wrong index type (B-tree vs GIN) 4) Composite index kolon sırası yanlış 5) Function index gerekli (LOWER(email) üzerinde) 6) Cost estimate yanlış — SET random_page_cost = 1.1 SSD için.

"WAL arşivi büyüdü" — temizleyebilir miyim?

pg_archivecleanup veya pgbackrest ile retention. Manuel silmek tehlikeli — replica/PITR bozulabilir. Backup tool'u retention politikası uygulasın.

Büyükweb VDS'te PostgreSQL Tuning

Büyükweb VDS paketleri PostgreSQL için ideal:

• 8-16 GB RAM — orta DB (10-50 GB), shared_buffers=4 GB
• 32 GB RAM — büyük DB (100+ GB), shared_buffers=8 GB, replication
• NVMe kritik — VACUUM ve EXPLAIN ANALYZE I/O ağır

Yapılandırma + tuning desteği için 0850 302 60 70.

İlgili Rehberler

• PostgreSQL Nedir Gelişmiş Veritabanı
• MySQL/MariaDB Performans Optimizasyonu
• Veritabanı Sharding ve Horizontal Scaling
• WordPress Veritabanı Optimizasyonu

İlgili Büyükweb Hizmetleri

Veritabanı performansı ve güvenilirliği için Türkiye lokasyonlu sunucu paketlerimiz:

• Yüksek IOPS VDS Sunucu
• E5 v4 İşlemcili VDS
• Fiziksel Dedicated
• WordPress Hosting (MySQL)
• cPanel Web Hosting

Sorularınız için 0850 302 60 70 numaralı destek hattımıza veya iletişim sayfamıza yazabilirsiniz.