Database Indexing Strategy: B-Trees, Hash Indexes, dan Lainnya

Index adalah struktur data tambahan yang dibuat database di samping tabel utama, mirip seperti daftar isi atau indeks di buku. Mungkin kamu pernah melihat indeks pada kamus atau buku yang tebal, biasanya ditulis di akhir buku, yang berisi daftar kata dan halaman tempat kata tersebut berada. Bayangkan mencari kata di kamus tanpa indeks: kamu harus membuka satu per satu setiap halaman. Tetapi dengan indeks, kamu bisa langsung melompat ke halaman yang tepat.

Hal yang sama berlaku untuk database. Tanpa index, database harus membaca setiap baris dari awal sampai akhir tabel untuk menemukan data yang kamu cari — proses ini disebut full table scan. Index memungkinkan database langsung melompat ke lokasi data yang tepat, sehingga query yang tadinya butuh waktu beberapa detik bisa selesai dalam hitungan milidetik.

Namun, index bukan obat ajaib. Setiap index yang kamu buat menyimpan salinan sebagian data dalam format terstruktur, artinya ia memakan ruang storage tambahan. Yang lebih penting, setiap kali kamu melakukan INSERT, UPDATE, atau DELETE, database juga harus memperbarui semua index yang ada di tabel itu — bukan hanya datanya. Semakin banyak index, semakin berat operasi tulis. Tabel dengan banyak index yang jarang dipakai bisa justru memperlambat aplikasimu secara keseluruhan.

Aturan praktisnya sederhana: buat index hanya untuk query yang benar-benar dijalankan, bukan untuk kolom yang “terasa penting” secara intuitif. Mulailah dari query yang paling sering muncul dan paling lambat — biasanya bisa ditemukan lewat slow query log di database-mu. Kolom yang sering muncul di klausa WHERE, JOIN ON, atau ORDER BY adalah kandidat utama. Jangan menebak-nebak; ukur dulu, lalu putuskan.

Daftar Isi

• Mulai dari bukti
• Tipe index umum
• Composite index mengikuti query
• Covering, partial, dan unique index
• Rawat index
• Baca execution plan lebih dulu
• Urutan kolom pada composite index
• Selectivity dan kolom low-cardinality
• Index juga menjaga kebenaran data
• Biaya write, maintenance, dan rollout aman
• Pagination yang ramah index
• FAQ

Mulai dari bukti

Ambil query yang lambat lalu periksa execution plan dengan fitur seperti EXPLAIN ANALYZE. Lihat jumlah row yang dibaca, filter dan join yang dipakai, serta apakah hasil harus diurutkan. Ukur sebelum dan sesudah menambah index; plan lebih bisa dipercaya daripada intuisi.

Tipe index umum

B-tree adalah default di banyak relational database. Ia mendukung equality, range, dan hasil terurut, sehingga cocok untuk primary key, foreign key, atau WHERE created_at >= .... Hash index khusus untuk equality lookup dan biasanya tidak membantu range query. Ada juga full-text, JSON, spatial, dan inverted index untuk tipe data serta operator tertentu.

Composite index mengikuti query

Misalnya query utama aplikasi adalah:

SELECT id, total
FROM orders
WHERE customer_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 20;

Index yang dimulai dengan (customer_id, status, created_at) dapat mendukung filter sekaligus pengurutan. Urutan dari kiri penting; index ini jauh kurang berguna bila query hanya memfilter status. Lebih baik punya sedikit index yang sesuai access path penting daripada semua kombinasi kolom.

Covering, partial, dan unique index

Covering index dapat menyertakan seluruh kolom yang dibutuhkan query sehingga table read berkurang. Partial index hanya menyimpan row dengan kondisi tertentu, misalnya akun aktif, sehingga lebih kecil. Unique index bukan sekadar optimasi: ia memastikan aturan bisnis seperti satu subscription per pelanggan tetap benar saat ada request bersamaan.

Rawat index

Tinjau index yang tidak dipakai atau duplikat, terutama setelah fitur berubah. Hindari membungkus kolom terindeks dengan function kecuali kamu membuat expression index, dan gunakan cursor untuk paginasi dalam daripada OFFSET besar. Selalu uji dengan data yang mendekati produksi.

Kesimpulan

Jadikan indexing sebagai loop: amati workload, baca plan, ubah satu hal, lalu ukur dampaknya. Kebiasaan ini mencegah read lambat dan write path yang terlalu banyak index.

Baca execution plan lebih dulu

Index hanya berguna jika optimizer dapat memakainya untuk query yang benar-benar dijalankan. Sebelum menambah index, ambil query dengan parameter realistis lalu periksa plan-nya. Di PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) sangat berguna karena ia menunjukkan apa yang benar-benar terjadi, bukan hanya perkiraan planner.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Perhatikan empat sinyal berikut:

• Row yang dibaca dibanding hasil yang dikembalikan. Membaca 100.000 row untuk menghasilkan 20 row biasanya perlu diperiksa.
• Jenis scan. Sequential scan bisa tepat untuk tabel kecil, tetapi mahal untuk query selektif pada tabel besar.
• Kerja sort dan join. Index kadang dapat menyediakan urutan dari ORDER BY atau membuat join lebih murah.
• Estimasi dibanding row aktual. Selisih besar dapat menunjukkan statistik basi atau distribusi data yang belum dipahami optimizer.

Jangan menganggap setiap sequential scan sebagai kegagalan. Jika query memang mengembalikan sebagian besar tabel kecil, membaca seluruh tabel dapat lebih murah daripada bolak-balik melalui index lalu mengambil banyak row tersebar.

Urutan kolom pada composite index

Composite index adalah area yang sering membuat upaya indexing yang terlihat benar menjadi tidak efektif. B-tree diurutkan dari kiri ke kanan. Pada index (customer_id, status, created_at), database dapat menggunakan customer_id terlebih dahulu, mempersempit dengan status, lalu membaca hasil sesuai urutan created_at.

Urutan terbaik mengikuti bentuk query, bukan aturan mutlak “kolom paling selektif harus pertama”. Kondisi equality biasanya diletakkan di awal; kondisi range seperti created_at >= ... umumnya mengakhiri prefix terurut yang dapat dipakai; kolom yang hanya ditampilkan dapat dipertimbangkan sebagai included column.

-- riwayat satu pelanggan
WHERE customer_id = ? ORDER BY created_at DESC

-- antrean operasional
WHERE status = 'pending' ORDER BY created_at ASC

Dua access pattern ini sering membutuhkan index berbeda. Memaksa satu index lebar untuk keduanya sering menghasilkan kompromi yang tidak cukup membantu keduanya. Konfirmasi dengan execution plan dan frekuensi workload sebelum menyimpan dua-duanya.

Selectivity dan kolom low-cardinality

Selectivity menggambarkan seberapa jauh kondisi mengurangi kandidat row. Kolom boolean seperti is_active sering punya selectivity rendah: jika 90 persen row aktif, index biasa di kolom itu mungkin tidak membantu. Partial index dapat lebih tepat ketika subset minoritas yang sering dicari:

CREATE INDEX CONCURRENTLY idx_orders_pending_created_at
ON orders (created_at)
WHERE status = 'pending';

Index ini lebih kecil karena tidak menyimpan seluruh order dan langsung mendukung worker yang terus mencari order pending. Kondisinya harus cocok dengan predicate query, jadi tetap verifikasi lewat execution plan.

Index juga menjaga kebenaran data

Gunakan unique index untuk invariant yang harus tetap benar saat request bersamaan. Validasi “cek lalu insert” di aplikasi dapat mengalami race condition; dua request sama-sama melihat data belum ada. Unique constraint membuat database menyelesaikan konflik tersebut.

CREATE UNIQUE INDEX users_email_unique
ON users (lower(email));

Expression index di atas juga mempercepat pencarian email yang case-insensitive jika query memakai ekspresi sama. Rancang respons untuk uniqueness violation; validasi sebelum write saja tidak cukup.

Biaya write, maintenance, dan rollout aman

Setiap insert menulis ke semua index yang relevan. Update dapat menulis ulang entri index, sedangkan delete meninggalkan pekerjaan vacuum atau maintenance. Index lebar memakai memory dan storage lebih besar; index duplikat memperlambat write tanpa banyak manfaat. Tinjau index setelah fitur berubah dan hapus duplikasi hanya setelah yakin ia tidak mendukung query atau constraint lain.

Pada tabel production yang sibuk, pembuatan index dapat mengunci tabel atau memakai resource besar. Gunakan mekanisme online atau concurrent dari database, jadwalkan dengan hati-hati, dan pantau progresnya. Uji waktu migration pada salinan data yang mendekati production. Index yang benar secara teknis tetap bisa menjadi deployment yang tidak aman bila mengunci tabel kritis saat traffic puncak.

Pagination yang ramah index

OFFSET besar memaksa database menemukan lalu membuang row-row sebelumnya. Beberapa page awal mungkin masih cepat, tetapi page ke-500 bisa mahal karena database tetap harus melewati row sebelumnya sebelum mengembalikan 50 row berikutnya.

-- Mudah ditulis, tetapi makin mahal untuk page yang jauh.
SELECT id, created_at, message
FROM events
WHERE account_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 25000;

Untuk activity feed, gunakan keyset pagination dengan cursor stabil. Cursor biasanya diambil dari row terakhir pada page sebelumnya, misalnya gabungan created_at dan id:

SELECT id, created_at, message
FROM events
WHERE account_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index (account_id, created_at DESC, id DESC) cocok dengan query ini. Kolom id membuat urutan tetap stabil saat timestamp sama, sehingga page berikutnya tidak melewatkan atau menggandakan row dengan nilai created_at yang sama.

CREATE INDEX CONCURRENTLY idx_events_account_feed
ON events (account_id, created_at DESC, id DESC);

Di layer API, row terakhir dapat dikembalikan sebagai cursor yang di-encode:

{
  "items": [
    { "id": 8842, "created_at": "2026-09-09T10:15:00Z", "message": "..." }
  ],
  "next_cursor": "2026-09-09T10:15:00Z|8842"
}

Cursor-based pagination paling cocok untuk feed, audit log, notifikasi, dan flow “next page”. OFFSET masih boleh dipakai untuk tabel admin kecil atau kasus ketika user benar-benar perlu loncat ke nomor page tertentu.

FAQ

Apakah setiap foreign key perlu index?

Biasanya ya, terutama ketika parent row dihapus atau diubah dan database harus mencari row yang mereferensikannya. Cek dokumentasi serta query plan karena perilaku tiap database dapat berbeda.

Apakah ORM otomatis membuat index yang tepat?

ORM dapat mendeklarasikan index, tetapi ia tidak tahu query aplikasi mana yang lambat atau penting. Simpan definisinya dalam migration, lalu validasi terhadap workload nyata.

Kapan index perlu dihapus?

Hapus ketika monitoring menunjukkan index tidak dipakai atau duplikat dan ia bukan constraint. Lakukan dengan sengaja, amati workload sesudahnya, dan siapkan rollback.