Bir tabloda 50 bin satır var. Bir yıldır bu sayı neredeyse hiç değişmedi.

Ama tablonun diskte kapladığı alan 40 MB’tan 6 GB’a çıkmış.

Sorgular yavaşlamış. Index’ler şişmiş.

Tabloda neler olduğunu anlamak için PostgreSQL’in güncelleme işlemini nasıl yaptığını bilmek gerekiyor.

UPDATE aslında bir INSERT

PostgreSQL, MVCC adı verilen bir eşzamanlılık modeli kullanır. Okuyanlar yazanları, yazanlar okuyanları beklemez.

Bunu sağlamak için bir satırı güncellerken eski satırın üzerine yazmaz. Yeni bir satır versiyonu oluşturur ve eskisini “artık geçerli değil” olarak işaretler.

Eski versiyon hemen silinmez. Çünkü o anda çalışan başka bir transaction hâlâ onu görmesi gereken bir snapshot’a sahip olabilir.

Sonuç: her UPDATE ve DELETE, tabloda bir dead tuple bırakır.

50 bin satırlık bir tabloda her satır günde birkaç kez güncelleniyorsa, bir yılda milyonlarca dead tuple birikir.

VACUUM

Dead tuple’ları temizleyen işlem VACUUM. Artık hiçbir transaction’ın görmesi gerekmeyen eski versiyonları bulur ve kapladıkları alanı yeniden kullanılabilir olarak işaretler.

Önemli bir detay: normal VACUUM alanı işletim sistemine geri vermez. Sadece tablo içinde yeniden kullanılabilir hale getirir. Tablo dosyası küçülmez ama büyümeyi durdurur.

PostgreSQL bunu autovacuum ile otomatik yapar. Varsayılan ayarlarda, bir tablodaki dead tuple sayısı satırların yaklaşık yüzde 20’sini geçtiğinde autovacuum devreye girer.

Küçük tablolar için uygun bir değer. Ama 100 milyon satırlık bir tabloda bu, vacuum çalışmadan önce 20 milyon dead tuple birikmesi demek.

Autovacuum’u engelleyen şeyler

Bloat’ın asıl sebebi genellikle autovacuum’un çalışamamasıdır.

Uzun süren transaction’lar. Açık kalan bir transaction, kendisinden sonra oluşan dead tuple’ların temizlenmesini engeller. Saatlerce açık kalan bir rapor sorgusu veya idle in transaction durumunda bekleyen bir connection, bütün veritabanında vacuum’u etkisiz hale getirebilir.

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Uygulamada transaction başlatıp kapatmayı unutan kod veya connection’ı transaction içinde havuza geri veren bir hata, bu listeye düşer. idle_in_transaction_session_timeout ayarı bu durumları otomatik olarak sonlandırabilir.

Unutulmuş replication slot’ları. Artık kullanılmayan bir logical replication slot’u da eski verilerin temizlenmesini engeller.

Yetersiz autovacuum kaynakları. Çok sayıda büyük tablo varsa, varsayılan worker sayısı ve hız sınırları yetmeyebilir.

Tablo bazında ayar

Yoğun güncellenen tablolar için autovacuum eşiklerini düşürmek mümkün:

ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

Artık yüzde 20 yerine yüzde 1 dead tuple biriktiğinde vacuum çalışır.

Bloat oluştuysa

Tablo zaten şiştiyse, normal vacuum alanı geri vermez.

VACUUM FULL tabloyu baştan yazar ve alanı geri verir. Ama işlem süresince tabloyu tamamen kilitler. Production’da büyük bir tablo için genellikle kabul edilemez.

pg_repack gibi araçlar aynı işi tabloyu uzun süre kilitlemeden yapabiliyor. Bloat’ı gidermenin production için daha uygun yolu bu.

Tasarım da etkiler

Bazı tablolar doğası gereği bloat üretir. Her istekte güncellenen bir last_seen_at kolonu, sürekli değişen sayaçlar, kuyruk olarak kullanılan tablolar.

Bu tür verileri ayrı ve küçük bir tabloya taşımak, sık değişen kolonları büyük ve geniş satırlardan ayırmak bloat’ın etkisini azaltır. PostgreSQL’in HOT update özelliği, index’lenmiş kolonlar değişmediğinde ve sayfada yer olduğunda index güncellemesini atlayabiliyor. Tablonun fillfactor değerini düşürmek bu ihtimali artırır.

PostgreSQL’in MVCC modeli eşzamanlılık için büyük bir avantaj.

Ama bu avantajın bir temizlik maliyeti var ve o maliyet ihmal edildiğinde, sessizce diskte birikiyor.