Menambahkan indeks secara spekulatif—dengan asumsi bahwa suatu kolom "mungkin akan dicari nanti"—adalah salah satu anti-pattern paling destruktif pada beban kerja OLTP (Online Transaction Processing) throughput tinggi. Setiap indeks sekunder di PostgreSQL memiliki biaya pemeliharaan non-sepele: setiap operasi INSERT, UPDATE, dan DELETE wajib memperbarui struktur pohon B-Tree indeks tersebut. Hal ini memicu amplifikasi penulisan (write amplification), memecah mekanisme HOT (Heap-Only Tuples), dan menyedot ruang berharga di shared_buffers.

Anatomi Beban Write: Kegagalan Mekanisme HOT

PostgreSQL mengimplementasikan optimasi Heap-Only Tuples (HOT). Jika operasi UPDATE memodifikasi baris tanpa menyentuh kolom yang terindeks, dan ruang penyimpanan tersedia di blok heap yang sama, engine tidak perlu menambahkan pointer baru ke dalam indeks-indeks yang ada. PostgreSQL cukup merangkai tuple lama ke tuple baru di level heap.

Ketika indeks spekulatif dipasang pada kolom yang sering mengalami modifikasi (seperti kolom status, timestamp, atau counter), optimasi HOT langsung gugur:

  • Write Amplification: Satu baris UPDATE berubah dari sekadar modifikasi blok heap lokal menjadi I/O traversal dan modifikasi B-Tree leaf pages di seluruh indeks spekulatif tersebut.
  • B-Tree Page Split: Jika leaf page B-Tree penuh, PostgreSQL harus membelah halaman (page split), mengalokasikan 8KB page baru, mendistribusikan keys, dan menuliskan catatan WAL tambahan.
  • Pencemaran Shared Buffers: Halaman indeks yang jarang dibaca tetap harus dimuat ke dalam memori saat terjadi penulisan, menggusur halaman data aktif (heap pages) dari buffer pool.

Deteksi Indeks Tidak Terpakai via pg_stat_user_indexes

Langkah pertama adalah menemukan indeks yang tidak pernah atau sangat jarang dipindai oleh planner. Statistik akumulatif dapat diperiksa melalui kombinasi sistem katalog pg_stat_user_indexes dan pg_index. Pastikan database telah berjalan cukup lama sejak reset statistik terakhir sebelum mengambil keputusan.

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size_bytes,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE NOT i.indisunique
  AND NOT i.indisprimary
  AND s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC;

Perhatikan rasio idx_scan terhadap volume write pada tabel. Jika sebuah tabel menerima 10 juta tuple modifikasi (n_tup_ins + n_tup_upd + n_tup_del pada pg_stat_user_tables) tetapi indeks hanya mencatat 10 kali idx_scan dalam kurun waktu satu bulan, indeks tersebut menghasilkan net negative ROI terhadap performa I/O secara keseluruhan.

Identifikasi Indeks Redundan dan Duplikat

B-Tree standar mendukung pencarian awalan terkiri (leftmost prefix). Indeks tunggal pada kolom (a) adalah redundan jika sudah ada indeks komposit pada (a, b), kecuali indeks tunggal tersebut memiliki predikat parsial khusus (partial index) atau operator class yang berbeda.

Jalankan kueri berikut untuk menemukan indeks redundan yang tumpang-tindih:

SELECT
    ind1.indrelid::regclass AS table_name,
    idx1.relname AS redundant_index,
    pg_size_pretty(pg_relation_size(ind1.indexrelid)) AS redundant_size,
    idx2.relname AS covering_index,
    pg_size_pretty(pg_relation_size(ind2.indexrelid)) AS covering_size
FROM pg_index ind1
JOIN pg_index ind2 ON ind1.indrelid = ind2.indrelid
JOIN pg_class idx1 ON ind1.indexrelid = idx1.oid
JOIN pg_class idx2 ON ind2.indexrelid = idx2.oid
WHERE ind1.indexrelid != ind2.indexrelid
  AND NOT ind1.indisunique
  AND ind1.indpred IS NULL
  AND ind2.indpred IS NULL
  AND ind1.indkey[0:array_length(ind1.indkey, 1) - 1] = ind2.indkey[0:array_length(ind1.indkey, 1) - 1]
  AND array_length(ind1.indkey, 1) < array_length(ind2.indkey, 1);

Prosedur Eliminasi Tanpa Downtime

Perintah DROP INDEX standar mengambil ACCESS EXCLUSIVE lock pada tabel induk, memblokir seluruh operasi SELECT, INSERT, UPDATE, dan DELETE sampai transaksi selesai. Pada tabel berukuran besar, hal ini memicu antrean query lock dan downtime aplikasi.

Gunakan klausa CONCURRENTLY untuk menurunkan level locking menjadi SHARE UPDATE EXCLUSIVE, yang tetap mengizinkan operasi read dan write berjalan secara normal.

Langkah Eksekusi Safe Drop

  1. Pastikan tidak berada di dalam blok transaksi eksplisit (jangan gunakan BEGIN ... COMMIT).
  2. Cek transaksi berjalan yang berpotensi memblokir proses drop:
SELECT pid, usename, state, age(clock_timestamp(), query_start), query
FROM pg_stat_activity
WHERE state != 'idle' AND backend_type = 'client backend';
  1. Jalankan penghapusan indeks:
DROP INDEX CONCURRENTLY IF EXISTS schema_name.idx_spekulatif_target;
Penting: DROP INDEX CONCURRENTLY menunggu semua transaksi yang sedang mengakses tabel selesai sebelum menghapus indeks secara fisik. Jika perintah dibatalkan di tengah jalan, indeks dapat ditandai sebagai invalid. Indeks berstatus invalid tidak akan dipakai query planner, namun masih memakan disk dan memperbarui data penulisan. Pastikan menghapus indeks invalid tersebut secara tuntas.

Benchmark & Verifikasi Metrik

Dampak langsung dari eliminasi indeks spekulatif dapat diukur melalui metrik throughput WAL (Write-Ahead Logging) dan aktivitas checkpointer.

1. Volume Produksi WAL

Penurunan jumlah indeks langsung memotong volume WAL record per transaksi. Bandingkan laju penulisan WAL sebelum dan sesudah eliminasi via pg_stat_wal (PostgreSQL 14+):

SELECT
    wal_records,
    wal_fpi,
    pg_size_pretty(wal_bytes) AS wal_size
FROM pg_stat_wal;

2. Checkpoint IOPS

Ketika indeks spekulatif dihapus, jumlah dirty buffers yang harus dibilas (flushed) ke disk saat checkpoint berkurang secara linier. Pada PostgreSQL 17+, metrik ini dapat dipantau di pg_stat_checkpointer (atau pg_stat_bgwriter pada versi 16 ke bawah):

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_checkpointer;

Dengan memangkas 3–5 indeks spekulatif pada tabel berbobot transaksi tinggi, sistem biasanya mengalami penurunan volume penulisan WAL sebesar 20–40% dan lonjakan performa write TPS tanpa perlu mengalokasikan CPU atau disk IOPS tambahan.