Jangan Percaya ORM: Audit Query Lambat Saat Data Membesar

Jangan percaya ORM jika satu-satunya bukti Anda hanyalah aplikasi masih terasa cepat di data kecil. ORM membantu produktivitas, tetapi ia juga menyembunyikan detail SQL yang justru menentukan performa saat jumlah baris naik, distribusi data berubah, dan query yang dulu murah menjadi mahal.

Masalah utamanya bukan ORM itu sendiri, melainkan asumsi bahwa abstraksi tinggi otomatis menghasilkan query yang tetap efisien di produksi. Ini mirip dengan pelajaran dari tren "Don't trust large context windows": konteks besar terdengar meyakinkan, tetapi tanpa verifikasi nyata, Anda tidak tahu apakah hasilnya benar. Di database, abstraksi query yang rapi juga tidak cukup. Yang penting adalah metrik, execution plan, cardinality, dan pola akses data yang benar-benar terjadi.

Gejala Query Lambat Saat Data Membesar

Query yang sehat di staging sering memburuk perlahan, bukan langsung rusak total. Karena itu, gejala awal perlu dikenali sebelum Anda buru-buru menambah CPU atau cache.

Latensi naik seiring pertumbuhan data, padahal kode endpoint tidak berubah.
P95/P99 lebih buruk daripada rata-rata, tanda query sensitif terhadap nilai parameter tertentu.
Endpoint list lebih lambat daripada detail, biasanya terkait filter, sorting, join, dan pagination.
Beban database naik meski traffic relatif stabil, sering karena plan berubah atau index tidak lagi efektif.
Timeout hanya pada halaman tertentu, misalnya page besar dengan OFFSET tinggi.

Jika gejalanya seperti di atas, jangan mulai dari ORM layer. Mulailah dari SQL yang benar-benar dikirim ke database dan perilakunya di bawah data produksi atau data yang bentuk distribusinya mirip produksi.

Cara Audit Query Lambat yang Benar

1. Ambil SQL mentah dan parameter nyatanya

Banyak engineer berhenti di level ORM: melihat method chain, merasa query "sederhana", lalu berasumsi database akan mengurus sisanya. Ini keliru. Audit harus dimulai dari:

SQL final yang dieksekusi
Nilai parameter yang dipakai
Waktu eksekusi
Jumlah baris yang dikembalikan
Frekuensi query per endpoint atau job

Penting untuk menangkap parameter nyata, karena query yang sama bisa menghasilkan plan berbeda untuk nilai yang berbeda. Filter pada status langka dan status yang sangat umum bisa memiliki biaya eksekusi yang tidak sama.

2. Kelompokkan berdasarkan pola, bukan hanya durasi tunggal

Satu query 800 ms mungkin belum kritis jika jarang dipanggil. Sebaliknya, query 80 ms yang dipanggil ribuan kali per menit bisa lebih merusak sistem. Saat mengaudit, kelompokkan berdasarkan:

Query paling sering
Query dengan total waktu kumulatif tertinggi
Query dengan P95/P99 tertinggi
Query yang jumlah row scanned jauh lebih besar daripada row returned

Tujuannya adalah menemukan query yang mahal secara sistemik, bukan hanya yang terlihat buruk sekali waktu.

3. Jalankan EXPLAIN atau EXPLAIN ANALYZE

EXPLAIN menunjukkan rencana eksekusi. EXPLAIN ANALYZE menambahkan runtime aktual pada database yang mendukungnya. Dari sini Anda bisa melihat apakah database:

Melakukan full table scan
Memakai index yang salah
Melakukan sort mahal di memory atau disk
Menghasilkan terlalu banyak row di tahap awal lalu membuangnya belakangan
Mengeksekusi nested loop join pada himpunan data besar

Jangan membaca execution plan hanya untuk mencari kata "index used". Banyak query tetap lambat walau memakai index, jika index-nya salah urutan, selectivity-nya rendah, atau sorting/pagination tetap mahal.

Cardinality: Alasan Query yang Dulu Cepat Menjadi Lambat

Cardinality secara praktis adalah gambaran seberapa selektif suatu kolom atau kombinasi kolom. Database memilih plan berdasarkan estimasi jumlah baris yang lolos di tiap tahap. Jika estimasi ini buruk, optimizer bisa memilih plan yang salah.

Contoh masalah cardinality

Anggap ada query daftar order:

SELECT id, customer_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Di awal, saat tabel kecil, query ini terasa cepat. Namun saat data menjadi jutaan baris:

status = 'paid' mungkin tidak selektif karena sebagian besar order memang paid.
ORDER BY created_at DESC memaksa database menyusun kandidat besar sebelum mengambil hasil.
OFFSET 5000 membuat database melewati ribuan row dulu sebelum mengembalikan 50 row.

Jika Anda hanya melihat kode ORM seperti Order.where(status: 'paid').order(created_at: :desc).limit(50).offset(5000), semuanya terlihat aman. Tetapi SQL-nya memiliki pola yang memburuk saat data tumbuh.

Tanda cardinality atau estimasi bermasalah

Estimated rows sangat berbeda dari actual rows pada execution plan.
Optimizer memilih scan besar padahal filter seharusnya sempit, atau sebaliknya.
Performa berubah drastis untuk parameter yang berbeda.

Jika ini terjadi, solusi tidak selalu menambah index baru. Kadang statistik database perlu diperbarui, kadang query perlu ditulis ulang agar lebih mudah dioptimalkan, dan kadang urutan kolom pada composite index harus diubah.

Index yang Salah Arah: Ada Index, Tapi Tetap Lambat

Banyak sistem memiliki banyak index, tetapi query tetap lambat karena index dibuat berdasarkan intuisi, bukan pola akses. Ini yang disebut index salah arah: index ada, tetapi tidak cocok dengan kombinasi WHERE, ORDER BY, dan cara data diambil.

Kapan composite index diperlukan

Composite index layak dipertimbangkan ketika query konsisten memakai kombinasi filter dan sort yang sama, misalnya:

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

Untuk pola seperti ini, index tunggal pada status atau created_at saja sering tidak cukup. Database mungkin masih perlu memfilter banyak row lalu melakukan sort tambahan. Composite index lebih masuk akal jika:

Kolom filter dipakai bersama secara konsisten
Sort mengikuti pola tetap
Query mengambil sedikit row dari himpunan besar
Endpoint ini penting dan sering dipanggil

Secara umum, urutan kolom dalam composite index harus mengikuti pola akses query, bukan urutan yang terlihat rapi di model. Kolom dengan selektivitas baik dan predicate yang stabil sering lebih berguna di depan, tetapi keputusan akhirnya harus divalidasi dengan execution plan, bukan aturan hafalan.

Contoh sebelum dan sesudah

Sebelum, query memakai index yang kurang cocok:

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

Index yang tersedia:

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

Masalahnya, database mungkin:

Memakai index status, lalu memfilter tenant_id, lalu sort berdasarkan created_at
Atau memakai index created_at, lalu memeriksa banyak row sampai menemukan cukup row yang cocok

Sesudah, index diselaraskan dengan pola query:

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

Dengan index seperti ini, database punya peluang lebih baik untuk:

Mempersempit row berdasarkan tenant_id dan status
Mengambil row dalam urutan created_at DESC
Menghindari sort besar terpisah

Trade-off-nya jelas: setiap index tambahan menambah biaya write, storage, vacuum/maintenance, dan kompleksitas optimizer. Karena itu, jangan mengindeks setiap kombinasi kolom yang pernah muncul sekali.

Filter + Sort + Pagination: Sumber Masalah yang Sering Disamarkan ORM

Pola paling umum pada aplikasi backend adalah endpoint list: ada filter, sort, limit, dan kadang join ke tabel lain. Di ORM, ini tampak sederhana. Di SQL, ini bisa menjadi pekerjaan mahal.

Masalah OFFSET pagination

OFFSET pagination nyaman dipakai karena mudah diimplementasikan:

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

Masalahnya, database tetap harus melewati 10.000 row lebih dulu sebelum mengembalikan 50 row berikutnya. Semakin besar offset, semakin mahal query, walau hasil yang dikirim tetap sedikit.

Ini biasanya terlihat pada halaman admin, export parsial, atau API list dengan page number besar. ORM jarang memperingatkan bahwa pola ini menurun secara struktural saat dataset tumbuh.

Gunakan keyset pagination bila memungkinkan

Untuk alur navigasi maju atau mundur berdasarkan urutan stabil, keyset pagination biasanya lebih efisien:

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND created_at < '2025-01-15 10:30:00'
ORDER BY created_at DESC
LIMIT 50;

Jika ada kemungkinan nilai created_at tidak unik, gunakan tie-breaker seperti id:

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

Keuntungan keyset pagination:

Tidak perlu membuang row sebanyak OFFSET
Latensi lebih stabil di page dalam
Lebih cocok untuk feed, timeline, dan data yang terus bertambah

Kekurangannya:

Tidak cocok jika Anda benar-benar butuh lompat ke page nomor acak
Implementasi cursor lebih rumit daripada page number
Harus memakai urutan yang stabil dan dapat diindeks dengan baik

Bottleneck Join: Saat Masalah Bukan di Tabel Utama

Query lambat sering disalahkan pada tabel terbesar, padahal bottleneck sebenarnya ada pada join yang memperbanyak row atau memaksa akses berulang ke tabel lain.

Contoh join yang tampak aman

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.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

Ini tampak biasa. Tetapi masalah dapat muncul jika:

Filter utama tidak cukup sempit sehingga banyak row orders harus diproses dulu
Join key tidak terindeks dengan baik di sisi yang tepat
Database memilih join order yang tidak efisien
Query sebenarnya membawa join yang belum perlu untuk page pertama

Kapan rewrite query lebih penting daripada tambah index

Menambah index tidak selalu menyelesaikan akar masalah. Rewrite query lebih penting jika:

Query mengambil terlalu banyak row sebelum di-LIMIT
Join dilakukan sebelum subset data dipersempit
Kolom yang diambil terlalu banyak sehingga I/O besar
Anda memakai subquery atau fungsi yang membuat index sulit dipakai

Contoh rewrite yang sering membantu adalah mempersempit data di tabel utama terlebih dahulu, lalu baru join:

SELECT o.id, o.created_at, c.name
FROM (
  SELECT id, customer_id, created_at
  FROM orders
  WHERE tenant_id = 42
    AND status = 'paid'
  ORDER BY created_at DESC
  LIMIT 50
) o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC;

Mengapa ini bisa membantu?

Subset dari orders dibatasi lebih awal
Join ke customers hanya dilakukan untuk 50 row, bukan ribuan kandidat
Planner punya peluang lebih baik jika index pada tabel utama sesuai dengan filter dan sort

Tentu, hasil aktual tetap harus divalidasi dengan EXPLAIN atau EXPLAIN ANALYZE. Rewrite yang membantu di satu sistem belum tentu lebih baik di sistem lain.

Checklist Investigasi Query Lambat di Produksi

Berikut checklist praktis yang bisa dipakai engineer backend saat query ORM mulai melambat di data besar.

Ambil SQL final dari log, profiler, atau instrumentation aplikasi.
Catat parameter nyata, jangan hanya template query.
Ukur durasi, frekuensi, row returned, dan bila tersedia row scanned.
Jalankan EXPLAIN/ANALYZE pada data yang representatif.
Bandingkan estimated rows vs actual rows untuk mendeteksi masalah cardinality.
Periksa apakah ada sort mahal, terutama setelah filter yang tidak selektif.
Lihat pola pagination: apakah memakai OFFSET besar?
Audit join order dan join cardinality: apakah join terjadi terlalu awal?
Periksa index yang ada: apakah urutannya cocok dengan WHERE + ORDER BY?
Kurangi kolom yang di-select jika endpoint tidak butuh semua field.
Uji rewrite query bila index saja tidak mengubah plan secara berarti.
Nilai biaya write sebelum menambah banyak index baru.

Kesalahan Umum Saat Mengaudit Query dari ORM

1. Mengandalkan data lokal yang terlalu kecil

Query yang cepat pada 10 ribu row belum tentu sehat pada 50 juta row. Yang penting bukan hanya jumlah total, tetapi juga distribusi data: nilai status dominan, tenant besar, timestamp padat, dan sebagainya.

2. Menganggap "index dipakai" berarti masalah selesai

Index bisa dipakai tetapi tetap mahal. Misalnya, database tetap harus membaca banyak row dari index lalu melakukan lookup tambahan atau sort besar.

3. Menambah index tanpa memahami pola baca dan tulis

Setiap index punya harga. Pada tabel dengan write tinggi, index berlebihan bisa memperlambat insert, update, dan maintenance.

4. Tidak memisahkan masalah query tunggal dan masalah N+1

ORM sering menimbulkan dua jenis masalah berbeda:

Satu query besar yang plan-nya buruk
Banyak query kecil berulang karena relasi tidak diambil dengan benar

Keduanya sama-sama berasal dari abstraksi ORM, tetapi teknik perbaikannya berbeda.

5. Terlalu cepat pindah ke cache

Cache bisa membantu, tetapi cache tidak memperbaiki query dasar yang tidak skalabel. Jika endpoint penting bergantung pada query yang rusak secara struktural, cache hanya menunda masalah dan menambah kompleksitas invalidasi.

Kapan Menambah Composite Index, Kapan Rewrite Query

Tambah composite index jika

Pola filter dan sort stabil
Query sangat sering dipakai
Execution plan menunjukkan sort atau scan bisa dipersempit dengan urutan index yang benar
Biaya write tambahan masih masuk akal

Rewrite query jika

Query membawa join atau subquery yang terlalu dini
OFFSET tinggi menjadi sumber utama biaya
Anda mengambil terlalu banyak kolom atau terlalu banyak row sebelum limit
Predicate membuat optimizer sulit memakai index secara efektif

Dalam praktik, perbaikan terbaik sering merupakan kombinasi keduanya: query disederhanakan dulu agar pola aksesnya jelas, lalu index dibuat mengikuti pola akses tersebut.

Penutup

Jangan percaya ORM hanya karena API-nya nyaman dan kodenya terlihat bersih. Seperti konteks besar yang tampak meyakinkan tetapi perlu diverifikasi, abstraksi query juga harus diuji terhadap metrik nyata: SQL final, execution plan, cardinality, row scanned, pola sort, pagination, dan join.

Saat data membesar, bottleneck biasanya bukan hal mistis. Ia hampir selalu terlihat jika Anda memeriksa plan dengan disiplin. Fokuslah pada query yang benar-benar dieksekusi, pahami bagaimana database membaca data, lalu putuskan secara sadar: cukup tambah composite index, ganti OFFSET dengan keyset pagination, atau tulis ulang query agar subset data dipersempit lebih awal. Itulah audit query lambat yang layak untuk sistem produksi.

Quick Search

shopping_cart Keranjang