Avoid Over-Indexing #

Index sering dianggap sebagai obat mujarab untuk semua masalah performa database. Query lambat? Tambah index. JOIN berat? Tambah index. ORDER BY lama? Tambah index.

Masalahnya: index bukan gratis.

Over-indexing adalah kondisi ketika database memiliki terlalu banyak index, baik yang jarang dipakai, redundant, atau bahkan kontraproduktif. Alih-alih mempercepat sistem, over-indexing justru sering menjadi sumber degradasi performa, pembengkakan storage, dan kompleksitas operasional.

Artikel ini membahas:

• Apa itu over-indexing
• Dampak teknis yang sering tidak disadari
• Ciri-ciri database yang sudah over-index
• Best practice menghindari over-indexing
• Prinsip berpikir yang sehat soal index

Apa Itu Over-Indexing? #

Over-indexing adalah kondisi ketika jumlah dan jenis index di sebuah tabel:

• Lebih banyak dari yang benar-benar dibutuhkan
• Tidak sebanding dengan pola query aktual
• Tidak sejalan dengan karakter workload (read vs write)

Contoh klasik:

• Setiap kolom diberi single index
• Banyak composite index dengan prefix mirip
• Index dibuat berdasarkan asumsi, bukan data real

Dampak Negatif Over-Indexing #

Write Performance Turun Drastis #

Setiap operasi berikut harus meng-update semua index terkait:

• INSERT
• UPDATE
• DELETE

Jika sebuah tabel memiliki:

• 1 primary key
• 6 secondary index

Maka satu INSERT berarti 7 struktur index harus dimodifikasi.

Efeknya:

• Write latency meningkat
• Lock lebih lama
• Throughput turun

Pada sistem high-write (event log, transaksi, audit trail), ini bisa jadi bottleneck utama.

Storage Membengkak Diam-Diam #

Index menyimpan:

• Key
• Pointer ke row
• Struktur B-Tree / LSM Tree

Beberapa fakta:

• Index bisa lebih besar dari data itu sendiri
• Composite index menyimpan duplikasi kolom
• Index jarang dipakai tetap makan storage

Akibatnya:

• Disk usage naik cepat
• Backup lebih besar
• Restore lebih lama
• Replikasi makin berat

Optimizer Bingung Memilih Index #

Terlalu banyak index membuat query optimizer punya terlalu banyak pilihan.

Efek nyata:

• Optimizer salah pilih index
• Execution plan tidak optimal
• Performa tidak konsisten antar waktu

Ini sering terjadi ketika:

• Banyak index mirip
• Statistik tidak up-to-date
• Distribusi data berubah

Maintenance Jadi Mahal #

Index membawa biaya operasional:

• REINDEX
• VACUUM
• ANALYZE
• Online index rebuild

Semakin banyak index:

• Maintenance window lebih lama
• Risiko lock meningkat
• Operasi schema change makin mahal

Ciri-Ciri Database Mengalami Over-Indexing #

Beberapa red flags:

• Jumlah index > jumlah query penting
• Banyak index tidak pernah dipakai
• Insert/update lambat tapi select cepat
• Query plan sering berubah tanpa perubahan query
• Storage index > storage table

Jika kamu melihat ini, hampir pasti index sudah kebanyakan.

Kesalahan Umum dalam Membuat Index #

Mengindex Semua Kolom #

“Biar aman” adalah alasan paling mahal dalam database.

Tidak semua kolom:

• Dipakai di WHERE
• Dipakai di JOIN
• Perlu index

Membuat Index Sebelum Query Ada #

Index berbasis asumsi:

• “Nanti pasti kepakai”
• “Takut lambat ke depannya”

Padahal:

• Pola query bisa berubah
• Data distribution bisa beda

Index harus reaktif terhadap kebutuhan nyata, bukan prediksi.

Redundant Index #

Contoh:

• Index (user_id)
• Composite index (user_id, created_at)

Single index user_id seringkali tidak lagi dibutuhkan karena sudah tercakup sebagai prefix.

Best Practice Menghindari Over-Indexing #

Index Berdasarkan Query Nyata #

Gunakan data, bukan intuisi:

• Slow query log
• Query monitoring
• EXPLAIN / EXPLAIN ANALYZE

Prinsip:

Tidak ada query → tidak ada index

Prioritaskan Composite Index yang Tepat #

Satu composite index yang benar:

• Lebih baik dari 3 single index
• Lebih hemat storage
• Lebih mudah dipahami optimizer

Tapi ingat:

• Urutan kolom sangat penting
• Jangan asal gabung kolom

Audit dan Hapus Index yang Tidak Dipakai #

Lakukan audit berkala:

• Index usage statistics
• Last used timestamp

Index yang:

• Tidak pernah dipakai
• Dipakai sangat jarang

→ kandidat kuat untuk dihapus.

Bedakan Read-Heavy dan Write-Heavy Table #

Read-heavy table:

• Lebih toleran terhadap index banyak

Write-heavy table:

• Sangat sensitif terhadap index
• Harus minimal dan selektif

Jangan samakan strategi index untuk keduanya.

Jangan Takut Menghapus Index #

Menghapus index:

• Lebih murah dari menambah
• Bisa dikembalikan jika perlu

Best practice:

• Drop index
• Monitor
• Rollback jika perlu

Index bukan kontrak seumur hidup.

Prinsip Mental Model yang Sehat tentang Index #

Beberapa prinsip penting:

• Index adalah biaya, bukan fitur gratis
• Index mempercepat SELECT, memperlambat WRITE
• Index harus melayani query, bukan ego developer
• Lebih sedikit index yang tepat > banyak index asal

Jika sebuah index tidak bisa kamu jelaskan:

“Query apa yang diselamatkan oleh index ini”

Maka besar kemungkinan index itu tidak perlu.

Penutup #

Over-indexing adalah masalah yang sering tidak terasa di awal, tapi sangat mahal di skala besar.

Database yang sehat bukan database dengan index paling banyak, tapi:

• Index yang tepat
• Sesuai pola akses
• Mudah dirawat

Ingat:

Database cepat bukan karena banyak index, tapi karena index yang benar.