postgresql.conf Optimizasyon Rehberi

PostgreSQL varsayılan ayarlarıyla kurulduğunda, 20 yıl önceki bir masaüstü bilgisayarda çalışacakmış gibi davranır. shared_buffers 128 MB'tır, work_mem 4 MB'tır, max_wal_size küçüktür. 32 GB belleğe sahip bir sunucuya bu ayarlarla PostgreSQL kurup "yavaş çalışıyor" demek, sekiz silindirli bir motoru ilk vitesle kullanmaya benzer. postgresql.conf optimizasyonu, bu makası kapatma işidir ve çoğu kurulumda tek başına iki-üç kat kazanç sağlar.

Bu rehberde postgresql.conf dosyasındaki hangi parametrenin ne işe yaradığını, sunucunuzun bellek ve disk yapısına göre nasıl hesaplanacağını ve hangi değişikliğin yeniden başlatma gerektirdiğini anlatacağım. Rakamları ezberlemenizi değil, formülü anlamanızı istiyorum; çünkü 8 GB'lık bir VDS ile 128 GB'lık bir veritabanı sunucusunun doğru ayarları birbirinden tamamen farklıdır. Ayrıca ayar değiştirdikten sonra gerçekten iyileşme olup olmadığını nasıl ölçeceğinize de değineceğim.

Dosyanın Yeri ve Değişiklikleri Uygulamak#

Ubuntu ve Debian'da yapılandırma dosyaları sürüm ve küme adına göre ayrılır. Dosyanın yerini tahmin etmek yerine veritabanına sorun:

SHOW config_file;
-- Örnek çıktı: /etc/postgresql/16/main/postgresql.conf
SHOW data_directory;

Ayarları doğrudan ana dosyaya yazmak yerine, kendi dosyanızı conf.d dizinine koymak daha temizdir; böylece paket güncellemesi sizin değişikliklerinizi ezmez:

sudo -u postgres tee /etc/postgresql/16/main/conf.d/10-tuning.conf > /dev/null <<'EOF'
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 2GB
EOF

PostgreSQL'de ayarlar üç sınıfa ayrılır ve hangisinin ne gerektirdiğini bilmezseniz gereksiz yere sunucu yeniden başlatırsınız:

Bir parametrenin hangi sınıfa girdiğini şu sorguyla öğrenirsiniz:

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections','wal_level');

context sütunu postmaster diyorsa yeniden başlatma şarttır. Değişikliği yeniden başlatmadan yüklemek için:

sudo systemctl reload postgresql@16-main
# ya da SQL tarafından:
# SELECT pg_reload_conf();

Bellek Parametreleri#

Bellek ayarları, optimizasyonun kalbidir ve en çok yanlış anlaşılan kısımdır. Dört parametreyi ayrı ayrı ele alalım.

shared_buffers — PostgreSQL'in kendi sayfa önbelleğidir. Diskten okunan veri sayfaları önce buraya gelir. Klasik başlangıç değeri toplam belleğin dörtte biridir. 32 GB RAM'li bir sunucuda 8 GB uygundur. Yarıdan fazlasına çıkmak genelde ters teper, çünkü PostgreSQL işletim sisteminin sayfa önbelleğinden de faydalanır ve iki katmanda aynı veriyi tutmak boşa bellek harcar.

effective_cache_size — Bu parametre hiçbir bellek ayırmaz; planlayıcıya "disk önbelleğinde kabaca ne kadar veri bulunabilir" bilgisini verir. Yanlış ayarlanırsa planlayıcı indeks taraması yerine tam tablo taraması seçer. Toplam belleğin dörtte üçü iyi bir tahmindir; 32 GB'lık sunucuda 24 GB yazın.

work_mem — Sıralama ve karma (hash) işlemlerinin diske taşmadan kullanabileceği bellek miktarıdır. En tehlikeli parametre budur çünkü bağlantı başına değil, işlem düğümü başına ayrılır. 100 eşzamanlı bağlantı ve sorgu başına 3 sıralama düğümü varsa, 32 MB'lık bir work_mem en kötü ihtimalle 9,6 GB tüketir. Formül şudur:

work_mem ≈ (Kullanılabilir RAM - shared_buffers) / (max_connections * ortalama_düğüm_sayısı)

Pratikte 4-8 MB'tan başlayıp yavaş sorgularda oturum bazında yükseltmek daha güvenlidir:

-- Yalnızca bu ağır rapor sorgusu için geçici olarak yükselt
SET LOCAL work_mem = '256MB';
SELECT ... ;  -- ağır sıralama içeren rapor

maintenance_work_mem — VACUUM, CREATE INDEX ve ALTER TABLE gibi bakım işlerinin kullandığı bellektir. Bu işler eşzamanlı olarak az sayıda çalıştığı için cömert olabilirsiniz; 1-2 GB tipik bir değerdir ve indeks oluşturma süresini belirgin şekilde kısaltır.

Bu tablodaki work_mem değerleri max_connections değerinin 100-200 aralığında olduğunu varsayar. Bağlantı sayınız yüksekse mutlaka düşürün ya da bir bağlantı havuzu kullanın; PgBouncer kurulumu yazısı bu konuyu ayrıntılı anlatıyor.

WAL ve Kontrol Noktası Ayarları#

WAL (Write-Ahead Log), PostgreSQL'in dayanıklılık mekanizmasıdır: her değişiklik önce WAL'a yazılır, sonra veri dosyalarına uygulanır. Varsayılan ayarlar küçük olduğu için yoğun yazma altında kontrol noktaları (checkpoint) çok sık tetiklenir ve her seferinde disk I/O tavan yapar.

# Yazma yoğun bir sistem için makul başlangıç
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on

checkpoint_completion_target = 0.9, kontrol noktası yazımını iki kontrol noktası arasındaki sürenin yüzde 90'ına yayar; böylece disk yükü ani bir tepe yerine düz bir çizgi hâline gelir. max_wal_size büyütmek kontrol noktalarını seyrekleştirir ama kurtarma süresini uzatır; bu bir denge işidir.

Kontrol noktalarınızın çok sık olup olmadığını loglardan görebilirsiniz:

log_checkpoints = on
log_min_duration_statement = 500ms
log_line_prefix = '%m [%p] %q%u@%d '
log_lock_waits = on
log_temp_files = 0

log_temp_files = 0 çok değerli bir ayardır: work_mem yetmediği için diske taşan her sıralama loga düşer. Loglarda sık temporary file satırı görüyorsanız, work_mem'i yükseltmeniz gerektiğinin en net kanıtı odur. Kontrol noktası logunda checkpoints are occurring too frequently uyarısını görürseniz max_wal_size küçük kalmış demektir.

Disk ve Planlayıcı Ayarları#

PostgreSQL varsayılan olarak dönen diskte çalıştığını varsayar ve rastgele okumayı sıralı okumadan dört kat pahalı sayar. SSD veya NVMe kullanıyorsanız bu varsayım yanlıştır ve planlayıcıyı indeks kullanmaktan caydırır:

# SSD/NVMe için
random_page_cost = 1.1
seq_page_cost = 1.0
effective_io_concurrency = 200

random_page_cost değerini 4'ten 1.1'e çekmek, tek başına birçok sorguda tam tablo taramasını indeks taramasına döndürür ve bunun etkisi bazen onlarca kat olur. Dönen disk kullanıyorsanız varsayılanı koruyun.

max_connections konusunda yaygın bir yanılgı var: yüksek tutmak kapasite artırmaz, aksine azaltır. Her bağlantı ayrı bir işletim sistemi süreci demektir ve her biri bellek tüketir. Genel kural, CPU çekirdek sayısının üç-dört katıdır. 8 çekirdekli bir sunucuda 100-200 arası bağlantı yeterlidir; daha fazlasına ihtiyaç duyuyorsanız çözüm bağlantı havuzudur, max_connections artırmak değil.

max_connections = 150
superuser_reserved_connections = 5

Paralel sorgu ayarları da çekirdek sayınıza bağlıdır:

max_worker_processes = 8            # CPU çekirdek sayısı
max_parallel_workers = 8            # aynı değer
max_parallel_workers_per_gather = 4 # tek sorguya en fazla 4 işçi

Bu değerleri çekirdek sayısının üzerine çıkarmak, paralel işçilerin birbirini beklemesine yol açar ve raporlama sorgularını hızlandırmak yerine yavaşlatır.

Autovacuum Ayarlarını Gözden Geçirmek#

postgresql.conf optimizasyonunun en sık atlanan kısmı autovacuum'dur. Varsayılan ayarlar küçük tablolar için tasarlanmıştır; milyonlarca satırlık bir tabloda autovacuum_vacuum_scale_factor = 0.2 demek, tablo yüzde 20 oranında ölü satır biriktirmeden temizliğin başlamayacağı anlamına gelir. On milyon satırlık bir tabloda bu iki milyon ölü satır demektir.

autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000

Yoğun güncellenen tek bir tablo varsa, genel ayarı bozmadan tablo bazında da ayarlayabilirsiniz:

ALTER TABLE oturumlar SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit  = 4000
);

Bu mekanizmanın nasıl çalıştığını ve şişme (bloat) sorununu ayrıntılı görmek için PostgreSQL VACUUM ve autovacuum yazısına göz atın.

Değişikliği Ölçmek#

Ayar değiştirdikten sonra "daha hızlı hissettim" demek yeterli değil. pg_stat_statements eklentisi, hangi sorgunun toplam ne kadar süre harcadığını gösterir ve optimizasyonun asıl pusulasıdır:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Yeniden başlattıktan sonra eklentiyi etkinleştirin ve en pahalı sorguları listeleyin:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    round(total_exec_time::numeric, 1) AS toplam_ms,
    calls,
    round(mean_exec_time::numeric, 2)  AS ortalama_ms,
    left(query, 90)                    AS sorgu
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Önbellek isabet oranını da izleyin; yüzde 99'un altına düşüyorsa shared_buffers küçük kalmış olabilir:

SELECT
    round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS onbellek_isabet_yuzdesi
FROM pg_stat_database;

Ayarları değiştirmeden önce mevcut durumu kaydedin, tek seferde tek bir grup parametre değiştirin ve aynı sorgu kümesiyle tekrar ölçün. Aynı anda on parametre değiştirirseniz hangisinin işe yaradığını asla bilemezsiniz.

Sık Yapılan Hatalar#

shared_buffers'ı belleğin tamamına yakın vermek. İşletim sisteminin sayfa önbelleğine yer bırakmazsanız çift önbellekleme yaparsınız ve toplam verim düşer. Dörtte bir kuralından ancak ölçerek sapın.

work_mem'i genel olarak çok yükseltmek. Tek bir rapor için 512 MB verdiğinizi düşünün; aynı anda 20 kullanıcı rapor açtığında sunucunun belleği tükenir ve OOM killer PostgreSQL sürecini öldürür. Genel değeri düşük tutup ihtiyaç duyulan yerde SET LOCAL kullanın.

max_connections'ı sorunun çözümü sanmak. "Too many connections" hatası alınca sayıyı 500'e çıkarmak, sorunu bellek tükenmesine dönüştürür. Doğru çözüm bağlantı havuzudur. Benzer bir sorunun MySQL tarafındaki karşılığı için MySQL too many connections hatası yazısında aynı mantığı görebilirsiniz.

Hazır hesaplayıcı çıktısını körü körüne uygulamak. İnternetteki ayar üreticileri iyi bir başlangıç noktasıdır ama iş yükünüzü bilmezler. OLTP ile raporlama iş yükünün doğru ayarları farklıdır.

fsync veya synchronous_commit'i kapatmak. fsync = off ayarını performans için önerenlere kulak asmayın: elektrik kesintisinde veritabanınız onarılamaz biçimde bozulur. synchronous_commit = off ise daha ölçülü bir seçenektir; veri bozulmaz ama son birkaç işlemi kaybedebilirsiniz. Kayıp toleransınızı bilmeden dokunmayın.

Ayar dosyasını yedeklememek. Değişiklikten önce dosyanın kopyasını alın ve tercihen sürüm kontrolünde tutun. Yedekleme stratejinizin şema ve yapılandırmayı da kapsaması için pg_dump ile yedekleme yazısına bakın.

Sıkça Sorulan Sorular#

shared_buffers değerini kaç yapmalıyım#

Genel kabul gören başlangıç, toplam sistem belleğinin dörtte biridir. 16 GB RAM'li bir sunucuda 4 GB, 32 GB'lık bir sunucuda 8 GB uygun bir değerdir. Belleğin yarısından fazlasını vermek genellikle ters teper çünkü PostgreSQL işletim sisteminin sayfa önbelleğinden de yararlanır. Değişiklik için sunucuyu yeniden başlatmanız gerekir.

postgresql.conf değişikliği için yeniden başlatma şart mı#

Hepsi için değil. work_mem, log ayarları ve autovacuum parametreleri gibi sighup sınıfındaki ayarlar SELECT pg_reload_conf() ya da systemctl reload ile anında uygulanır. shared_buffers, max_connections ve wal_level gibi postmaster sınıfındaki ayarlar ise yeniden başlatma ister. Hangisinin ne olduğunu pg_settings görünümündeki context sütunundan görebilirsiniz.

work_mem çok yüksek olursa ne olur#

Bellek tükenir. work_mem bağlantı başına değil, sorgu planındaki her sıralama veya karma düğümü başına ayrılır; yani tek bir sorgu bu değeri birkaç kez tüketebilir. Yüz eşzamanlı bağlantıda yüksek bir work_mem, sunucunun belleğini bitirip PostgreSQL sürecinin işletim sistemi tarafından sonlandırılmasına yol açabilir. Genel değeri düşük tutup ağır sorgularda oturum bazında yükseltin.

Ayarları değiştirdikten sonra iyileşmeyi nasıl ölçerim#

pg_stat_statements eklentisini etkinleştirin ve değişiklikten önce en pahalı sorguların toplam sürelerini kaydedin. Aynı iş yükünü tekrarladıktan sonra pg_stat_statements_reset() ile sayaçları sıfırlayıp yeniden ölçün. Ayrıca log_min_duration_statement ile yavaş sorguları, log_temp_files ile diske taşan sıralamaları takip edin; bu iki log satırı çoğu zaman doğrudan hangi parametreyi ayarlamanız gerektiğini söyler.

effective_cache_size gerçekten bellek ayırır mı#

Hayır, tek bir bayt bile ayırmaz. Bu parametre yalnızca sorgu planlayıcısına bir tahmin verir: "diskteki verinin ne kadarı önbellekte bulunabilir". Değeri düşük bırakırsanız planlayıcı indeks taramasını gereğinden pahalı sanır ve tam tablo taraması seçer. Toplam belleğin dörtte üçü iyi bir tahmindir ve değiştirmek için yeniden başlatma gerekmez.

Küçük bir VDS'te PostgreSQL nasıl ayarlanmalı#

2-4 GB belleğe sahip bir sunucuda cömert olmayın: shared_buffers 512 MB - 1 GB, work_mem 4 MB, maintenance_work_mem 128-256 MB, max_connections 50-100 aralığında tutun ve mutlaka bir bağlantı havuzu kullanın. SSD kullanıyorsanız random_page_cost = 1.1 ayarını yapmayı unutmayın; küçük sunucularda en büyük tek kazancı genelde bu sağlar.

Kapanış#

postgresql.conf optimizasyonu sihir değil, sunucunun gerçek kaynaklarını veritabanına doğru anlatma işidir. Aklınızda tutmanız gereken dört alışkanlık şunlar: bellek parametrelerini toplam RAM'e oranlayarak hesaplayın ve work_mem konusunda tutumlu olun, SSD kullanıyorsanız random_page_cost değerini mutlaka düşürün, log_temp_files ve log_checkpoints ayarlarını açıp logların size ne söylediğini dinleyin ve her değişiklikten sonra pg_stat_statements ile ölçüm yapın.

Doğru ayarlar ancak altındaki donanım tutarlı performans verdiğinde anlam kazanır; özellikle disk gecikmesi ve bellek genişliği bu işin belirleyicisidir. NVMe destekli, kaynakları garantili VDS ve bulut sunucu paketlerimizde PostgreSQL'i kendi ayarlarınızla işletebilir, ayar ve izleme yükünü devretmek isterseniz sunucu yönetimi hizmetimizden yararlanabilirsiniz. Yoğun yazma altındaki verinizin güvenliği için yedekleme çözümlerimize de göz atın.