VACUUM dan Autovacuum

Database yang sibuk menulis terus-menerus menimbun sampah yang tidak terlihat dari hasil query. Setiap UPDATE dan DELETE di PostgreSQL — karena arsitektur MVCC yang menjaga isolation antar transaksi — meninggalkan versi baris lama di dalam file tabel. Versi lama itu disebut dead tuple: tidak pernah lagi dibaca oleh query mana pun, tetapi tetap memakan ruang, tetap dipindai oleh sequential scan, dan tetap menahan index dari bentuk paling cepatnya. Chapter ini membedah siklus hidup dead tuple pada tabel stok Toko Maju Jaya, lalu membersihkannya dengan VACUUM, membandingkannya dengan VACUUM FULL, dan mengatur autovacuum agar pembersihan terjadi tanpa campur tangan operator.

Dead Tuple: Sampah yang Ditimbun MVCC #

MVCC bekerja dengan menyalin, bukan menimpa. Ketika sebuah baris di-UPDATE, PostgreSQL menulis versi baru di akhir tabel dan menandai versi lama sebagai tidak lagi hidup begitu semua transaksi pembaca lama selesai. File berikut membuat tabel stok berisi 50.000 baris, mengukur ukurannya, lalu meng-UPDATE seluruh barisnya:

sql
buat-tabel-dan-update.sql
-- Tabel stok Toko Maju Jaya. Autovacuum sengaja dimatikan
-- untuk tabel ini agar latihan deterministik: tidak ada pekerja
-- latar yang menyapu bersih dead tuple sebelum kita mengamatinya.
CREATE TABLE stok_barang (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL,
    harga integer NOT NULL
) WITH (autovacuum_enabled = false);

INSERT INTO stok_barang (nama, harga)
SELECT 'Barang-' || g, 1000 + g
FROM generate_series(1, 50000) AS g;

ANALYZE stok_barang;

SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_sebelum_update;

-- UPDATE di bawah MVCC tidak menimpa baris lama: versi baru ditulis,
-- versi lama dibiarkan di tempat sebagai dead tuple.
UPDATE stok_barang SET harga = harga + 1;

SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_sesudah_update;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 50000
ANALYZE
 ukuran_sebelum_update 
-----------------------
 2552 kB
(1 row)

UPDATE 50000
 ukuran_sesudah_update 
-----------------------
 5096 kB
(1 row)

Ukuran tabel naik dari 2552 kB menjadi 5096 kB — hampir tepat dua kali lipat — padahal jumlah baris hidup tetap 50.000. Perbedaan itu adalah ruang yang ditempati 50.000 versi lama. Perhatikan juga klausa WITH (autovacuum_enabled = false) saat pembuatan tabel: untuk latihan ini pekerja latar sengaja dibekukan supaya dead tuple bertahan sampai kita mengamatinya, bukan dibersihkan diam-diam di tengah demo.

Jumlah dead tuple dibaca dari view statistik pg_stat_user_tables, kolom n_dead_tup. Statistik baru terlihat setelah sesi penulis berakhir, sehingga pemeriksaan ini dijalankan dari file terpisah:

sql
dead-tuple.sql
-- Statistik tabel baru terlihat setelah sesi penulis berakhir,
-- jadi jumlah dead tuple dibaca dari sesi terpisah ini.
-- n_dead_tup = 50000: satu versi lama per baris yang di-UPDATE.
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'stok_barang';

Output yang Dihasilkan:

text
 n_live_tup | n_dead_tup 
------------+------------
     100000 |      50000
(1 row)

Dua angka itu layak dibedah. n_dead_tup = 50000 tepat satu versi lama per baris yang di-UPDATE. n_live_tup = 100000 terlihat salah, padahal tidak: kolom ini adalah estimasi yang dihitung dari delta penulisan (50.000 hasil INSERT ditambah 50.000 versi baru hasil UPDATE) dan belum dikoreksi dengan penghitungan fisik. Setelah VACUUM berjalan, estimasi ini akan dikoreksi menjadi angka yang tepat.

VACUUM: Menyapu dan Menandai #

Sebelum dibersihkan, dead tuple merusak performa lewat jalur yang jarang disadari: visibility map. Peta per halaman ini mencatat halaman mana yang seluruh barisnya pasti terlihat oleh semua transaksi. Index-only scan — trik berhenti di index tanpa menyentuh tabel, yang dibahas di chapter Covering Index — hanya bisa dipakai pada halaman yang tercatat di peta itu. Query berikut dijalankan sebelum pembersihan:

sql
heap-fetches-sebelum-vacuum.sql
-- Index-only scan untuk rentang id 1..10000 terpaksa memeriksa
-- heap 20.000 kali: visibility map masih kosong karena 50.000
-- dead tuple belum dibersihkan oleh VACUUM.
-- Nilai waktu dan buffer bervariasi antar mesin dan antar eksekusi.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(id) FROM stok_barang WHERE id <= 10000;

Output yang Dihasilkan:

text
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=763.79..763.80 rows=1 width=8) (actual time=7.427..7.427 rows=1 loops=1)
   Buffers: shared hit=20056
   ->  Index Only Scan using stok_barang_pkey on stok_barang  (cost=0.29..712.98 rows=20325 width=4) (actual time=0.059..6.888 rows=10000 loops=1)
         Index Cond: (id <= 10000)
         Heap Fetches: 20000
         Buffers: shared hit=20056
 Planning:
   Buffers: shared hit=66
 Planning Time: 0.203 ms
 Execution Time: 7.483 ms
(10 rows)

Rencana bernama Index Only Scan itu ternyata menyentuh heap 20.000 kali — terlihat di baris Heap Fetches: 20000. Index primary key memuat dua entri per id (versi lama dan versi baru), dan karena peta visibilitas kosong, setiap entri harus diverifikasi ke tabel. Nilai waktu, buffer, dan estimasi baris pada output di atas bervariasi antar mesin dan antar eksekusi karena sampling ANALYZE dan kondisi cache; angka aktual seperti rows=10000 dan Heap Fetches stabil untuk data yang sama.

Sekarang pembersihannya:

sql
vacuum.sql
-- VACUUM ANALYZE membersihkan dead tuple sekaligus memperbarui
-- statistik planner dalam satu perintah.
VACUUM ANALYZE stok_barang;

-- Setelah VACUUM: tidak ada dead tuple, jumlah baris hidup tepat,
-- dan vacuum_count mencatat satu vacuum manual yang baru terjadi.
SELECT n_live_tup, n_dead_tup, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'stok_barang';

-- Ruang yang dipulihkan hanya boleh dipakai ulang DI DALAM tabel:
-- ukuran file heap tidak mengecil, hanya berhenti membesar.
SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_tetap;

Output yang Dihasilkan:

text
VACUUM
 n_live_tup | n_dead_tup | vacuum_count | autovacuum_count 
------------+------------+--------------+------------------
      50000 |          0 |            1 |                0
(1 row)

 ukuran_tetap 
--------------
 5096 kB
(1 row)

Tiga fakta penting terbaca dari output tersebut. Pertama, n_dead_tup turun menjadi 0 dan n_live_tup dikoreksi menjadi tepat 50.000. Kedua, autovacuum_count tetap 0 — pembersihan ini murni hasil perintah manual, bukan pekerja latar. Ketiga, dan paling sering disalahpahami: ukuran file tetap 5096 kB. VACUUM menandai ruang bekas dead tuple sebagai kosong untuk dipakai ulang oleh INSERT dan UPDATE berikutnya di tabel yang sama; ruang itu tidak dikembalikan ke sistem operasi.

Query yang sama dijalankan ulang setelah pembersihan:

sql
heap-fetches-setelah-vacuum.sql
-- Setelah VACUUM, visibility map terisi: seluruh halaman ditandai
-- all-visible sehingga index-only scan berhenti menyentuh heap.
-- Heap Fetches turun dari 20.000 menjadi 0.
-- Nilai waktu dan buffer bervariasi antar mesin dan antar eksekusi.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(id) FROM stok_barang WHERE id <= 10000;

Output yang Dihasilkan:

text
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=426.25..426.26 rows=1 width=8) (actual time=1.445..1.445 rows=1 loops=1)
   Buffers: shared hit=57
   ->  Index Only Scan using stok_barang_pkey on stok_barang  (cost=0.29..401.01 rows=10098 width=4) (actual time=0.025..0.915 rows=10000 loops=1)
         Index Cond: (id <= 10000)
         Heap Fetches: 0
         Buffers: shared hit=57
 Planning:
   Buffers: shared hit=66
 Planning Time: 0.242 ms
 Execution Time: 1.485 ms
(10 rows)

Perbandingan kedua rencana itu adalah inti chapter ini. Heap Fetches jatuh dari 20.000 menjadi 0 dan buffer yang disentuh dari 20.056 menjadi 57, karena VACUUM mengisi visibility map dengan tanda all-visible per halaman. Query yang sama, index yang sama, data yang sama — lima kali lebih cepat hanya karena sampahnya disapu. Sekali lagi: angka waktu dan buffer bervariasi antar mesin, tetapi Heap Fetches: 0 adalah hasil struktural dari visibility map yang terisi.

VACUUM FULL: Mengecilkan File dengan Harga Kunci #

Ketika tabel sudah bloat — ukuran filenya jauh lebih besar daripada data hidupnya, misalnya setelah operasi massal yang tidak pernah divacuum — satu-satunya cara bawaan untuk mengecilkan file adalah VACUUM FULL:

sql
vacuum-full.sql
-- VACUUM FULL menulis ulang tabel secara fisik menjadi file padat,
-- tetapi membutuhkan kunci ACCESS EXCLUSIVE: semua pembaca dan
-- penulis tabel ini terblokir sampai selesai.
VACUUM FULL stok_barang;

SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_setelah_vacuum_full;

SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'stok_barang';

Output yang Dihasilkan:

text
VACUUM
 ukuran_setelah_vacuum_full 
----------------------------
 2552 kB
(1 row)

 n_live_tup | n_dead_tup 
------------+------------
      50000 |          0
(1 row)

File mengecil kembali ke 2552 kB — persis ukuran sebelum UPDATE massal — karena VACUUM FULL menulis ulang seluruh tabel ke file baru yang padat, lalu menggantikan file lama. Harganya sebanding mahalnya:

Autovacuum: Penjaga yang Berjalan Sendiri #

Menjalankan VACUUM manual setiap kali tabel kotor bukan pekerjaan manusia. Autovacuum — sekumpulan proses latar yang aktif secara default — memantau statistik tiap tabel dan memicu pembersihan ketika dead tuple melampaui ambang. Ambang itu dihitung dari dua parameter:

sql
autovacuum.sql
-- Empat parameter ambang autovacuum default, dibaca dari pg_settings.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('autovacuum_vacuum_threshold',
               'autovacuum_vacuum_scale_factor',
               'autovacuum_analyze_threshold',
               'autovacuum_analyze_scale_factor')
ORDER BY name;

-- Ambang pemicu vacuum untuk tabel dengan 50.000 baris hidup:
-- threshold + scale_factor * n_live_tup.
SELECT round(50 + 0.2 * 50000) AS ambang_dead_tuple;

-- Audit pengaturan per tabel: reloptions di pg_class
-- memperlihatkan autovacuum yang kita matikan saat membuat tabel.
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'stok_barang';

Output yang Dihasilkan:

text
              name               | setting | unit 
---------------------------------+---------+------
 autovacuum_analyze_scale_factor | 0.1     | 
 autovacuum_analyze_threshold    | 50      | 
 autovacuum_vacuum_scale_factor  | 0.2     | 
 autovacuum_vacuum_threshold     | 50      | 
(4 rows)

 ambang_dead_tuple 
-------------------
             10050
(1 row)

   relname   |         reloptions         
-------------+----------------------------
 stok_barang | {autovacuum_enabled=false}
(1 row)

Rumus pemicunya: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup. Dengan nilai bawaan 50 dan 0.2, tabel 50.000 baris baru divacuum setelah menimbun 10.050 dead tuple — itulah angka ambang_dead_tuple pada output. Tabel kecil menyala cepat lewat komponen tetap threshold; tabel besar butuh 20% tambahan baris mati lewat komponen proporsional scale_factor, sehingga tabel raksasa kerap perlu scale_factor yang lebih kecil (misalnya 0.01–0.05) lewat ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.05) agar bloat tidak menumpuk. Query terakhir pada output menunjukkan cara mengaudit penyetelan per tabel: kolom reloptions di pg_class memperlihatkan autovacuum_enabled=false yang kita pasang di awal chapter — pola yang sama dipakai untuk menyetel ambang berbeda per tabel tanpa menyentuh konfigurasi server.

Ritme harian yang sehat menjadi sederhana setelah memahami chapter ini: biarkan autovacuum bekerja dengan ambang yang wajar, pantau n_dead_tup di pg_stat_user_tables untuk tabel-tabel panas, dan gunakan VACUUM ANALYZE manual setelah operasi tulis massal seperti migration atau backfill. Dengan heap yang bersih dan visibility map yang terisi, fondasi fisik query sudah sehat — dan kita siap membenahi sisi logikanya: pola join yang membuat query berat di chapter berikutnya.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Empat parameter ambang autovacuum default, dibaca dari pg_settings.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('autovacuum_vacuum_threshold',
               'autovacuum_vacuum_scale_factor',
               'autovacuum_analyze_threshold',
               'autovacuum_analyze_scale_factor')
ORDER BY name;

-- Ambang pemicu vacuum untuk tabel dengan 50.000 baris hidup:
-- threshold + scale_factor * n_live_tup.
SELECT round(50 + 0.2 * 50000) AS ambang_dead_tuple;

-- Audit pengaturan per tabel: reloptions di pg_class
-- memperlihatkan autovacuum yang kita matikan saat membuat tabel.
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'stok_barang';

sql
-- Tabel stok Toko Maju Jaya. Autovacuum sengaja dimatikan
-- untuk tabel ini agar latihan deterministik: tidak ada pekerja
-- latar yang menyapu bersih dead tuple sebelum kita mengamatinya.
CREATE TABLE stok_barang (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL,
    harga integer NOT NULL
) WITH (autovacuum_enabled = false);

INSERT INTO stok_barang (nama, harga)
SELECT 'Barang-' || g, 1000 + g
FROM generate_series(1, 50000) AS g;

ANALYZE stok_barang;

SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_sebelum_update;

-- UPDATE di bawah MVCC tidak menimpa baris lama: versi baru ditulis,
-- versi lama dibiarkan di tempat sebagai dead tuple.
UPDATE stok_barang SET harga = harga + 1;

SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_sesudah_update;

sql
-- Statistik tabel baru terlihat setelah sesi penulis berakhir,
-- jadi jumlah dead tuple dibaca dari sesi terpisah ini.
-- n_dead_tup = 50000: satu versi lama per baris yang di-UPDATE.
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'stok_barang';

sql
-- Index-only scan untuk rentang id 1..10000 terpaksa memeriksa
-- heap 20.000 kali: visibility map masih kosong karena 50.000
-- dead tuple belum dibersihkan oleh VACUUM.
-- Nilai waktu dan buffer bervariasi antar mesin dan antar eksekusi.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(id) FROM stok_barang WHERE id <= 10000;

sql
-- Setelah VACUUM, visibility map terisi: seluruh halaman ditandai
-- all-visible sehingga index-only scan berhenti menyentuh heap.
-- Heap Fetches turun dari 20.000 menjadi 0.
-- Nilai waktu dan buffer bervariasi antar mesin dan antar eksekusi.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(id) FROM stok_barang WHERE id <= 10000;

sql
-- VACUUM FULL menulis ulang tabel secara fisik menjadi file padat,
-- tetapi membutuhkan kunci ACCESS EXCLUSIVE: semua pembaca dan
-- penulis tabel ini terblokir sampai selesai.
VACUUM FULL stok_barang;

SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_setelah_vacuum_full;

SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'stok_barang';

sql
-- VACUUM ANALYZE membersihkan dead tuple sekaligus memperbarui
-- statistik planner dalam satu perintah.
VACUUM ANALYZE stok_barang;

-- Setelah VACUUM: tidak ada dead tuple, jumlah baris hidup tepat,
-- dan vacuum_count mencatat satu vacuum manual yang baru terjadi.
SELECT n_live_tup, n_dead_tup, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'stok_barang';

-- Ruang yang dipulihkan hanya boleh dipakai ulang DI DALAM tabel:
-- ukuran file heap tidak mengecil, hanya berhenti membesar.
SELECT pg_size_pretty(pg_relation_size('stok_barang')) AS ukuran_tetap;