Menguji Query SQL di Kondisi Ekstrem Sebelum Bottleneck Produksi

Menguji query SQL di kondisi ekstrem berarti memaksa query menghadapi data, distribusi, dan pola akses yang paling merugikan sebelum masalah muncul di produksi. Query yang terlihat aman pada dataset kecil sering melambat drastis ketika cardinality berubah, filter tidak lagi selektif, hasil sort membesar, atau pagination berbasis offset harus melompati jutaan baris.

Masalah utamanya bukan hanya ukuran tabel, tetapi bentuk data dan cara optimizer memilih rencana eksekusi. Karena itu, pengujian performa query yang benar tidak cukup memakai data contoh yang rapi. Anda perlu menyusun dataset buruk, memeriksa hasil EXPLAIN atau EXPLAIN ANALYZE, lalu membuktikan apakah index, join, filter, dan pagination masih bekerja baik dalam kondisi yang mendekati skenario terburuk.

Mengapa query yang awalnya cepat bisa menjadi bottleneck

Di lingkungan pengembangan, query sering diuji pada data yang terlalu kecil, terlalu bersih, atau terlalu merata. Ini menutupi beberapa masalah umum:

Index terlihat benar tetapi tidak selektif. Misalnya, ada index pada kolom status, tetapi 95% baris bernilai active. Secara teori index ada, secara praktik optimizer bisa memilih scan besar atau index scan yang tetap mahal.
Pagination memburuk seiring pertumbuhan data. LIMIT ... OFFSET ... tetap harus menghitung atau melewati banyak baris sebelum mengembalikan hasil.
Sort menjadi mahal. Query yang mengurutkan hasil besar tanpa index yang mendukung bisa memicu sort di memori atau disk.
Join meledak karena cardinality salah perkiraan. Estimasi optimizer yang meleset dapat memilih urutan join yang buruk.
Filter yang dulu efektif menjadi lemah. Perubahan distribusi data membuat predicate tidak lagi memperkecil hasil secara signifikan.

Gejala di produksi biasanya terlihat sebagai latensi yang naik perlahan, CPU database tinggi, lonjakan I/O, query yang sesekali sangat lambat, atau waktu respons API yang buruk pada halaman tertentu saja.

Menyusun dataset buruk, bukan dataset nyaman

Jika tujuan Anda adalah menemukan bottleneck sebelum produksi, buat data yang sengaja memukul asumsi query. Inspirasi pendekatan eksplorasi ekstrem adalah: jangan hanya menguji skenario normal, tetapi cari input yang membuat sistem salah, lambat, atau tidak stabil.

Pola dataset buruk yang perlu dibuat

Skewed distribution: sebagian besar baris memiliki nilai yang sama.
Hot partition atau hot tenant: satu akun atau organisasi memiliki data sangat besar dibanding yang lain.
Rentang waktu padat: banyak data terkonsentrasi di periode terbaru, karena halaman produksi sering membaca data baru.
Kolom nullable atau sparsity tinggi: sebagian besar null, sebagian kecil bernilai.
Many-to-one yang timpang: satu entitas punya jutaan turunan, yang lain hanya beberapa.
Duplikasi nilai sort key: misalnya banyak baris memiliki created_at yang sama, sehingga pagination dan ordering perlu tie-breaker.

Contoh skema backend nyata

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

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  price NUMERIC(12,2) NOT NULL
);

Query yang sering muncul pada backend:

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

Pada awalnya query ini mungkin cepat. Namun jika tenant 42 adalah tenant terbesar, hampir semua order berstatus paid, dan jumlah order mencapai puluhan juta, offset besar akan menjadi mahal.

Cara praktis membuat data uji yang memancing masalah

Anda tidak harus meniru seluruh data produksi. Yang penting adalah meniru karakteristik yang memengaruhi rencana eksekusi:

Buat satu atau beberapa tenant dengan volume sangat besar.
Pastikan satu nilai filter dominan, misalnya status='paid' untuk mayoritas data.
Buat rentang waktu terbaru sangat padat, karena query dashboard dan API list biasanya memukul area ini.
Masukkan nilai duplikat pada kolom yang dipakai untuk sort.
Buat beberapa relasi dengan fan-out tinggi agar join lebih berat.

Tujuannya bukan sekadar data banyak, tetapi data yang bisa mengubah keputusan optimizer.

Membaca EXPLAIN dan EXPLAIN ANALYZE dengan benar

EXPLAIN menunjukkan rencana yang dipilih optimizer. EXPLAIN ANALYZE menambahkan eksekusi nyata sehingga Anda bisa membandingkan estimasi dengan realitas. Nama output dan detailnya berbeda antar database, tetapi prinsip auditnya sama.

Yang perlu diperhatikan

Access path: apakah query memakai index scan, range scan, bitmap scan, atau full table scan.
Rows estimated vs actual: jika estimasi jauh meleset, statistik atau distribusi data kemungkinan menjadi masalah.
Sort: apakah sorting terjadi setelah mengambil banyak baris, dan apakah ada indikasi spill ke disk.
Join order: tabel mana yang dibaca dulu, dan apakah join dilakukan setelah hasil antara membesar.
Filter placement: apakah filter bisa didorong lebih awal atau baru terjadi setelah join/scan besar.

Contoh audit sederhana

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

Jika hasil menunjukkan database harus membaca atau melewati puluhan ribu hingga jutaan baris sebelum menghasilkan 50 baris akhir, akar masalahnya biasanya bukan pada LIMIT 50, melainkan pada OFFSET 50000 dan urutan akses data.

Catatan: saat memakai EXPLAIN ANALYZE di lingkungan yang sensitif, hati-hati karena query benar-benar dijalankan. Gunakan replika, staging, atau dataset uji yang representatif.

Tanda akar masalah dari output plan

Actual rows jauh lebih besar daripada baris hasil akhir: query membuang terlalu banyak data di tengah jalan.
Estimated rows jauh lebih kecil dari actual rows: optimizer salah menilai selectivity.
Sort di atas hasil besar: index tidak mendukung filter sekaligus ordering.
Nested loop pada input besar: bisa benar pada input kecil, tetapi buruk pada distribusi ekstrem.

Menguji cardinality dan selectivity index

Salah satu kesalahan paling umum adalah menganggap setiap index otomatis membantu. Padahal efektivitas index sangat bergantung pada cardinality, urutan kolom, dan pola query.

Contoh index yang tampak benar tetapi gagal

CREATE INDEX idx_orders_status ON orders(status);

Index ini masuk akal jika Anda sering memfilter berdasarkan status. Tetapi jika hampir semua order bernilai paid, index ini mungkin kurang berguna. Database tetap harus membaca sangat banyak entri index dan mengambil banyak baris dari tabel.

Dalam kasus daftar order per tenant yang diurutkan berdasarkan waktu, index yang lebih sesuai sering berbentuk komposit yang mengikuti pola filter dan sort:

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

Mengapa ini lebih baik? Karena database bisa mempersempit baris berdasarkan tenant_id dan status, lalu membaca hasil sudah dalam urutan yang dibutuhkan, sehingga kebutuhan sort berkurang atau hilang.

Hal yang harus diuji pada index

Apakah kolom paling kiri index benar-benar dipakai dalam predicate utama.
Apakah urutan kolom sesuai dengan pola WHERE dan ORDER BY.
Apakah index tetap efektif saat nilai filter sangat dominan.
Apakah query tetap harus melakukan sort tambahan meski index ada.
Apakah index terlalu lebar sehingga biaya tulis meningkat signifikan.

Trade-off index komposit

Index komposit sering mempercepat query baca tertentu, tetapi ada biaya:

Insert dan update menjadi lebih mahal.
Ukuran penyimpanan bertambah.
Terlalu banyak index membuat planner memiliki lebih banyak pilihan dan pemeliharaan lebih mahal.

Karena itu, keputusan mengubah index sebaiknya didasarkan pada query yang benar-benar penting, bukan menambah index untuk semua kombinasi kolom.

Offset vs keyset pagination: mana yang tahan saat data besar

Pagination adalah sumber bottleneck klasik. Offset pagination mudah dipakai, tetapi biaya cenderung naik seiring nomor halaman. Keyset pagination biasanya lebih stabil karena tidak perlu melompati banyak baris.

Offset pagination

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

Masalah utamanya: database tetap harus memproses baris sampai offset terpenuhi. Semakin dalam halamannya, semakin mahal biaya baca dan sort.

Keyset pagination

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

Pada pendekatan ini, halaman berikutnya diambil berdasarkan nilai terakhir dari halaman sebelumnya. Jika ada index yang sesuai dengan urutan ini, database bisa melanjutkan pembacaan dari posisi tertentu, bukan menghitung ulang dari awal.

Kapan memilih masing-masing

Offset pagination cocok untuk dataset kecil, halaman dangkal, atau kebutuhan lompat langsung ke halaman nomor tertentu.
Keyset pagination lebih cocok untuk feed, riwayat, log, transaksi, dan daftar besar yang diurutkan stabil.

Kesalahan umum pada keyset pagination

Tidak memakai tie-breaker unik seperti id, sehingga urutan tidak stabil saat ada banyak nilai created_at yang sama.
Menggunakan kolom sort yang dapat berubah, sehingga cursor menjadi tidak konsisten.
Tetap memaksa query yang tidak sesuai urutan index.

Bottleneck pada sort, filter, dan join

Sort tanpa dukungan index

Jika query memfilter banyak baris lalu mengurutkan hasil besar, biaya utama bisa berasal dari sort, bukan filter. Tanda umumnya adalah plan menunjukkan langkah sort setelah scan besar. Solusinya bisa berupa:

membuat index yang sesuai urutan akses,
mengurangi jumlah kolom atau baris yang disort,
memecah query menjadi dua tahap bila perlu.

Filter yang tidak selektif

Predicate seperti status='active', deleted_at IS NULL, atau is_visible=true sering tampak penting tetapi kurang membatasi hasil. Jika query bergantung hanya pada filter seperti ini, index tunggal mungkin tidak banyak membantu. Gabungkan dengan kolom yang benar-benar memotong ruang pencarian, misalnya tenant_id, category_id, atau rentang waktu.

Join yang membesar di tengah jalan

Contoh query laporan:

SELECT o.id, SUM(oi.quantity * oi.price) AS subtotal
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.tenant_id = 42
  AND o.status = 'paid'
  AND o.created_at >= '2026-01-01'
GROUP BY o.id
ORDER BY o.id DESC
LIMIT 100;

Jika filter pada orders tidak cukup dipersempit lebih awal, join ke order_items dapat menarik volume besar sebelum agregasi. Audit yang perlu dilakukan:

Pastikan tabel pendorong query adalah yang paling selektif untuk kondisi tersebut.
Pastikan kolom join memiliki index yang sesuai.
Periksa apakah agregasi bisa dilakukan setelah set order dipersempit lebih dulu.

Dalam beberapa kasus, lebih efisien mengambil 100 order yang relevan dulu, lalu melakukan join ke detailnya, dibanding membiarkan join terjadi pada ruang data besar sejak awal.

Langkah audit praktis sebelum rilis

Berikut checklist yang bisa dipakai saat mereview query penting sebelum produksi:

1. Identifikasi query yang benar-benar kritis

Daftar endpoint API dengan volume tinggi.
Halaman admin atau dashboard yang membaca banyak data.
Job batch, sinkronisasi, ekspor, dan laporan.

2. Definisikan skenario ekstrem

Tenant terbesar.
Filter paling tidak selektif.
Halaman pagination terdalam yang masih mungkin terjadi.
Rentang tanggal terpadat.
Join pada relasi dengan fan-out terbesar.

3. Jalankan EXPLAIN dan EXPLAIN ANALYZE

Bandingkan estimasi dan realisasi jumlah baris.
Cari scan besar, sort mahal, dan join order yang aneh.
Periksa apakah index yang diharapkan benar-benar dipakai.

4. Validasi urutan index terhadap query

Kolom equality filter biasanya perlu muncul lebih awal.
Kolom untuk ordering perlu konsisten dengan pola akses.
Hindari index yang hanya terasa masuk akal secara intuitif tetapi tidak cocok dengan query nyata.

5. Uji offset besar dan bandingkan dengan keyset

Jangan hanya menguji halaman pertama.
Pastikan latensi tidak memburuk drastis di halaman dalam.
Jika ya, pertimbangkan migrasi ke keyset pagination.

6. Audit pola akses data, bukan hanya query

Apakah daftar besar ini memang harus dibuka secara acak per halaman?
Apakah data bisa diarsipkan, dipartisi, atau dipisah ke tabel ringkas?
Apakah agregasi sebaiknya dipra-hitung daripada dihitung setiap request?

Kapan perlu ubah index, query, atau pola akses data

Ubah index jika

query sebenarnya sudah benar tetapi planner tidak punya jalur akses efisien,
sort bisa dihindari dengan index yang sesuai,
predicate utama dan ordering stabil serta sering dipakai.

Ubah query jika

query membuang terlalu banyak baris setelah membaca data besar,
join dilakukan terlalu awal,
pagination berbasis offset menjadi bottleneck,
seleksi bisa dipersempit sebelum agregasi atau join.

Ubah pola akses data jika

kebutuhan bisnis meminta eksplorasi data yang terlalu mahal untuk query OLTP biasa,
laporan berat berjalan di jalur request pengguna,
dataset historis sangat besar dan seharusnya diarsipkan atau dipisahkan,
query yang sama terus membutuhkan perhitungan agregat besar secara berulang.

Di tahap ini, solusi bisa berupa materialized summary, tabel denormalisasi terbatas, cache hasil, pipeline analitik terpisah, atau partisi data. Ini bukan pengganti query yang baik, tetapi sering diperlukan saat pola akses memang berubah.

Kesalahan yang sering terjadi saat pengujian performa SQL

Menguji hanya dengan data sedikit dan distribusi merata.
Menganggap adanya index berarti query pasti cepat.
Tidak memeriksa halaman pagination yang dalam.
Hanya melihat waktu total, tanpa membaca rencana eksekusi.
Tidak membedakan masalah query dengan masalah pola akses aplikasi.
Menambah banyak index tanpa menghitung biaya tulis dan pemeliharaan.

Penutup

Menguji query SQL di kondisi ekstrem bukan sekadar stress test dengan data besar. Yang lebih penting adalah menguji kasus buruk yang realistis: filter yang tidak selektif, tenant raksasa, offset dalam, sort besar, dan join dengan cardinality timpang. Dengan pendekatan ini, Anda bisa menemukan query yang tampak aman tetapi sebenarnya rapuh saat data tumbuh.

Gunakan dataset yang sengaja merugikan, baca EXPLAIN dan EXPLAIN ANALYZE secara disiplin, bandingkan offset dengan keyset pagination, lalu tentukan apakah masalahnya harus diselesaikan dengan index baru, perbaikan query, atau perubahan pola akses data. Bottleneck produksi sering muncul dari asumsi yang tidak pernah diuji; tugas review sebelum rilis adalah membongkar asumsi itu lebih awal.

Quick Search

shopping_cart Keranjang