Database Maintenance

VACUUM dan ANALYZE dari chapter sebelumnya adalah dua pekerjaan harian yang dikerjakan autovacuum — tetapi memelihara database jangka panjang butuh kalender yang lebih luas. Index ikut membengkak dan perlu dibangun ulang; disk terisi pelan-pelan sampai suatu pagi penuh; kerusakan perangkat keras sesekali meninggalkan luka tersembunyi di struktur data; dan file log lama memakan ruang tanpa pernah menghapus dirinya sendiri. Semua pekerjaan ini tidak mendesak hari ini, dan justru karena itu semuanya mudah dilupakan sampai menjadi insiden. Chapter ini menyusun seluruh rutinitas menjadi daftar periksa yang bisa dijadwalkan, lengkap dengan query audit yang menjalankannya di atas satu tabel uji — sehingga setiap angka yang dibaca punya pembanding yang jelas.

Daftar Periksa Pemeliharaan Berkala #

Kerangka kalender berikut merangkum seluruh pekerjaan beserta frekuensi lazimnya. Frekuensi pastinya bergantung beban tulis server Anda — server yang menulis jutaan baris per hari membutuhkan siklus lebih pendek daripada server yang nyaris hanya dibaca:

Empat bagian berikut menjalankan baris tengah daftar itu satu per satu di atas tabel uji yang sama, lalu dua urusan terakhir ditutup sebagai prosedur. Seluruh query memakai tabel stok yang disiapkan di file pertama.

Pemeriksaan Paling Dasar: Server Masih Hidup #

Sebelum mengaudit apa pun, pastikan server menjawab koneksi. Perintah pg_isready membuka koneksi ringan dan melaporkan statusnya tanpa perlu kredensial atau database:

#!/bin/bash
# Pemeriksaan paling mendasar: apakah server masih melayani koneksi?
# Skrip ini layak dijalankan dari cron setiap menit.
pg_isready -h "$PGHOST" -p "$PGPORT"

Output yang Dihasilkan:

/home/z/my-project/pgrun:54329 - accepting connections

Baris output itu memuat alamat socket, port, dan vonis accepting connections — server latihan kita sehat. Dua vonis lain yang mungkin: rejecting connections (server hidup tapi menolak, biasanya sedang recovery atau tahap akhir pg_ctl stop) dan no response (tidak ada yang mendengarkan port itu). Skrip satu baris seperti ini adalah calon sempurna untuk cron setiap menit: exit code-nya 0 saat menerima dan bukan nol saat bermasalah, sehingga cukup ditambah satu if untuk mengirim notifikasi. Opsi yang berguna di skrip produksi: -d nama_db untuk memeriksa database tertentu, -t 5 membatasi waktu tunggu lima detik, dan -q membuatnya bisu kecuali gagal.

Mendeteksi Bloat Tabel #

File berikut menyiapkan tabel uji stok dengan pola yang meniru gudang nyata: 20.000 barang masuk, 10.000 di antaranya sudah tidak dipesan dan dihapus, lalu lima query pencarian dijalankan sebagai beban baca yang terkendali. Sama seperti chapter sebelumnya, autovacuum_enabled = false menjaga angka tetap stabil untuk pembelajaran:

-- Tabel stok gudang Toko Maju Jaya: 20.000 baris, setengahnya
-- sudah tidak dipesan lagi dan dihapus -- beban tulis-hapus yang khas.
-- autovacuum dimatikan agar angka pembanding stabil sepanjang chapter.
CREATE TABLE stok (
  id integer PRIMARY KEY,
  nama varchar(32),
  qty integer
) WITH (autovacuum_enabled = false);

INSERT INTO stok (id, nama, qty)
SELECT g,
       'Barang-' || lpad(g::text, 5, '0'),
       (g % 9) + 1
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_stok_nama ON stok (nama);

DELETE FROM stok WHERE id <= 10000;

-- Beban baca yang kita kontrol: tiga lookup lewat primary key ...
SELECT count(*) AS lookup_id FROM stok WHERE id = 15000;
SELECT count(*) AS lookup_id FROM stok WHERE id = 15000;
SELECT count(*) AS lookup_id FROM stok WHERE id = 15000;

-- ... dan dua lookup lewat index nama.
SELECT count(*) AS lookup_nama FROM stok WHERE nama = 'Barang-15000';
SELECT count(*) AS lookup_nama FROM stok WHERE nama = 'Barang-15000';

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 20000
CREATE INDEX
DELETE 10000
 lookup_id 
-----------
         1
(1 row)

 lookup_id 
-----------
         1
(1 row)

 lookup_id 
-----------
         1
(1 row)

 lookup_nama 
-------------
           1
(1 row)

 lookup_nama 
-------------
           1
(1 row)

Angka-angka pada file itu ditunjukkan apa adanya untuk pembelajaran, bukan karena kecil di dunia nyata: INSERT 0 20000, DELETE 10000, dan lima hasil count(*) yang masing-masing menemukan satu barang. Query audit bloat-nya sendiri sederhana:

-- Deteksi bloat: bandingkan baris mati dengan total baris tercatat.
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup
             / nullif(n_live_tup + n_dead_tup, 0), 1) AS rasio_bloat_pct
FROM pg_stat_user_tables
WHERE relname = 'stok';

Output yang Dihasilkan:

 relname | n_live_tup | n_dead_tup | rasio_bloat_pct 
---------+------------+------------+-----------------
 stok    |      10000 |      10000 |            50.0
(1 row)

Hasil 50.0 persen adalah bloat buatan yang disengaja: 10.000 baris mati berbanding 20.000 total tercatat. Dalam audit mingguan nyata, angka ini dibaca dengan aturan praktis: di bawah 5 persen sehat dan biarkan autovacuum bekerja, di kisaran 5–20 persen pantau trennya, dan di atas 20 persen yang bertahan beberapa minggu — padahal autovacuum aktif — saatnya VACUUM (FULL) di jendela perawatan atau penyelidikan kenapa autovacuum tak kunjung selesai (transaksi terbuntu yang lama adalah tersangka nomor satu, seperti dibahas di chapter VACUUM dan ANALYZE ).

Audit Pemakaian Index #

Index yang tidak pernah dipakai tetap dibayar setiap INSERT, UPDATE, dan DELETE — pemakaiannya tercatat di kolom idx_scan, dan menggabungkannya dengan ukuran index langsung mengurutkan kandidat penghematan:

-- Audit pemakaian index: mana yang sibuk, mana yang hanya makan tempat.
SELECT indexrelname,
       idx_scan AS pemakaian,
       pg_size_pretty(pg_relation_size(indexrelid)) AS ukuran
FROM pg_stat_user_indexes
WHERE relname = 'stok'
ORDER BY idx_scan, indexrelname;

Output yang Dihasilkan:

 indexrelname  | pemakaian | ukuran 
---------------+-----------+--------
 idx_stok_nama |         2 | 632 kB
 stok_pkey     |         4 | 456 kB
(2 rows)

Dua baris hasil itu punya cerita yang bisa dibaca utuh: idx_stok_nama dipakai dua kali — persis dua query lookup_nama yang kita jalankan — sedangkan stok_pkey tercatat empat kali: tiga lookup_id ditambah satu pemakaian oleh DELETE ... WHERE id <= 10000 yang juga mencari baris lewat primary key. Di audit nyata, baris yang menonjol adalah kebalikannya: index besar dengan pemakaian = 0 minggu demi minggu adalah beban murni. Prosedur lengkap menimbang calon penghapusan — termasuk jebakan query tahunan yang hanya muncul saat tutup buku — sudah dibahas di chapter Biaya Pemeliharaan Index .

Memantau Ruang Disk #

Disk yang penuh membuat PostgreSQL berhenti menulis WAL — keadaan yang menghentikan seluruh transaksi server. Pemantauannya dua arah: angka df di sistem operasi untuk ruang fisik partisi, dan ukuran per database dari dalam server untuk melacak siapa yang tumbuh:

-- Pemantauan ruang dari sisi database aktif.
SELECT pg_size_pretty(pg_database_size(current_database())) AS ukuran_database;

-- pg_total_relation_size menghitung tabel + seluruh index-nya.
SELECT pg_size_pretty(pg_total_relation_size('stok')) AS stok_dengan_index,
       pg_size_pretty(pg_relation_size('stok')) AS stok_heap;

Output yang Dihasilkan:

 ukuran_database 
-----------------
 9718 kB
(1 row)

 stok_dengan_index | stok_heap 
-------------------+-----------
 2136 kB           | 1024 kB
(1 row)

Tiga angka itu saling menjelaskan: database uji utuh (ukuran_database) memakan 9718 kB — jauh lebih besar dari jumlah tabelnya karena katalog sistem ikut terhitung; tabel stok bersama kedua index-nya 2136 kB; dan heap tabel itu sendiri 1024 kB — tepat 1 MB yang berisi 10.000 baris hidup plus 10.000 baris mati yang menumpuk. Selisih 2136 kB dan 1024 kB itulah porsi index. Query yang sama tanpa filter WHERE menjadi laporan pertumbuhan harian: catat ukuran tiap database setiap hari, dan grafik yang menanjak curam menunjukkan tabel mana yang perlu diselidiki — atau file log yang perlu dirotasi lebih agresif.

REINDEX: Membangun Ulang Index yang Membengkak #

B-tree tidak menyusut ketika baris yang dirujuknya mati: halaman index tetap dialokasikan dengan banyak entri kosong di dalamnya. Bukitnya ada di audit sebelumnya — idx_stok_nama setebal 632 kB untuk data yang kini tinggal 10.000 entri. REINDEX menulis ulang index dari isi tabel sehingga hasilnya padat:

-- Index ikut membengkak: entri ke baris mati tetap menempati halaman.
SELECT pg_relation_size('idx_stok_nama') AS idx_sebelum_byte,
       pg_size_pretty(pg_relation_size('idx_stok_nama')) AS idx_sebelum;

-- REINDEX menulis ulang index menjadi padat.
REINDEX INDEX idx_stok_nama;

SELECT pg_relation_size('idx_stok_nama') AS idx_sesudah_byte,
       pg_size_pretty(pg_relation_size('idx_stok_nama')) AS idx_sesudah;

-- REINDEX CONCURRENTLY: dibangun ulang tanpa memblokir pembaca
-- dan penulis tabel (tersedia sejak PostgreSQL 12).
REINDEX INDEX CONCURRENTLY stok_pkey;

SELECT pg_relation_size('stok_pkey') AS pk_sesudah_byte;

Output yang Dihasilkan:

 idx_sebelum_byte | idx_sebelum 
------------------+-------------
           647168 | 632 kB
(1 row)

REINDEX
 idx_sesudah_byte | idx_sesudah 
------------------+-------------
           335872 | 328 kB
(1 row)

REINDEX
 pk_sesudah_byte 
-----------------
          245760
(1 row)

Angka-angka hasilnya rapi: idx_stok_nama mengecil dari 647168 byte (632 kB) menjadi 335872 byte (328 kB) — nyaris setengahnya, sebanding baris hidup yang tersisa — dan primary key yang dibangun ulang dengan CONCURRENTLY berakhir di 245760 byte. Bedanya dua bentuk perintah itu penting untuk dijadwalkan:

Pemeriksaan Integritas dengan amcheck #

Pekerjaan terakhir sifatnya asuransi: memastikan struktur B-tree index tidak rusak diam-diam — misalnya setelah kehilangan daya mendadak atau crash sistem. Extension resmi amcheck menyediakan function pemeriksa yang memvalidasi invariant internal index:

-- amcheck: extension resmi untuk memeriksa struktur B-tree.
CREATE EXTENSION amcheck;

-- bt_index_check memvalidasi konsistensi internal sebuah index.
-- Hasil kosong berarti tidak ada temuan; korupsi dilaporkan sebagai
-- error yang gagalkan panggilan ini.
SELECT bt_index_check('stok_pkey');

Output yang Dihasilkan:

CREATE EXTENSION
 bt_index_check 
----------------
 
(1 row)

Baris hasil dengan kolom kosong adalah bentuk “tidak ada temuan”: bt_index_check tidak mengembalikan laporan ketika index sehat, ia baru bersuara ketika menemukan pelanggaran — dan suaranya berupa ERROR yang gagalkan pemanggilannya (bentuk exit="3" kalau dijalankan lewat psql). Pemeriksaan ini memverifikasi hal-hal yang tak terlihat dari hasil query biasa: urutan item di setiap halaman, konsistensi struktur naik-turun antar halaman, dan kebenaran penunjuk ke tabel. Jadwalkan secara berkala untuk index penting, dan selalu setelah insiden perangkat keras. Function bt_index_parent_check memeriksa lebih dalam dengan mengunci lebih lama — untuk index partitioned dan investigasi forensik; keduanya terdokumentasi di halaman resmi amcheck.

Jendela Perawatan dan Retensi #

Dua prosedur terakhir dari daftar periksa di awal chapter tidak berbentuk query. Pertama, jendela perawatan: kerja-kerja yang mengunci — VACUUM FULL, REINDEX biasa, peningkatan versi — dijalankan di jam lalu lintas terendah, diumumkan ke pemilik aplikasi lebih dulu, dengan durasi yang diestimasi dari uji coba di server staging; dan selalu setelah backup segar tersedia, topik yang dibahas khusus di section Backup dan Recovery . Kedua, retensi file: PostgreSQL mengotomatiskan rotasi log lewat log_rotation_age dan log_rotation_size, tetapi penghapusan file lama tidak pernah dilakukannya sendiri — cron harian yang membuang log berumur di atas 30–90 hari menutup rantai itu, melengkapi arsitektur rotasi yang dibahas di chapter Server Log dan Logging Configuration .

Daftar periksa ini membuat seluruh pekerjaan pemeliharaan terjadwal dan terukur — semuanya berjalan baik-baik saja selama seseorang menjalankannya. Pertanyaan berikutnya: bagaimana bila server menurun di antara dua jadwal audit? Jawabannya adalah pengamatan menerus dengan ambang peringatan — koneksi yang menipis, cache yang memburuk, disk yang menipis — topik penutup section ini di chapter Monitoring dan Alerting .

Referensi #

• REINDEX
• amcheck
• Determining Disk Usage

-- Audit pemakaian index: mana yang sibuk, mana yang hanya makan tempat.
SELECT indexrelname,
       idx_scan AS pemakaian,
       pg_size_pretty(pg_relation_size(indexrelid)) AS ukuran
FROM pg_stat_user_indexes
WHERE relname = 'stok'
ORDER BY idx_scan, indexrelname;

-- Pemantauan ruang dari sisi database aktif.
SELECT pg_size_pretty(pg_database_size(current_database())) AS ukuran_database;

-- pg_total_relation_size menghitung tabel + seluruh index-nya.
SELECT pg_size_pretty(pg_total_relation_size('stok')) AS stok_dengan_index,
       pg_size_pretty(pg_relation_size('stok')) AS stok_heap;

-- Deteksi bloat: bandingkan baris mati dengan total baris tercatat.
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup
             / nullif(n_live_tup + n_dead_tup, 0), 1) AS rasio_bloat_pct
FROM pg_stat_user_tables
WHERE relname = 'stok';

-- amcheck: extension resmi untuk memeriksa struktur B-tree.
CREATE EXTENSION amcheck;

-- bt_index_check memvalidasi konsistensi internal sebuah index.
-- Hasil kosong berarti tidak ada temuan; korupsi dilaporkan sebagai
-- error yang gagalkan panggilan ini.
SELECT bt_index_check('stok_pkey');

#!/bin/bash
# Pemeriksaan paling mendasar: apakah server masih melayani koneksi?
# Skrip ini layak dijalankan dari cron setiap menit.
pg_isready -h "$PGHOST" -p "$PGPORT"

-- Index ikut membengkak: entri ke baris mati tetap menempati halaman.
SELECT pg_relation_size('idx_stok_nama') AS idx_sebelum_byte,
       pg_size_pretty(pg_relation_size('idx_stok_nama')) AS idx_sebelum;

-- REINDEX menulis ulang index menjadi padat.
REINDEX INDEX idx_stok_nama;

SELECT pg_relation_size('idx_stok_nama') AS idx_sesudah_byte,
       pg_size_pretty(pg_relation_size('idx_stok_nama')) AS idx_sesudah;

-- REINDEX CONCURRENTLY: dibangun ulang tanpa memblokir pembaca
-- dan penulis tabel (tersedia sejak PostgreSQL 12).
REINDEX INDEX CONCURRENTLY stok_pkey;

SELECT pg_relation_size('stok_pkey') AS pk_sesudah_byte;

-- Tabel stok gudang Toko Maju Jaya: 20.000 baris, setengahnya
-- sudah tidak dipesan lagi dan dihapus -- beban tulis-hapus yang khas.
-- autovacuum dimatikan agar angka pembanding stabil sepanjang chapter.
CREATE TABLE stok (
  id integer PRIMARY KEY,
  nama varchar(32),
  qty integer
) WITH (autovacuum_enabled = false);

INSERT INTO stok (id, nama, qty)
SELECT g,
       'Barang-' || lpad(g::text, 5, '0'),
       (g % 9) + 1
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_stok_nama ON stok (nama);

DELETE FROM stok WHERE id <= 10000;

-- Beban baca yang kita kontrol: tiga lookup lewat primary key ...
SELECT count(*) AS lookup_id FROM stok WHERE id = 15000;
SELECT count(*) AS lookup_id FROM stok WHERE id = 15000;
SELECT count(*) AS lookup_id FROM stok WHERE id = 15000;

-- ... dan dua lookup lewat index nama.
SELECT count(*) AS lookup_nama FROM stok WHERE nama = 'Barang-15000';
SELECT count(*) AS lookup_nama FROM stok WHERE nama = 'Barang-15000';