VACUUM dan ANALYZE

Tabel yang sibuk ditulis meninggalkan dua utang senyap. Utang pertama adalah baris mati: karena MVCC tidak menimpa data lama saat UPDATE atau DELETE berjalan — mekanisme yang sudah kita bedah di chapter MVCC — setiap perubahan meninggalkan mayat baris yang menempati disk sampai seseorang memungutnya. Utang kedua adalah statistik usang: perencana query menyusun rencana eksekusi berdasarkan perkiraan jumlah baris dan sebaran nilainya, dan perkiraan itu tidak membarui dirinya sendiri. Dua pekerjaan rutin administrator melunasi utang tersebut: VACUUM memungut baris mati, ANALYZE menyegarkan statistik. Chapter ini menjalankan keduanya secara sadar pada satu tabel uji, membaca laporan VERBOSE-nya, membedakan VACUUM biasa dari VACUUM FULL, lalu menyetel autovacuum yang mengerjakan semuanya secara otomatis.

Baris Mati: Utang MVCC yang Mengembang #

File berikut menyiapkan tabel uji barang_keluar berisi 3000 baris, lalu menghapus setengahnya. Satu detail penting sejak awal: opsi WITH (autovacuum_enabled = false) sengaja mematikan pembersih otomatis agar angka-angka contoh stabil sepanjang chapter — di produksi, opsi itu justru tidak boleh dipakai tanpa alasan kuat:

-- Tabel latihan: catatan barang keluar gudang Toko Maju Jaya.
-- autovacuum sengaja dimatikan agar angka contoh stabil;
-- di server produksi biarkan autovacuum menyala.
CREATE TABLE barang_keluar (
  id integer PRIMARY KEY,
  kode varchar(16),
  jumlah integer,
  harga integer
) WITH (autovacuum_enabled = false);

INSERT INTO barang_keluar (id, kode, jumlah, harga)
SELECT g,
       'BRG-' || lpad(g::text, 4, '0'),
       (g % 5) + 1,
       15000
FROM generate_series(1, 3000) AS g;

-- Semua baris masih hidup saat ini.
SELECT count(*) AS total_sebelum_hapus FROM barang_keluar;

-- Setengah baris dihapus: sumber baris mati untuk seluruh chapter.
DELETE FROM barang_keluar WHERE id <= 1500;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 3000
 total_sebelum_hapus 
---------------------
                3000
(1 row)

DELETE 1500

Setelah DELETE 1500, statistik server dan ukuran fisik tabel menceritakan dua hal yang berbeda dari apa yang dilihat query biasa:

-- Baris mati terlihat di statistik server, bukan di hasil query.
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'barang_keluar';

-- Ukuran fisik tabel di disk masih menghitung 3000 baris masa lalu.
SELECT pg_relation_size('barang_keluar') AS ukuran_byte,
       pg_size_pretty(pg_relation_size('barang_keluar')) AS ukuran;

Output yang Dihasilkan:

 n_live_tup | n_dead_tup 
------------+------------
       1500 |       1500
(1 row)

 ukuran_byte | ukuran 
-------------+--------
      163840 | 160 kB
(1 row)

Tiga angka itu adalah definisi bloat dalam bentuk nyata: statistik melaporkan 1500 baris hidup dan 1500 baris mati, file tabel di disk masih setebal 163840 byte (160 kB) untuk data yang kini cuma setengahnya, dan hanya query yang teliti yang melihat 1500 baris. Semakin lama pola tulis-hapus ini berulang tanpa dibersihkan, semakin tebal selisihnya — table scan membaca mayat ikut sertanya, cache menampung halaman yang isinya sudah tidak relevan, dan itulah mengapa pembersihan adalah rutinitas, bukan tindakan darurat.

VACUUM: Memungut Baris Mati #

VACUUM membaca seluruh tabel, menandai ruang bekas baris mati sebagai siap pakai untuk baris baru, membersihkan penunjuk baris mati dari index yang mengacunya, lalu memperbarui statistik penghitungnya. Perintah ini berjalan tanpa mengunci pembaca dan penulis lain — tabel tetap melayani query selama pembersihan berlangsung. Opsi VERBOSE menambahkan laporan rinci yang sangat berguna untuk belajar apa yang sebenarnya dikerjakan:

-- VACUUM memungut baris mati agar ruangnya bisa dipakai ulang.
-- VERBOSE menambahkan laporan rinci (dikirim sebagai pesan INFO).
VACUUM (VERBOSE) barang_keluar;

Output yang Dihasilkan:

VACUUM

Di layar psql hanya terlihat satu tag VACUUM. Seluruh laporan verbose dikirim sebagai pesan INFO ke standard error klien, bukan ke hasil query — inilah bentuk lengkapnya dari eksekusi nyata file yang sama:

-- VACUUM memungut baris mati agar ruangnya bisa dipakai ulang.
-- VERBOSE menambahkan laporan rinci (dikirim sebagai pesan INFO).
VACUUM (VERBOSE) barang_keluar;

Output yang Dihasilkan:

INFO:  vacuuming "pgtutor_be8a94cf3c.public.barang_keluar"
INFO:  finished vacuuming "pgtutor_be8a94cf3c.public.barang_keluar": index scans: 1
pages: 0 removed, 20 remain, 20 scanned (100.00% of total)
tuples: 1500 removed, 1500 remain, 0 are dead but not yet removable
removable cutoff: 16825, which was 0 XIDs old when operation ended
new relfrozenxid: 16823, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 10 pages from table (50.00% of total) had 1500 dead item identifiers removed
index "barang_keluar_pkey": pages: 11 in total, 4 newly deleted, 4 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 60.328 MB/s
buffer usage: 136 hits, 0 misses, 4 dirtied
WAL usage: 56 records, 1 full page images, 20820 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Baris-baris terpenting dari laporan itu:

1. tuples: 1500 removed, 1500 remain — 1500 baris mati berhasil dipungut; yang tersisa memang 1500 baris hidup. Inilah angka yang menjawab “apa kerja VACUUM hari ini”.
2. index scan needed: 10 pages ... had 1500 dead item identifiers removed — VACUUM juga menyapu 1500 penunjuk baris mati di index barang_keluar_pkey yang mengarah ke mayat-mayat tadi; tanpa sapuan ini index ikut membengkak.
3. pages: 0 removed, 20 remain — tidak ada halaman yang dikembalikan ke sistem operasi; dua puluh halaman tetap dimiliki tabel dan akan dipakai ulang oleh baris baru. Poin ini menjadi penting di bagian VACUUM FULL berikutnya.
4. removable cutoff dan new relfrozenxid — catatan pembukuan transaction ID: baris yang lebih tua dari batas potong boleh dibekukan (frozen) agar aman dari pembungkusan XID — dibahas di bagian akhir chapter.

VACUUM dan ANALYZE juga bisa digabung dalam satu perintah: VACUUM ANALYZE barang_keluar; membersihkan baris mati sekaligus menyegarkan statistik dalam satu kali jalan — bentuk yang paling lazim untuk rutinitas manual.

VACUUM FULL: Menulis Ulang Tabel Secara Padat #

VACUUM biasa tidak mengecilkan file tabel — ruang kosong ditandai siap pakai di dalam tabel itu sendiri. File berikut membuktikannya, lalu menjalankan VACUUM FULL yang menulis ulang seluruh tabel ke file fisik baru berisi baris hidup saja:

-- Setelah VACUUM: baris mati sudah dipungut dari statistik ...
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'barang_keluar';

-- ... tetapi ukuran file tidak berubah: ruang hanya ditandai siap pakai.
SELECT pg_relation_size('barang_keluar') AS sebelum_full_byte;

-- VACUUM FULL menulis ulang tabel dalam bentuk padat.
VACUUM FULL barang_keluar;

SELECT pg_relation_size('barang_keluar') AS sesudah_full_byte,
       pg_size_pretty(pg_relation_size('barang_keluar')) AS sesudah;

-- Data tidak ada yang hilang.
SELECT count(*) AS baris_hidup FROM barang_keluar;

Output yang Dihasilkan:

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

 sebelum_full_byte 
-------------------
            163840
(1 row)

VACUUM
 sesudah_full_byte | sesudah 
-------------------+---------
             81920 | 80 kB
(1 row)

 baris_hidup 
-------------
        1500
(1 row)

Urutan angka di output memperlihatkan keseluruhan cerita: statistik sudah bersih (n_dead_tup = 0) tetapi file masih 163840 byte sebelum VACUUM FULL, lalu mengecil menjadi tepat setengahnya (81920 byte) setelah penulisan ulang, dengan 1500 baris tetap utuh. Kompaksi sebesar ini hanya mungkin karena baris hidup benar-benar dipindah ke file baru — dan di situlah harga yang harus dibayar:

ANALYZE: Menyegarkan Statistik Perencana #

Pekerjaan kedua bergerak di sisi perencana query. Saat menerima SELECT ... WHERE kode = 'BRG-2999', perencana memperkirakan berapa baris yang lolos saringan itu memakai statistik per kolom: jumlah nilai berbeda, lebar rata-rata, sebaran nilai. Statistik itu diambil dari sampel acak tabel — dan sampel hanya diperbarui saat ANALYZE berjalan:

-- ANALYZE menyegarkan statistik yang dipakai perencana query.
-- VERBOSE memperlihatkan proses sampling-nya (pesan INFO).
ANALYZE (VERBOSE) barang_keluar;

Output yang Dihasilkan:

ANALYZE

Sama seperti VACUUM, tag di layar hanya satu kata ANALYZE; laporan verbose-nya tampil di standard error:

-- ANALYZE menyegarkan statistik yang dipakai perencana query.
-- VERBOSE memperlihatkan proses sampling-nya (pesan INFO).
ANALYZE (VERBOSE) barang_keluar;

Output yang Dihasilkan:

INFO:  analyzing "public.barang_keluar"
INFO:  "barang_keluar": scanned 10 of 10 pages, containing 1500 live rows and 0 dead rows; 1500 rows in sample, 1500 estimated total rows
ANALYZE

Laporan itu memperlihatkan cara kerja sampling: tabel kecil discan seluruhnya (10 dari 10 halaman), 1500 baris hidup masuk sampel, dan perkiraan total pun tepat 1500. Tabel besar tidak discan penuh — ANALYZE mengambil sampel acak sekitar 300 × default_statistics_target baris, cukup untuk perkiraan yang baik dengan biaya yang jauh lebih murah. Hasilnya tersimpan di pg_stats dan bisa diaudit per kolom:

-- pg_stats menyimpan hasil sampling ANALYZE per kolom.
SELECT attname, n_distinct, avg_width
FROM pg_stats
WHERE tablename = 'barang_keluar'
ORDER BY attname;

Output yang Dihasilkan:

 attname | n_distinct | avg_width 
---------+------------+-----------
 harga   |          1 |         4
 id      |         -1 |         4
 jumlah  |          5 |         4
 kode    |         -1 |         9
(4 rows)

Empat baris hasil itu mudah dibaca: n_distinct = -1 pada id dan kode berarti “semua nilai berbeda” (tanda kolom unik), jumlah memiliki tepat 5 nilai berbeda (sesuai rumus (g % 5) + 1 saat pengisian), dan harga hanya satu nilai sehingga perencana tahu kolom itu tidak berguna untuk penyaringan. Kapan ANALYZE perlu dijalankan manual? Setelah memuat data besar sekaligus (COPY jutaan baris), setelah perubahan pola data yang drastis, atau saat rencana eksekusi mulai anjlok tanpa sebab jelas — perintahnya cepat, tidak mengunci, dan tidak pernah merusak.

Autovacuum: Petugas yang Bekerja Sendiri #

Menjalankan VACUUM manual untuk setiap tabel setiap hari adalah pekerjaan yang segera dilupakan orang — karena itu PostgreSQL mengirim petugasnya sendiri. Autovacuum launcher bangun tiap autovacuum_naptime (60 detik), memeriksa tabel-tabel yang melewati ambang kekotoran, lalu mengutus worker untuk menjalankan VACUUM atau ANALYZE pada tabel-tabel tersebut. Ambangnya ditentukan dua pasang parameter:

-- Bingkai kerja autovacuum di level server.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('autovacuum',
               'autovacuum_naptime',
               'autovacuum_vacuum_threshold',
               'autovacuum_vacuum_scale_factor',
               'autovacuum_analyze_threshold',
               'autovacuum_analyze_scale_factor')
ORDER BY name;

-- Tabel yang ramai churn: ambang lebih agresif agar sering dibersihkan.
ALTER TABLE barang_keluar SET (autovacuum_vacuum_scale_factor = 0.05);

-- Tabel arsip yang tak pernah ditulis ulang: autovacuum dimatikan.
ALTER TABLE barang_keluar SET (autovacuum_enabled = false);

SELECT reloptions
FROM pg_class
WHERE relname = 'barang_keluar';

-- Kembalikan ke bawaan server (autovacuum kembali menyala).
ALTER TABLE barang_keluar RESET (autovacuum_enabled, autovacuum_vacuum_scale_factor);

SELECT reloptions
FROM pg_class
WHERE relname = 'barang_keluar';

Output yang Dihasilkan:

              name               | setting | unit 
---------------------------------+---------+------
 autovacuum                      | on      | 
 autovacuum_analyze_scale_factor | 0.1     | 
 autovacuum_analyze_threshold    | 50      | 
 autovacuum_naptime              | 60      | s
 autovacuum_vacuum_scale_factor  | 0.2     | 
 autovacuum_vacuum_threshold     | 50      | 
(6 rows)

ALTER TABLE
ALTER TABLE
                           reloptions                           
----------------------------------------------------------------
 {autovacuum_vacuum_scale_factor=0.05,autovacuum_enabled=false}
(1 row)

ALTER TABLE
 reloptions 
------------
 
(1 row)

Rumus pemicunya membaca parameter di output itu: vacuum dijalankan saat baris mati melewati autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × jumlah baris hidup — dengan bawaan 50 + 0.2 × n, tabel 1500 baris baru dibersihkan setelah menumpuk 350 baris mati, sedangkan tabel 10 juta baris menunggu 2 juta. Bagian kedua file mendemokan penyetelan per tabel lewat reloptions: autovacuum_vacuum_scale_factor = 0.05 untuk tabel ramai tulis-hapus agar pemicunya lima kali lebih sensitif, dan autovacuum_enabled = false untuk tabel arsip yang tidak pernah berubah. Kolom reloptions di pg_class lalu memperlihatkan kedua penyetelan itu tersimpan sebagai opsi melekat pada tabel, dan RESET mengembalikannya ke bawaan server. Penyetelan per tabel seperti ini hampir selalu lebih tepat daripada mengubah enam parameter globalnya.

Wraparound XID: Alasan VACUUM Tidak Boleh Ditinggalkan #

Satu alasan terakhir membuat VACUUM bersifat wajib, bukan sekadar manis. Transaction ID di PostgreSQL adalah bilangan 32-bit yang berputar — sekitar empat miliar nilai — dan usia baris dihitung dari jaraknya ke XID sekarang. Baris yang sangat tua harus dibekukan (frozen, ditandai bit khusus) supaya aman ketika angka XID berputar kembali; pembekuan inilah pekerjaan tambahan yang terlihat di laporan verbose sebagai frozen: N pages ... had N tuples frozen. Bila pembekuan ketinggalan jauh, server akan memaksa anti-wraparound vacuum sendirian, dan di titik paling parah menolak perintah tulis dengan error database is not accepting commands to avoid wraparound. Autovacuum menangani hampir semua kasus secara diam-diam; Anda menyadarinya hanya lewat baris log automatic vacuum of table ... — satu lagi alasan menjaga autovacuum tetap menyala dan membaca log server secara rutin.

VACUUM dan ANALYZE adalah dua rutinitas harian yang menjaga performa tetap stabil. Namun pekerjaan pemeliharaan tidak berhenti di situ: index ikut membengkak dan perlu dibangun ulang, disk harus dipantau agar tidak penuh, dan integritas data sesekali perlu diperiksa. Kalender lengkapnya — lengkap dengan ambang kapan setiap pekerjaan dijalankan — dibahas di chapter Database Maintenance .

Referensi #

• Routine Vacuuming
• VACUUM
• ANALYZE

-- ANALYZE menyegarkan statistik yang dipakai perencana query.
-- VERBOSE memperlihatkan proses sampling-nya (pesan INFO).
ANALYZE (VERBOSE) barang_keluar;

-- Bingkai kerja autovacuum di level server.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('autovacuum',
               'autovacuum_naptime',
               'autovacuum_vacuum_threshold',
               'autovacuum_vacuum_scale_factor',
               'autovacuum_analyze_threshold',
               'autovacuum_analyze_scale_factor')
ORDER BY name;

-- Tabel yang ramai churn: ambang lebih agresif agar sering dibersihkan.
ALTER TABLE barang_keluar SET (autovacuum_vacuum_scale_factor = 0.05);

-- Tabel arsip yang tak pernah ditulis ulang: autovacuum dimatikan.
ALTER TABLE barang_keluar SET (autovacuum_enabled = false);

SELECT reloptions
FROM pg_class
WHERE relname = 'barang_keluar';

-- Kembalikan ke bawaan server (autovacuum kembali menyala).
ALTER TABLE barang_keluar RESET (autovacuum_enabled, autovacuum_vacuum_scale_factor);

SELECT reloptions
FROM pg_class
WHERE relname = 'barang_keluar';

-- Tabel latihan: catatan barang keluar gudang Toko Maju Jaya.
-- autovacuum sengaja dimatikan agar angka contoh stabil;
-- di server produksi biarkan autovacuum menyala.
CREATE TABLE barang_keluar (
  id integer PRIMARY KEY,
  kode varchar(16),
  jumlah integer,
  harga integer
) WITH (autovacuum_enabled = false);

INSERT INTO barang_keluar (id, kode, jumlah, harga)
SELECT g,
       'BRG-' || lpad(g::text, 4, '0'),
       (g % 5) + 1,
       15000
FROM generate_series(1, 3000) AS g;

-- Semua baris masih hidup saat ini.
SELECT count(*) AS total_sebelum_hapus FROM barang_keluar;

-- Setengah baris dihapus: sumber baris mati untuk seluruh chapter.
DELETE FROM barang_keluar WHERE id <= 1500;

-- pg_stats menyimpan hasil sampling ANALYZE per kolom.
SELECT attname, n_distinct, avg_width
FROM pg_stats
WHERE tablename = 'barang_keluar'
ORDER BY attname;

-- Baris mati terlihat di statistik server, bukan di hasil query.
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'barang_keluar';

-- Ukuran fisik tabel di disk masih menghitung 3000 baris masa lalu.
SELECT pg_relation_size('barang_keluar') AS ukuran_byte,
       pg_size_pretty(pg_relation_size('barang_keluar')) AS ukuran;

-- Setelah VACUUM: baris mati sudah dipungut dari statistik ...
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'barang_keluar';

-- ... tetapi ukuran file tidak berubah: ruang hanya ditandai siap pakai.
SELECT pg_relation_size('barang_keluar') AS sebelum_full_byte;

-- VACUUM FULL menulis ulang tabel dalam bentuk padat.
VACUUM FULL barang_keluar;

SELECT pg_relation_size('barang_keluar') AS sesudah_full_byte,
       pg_size_pretty(pg_relation_size('barang_keluar')) AS sesudah;

-- Data tidak ada yang hilang.
SELECT count(*) AS baris_hidup FROM barang_keluar;

-- VACUUM memungut baris mati agar ruangnya bisa dipakai ulang.
-- VERBOSE menambahkan laporan rinci (dikirim sebagai pesan INFO).
VACUUM (VERBOSE) barang_keluar;