VACUUM ve ANALYZE ile PostgreSQL Tablo Bakımı

yazar

PostgreSQL veritabanlarında zamanla bir sorun ortaya çıkar: tablolar şişer, sorgular yavaşlar ve disk alanı gereksiz yere dolar. Bunun arkasında PostgreSQL’in çok sürümlü eşzamanlılık kontrolü (MVCC) mekanizması yatar. Her güncelleme ve silme işlemi, eski satır versiyonlarını “ölü tuple” olarak bırakır. İşte VACUUM ve ANALYZE komutları tam bu noktada devreye girer. Bu yazıda bu iki kritik bakım aracını, nasıl çalıştıklarını ve production ortamında nasıl yönetmeniz gerektiğini ele alacağız.

PostgreSQL MVCC ve Ölü Tuple Sorunu

PostgreSQL, bir satırı güncellediğinizde eski versiyonu fiziksel olarak silmez. Bunun yerine eski satırı “görünmez” olarak işaretler ve yeni bir versiyon oluşturur. Aynı şey DELETE işlemlerinde de geçerlidir; satır anında silinmez, sadece işaretlenir.

Bu tasarım eşzamanlı okuma-yazma işlemlerini çok verimli kılar çünkü okuyucular yazıcıları, yazıcılar okuyucuları beklemez. Ancak bunun bir bedeli vardır: tablolarda zamanla binlerce, hatta milyonlarca ölü tuple birikir.

Bunu gerçek dünyadan bir örnekle açıklayalım. E-ticaret sitenizin orders tablosunda günde 50.000 sipariş güncelleniyor diyelim. Her güncelleme eski bir tuple bırakır. Bir haftada 350.000 ölü tuple birikir. Bu durum:

  • Tablo boyutunu gereksiz yere artırır
  • Sequential scan sorgularını yavaşlatır
  • Index bloat’a yol açar
  • Transaction ID wraparound riskini artırır

VACUUM bu ölü tuple’ları temizler ve alanı yeniden kullanılabilir hale getirir.

VACUUM Komutu: Temel Kullanım

En basit haliyle VACUUM şu şekilde çalışır:

# Belirli bir tabloyu vacuum et
psql -U postgres -d mydb -c "VACUUM orders;"

# Tüm veritabanını vacuum et
psql -U postgres -d mydb -c "VACUUM;"

# Verbose çıktı ile vacuum (ne yapıldığını görürsünüz)
psql -U postgres -d mydb -c "VACUUM VERBOSE orders;"

Normal VACUUM, ölü tuple’ları temizler ve bu alanları tablonun kendi içinde yeniden kullanılabilir hale getirir. Ancak bu alan işletim sistemine geri iade edilmez. Yani tablo dosyasının fiziksel boyutu küçülmez.

VACUUM FULL: Dikkatli Kullanın

# VACUUM FULL - tabloyu yeniden yazar, disk alanını geri alır
psql -U postgres -d mydb -c "VACUUM FULL orders;"

# Tüm veritabanı için VACUUM FULL
psql -U postgres -d mydb -c "VACUUM FULL;"

VACUUM FULL, tabloyu baştan yazar ve disk alanını işletim sistemine geri iade eder. Ama ciddi bir bedeli var: tablo üzerinde exclusive lock alır. Bu sürede tabloya hiçbir okuma veya yazma işlemi yapılamaz. Production ortamında bunu sadece acil durumlarda ve bakım penceresi sırasında çalıştırın.

Büyük bir tabloda VACUUM FULL’un ne kadar sürebileceğini görmek için:

# Tablo boyutlarını kontrol et
psql -U postgres -d mydb -c "
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(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;"

Bu sorgu size hangi tablolarda en çok ölü tuple olduğunu gösterir. Dead ratio yüzde 20’nin üzerindeyse o tablo bakıma muhtaçtır.

ANALYZE Komutu: Sorgu Planlayıcısını Besleyin

ANALYZE, tablo istatistiklerini toplar ve bunları pg_statistic sistem tablosuna yazar. PostgreSQL sorgu planlayıcısı (query planner), hangi execution plan’ı seçeceğine bu istatistiklere bakarak karar verir.

# Belirli bir tabloyu analiz et
psql -U postgres -d mydb -c "ANALYZE orders;"

# Tüm veritabanını analiz et
psql -U postgres -d mydb -c "ANALYZE;"

# Verbose çıktı ile
psql -U postgres -d mydb -c "ANALYZE VERBOSE orders;"

# Belirli kolonları analiz et
psql -U postgres -d mydb -c "ANALYZE orders (customer_id, order_date, status);"

Gerçek dünyada şöyle bir senaryo yaşarsınız: tabloya büyük bir veri yükü yaptınız, belki CSV import veya bulk insert. Sorgu planlayıcısı hâlâ eski istatistiklere bakıyor ve yanlış plan seçiyor. ANALYZE çalıştırmak bu sorunu anında çözer.

VACUUM ANALYZE: İkisini Birlikte

Production ortamında bu iki komutu genellikle birlikte kullanırsınız:

# Hem vacuum hem analyze - en yaygın kullanım
psql -U postgres -d mydb -c "VACUUM ANALYZE orders;"

# Tüm veritabanı için
psql -U postgres -d mydb -c "VACUUM ANALYZE;"

# vacuumdb utility ile (daha fazla seçenek sunar)
vacuumdb -U postgres -d mydb --analyze --verbose mydb

Autovacuum: Otomatik Bakım Mekanizması

PostgreSQL 8.1’den itibaren autovacuum daemon’ı varsayılan olarak aktiftir. Bu daemon, tablolardaki değişiklikleri takip eder ve belirli eşiklere ulaşıldığında otomatik olarak VACUUM ve ANALYZE çalıştırır.

Autovacuum ayarlarını postgresql.conf üzerinden yönetirsiniz:

# postgresql.conf içindeki önemli autovacuum parametreleri
autovacuum = on                          # Autovacuum açık/kapalı
autovacuum_naptime = 1min               # Kontrol aralığı
autovacuum_vacuum_threshold = 50        # Minimum dead tuple sayısı
autovacuum_vacuum_scale_factor = 0.2    # Tablonun yüzde kaçı değişince vacuum
autovacuum_analyze_threshold = 50       # Minimum değişen tuple sayısı
autovacuum_analyze_scale_factor = 0.1   # Tablonun yüzde kaçı değişince analyze
autovacuum_vacuum_cost_delay = 2ms      # I/O throttle için bekleme süresi
autovacuum_max_workers = 3             # Eşzamanlı worker sayısı

autovacuum_vacuum_scale_factor = 0.2 demek, tablonun yüzde 20’si kadar ölü tuple biriktiğinde vacuum tetiklenir anlamına gelir. 1 milyon satırlık bir tabloda bu 200.000 ölü tuple demektir. Büyük tablolar için bu değer çok yüksek olabilir.

Tablo Bazlı Autovacuum Ayarları

Büyük ve yoğun kullanılan tablolar için genel ayarlar yeterli olmaz. Storage parametrelerini tablo bazında override edebilirsiniz:

# Yoğun kullanılan orders tablosu için özel autovacuum ayarları
psql -U postgres -d mydb -c "
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 100,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold = 50,
    autovacuum_vacuum_cost_delay = 10
);"

Bu ayarlarla orders tablosu, genel ayarlara göre çok daha agresif şekilde vacuum edilecektir. Yüzde 5 dead ratio’da vacuum tetiklenecek ve istatistikler de daha sık güncellenecek.

Autovacuum’u İzleyin

Autovacuum çalışıyor mu, ne zaman çalıştı, ne kadar sürdü? Bunları takip etmek için birkaç sorgu:

# Son vacuum ve analyze zamanlarını görüntüle
psql -U postgres -d mydb -c "
SELECT 
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    n_dead_tup,
    n_live_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST
LIMIT 20;"

# Şu an çalışan autovacuum worker'larını gör
psql -U postgres -d mydb -c "
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%'
ORDER BY duration DESC;"

Bu sorguları bir monitoring script’ine ekleyerek düzenli çalıştırabilirsiniz. Eğer bir tablo günlerce vacuum görmemişse alarm üretmeye değer.

Transaction ID Wraparound: Kritik Bir Risk

PostgreSQL’de her transaction bir ID alır ve bu ID 32-bit integer’dır. Maksimum değer yaklaşık 2 milyar. Bu dolduğunda PostgreSQL veritabanını korumak için zorla shutdown yapar. Evet, production veritabanınız kapanır.

Bu durum için VACUUM FREEZE devreye girer:

# Transaction ID wraparound riskini kontrol et
psql -U postgres -d mydb -c "
SELECT 
    datname,
    age(datfrozenxid) AS xid_age,
    2000000000 - age(datfrozenxid) AS xid_remaining
FROM pg_database
ORDER BY xid_age DESC;"

# Kritik tablolardaki freeze durumunu kontrol et
psql -U postgres -d mydb -c "
SELECT 
    schemaname,
    tablename,
    age(relfrozenxid) AS table_xid_age
FROM pg_stat_user_tables
JOIN pg_class ON relname = tablename
ORDER BY table_xid_age DESC
LIMIT 10;"

XID age 1.5 milyarı geçiyorsa acil önlem almanız gerekir:

# Aggressive freeze - tüm satırları freeze et
psql -U postgres -d mydb -c "VACUUM FREEZE orders;"

# Tüm veritabanı için
vacuumdb -U postgres --all --freeze --analyze-in-stages

PostgreSQL otomatik olarak XID age 200 milyona ulaştığında anti-wraparound autovacuum başlatır. Bu durumu loglardan şu şekilde görebilirsiniz: autovacuum: found dead tuples veya autovacuum: preventing wraparound mesajları.

Gerçek Dünya Senaryosu: E-Ticaret Veritabanı Bakımı

Büyük bir e-ticaret platformu düşünelim. orders, order_items, products ve sessions tabloları var. sessions tablosu özellikle sorun çıkarıyor çünkü her birkaç dakikada bir yazılıp güncelleniyor.

Sabah monitörünüzde yavaşlayan sorgu alarmı geliyor. Kontrol ediyorsunuz:

# Sorunlu tabloları tespit et
psql -U postgres -d ecommerce -c "
SELECT 
    schemaname || '.' || tablename AS table_name,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS size,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;"

Çıktıda sessions tablosunun dead_pct’si yüzde 45 görünüyor ve son autovacuum 6 saat öncesinde. Autovacuum neden tetiklenmedi? Çünkü tablo çok büyük (10 milyon satır) ve scale_factor’ü geçmemiş olabilir ya da autovacuum worker’ları başka tablolarla meşgul.

Hemen müdahale ediyorsunuz:

# Manual vacuum başlat, lock almadan
psql -U postgres -d ecommerce -c "VACUUM ANALYZE sessions;"

# Sessions tablosu için autovacuum'u daha agresif yap
psql -U postgres -d ecommerce -c "
ALTER TABLE sessions SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 5
);"

Birkaç dakika sonra sorgu süreleri normale döner.

Bakım Script’i: Haftalık Rutin

Production ortamında işleri otomatize etmek için basit bir bakım script’i:

#!/bin/bash
# /usr/local/bin/pg_maintenance.sh
# PostgreSQL haftalık bakım scripti

DB_HOST="localhost"
DB_USER="postgres"
DB_NAME="ecommerce"
LOG_FILE="/var/log/pg_maintenance.log"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

echo "[$DATE] PostgreSQL bakimi basliyor..." >> $LOG_FILE

# Yüksek dead tuple olan tabloları bul ve vacuum et
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "
SELECT schemaname || '.' || tablename
FROM pg_stat_user_tables
WHERE n_dead_tup > 50000
   OR (n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0)) > 0.15
ORDER BY n_dead_tup DESC;" | while read table; do
    if [ -n "$table" ]; then
        echo "[$DATE] Vacuum + Analyze: $table" >> $LOG_FILE
        psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "VACUUM ANALYZE $table;" >> $LOG_FILE 2>&1
    fi
done

# XID wraparound kontrolü
XID_AGE=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "
SELECT age(datfrozenxid) FROM pg_database WHERE datname = '$DB_NAME';")

echo "[$DATE] XID Age: $XID_AGE" >> $LOG_FILE

if [ "$XID_AGE" -gt 1500000000 ]; then
    echo "[$DATE] UYARI: XID wraparound riski! Freeze baslatiliyor..." >> $LOG_FILE
    vacuumdb -h $DB_HOST -U $DB_USER -d $DB_NAME --freeze >> $LOG_FILE 2>&1
fi

echo "[$DATE] Bakim tamamlandi." >> $LOG_FILE

Bu script’i cron’a ekleyin:

# Crontab - her gece 02:00'de çalıştır
0 2 * * * /usr/local/bin/pg_maintenance.sh

# Veya systemd timer ile daha modern yaklaşım
# /etc/systemd/system/pg-maintenance.timer

pg_stat_user_tables ile Sürekli İzleme

Sadece manuel bakım yetmez, sürekli izleme şart. Bir Nagios/Zabbix check script’i yazabilirsiniz:

#!/bin/bash
# pg_check_bloat.sh - Tablo bloat monitoring

WARN_THRESHOLD=20   # Yüzde 20 dead tuple warning
CRIT_THRESHOLD=40   # Yüzde 40 dead tuple critical

RESULT=$(psql -U postgres -d mydb -t -c "
SELECT COUNT(*) 
FROM pg_stat_user_tables
WHERE n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) * 100 > $CRIT_THRESHOLD
  AND n_live_tup > 1000;")

if [ "$RESULT" -gt 0 ]; then
    echo "CRITICAL: $RESULT tablo kritik seviyede bloat iceriyor"
    exit 2
fi

RESULT_WARN=$(psql -U postgres -d mydb -t -c "
SELECT COUNT(*) 
FROM pg_stat_user_tables
WHERE n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) * 100 > $WARN_THRESHOLD
  AND n_live_tup > 1000;")

if [ "$RESULT_WARN" -gt 0 ]; then
    echo "WARNING: $RESULT_WARN tablo uyari seviyesinde bloat iceriyor"
    exit 1
fi

echo "OK: Tum tablolar normal bloat seviyesinde"
exit 0

Sık Yapılan Hatalar

Production’da VACUUM FULL çalıştırmak: En yaygın hata. Tablo lock alındığı için uygulama tamamen durur. Bunun yerine pg_repack extension’ını kullanın; online tablo reorganizasyonu yapar.

Autovacuum’u tamamen kapatmak: Performans sorunlarını çözmek için bazen DBA’lar autovacuum’u kapatır. Bu kısa vadede yardımcı olsa da uzun vadede çok daha büyük problemlere yol açar.

Scale factor’ü büyük tablolar için ayarlamamak: 100 milyon satırlık bir tabloda 0.2 scale_factor, 20 milyon dead tuple birikmesine izin verir. Büyük tablolar için 0.01 veya daha düşük değerler kullanın.

ANALYZE’ı ihmal etmek: Vacuum yaparken analyze yapmayı unutmak, sorgu planlayıcısının eski istatistiklerle çalışmasına neden olur.

Lock bekleme sorunlarını görmezden gelmek: Uzun süren transaction’lar vacuum’ın temizleyebileceği alanı kilitler. pg_stat_activity ile uzun süren transaction’ları düzenli olarak kontrol edin.

Sonuç

VACUUM ve ANALYZE, PostgreSQL’in sağlıklı çalışması için olmazsa olmaz bakım araçlarıdır. Autovacuum varsayılan olarak açık ve bir çok durumda yeterlidir, ancak yoğun iş yüklerinde manuel müdahale ve ince ayar şart. Özellikle büyük tablolar için tablo bazlı autovacuum parametrelerini mutlaka gözden geçirin.

Transaction ID wraparound senaryosu ise hiç görmek istemeyeceğiniz bir durumdu, production veritabanının aniden kapanması. XID age izlemeyi mutlaka monitoring altyapınıza ekleyin.

Günlük rutininize pg_stat_user_tables sorgularını, haftalık rutininize ise dead tuple temizlik script’lerini ekleyin. PostgreSQL size güvenilir ve performanslı bir veritabanı sunar, siz de ona düzenli bakım yapın.

Yorum yapın