MVCC, VACUUM ve Autovacuum: Dead Tuple, Bloat, HOT Update ve Transaction ID Wraparound

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı) üzerinde, 02'de kurduğumuz Bookstore lab'ında, şema yeniden yüklendikten hemen sonra çalıştırılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) her gece depodan gelen sayımla stock_items tablosundaki 40.000 satırın hepsini güncelliyor. Denemenin ortasında autovacuum araya girmesin diye tablo için kapattık ve tek bir UPDATE çalıştırdık:

ALTER TABLE stock_items SET (autovacuum_enabled = off);
SELECT pg_size_pretty(pg_relation_size('stock_items')) AS heap, pg_relation_size('stock_items') / 8192 AS pages;
UPDATE stock_items SET quantity_reserved = quantity_reserved + 1;
SELECT pg_size_pretty(pg_relation_size('stock_items')) AS heap, pg_relation_size('stock_items') / 8192 AS pages;

Satır sayısı değişmedi, kolonların tipi ve uzunluğu da aynı. SQL Server'da bu güncelleme satırları yerinde değiştirir ve tablo boyutu yerinde kalır. PostgreSQL'de tablo kaç page olur? Ardından VACUUM çalıştırırsan dosya eski boyutuna döner mi?

  heap   | pages
---------+-------
 2040 kB |   255

UPDATE 40000
  heap   | pages
---------+-------
 4080 kB |   510

Tablo iki katına çıktı. 01'de iki oturumla gördüğümüz MVCC (Multi-Version Concurrency Control; okuyanın satırın eski sürümünü okuyup yazanı beklemediği yöntem) davranışının toplu hâli bu: her UPDATE yeni bir satır sürümü yazıyor, eskisi yerinde kalıyor. Bu yazıda o eski sürümlerin ne olduğunu, VACUUM'un onlarla ne yaptığını ve ne yapmadığını, autovacuum'un ne zaman devreye girdiğini, onu neyin durdurduğunu ve PostgreSQL'e özgü bir risk olan transaction ID wraparound'u görüyoruz. Sorunun ikinci yarısının cevabı ikinci bölümde.

xmin, xmax ve Dead Tuple

PostgreSQL'de her satır sürümü (tuple) başlığında iki transaction ID taşır: xmin onu oluşturan transaction, xmax onu silen ya da güncelleyen transaction. UPDATE, eski sürümün xmax'ına kendi ID'sini yazar ve yeni bir sürüm ekler; DELETE yalnızca xmax'ı doldurur. Bir sorgu hangi sürümü göreceğine kendi snapshot'ına göre karar verir. Eski sürümü görebilecek hiçbir transaction kalmadığında o sürüm dead tuple olur: yer kaplar ama kimseye görünmez.

Güncellemeden sonra istatistik view'ı ve pgstattuple extension'ı (03'te kurduk) aynı şeyi söylüyor:

SELECT n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'stock_items';
SELECT tuple_count, dead_tuple_count, round(dead_tuple_percent::numeric, 1) AS dead_pct,
       round(free_percent::numeric, 1) AS free_pct
FROM pgstattuple('stock_items');

 n_live_tup | n_dead_tup | n_tup_upd | n_tup_hot_upd
------------+------------+-----------+---------------
      40000 |      40000 |     40000 |             0

 tuple_count | dead_tuple_count | dead_pct | free_pct
-------------+------------------+----------+----------
       40000 |            40000 |     46.0 |      0.1

40.000 canlı, 40.000 ölü satır; dosyanın %46'sı dead tuple. Bir not: pg_stat_user_tables sayaçları her ifadeden hemen sonra değil, oturum kısa aralıklarla bildirdiğinde güncellenir; script içinde hemen okumak için önce SELECT pg_stat_force_next_flush(); çalıştırdık. pgstattuple ise tabloyu baştan sona okur; büyük tabloda pahalıdır, production'da pg_stat_user_tables yeterli.

VACUUM Ne Yapar, Ne Yapmaz

VACUUM (VERBOSE) stock_items;
SELECT pg_size_pretty(pg_relation_size('stock_items')) AS heap;

INFO:  finished vacuuming "bookstore.public.stock_items": index scans: 1
pages: 0 removed, 510 remain, 510 scanned (100.00% of total), 0 eagerly scanned
tuples: 40000 removed, 40000 remain, 0 are dead but not yet removable
...
visibility map: 510 pages set all-visible, 254 pages set all-frozen (0 were all-visible)
index scan needed: 255 pages from table (50.00% of total) had 40000 dead item identifiers removed

  heap
---------
 4080 kB

VACUUM 40.000 dead tuple'ı sildi, index'teki onlara işaret eden girişleri temizledi ve page'leri visibility map'te all-visible olarak işaretledi (06'daki index-only scan'in Heap Fetches: 0 diyebilmesi buna bağlı). Ama dosya 4080 kB'ta kaldı. Tahminin ikinci yarısının cevabı bu: VACUUM alanı yeniden kullanıma açar, işletim sistemine geri vermez. İstisnası, tablonun sonundaki page'lerin tamamen boşalması; o zaman dosyanın sonunu kesebilir. Boşalan alan tabloda kalır ve sonraki yazmalar onu kullanır:

UPDATE stock_items SET quantity_reserved = quantity_reserved + 1;
SELECT pg_size_pretty(pg_relation_size('stock_items')) AS heap;
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'stock_items';

  heap
---------
 4080 kB

 n_tup_upd | n_tup_hot_upd
-----------+---------------
     80000 |           105

İkinci toplu güncelleme dosyayı büyütmedi; yeni sürümler ilk güncellemede boşalan page'lere yazıldı. Tablo bu boyutta dengeye oturur. Sık güncellenen bir tablonun, canlı satırlarının kapladığından daha büyük durması bu yüzden normaldir; sorun, bu boşluğun sürekli büyümesidir (bloat).

Dosyayı gerçekten küçültmek için tabloyu yeniden yazmak gerekir:

VACUUM FULL stock_items;
SELECT pg_size_pretty(pg_relation_size('stock_items')) AS heap, pg_size_pretty(pg_relation_size('stock_items_pkey')) AS pkey;

  heap   |  pkey
---------+--------
 2040 kB | 896 kB

Tablo 2040 kB'a, primary key index'i 1768 kB'tan 896 kB'a döndü. Bedeli kilit: VACUUM FULL tablonun yeni bir kopyasını yazar ve süre boyunca ACCESS EXCLUSIVE lock tutar; okuma bile bekler. 2,5 milyon satırlık order_items'ta bir oturum VACUUM FULL çalıştırırken diğerinden okumayı denedik:

SELECT l.mode, l.granted, a.query FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'order_items'::regclass;
SET lock_timeout = '200ms';
SELECT count(*) FROM order_items WHERE order_id = 42;

        mode         | granted |          query
---------------------+---------+-------------------------
 AccessExclusiveLock | t       | VACUUM FULL order_items

ERROR:  canceling statement due to lock timeout

Ayrıca yeni kopya için tablonun boyutu kadar boş disk ister. Production'da VACUUM FULL bakım penceresi işidir; yerine kilitlemeden çalışan üçüncü parti pg_repack extension'ı kullanılır. PostgreSQL 19 bu iş için çekirdeğe REPACK komutunu ve kilitlemeden çalışan REPACK (CONCURRENTLY) seçeneğini getiriyor; serinin son yazısında.

HOT Update ve fillfactor

İkinci güncellemede 80.000 güncellemenin yalnızca 105'i HOT (Heap-Only Tuple; yeni sürüm aynı page'e yazılıp index'e yeni giriş eklenmeyen güncelleme) oldu. HOT için iki koşul var: güncellenen kolon index'li olmayacak (06'da bunu gördük) ve yeni sürüm eski sürümle aynı page'e sığacak. Buradaki sorun ikincisi. Canlı satırlar ilk güncellemeden sonra tamamen dolu page'lerdeydi; boş alan ise başka page'lerdeydi. Yeni sürüm başka page'e gidince index'e de yeni giriş yazılması gerekti.

Gerçek iş yükü toplu güncelleme değil, gün içinde küçük gruplar hâlinde güncellemedir. Tablonun iki kopyasını aldık: biri varsayılan fillfactor = 100 ile, diğeri page'lerin %20'sini boş bırakan fillfactor = 80 ile. Her birinde satırların onda birini on tur boyunca güncelledik:

CREATE TABLE stock_ff100 AS SELECT * FROM stock_items;
CREATE TABLE stock_ff80 WITH (fillfactor = 80) AS SELECT * FROM stock_items;
ALTER TABLE stock_ff100 ADD PRIMARY KEY (book_id);
ALTER TABLE stock_ff80 ADD PRIMARY KEY (book_id);
DO $$
BEGIN
    FOR r IN 0..9 LOOP
        UPDATE stock_ff100 SET quantity_reserved = quantity_reserved + 1 WHERE book_id % 10 = r;
        UPDATE stock_ff80 SET quantity_reserved = quantity_reserved + 1 WHERE book_id % 10 = r;
        COMMIT;
    END LOOP;
END $$;

   relname   | n_tup_upd | n_tup_hot_upd | hot_pct |  heap
-------------+-----------+---------------+---------+---------
 stock_ff100 |     40000 |         33218 |      83 | 2392 kB
 stock_ff80  |     40000 |         40000 |     100 | 2560 kB

Varsayılan tabloda bile güncellemelerin %83'ü HOT oldu. Bunu sağlayan page pruning: PostgreSQL bir page'e erişirken üzerinde temizlenebilecek dead tuple görürse, VACUUM'u beklemeden o page'i kendisi temizler. İlk turlarda dolu page'lerde yer yoktu; o güncellemeler başka page'lere gitti ve tablo 2048 kB'tan 2392 kB'a büyüdü. fillfactor = 80 ile başlangıçta tablo %25 büyük, ama bütün güncellemeler HOT oldu ve tablo hiç büyümedi. Stok, sipariş durumu, sayaç gibi sık güncellenen kolonları olan tablolarda 80–90 arası bir fillfactor iyi bir başlangıç. Sadece insert alan, hiç güncellenmeyen tablolarda (order_items) varsayılan 100 doğru.

Autovacuum: Eşikler ve Tablo Bazında Ayar

Autovacuum launcher (01'deki background process) her database'e varsayılan olarak dakikada bir (autovacuum_naptime = 60s) bakar ve eşiği aşan tablolar için worker başlatır (varsayılan 3 worker). Bir tablonun vacuum eşiği:

vacuum threshold = min(autovacuum_vacuum_max_threshold,
                       autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples)

Varsayılanlar 50 + %20; autovacuum_vacuum_max_threshold 18 ile geldi ve varsayılanı 100 milyon. Insert ağırlıklı tablolar için 13'ten beri ayrı bir insert eşiği var (1000 + %20). Lab'daki tablolar için hesap:

   relname   | reltuples | vacuum_threshold
-------------+-----------+------------------
 order_items |   2500496 |           500149
 orders      |   1000000 |           200050
 stock_items |     40000 |              500

orders için 200 bin dead tuple birikmeden autovacuum çalışmaz; tablo büyüdükçe bu sayı da büyür ve bir milyar satırlık tabloda 200 milyonu bulur (18'deki üst sınır tam bu durum için). Sık güncellenen tablolarda eşiği tablo bazında düşür. stock_items için %1 + 100 verdik, yani 500; ayrıca bu tablonun autovacuum çalışmalarını süreye bakmadan log'a yazdırdık (log_autovacuum_min_duration varsayılanı 10 dakika):

ALTER TABLE stock_items SET (autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 100, log_autovacuum_min_duration = 0);
UPDATE stock_items SET quantity_reserved = quantity_reserved + 1 WHERE book_id <= 1000;

LOG:  automatic vacuum of table "bookstore.public.stock_items": index scans: 1
	pages: 0 removed, 262 remain, 15 scanned (5.73% of total), 0 eagerly scanned
	tuples: 1000 removed, 39966 remain, 0 are dead but not yet removable
	...
	index scan needed: 7 pages from table (2.67% of total) had 965 dead item identifiers removed

   relname   | n_dead_tup |        last_autovacuum        | autovacuum_count
-------------+------------+-------------------------------+------------------
 stock_items |          0 | 2026-11-14 02:20:02.333379+00 |                1

1000 dead tuple eşiği geçti ve bir dakika içinde autovacuum çalıştı. Dikkat: 262 page'in yalnızca 15'ini taradı. Visibility map'te all-visible işaretli page'lerde dead tuple olamayacağı için onları atlıyor; bu yüzden düzenli çalışan autovacuum ucuzdur. Index'te 1000 değil 965 giriş temizlendi; güncellemelerin bir kısmı HOT olmuştu ve onların index girişi yoktu.

Autovacuum production'ı yavaşlatmasın diye kendini frenler: belirli bir iş miktarından (vacuum_cost_limit, 200) sonra autovacuum_vacuum_cost_delay (2 ms) kadar uyur. Büyük ve yoğun tablolarda autovacuum'un dead tuple üretim hızına yetişemediğini görürsen ilk ayar bu frenin limitini yükseltmek ve worker sayısını artırmaktır. 18'de autovacuum_worker_slots sayesinde autovacuum_max_workers artık restart olmadan bu üst sınıra kadar yükseltilebiliyor.

Uzun Transaction VACUUM'u Durdurur

Bir dead tuple ancak onu görebilecek hiçbir transaction kalmadığında silinebilir. Bookstore API'sinde bir endpoint transaction açıp siparişi güncelliyor, sonra commit etmeden önce ödeme servisini çağırıyor. Bunu bir oturumda canlandırdık ve transaction'ı açık bıraktık:

BEGIN;
UPDATE orders SET status = status WHERE order_id = 42;

Başka bir oturumda stock_items'ta 1000 satır güncelleyip VACUUM çalıştırdık:

tuples: 0 removed, 41000 remain, 1000 are dead but not yet removable

 pid |    usename    |        state        | backend_xid | backend_xmin |    xact_age    |                     last_query
-----+---------------+---------------------+-------------+--------------+----------------+----------------------------------------------------
 358 | bookstore_app | idle in transaction |        1063 |              | 00:00:02.01194 | UPDATE orders SET status = status WHERE order_id =

Açık transaction başka bir tabloya dokunmuş olsa da VACUUM 1000 dead tuple'ın hiçbirini silemedi. Sınır database genelinde: o transaction'ın ID'sinden (1063) sonra ölen hiçbir satır sürümü, transaction bitene kadar silinemez. İlk oturum commit ettikten sonra aynı komut 1000 removed dedi. Production'da bu oturum saatlerce açık kalırsa bütün database'teki güncellemeler birikir, tablolar ve index'ler şişer.

Aynı etkiyi yaratan başka kaynaklar da var: REPEATABLE READ transaction'ında uzun süren bir rapor, terk edilmiş bir replication slot ve hot_standby_feedback = on olan bir replica'daki uzun sorgu. Sonuncusu sinsi: replica, primary'ye "bu satırlara hâlâ ihtiyacım var" diye bildirir ve primary'deki VACUUM onları silmez. Replica'daki raporlama sorgusu primary'de bloat yaratır. Kontrol için pg_stat_activity'de backend_xid ve backend_xmin'i, pg_replication_slots'ta xmin'i izle. Önlem olarak idle_in_transaction_session_timeout (varsayılan kapalı) ile boşta bekleyen transaction'ları sonlandırabilirsin; bu ve diğer timeout'lar 09'da.

Transaction ID Wraparound ve Freeze

Transaction ID'ler 32 bit ve karşılaştırma döngüsel: her ID için yaklaşık iki milyar ID "geçmişte", iki milyar ID "gelecekte" sayılır. Hiç dokunulmayan bir satırın xmin'i yeterince eskirse bir gün "gelecekte" görünür ve satır kaybolmuş gibi olur. PostgreSQL bunu freeze ile önler: VACUUM, yeterince eski satırları "frozen" olarak işaretler ve bunlar artık her transaction'a görünür. Her tablonun en eski dondurulmamış ID'si relfrozenxid'de, database'inki datfrozenxid'de durur:

SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;
SELECT c.relname, age(c.relfrozenxid) AS xid_age, pg_size_pretty(pg_table_size(c.oid)) AS size
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 't') AND n.nspname = 'public'
ORDER BY age(c.relfrozenxid) DESC LIMIT 3;

  datname  | xid_age
-----------+---------
 postgres  |     588
 template1 |     588
 template0 |     588
 bookstore |     588

  relname  | xid_age |  size
-----------+---------+---------
 authors   |      68 | 304 kB
 books     |      67 | 3592 kB
 customers |      66 | 9360 kB

Lab yeni olduğu için yaşlar yüzlerle ölçülüyor. Önemli eşikler şunlar. Bir tablonun yaşı autovacuum_freeze_max_age'i (200 milyon) geçince autovacuum, tablo için autovacuum kapalı olsa bile, bütün tabloyu tarayan "anti-wraparound" vacuum başlatır; büyük bir tabloda bu saatler sürebilir ve iptal edilse de yeniden başlar. vacuum_failsafe_age (1,6 milyar) geçilirse VACUUM fren ve index temizliği gibi işleri atlayıp yalnızca freeze'e odaklanır. Wraparound'a 40 milyon kala log'a uyarılar düşer, 3 milyon kala PostgreSQL yeni transaction ID vermeyi reddeder: yazma yapan her komut database is not accepting commands that assign new transaction IDs hatası alır. Çıkış yolu açık transaction'ları, eski prepared transaction'ları ve kullanılmayan replication slot'ları temizleyip database'te VACUUM çalıştırmak.

18'de normal vacuum'lar, all-visible ama henüz dondurulmamış page'lerin bir kısmını önden dondurmaya başladı (eager freezing; oranı vacuum_max_eager_freeze_failure_rate, varsayılan 0,03). Böylece 200 milyon eşiğindeki büyük tarama daha az iş bulur. Yukarıdaki VACUUM VERBOSE çıktısındaki eagerly scanned alanı bu mekanizmayı gösteriyor.

Üretimde Dikkat

İzlenecek dört şey: tablo bazında n_dead_tup ve last_autovacuum (pg_stat_user_tables; 18'de eklenen total_autovacuum_time ile autovacuum'un o tabloda ne kadar zaman harcadığı), database yaşı (age(datfrozenxid), 200 milyonun yarısını geçerse alarm), en eski açık transaction (pg_stat_activity.xact_start) ve replication slot'ların xmin'i. Uzun süren vacuum'ların ilerlemesini pg_stat_progress_vacuum gösterir.

log_autovacuum_min_duration'ı 10 dakikadan aşağı çek (ör. 1 saniye); hangi tablonun ne sıklıkla ve ne kadar sürede temizlendiği log'da görünsün. Büyük ve sık güncellenen tablolar için global ayarlara dokunmadan tablo bazında scale factor ve fillfactor ver.

Toplu silme ya da toplu güncellemeden sonra ihtiyaç varsa elle VACUUM (ANALYZE) çalıştır. Bir tablonun büyük kısmı tamamen silinecekse DELETE yerine TRUNCATE ya da (Veritabanı Performansı ve Scaling serisinde anlatılan) partition'ı düşürmek dead tuple üretmez.

Sık Düşülen Tuzaklar

Autovacuum'u "performansı düşürüyor" diye kapatmak: dead tuple birikir, tablolar şişer, visibility map güncellenmez ve index-only scan'ler bozulur. Wraparound koruması yine de çalışır, ama en kötü anda ve en büyük haliyle. Autovacuum yavaş geliyorsa çözüm daha sık ve daha hızlı çalıştırmaktır.

VACUUM FULL'u rutin bakım sanmak: tabloyu kilitler, yeni kopya için ek disk ister ve sık güncellenen tablo birkaç gün içinde aynı boyuta döner. Kalıcı çözüm autovacuum ayarı ve fillfactor. Bizim lab'da VACUUM FULL'dan sonra n_dead_tup 40.000'de kaldı; istatistik sayacını sıfırlamadığı için ardından düz bir VACUUM (ANALYZE) çalıştırdık.

Transaction içinde dış servis çağırmak: yukarıdaki örnek tam olarak bu. HTTP çağrısını transaction'ın dışına al; gerekiyorsa outbox ile (Message Queue'larla Çalışmak serisinin outbox yazısı) sonra gönder.

Bloat'ı yalnızca tabloda aramak: index'ler de şişer ve VACUUM onları küçültmez. Şişmiş bir index'i kilitlemeden yeniden kurmanın yolu REINDEX INDEX CONCURRENTLY.

Küçük Bir Deneme

★☆☆ stock_items'ta tek bir satırı güncelle ve SELECT ctid, xmin, xmax FROM stock_items WHERE book_id = 1 ile güncelleme öncesi ve sonrasında satırın fiziksel adresinin değiştiğini gör. Aynı sorguyu başka bir oturumda, güncellemeyi yapan transaction commit edilmeden önce çalıştır.

★★☆ orders tablosunda Delivered durumundaki siparişlerin %10'unu sil ve pg_stat_user_tables'tan autovacuum'un çalışıp çalışmadığını izle. Neden çalışmadı? Tablo bazında eşiği düşürüp tekrar dene.

★★★ Bir oturumda BEGIN ISOLATION LEVEL REPEATABLE READ; ile transaction aç ve bir SELECT çalıştır. Başka oturumda stock_items'ı güncelleyip VACUUM VERBOSE çalıştır; pg_stat_activity'de bu kez backend_xid yerine hangi kolonun dolu olduğuna bak ve farkı açıkla.

Bir sonraki yazıda bu yazıda kenarından geçtiğimiz konuya giriyoruz: PostgreSQL'de transaction'lar, isolation level'lar, SELECT ... FOR UPDATE SKIP LOCKED ile veritabanında job queue, advisory lock ve .NET tarafında serialization hatalarında retry.