Dalam proyek berskala menengah, tim sering terjebak pada query lambat yang menghambat fitur baru. Mengingat kekaguman terhadap Fabrice Bellard yang mampu menyusun sistem kompleks secara efisien, tim engineering dapat meniru pendekatan sistematis yang memadukan pengamatan nyata dengan optimisasi praktis untuk menangani bottleneck SQL.

Pembaca akan langsung melihat langkah untuk menemukan query bermasalah, optimasi index dengan variasi partial/filtered, serta cara menggunakan sampling dan pagination agar kinerja tetap stabil seiring data tumbuh.

Identifikasi Bottleneck SQL dengan Data Nyata

Langkah pertama ialah memahami query yang paling berat tanpa menebak. Jalankan EXPLAIN ANALYZE atau observasi dari slow query log untuk melihat apakah bottleneck pada full table scan, hash join, atau operasi disk I/O. Contoh output yang menandakan masalah: waktu eksekusi melebihi 200 ms dan ada Seq Scan di tabel besar.

Catat pattern berikut:

  • Kolom filter tidak punya index.
  • Kondisi WHERE lebih kompleks dari sekadar =, misalnya IS NOT NULL atau rentang waktu.
  • Query yang selalu membaca seluruh tabel karena agregasi lengkap.

Setelah mengidentifikasi query tertentu, periksa parameterizasi dan apakah ada bind variable berbeda yang membuat index tidak digunakan.

Optimasi Index untuk Query Tertentu

Untuk kasus yang paling umum, index komposit diurutkan menurut urutan kolom dalam kondisi WHERE dan JOIN. Jika query Anda melihat kolom status lalu created_at, index sebaiknya dibuat sebagai (status, created_at).

Contoh SQL untuk menambahkan index komposit:

CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC);

Perhatikan bahwa urutan kolom penting karena database hanya memakai prefix index. Jika query menggunakan WHERE status = 'active' lalu ORDER BY created_at DESC, index komposit akan membatasi biaya scanning.

Untuk query dengan filter boolean atau rentang kecil, partial index jauh lebih efisien.

Partial atau Filtered Index untuk Kondisi Umum

Partial index menyimpan subset data, sehingga lebih kecil dan cepat dicari. Misalnya, jika 90% baris memiliki status = 'archived' tetapi query hanya menargetkan 'running', buat index seperti:

CREATE INDEX idx_orders_running ON orders (created_at DESC) WHERE status = 'running';

Kombinasi partial index dan parameter kondisi membuat planner memilih index yang tepat. Namun, hati-hati dengan update frekuensi: partial index harus dijaga konsistensinya dan tidak cocok jika kolom filter sering berubah nilai.

Trade-off: partial index menambah biaya penulisan jika data bergerak antar kondisi, jadi monitor write amplification.

Sampling untuk Analisis dan Pagging Stabil

Sampling berguna saat data terlalu besar untuk scan penuh, terutama untuk metrik agregasi atau validasi. Gunakan sampling yang bisa diandalkan seperti:

  • TABLESAMPLE BERNOULLI untuk PostgreSQL, mengambil persentase baris secara acak.
  • Sampel log permanen untuk analisis distribusi query.

Contoh penggunaan:

SELECT user_id, COUNT(*) FROM events TABLESAMPLE BERNOULLI (1) WHERE created_at >= current_date - interval '1 day' GROUP BY user_id;

Penting: sampling tidak menjamin akurasi absolut. Gunakan hasilnya untuk deteksi anomali atau perbandingan relatif, bukan penghitungan keuangan.

Strategi Pagination dan Cursor untuk Query yang Melibatkan Banyak Baris

Pagination klasik dengan OFFSET menyebabkan skip scan di awal terus menerus, sehingga cursor-based pagination lebih scalable. Contoh pendekatan:

SELECT * FROM orders WHERE status = 'running' AND created_at < :last_created_at ORDER BY created_at DESC LIMIT 50;

Arahkan query menggunakan kolom berindeks (misalnya created_at) dan simpan nilai terakhir sebagai cursor. Ini menghindari penghitungan ulang seluruh offset setiap halaman.

Perlu dicatat bahwa cursor-based pagination memerlukan determinisme dalam sort order dan tidak cocok untuk data yang sering diubah urutannya.

Monitoring dan Debugging Berkelanjutan

Setelah perubahan index dan sampling diterapkan, pantau metrik seperti avg query time, db cpu, dan page_io. Gunakan pg_stat_statements atau equivalent untuk membandingkan jumlah pemanggilan query sebelum dan sesudah. Jika query tetap lambat, cek:

  • Apakah planner memilih index yang diinginkan (gunakan EXPLAIN).
  • Apakah statistik kolom terbaru (jalankan ANALYZE).
  • Apakah data cukup kecil untuk cache? Jika tidak, pertimbangkan in-memory caching layer untuk hasil tertentu.

Kesimpulannya, dengan pendekatan rasional seperti yang dikagumi dari Fabrice Bellard—mengobservasi secara detail lalu membangun solusi kecil tapi tepat—tim bisa mengatasi query lambat menggunakan index cerdas, sampling yang sesuai, dan pagination berbasis cursor tanpa harus mengorbankan konsistensi.