📌 Problem Statement
1. Terlalu banyak indeks pada tabel dapat memperlambat kinerja secara signifikan, baik untuk operasi tulis maupun baca.
2. Indeks yang tidak digunakan atau berlebih memboroskan disk, mencemari cache, dan menambah beban autovacuum serta WAL.
3. Tanpa pemeliharaan rutin (menghapus indeks redundan, merekonstruksi indeks yang terfragmentasi), sistem database dapat kehilangan efisiensi tanpa ketahuan.
🛠️ Methodology / Solusi / Hipotesis
1. Tinjauan mendalam dilakukan terhadap sistem internal PostgreSQL (versi 18) untuk memahami dampak indeks yang berlebihan.
2. Solusi yang disarankan:
a. Hapus indeks yang tidak pernah digunakan.
b. Hapus indeks yang redundan (meng-cover kolom yang sama atau subset).
c. Rutin re-index atau REINDEX indeks yang sangat ter-bloat.
3. Hipotesis: Dengan mengurangi jumlah indeks yang benar-benar dibutuhkan → kecepatan tulis meningkat, beban baca & rencana query berkurang, efisiensi sistem naik.
📊 Findings / Results / Impact
1. Setiap INSERT atau UPDATE non-HOT harus memperbarui semua indeks pada tabel, sehingga “write amplification” meningkat seiring banyaknya indeks.
2. Proses perencanaan query (SELECT) mempertimbangkan semua indeks—semakin banyak indeks, semakin kompleks dan lambat perencanaan.
3. Ketika indeks sangat terfragmentasi (misalnya ukuran >10× optimal), latensi scan indeks meningkat secara nyata.
4. Manajemen indeks yang baik dapat mengonsumsi sumber daya seperti buffer pool & page cache yang dapat digunakan untuk operasi yang lebih produktif.
🧩 How to Use
1. Identifikasi tabel yang memiliki banyak indeks:
SELECT schemaname, tablename, COUNT(*) AS index_count
FROM pg_indexes
GROUP BY schemaname, tablename
HAVING COUNT(*) > 10
ORDER BY index_count DESC;
2. Cari indeks yang tak pernah digunakan atau berpotensi redundan:
SELECT
idx.relname AS index_name,
pg_stat_user_indexes.idx_scan,
pg_relation_size(idx.indexrelid) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index ON idx.indexrelid = pg_stat_user_indexes.indexrelid idx
WHERE idx_scan = 0
AND pg_relation_size(idx.indexrelid) > some_threshold;
3. Hapus atau reindex secara aman dengan perintah seperti DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, atau ALTER INDEX … REBUILD.
4. Jadwalkan monitoring rutin untuk aktivitas indeks dan bloat, serta sesuaikan autovacuum dan fillfactor jika perlu.
✅ Key Takeaways
1. Indeks bukan “gratis”—terlalu banyak atau tak tepat fitur dapat merusak kinerja database.
2. Pemeliharaan indeks yang disiplin adalah bagian penting dari operasi PostgreSQL yang sehat.
3. Dengan set indeks yang lebih “lean”, Anda mendapatkan: tulis lebih cepat, baca lebih efisien, cache lebih optimal.
4. Monitoring metadata internal PostgreSQL (scan indexing, bloat, size) memberi insight yang langsung dapat dikonversi ke tindakan nyata.
5. Organisasi yang mengabaikan indeks akan menghadapi hambatan performa tersembunyi—fokus bukan hanya pada query tapi juga struktur indeks.
Sumber:
https://postgres.ai/blog/20251110-postgres-marathon-2-013-why-keep-your-index-set-lean
#PostgreSQL #Indexing #DatabasePerformance #DBATuning #PostgresInternals #OverIndexing #QueryPlan