Saat menghadapi optimasi query lambat di codebase yang belum sepenuhnya dipahami, langkah terbaik biasanya bukan refactor besar-besaran. Pendekatan yang lebih aman adalah mencari jalur request paling mahal, mengukur query yang benar-benar lambat, membaca rencana eksekusi, lalu menerapkan perbaikan kecil dengan risiko rendah seperti indeks yang tepat, perubahan pagination, atau batching.

Ini penting karena masalah performa database sering terlokalisasi. Anda tidak perlu memahami seluruh arsitektur untuk memperbaiki satu query yang menyebabkan latensi tinggi, timeout, atau beban CPU database melonjak. Yang dibutuhkan adalah disiplin observabilitas, analisis eksekusi query, dan validasi hasil sebelum dan sesudah perubahan.

Mulai dari gejala, bukan dari asumsi

Kesalahan umum saat menemukan aplikasi lambat adalah langsung membaca banyak file dan mencoba memahami semua alur bisnis. Pada codebase besar, itu mahal dan sering tidak perlu. Mulailah dari gejala yang terukur:

  • Endpoint API tertentu lambat secara konsisten
  • Halaman admin timeout saat dataset membesar
  • Job background memakan waktu jauh lebih lama dari biasanya
  • CPU atau I/O database tinggi pada jam tertentu
  • P95 atau P99 latensi naik, sementara rata-rata terlihat normal

Target pertama Anda adalah menjawab dua pertanyaan:

  1. Request atau job mana yang paling mahal?
  2. Query SQL mana yang paling banyak menghabiskan waktu di jalur tersebut?

Dengan cara ini, Anda menghindari refactor yang tidak perlu dan fokus pada bottleneck nyata.

Identifikasi endpoint dan query yang paling mahal

Gunakan APM jika tersedia

Jika sistem sudah memakai APM, manfaatkan data yang ada untuk mengurutkan endpoint berdasarkan durasi total, frekuensi, atau kontribusi ke error/timeout. Cari transaksi dengan pola berikut:

  • Durasi tinggi dan trafik tinggi
  • Durasi tinggi meski trafik rendah
  • Lonjakan latensi saat volume data meningkat
  • Pola N+1 atau query yang dieksekusi berulang dalam satu request

Tujuan tahap ini bukan memahami semua trace, tetapi menemukan satu jalur yang cukup mahal dan cukup sering terjadi sehingga layak dioptimasi.

Jika tidak ada APM, aktifkan slow query log

Slow query log membantu ketika Anda belum tahu query mana yang bermasalah. Atur ambang waktu yang masuk akal untuk lingkungan Anda, lalu kumpulkan sampel query yang lambat. Di tahap awal, lebih berguna melihat query nyata daripada menebak dari kode.

Catatan: jangan langsung menilai query hanya dari teks SQL. Query yang tampak sederhana bisa mahal karena data besar, statistik usang, sort di memori, atau join dengan cardinality yang buruk.

Korelasikan query dengan endpoint

Ini langkah yang sering terlewat. Anda perlu tahu query lambat tersebut berasal dari endpoint atau job mana. Caranya bisa melalui:

  • Log aplikasi yang menyertakan request id atau trace id
  • Instrumentation pada layer database
  • Log SQL yang menyimpan parameter dan konteks pemanggil
  • Sampling manual pada endpoint yang dicurigai

Tanpa korelasi ini, Anda bisa mengoptimasi query yang jarang dipakai, sementara bottleneck utama tetap tidak tersentuh.

Baca EXPLAIN untuk menemukan bottleneck nyata

Setelah query target ditemukan, langkah berikutnya adalah membaca EXPLAIN atau rencana eksekusi yang setara. Fokus utamanya bukan menghafal semua istilah engine database, tetapi memahami bagaimana database memutuskan mengambil data.

Hal yang perlu dicari di EXPLAIN

  • Full table scan pada tabel besar
  • Join yang membaca terlalu banyak row
  • Sort mahal karena tidak didukung indeks
  • Filter yang diterapkan terlambat
  • Temporary table atau operasi materialisasi besar
  • Perbedaan jauh antara row yang diperkirakan dan row aktual, jika engine menampilkan keduanya

Secara praktis, Anda ingin tahu: apakah query membaca jauh lebih banyak data daripada yang akhirnya dikembalikan?

Mengapa cardinality penting

Cardinality adalah seberapa selektif suatu kolom atau kombinasi kolom. Jika Anda memfilter berdasarkan kolom dengan sedikit variasi nilai, indeks tunggal di kolom itu mungkin tidak efektif. Sebaliknya, indeks komposit yang mengikuti pola filter dan sort bisa jauh lebih berguna.

Contoh masalah umum:

  • Memfilter status = 'active' pada tabel besar, padahal hampir semua baris aktif
  • Mengurutkan berdasarkan created_at setelah filter di beberapa kolom lain
  • Join ke tabel besar dengan foreign key yang belum terindeks dengan baik

Jika cardinality buruk, database mungkin tetap membaca banyak row meski indeks ada.

Studi kasus: memperbaiki query tanpa refactor besar

Misalkan endpoint admin menampilkan daftar pesanan terbaru untuk satu tenant, hanya yang belum diarsip, dengan pagination. Query awalnya seperti ini:

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND archived = false
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Gejalanya:

  • Masih cepat di data kecil
  • Menjadi lambat saat jumlah pesanan tenant bertambah
  • Timeout terjadi pada halaman pagination yang dalam

Apa masalahnya?

Ada dua kandidat utama:

  1. OFFSET besar membuat database tetap harus melangkahi banyak row sebelum mengembalikan 50 hasil
  2. Indeks tidak sesuai dengan pola WHERE tenant_id, archived, dan ORDER BY created_at

Perbaikan berisiko rendah 1: indeks komposit

Jika pola query ini penting dan sering dipakai, indeks komposit dapat membantu database menemukan subset row yang relevan sekaligus menjaga urutan pencarian lebih efisien.

-- Contoh umum, sesuaikan sintaks dengan database Anda
CREATE INDEX idx_orders_tenant_archived_created_at
ON orders (tenant_id, archived, created_at DESC);

Mengapa ini membantu:

  • Database bisa menyaring berdasarkan tenant_id dan archived lebih awal
  • Urutan created_at lebih dekat dengan kebutuhan ORDER BY
  • Mengurangi kebutuhan sort terpisah pada subset data besar

Trade-off:

  • Indeks menambah biaya write pada INSERT dan UPDATE
  • Ukuran storage bertambah
  • Urutan kolom indeks harus mengikuti pola akses paling penting, bukan semua query sekaligus

Perbaikan berisiko rendah 2: ganti OFFSET besar dengan keyset pagination

Jika pengguna selalu menelusuri data secara berurutan, keyset pagination biasanya lebih stabil daripada OFFSET besar.

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND archived = false
  AND created_at < '2024-12-01 10:15:00'
ORDER BY created_at DESC
LIMIT 50;

Dengan pendekatan ini, database tidak perlu melewati 10.000 row hanya untuk sampai ke halaman berikutnya. Untuk hasil yang stabil, sering kali perlu menambahkan tie-breaker seperti id jika banyak row memiliki timestamp sama.

Trade-off:

  • Lebih cepat untuk dataset besar
  • Tidak ideal jika UI membutuhkan lompat langsung ke halaman 237
  • Implementasi frontend dan API perlu token cursor atau pasangan kolom acuan

Perbaikan berisiko rendah 3: covering index bila akses kolom sempit

Jika query membaca sedikit kolom dan sangat sering dipanggil, covering index dapat mengurangi kebutuhan membaca tabel utama karena data yang diperlukan sudah tersedia di indeks, tergantung kemampuan engine database.

Namun, jangan menambahkan terlalu banyak kolom ke indeks tanpa alasan. Indeks akan membesar dan biaya write ikut naik. Pilih hanya kolom yang benar-benar membantu pola baca dominan.

Mencari bottleneck join, sort, dan filter

Join yang terlihat benar belum tentu murah

Contoh query laporan:

SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.tenant_id = 42
  AND o.created_at >= '2025-01-01'
  AND c.deleted_at IS NULL
ORDER BY o.created_at DESC
LIMIT 100;

Masalah yang mungkin muncul:

  • orders.customer_id tidak terindeks dengan baik untuk pola join yang dominan
  • Filter paling selektif tidak diterapkan di sisi tabel yang tepat
  • Query membaca terlalu banyak row orders sebelum limit benar-benar efektif

Pendekatan aman:

  1. Pastikan kolom join penting memiliki indeks yang relevan
  2. Lihat tabel mana yang paling besar dan paling selektif
  3. Periksa apakah sort terjadi setelah join besar, bukan sebelum subset mengecil

Sort mahal sering tersembunyi

Sort mahal biasanya muncul saat query mengurutkan hasil dari row yang sudah terlalu banyak. Bahkan jika hasil akhir hanya 100 row, database bisa saja harus mengumpulkan dan mengurutkan ribuan atau jutaan row lebih dulu.

Indikasi umum:

  • EXPLAIN menunjukkan operasi sort terpisah
  • Latensi naik tajam saat range tanggal diperlebar
  • Query cepat tanpa ORDER BY tetapi lambat saat diurutkan

Solusi paling aman biasanya bukan memaksa hint atau rewrite kompleks, melainkan memastikan pola filter dan urutan didukung indeks yang tepat.

Filter yang diterapkan terlambat

Query lambat juga bisa disebabkan filter yang secara logis benar, tetapi secara fisik diterapkan setelah terlalu banyak data dibaca. Ini umum terjadi pada:

  • Fungsi pada kolom di klausa WHERE
  • Pencarian dengan transformasi yang membuat indeks sulit dipakai
  • Kondisi OR yang membuat planner memilih jalur mahal

Contoh anti-pattern umum:

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

Sering kali lebih baik menggunakan range yang mempertahankan peluang pemakaian indeks:

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

Intinya: hindari memanipulasi kolom terindeks dengan cara yang menyulitkan planner.

Kapan tidak perlu refactor besar

Pada codebase yang belum dipahami sepenuhnya, refactor besar memiliki risiko tinggi:

  • Perubahan perilaku bisnis yang tidak disadari
  • Regresi di endpoint lain
  • Waktu investigasi membengkak
  • Sulit memvalidasi apakah performa benar-benar membaik

Anda sering tidak perlu refactor besar jika:

  • Ada satu atau dua query dominan yang jelas dari metrik
  • Masalah bisa diselesaikan dengan indeks, pagination, batching, atau pengurangan row yang dibaca
  • Struktur query masih bisa dipahami dari log dan EXPLAIN
  • Tujuan Anda adalah menurunkan latensi atau beban DB, bukan merombak model domain

Refactor besar lebih masuk akal jika:

  • Masalah utamanya N+1 sistemik di banyak jalur
  • Akses data tersebar di banyak layer tanpa kontrol
  • Query dibangun dinamis secara tidak terprediksi
  • Performa buruk berasal dari desain data yang memang tidak cocok dengan pola akses

Prinsip praktis: jika perbaikan kecil yang terukur dapat menghilangkan 80% masalah, dahulukan itu. Pemahaman codebase yang belum lengkap bukan alasan untuk diam, tetapi juga bukan alasan untuk membongkar semuanya.

Batching dan pengurangan beban query

Tidak semua optimasi harus berupa indeks. Kadang masalahnya adalah terlalu banyak data diminta sekaligus atau terlalu banyak query kecil dieksekusi berurutan.

Gunakan batching untuk pekerjaan massal

Contoh umum adalah job yang mengambil jutaan row lalu memproses semuanya dalam satu transaksi atau satu query besar. Batching membantu mengurangi tekanan memori, lock duration, dan lonjakan I/O.

-- Ambil per batch berdasarkan primary key atau kolom yang stabil
SELECT id, tenant_id, status
FROM orders
WHERE id > :last_id
ORDER BY id
LIMIT 1000;

Mengapa pendekatan ini aman:

  • Ukuran kerja per batch terkontrol
  • Lebih mudah di-retry bila gagal
  • Lebih stabil saat data terus bertambah

Trade-off:

  • Perlu mekanisme checkpoint atau cursor
  • Harus hati-hati terhadap perubahan data di tengah proses
  • Tidak selalu cocok untuk laporan yang butuh snapshot konsisten penuh

Kurangi kolom yang dibaca

SELECT * sering memperparah biaya I/O, terutama pada tabel lebar atau join banyak tabel. Untuk endpoint yang sensitif terhadap latensi, ambil hanya kolom yang benar-benar dipakai. Ini juga meningkatkan peluang covering index berguna.

Validasi dampak dengan metrik before/after

Perubahan performa yang baik harus bisa dibuktikan. Jangan mengandalkan kesan “rasanya lebih cepat”. Sebelum deploy, catat baseline. Setelah deploy, bandingkan metrik yang sama.

Metrik yang layak dibandingkan

  • Durasi query target
  • P95 atau P99 endpoint terkait
  • Jumlah row yang dibaca vs row yang dikembalikan, jika tersedia
  • CPU database
  • I/O read atau buffer hit yang relevan
  • Frekuensi timeout atau lock wait

Jika memungkinkan, ambil contoh query plan sebelum dan sesudah. Dengan begitu, Anda tidak hanya tahu hasilnya membaik, tetapi juga tahu mengapa membaik.

Hal yang perlu diwaspadai setelah menambah indeks

  • Write latency meningkat pada tabel yang sangat aktif
  • Storage indeks bertambah signifikan
  • Planner belum memilih indeks baru karena statistik belum akurat
  • Query lain justru berubah rencana eksekusinya

Karena itu, selalu pantau dampak pascadeploy, terutama pada tabel dengan trafik tulis tinggi.

Checklist investigasi untuk data yang terus tumbuh

Berikut checklist praktis saat query mulai melambat seiring pertumbuhan data:

  1. Identifikasi endpoint, job, atau halaman yang paling mahal dari metrik produksi
  2. Kumpulkan query nyata dari slow query log atau APM, bukan dugaan dari pembacaan kode
  3. Korelasikan query dengan jalur request yang memicunya
  4. Jalankan EXPLAIN dan cari full scan, join mahal, sort besar, atau filter terlambat
  5. Bandingkan row yang dibaca dengan row yang dikembalikan
  6. Periksa cardinality kolom filter dan urutan kolom dalam indeks
  7. Pastikan kolom join penting diindeks sesuai pola akses
  8. Evaluasi apakah OFFSET besar bisa diganti keyset pagination
  9. Kurangi kolom hasil jika endpoint hanya butuh subset data
  10. Pertimbangkan batching untuk proses massal atau backfill
  11. Ukur before/after dengan metrik yang sama
  12. Pantau efek samping terhadap write, storage, dan query lain

Kesalahan umum yang sering membuang waktu

  • Mulai dari ORM code sebelum melihat query aktual yang lambat
  • Menambah indeks sembarangan tanpa memahami pola filter dan sort
  • Mengoptimasi query yang jarang dipanggil karena terlihat buruk di kode
  • Mengandalkan rata-rata dan mengabaikan P95/P99
  • Menguji di dataset kecil lalu menyimpulkan aman untuk produksi
  • Mengubah banyak hal sekaligus sehingga sulit tahu perubahan mana yang efektif

Penutup

Optimasi query lambat di codebase yang belum sepenuhnya dipahami tidak harus dimulai dari pemahaman total terhadap seluruh sistem. Pendekatan yang lebih efektif adalah bekerja dari luar ke dalam: temukan endpoint paling mahal, kumpulkan query nyata, baca EXPLAIN, pahami bottleneck join/sort/filter, cek cardinality, lalu pilih perbaikan kecil yang risikonya rendah.

Dalam banyak kasus, indeks komposit yang tepat, covering index yang selektif, penghindaran OFFSET besar, atau batching sudah cukup untuk menurunkan latensi secara signifikan tanpa refactor besar. Yang membedakan optimasi yang berguna dari perubahan spekulatif adalah disiplin pengukuran: selalu validasi with before/after metrics dan ulangi hanya pada bottleneck berikutnya.