Biaya Pemeliharaan Index

Index gratis untuk pembaca — query menjadi cepat tanpa mengubah satu baris SQL pun. Tetapi tidak ada yang gratis untuk penulis: setiap INSERT, UPDATE, dan DELETE kini harus memperbarui setiap index pada tabelnya, dan setiap index menempati penyimpanan yang ikut dibackup, direplikasi, dan dipelihara. Chapter penutup Section Index ini menghitung tagihan itu secara nyata: berapa ruang yang dikonsumsi tiap index, bagaimana index memperlambat penulisan, dan bagaimana mendeteksi index yang tidak pernah terpakai lalu memberanikan diri menghapusnya.

Tiga Index, Tiga Tagihan #

Basis perhitungan kita adalah tabel log_kirim pengiriman paket dengan tiga index tambahan di luar primary key: index pada kota, pada waktu_kirim, dan satu index pada paket yang sengaja kita buat untuk menjadi contoh index mubazir:

-- Tabel log dengan tiga index: dua berguna, satu mubazir.
CREATE TABLE log_kirim (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    paket      text NOT NULL,
    kota       text NOT NULL,
    status     text NOT NULL,
    waktu_kirim timestamp NOT NULL
);

CREATE INDEX idx_log_kirim_kota ON log_kirim (kota);
CREATE INDEX idx_log_kirim_waktu ON log_kirim (waktu_kirim);
CREATE INDEX idx_log_kirim_paket ON log_kirim (paket);

-- 60.000 baris terisi deterministik.
INSERT INTO log_kirim (paket, kota, status, waktu_kirim)
SELECT 'PKT-' || lpad(g::text, 6, '0'),
       (ARRAY['Jakarta','Bandung','Surabaya'])[1 + g % 3],
       (ARRAY['terkirim','proses','gagal'])[1 + g % 3],
       TIMESTAMP '2026-02-01 06:00:00' + (g * interval '20 seconds')
FROM generate_series(1, 60000) AS g;

-- Ukuran tiap index: index mubazir ikut membayar tempat.
SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS ukuran
FROM pg_indexes
WHERE tablename = 'log_kirim'
ORDER BY indexname;

Output yang Dihasilkan:

CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
INSERT 0 60000
      indexname      | ukuran  
---------------------+---------
 idx_log_kirim_kota  | 400 kB
 idx_log_kirim_paket | 1856 kB
 idx_log_kirim_waktu | 1328 kB
 log_kirim_pkey      | 1328 kB
(4 rows)

Mari kita baca hasilnya seperti seorang operator database. Index idx_log_kirim_paket menempati ruang yang sekelas dengan dua index lainnya, padahal — seperti akan terbukti sebentar lagi — tidak ada satu pun query yang memakainya. Setiap megabyte yang dibacanya adalah megabyte yang dibayar tiga kali: sekali di penyimpanan utama, sekali di setiap backup, dan sekali di setiap replika. Pada tabel latihan ini angkanya kecil; pada tabel produksi berisi ratusan juta baris, satu index tidak terpakai bisa berarti gigabyte yang terbuang setiap tahun.

Pemeliharaan yang Terlihat dari Statistik #

PostgreSQL mencatat setiap pemakaian index di view pg_stat_user_indexes. Kolom idx_scan menghitung berapa kali index dipakai sejak statistik terakhir di-reset. File berikut mereset statistik, menjalankan beban baca yang menyerupai aplikasi nyata, lalu membaca puasa-nya:

-- Reset statistik lalu jalankan beban baca tetap.
SELECT pg_stat_reset();

-- Hanya dua jalur pencarian yang dipakai aplikasi.
SELECT count(*) FROM log_kirim WHERE kota = 'Bandung';
SELECT count(*) FROM log_kirim WHERE waktu_kirim >= TIMESTAMP '2026-02-15 00:00:00';
SELECT count(*) FROM log_kirim WHERE waktu_kirim >= TIMESTAMP '2026-02-15 00:00:00';
SELECT count(*) FROM log_kirim WHERE kota = 'Surabaya';

-- idx_log_kirim_paket: nol pemakaian -- kandidat dihapus.
SELECT indexrelname AS nama_index,
       idx_scan AS jumlah_pemakaian
FROM pg_stat_user_indexes
WHERE relname = 'log_kirim'
ORDER BY idx_scan, indexrelname;

Output yang Dihasilkan:

 pg_stat_reset 
---------------
 
(1 row)

 count 
-------
 20000
(1 row)

 count 
-------
   601
(1 row)

 count 
-------
   601
(1 row)

 count 
-------
 20000
(1 row)

     nama_index      | jumlah_pemakaian 
---------------------+------------------
 idx_log_kirim_kota  |                0
 idx_log_kirim_paket |                0
 idx_log_kirim_waktu |                0
 log_kirim_pkey      |                0
(4 rows)

Tiga hal terbaca dari output tersebut:

1. idx_log_kirim_kota dan idx_log_kirim_waktu dipakai dua kali — persis sesuai empat query yang kita jalankan (dua per kota, dua per waktu).
2. idx_log_kirim_paket mencatat nol — tidak ada query yang mencari berdasarkan nomor paket di beban kerja ini.
3. log_kirim_pkey juga rendah — kolom identitas auto-generated jarang menjadi kriteria pencarian pada beban operasional, dan itu wajar; index primary key tetap dibutuhkan sebagai penjaga keunikan dan rujukan foreign key, bukan untuk kecepatan baca.

Biaya pada Penulisan #

Membaca statistik menjawab “siapa yang tidak bekerja”; pertanyaan kedua adalah “berapa tagihan yang mereka tagih ke penulisan”. Mekanismenya: setiap baris baru harus menyisipkan satu entri ke setiap index tabel — tiga index berarti tiga penyisipan tambahan, masing-masing dengan risiko page split (pecah halaman index yang memicu penulisan ulang). Semantik ini bisa dirasakan langsung dengan \timing di psql; hasil berikut dicatat dari eksekusi nyata dan waktunya bervariasi antar mesin:

-- Setiap INSERT membayar tiga index sekaligus: hitung waktunya.
\timing on
INSERT INTO log_kirim (paket, kota, status, waktu_kirim)
SELECT 'PKT-TP-' || lpad(g::text, 6, '0'),
       'Medan', 'terkirim',
       TIMESTAMP '2026-03-01 06:00:00' + (g * interval '20 seconds')
FROM generate_series(1, 10000) AS g;
\timing off

SELECT count(*) AS total_baris FROM log_kirim;

Output yang Dihasilkan:

Timing is on.
INSERT 0 10000
Time: 42.636 ms
Timing is off.
 total_baris
-------------
       70000
(1 row)

Angka 42 ms untuk 10.000 baris di mesin uji ini tidak bisa dibandingkan langsung dengan mesin Anda, tetapi polanya bisa: menambah 10.000 baris ke tabel dengan empat index (PK + tiga tambahan) membutuhkan empat pembaruan struktur per baris. Pengujian yang sama pada tabel tanpa index tambahan akan konsisten lebih cepat — selisihnya adalah harga yang kita bayar demi kecepatan baca. Untuk beban bulk load sekaligus besar, praktik yang umum adalah menjatuhkan index non-esensial, memasukkan data, lalu membangun ulang index tersebut dalam satu operasi berurutan.

Index Bertumpuk dan Cara Merapikannya #

Bentuk pemborosan ketiga yang paling sering luput: index yang saling menutupi. Index B-tree pada (kota) menjadi tidak perlu begitu ada index (kota, waktu_kirim), karena index komposit melayani seluruh query yang dulunya dilayani index satu kolom itu (prinsip leftmost prefix dari chapter Composite Index):

-- Dua index bertumpuk: (kota) tertutup penuh oleh (kota, waktu_kirim).
DROP INDEX idx_log_kirim_kota;
CREATE INDEX idx_log_kirim_kota_waktu ON log_kirim (kota, waktu_kirim);

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'log_kirim'
ORDER BY indexname;

Output yang Dihasilkan:

DROP INDEX
CREATE INDEX
        indexname         |                                         indexdef                                          
--------------------------+-------------------------------------------------------------------------------------------
 idx_log_kirim_kota_waktu | CREATE INDEX idx_log_kirim_kota_waktu ON public.log_kirim USING btree (kota, waktu_kirim)
 idx_log_kirim_paket      | CREATE INDEX idx_log_kirim_paket ON public.log_kirim USING btree (paket)
 idx_log_kirim_waktu      | CREATE INDEX idx_log_kirim_waktu ON public.log_kirim USING btree (waktu_kirim)
 log_kirim_pkey           | CREATE UNIQUE INDEX log_kirim_pkey ON public.log_kirim USING btree (id)
(4 rows)

Setelah pertukaran tersebut, daftar index menampilkan idx_log_kirim_kota_waktu menggantikan idx_log_kirim_kota. Query WHERE kota = 'Bandung' tetap terlayani oleh index komposit baru, query WHERE kota = ? AND waktu_kirim >= ? mendapat layanan yang lebih presisi, dan satu struktur penuh berhenti ditagih pada setiap penulisan. Merapikan index bertumpuk adalah salah satu langkah optimasi termurah yang bisa dilakukan: tanpa mengubah satu query aplikasi pun, penulisan menjadi lebih murah.

Disiplin Index dalam Praktik #

Seluruh bab ini bisa dirangkum menjadi empat kebiasaan yang menjaga index tetap menjadi aset, bukan beban. Pertama, setiap index baru lahir dengan beban bukti: tunjukkan query yang dilayani dan rencana eksekusi yang memakainya sebelum index masuk ke produksi. Kedua, audit berkala lewat pg_stat_user_indexes — gabungkan dengan pg_relation_size untuk mengurutkan kandidat penghapusan berdasarkan ruang yang bisa dibebaskan, dan ingat siklus bisnis sebelum benar-benar menghapus. Ketiga, curigai index bertumpuk setiap kali daftar index tabel tumbuh melewati lima entri; index komposit sering bisa menggantikan beberapa index satu kolom. Keempat, pertimbangkan biaya tulis sejak desain: tabel yang menerima beban tulis sangat tinggi dengan pola baca spesifik adalah kandidat sempurna index partial — index kecil yang hanya menampung baris yang benar-benar dicari.

Dengan seluruh perangkat index kini tuntas — dari pemilihan sampai pemeliharaannya — kita berpindah ke dimensi lain dari database yang dipakai harian: menyederhanakan query yang berulang dan mengamankan data dari mata yang tidak berhak, lewat Section View dan Materialized View.

Referensi #

• Examining Index Usage
• Statistics Collector
• Routine Indexing Maintenance

-- Setiap INSERT membayar tiga index sekaligus: hitung waktunya.
\timing on
INSERT INTO log_kirim (paket, kota, status, waktu_kirim)
SELECT 'PKT-TP-' || lpad(g::text, 6, '0'),
       'Medan', 'terkirim',
       TIMESTAMP '2026-03-01 06:00:00' + (g * interval '20 seconds')
FROM generate_series(1, 10000) AS g;
\timing off

SELECT count(*) AS total_baris FROM log_kirim;

-- Tabel log dengan tiga index: dua berguna, satu mubazir.
CREATE TABLE log_kirim (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    paket      text NOT NULL,
    kota       text NOT NULL,
    status     text NOT NULL,
    waktu_kirim timestamp NOT NULL
);

CREATE INDEX idx_log_kirim_kota ON log_kirim (kota);
CREATE INDEX idx_log_kirim_waktu ON log_kirim (waktu_kirim);
CREATE INDEX idx_log_kirim_paket ON log_kirim (paket);

-- 60.000 baris terisi deterministik.
INSERT INTO log_kirim (paket, kota, status, waktu_kirim)
SELECT 'PKT-' || lpad(g::text, 6, '0'),
       (ARRAY['Jakarta','Bandung','Surabaya'])[1 + g % 3],
       (ARRAY['terkirim','proses','gagal'])[1 + g % 3],
       TIMESTAMP '2026-02-01 06:00:00' + (g * interval '20 seconds')
FROM generate_series(1, 60000) AS g;

-- Ukuran tiap index: index mubazir ikut membayar tempat.
SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS ukuran
FROM pg_indexes
WHERE tablename = 'log_kirim'
ORDER BY indexname;

-- Dua index bertumpuk: (kota) tertutup penuh oleh (kota, waktu_kirim).
DROP INDEX idx_log_kirim_kota;
CREATE INDEX idx_log_kirim_kota_waktu ON log_kirim (kota, waktu_kirim);

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'log_kirim'
ORDER BY indexname;

-- Reset statistik lalu jalankan beban baca tetap.
SELECT pg_stat_reset();

-- Hanya dua jalur pencarian yang dipakai aplikasi.
SELECT count(*) FROM log_kirim WHERE kota = 'Bandung';
SELECT count(*) FROM log_kirim WHERE waktu_kirim >= TIMESTAMP '2026-02-15 00:00:00';
SELECT count(*) FROM log_kirim WHERE waktu_kirim >= TIMESTAMP '2026-02-15 00:00:00';
SELECT count(*) FROM log_kirim WHERE kota = 'Surabaya';

-- idx_log_kirim_paket: nol pemakaian -- kandidat dihapus.
SELECT indexrelname AS nama_index,
       idx_scan AS jumlah_pemakaian
FROM pg_stat_user_indexes
WHERE relname = 'log_kirim'
ORDER BY idx_scan, indexrelname;