Audit Moral Query Lambat: Kapan Index Layak Ditambah?

Query lambat tidak otomatis berarti database butuh index baru. Dalam banyak kasus, masalahnya justru ada pada bentuk query, pola pagination, distribusi data, atau ekspektasi produk yang tidak realistis. Menambah index memang bisa mempercepat read, tetapi juga menambah biaya write, storage, maintenance, dan kompleksitas operasional.

Karena itu, audit moral query lambat berarti melihat optimasi database sebagai keputusan teknik yang berdampak ke tim, biaya, dan pengguna. Pertanyaannya bukan hanya “bisa lebih cepat?”, tetapi juga “siapa yang menanggung konsekuensinya?”: tim backend yang menghadapi write amplification, tim ops yang memantau storage dan bloat, serta pengguna yang terdampak oleh latensi, timeout, atau data yang terlambat masuk.

Apa yang Dimaksud dengan Audit Moral Query Lambat?

Istilah ini berguna untuk mengingatkan bahwa performa bukan sekadar angka benchmark. Satu index tambahan bisa memperbaiki endpoint pencarian, tetapi juga memperlambat insert dan update pada tabel yang sibuk. Query analitik yang dipaksa cepat di database transaksi bisa mengganggu workload utama. Pagination offset besar bisa terlihat sederhana di API, tetapi mahal di database.

Dalam praktik sehari-hari, audit ini berarti:

Memastikan masalahnya nyata, bukan sekadar asumsi dari feeling atau metrik yang belum dipisahkan.
Membaca rencana eksekusi sebelum mengubah skema.
Menilai distribusi data, bukan hanya nama kolom yang sering dipakai di WHERE.
Mempertimbangkan dampak write saat menambah index.
Memilih solusi paling murah yang menyelesaikan akar masalah.

Workflow Nyata Saat Menemukan Query Lambat

1. Konfirmasi query yang benar-benar lambat

Mulailah dari log query lambat, APM, tracing, atau metrik database. Jangan menebak dari endpoint yang terasa lambat. Bisa jadi bottleneck ada di network, serialization, N+1 query, atau lock contention, bukan pada satu query besar.

Catat minimal hal berikut:

SQL yang dieksekusi.
Parameter atau pola parameter yang umum.
Frekuensi query.
p95/p99 latency jika tersedia.
Jumlah baris hasil.
Dampak bisnisnya: endpoint utama, job background, atau dashboard internal.

2. Baca EXPLAIN atau EXPLAIN ANALYZE

EXPLAIN menunjukkan rencana eksekusi. EXPLAIN ANALYZE menambahkan eksekusi nyata dan waktu aktual. Nama output berbeda antar database, tetapi prinsipnya sama: lihat bagaimana database memilih akses data, join, sort, dan filter.

Fokus pada sinyal berikut:

Full table scan / sequential scan pada tabel besar.
Rows examined jauh lebih besar daripada rows returned.
Sort mahal karena ORDER BY tidak didukung index.
Filter terjadi setelah scan besar.
Nested loop yang masuk akal pada set kecil, tetapi buruk pada input besar.
Estimate meleset jauh dari actual, yang bisa menandakan statistik usang atau distribusi data yang tidak merata.

EXPLAIN ANALYZE
SELECT id, user_id, created_at
FROM orders
WHERE status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Jika rencana menunjukkan scan besar lalu sort, pertanyaannya bukan langsung “kolom mana yang di-index?”, tetapi:

Apakah filter status = 'paid' cukup selektif?
Apakah created_at dipakai juga untuk sorting?
Apakah query ini selalu butuh 50 baris terbaru?
Apakah index gabungan lebih masuk akal daripada dua index tunggal?

3. Identifikasi apakah full scan itu masalah atau justru wajar

Full scan tidak selalu salah. Jika tabel kecil, atau query memang mengambil sebagian besar baris, scan penuh bisa lebih efisien daripada memakai index lalu melakukan banyak lookup acak.

Tambahkan index hanya jika:

Tabel cukup besar sehingga scan penuh mahal.
Query hanya butuh sebagian kecil baris.
Query sering dijalankan atau penting bagi pengguna.
Index dapat membantu filter, sort, atau join secara nyata.

Jangan tambahkan index jika query memang membaca sebagian besar tabel. Dalam kasus seperti itu, index sering hanya memindahkan biaya, bukan menghapusnya.

Kapan Index Layak Ditambah?

1. Saat selectivity cukup baik

Selectivity adalah seberapa kecil subset data yang dipilih oleh kondisi. Kolom dengan nilai yang sangat beragam biasanya lebih selektif daripada kolom dengan sedikit variasi.

Contoh:

email = ? biasanya sangat selektif.
status = 'active' sering kurang selektif jika mayoritas baris aktif.
created_at > now() - interval '1 day' bisa selektif atau tidak, tergantung volume data.

Index pada kolom dengan selectivity rendah sering mengecewakan. Database bisa tetap memilih scan penuh karena membaca terlalu banyak baris lewat index tidak efisien.

2. Saat cardinality mendukung

Cardinality secara praktis menggambarkan banyaknya nilai unik atau variasi distribusi yang membantu database membedakan baris. Kolom dengan cardinality tinggi biasanya kandidat index yang lebih baik untuk filter presisi.

Namun jangan berhenti di “nilai unik banyak”. Lihat juga pola query nyata. Kolom dengan cardinality tinggi tetap tidak berguna jika hampir tidak pernah dipakai pada WHERE, JOIN, atau ORDER BY.

3. Saat index mendukung pola filter + sort sekaligus

Salah satu kasus terbaik untuk menambah index adalah saat query memfilter dan mengurutkan data dengan pola yang stabil. Misalnya daftar order terbaru untuk satu user:

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

Dalam pola seperti ini, index gabungan pada kolom yang sesuai sering lebih berguna daripada index tunggal terpisah. Alasannya: database dapat menemukan subset data yang tepat sekaligus mempertahankan urutan yang dibutuhkan, sehingga biaya sort turun.

Catatan: Urutan kolom dalam index gabungan penting. Pilih berdasarkan pola query dominan, bukan urutan kebetulan di model atau migrasi.

4. Saat query sering dieksekusi dan berdampak ke pengguna

Query lambat yang dieksekusi ribuan kali per menit lebih layak dioptimasi dengan index dibanding query laporan bulanan yang bisa dijalankan offline. Frekuensi dan criticality sama pentingnya dengan latency per eksekusi.

Tanyakan:

Apakah ini endpoint utama aplikasi?
Apakah query ini memengaruhi p95 pengguna?
Apakah alternatifnya memerlukan perubahan aplikasi yang jauh lebih besar?

5. Saat biaya write tambahan masih dapat diterima

Setiap index baru biasanya menambah pekerjaan saat INSERT, UPDATE, dan DELETE. Pada tabel yang sangat write-heavy, tambahan index bisa menjadi biaya utama.

Index layak ditambah jika:

Read jauh lebih dominan daripada write.
Latency read kritis untuk pengalaman pengguna.
Volume write masih aman setelah diuji.
Biaya storage dan maintenance dapat diterima.

Kapan Sebaiknya Query Ditulis Ulang?

1. Saat fungsi atau transformasi membuat index sulit dipakai

Penggunaan fungsi pada kolom yang difilter sering membuat optimizer sulit memanfaatkan index biasa.

-- Kurang ideal
SELECT *
FROM users
WHERE DATE(created_at) = '2025-08-01';

Lebih baik ubah menjadi rentang waktu yang eksplisit:

SELECT *
FROM users
WHERE created_at >= '2025-08-01 00:00:00'
  AND created_at <  '2025-08-02 00:00:00';

Mengapa ini bekerja? Karena database dapat mencari rentang nilai langsung pada kolom, alih-alih menghitung fungsi untuk banyak baris terlebih dahulu.

2. Saat SELECT mengambil kolom yang tidak perlu

SELECT * memperbesar I/O, transfer data, dan biaya deserialisasi. Pada tabel lebar atau hasil besar, memilih kolom seperlunya memberi dampak nyata.

3. Saat join dan subquery bisa dipersempit lebih awal

Jika query melakukan join antar tabel besar, usahakan filter diterapkan sedini mungkin. Kadang bukan index yang kurang, tetapi bentuk query membuat database memproses terlalu banyak baris sebelum menyaring hasil akhir.

4. Saat masalah utamanya ada pada model akses data

Contoh umum adalah endpoint yang meminta “semua data lalu difilter di aplikasi”. Solusinya bukan index tambahan, melainkan memindahkan filter ke SQL, menambah batas hasil, atau memecah kebutuhan menjadi endpoint yang lebih spesifik.

Kapan Harus Membatasi Pagination dengan Offset Besar?

Offset pagination nyaman dipakai, tetapi mahal pada halaman besar. Query seperti LIMIT 50 OFFSET 100000 tetap memaksa database menghitung atau melewati banyak baris sebelum mengembalikan hasil.

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 50 OFFSET 100000;

Meskipun ada index untuk created_at, offset besar tetap punya biaya. Database tidak bisa “meloncat gratis” ke baris ke-100000 tanpa konsekuensi logis pada traversal data.

Pilih keyset/cursor pagination saat:

Data besar dan terus bertambah.
Pengguna lebih sering menelusuri maju daripada lompat ke halaman arbitrer.
Urutan hasil stabil, misalnya berdasarkan created_at dan id.

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pendekatan ini biasanya lebih stabil untuk dataset besar karena database mencari titik lanjutan dari cursor terakhir, bukan menghitung offset yang makin mahal.

Kapan offset masih boleh dipakai?

Tabel kecil.
Halaman yang diakses dangkal.
Kebutuhan UI memang perlu nomor halaman tetap.
Biaya query sudah terukur dan masih aman.

Prinsip praktis: Jika masalah utama ada pada offset besar, menambah index baru sering tidak menyelesaikan akar masalah. Yang perlu diubah adalah strategi pagination.

Trade-off Nyata: Write Amplification, Storage, dan Index Bloat

Write amplification

Setiap index tambahan berarti setiap perubahan data harus memperbarui lebih banyak struktur. Dampaknya paling terasa pada tabel transaksi, event log, atau sinkronisasi data yang padat write.

Gejalanya bisa berupa:

Insert/update melambat.
Kontensi meningkat.
Autovacuum atau maintenance makin berat pada database tertentu.
Replikasi atau backup ikut membesar.

Biaya storage

Index mengonsumsi ruang disk, kadang mendekati atau melampaui ukuran data efektif untuk pola tertentu. Pada skala kecil ini mungkin tidak terasa, tetapi pada tabel besar biaya storage, backup, dan restore akan ikut naik.

Index bloat

Index yang sering berubah dapat mengalami bloat: ukuran fisik membesar dan efisiensi turun. Ini bukan alasan untuk takut memakai index, tetapi alasan untuk memantau dampaknya setelah optimasi.

Perhatikan tanda-tanda berikut:

Ukuran index tumbuh lebih cepat dari pertumbuhan data.
Performa query memburuk meski index “sudah ada”.
Maintenance window makin berat.

Jika database Anda menyediakan statistik ukuran objek, gunakan itu untuk membandingkan ukuran tabel, ukuran index, dan laju pertumbuhannya. Jangan hanya melihat latency query di aplikasi.

Cara Evaluasi: Index Baru atau Perubahan Query?

Pertanyaan keputusan yang berguna

Apakah query lambat karena membaca terlalu banyak baris?
Jika ya, cek apakah index bisa membuat subset lebih kecil.
Apakah predicate cukup selektif?
Jika tidak, index mungkin tidak membantu banyak.
Apakah ORDER BY menyebabkan sort mahal?
Jika ya, pertimbangkan index yang sesuai dengan pola filter dan urutan.
Apakah query memakai fungsi pada kolom?
Jika ya, tulis ulang query terlebih dahulu.
Apakah offset pagination menjadi akar masalah?
Jika ya, ganti strategi pagination.
Apakah tabel sangat write-heavy?
Jika ya, biaya index tambahan harus diuji lebih hati-hati.
Apakah query ini cukup sering atau cukup penting?
Jangan optimasi agresif untuk query langka tanpa dampak nyata.

Contoh keputusan praktis

Misalnya ada query:

SELECT id, user_id, amount, created_at
FROM payments
WHERE tenant_id = ?
  AND status = 'settled'
ORDER BY created_at DESC
LIMIT 100;

Workflow berpikir yang sehat:

Lihat EXPLAIN/ANALYZE: apakah ada full scan, sort besar, atau rows examined tinggi?
Ukur distribusi: apakah satu tenant_id punya data sangat banyak? Apakah status = 'settled' mayoritas?
Jika tenant_id sangat membatasi data dan query ini sering dipakai, index gabungan yang mendukung filter dan urutan mungkin layak.
Jika ternyata hampir semua baris berstatus settled dan data per tenant tetap besar, index pada status saja mungkin tidak berguna.
Jika endpoint memakai offset halaman dalam, perbaiki pagination sebelum menambah index baru.

Checklist Engineer Backend untuk Audit Moral Query Lambat

Ambil query nyata dari log atau tracing, bukan pseudo-query dari ingatan.
Jalankan EXPLAIN/ANALYZE pada parameter yang representatif.
Bandingkan rows scanned vs rows returned.
Cek full scan dan pastikan apakah itu buruk atau justru wajar.
Nilai selectivity dari kondisi WHERE yang dominan.
Evaluasi cardinality dan distribusi data, bukan hanya jumlah row total.
Lihat pola ORDER BY, JOIN, dan LIMIT.
Periksa apakah query bisa ditulis ulang agar lebih ramah index.
Audit pagination: adakah offset besar yang sebenarnya masalah utama?
Hitung trade-off write pada tabel yang sering berubah.
Cek ukuran index dan potensi bloat setelah perubahan.
Uji sebelum-sesudah dengan beban yang realistis, bukan hanya satu eksekusi lokal.
Dokumentasikan alasan keputusan: mengapa menambah index, mengapa tidak, dan dampak yang diharapkan.

Kesalahan Umum yang Sering Terjadi

Menambah index untuk setiap kolom di WHERE tanpa memahami query plan.
Mengandalkan index tunggal terpisah padahal query butuh dukungan filter + sort yang spesifik.
Mengabaikan biaya write karena hanya fokus pada latency read.
Mengoptimasi query langka sementara bottleneck utama ada di query yang sering.
Menggunakan offset besar lalu menyalahkan database karena lambat.
Tidak memeriksa bloat dan statistik setelah menambah index.
Menguji dengan data kecil lalu menganggap hasilnya berlaku di produksi.

Penutup

Audit moral query lambat mengajarkan bahwa keputusan menambah index harus dibenarkan oleh bukti, bukan refleks. Tambahkan index ketika query benar-benar diuntungkan oleh selectivity, cardinality, dan pola akses yang tepat. Tulis ulang query ketika bentuk SQL menghambat optimizer. Batasi offset besar ketika masalah utamanya ada pada strategi pagination. Dan jika index baru memang diperlukan, terima trade-off-nya secara sadar: write amplification, storage, maintenance, dan risiko bloat.

Optimasi yang dewasa bukan yang selalu paling cepat di satu benchmark, tetapi yang paling bertanggung jawab terhadap sistem secara keseluruhan.

Quick Search

shopping_cart Keranjang