PostgreSQL Autovacuum Ayarları ve Tablo Şişme Problemi

yazar

PostgreSQL veritabanlarında zamanla ortaya çıkan performans sorunlarının büyük çoğunluğu aslında tek bir nedene dayanır: tablo şişmesi, yani bloat. Yeni başlayan DBA’lerin görmezden geldiği, deneyimli sysadminlerin ise uykularını kaçıran bu problem, doğru autovacuum ayarları yapılmadığında sessizce büyüyerek bir gün patlar. Bu yazıda autovacuum’un nasıl çalıştığını, neden yetersiz kalabileceğini ve production ortamında nasıl optimize edileceğini gerçek senaryolarla açıklayacağım.

PostgreSQL MVCC ve Neden Vakuma İhtiyaç Duyulur

PostgreSQL, MVCC (Multi-Version Concurrency Control) adlı bir eşzamanlılık modeli kullanır. Bu modelde bir satırı güncellediğinizde veya sildiğinizde, eski satır fiziksel olarak diskten silinmez. Bunun yerine “ölü tuple” olarak işaretlenir ve yerinde bırakılır. Yeni versiyon ayrı bir yere yazılır.

Bu yaklaşım okuma-yazma çakışmalarını önler ama bedeli vardır: zamanla tablolar gereksiz verilerle dolar, fiziksel boyutu şişer ve sorgu performansı düşer. İşte vacuum bu ölü tuple’ları temizlemek için vardır.

Autovacuum ise bu işlemi otomatik yapan arka plan servisinin adıdır. Doğru ayarlanmadığında ya hiç çalışmaz, ya çok geç çalışır, ya da yetişemez.

Ölü Tuple’ları Görmek

SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Bu sorguyu çalıştırdığınızda dead_ratio değeri %10’un üzerine çıkmış tablolar varsa, autovacuum ayarlarınızı gözden geçirmeniz gerekiyor demektir.

Tablo Şişmesi Gerçek Hayatta Nasıl Görünür

Bir e-ticaret projesi düşünün. Sipariş tablosunda günde 500.000 satır update işlemi yapılıyor; sipariş durumu değişiyor, fiyat güncelleniyor, zaman damgaları yazılıyor. Her update aslında bir insert + ölü tuple bırakma işlemi. Autovacuum bu hıza yetişemediğinde ne olur?

  • Tablo fiziksel olarak büyür, disk I/O artar
  • Sequential scan’ler yavaşlar çünkü daha fazla blok okumak gerekir
  • Index bloat da gelişir, index taramaları yavaşlar
  • Sorgu planları bozulur çünkü istatistikler güncel değildir
  • En kötü senaryoda transaction ID wraparound yaşanır ve PostgreSQL veritabanını sadece-okuma moduna alır

Transaction ID wraparound’u biraz açayım çünkü bu gerçekten kritik. PostgreSQL her işleme 32-bit bir transaction ID atar. Bu sayaç 2 milyarı aştığında sıfırlanır. Eğer vacuum zamanında çalışmazsa eski transaction ID’ler “gelecekte” gibi görünür ve veriler görünmez hale gelir. PostgreSQL bunu önlemek için veritabanını dondurur. Production’da bu senaryoyla karşılaşmak kabus gibidir.

Autovacuum Nasıl Çalışır: Tetikleme Mantığı

Autovacuum bir tabloyu ne zaman vakumlayacağına şu formüle göre karar verir:

vacuum eşiği = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * tablo_satır_sayısı

Varsayılan değerlerle bu şöyle çalışır:

  • autovacuum_vacuum_threshold: 50 (minimum ölü tuple sayısı)
  • autovacuum_vacuum_scale_factor: 0.2 (tablonun %20’si)

Yani 1 milyon satırlık bir tabloda vacuum tetiklenebilmesi için 200.050 ölü tuple birikmesi gerekir. Bu çok yüksek bir eşik. Yüksek trafik alan tablolar için bu değerleri tablo bazında düşürmeniz şart.

Analyze için de benzer bir formül var:

analyze eşiği = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * tablo_satır_sayısı

Varsayılan scale_factor değeri 0.1, yani %10. 10 milyon satırlık tabloda 1 milyon satır değişmeden istatistikler güncellenmez. Bu da sorgu planlarını bozar.

postgresql.conf Temel Autovacuum Ayarları

Global ayarları postgresql.conf dosyasında yapıyorsunuz. Genel bir production sunucusu için önerdiğim başlangıç değerleri:

# Autovacuum worker sayısı (CPU çekirdek sayısına göre artırın)
autovacuum_max_workers = 6

# Her worker'ın ne kadar CPU kullanabileceği (1.0 = tam 1 çekirdek)
autovacuum_vacuum_cost_limit = 800

# Worker'lar arasındaki bekleme süresi (ms)
autovacuum_vacuum_cost_delay = 2ms

# Vacuum tetikleme eşiği (minimum ölü tuple)
autovacuum_vacuum_threshold = 50

# Vacuum için scale factor (%2 - büyük tablolar için önemli)
autovacuum_vacuum_scale_factor = 0.02

# Analyze tetikleme eşiği
autovacuum_analyze_threshold = 50

# Analyze için scale factor (%1)
autovacuum_analyze_scale_factor = 0.01

# Autovacuum ne sıklıkla kontrol eder (saniye)
autovacuum_naptime = 30s

# Transaction ID wraparound koruması için agresif vacuum eşiği
autovacuum_freeze_max_age = 200000000

Değişiklikleri uygulamak için:

# postgresql.conf'u düzenledikten sonra
sudo systemctl reload postgresql

# Ya da psql ile
psql -U postgres -c "SELECT pg_reload_conf();"

# Ayarların aktif olup olmadığını kontrol et
psql -U postgres -c "SHOW autovacuum_vacuum_scale_factor;"

Tablo Bazında Autovacuum Ayarları

Global ayarlar tüm tablolara uygulanır ama her tablo farklıdır. Çok yazılan büyük tablolar için tablo bazında override yapmak çok daha etkili.

-- Yoğun işlem gören siparişler tablosu için agresif ayarlar
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 100,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 1000
);

-- Neredeyse hiç değişmeyen lookup tabloları için gevşek ayarlar
ALTER TABLE country_codes SET (
  autovacuum_vacuum_scale_factor = 0.5,
  autovacuum_analyze_scale_factor = 0.3
);

Bu ayarları uyguladıktan sonra kontrol etmek için:

SELECT
  relname,
  reloptions
FROM pg_class
WHERE reloptions IS NOT NULL
AND relkind = 'r'
ORDER BY relname;

Mevcut Bloat Durumunu Ölçmek

Tablo ve index bloat’ını ölçmek için pgstattuple extension’ını kullanabilirsiniz. Bu extension genellikle postgresql-contrib paketinde gelir.

# Extension'ı yükle
sudo apt install postgresql-contrib  # Debian/Ubuntu
sudo dnf install postgresql-contrib  # RHEL/Fedora
-- Extension'ı aktif et
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Belirli bir tablonun bloat durumu
SELECT
  table_len,
  tuple_count,
  dead_tuple_count,
  dead_tuple_percent,
  free_space,
  free_percent
FROM pgstattuple('public.orders');

Daha hızlı çalışan ama yaklaşık sonuç veren bir sorgu (büyük veritabanlarında pgstattuple çok yavaş olabilir):

WITH bloat_data AS (
  SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    n_dead_tup,
    n_live_tup,
    round(
      100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0),
      2
    ) AS bloat_pct
  FROM pg_stat_user_tables
  WHERE pg_total_relation_size(schemaname||'.'||tablename) > 10 * 1024 * 1024
)
SELECT *
FROM bloat_data
WHERE bloat_pct > 10
ORDER BY bloat_pct DESC;

Autovacuum Loglarını İzlemek

Autovacuum’un ne yaptığını anlamak için log ayarlarını açmak şart:

# postgresql.conf
log_autovacuum_min_duration = 1000  # 1 saniyeden uzun süren vacuum'ları logla
# Log dosyasında autovacuum aktivitesini izle
sudo tail -f /var/log/postgresql/postgresql-*.log | grep -i autovacuum

# Ya da journald kullanıyorsanız
journalctl -u postgresql -f | grep -i autovacuum

Logda şuna benzer satırlar göreceksiniz:

LOG: automatic vacuum of table "mydb.public.orders": index scans: 1,
pages: 0 removed, 8432 remain, 1423 skipped due to pins, 0 skipped frozen;
tuples: 94821 removed, 2341892 remain, 8234 are dead but not yet removable;
avg read rate: 12.423 MB/s, avg write rate: 0.891 MB/s;
buffer usage: 29841 hits, 1293 misses, 234 dirtied;
WAL usage: 423 records, 23 full page images, 123456 bytes;
system usage: CPU: user: 0.89 s, system: 0.12 s, elapsed: 2.34 s

Bu logdan şunları çıkarabilirsiniz:

  • tuples removed: Kaç ölü tuple temizlendi
  • are dead but not yet removable: Aktif transaction’lar nedeniyle henüz silinemeyen tuple’lar (uzun çalışan transaction varsa bu artar)
  • elapsed: Vacuum ne kadar sürdü

Uzun Çalışan Transaction’lar ve Autovacuum’un Etkisiz Kalması

Autovacuum çalışıyor ama yine de ölü tuple’lar birikim yapıyorsa, ilk şüphelenilecek konu uzun çalışan transaction’lardır. Bir transaction açık olduğu sürece, o transaction başlamadan önce oluşan ölü tuple’lar vacuum tarafından silinemez.

-- 5 dakikadan uzun çalışan transaction'ları bul
SELECT
  pid,
  usename,
  application_name,
  state,
  round(extract(epoch from now() - xact_start) / 60, 2) AS duration_minutes,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

Sorunlu bir transaction buldunuz ve sonlandırmanız gerekiyor:

-- Transaction'ı nazikçe sonlandır
SELECT pg_cancel_backend(pid);

-- Eğer cancel çalışmazsa zorla sonlandır
SELECT pg_terminate_backend(pid);

Uygulama tarafında da bu tür transaction’ların oluşmasını engellemek için idle_in_transaction_session_timeout parametresini ayarlayın:

# postgresql.conf
idle_in_transaction_session_timeout = 300000  # 5 dakika (ms cinsinden)
statement_timeout = 60000  # 1 dakika

Manuel Vacuum Senaryoları

Bazen autovacuum’u beklemenin lüksü olmaz. Büyük bir toplu silme işleminden sonra veya şişme kritik seviyeye geldiğinde manuel müdahale gerekir.

# Temel vacuum - ölü tuple'ları temizle
vacuumdb -U postgres -d mydb -t orders -v

# Analyze ile birlikte - istatistikleri de güncelle
vacuumdb -U postgres -d mydb -t orders -z -v

# FULL vacuum - şişmeyi tamamen temizle ama tabloyu kilitler!
vacuumdb -U postgres -d mydb -t orders --full -v

# Tüm veritabanını vacuum et (maintenance window gerekir)
vacuumdb -U postgres -a -j 4 -v  # 4 paralel worker ile

VACUUM FULL kullanırken dikkat: Bu işlem tablo üzerinde exclusive lock alır ve işlem boyunca yazma işlemleri bekler. Production’da maintenance window dışında çalıştırmak tehlikeli. Alternatif olarak pg_repack extension’ını kullanabilirsiniz; bu araç kilitleme olmadan şişmeyi giderir.

# pg_repack kurulumu
sudo apt install postgresql-15-repack

# Kilitsiz tablo yeniden düzenleme
pg_repack -U postgres -d mydb -t orders --no-kill-backend

Autovacuum Performans Ayarı: Cost-Based Throttling

Autovacuum varsayılan olarak disk I/O’yu kısıtlayan bir throttling mekanizması kullanır. Bu, production’da disk okuma/yazma yarışına girmemek için önemli ama bazen vacuum yetersiz kalır.

Throttling şu parametrelerle kontrol edilir:

  • vacuum_cost_page_hit: Shared buffer’da bulunan sayfa maliyeti (varsayılan: 1)
  • vacuum_cost_page_miss: Diskten okunan sayfa maliyeti (varsayılan: 2)
  • vacuum_cost_page_dirty: Yazılan sayfa maliyeti (varsayılan: 20)
  • autovacuum_vacuum_cost_limit: Her worker’ın kullanabileceği toplam maliyet (varsayılan: 200)
  • autovacuum_vacuum_cost_delay: Limit aşıldığında bekleme süresi (varsayılan: 2ms)

Varsayılan 200 değeri çok muhafazakar. SSD’li sistemlerde 400-800 arasına çıkabilirsiniz:

-- Anlık olarak vacuum hızını artır (session level)
SET vacuum_cost_limit = 800;
SET vacuum_cost_delay = 1;

-- Manuel vacuum çalıştır
VACUUM ANALYZE orders;

İzleme ve Alarm Kurulumu

Autovacuum durumunu sürekli izlemek için basit bir bash script:

#!/bin/bash
# /usr/local/bin/check_pg_bloat.sh

DB="mydb"
PG_USER="postgres"
THRESHOLD=20  # %20 bloat eşiği

BLOAT_TABLES=$(psql -U $PG_USER -d $DB -t -A -F'|' << 'EOF'
SELECT
  schemaname || '.' || tablename,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2)
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
AND round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) > 20
ORDER BY 2 DESC;
EOF
)

if [ -n "$BLOAT_TABLES" ]; then
  echo "UYARI: Yüksek bloat tespit edildi:"
  echo "$BLOAT_TABLES" | while IFS='|' read TABLE RATIO; do
    echo "  Tablo: $TABLE - Bloat: %$RATIO"
  done
  # Buraya Slack webhook, mail veya PagerDuty entegrasyonu ekleyin
  exit 1
fi

echo "OK: Bloat seviyeleri normal."
exit 0
# Script'i çalıştırılabilir yap ve cron'a ekle
chmod +x /usr/local/bin/check_pg_bloat.sh
echo "*/15 * * * * postgres /usr/local/bin/check_pg_bloat.sh >> /var/log/pg_bloat_check.log 2>&1" | sudo crontab -

Transaction ID Wraparound Riskini İzlemek

Bu kritik durum için ayrı bir kontrol yapmanızı kesinlikle öneririm:

-- Wraparound'a en yakın veritabanları
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  round(age(datfrozenxid)::numeric / 2000000000 * 100, 2) AS wraparound_pct
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Tablo bazında kontrol
SELECT
  schemaname,
  tablename,
  age(relfrozenxid) AS xid_age,
  round(age(relfrozenxid)::numeric / 2000000000 * 100, 2) AS wraparound_pct
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

wraparound_pct değeri %50’yi geçtiğinde alarm vermenizi, %75’i geçtiğinde acil müdahale planınızı devreye almanızı öneririm. %80 üzeri risk bölgesidir.

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

Bir projede order_items tablosu 50 GB’a şişmişti, sorgu süreleri 200ms’den 8 saniyeye çıkmıştı. Yaptığımız adımlar şunlardı:

İlk olarak mevcut durumu ölçtük, ölü tuple oranı %34’tü. Sonra uzun süren transaction’ları tespit ettik; bir batch işlem 2 saat boyunca açık transaction tutuyordu. O işlemi düzeltip, tablo bazında autovacuum ayarlarını güncelledik:

ALTER TABLE order_items SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_vacuum_threshold = 500,
  autovacuum_vacuum_cost_limit = 1200,
  autovacuum_vacuum_cost_delay = 1
);

Ardından maintenance window’da pg_repack ile mevcut şişmeyi temizledik. Bir hafta sonra tablo boyutu 50 GB’dan 31 GB’a indi, sorgu süreleri 200-400ms bandına döndü.

Sonuç

PostgreSQL autovacuum sistemi, kutudan çıktığı haliyle orta ölçekli workload’lar için yeterlidir. Ama yüksek yazma trafiğine sahip tablolar, büyük veritabanları veya kritik production ortamları için varsayılan ayarları bırakmak zamanla ciddi performans sorunlarına yol açar.

Özet yapacak olursam önce ölçün, sonra ayarlayın. pg_stat_user_tables üzerinden düzenli olarak bloat oranlarını takip edin. Global autovacuum_vacuum_scale_factor değerini 0.2’den 0.02’ye indirmek büyük tablolarda tek başına büyük fark yaratır. Yoğun tablolar için tablo bazında override yapın. Uzun çalışan transaction’ları mutlaka izleyin ve idle_in_transaction_session_timeout ile sınırlayın. Transaction ID wraparound’u asla görmezden gelmeyin; bu veritabanınızı dondurur.

Autovacuum bir sihir değil, bir arka plan işçisi. Ona doğru araçları ve yeterli kaynağı verirseniz, şişme problemi büyük ölçüde kendiliğinden çözülür.

Yorum yapın