Database Index untuk Application Developer: Kapan, Index Mana, dan Kenapa Jangan Kebanyakan

Database Index untuk Application Developer: Kapan, Index Mana, dan Kenapa Jangan Kebanyakan

Kebanyakan tim aplikasi memperlakukan index seperti tombol performa gratis. Query terasa lambat, tambah index, lanjut. Cara ini jalan, sampai satu tabel punya lima belas index, setiap write jadi lebih lambat, dan tidak ada yang ingat index mana melayani query yang mana.

Solusinya bukan disiplin indexing yang lebih abstrak. Solusinya memahami betul ongkos sebuah index, lalu mengirim satu index dalam satu waktu.

Ongkos nyata index di setiap write

Bayangkan index seperti daftar isi buku: begitu satu bab ditambahkan, daftar isinya harus ditulis ulang juga. Index database sama saja — ia struktur data kedua yang harus tetap sinkron dengan tabel aslinya, bukan sekadar catatan tempelan.

Setiap INSERT menulis satu baris dan satu entri di setiap index pada tabel itu. Setiap UPDATE yang menyentuh kolom terindeks menulis ulang entri index yang bersangkutan. Setiap DELETE menghapus baris dari tabel sekaligus dari setiap index yang menunjuk ke sana. Tabel dengan lima index mengubah satu INSERT menjadi enam kali penulisan.

Ongkos ini terus bertambah. Benchmark Percona menaikkan jumlah index pada skema PostgreSQL dari 7 ke 39, di bawah beban kerja campuran insert/update/select. Throughput turun dari sekitar 1.400 transaksi per detik menjadi sekitar 600 — kira-kira 42% dari titik awal. Rata-rata waktu transaksi naik dari 11ms ke 26ms.

Beban query-nya sendiri tidak berubah. Satu-satunya variabel adalah berapa banyak struktur index yang harus dipelihara di setiap penulisan. Ini bukan argumen menolak index. Ini argumen menolak menambah index karena firasat: setiap index yang Anda kirim adalah pajak permanen untuk setiap write di masa depan, dan ia harus punya query jelas yang menjustifikasinya.

Urutan kolom composite index: lupakan "kolom paling selektif duluan"

Saran "kolom paling selektif ditaruh duluan" sering diulang tapi jarang benar-benar berlaku. Aturan sebenarnya, menurut dokumentasi resmi PostgreSQL: constraint equality di kolom-kolom terdepan, ditambah satu constraint range atau inequality tepat setelahnya — itulah yang membatasi seberapa besar bagian index yang benar-benar di-scan. Kolom di posisi lebih kanan tetap diperiksa di dalam index, sehingga menghemat kunjungan ke tabel, tapi tidak mempersempit rentang scan seperti kolom equality terdepan.

Jadi pertanyaan desainnya bukan "kolom mana yang punya lebih banyak nilai unik." Pertanyaannya "kolom mana yang query saya filter dengan =, dan kolom mana yang difilter dengan range." Index pada (status, created_at) melayani WHERE status = 'active' AND created_at > $1 dengan baik terlepas dari mana yang lebih selektif, karena status adalah predikat equality dan wajib di depan.

Urutan kolom harus mengikuti query yang Anda jalankan, bukan tebakan cardinality. Alasan yang sama kenapa dashboard yang dikelompokkan per timezone butuh bentuk index sendiri, yang saya bahas di grouping data multi-timezone di PostgreSQL. Selektivitas memang jadi penentu saat dua urutan sama-sama melayani query yang sama, tapi itu faktor sekunder — bukan aturan utama seperti yang diklaim mitos "paling selektif duluan". Jangan menumpuk kolom tanpa batas juga: dokumentasi PostgreSQL sendiri bilang index satu kolom biasanya sudah cukup, dan index lebih dari tiga kolom jarang membantu kecuali pola pemakaian tabelnya sangat khusus.

Covering index dan INCLUDE: baca gratis, sampai tidak lagi

Index B-tree bisa menjawab query sepenuhnya dari dirinya sendiri, tanpa menyentuh tabel, kalau semua kolom yang dibutuhkan query ada di dalam index. Ini disebut index-only scan, jalur baca tercepat yang ada. Kalau query butuh satu kolom tambahan hanya untuk output, bukan untuk filter, CREATE INDEX ON orders(customer_id) INCLUDE (total_amount) menempelkan kolom itu sebagai payload tanpa memperlebar key yang dicari.

Ada jebakan yang diam-diam membatalkan manfaatnya. Index-only scan hanya melewati tabel kalau halaman heap-nya ditandai all-visible di visibility map PostgreSQL. Kalau belum, mesin tetap harus mengunjungi baris itu untuk memastikan visibilitasnya. I/O yang didapat sama seperti index scan biasa, tanpa keuntungan apa pun.

Tabel yang mayoritas dibaca akan tetap all-visible dan covering index-nya terbayar. Tabel dengan write terus-menerus di antara proses vacuum jarang sempat mengejar, dan kolom INCLUDE yang Anda tambahkan jadi beban mati. Cek jadwal autovacuum dulu sebelum mengandalkan ini.

Membaca EXPLAIN (ANALYZE, BUFFERS) tanpa panik

Output EXPLAIN ANALYZE terlihat menakutkan: node bersarang, estimasi cost, jumlah buffer, timing di mana-mana. Abaikan hampir semuanya di bacaan pertama. Cek satu angka saja: estimasi rows versus rows aktual di node yang paling dekat dengan masalahnya.

Menurut dokumentasi PostgreSQL, angka rows pada sebuah node adalah perkiraan yang dikeluarkan node itu setelah filtering-nya sendiri, bukan jumlah yang di-scan. Angka ini rutin lebih rendah dari jumlah baris mentah. Estimasi dan aktual yang berdekatan berarti planner memahami data Anda dengan baik. Selisih 10x atau lebih biasanya berarti statistik yang basi, dan index baru tidak akan memperbaikinya — ANALYZE pada tabel sering lebih membantu daripada index baru.

Ada satu jebakan operasional sebelum menjalankan ini pada apa pun selain SELECT: EXPLAIN ANALYZE bukan sekadar merencanakan statement, ia benar-benar menjalankannya. Jalankan pada UPDATE dan write-nya sungguhan terjadi. Bungkus dalam transaksi lalu rollback:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

Kenapa "index saja semua kolom" itu tanda bahaya, bukan strategi

Selain ongkos per-write di atas, tumpukan index yang terus bertambah punya dua konsekuensi lagi. Storage: index sebuah tabel yang digabung sering kali ukurannya melebihi tabelnya sendiri. Ongkos planning: planner harus mempertimbangkan setiap kandidat index yang menyentuh kolom terkait, dan index yang mirip-mirip satu sama lain memberinya lebih banyak peluang memilih yang justru kurang cocok.

Ada feedback loop di baliknya. Semakin banyak index, semakin banyak kerja autovacuum di setiap putaran. Vacuum makin tertinggal di tabel yang sibuk. Bloat menumpuk. Query melambat — dan reaksi umumnya adalah menambah index lagi untuk mengompensasi. Setiap index yang dikirim tanpa query spesifik di baliknya membuat query lambat berikutnya sedikit lebih sulit diperbaiki.

Alur kerja konsulting: satu query lambat dalam satu waktu

Versi yang benar-benar bertahan dalam proyek konsulting, di database production milik klien yang tidak bebas saya jadikan bahan eksperimen, tetap sempit:

Satu query, satu hipotesis, satu perubahan, satu pengukuran. Sekumpulan index spekulatif yang ditambahkan sekaligus mungkin memperbaiki masalah. Tapi index mana yang berperan tidak akan ketahuan, dan ongkos write untuk semua index lainnya tetap ditanggung tanpa batas waktu. Ubah satu variabel dalam satu waktu. Setiap index yang tersisa punya query yang menjustifikasinya.

Checklist sebelum mengirim index baru

• Apakah Anda punya query lambat yang sebenarnya beserta output EXPLAIN ANALYZE-nya, bukan sekadar tebakan?
• Apakah estimasi jumlah rows kurang lebih cocok dengan aktualnya, atau Anda perlu ANALYZE dulu?
• Apakah urutan kolom mengikuti cara query memfilter — equality dulu, baru kolom range — bukan cardinality mentah?
• Apakah INCLUDE bisa dipakai di sini, dan apakah tabel ini cukup terjaga vacuum-nya untuk mendapat index-only scan?
• Apakah Anda mengirim index ini sendirian, sehingga efeknya bisa diukur secara terisolasi?
• Apakah sudah ada index dengan kolom terdepan yang sama yang mencakup kasus ini?

Indexing adalah alat yang ditargetkan, bukan pengaturan yang harus dimaksimalkan. Tim yang paling optimal memanfaatkan database bukan yang punya index terbanyak. Yang bisa menunjuk setiap index yang mereka miliki dan menyebutkan query yang menjadi alasannya.