postgresql.conf Optimizasyonu: Temel Performans Parametreleri

PostgreSQL kurulumunu tamamladınız, veritabanınız çalışıyor ama performans beklediğiniz gibi değil. Sorgular yavaş, connection sayısı artınca sistem zorlanıyor, bellek kullanımı kafanızı karıştırıyor. Sorunun büyük ihtimalle kaynağı postgresql.conf dosyasındaki varsayılan ayarlar. PostgreSQL, kurulumda son derece muhafazakar değerlerle gelir çünkü her türlü donanımda çalışması gerekir. Ama siz gerçek bir production sunucusu yönetiyorsunuz ve o sunucunun kapasitesini sonuna kadar kullanmak istiyorsunuz. Bu yazıda kritik parametreleri tek tek ele alacağız, neyi neden ayarlamamız gerektiğini açıklayacağız ve gerçek dünya senaryolarıyla somutlaştıracağız.

postgresql.conf Dosyası Nerede ve Nasıl Düzenlenir

Önce temel bilgilerle başlayalım. postgresql.conf dosyası genellikle PostgreSQL’in data dizininde bulunur. Sisteme göre konumu değişebilir.

# Dosyanın yerini bulmak için
sudo -u postgres psql -c "SHOW config_file;"

# Debian/Ubuntu sistemlerde genellikle
ls /etc/postgresql/15/main/postgresql.conf

# RHEL/CentOS sistemlerde genellikle
ls /var/lib/pgsql/15/data/postgresql.conf

# Değişiklikleri uygulamak için (tüm parametreler restart gerektirmez)
sudo systemctl reload postgresql

# Bazı parametreler için tam restart gerekir
sudo systemctl restart postgresql

# Hangi parametrenin ne gerektirdiğini görmek için
sudo -u postgres psql -c "SELECT name, context FROM pg_settings WHERE context IN ('postmaster', 'sighup') ORDER BY context, name;"

Değişiklik yapmadan önce mutlaka yedeğini alın:

sudo cp /etc/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf.bak.$(date +%Y%m%d)

Bellek Parametreleri: En Kritik Ayarlar

shared_buffers

Bu parametre PostgreSQL’in veri önbelleği için kullandığı bellek miktarını belirler. Varsayılan değer genellikle 128MB’dır, bu modern sunucular için son derece yetersizdir.

Önerilen değer: Toplam RAM’in %25’i. 32GB RAM’li bir sunucu için 8GB idealdir. %40’ın üzerine çıkmanızı önermiyorum çünkü işletim sistemi de kendi önbelleği için belleğe ihtiyaç duyar.

# postgresql.conf içinde
shared_buffers = 8GB          # 32GB RAM için
# ya da
shared_buffers = 4GB          # 16GB RAM için
# ya da  
shared_buffers = 2GB          # 8GB RAM için

effective_cache_size

Bu parametre PostgreSQL’e işletim sisteminin disk önbelleği dahil toplam kaç bellek kullanılabilir olduğunu söyler. Sorgu planlayıcı bu bilgiyi kullanır, gerçekten bellek ayırmaz. Yanlış anlaşılan parametrelerin başında gelir.

Önerilen değer: Toplam RAM’in %75’i.

effective_cache_size = 24GB   # 32GB RAM için

work_mem

Her bir sorgu operasyonu (sort, hash join vb.) için kullanılabilecek bellek. Dikkat: Bu değer bağlantı sayısı ve her bağlantıdaki paralel operasyonlarla çarpılır. 100 bağlantınız varsa ve her biri 64MB work_mem kullanıyorsa, teorik maksimum 6.4GB’a ulaşabilir.

# Varsayılan 4MB, OLTP için 16-64MB arası iyi bir başlangıç
work_mem = 32MB

# Analitik sorgular için gerekirse oturum bazlı artırabilirsiniz
# psql içinde: SET work_mem = '256MB';

maintenance_work_mem

VACUUM, CREATE INDEX, ALTER TABLE gibi bakım işlemleri için ayrılan bellek. Bu parametreyi work_mem’den bağımsız olarak yüksek tutabilirsiniz çünkü aynı anda çok fazla bakım işlemi çalışmaz.

maintenance_work_mem = 512MB  # Genel öneri, büyük tablolar için 1GB+

Gerçek Dünya Senaryosu: E-ticaret Uygulaması

16GB RAM, 8 CPU’lu bir e-ticaret sunucusu için tipik bellek ayarları:

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 256MB

WAL ve Checkpoint Ayarları

Write-Ahead Log (WAL) yapılandırması hem performansı hem de güvenilirliği doğrudan etkiler.

checkpoint_completion_target

PostgreSQL checkpoint işlemini ne kadar süreye yayacağını belirler. Yüksek değer daha az I/O spike demektir.

checkpoint_completion_target = 0.9  # Varsayılan 0.5, 0.9 genellikle iyidir

wal_buffers

WAL verisi için ayrılan bellek. shared_buffers’ın %3’ü iyi bir başlangıç noktasıdır. -1 değeri PostgreSQL’in otomatik hesaplamasına bırakır.

wal_buffers = 64MB  # Ya da -1 (otomatik)

min_wal_size ve max_wal_size

WAL dosyalarının boyut sınırları. Checkpoint’ler arası yazılan veri miktarını kontrol eder.

min_wal_size = 1GB
max_wal_size = 4GB

wal_compression

PostgreSQL 9.5 ile gelen bu özellik WAL’ı sıkıştırarak I/O yükünü azaltır. CPU maliyeti olsa da disk I/O bottleneck’i olan sistemlerde işe yarar.

wal_compression = on

Checkpoint Ayarlarını Test Etme

# Mevcut checkpoint istatistiklerini görüntüleme
sudo -u postgres psql -c "SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, buffers_backend FROM pg_stat_bgwriter;"

# Eğer checkpoints_req değeri checkpoints_timed'dan çok yüksekse
# max_wal_size değerini artırmanız gerekiyor demektir

Bağlantı ve Parallelism Ayarları

max_connections

Her bağlantı bellek tüketir. PostgreSQL yüksek bağlantı sayısında verimini kaybeder. Production ortamında PgBouncer gibi bir connection pooler kullanmayı kesinlikle öneririm.

# Direct bağlantı kullanıyorsanız
max_connections = 200

# PgBouncer kullanıyorsanız PostgreSQL tarafı daha düşük olabilir
max_connections = 100

Paralel Sorgu Parametreleri

Modern PostgreSQL paralel sorgu çalıştırabilir. Bu ayarlar özellikle analitik sorgularda büyük fark yaratır.

# Paralel worker sayısı (CPU sayısına göre ayarlayın)
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# Paralel sorgu için maliyet eşiği
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

PgBouncer ile Beraber Kullanım Senaryosu

# /etc/pgbouncer/pgbouncer.ini örneği
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

Bu yapıyla PostgreSQL tarafında 100 bağlantıyla 1000 uygulama bağlantısına hizmet verebilirsiniz.

Sorgu Planlayıcı Ayarları

random_page_cost ve seq_page_cost

Planlayıcıya diskten veri okuma maliyetini söyler. SSD kullandığınızda random_page_cost’u düşürmeniz planlayıcının index kullanımını artırmasını sağlar.

# HDD için varsayılan
random_page_cost = 4.0
seq_page_cost = 1.0

# SSD için
random_page_cost = 1.1
seq_page_cost = 1.0

# NVMe SSD için
random_page_cost = 1.0
seq_page_cost = 1.0

enable_* Parametreleri

Bu parametreler planlayıcının belirli join veya scan stratejilerini kullanıp kullanmayacağını kontrol eder. Production’da genellikle bunlara dokunmamanızı öneririm. Yalnızca belirli bir sorguda planlayıcının hatalı karar verdiğini kesin olarak tespit ettiğinizde oturum bazlı kullanın.

# Kötü bir plan tespit ettiğinizde geçici olarak
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_nestloop = on;

statistics_target

Planlayıcı istatistiklerinin ne kadar detaylı toplanacağını belirler. Yüksek değer daha iyi planlar ama ANALYZE süresi uzar.

# Varsayılan 100, genellikle 200-500 arası iyi
default_statistics_target = 200

# Belirli sütunlar için daha yüksek değer
# ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

Logging ve Monitoring Parametreleri

Performans sorunlarını tespit etmek için doğru logging ayarları şart.

# Yavaş sorguları loglamak için (1 saniyenin üstü)
log_min_duration_statement = 1000   # milisaniye cinsinden

# Checkpoint bilgilerini logla
log_checkpoints = on

# Bağlantı/bağlantı kesme logları (yoğun sistemlerde kapatın)
log_connections = off
log_disconnections = off

# Lock timeout logları
log_lock_waits = on
deadlock_timeout = 1s

# Sorgu istatistikleri için pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

pg_stat_statements Kurulumu ve Kullanımı

# pg_stat_statements extension'ı etkinleştirme
# Önce postgresql.conf'a shared_preload_libraries ekleyin, restart yapın
sudo systemctl restart postgresql

# Sonra veritabanında extension'ı oluşturun
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# En çok zaman harcayan sorguları bulma
sudo -u postgres psql -d myapp -c "
SELECT 
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    calls,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
"

Autovacuum Optimizasyonu

Autovacuum yanlış yapılandırıldığında hem tablo şişmesine hem de ani performans düşüşlerine yol açar. Bırakın çalışsın ama akıllıca yapılandırın.

# Autovacuum ayarları
autovacuum = on
autovacuum_max_workers = 4          # Varsayılan 3, yoğun sistemlerde artırın
autovacuum_naptime = 30s            # Ne sıklıkla kontrol etsin
autovacuum_vacuum_threshold = 50    # Minimum değişen satır sayısı
autovacuum_vacuum_scale_factor = 0.02  # Tablonun %2'si değişince vacuum
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.01  # %1 değişince analyze
autovacuum_vacuum_cost_delay = 2ms  # Vacuum'un I/O'ya etkisini azaltır
autovacuum_vacuum_cost_limit = 400  # Varsayılan 200

Büyük Tablolar İçin Tablo Bazlı Autovacuum Ayarları

Milyonlarca satırlı tablolarda scale_factor yetersiz kalır. Tablo bazlı override kullanın:

# psql içinde
sudo -u postgres psql -d myapp -c "
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005
);
"

OLTP vs OLAP Senaryolarına Göre Farklı Konfigürasyonlar

OLTP Odaklı Yapılandırma (32GB RAM, 8 CPU)

Yüksek concurrent transaction, küçük sorgular, hızlı yanıt süresi önceliği:

# Bellek
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 8MB               # Düşük tutun, bağlantı sayısı yüksek
maintenance_work_mem = 1GB

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
synchronous_commit = on      # Veri güvenliği öncelikli

# Bağlantı
max_connections = 300

# Planlayıcı
random_page_cost = 1.1       # SSD varsayımı
default_statistics_target = 200

# Parallelism (OLTP'de genellikle kısıtlayın)
max_parallel_workers_per_gather = 2

OLAP/Analitik Odaklı Yapılandırma (64GB RAM, 16 CPU)

Az sayıda karmaşık sorgu, büyük veri taramaları:

# Bellek
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 256MB             # Yüksek tutun, az bağlantı var
maintenance_work_mem = 2GB

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 8GB
synchronous_commit = off     # Performans öncelikli (dikkatli kullanın)

# Parallelism (OLAP'ta agresif kullanın)
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_parallel_maintenance_workers = 8

# Planlayıcı
random_page_cost = 1.0
default_statistics_target = 500

Konfigürasyonu Doğrulama ve Test Etme

Değişiklikleri yaptıktan sonra doğru uygulandığını kontrol edin:

# Tüm non-default ayarları görüntüleme
sudo -u postgres psql -c "
SELECT name, setting, unit, source 
FROM pg_settings 
WHERE source != 'default' 
ORDER BY name;
"

# Belirli parametreyi kontrol etme
sudo -u postgres psql -c "SHOW shared_buffers;"

# Reload gerektiren değişiklikleri uygulama (restart gerekmez)
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Cache hit ratio kontrolü (bu değer %95+ olmalı)
sudo -u postgres psql -d myapp -c "
SELECT 
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(100.0 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read) + 0.001), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;
"

Cache hit ratio %95’in altındaysa shared_buffers’ı artırmanız ya da daha fazla veriyi önbelleğe alabileceğinizi değerlendirmeniz gerekiyor.

PgTune ile Otomatik Başlangıç Noktası

Sıfırdan başlıyorsanız pgTune aracı iyi bir başlangıç noktası sunar:

# pgTune web arayüzü: https://pgtune.leopard.in.ua
# Ya da komut satırından benzer hesaplama

# Temel sistem bilgilerini toplayın
free -h
nproc
cat /sys/block/sda/queue/rotational  # 0 = SSD, 1 = HDD

pgTune’un önerilerini kör kopyalamayın. Uygulamanızın profiline göre mutlaka ince ayar yapın.

Değişikliklerin Etkisini Ölçme

Ayar değişikliklerini yapmadan önce ve sonra benchmark alın:

# pgbench ile temel performans testi
sudo -u postgres pgbench -i -s 100 myapp  # Test verisi oluştur

# Optimizasyon öncesi
sudo -u postgres pgbench -c 20 -j 4 -T 60 myapp > onceki_sonuc.txt

# postgresql.conf değişikliklerini yapın, restart edin

# Optimizasyon sonrası
sudo -u postgres pgbench -c 20 -j 4 -T 60 myapp > sonraki_sonuc.txt

# Karşılaştırın
diff onceki_sonuc.txt sonraki_sonuc.txt

Sonuç

postgresql.conf optimizasyonu tek seferlik bir iş değil, sürekli izleme ve ince ayar gerektiren bir süreç. Şu sırayla ilerleyin:

• İlk adım: shared_buffers, effective_cache_size ve work_mem değerlerini donanımınıza göre ayarlayın. Bu üç parametre en büyük etkiyi yaratır.
• İkinci adım: pg_stat_statements ve logging ayarlarıyla darboğazları tespit edin.
• Üçüncü adım: WAL, checkpoint ve autovacuum ayarlarını iş yüküne göre optimize edin.
• Dördüncü adım: Planlayıcı ayarlarına storage tipinize göre dokunun.
• Beşinci adım: Düzenli benchmark alarak değişikliklerin etkisini ölçün.

Hiçbir zaman “herkese uyan tek bir yapılandırma” yoktur. 32GB RAM’li bir OLTP sunucusu ile aynı donanımdaki analitik bir veritabanı tamamen farklı ayarlar gerektirir. Kendi sisteminizdeki pg_stat_bgwriter, pg_stat_user_tables ve pg_stat_statements verilerini düzenli olarak izleyin, anomalilere hızlı tepki verin. Doğru yapılandırılmış bir PostgreSQL, donanım kapasitesinin çok daha verimli kullanılmasını sağlar ve pahalı donanım yükseltmelerinin önüne geçebilir.