Memahami VACUUM di PostgreSQL: MVCC, Dead Tuple, dan Cara Mencegah Table Bloat

Ada gejala yang cukup sering dijumpai tim yang mengelola database PostgreSQL berumur panjang: ukuran tabel di disk terus membengkak padahal jumlah baris aktualnya relatif stabil, query yang dulunya cepat perlahan melambat tanpa perubahan skema apapun, dan suatu hari muncul peringatan aneh soal “transaction ID wraparound” yang terdengar menakutkan. Hampir semua gejala ini berujung pada satu proses maintenance yang sering diabaikan sampai jadi masalah nyata: VACUUM. Artikel ini membahas VACUUM dari akar masalahnya — bagaimana PostgreSQL menyimpan data lewat model MVCC sehingga dead tuple bisa menumpuk — sampai bagaimana autovacuum bekerja di balik layar, dampak performa kalau proses ini terlambat, dan langkah konkret yang bisa kamu ambil supaya tabel besar tetap sehat dalam jangka panjang.

Apa itu MVCC — Fondasi Kenapa Vacuum Dibutuhkan

Untuk memahami kenapa VACUUM dibutuhkan sama sekali, kamu perlu memahami dulu model penyimpanan yang dipakai PostgreSQL: MVCC (Multi-Version Concurrency Control). Prinsip dasarnya, PostgreSQL tidak pernah menimpa (overwrite) baris data secara langsung ketika terjadi UPDATE, dan tidak langsung menghapus baris secara fisik ketika terjadi DELETE.

Ketika kamu menjalankan UPDATE, PostgreSQL menandai baris versi lama sebagai “tidak berlaku lagi” (dengan menyimpan nomor transaksi yang membuatnya tidak berlaku), lalu menulis baris versi baru sebagai entri terpisah di storage. Baris lama itu tidak langsung hilang — ia tetap ada secara fisik, hanya saja transaksi baru tidak akan melihatnya sebagai versi yang valid.

flowchart TD
    A["Baris awal: id=1, saldo=1000 (versi 1)"] -->|UPDATE saldo=1500| B["Versi 1 ditandai tidak berlaku"]
    A --> C["Versi 2 dibuat: id=1, saldo=1500"]
    B --> D[Dead Tuple - masih di disk, tidak berlaku lagi]
    C --> E[Live Tuple - versi yang valid saat ini]

Mengapa PostgreSQL dirancang seperti ini, bukan langsung menimpa data di tempat? Alasannya adalah concurrency tanpa locking berat. Bayangkan transaksi A sedang membaca baris tertentu, sementara transaksi B secara bersamaan mengupdate baris yang sama. Kalau PostgreSQL menimpa data secara langsung, transaksi A berisiko membaca data yang setengah berubah, atau harus menunggu (locking) sampai transaksi B selesai. Dengan MVCC, transaksi A tetap bisa membaca versi lama sesuai snapshot waktu transaksinya dimulai, sementara transaksi B menulis versi barunya secara independen — keduanya berjalan tanpa saling memblokir.

Trade-off dari desain elegan ini adalah: versi-versi lama yang sudah tidak berlaku itu tidak menghilang begitu saja. Mereka tetap menempati ruang fisik di disk sampai ada proses yang secara eksplisit membersihkannya. Proses itulah yang disebut VACUUM.

Apa itu Dead Tuple dan Bagaimana Ia Menumpuk

Dead tuple adalah istilah untuk baris versi lama yang sudah tidak berlaku — hasil dari UPDATE atau DELETE — tapi belum dibersihkan dari storage fisik. Setiap UPDATE pada dasarnya menghasilkan satu dead tuple (versi lama) dan satu live tuple baru (versi terkini). Setiap DELETE menghasilkan satu dead tuple tanpa live tuple pengganti.

Bayangkan skenario konkret: sebuah tabel saldo_wallet dengan satu juta baris, di mana setiap baris di-UPDATE rata-rata seratus kali per hari untuk mencatat perubahan saldo. Dalam satu hari saja, itu menghasilkan seratus juta dead tuple — seratus kali lebih banyak dari jumlah baris live yang sebenarnya kamu butuhkan. Tanpa proses pembersihan, ruang fisik yang ditempati tabel ini akan jauh melampaui ukuran data aktualnya.

flowchart LR
    subgraph Page Storage Sebelum Vacuum
        A[Live Tuple] 
        B[Dead Tuple]
        C[Dead Tuple]
        D[Live Tuple]
        E[Dead Tuple]
    end

PostgreSQL menyimpan data dalam satuan page (umumnya 8KB per page). Ketika dead tuple menumpuk dalam satu page, ruang yang bisa dipakai untuk baris baru jadi berkurang, memaksa PostgreSQL mengalokasikan page baru lebih sering dari yang seharusnya — inilah akar dari fenomena yang disebut table bloat, yang akan dibahas lebih detail di bagian performa.

Penting dipahami bahwa dead tuple bukan bug atau kesalahan — ia adalah konsekuensi wajar dari desain MVCC. Masalah baru muncul ketika dead tuple ini tidak pernah dibersihkan secara teratur.

Apa yang Sebenarnya Dilakukan VACUUM

VACUUM menjalankan beberapa pekerjaan sekaligus, dan penting memahami bahwa tidak semua pekerjaan itu sama dampaknya terhadap ukuran file fisik di disk.

Menandai Ruang Dead Tuple Sebagai Reusable

Ini adalah fungsi utama VACUUM standar. Ketika VACUUM berjalan, ia memindai tabel, mengidentifikasi dead tuple yang sudah tidak mungkin lagi dibutuhkan oleh transaksi manapun (karena semua transaksi yang mungkin masih melihat versi lama itu sudah selesai), lalu menandai ruang yang ditempatinya sebagai bisa dipakai ulang untuk INSERT atau UPDATE berikutnya.

Poin krusial di sini: VACUUM standar tidak mengembalikan ruang itu ke sistem operasi. Ukuran file tabel di disk tetap sama seperti sebelum VACUUM dijalankan — yang berubah hanyalah ruang kosong bekas dead tuple itu sekarang bisa dipakai ulang secara internal oleh PostgreSQL untuk data baru, alih-alih PostgreSQL harus mengalokasikan ruang baru di ujung file.

Update Visibility Map

PostgreSQL menyimpan struktur bernama visibility map — peta yang mencatat page mana saja yang seluruh tuple-nya sudah “visible” (terlihat) oleh semua transaksi aktif, sehingga tidak perlu dicek ulang untuk transaksi baru. VACUUM memperbarui peta ini, yang juga membantu mempercepat operasi index-only scan karena PostgreSQL bisa langsung mempercayai data di index tanpa perlu mengecek tabel utama untuk page yang sudah ditandai fully visible.

Update Statistik untuk Query Planner

VACUUM (terutama varian VACUUM ANALYZE) memperbarui statistik distribusi data yang dipakai query planner untuk menyusun execution plan — seperti perkiraan jumlah baris yang cocok dengan kondisi tertentu. Statistik yang basi bisa membuat planner memilih strategi eksekusi yang buruk, misalnya memilih sequential scan padahal index scan jauh lebih efisien, karena planner mengira jumlah baris yang cocok jauh lebih banyak dari kenyataan.

Mencegah Transaction ID Wraparound

Ini adalah fungsi VACUUM yang paling kritis dan paling sering diabaikan sampai jadi masalah serius — dibahas lebih detail di section tersendiri nanti, karena dampaknya bisa melumpuhkan database sepenuhnya kalau diabaikan terlalu lama.

Varian VACUUM

PostgreSQL menyediakan beberapa varian perintah VACUUM dengan trade-off locking dan efek yang berbeda-beda.

-- VACUUM standar: menandai ruang reusable, tidak mengunci tabel untuk read/write
VACUUM saldo_wallet;

-- VACUUM ANALYZE: vacuum standar + update statistik planner sekaligus
VACUUM ANALYZE saldo_wallet;

-- VACUUM FULL: benar-benar mengembalikan ruang ke OS, tapi mengunci tabel penuh
VACUUM FULL saldo_wallet;

-- VACUUM FREEZE: vacuum standar + membekukan transaction ID lebih agresif
VACUUM FREEZE saldo_wallet;

VACUUM standar dijalankan sebagai operasi non-blocking — proses ini bisa berjalan bersamaan dengan SELECT, INSERT, UPDATE, dan DELETE normal tanpa mengunci tabel sepenuhnya, meski ia tetap butuh lock ringan (ACCESS SHARE) yang bisa berkonflik dengan operasi DDL seperti ALTER TABLE.

VACUUM FULL bekerja dengan cara yang sama sekali berbeda: ia menulis ulang seluruh tabel ke file baru yang lebih ringkas (tanpa dead tuple sama sekali), lalu mengganti file lama dengan yang baru. Ini benar-benar mengecilkan ukuran file di disk, tapi membutuhkan exclusive lock penuh terhadap tabel selama proses berjalan — tidak ada operasi baca maupun tulis yang bisa dilakukan sampai proses selesai. Untuk tabel besar, ini bisa memakan waktu lama dan harus dijadwalkan di luar jam sibuk.

Karena VACUUM FULL mengunci tabel secara eksklusif, menjalankannya di jam sibuk pada tabel besar bisa menyebabkan seluruh query terhadap tabel itu — termasuk SELECT sederhana — tertahan sampai proses selesai. Untuk tabel besar yang butuh dikecilkan ukurannya tanpa downtime, pertimbangkan ekstensi seperti pg_repack yang melakukan operasi serupa tanpa exclusive lock berkepanjangan.

Autovacuum — Cara Kerja dan Konfigurasinya

Menjalankan VACUUM secara manual setiap saat tidak praktis, karena itu PostgreSQL punya proses background bernama autovacuum yang berjalan otomatis berdasarkan threshold perubahan pada tabel.

Secara default, autovacuum dipicu ketika jumlah baris yang berubah (lewat UPDATE atau DELETE) pada suatu tabel melampaui ambang batas tertentu, dihitung dari kombinasi nilai tetap dan proporsi terhadap jumlah baris tabel.

Threshold trigger (default):
  autovacuum_vacuum_threshold = 50 (baris)
  autovacuum_vacuum_scale_factor = 0.2 (20% dari total baris tabel)

Rumus: threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * jumlah_baris_tabel)

Untuk tabel dengan sepuluh ribu baris, autovacuum terpicu setelah kira-kira 2.050 baris berubah (50 + 20% dari 10.000). Untuk tabel dengan seratus juta baris, threshold-nya jadi dua puluh juta baris berubah — angka yang terdengar besar, tapi untuk tabel dengan write rate sangat tinggi, dua puluh juta perubahan bisa terjadi dalam hitungan jam, sehingga autovacuum jadi jarang terpicu relatif terhadap laju penumpukan dead tuple yang sebenarnya.

flowchart TD
    A[Perubahan data terus dipantau per tabel] --> B{Jumlah baris berubah > threshold?}
    B -- Belum --> A
    B -- Ya --> C[Autovacuum worker dijadwalkan]
    C --> D[VACUUM dijalankan di background]
    D --> A

Kapan Konfigurasi Default Tidak Cukup

Untuk tabel dengan pola write sangat tinggi — seperti tabel log, sesi, atau antrian pesan yang terus-menerus di-INSERT dan DELETE — konfigurasi default sering kali membuat autovacuum berjalan terlalu jarang relatif terhadap laju penumpukan dead tuple, sehingga bloat tetap terbentuk di antara siklus autovacuum. Solusinya adalah mengatur parameter ini secara spesifik per tabel, bukan mengubah default global yang berlaku ke semua tabel di database.

-- Turunkan threshold khusus untuk tabel dengan write rate sangat tinggi
ALTER TABLE antrian_pesan SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000
);

Dengan scale factor diturunkan ke 1%, autovacuum akan jauh lebih sering terpicu pada tabel spesifik ini, menjaga dead tuple tidak sempat menumpuk besar-besaran sebelum dibersihkan.

Performa: Dampak Vacuum yang Terlambat atau Diabaikan

Table Bloat

Ketika dead tuple menumpuk lebih cepat dari kecepatan vacuum membersihkannya, tabel mengalami bloat — ukuran fisiknya di disk jauh melebihi ukuran data aktual yang seharusnya dibutuhkan. Table bloat berdampak langsung ke performa karena sequential scan harus memindai lebih banyak page yang sebagian besar isinya dead tuple yang tidak relevan, dan bahkan index scan pun jadi kurang efisien karena data yang secara logis “berdekatan” kini tersebar di lebih banyak page fisik akibat bloat.

Kamu bisa mendeteksi indikasi bloat lewat statistik bawaan PostgreSQL:

-- Melihat rasio dead tuple terhadap live tuple per tabel
SELECT
    relname AS nama_tabel,
    n_live_tup AS baris_live,
    n_dead_tup AS baris_dead,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS persentase_dead
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Untuk analisis lebih presisi soal ukuran bloat aktual (bukan sekadar rasio dead tuple), ekstensi seperti pgstattuple memberi angka konkret berapa persen ruang fisik tabel yang sebenarnya “sia-sia” karena bloat.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('saldo_wallet');
-- Output mencakup: table_len, dead_tuple_percent, free_percent, dsb

Index Bloat sebagai Efek Samping

Bloat tidak hanya terjadi di tabel utama, tapi juga di index-nya. Setiap kali baris di-UPDATE, entri index untuk baris tersebut juga perlu diperbarui, dan entri index lama yang menunjuk ke dead tuple akan menumpuk dengan cara yang serupa. Index yang bloat membuat index scan jadi kurang efisien — planner harus melewati lebih banyak entri yang sudah tidak relevan sebelum menemukan data yang dicari, meski secara teori index seharusnya jauh lebih cepat dibanding sequential scan.

Query Melambat Tanpa Perubahan Skema

Gejala paling sering dikeluhkan tim adalah: query yang sama, skema yang sama, tapi performanya menurun drastis seiring waktu tanpa perubahan kode apapun. Dalam banyak kasus, akar masalahnya adalah bloat yang terakumulasi karena autovacuum tidak mengimbangi laju perubahan data — bukan karena volume data aktual yang bertambah signifikan.

Transaction ID Wraparound — Risiko Paling Serius

Di antara semua fungsi VACUUM, mencegah transaction ID wraparound adalah yang paling kritis, karena dampaknya bukan sekadar performa lambat, tapi database yang berhenti menerima transaksi baru sepenuhnya.

PostgreSQL memberi setiap transaksi sebuah transaction ID (XID) — angka yang terus bertambah setiap kali transaksi baru dimulai, dipakai sebagai bagian dari mekanisme MVCC untuk menentukan versi baris mana yang terlihat oleh transaksi mana. Masalahnya, XID di PostgreSQL disimpan sebagai angka 32-bit, yang berarti nilainya bisa “habis” dan berputar kembali ke awal (wraparound) setelah sekitar dua miliar transaksi.

flowchart LR
    A[XID terus bertambah setiap transaksi] --> B{Mendekati batas 2 miliar?}
    B -- Belum, VACUUM rutin berjalan --> C[XID lama di-freeze, aman]
    B -- Ya, VACUUM diabaikan lama --> D[Risiko wraparound]
    D --> E[Database masuk mode proteksi: menolak transaksi baru]

Kalau wraparound benar-benar terjadi tanpa penanganan, baris-baris lama bisa tiba-tiba “terlihat dari masa depan” oleh mekanisme MVCC — sebuah masalah integritas data yang serius. Untuk mencegah ini, PostgreSQL punya mekanisme proteksi: ketika sistem mendeteksi database mendekati batas wraparound tanpa vacuum yang memadai, ia akan menolak transaksi baru sepenuhnya sampai VACUUM darurat dijalankan — situasi yang secara efektif membuat database read-only atau bahkan sama sekali tidak bisa menerima koneksi baru untuk keperluan menulis.

VACUUM mencegah ini dengan proses yang disebut freezing — menandai baris-baris lama yang sudah pasti visible oleh semua transaksi sebagai “frozen”, sehingga mereka tidak lagi dihitung dalam perhitungan XID yang berjalan, secara efektif “menghentikan jam” untuk baris tersebut. VACUUM FREEZE melakukan proses ini secara lebih agresif dibanding VACUUM biasa.

-- Mengecek seberapa dekat database dengan batas wraparound
SELECT
    datname,
    age(datfrozenxid) AS umur_transaksi_tertua
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Kalau nilai age(datfrozenxid) mendekati parameter autovacuum_freeze_max_age (default sekitar 200 juta), itu tanda peringatan bahwa freeze belum berjalan cukup agresif dan butuh perhatian sebelum mendekati batas kritis 2 miliar.

Wraparound protection bukan sekadar peringatan performa — begitu database menyentuh batas kritisnya, PostgreSQL akan menolak menjalankan transaksi baru sama sekali untuk mencegah kerusakan data, memaksa administrator menjalankan VACUUM darurat dalam kondisi sistem yang sudah tidak bisa melayani traffic normal. Mencegah situasi ini lewat monitoring rutin jauh lebih murah dibanding menanganinya setelah terjadi.

Autovacuum vs Manual VACUUM — Kapan Perlu Turun Tangan

CUKUP ANDALKAN autovacuum jika:
  ✓ pola write tabel relatif stabil dan tidak ekstrem
  ✓ monitoring rutin menunjukkan rasio dead tuple tetap terkendali
  ✓ konfigurasi default atau yang sudah di-tuning per tabel sudah sesuai laju perubahan data

PERLU INTERVENSI MANUAL jika:
  ✗ baru saja melakukan bulk delete/update besar-besaran (jutaan baris sekaligus)
  ✗ statistik planner terasa basi setelah perubahan data signifikan (jalankan VACUUM ANALYZE)
  ✗ bloat sudah terlanjur parah dan butuh benar-benar mengecilkan ukuran file (VACUUM FULL/pg_repack)
  ✗ age(datfrozenxid) mendekati ambang batas peringatan wraparound

Anti-Pattern Umum

-- ✗ Mematikan autovacuum tanpa strategi pengganti
ALTER TABLE tabel_besar SET (autovacuum_enabled = false);
-- Dead tuple menumpuk tanpa batas, risiko bloat parah dan wraparound

-- ✓ Kalau butuh kontrol lebih ketat, tuning parameter alih-alih mematikan sepenuhnya
ALTER TABLE tabel_besar SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_cost_delay = 2
);


-- ✗ Menjalankan VACUUM FULL di jam sibuk pada tabel besar
VACUUM FULL orders;  -- mengunci tabel orders sepenuhnya saat traffic tinggi

-- ✓ Jadwalkan VACUUM FULL di maintenance window, atau gunakan pg_repack
-- yang tidak butuh exclusive lock berkepanjangan
-- (dijalankan lewat CLI: pg_repack --table=orders di luar jam sibuk)


-- ✗ Mengabaikan monitoring bloat sampai performa sudah terasa parah
-- (tidak ada query/dashboard yang memantau n_dead_tup secara berkala)

-- ✓ Monitoring rutin dead tuple ratio sebagai bagian dari observability standar
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Best Practice VACUUM

1. Monitoring Rutin, Bukan Reaktif

Jadikan pengecekan pg_stat_user_tables dan age(datfrozenxid) bagian dari dashboard observability standar, bukan sesuatu yang baru dicek setelah ada keluhan performa. Deteksi dini jauh lebih murah ditangani dibanding bloat yang sudah parah.

2. Tuning Autovacuum Per Tabel, Bukan Global

Tabel dengan karakteristik write yang sangat berbeda-beda (tabel log write-heavy vs tabel referensi yang jarang berubah) sebaiknya punya parameter autovacuum yang disesuaikan masing-masing, bukan mengandalkan satu setting global yang dipaksakan ke semua tabel.

3. Jadwalkan VACUUM FULL atau pg_repack di Maintenance Window

Untuk kasus bloat yang sudah parah dan butuh benar-benar mengecilkan ukuran file, rencanakan eksekusinya di jam traffic rendah, dan pertimbangkan pg_repack sebagai alternatif yang tidak memerlukan downtime penuh untuk tabel besar di production.

4. Jalankan VACUUM ANALYZE Setelah Operasi Data Besar

Setelah migrasi data, bulk import, atau bulk delete besar-besaran, jalankan VACUUM ANALYZE secara eksplisit alih-alih menunggu siklus autovacuum berikutnya, supaya statistik planner segera ter-update dan query berikutnya mendapat execution plan yang akurat.

5. Jangan Matikan Autovacuum Tanpa Pengganti yang Setara

Kalau kamu punya alasan kuat untuk menonaktifkan autovacuum pada tabel tertentu (misalnya untuk kontrol timing yang sangat presisi), pastikan ada mekanisme pengganti yang menjalankan VACUUM secara terjadwal dengan disiplin yang setara — jangan biarkan tabel itu tanpa vacuum sama sekali.

6. Pantau Freeze Age Secara Khusus untuk Database Berumur Panjang

Untuk database yang sudah berjalan bertahun-tahun dengan volume transaksi tinggi, freeze age mendekati batas kritis adalah risiko nyata, bukan sekadar teori. Tetapkan alert eksplisit ketika age(datfrozenxid) melewati persentase tertentu dari autovacuum_freeze_max_age.

Checklist singkat sebagai referensi cepat:

MONITORING RUTIN:
  □ Dashboard memantau n_dead_tup / n_live_tup per tabel
  □ Alert aktif untuk age(datfrozenxid) mendekati ambang kritis
  □ Review berkala terhadap tabel dengan bloat tertinggi

KONFIGURASI:
  □ Parameter autovacuum di-tuning per tabel untuk tabel write-heavy
  □ Tidak ada tabel dengan autovacuum_enabled = false tanpa pengganti setara
  □ autovacuum_freeze_max_age dipahami dan tidak dibiarkan default tanpa evaluasi

PENANGANAN BLOAT PARAH:
  □ VACUUM FULL/pg_repack dijadwalkan di maintenance window, bukan jam sibuk
  □ VACUUM ANALYZE dijalankan eksplisit setelah operasi data besar-besaran
  □ Rencana eskalasi jelas kalau freeze age mendekati batas kritis

Ringkasan

• PostgreSQL memakai MVCC — UPDATE/DELETE tidak menimpa data langsung, tapi menandai versi lama tidak berlaku dan menulis versi baru terpisah, menghasilkan dead tuple yang menumpuk di storage.
• VACUUM menandai ruang dead tuple sebagai reusable secara internal — bukan mengembalikan ruang ke OS — sekaligus memperbarui visibility map, statistik planner, dan mencegah transaction ID wraparound.
• VACUUM FULL benar-benar mengecilkan ukuran file fisik tapi butuh exclusive lock penuh; VACUUM standar non-blocking tapi tidak mengecilkan file.
• Autovacuum berjalan otomatis berdasarkan threshold perubahan data, tapi konfigurasi default sering tidak cukup agresif untuk tabel dengan write rate sangat tinggi — butuh tuning per tabel.
• Table bloat dan index bloat adalah akibat langsung dead tuple yang tidak dibersihkan tepat waktu, menyebabkan query melambat tanpa perubahan skema atau volume data aktual.
• Transaction ID wraparound adalah risiko paling serius — kalau dibiarkan, PostgreSQL akan menolak transaksi baru sepenuhnya untuk mencegah kerusakan data.
• Jangan matikan autovacuum tanpa pengganti setara, dan jadwalkan VACUUM FULL/pg_repack di maintenance window untuk kasus bloat parah, bukan di jam traffic tinggi.
• Monitoring rutin lewat pg_stat_user_tables dan age(datfrozenxid) jauh lebih murah dibanding menangani bloat parah atau wraparound darurat setelah terjadi.