Pola Index Bertingkat ala Daisugi untuk Data yang Terus Tumbuh

Konsep Daisugi menarik sebagai analogi: fondasi pohon tetap dipertahankan, lalu pertumbuhan baru diarahkan agar tetap rapi dan produktif. Dalam database produksi, prinsip yang mirip berlaku untuk pola index bertingkat: jangan buru-buru merombak skema atau menambah index secara acak setiap kali query melambat. Bangun struktur index yang menopang pertumbuhan data secara bertahap, dengan fokus pada pola akses yang benar-benar dipakai aplikasi.

Masalah yang paling sering muncul biasanya sederhana: tabel terus membesar, query list yang tadinya cepat mulai melambat, kombinasi filter + sort makin berat, dan pagination offset semakin mahal. Solusinya bukan satu jenis index untuk semua kasus. Anda perlu tahu kapan memakai composite index, kapan covering index masuk akal, kapan partial index lebih efisien, dan kapan saatnya meninggalkan offset pagination ke keyset pagination.

Analogi Daisugi cukup dipakai sebagai pembuka: tujuan kita bukan menanam pohon baru untuk setiap masalah query, tetapi membentuk fondasi index yang bisa menopang banyak pertumbuhan tanpa membuat biaya write meledak.

Gejala Nyata Saat Index Sudah Tidak Mengimbangi Pertumbuhan Data

Sebelum menambah index baru, kenali dulu gejala bottleneck yang umum di produksi. Gejala ini lebih berguna daripada sekadar asumsi bahwa “tabel besar pasti butuh index tambahan”.

Endpoint list melambat seiring waktu, padahal logika aplikasi tidak banyak berubah.
Query dengan filter dan sort tertentu timeout, tetapi query lain pada tabel yang sama masih normal.
CPU database naik saat jam sibuk karena banyak operasi sort atau scan besar.
I/O meningkat karena engine harus membaca banyak halaman data untuk mengambil sedikit hasil.
Offset pagination makin lambat di halaman belakang, misalnya page 1 cepat, page 500 sangat lambat.
Write throughput turun setelah banyak index ditambahkan, terutama pada tabel dengan insert/update tinggi.

Gejala-gejala ini menunjukkan dua kemungkinan besar: query tidak memakai index yang cocok, atau index yang ada tidak sesuai urutan filter dan sort yang dipakai aplikasi.

Audit Query Lambat Sebelum Menambah Index

Kesalahan umum adalah langsung membuat index baru tanpa melihat query nyata di produksi. Padahal satu index tambahan punya biaya permanen pada insert, update, delete, reindex, dan penggunaan storage.

1. Kumpulkan query yang benar-benar lambat

Ambil dari slow query log, query monitoring APM, atau statistik database. Cari query yang:

sering dipanggil,
punya total waktu kumulatif besar,
berdampak langsung ke endpoint utama,
menggunakan filter, sort, dan pagination.

Jangan optimalkan query yang jarang dieksekusi tetapi kebetulan terlihat mahal sekali jika dampaknya kecil.

2. Kelompokkan berdasarkan pola akses

Contoh pola yang sering muncul:

List order berdasarkan created_at DESC
Filter status lalu urutkan waktu terbaru
Filter tenant/user lalu urutkan ID atau waktu
Pencarian data aktif saja, bukan seluruh riwayat

Dari sini Anda bisa melihat bahwa yang perlu dioptimalkan bukan kolom individual secara terpisah, melainkan kombinasi kolom yang muncul bersama.

3. Jalankan EXPLAIN

EXPLAIN membantu melihat apakah database memakai index, melakukan full scan, harus sort tambahan, atau membaca terlalu banyak baris. Detail output berbeda antar mesin database, tetapi prinsip membacanya cukup umum:

Access path: apakah query memakai index scan, range scan, atau full table scan.
Filter pushdown: apakah kondisi WHERE diterapkan lewat index atau setelah banyak baris diambil.
Sort step: apakah ORDER BY bisa dipenuhi dari urutan index atau perlu sort terpisah.
Estimated rows: berapa banyak baris yang diperkirakan dibaca sebelum hasil akhir dikembalikan.
Lookup tambahan ke tabel: apakah setelah menemukan key di index, engine masih harus sering mengambil row penuh dari heap/table.

Jika EXPLAIN menunjukkan pembacaan ratusan ribu baris untuk mengembalikan 20 item list, hampir pasti ada peluang desain index yang lebih baik.

4. Bandingkan query sebelum dan sesudah perubahan

Optimasi index harus diukur. Lihat perubahan pada:

jenis scan,
jumlah row yang dibaca,
adanya operasi sort,
latensi nyata di lingkungan staging atau produksi terkontrol.

Composite Index: Fondasi Utama untuk Filter + Sort

Untuk query list pada tabel yang terus tumbuh, composite index biasanya menjadi fondasi utama. Ini adalah index yang mencakup beberapa kolom sekaligus, disusun sesuai pola query.

Kapan composite index dipakai

Gunakan ketika query sering memakai kombinasi seperti:

SELECT id, status, created_at, total_amount
FROM orders
WHERE tenant_id = ?
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Jika Anda hanya punya index terpisah pada tenant_id, status, dan created_at, itu belum tentu cukup. Database sering tetap harus melakukan pekerjaan tambahan besar karena filter dan urutan tidak cocok dengan struktur index.

Index yang lebih selaras dengan query biasanya berbentuk:

CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at DESC);

Mengapa urutan kolom penting

Composite index bekerja baik karena engine bisa menelusuri data dalam urutan yang sudah dekat dengan kebutuhan query. Secara umum:

Kolom dengan filter equality sering diletakkan di depan, misalnya tenant_id, status.
Kolom untuk range atau sort biasanya menyusul, misalnya created_at.
Susunan ini membantu query memfilter lebih awal dan menghindari sort terpisah.

Namun tidak ada aturan tunggal untuk semua kasus. Anda harus menyesuaikan dengan pola query dominan. Jika aplikasi selalu memfilter tenant_id lalu mengurutkan berdasarkan created_at, index yang dimulai dari status bisa jadi kurang efektif meski semua kolom yang sama tetap ada.

Kesalahan umum pada composite index

Membuat semua permutasi index untuk berjaga-jaga. Ini cepat membebani write dan storage.
Mengandalkan index kolom tunggal untuk query multi-kondisi dan multi-sort.
Tidak mencocokkan arah sort dengan query yang dominan, jika engine/database Anda sensitif pada arah traversal untuk performa tertentu.
Menaruh kolom low-selectivity secara sembarang di depan tanpa melihat pola filter aktual.

Covering Index: Mengurangi Lookup ke Tabel

Setelah composite index benar, bottleneck berikutnya sering datang dari table lookup. Query menemukan kandidat row dari index, tetapi masih harus bolak-balik mengambil kolom lain dari tabel utama. Pada skala besar, ini mahal.

Covering index membantu ketika index tidak hanya dipakai untuk filter dan sort, tetapi juga sudah memuat kolom yang dibutuhkan query sehingga engine bisa menyelesaikan query langsung dari index, atau setidaknya mengurangi lookup tambahan.

Contoh kasus

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = ?
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Jika pola ini sangat sering dipanggil, Anda mungkin mempertimbangkan index yang mencakup kolom pencarian sekaligus kolom hasil yang kecil dan stabil. Bentuk spesifiknya berbeda tergantung database, tetapi idenya sama: jadikan index cukup “menutup” kebutuhan query.

Kapan covering index layak

Query list sangat sering dipanggil.
Kolom yang diambil sedikit dan ukurannya tidak besar.
Latensi banyak tersita pada lookup row setelah index scan.
Pola select cukup stabil, tidak sering berubah-ubah.

Kapan sebaiknya dihindari

Kolom yang dimasukkan ke index terlalu banyak.
Ada kolom besar seperti teks panjang atau payload JSON yang tidak cocok untuk dibawa ke index.
Tabel punya write rate tinggi sehingga pembesaran index sangat terasa.

Trade-off utamanya jelas: read bisa lebih cepat, tetapi index menjadi lebih besar, maintenance lebih mahal, dan update ke kolom terkait ikut menambah biaya.

Partial Index: Fokus ke Data yang Benar-Benar Sering Diakses

Pada banyak sistem produksi, tidak semua row sama pentingnya. Query list harian mungkin hampir selalu membaca data aktif, status tertentu, atau rentang waktu mutakhir. Jika begitu, partial index bisa lebih efisien daripada mengindeks seluruh tabel.

Contoh pola

Misalnya aplikasi hampir selalu menampilkan order yang belum diarsipkan:

SELECT id, tenant_id, status, created_at
FROM orders
WHERE archived = false
  AND tenant_id = ?
ORDER BY created_at DESC
LIMIT 50;

Pada database yang mendukung partial index, Anda dapat membuat index hanya untuk subset row yang relevan, misalnya data archived = false.

Keuntungan partial index

Ukuran index lebih kecil.
Write overhead lebih rendah dibanding index penuh.
Query yang memang menargetkan subset itu bisa lebih efisien.

Batasan partial index

Hanya efektif jika predicate query konsisten dengan kondisi partial index.
Tidak membantu query yang sering mengakses seluruh data atau kondisi yang berbeda-beda.
Perlu disiplin pada desain query aplikasi agar engine dapat memanfaatkannya.

Partial index sangat cocok untuk pola “data aktif vs arsip”, “soft delete”, “status tertentu yang mendominasi traffic”, atau antrian kerja yang hanya memproses item belum selesai.

Keyset Pagination: Saat OFFSET Mulai Menjadi Beban

Banyak query list melambat bukan hanya karena kurang index, tetapi karena menggunakan LIMIT ... OFFSET ... pada tabel besar. Masalahnya, untuk mencapai offset tinggi, database sering tetap harus melewati banyak row terlebih dahulu.

Masalah offset pagination

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 10000;

Walau ada index, offset besar tetap bisa mahal karena engine harus mengabaikan banyak hasil sebelum mengambil 50 row berikutnya.

Gunakan keyset pagination

Keyset pagination memakai nilai baris terakhir sebagai penanda halaman berikutnya. Ini biasanya lebih stabil untuk data yang terus tumbuh.

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Dengan index yang sesuai, misalnya pada (tenant_id, created_at DESC, id DESC), database bisa langsung melanjutkan dari posisi terakhir tanpa membuang ribuan row.

Kapan keyset pagination lebih tepat

Endpoint list dengan traffic tinggi.
Data terus bertambah.
Sort menggunakan kolom yang stabil dan deterministik.
Pengguna tidak benar-benar butuh loncat ke halaman nomor arbitrer seperti page 527.

Hal yang perlu diperhatikan

Urutan harus deterministik. Jika created_at bisa sama, tambahkan tie-breaker seperti id.
Cursor harus konsisten dengan urutan sort.
Implementasi API perlu mengubah kontrak dari page number ke cursor/token.

Untuk list besar, kombinasi composite index + keyset pagination sering memberi dampak lebih nyata daripada sekadar menambah index baru untuk menolong offset yang memang mahal secara desain.

Kapan Index Justru Membebani Write

Setiap index tambahan mempercepat beberapa read, tetapi juga menambah kerja saat data ditulis. Pada tabel transaksi, event log, queue, atau metrics, hal ini sangat terasa.

Biaya write yang sering terlupakan

INSERT: setiap row baru harus masuk ke semua index terkait.
UPDATE: jika kolom terindeks berubah, engine harus memperbarui entry index.
DELETE: index juga harus dibersihkan.
Storage dan cache pressure: index besar memakan disk dan buffer pool/cache.
Vacuum, compaction, maintenance: makin banyak index, makin panjang biaya perawatan internal.

Tanda Anda sudah over-indexed

Query read hanya sedikit membaik, tetapi insert/update turun nyata.
Ada banyak index mirip dengan perbedaan kecil yang jarang dipakai.
Monitoring menunjukkan sebagian besar index hampir tidak pernah disentuh.

Karena itu, pola index bertingkat ala Daisugi berarti menambah “cabang” index hanya untuk jalur pertumbuhan yang memang dibutuhkan, bukan untuk setiap kemungkinan query yang belum tentu penting.

Cara Membaca EXPLAIN Secara Umum

Setiap database punya format EXPLAIN berbeda, tetapi beberapa pertanyaan ini hampir selalu relevan:

Apakah query memakai index yang diharapkan?
Jika tidak, cari tahu apakah predicate tidak cocok, statistik tidak akurat, atau urutan kolom index tidak selaras.
Apakah jumlah row yang dibaca terlalu besar?
Jika ya, index mungkin terlalu umum atau filter utama belum ada di bagian depan index.
Apakah ada langkah sort terpisah?
Jika ada, mungkin ORDER BY belum didukung oleh urutan index.
Apakah query harus kembali ke tabel utama untuk setiap row?
Jika ya, covering index mungkin relevan jika cost lookup tinggi.
Apakah cardinality/selectivity masuk akal?
Kolom dengan nilai berulang besar tidak selalu ideal sebagai satu-satunya tumpuan index.

EXPLAIN bukan sekadar alat untuk melihat “index dipakai atau tidak”. Query bisa memakai index tetapi tetap lambat jika harus membaca terlalu banyak row, melakukan sort besar, atau offset terlalu jauh.

Contoh Strategi Index Bertingkat untuk Query List Produksi

Bayangkan tabel orders terus tumbuh dan endpoint utama aplikasi adalah daftar order per tenant dengan beberapa variasi:

default list: WHERE tenant_id = ? ORDER BY created_at DESC
filter status: WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC
hanya data aktif: WHERE tenant_id = ? AND archived = false ORDER BY created_at DESC
pagination pada data besar

Pendekatan bertingkat yang masuk akal bisa seperti ini:

Fondasi utama: index untuk pola list paling sering, misalnya tenant + waktu.
Lapisan kedua: composite index untuk tenant + status + waktu jika filter status benar-benar dominan.
Lapisan ketiga: partial index untuk data aktif saja jika traffic list hampir seluruhnya mengarah ke row non-arsip.
Optimasi lanjutan: ubah pagination ke keyset agar tidak bergantung pada offset besar.
Covering index selektif: hanya jika lookup tambahan masih menjadi bottleneck dan kolom hasil tetap kecil.

Dengan cara ini, Anda tidak menumpuk index sejak awal. Anda menumbuhkan index berdasarkan pola akses nyata, sambil menjaga fondasi tetap stabil.

Checklist Sebelum Menambah Index Baru

Gunakan checklist ini agar keputusan lebih disiplin:

Apakah query ini benar-benar penting berdasarkan traffic atau dampak bisnis?
Apakah ada bukti dari slow query log atau monitoring, bukan asumsi?
Apakah EXPLAIN menunjukkan scan besar, sort mahal, atau lookup berlebihan?
Apakah masalah sebenarnya pagination offset, bukan sekadar kurang index?
Apakah index baru selaras dengan kombinasi WHERE dan ORDER BY yang dominan?
Apakah index yang mirip sebenarnya sudah ada dan bisa dimanfaatkan?
Apakah write overhead pada tabel ini masih aman jika index ditambah?
Apakah subset data lebih cocok memakai partial index?
Apakah query perlu covering index, atau cukup composite index biasa?
Apakah ada rencana menghapus index yang redundant setelah validasi?

Debugging Tips Saat Query Tetap Lambat

Cek query aktual dari aplikasi, bukan versi yang sudah dirapikan secara manual. Kadang ORM menghasilkan kondisi tambahan yang mengubah rencana eksekusi.
Perhatikan urutan sort dan tie-breaker. Query dengan ORDER BY created_at saja bisa tidak stabil jika banyak nilai sama.
Lihat ukuran row yang diambil. Mengambil terlalu banyak kolom bisa membuat optimasi index kurang terasa.
Validasi parameter paling umum. Query cepat untuk satu tenant kecil belum tentu cepat untuk tenant terbesar.
Jangan lupa statistik database. Optimizer yang bekerja dengan statistik usang bisa memilih rencana yang buruk.
Audit index yang sudah ada. Index berlebihan kadang menutupi masalah desain query dan justru membebani sistem.

Penutup

Pola index bertingkat ala Daisugi berarti membangun index secara sadar, berlapis, dan mengikuti pertumbuhan query yang nyata. Mulailah dari composite index untuk pola filter + sort utama, gunakan covering index hanya jika lookup ke tabel masih mahal, manfaatkan partial index untuk subset data yang benar-benar dominan, dan pertimbangkan keyset pagination saat offset tidak lagi efisien.

Yang paling penting, jangan menganggap index sebagai solusi gratis. Index yang baik menopang pertumbuhan data tanpa merombak fondasi, tetapi index yang berlebihan bisa menjadi beban permanen pada write path. Audit query lambat, baca EXPLAIN dengan disiplin, ukur hasilnya, lalu tumbuhkan struktur index seperti Daisugi: sedikit, terarah, dan kuat menopang pertumbuhan jangka panjang.

Quick Search

shopping_cart Keranjang