Pendahuluan

Query reporting yang lambat biasanya disebabkan oleh dua penghambat utama: lock contention di level database dan indexing yang tidak optimal. Artikel ini langsung menunjukkan langkah diagnostik praktis untuk melihat kedua aspek tersebut dan menyajikan mitigasi yang dapat diterapkan di produksi.

Langkah-langkah berikut perlu dilakukan secara berurutan: baca log DB, gunakan EXPLAIN/ANALYZE, pantau lock contention dan I/O, identifikasi index yang kurang optimal, pertimbangkan partial atau covering index, serta siapkan strategi pagination atau batching saat data tumbuh besar.

1. Memulai Diagnostik: Log Database hingga EXPLAIN

Baca log database secara sistematis

Mulai dari log slow query atau statement logging (misal PostgreSQL log_min_duration_statement). Catat query yang dieksekusi selama waktu reporting lambat: timestamp, user, dan teks query. Jika platform menyediakan, gabungkan dengan data query profiler untuk melihat durasi eksekusi dan timestamp.

Gunakan EXPLAIN/ANALYZE untuk memetakan rencana eksekusi

Ambil salah satu query bermasalah dan jalankan EXPLAIN (ANALYZE, BUFFERS). Fokus pada:

  • Sequential scan terhadap tabel besar.
  • Nested loop yang tidak perlu karena join tanpa index.
  • Readahead atau heap scan yang menunjukkan I/O tinggi.
  • Actual time dan rows per node.

Contoh potongan EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS) SELECT r.id, u.email, SUM(r.amount) FROM reports r JOIN users u ON u.id = r.user_id WHERE r.created_at > now() - interval '7 days' GROUP BY r.id, u.email;

Perhatikan apakah planner memilih sequential scan karena tidak ada index yang sesuai. Catat juga perbedaan antara plan estimate dengan actual rows untuk mendeteksi statistik usang.

2. Identifikasi Lock Contention dan I/O Bottleneck

Pantau lock contention secara real time

Gunakan query monitoring seperti di PostgreSQL pg_stat_activity dan pg_locks atau MySQL INFORMATION_SCHEMA.PROCESSLIST. Fokus pada transaksi yang:

  • Mengunci tabel atau baris yang sama berulang kali.
  • Mengalami wait event seperti Lock, IO, BufferPin.
  • Lama idle sedang menunggu commit.

Ketika query reporting membaca data yang sering diperbarui, locking pada tabel source bisa membuat query lain menunggu. Pertimbangkan opsi snapshot isolation atau memindahkan reporting ke replica read-only untuk mengurangi contention.

Analisa I/O dan throughput

Monitoring sistem (mis. iostat, vmstat, atau metric DB seperti buffer cache hit ratio) membantu mendeteksi apakah query men-trigger desakan disk. Jika buffer hit ratio rendah, query melakukan banyak random I/O, yang mengindikasikan scan tabel besar.

Skalakan dalam konteks reporting dengan:

  • Menjalankan query di replica read-only untuk memisahkan beban read-heavy dari transactional write.
  • Memperbesar buffer pool atau shared_buffers hingga batas wajar.

3. Indexing: Evaluasi, Perbaiki, dan Tambahbagi

Identifikasi index yang tidak digunakan atau kurang optimal

Gunakan informasi dari EXPLAIN untuk melihat apakah planner menggunakan index terhadap kolom filter atau join. Jika tidak, periksa:

  • Status statistik kolom (misalnya ANALYZE di PostgreSQL).
  • Existence index pada kolom yang digunakan di WHERE, JOIN, ORDER BY.
  • Kolom dengan cardinality rendah yang perlu digabung ke partial index (lihat bagian berikut).

Hapus index yang jarang digunakan karena setiap insert/update akan mempengaruhi biaya write.

Pertimbangkan partial dan covering index

Partial index membantu menyingkat scanning pada subset data penting, misalnya hanya baris 30 hari terakhir yang diperlukan untuk reporting:

CREATE INDEX idx_reports_recent ON reports (user_id, created_at) WHERE created_at > now() - interval '30 days';

Covering index (menampung semua kolom yang dipilih) menghindari lookup tambahan ke tabel utama. Pastikan index tidak tumbuh terlalu besar karena dapat menyebabkan bloat.

4. Strategi Pagination/Batching untuk Dataset Besar

Gunakan pagination berbasis cursor atau timestamp

Reporting yang memproses jutaan baris harus menghindari OFFSET besar. Gunakan pagination cursor, misalnya:

SELECT id, amount FROM reports WHERE created_at > :last_timestamp ORDER BY created_at LIMIT 10_000;

Setiap iterasi menyimpan created_at terakhir. Ini menjaga planner menggunakan index yang sama dan menghindari re-scan dari awal.

Batching dan materialized view

Menggabungkan data dalam batch kecil atau menggunakan materialized view untuk pre-aggregation dapat sangat mempercepat reporting. Pertimbangkan:

  • Batch insert ke tabel reporting setiap interval (misalnya tiap 5 menit).
  • Materialized view dengan refresh incremental jika tersedia.
  • ETL ringan yang menyalin subset data ke schema khusus reporting.

Pastikan proses ini diatur agar tidak mengunci tabel utama saat penulisan.

5. Validasi dan Pengawasan Berkelanjutan

Setelah perbaikan, ulangi EXPLAIN/ANALYZE untuk melihat perubahan dalam rencana eksekusi. Terus pantau metric lock wait time, buffer hit ratio, dan duration query reporting. Dokumentasikan perubahan index serta alasan trade-off antara read performance dan write cost.

Jangan lupa mempertimbangkan rollback: jika partial index memperlambat write kritikal, siapkan skrip drop index sebelum deployment lanjutan.

Kesimpulan

Dengan menjalankan langkah diagnostik log DB, EXPLAIN, monitoring lock/I/O, serta memperbaiki indexing dan batching data, Anda dapat mengurangi lock contention dan mempercepat query reporting lambat tanpa mengorbankan stabilitas produksi. Pendekatan bertahap ini memungkinkan trade-off berdasarkan kebutuhan sistem Anda.