Audit Query Lambat Saat Trafik Naik: Index, COUNT, dan Pagination

Audit query lambat saat trafik naik biasanya dimulai dari gejala yang sangat biasa: endpoint list makin lambat, CPU database naik, p95 latency memburuk, dan tim mulai curiga bahwa keputusan lama soal index, COUNT(*), atau pagination sudah tidak cocok lagi dengan skala data sekarang.

Masalahnya jarang ada pada satu query saja. Sering kali yang terlihat lambat di level API sebenarnya kombinasi dari beberapa hal: filter tidak cocok dengan index, COUNT(*) ikut menghitung dataset besar di setiap request, OFFSET makin mahal saat halaman bertambah, dan query plan berubah karena distribusi data tidak lagi sama seperti saat fitur pertama kali dibuat. Artikel ini membahas cara mengauditnya secara teknis dan jujur, tanpa asumsi bahwa satu solusi cocok untuk semua kasus.

Gejala nyata yang biasanya muncul lebih dulu

Sebelum bicara optimasi, penting untuk mengenali pola masalah yang sering muncul ketika produk mulai ramai dipakai.

1. Endpoint list mendadak melambat

Contoh umum: endpoint seperti GET /orders, GET /transactions, atau halaman admin dengan banyak filter. Saat data masih sedikit, query seperti ini terasa normal:

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

Begitu baris bertambah menjadi jutaan, kombinasi WHERE, ORDER BY, dan OFFSET bisa menjadi mahal. Apalagi jika endpoint juga menjalankan query terpisah untuk total count.

2. COUNT(*) ikut menjadi bottleneck

Banyak API pagination mengembalikan metadata seperti total data, total halaman, atau jumlah hasil filter. Itu berarti setiap request list bukan hanya mengambil 50 baris, tetapi juga menjalankan:

SELECT COUNT(*)
FROM orders
WHERE tenant_id = 42
  AND status = 'paid';

Jika filter mengenai banyak baris dan index tidak mendukung, query ini bisa lebih mahal daripada query data utamanya. Masalah bertambah parah jika count dipanggil berulang untuk dashboard, polling, atau halaman yang sering di-refresh.

3. OFFSET makin besar, makin lambat

LIMIT 50 OFFSET 10000 terlihat sederhana, tetapi database tetap perlu melewati banyak baris sebelum sampai ke hasil yang diminta. Ini bukan sekadar masalah transfer data; biaya terbesar sering ada pada proses scan, sort, atau bookmark lookup ke banyak baris yang akhirnya dibuang.

4. Index “ada”, tapi tidak efektif

Kalimat “kan kolomnya sudah di-index” sering menyesatkan. Index tunggal pada status belum tentu berguna untuk query yang memfilter tenant_id, status, lalu mengurutkan berdasarkan created_at. Index yang salah urutan, terlalu lebar, atau tidak sesuai pola akses bisa membuat optimizer tetap memilih scan yang mahal.

Langkah audit query lambat saat trafik naik

Tujuan audit bukan langsung menambah index sebanyak mungkin, tetapi memahami query mana yang mahal, kenapa mahal, dan apakah optimasi yang dipilih sesuai pola akses nyata.

1. Mulai dari query paling sering dan paling mahal

Prioritaskan berdasarkan dampak, bukan intuisi. Ambil data dari log query lambat, APM, database monitoring, atau telemetry aplikasi. Catat setidaknya:

SQL yang dijalankan
frekuensi eksekusi
latency p95 atau p99
jumlah row yang dibaca vs row yang dikembalikan
endpoint atau job yang memicu query
pola parameter yang sering muncul

Query yang berjalan 5 ms tetapi dipanggil 20.000 kali per menit bisa lebih penting daripada query 500 ms yang jarang terjadi.

2. Pisahkan masalah aplikasi dan masalah database

Pastikan Anda tidak sedang salah mendiagnosis. Endpoint lambat bisa disebabkan oleh:

N+1 query di level ORM
koneksi database yang menunggu lama di pool
payload terlalu besar
serialisasi JSON lambat
lock atau blocking transaction

Audit query tetap relevan, tetapi hasilnya akan bias jika bottleneck utama ada di tempat lain.

3. Reproduksi dengan parameter nyata

Jangan menguji dengan data kecil atau filter yang terlalu ideal. Query plan bisa berubah total untuk tenant besar, rentang tanggal lebar, atau status yang sangat dominan. Gunakan contoh parameter yang benar-benar mewakili traffic produksi.

4. Jalankan EXPLAIN pada query utama dan query COUNT

EXPLAIN membantu melihat bagaimana optimizer berniat mengeksekusi query. Di banyak database, Anda juga bisa melihat apakah query memakai index, melakukan scan besar, atau perlu sort tambahan.

Contoh query list:

EXPLAIN
SELECT id, user_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

Contoh query count:

EXPLAIN
SELECT COUNT(*)
FROM orders
WHERE tenant_id = 42
  AND status = 'paid';

Cara membaca EXPLAIN tanpa terjebak detail yang salah

Format output EXPLAIN berbeda antar database, tetapi ada beberapa pertanyaan universal yang perlu dijawab.

Apakah database melakukan full scan?

Jika planner membaca hampir seluruh tabel atau bagian besar index untuk query yang seharusnya sempit, itu sinyal bahwa index tidak cocok, statistik tidak akurat, atau filter memang kurang selektif.

Apakah jumlah row yang diperkirakan sangat besar?

Perhatikan estimasi row yang dibaca. Jika query hanya mengembalikan 50 hasil tetapi harus memeriksa ratusan ribu atau jutaan row, ada peluang optimasi yang besar.

Apakah ada sort terpisah?

Jika hasil perlu di-sort setelah filtering, database mungkin harus mengumpulkan lebih banyak row sebelum bisa mengambil 50 teratas. Idealnya, urutan index membantu memenuhi ORDER BY sehingga pekerjaan sort berkurang atau hilang.

Apakah index yang dipakai sesuai urutan filter?

Misalnya query:

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

Index seperti ini sering lebih relevan daripada beberapa index tunggal terpisah:

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

Kenapa? Karena query memfilter berdasarkan tenant_id dan status, lalu membutuhkan urutan created_at dan id. Composite index yang mengikuti pola akses dapat membantu database menemukan subset data yang tepat sekaligus membaca dalam urutan yang dibutuhkan.

Catatan: urutan kolom pada composite index sangat penting. Index (status, tenant_id, created_at) belum tentu seefektif (tenant_id, status, created_at) jika hampir semua query selalu memulai dari tenant_id.

Memilih composite index yang tepat

Menambah index adalah keputusan desain, bukan reaksi panik. Setiap index mempercepat sebagian query tetapi menambah biaya write, storage, dan maintenance.

Prinsip praktis

Mulai dari pola WHERE yang paling umum.
Masukkan kolom untuk ORDER BY jika urutan hasil konsisten.
Hindari membuat banyak index yang tumpang tindih tanpa alasan jelas.
Perhatikan apakah query mengambil banyak kolom di luar index; kadang manfaat index turun jika database tetap harus membaca table row satu per satu dalam jumlah besar.

Contoh pola yang sering salah

Misalnya ada index tunggal berikut:

CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);

Ini terlihat aman, tetapi untuk query list yang butuh kombinasi filter dan urutan, tiga index tunggal belum tentu lebih baik daripada satu composite index yang benar. Optimizer tetap bisa memilih plan yang mahal karena harus menggabungkan hasil atau melakukan sort tambahan.

Trade-off composite index

Pro: jauh lebih cocok untuk query kritis dengan pola filter yang stabil.
Kontra: menambah biaya INSERT/UPDATE, memperbesar ukuran index, dan tidak fleksibel jika pola query sering berubah.

Karena itu, audit harus fokus pada endpoint nyata yang paling penting, bukan mencoba mengoptimalkan semua query sekaligus.

Kenapa COUNT(*) sering mahal

Banyak tim baru sadar bahwa COUNT(*) bukan operasi gratis ketika traffic naik. Secara logis, database harus menghitung jumlah baris yang memenuhi filter. Jika filter menyentuh banyak data, pekerjaan ini bisa signifikan.

Kapan COUNT(*) menjadi masalah

dataset besar dan terus bertumbuh
filter sering dipakai pada halaman list populer
query count dijalankan di setiap request
count perlu akurat sampai baris terakhir
index tidak mendukung filter dengan baik

Pilihan penanganan yang realistis

Hilangkan total count jika tidak benar-benar dibutuhkan. Banyak UI sebenarnya hanya butuh tombol next/previous.
Hitung lebih jarang. Misalnya hanya di halaman pertama, atau saat filter berubah.
Cache hasil count. Cocok untuk filter yang sering berulang dan toleran terhadap sedikit stale data.
Simpan counter teragregasi. Cocok jika kebutuhan count sangat sering dan dimensi agregasinya terbatas.
Gunakan pendekatan approximate jika bisnis tidak menuntut angka presisi untuk setiap request.

Keputusan terbaik bergantung pada kebutuhan produk. Jika UI tidak pernah memakai jumlah total untuk keputusan penting, memaksa count akurat di setiap request biasanya pemborosan.

Contoh memisahkan query data dan query count

-- query data utama
SELECT id, user_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- query count hanya jika diperlukan
SELECT COUNT(*)
FROM orders
WHERE tenant_id = 42
  AND status = 'paid';

Praktik ini terlihat sederhana, tetapi penting untuk menilai apakah query kedua memang harus selalu ada.

Offset pagination vs keyset pagination

Bagian ini sering menjadi sumber salah paham. Offset pagination mudah dipakai, tetapi biayanya tumbuh seiring nomor halaman. Keyset pagination lebih stabil untuk dataset besar, tetapi ada trade-off pada UX dan implementasi.

Offset pagination

SELECT id, user_id, status, created_at
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

Kelebihan:

mudah dipahami
mudah implementasi di banyak ORM
cocok untuk halaman kecil atau dataset terbatas
mendukung lompat langsung ke halaman tertentu

Kekurangan:

makin mahal saat offset membesar
berisiko hasil tidak konsisten saat data berubah di tengah navigasi
sering tetap butuh count untuk total halaman

Keyset pagination

Alih-alih bilang “ambil halaman ke-101”, keyset mengatakan “ambil 50 data setelah baris terakhir yang tadi saya terima”.

SELECT id, user_id, status, created_at
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) < ('2025-01-10 12:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kenapa ini lebih cepat? Karena database tidak perlu membuang ribuan row lebih dulu seperti pada offset besar. Dengan index yang sesuai, query bisa langsung melanjutkan dari posisi terakhir.

Kelebihan:

lebih stabil untuk data besar
lebih cocok untuk infinite scroll atau feed
mengurangi biaya offset besar

Kekurangan:

lebih sulit jika UI harus lompat ke halaman tertentu
perlu urutan yang stabil dan unik
cursor harus dibentuk dengan benar, biasanya dari kombinasi kolom sort

Kapan memilih masing-masing

Pilih offset pagination jika dataset masih kecil, admin tool butuh lompat halaman, atau biaya query masih terbukti aman.
Pilih keyset pagination jika endpoint list sangat sering dipakai, data terus tumbuh, dan performa halaman belakang mulai memburuk.

Prinsip penting: jika urutan hanya berdasarkan created_at, tambahkan tie-breaker seperti id agar urutan stabil. Tanpa itu, hasil antar halaman bisa duplikat atau terlewat.

Kapan index saja tidak cukup

Ada fase ketika query sudah dibenahi, index sudah lebih cocok, tetapi beban tetap tinggi karena pola akses produk memang berat. Di titik ini, Anda perlu mempertimbangkan perubahan desain data.

1. Denormalisasi terkontrol

Jika endpoint selalu membutuhkan join mahal atau agregasi yang sama, menyimpan sebagian data turunan bisa lebih murah daripada menghitung ulang terus-menerus.

Contoh:

menyimpan last_order_at pada tabel customer
menyimpan counter per status per tenant
membuat tabel ringkasan harian untuk dashboard

Trade-off: write path menjadi lebih kompleks, perlu mekanisme sinkronisasi, dan ada risiko data inkonsisten jika update gagal.

2. Cache

Cache cocok untuk query yang mahal tetapi hasilnya sering sama dalam periode singkat. Misalnya daftar teratas, count filter populer, atau hasil dashboard.

Namun cache bukan alasan untuk membiarkan query dasar tetap buruk. Jika cache miss terlalu sering atau invalidasi tidak disiplin, masalah hanya dipindahkan.

Pertimbangkan cache jika:

hasil dibaca jauh lebih sering daripada ditulis
stale data beberapa detik atau menit masih dapat diterima
pola query repetitif
invalidasi bisa dirancang dengan jelas

3. Precomputation atau background refresh

Untuk agregasi mahal, kadang lebih masuk akal menghitung di background lalu menyajikan hasil yang sudah siap pakai. Ini umum untuk dashboard, laporan, atau statistik yang tidak harus real-time sampai detik terakhir.

Checklist investigasi yang bisa langsung dipakai

Identifikasi endpoint dengan latency tertinggi saat trafik naik.
Ambil SQL aktual beserta parameter nyatanya.
Ukur frekuensi query, bukan hanya durasi satu kali eksekusi.
Pisahkan query data, query count, dan query turunan lain.
Jalankan EXPLAIN untuk masing-masing query.
Lihat apakah terjadi scan besar, sort mahal, atau pembacaan row berlebih.
Cek apakah urutan kolom pada index sesuai pola WHERE dan ORDER BY.
Evaluasi apakah COUNT(*) benar-benar perlu di setiap request.
Uji keyset pagination jika offset mulai besar.
Bandingkan biaya read improvement dengan biaya write akibat index baru.
Validasi hasil pada tenant besar atau data skewed, bukan hanya data kecil.
Pantau ulang setelah deploy; query plan yang terlihat baik di staging belum tentu sama di produksi.

Anti-pattern yang sering muncul

Menambah index satu per satu tanpa memahami pola query

Ini membuat tabel penuh index yang saling tumpang tindih, write melambat, tetapi query utama belum tentu membaik.

Mengandalkan ORM default pagination tanpa audit

Banyak ORM memudahkan pagination, tetapi sering menyembunyikan biaya OFFSET besar dan query count otomatis.

Selalu mengembalikan total pages

Secara UX terlihat rapi, tetapi secara performa bisa mahal. Tanyakan apakah produk benar-benar membutuhkan angka itu.

Mengurutkan hasil tanpa tie-breaker yang stabil

Misalnya hanya ORDER BY created_at DESC. Saat banyak row punya timestamp sama, pagination bisa tidak konsisten.

Mengoptimasi query yang jarang dipakai, mengabaikan query sedang tetapi sangat sering

Performa sistem sering lebih dipengaruhi volume total kerja daripada satu query spektakuler yang jarang terjadi.

Menggunakan cache untuk menutupi query yang secara struktural salah

Cache membantu, tetapi jika desain query dasarnya tidak sehat, biaya akan kembali muncul saat cache miss, rebuild, atau invalidasi.

Panduan keputusan yang jujur

Jika tim sedang mempertanyakan keputusan teknis lama, itu normal. Banyak desain yang masuk akal saat data masih puluhan ribu baris menjadi mahal saat sudah jutaan. Audit yang baik bukan mencari siapa yang salah, tetapi memastikan asumsi lama diuji ulang terhadap beban sekarang.

Jika masalah utama ada pada scan dan sort, periksa composite index.
Jika list endpoint lambat karena metadata total, audit kebutuhan COUNT(*).
Jika halaman belakang sangat lambat, uji keyset pagination.
Jika query sudah cukup optimal tetapi tetap mahal secara bisnis, pertimbangkan cache, precomputation, atau denormalisasi.

Pada akhirnya, audit query lambat saat trafik naik bukan pekerjaan sekali selesai. Query yang sehat hari ini bisa menjadi bottleneck enam bulan lagi ketika distribusi data, pola akses, atau kebutuhan produk berubah. Karena itu, yang paling berharga bukan hanya index baru atau query yang lebih cepat, tetapi disiplin untuk mengukur, membaca plan, dan meninjau ulang asumsi sebelum bottleneck berubah menjadi insiden.

Quick Search

shopping_cart Keranjang