Audit Bottleneck SQL: Belajar dari Arsitektur Backend Bertahap

Audit bottleneck SQL paling efektif dilakukan secara bertahap, bukan langsung menambah index di mana-mana atau menebak-nebak penyebab lambat. Pada kasus endpoint daftar besar yang makin lambat saat data tumbuh, pola yang sering terjadi adalah query awal masih terasa cepat di data kecil, lalu memburuk karena scan terlalu banyak baris, sort mahal, dan pagination berbasis OFFSET makin berat.

Pendekatan bertahap ini mirip cara membangun backend compiler per tahap: selesaikan satu lapisan, validasi, lalu lanjut ke bottleneck berikutnya. Untuk SQL, urutannya sederhana: ukur gejalanya, identifikasi query lambat, baca rencana eksekusi, cocokkan dengan pola filter/sort di aplikasi, pilih index yang tepat, ganti pagination jika perlu, lalu verifikasi hasilnya lewat metrik yang relevan.

Mengapa pendekatan bertahap lebih aman

Masalah performa query sering terlihat seperti satu gejala, padahal sumbernya bisa berbeda:

Filter tidak selektif, sehingga database tetap membaca terlalu banyak baris.
Urutan index tidak cocok dengan kombinasi WHERE dan ORDER BY.
OFFSET besar memaksa database melompati banyak baris sebelum mengembalikan hasil.
Sort di luar index membuat mesin perlu operasi tambahan yang mahal.
Query count dan data retrieval tercampur dalam satu endpoint dengan beban berbeda.

Jika langsung menambah index tanpa membaca pola akses data, hasilnya bisa salah arah: write jadi lebih mahal, storage membesar, tapi latency endpoint tidak banyak berubah. Karena itu, audit sebaiknya mengikuti urutan diagnosis yang jelas.

Kasus nyata: endpoint daftar besar makin lambat saat data tumbuh

Misalkan ada endpoint backend untuk daftar pesanan:

GET /orders?tenant_id=42&status=paid&sort=created_at_desc&page=120

Pada awalnya tabel masih kecil dan endpoint terasa cepat. Setelah data bertambah, keluhan mulai muncul:

Halaman awal masih relatif cepat.
Halaman belakang jauh lebih lambat.
Beban CPU database naik saat jam sibuk.
Query timeout mulai muncul di tenant besar.

Skema sederhananya seperti berikut:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

Query awal yang sering ditemukan di aplikasi:

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

Secara bisnis query ini masuk akal. Secara performa, ada dua titik rawan:

Database harus mencari semua baris yang cocok dengan tenant_id dan status.
Untuk OFFSET besar, database tetap perlu memproses dan melewati ribuan baris sebelum mengembalikan 50 data.

Tahap 1: ukur dulu, jangan menebak

Aktifkan pengamatan query lambat

Langkah pertama dalam audit bottleneck SQL adalah memastikan query lambat benar-benar terlihat. Gunakan slow query log atau fitur observabilitas APM/query monitoring yang tersedia di stack Anda. Tujuannya bukan sekadar tahu “query ini lambat”, tetapi juga menangkap:

teks query final,
parameter penting,
durasi eksekusi,
frekuensi kemunculan,
rows examined / scanned jika tersedia,
konteks endpoint atau tenant.

Yang perlu dihindari adalah menganalisis query contoh yang tidak representatif. Query lambat biasanya terkait kombinasi parameter tertentu: tenant besar, status populer, rentang tanggal lebar, atau page tinggi.

Pilih query yang benar-benar berdampak

Prioritaskan query berdasarkan kombinasi:

latency tinggi,
frekuensi tinggi,
kontribusi ke endpoint penting.

Query yang jalan 2 detik tapi hanya sekali sehari belum tentu lebih prioritas daripada query 200 ms yang dipanggil ribuan kali per menit.

Catatan: latency API tidak selalu identik dengan waktu query. Pastikan Anda memisahkan waktu di database dari overhead lain seperti serialisasi JSON, N+1 query, dan panggilan ke service lain.

Tahap 2: baca EXPLAIN untuk melihat pola bottleneck

Setelah query target ditemukan, jalankan EXPLAIN pada query yang sama atau sedekat mungkin dengan kondisi produksi.

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

Hal yang perlu diperhatikan dari hasil EXPLAIN secara umum:

Index apa yang dipakai atau justru tidak dipakai.
Perkiraan jumlah baris yang harus dibaca.
Apakah ada sort tambahan di luar urutan index.
Apakah terjadi full table scan atau range scan yang masih terlalu lebar.

Nama kolom output EXPLAIN bisa berbeda antar database, tetapi prinsip membacanya sama: lihat seberapa banyak data yang perlu disentuh sebelum hasil akhir didapat.

Tanda-tanda yang sering muncul

Rows examined jauh lebih besar daripada rows returned.
Sort tidak memanfaatkan index, sehingga database harus menyusun ulang hasil.
Filter utama ada, tetapi urutan kolom index tidak membantu ORDER BY.
OFFSET tinggi tetap membuat biaya eksekusi membesar walau index sudah lumayan baik.

Tahap 3: audit filter dan sort sebelum membuat index

Index yang baik mengikuti pola akses data nyata. Karena itu, sebelum menentukan composite index, audit dulu bentuk query-nya.

Pertanyaan audit yang perlu dijawab

Kolom mana yang hampir selalu muncul di WHERE?
Kolom mana yang paling selektif?
Apakah query diurutkan dengan ORDER BY created_at DESC, id DESC, atau kombinasi lain?
Apakah ada filter rentang tanggal yang mengubah strategi index?
Apakah endpoint selalu per-tenant?

Pada contoh kita, pola query inti adalah:

filter: tenant_id = ?
filter: status = ?
sort: created_at DESC
pagination: LIMIT ... OFFSET ...

Maka index tunggal pada tenant_id saja biasanya belum cukup. Database mungkin masih harus membaca banyak baris untuk tenant tersebut, lalu memfilter status, lalu mengurutkan created_at.

Memilih composite index yang sesuai

Untuk pola di atas, kandidat yang masuk akal adalah composite index yang mengikuti filter lalu sort:

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

Mengapa urutan ini sering lebih cocok?

tenant_id membatasi ruang data per tenant.
status mempersempit lagi hasil dalam tenant.
created_at membantu pengurutan hasil tanpa sort tambahan yang mahal.

Namun, tidak ada satu index yang selalu benar untuk semua query. Jika aplikasi juga sering mencari:

WHERE tenant_id = ?
ORDER BY created_at DESC

maka Anda perlu mengevaluasi apakah index di atas masih cukup, atau perlu index tambahan yang lebih cocok untuk pola kedua. Di sinilah trade-off mulai muncul.

Kesalahan umum saat memilih composite index

Mengikuti intuisi, bukan pola query nyata.
Membuat terlalu banyak index mirip yang saling tumpang tindih.
Hanya fokus pada WHERE dan melupakan ORDER BY.
Mengabaikan biaya write: setiap insert/update juga harus memelihara index.

Tahap 4: bandingkan query sebelum dan sesudah index

Setelah menambahkan index yang relevan, ulangi EXPLAIN dan bandingkan metrik dasarnya. Target utamanya bukan “index terpakai”, melainkan:

baris yang diperiksa menurun,
sort tambahan berkurang atau hilang,
latency aktual endpoint turun.

Query-nya mungkin tetap sama:

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

Tetapi jika hasil EXPLAIN menunjukkan pembacaan data jauh lebih terarah, Anda sudah memperbaiki lapisan pertama bottleneck.

Meski begitu, ada batasnya: OFFSET besar tetap mahal. Composite index memperbaiki cara mencari dan mengurutkan, tetapi tidak menghilangkan biaya melompati ribuan baris.

Tahap 5: hindari OFFSET besar dengan keyset pagination

Jika endpoint daftar harus melayani halaman jauh ke belakang, pagination berbasis OFFSET sering menjadi bottleneck utama. Solusi yang lebih stabil adalah keyset pagination, kadang disebut juga cursor-based pagination.

Mengapa OFFSET mahal

Untuk query seperti ini:

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

database tetap harus menemukan dan melewati 5.950 baris sebelum mengembalikan 50 baris berikutnya. Ini memburuk seiring page bertambah besar.

Pola keyset pagination

Gunakan kolom urutan yang stabil, idealnya unik atau dipasangkan dengan kolom unik. Misalnya urutan:

ORDER BY created_at DESC, id DESC

Halaman pertama:

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

Setelah itu, kirim cursor dari baris terakhir, misalnya created_at dan id:

SELECT id, customer_id, status, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Agar pola ini efisien, index juga harus selaras:

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

Kapan keyset pagination lebih tepat

Daftar diurutkan berdasarkan waktu atau ID yang stabil.
Pengguna lebih sering menelusuri “halaman berikutnya” daripada lompat ke nomor halaman spesifik.
Volume data besar dan performa harus konsisten di page dalam.

Trade-off keyset pagination

Kelebihan: performa lebih stabil, tidak memburuk drastis pada page besar.
Kekurangan: tidak natural untuk UI yang butuh lompat langsung ke halaman 120.
Perlu cursor di API dan logika klien yang sedikit berbeda.
Urutan harus deterministik; jika hanya pakai created_at dan nilainya duplikat, hasil bisa tidak stabil. Karena itu sering perlu pasangan id.

Tahap 6: validasi hasil dengan metrik, bukan asumsi

Optimasi dianggap berhasil jika perubahannya terlihat pada metrik yang benar. Minimal, bandingkan sebelum dan sesudah pada kondisi beban yang serupa.

Metrik yang perlu dicek

Latency endpoint: median dan tail latency jika tersedia.
Waktu eksekusi query: dari slow query log atau monitoring query.
Rows examined / rows scanned: apakah jumlah baris yang disentuh turun signifikan.
Frekuensi query lambat: apakah insiden timeout berkurang.
Beban write: apakah insert/update jadi lebih mahal setelah penambahan index.

Jangan hanya memvalidasi di laptop atau dataset kecil. Query yang cepat di data 10 ribu baris belum tentu sehat di data 10 juta baris. Jika salinan data produksi tidak tersedia, setidaknya gunakan data dengan distribusi yang realistis: tenant besar, status populer, dan page yang dalam.

Prinsip penting: index yang “dipakai” belum tentu berarti query sudah efisien. Fokus pada jumlah baris yang diperiksa dan konsistensi latency di skenario nyata.

Trade-off: biaya write vs performa read

Setiap index tambahan punya harga:

Insert lebih mahal karena index harus diperbarui.
Update pada kolom terindeks juga lebih berat.
Storage bertambah.
Maintenance lebih rumit jika terlalu banyak index mirip.

Karena itu, jangan mengoptimalkan semua kemungkinan query sekaligus. Fokus pada endpoint yang benar-benar kritikal. Jika endpoint daftar dibaca sangat sering dan menahan traffic utama aplikasi, trade-off write biasanya masuk akal. Sebaliknya, untuk tabel yang write-heavy, penambahan index harus lebih konservatif.

Contoh audit singkat sebelum dan sesudah

Sebelum

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

Gejala: halaman awal cepat, halaman belakang lambat.
Temuan EXPLAIN: pembacaan baris besar, sort tidak optimal, offset mahal.
Risiko: timeout pada tenant besar.

Sesudah tahap pertama

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

Hasil yang diharapkan: filter dan sort lebih efisien.
Batasan: page dalam masih bisa lambat karena OFFSET.

Sesudah tahap kedua

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

SELECT id, customer_id, status, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Hasil yang diharapkan: latency lebih stabil di semua halaman lanjutan.
Trade-off: API berubah dari nomor halaman ke cursor.

Kesalahan yang sering terjadi saat audit bottleneck SQL

Mengandalkan ORM log tanpa melihat query final yang benar-benar dieksekusi.
Tidak memeriksa parameter nyata; query untuk tenant kecil dan tenant besar bisa sangat berbeda dampaknya.
Mencampur masalah SQL dengan masalah aplikasi seperti N+1 query atau serialisasi berlebihan.
Menambah index setelah melihat satu query tanpa memeriksa dampak ke write path.
Berhenti setelah EXPLAIN terlihat lebih baik tanpa validasi latency aktual.

Checklist audit yang bisa dipakai tim backend

Identifikasi endpoint yang melambat saat data tumbuh.
Kumpulkan query nyata dari slow query log atau monitoring.
Kelompokkan berdasarkan pola: filter, sort, pagination, tenant, rentang waktu.
Jalankan EXPLAIN pada query yang paling berdampak.
Catat index yang dipakai dan estimasi jumlah baris yang diperiksa.
Audit kecocokan WHERE + ORDER BY terhadap index yang ada.
Pilih composite index berdasarkan pola akses dominan, bukan dugaan.
Uji ulang EXPLAIN setelah perubahan.
Evaluasi OFFSET besar; jika perlu, migrasikan ke keyset pagination.
Validasi metrik produksi: latency, rows examined, timeout, dan dampak write.
Dokumentasikan keputusan: query target, index baru, trade-off, dan alasan desain.

Penutup

Inti dari audit bottleneck SQL adalah disiplin bertahap: jangan langsung menyelesaikan semuanya dengan satu trik. Mulai dari observasi, pahami rencana eksekusi, selaraskan filter dan sort dengan composite index, lalu hilangkan bottleneck struktural seperti OFFSET besar dengan keyset pagination. Pendekatan ini lebih membumi, lebih mudah divalidasi, dan jauh lebih aman untuk sistem backend yang terus tumbuh.

Jika tim Anda sering menghadapi endpoint daftar yang memburuk saat volume data naik, workflow audit seperti ini layak dijadikan standar. Bukan karena semua query akan sama, tetapi karena pola berpikirnya konsisten: ukur, pahami, perbaiki satu lapisan, lalu verifikasi hasilnya.

Quick Search

shopping_cart Keranjang