Jawaban langsung pada bottleneck SQL akibat pertumbuhan data

Ketika John Carmack membagikan pelajaran debugging Quake, inti pesannya adalah: bottleneck bisa muncul bukan karena fitur baru, melainkan karena data lama tumbuh tanpa diantisipasi. Masalah utama adalah query yang berjalan lancar pada dataset kecil tapi melambung saat volume naik. Oleh karena itu fokus utama adalah profil query, membaca execution plan, dan mengelola indeks serta pagination agar tetap efisien saat data tumbuh.

Diagnosa awal: profiling query dan metrik penting

Pertumbuhan data memperbesar rentang waktu eksekusi query. Langkah pertama adalah memprofil langsung query yang melambat. Gunakan profiling bawaan database, misalnya EXPLAIN ANALYZE di PostgreSQL atau SHOW PROFILE di MySQL, untuk melihat fase parsing, planning, dan execution.

Fokuslah pada waktu total, log disk scan, dan jumlah baris yang dibaca dibandingkan dengan hasil yang dikirim ke aplikasi. Jika query membaca jutaan baris namun hanya mengirimkan ratusan, maka bottleneck jelas ada di scan.

Jangan lupa mencatat apakah query digunakan di jalur yang sering dipanggil (API, batch job). Profiling sebaiknya dijalankan saat beban nyata, bukan hanya di sandbox. Gunakan trace distribusi response time jika tersedia.

Eksekusi plan: memahami apa yang sebenarnya dilakukan database

Execution plan menunjukkan operasi seperti sequential scan, index scan, merge join, dan hash join. Perhatikan apakah planner memilih index scan yang relevan. Ketika data bertumbuh, planner bisa mulai memilih sequential scan jika indeks tidak mencakup kolom filter atau cardinality diperkirakan tinggi.

Periksa estimasi baris (estimated rows) versus aktual. Jika estimasi jauh lebih kecil, planner mungkin memutuskan strategi non-indeks. Validasi cardinality dengan membandingkan statistik dari catalog (misalnya pg_stats atau information_schema.STATISTICS) terhadap distribusi aktual. Jika statistik tidak akurat, jalankan ANALYZE, atau atur auto_analyze untuk menjaga kesesuaian estimasi seiring pertumbuhan data.

Contoh pemeriksaan cardinality: 

SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'orders';

Jika n_distinct jauh lebih kecil dari distribusi aktual, tambahkan N_DISTINCT estimator manual sebelum running planner tergantung sistem.

Indexing dan evaluasi indeks covering

Indeks yang efektif membuat perbedaan terbesar. Tapi ketika data bertumbuh, indeks merusak keseimbangan jika kolom filter tidak selektif atau indeks terlalu luas. Terapkan evaluasi berikut:

  1. Periksa kolom filter utama: apakah filter di WHERE digunakan dalam join atau group by? Indeks multi-kolom harus mengikuti urutan filter paling selektif terlebih dahulu.
  2. Validasi cardinality: kolom dengan 90% nilai sama tidak akan membantu. Pastikan indeks diarahkan ke kolom dengan high cardinality untuk memaksimalkan selectivity.
  3. Covering index: jika query hanya memerlukan kolom-kolom yang ada di indeks, database dapat memenuhi query tanpa mengunjungi tabel utama. Tambahkan kolom lainnya di INCLUDE (PostgreSQL) atau sebagai part dari index key (MySQL) setelah pertimbangan ukuran indeks.

Jangan membuat indeks terlampau banyak; biaya insert/update bisa meledak. Saat data bertumbuh, pertimbangkan indeks partial atau filter index yang hanya melayani subset data aktif.

Parameterisasi query dan strategi pagination efisien

Parameterisasi query menahan planner untuk menyusun rencana yang konsisten. Query yang direkam dengan literal menyebabkan re-parse per nilai dan bisa memicu plan cache pollution. Gunakan prepared statement atau library ORM yang memanfaatkan parameter sehingga planner dapat memilih plan yang stabil, terutama jika data tidak berubah drastis.

Pagination tradisional OFFSET/LIMIT bikin database memindai baris sebelumnya. Ketika data bertumbuh, hitung offset besar sangat mahal. Alternatifnya gunakan keyset pagination (cursor-based) dengan kondisi seperti WHERE (created_at, id) > (?, ?). Dengan indeks yang tepat, query tetap O(log n).

Checklist diagnosa dan indikator refactor atau arsitektur ulang

Gunakan checklist berikut sebelum memutuskan refactor:

  • Profiling menunjukkan waktu disk scan dominan.
  • Execution plan mengindikasikan sequential scan karena estimasi cardinality yang salah.
  • Indeks tidak mencakup kolom ORDER BY atau JOIN yang sering dipakai.
  • Jumlah baris yang dibaca jauh lebih besar dari hasil dikirim ke aplikasi.
  • Query lama tetap lambat walaupun indeks ditambahkan; artinya arsitektur data tidak berskala.

Indikator bahwa perlu refactor lebih dalam:

  • Beberapa query mengakses tabel histori besar yang terus tumbuh: pertimbangkan partisi waktu atau table sharding.
  • Indeks menyebabkan write latency tinggi karena insert/update frekuen; bisa mempertimbangkan desain write-optimized seperti queue/persistent log sebelum commit ke read-model terindeks.
  • Ada perbedaan pola akses antara batch dan online; pertimbangkan materialized view atau CQRS untuk read-heavy.

Debugging tip: setelah tuning pastikan run profiling lagi, karena perubahan indeks bisa memperNGaruhi planner dan memicu plan cache invalidation. Selalu dokumentasikan asumsi cardinality dan indexing untuk tim.

Simpulan: Pelajaran Quake membekali kita bahwa bottleneck tidak selalu karena perubahan besar—kadang karena data yang makin banyak. Dengan profiling tepat, membaca execution plan, mengelola indeks, dan pagination efisien, kita menjaga SQL tetap scalable sebelum perlu refactor arsitektur.