Verifikasi Query SQL Kritis sebelum Optimasi Index di Produksi

Verifikasi query SQL kritis sebelum optimasi index di produksi berarti memastikan hasil query tetap benar terlebih dahulu, baru kemudian dipercepat. Ini penting karena index yang salah sasaran sering hanya mempercepat query yang secara logika sudah bermasalah: hasil bisa lompat antar halaman, ada data yang hilang, duplikat muncul, atau urutan tidak stabil ketika data terus berubah.

Untuk query yang memakai kombinasi filter, ORDER BY, dan LIMIT/OFFSET, masalah performa hampir selalu beririsan dengan masalah kebenaran hasil. Karena itu, pendekatannya sebaiknya mirip ide model checking seperti di Kani untuk Rust: definisikan kondisi yang harus selalu benar (invariant), uji dengan data kecil yang sengaja memicu sudut kasus, lalu baru evaluasi rencana eksekusi dan desain index.

Mengapa optimasi terlalu cepat sering menyesatkan

Banyak tim langsung menambah index ketika melihat query lambat di produksi. Masalahnya, query lambat belum tentu disebabkan index yang kurang. Bisa jadi akar masalahnya adalah:

Urutan hasil tidak deterministik karena kolom pada ORDER BY tidak unik.
Pagination tidak stabil karena memakai OFFSET pada data yang terus menerima insert/update.
Predikat filter ambigu akibat kombinasi AND/OR tanpa tanda kurung yang jelas.
Index tidak cocok dengan pola akses sehingga database tetap melakukan sort besar atau scan mahal.
Optimasi lokal yang mengubah semantik, misalnya memaksa index atau menulis ulang query tanpa memverifikasi hasil ekuivalen.

Jika query belum diverifikasi, index baru dapat menyamarkan bug. Query menjadi lebih cepat, tetapi hasilnya tetap salah.

Kerangka verifikasi: pikirkan seperti memeriksa invariant

Inspirasi dari model checking bukan berarti Anda harus memakai tool formal untuk SQL. Intinya adalah disiplin berpikir: tentukan properti yang harus selalu benar untuk semua keadaan data yang relevan, lalu buat skenario kecil yang menekan batas logika query.

Invariant yang layak didefinisikan

Untuk query kritis, terutama yang dipakai daftar transaksi, order, notifikasi, atau audit log, beberapa invariant berikut biasanya berguna:

Semua baris hasil memenuhi filter.
Tidak ada baris yang seharusnya lolos tetapi hilang.
Urutan hasil deterministik. Jika dua baris punya nilai sort sama, harus ada tie-breaker tambahan.
Pagination tidak menghasilkan duplikat antar halaman.
Pagination tidak melewatkan baris yang seharusnya tampil.
Perubahan index tidak mengubah hasil query. Index hanya boleh mengubah performa, bukan semantik.

Dengan invariant ini, diskusi review menjadi lebih objektif. Anda tidak hanya bertanya, “lebih cepat atau tidak?”, tetapi juga “apakah hasilnya tetap benar di kasus tepi?”.

Studi kasus: query lambat dengan filter gabungan, ORDER BY, dan LIMIT/OFFSET

Misalkan ada tabel orders yang terus bertambah:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  paid_at TIMESTAMP NULL,
  total_amount NUMERIC(12,2) NOT NULL,
  archived BOOLEAN NOT NULL DEFAULT FALSE
);

Tim Anda memiliki query daftar order terbaru untuk satu tenant, dengan filter status tertentu dan pagination:

SELECT id, tenant_id, status, created_at, paid_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND archived = FALSE
  AND (status = 'paid' OR status = 'shipped')
ORDER BY created_at DESC
LIMIT 50 OFFSET 1000;

Query ini tampak wajar, tetapi menyimpan dua risiko sekaligus: performa dan konsistensi hasil.

Risiko 1: hasil tidak stabil karena ORDER BY tidak unik

Jika banyak order memiliki created_at yang sama, urutan antar baris dengan timestamp identik tidak dijamin stabil. Dalam praktiknya, halaman 1 dan halaman 2 bisa berubah isi ketika query dieksekusi ulang, meskipun tanpa perubahan besar pada aplikasi.

Perbaikan minimum adalah menambahkan tie-breaker yang unik:

SELECT id, tenant_id, status, created_at, paid_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND archived = FALSE
  AND (status = 'paid' OR status = 'shipped')
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000;

Dengan id sebagai tie-breaker, urutan menjadi deterministik.

Risiko 2: OFFSET makin mahal dan makin rentan inkonsisten

OFFSET 1000 berarti database tetap harus menemukan dan mengurutkan setidaknya 1050 baris sebelum membuang 1000 pertama. Pada dataset yang terus tumbuh, biaya ini naik. Selain itu, ketika ada baris baru masuk di depan urutan, halaman berikutnya bisa bergeser sehingga pengguna melihat duplikat atau kehilangan baris tertentu.

Contohnya:

Pengguna membuka halaman 1.
Baru kemudian ada beberapa order baru dengan created_at lebih besar.
Pengguna membuka halaman 2 dengan OFFSET yang sama.
Posisi data lama telah bergeser; sebagian baris bisa terulang atau terlewat.

Langkah audit sebelum menambah index

1. Definisikan invariant hasil query secara eksplisit

Jangan mulai dari DDL index. Mulailah dari spesifikasi hasil query. Misalnya untuk query daftar order di atas:

Semua hasil harus berasal dari tenant_id = 42.
archived harus selalu FALSE.
Status hanya paid atau shipped.
Urutan harus turun berdasarkan created_at, lalu id.
Baris yang muncul di halaman N tidak boleh muncul lagi di halaman N+1 untuk snapshot data yang sama.

Jika Anda tidak bisa menuliskan invariant ini dengan jelas, berarti query belum siap dioptimasi.

2. Buat dataset kecil yang sengaja memicu bug

Jangan hanya menguji dengan data acak besar. Data kecil lebih mudah diverifikasi manual dan lebih efektif untuk menemukan bug logika.

INSERT INTO orders (id, tenant_id, status, created_at, paid_at, total_amount, archived) VALUES
  (101, 42, 'paid',    '2025-01-10 10:00:00', '2025-01-10 10:05:00', 100.00, FALSE),
  (102, 42, 'shipped', '2025-01-10 10:00:00', '2025-01-10 10:06:00', 150.00, FALSE),
  (103, 42, 'pending', '2025-01-10 09:59:59', NULL,                   200.00, FALSE),
  (104, 42, 'paid',    '2025-01-10 10:00:00', '2025-01-10 10:07:00', 120.00, TRUE),
  (105, 99, 'paid',    '2025-01-10 10:00:00', '2025-01-10 10:08:00', 130.00, FALSE),
  (106, 42, 'shipped', '2025-01-10 09:58:00', '2025-01-10 10:09:00', 170.00, FALSE);

Dataset seperti ini sengaja memuat:

Timestamp sama untuk menguji kestabilan sort.
Status yang harus terfilter keluar.
Baris archived yang tidak boleh muncul.
Data tenant lain untuk menguji isolasi filter.

Dari sini Anda bisa memverifikasi hasil secara manual. Jika urutan atau isi hasil berbeda dari ekspektasi, masalahnya ada pada query, bukan pada index.

3. Verifikasi bentuk logika WHERE, terutama OR

Kesalahan umum adalah perubahan kecil pada kondisi yang mengubah semantik. Contoh anti-pattern:

-- Salah secara semantik jika maksudnya archived = FALSE berlaku untuk semua status
WHERE tenant_id = 42
  AND archived = FALSE
  AND status = 'paid' OR status = 'shipped'

Karena prioritas operator SQL, ekspresi di atas setara dengan:

WHERE (tenant_id = 42 AND archived = FALSE AND status = 'paid')
   OR status = 'shipped'

Akibatnya, semua shipped bisa lolos tanpa memedulikan tenant atau archived. Gunakan tanda kurung secara eksplisit:

WHERE tenant_id = 42
  AND archived = FALSE
  AND (status = 'paid' OR status = 'shipped')

Jika DBMS Anda mendukung dan sesuai kebutuhan, bentuk IN (...) juga dapat membuat maksud lebih jelas:

WHERE tenant_id = 42
  AND archived = FALSE
  AND status IN ('paid', 'shipped')

4. Gunakan EXPLAIN atau EXPLAIN ANALYZE setelah kebenaran hasil diverifikasi

Setelah yakin semantik query benar, periksa rencana eksekusinya. Nama dan format output memang berbeda antar database, tetapi prinsip yang perlu dicari umumnya sama:

Apakah database melakukan full table scan atau bisa memakai index?
Apakah ada langkah sort besar karena urutan tidak didukung index?
Apakah jumlah baris yang diperkirakan sangat meleset dari aktual?
Apakah OFFSET membuat database tetap memproses terlalu banyak baris?

Contoh umum:

EXPLAIN ANALYZE
SELECT id, tenant_id, status, created_at, paid_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND archived = FALSE
  AND status IN ('paid', 'shipped')
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000;

Jika Anda melihat scan besar diikuti sort, kemungkinan index belum cocok dengan pola filter dan urutan. Namun jangan terburu-buru membuat banyak index; validasi dulu pola akses dominan aplikasi.

5. Validasi desain index terhadap pola query, bukan terhadap satu kolom populer

Index yang baik mengikuti cara query menyaring dan mengurutkan data. Untuk query studi kasus, kebutuhan logisnya adalah:

Filter oleh tenant_id.
Filter oleh archived.
Filter oleh status.
Urutkan dengan created_at DESC, id DESC.

Dalam banyak kasus, index komposit lebih relevan daripada index tunggal terpisah. Contoh bentuk umum:

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

Tetapi ini bukan resep universal. Trade-off yang harus diperhatikan:

Biaya write naik. Insert dan update menjadi lebih mahal karena index harus dipelihara.
Ukuran storage bertambah.
Index bisa tidak efektif jika selectivity rendah atau pola query sebenarnya beragam.
Terlalu banyak index mirip sering menambah overhead tanpa manfaat nyata.

Karena itu, validasi dengan EXPLAIN tetap wajib. Jangan mengasumsikan index tertentu pasti dipakai hanya karena namanya terlihat cocok.

Kapan LIMIT/OFFSET harus diganti ke keyset pagination

Jika daftar diakses berurutan ke halaman berikutnya dan data terus berubah, keyset pagination biasanya lebih stabil daripada OFFSET. Prinsipnya: halaman berikutnya ditentukan oleh nilai baris terakhir dari halaman sebelumnya, bukan oleh jumlah baris yang dilewati.

Contoh keyset pagination

Halaman pertama:

SELECT id, tenant_id, status, created_at, paid_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND archived = FALSE
  AND status IN ('paid', 'shipped')
ORDER BY created_at DESC, id DESC
LIMIT 50;

Misalkan baris terakhir pada halaman pertama memiliki:

created_at = '2025-01-10 10:00:00'
id = 102

Halaman berikutnya:

SELECT id, tenant_id, status, created_at, paid_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND archived = FALSE
  AND status IN ('paid', 'shipped')
  AND (
    created_at < '2025-01-10 10:00:00'
    OR (created_at = '2025-01-10 10:00:00' AND id < 102)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Mengapa ini lebih baik?

Lebih stabil pada data yang berubah di depan urutan.
Lebih efisien untuk halaman dalam, karena tidak perlu membuang ribuan baris dengan OFFSET.
Selaras dengan index urutan jika disusun benar.

Kapan OFFSET masih masuk akal

OFFSET masih dapat diterima jika:

Ukuran data relatif kecil.
Pengguna memang perlu lompat langsung ke halaman tertentu berdasarkan nomor halaman.
Daftar tidak terlalu sensitif terhadap perubahan data di tengah navigasi.
Biaya query masih terukur dan stabil.

Sebaliknya, pertimbangkan keyset pagination jika:

Data bertambah terus-menerus.
Halaman dalam sering diakses.
Konsistensi antar halaman penting.
Urutan hasil harus stabil dan dapat diulang.

Pola verifikasi praktis sebelum deploy index atau perubahan pagination

Bandingkan hasil query lama vs query usulan

Sebelum rollout, jalankan kedua query pada dataset uji dan, jika aman, pada snapshot produksi. Tujuannya bukan hanya membandingkan waktu eksekusi, tetapi juga isi hasil.

Hal yang perlu dicek:

Apakah jumlah baris sama untuk parameter yang sama?
Apakah urutan identik?
Apakah ada baris yang hilang atau tambahan?
Apakah halaman lanjutan tetap konsisten?

Untuk query kritis, pendekatan yang baik adalah menyimpan beberapa skenario parameter nyata dari aplikasi lalu menjalankannya sebagai suite regresi SQL.

Uji kasus tepi yang sering terlupakan

Banyak baris dengan nilai sort sama.
Nilai NULL pada kolom yang ikut filter atau urutan.
Tenant dengan volume data sangat besar dibanding tenant lain.
Rentang waktu yang padat saat banyak insert terjadi bersamaan.
Perubahan status setelah baris sudah pernah muncul di halaman sebelumnya.

Kasus tepi inilah yang sering membedakan query “terlihat benar” dari query yang benar di produksi.

Perhatikan snapshot dan isolasi transaksi

Jika Anda membandingkan hasil pagination pada dua eksekusi berbeda, perlu diingat bahwa perubahan data di antara eksekusi memang dapat menghasilkan hasil berbeda, terutama pada isolation level tertentu. Ini bukan selalu bug query, tetapi harus dipahami saat menguji.

Karena itu, verifikasi kebenaran biasanya dibagi menjadi dua lapis:

Snapshot tetap: untuk memastikan logika query dan urutan benar.
Data bergerak: untuk menilai apakah strategi pagination tetap masuk akal di kondisi nyata.

Checklist review query SQL kritis

Apakah tujuan query dan invariant hasil sudah tertulis jelas?
Apakah kondisi WHERE bebas dari ambiguitas AND/OR?
Apakah ORDER BY deterministik dengan tie-breaker unik?
Apakah LIMIT/OFFSET benar-benar sesuai kebutuhan produk?
Apakah keyset pagination lebih cocok untuk pola akses ini?
Apakah dataset kecil pemicu bug sudah dibuat dan diuji?
Apakah query lama dan query baru dibandingkan hasilnya, bukan hanya latensinya?
Apakah EXPLAIN menunjukkan scan/sort yang sesuai ekspektasi?
Apakah index yang diusulkan mengikuti pola filter dan urutan query?
Apakah biaya write, storage, dan maintenance index sudah dipertimbangkan?
Apakah ada risiko hasil berubah saat data bertambah cepat?
Apakah rollback plan untuk index atau perubahan query sudah disiapkan?

Anti-pattern umum

1. Menambah index sebelum mendefinisikan query yang benar

Ini anti-pattern paling umum. Index mempercepat akses, tetapi tidak memperbaiki semantik.

2. ORDER BY pada kolom non-unik tanpa tie-breaker

Hasil bisa berubah-ubah antar eksekusi dan membuat pagination tidak stabil.

3. Mengandalkan OFFSET untuk halaman dalam pada data yang terus tumbuh

Biaya query bertambah dan risiko duplikat atau data hilang antar halaman meningkat.

4. Memecah index menjadi banyak index tunggal tanpa melihat query nyata

Database belum tentu dapat memanfaatkan kombinasi itu seefisien index komposit yang sesuai pola query.

5. Menguji hanya dengan data besar acak

Data besar berguna untuk performa, tetapi bug logika sering lebih mudah ditemukan pada data kecil yang sengaja dirancang.

6. Menilai sukses hanya dari penurunan waktu query

Query yang lebih cepat tetapi hasilnya berubah adalah regresi, bukan optimasi.

Penutup

Dalam verifikasi query SQL kritis sebelum optimasi index di produksi, urutan kerja yang aman adalah: tentukan invariant hasil, bangun dataset kecil pemicu bug, verifikasi semantik query, evaluasi EXPLAIN/ANALYZE, lalu sesuaikan index atau strategi pagination. Pendekatan ini sejalan dengan ide model checking: pastikan properti penting selalu benar, termasuk di edge case yang jarang terlihat.

Jika query Anda melibatkan filter gabungan, ORDER BY, dan LIMIT/OFFSET pada data yang terus bertambah, jangan hanya bertanya “index apa yang kurang?”. Tanyakan juga: “apakah hasilnya deterministik, apakah halaman stabil, dan apakah perubahan performa ini tetap menjaga kebenaran hasil?” Biasanya, justru pertanyaan itulah yang mencegah bug produksi paling mahal.

Quick Search

shopping_cart Keranjang