Ketika data tumbuh, bottleneck SQL muncul bukan karena engine lupa apa yang harus dilakukan, tetapi karena situasi berubah—data berat, indeks tidak lagi cocok, dan pola akses terdistorsi. Untuk mengatasi query lambat, artikel ini langsung menjelaskan langkah diagnosis, evaluasi indeks, serta pendekatan pagination dan partitioning yang menjaga latency tetap stabil.

1. Diagnosa Query Lambat dengan Fokus Situasional

Langkah pertama adalah mengumpulkan situasi: tabel mana yang tumbuh paling cepat, pola filter apa yang digunakan, dan apakah query masih menggunakan indeks yang relevan. Jangan langsung memikirkan caching atau hardware; pahami konteks terkini. Fokuskan pada query yang paling sering dieksekusi, terutama gabungan JOIN, filter waktu, atau agregasi berulang.

1.1 Gunakan EXPLAIN untuk membaca situasi

Jalankan EXPLAIN atau EXPLAIN ANALYZE di database Anda untuk melihat rencana eksekusi aktual. Titik kunci: perhatikan scan penuh tabel besar atau indeks yang tidak digunakan.

EXPLAIN ANALYZE SELECT o.id, o.total FROM orders o WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days' AND o.status = 'paid';

Perhatikan kolom seperti Seq Scan vs Index Scan, estimasi baris dan waktu. Situasi berubah saat data bertambah, jadi ulangi analisis secara berkala.

1.2 Catat metrik waktu dan frekuensi

Gunakan monitoring query (misalnya slow query log atau pg_stat_statements) untuk mencatat berapa lama query berjalan dan berapa kali dipanggil. Salah satu kesalahan umum adalah hanya mengoptimalkan query yang lama tapi jarang dipakai; prioritaskan yang berdampak pada latency sistem.

2. Evaluasi Indeks Saat Data Membengkak

Indeks yang dibuat saat tabel kecil bisa menjadi beban saat data tumbuh. Situasi baru memerlukan evaluasi ulang: apakah indeks masih mencakup kolom filter utama, apakah urutan kolom tepat, dan apakah ada indeks yang jarang digunakan namun memperlambat penulisan.

2.1 Prioritaskan indeks berdasarkan pola filter

Jika query paling sering memfilter pada created_at dan status, pastikan indeks komposit mencerminkan urutan kolom yang digunakan dalam WHERE dan ORDER BY. Contoh:

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

Urutan kolom penting karena database hanya bisa menggunakan bagian awal indeks. Situasi akses berubah saat aplikasi menambahkan filter baru; pantau dan ubah indeks jika diperlukan.

2.2 Hapus indeks yang tidak lagi relevan

Indeks tambahan membebani operasi write. Gunakan data penggunaan indeks (misalnya pg_stat_user_indexes) untuk eliminasi. Hati-hati dengan indeks berlebihan—bisa jadi menyelamatkan sedikit read tapi memperburuk insert/update saat data terus bertambah.

3. Menjaga Latency Stabil dengan Pagination dan Partitioning

Ketika tabel terus tumbuh, salah satu akibatnya adalah scan scan besar yang memperlambat pagination. Strategi situasional: batasi jumlah data yang diproses per permintaan, dan pisahkan data berdasarkan kriteria logis.

3.1 Pagination berbasis posisi, bukan offset tinggi

Offset besar memaksa server melompati baris sebelum halaman dimulai; waktu eksekusi membengkak seiring offset. Gunakan pagination berbasis cursor (misalnya batasan nilai created_at atau id):

SELECT * FROM orders WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 50;

Cursor mempertahankan situasi terakhir tanpa memindai semua baris sebelumnya. Untuk mempertahankan konsistensi, simpan batas ini di client atau server state.

3.2 Partitioning untuk mengisolasi data besar

Ketika volume data bulanan sangat besar, partitioning memecah tabel menjadi segmen logis (misalnya per bulan). Ini menjaga situasi yang di query tetap kecil. Pastikan query menyertakan kolom partition key agar optimizer hanya membaca partisi relevan.

Trade-off: partitioning memerlukan sedikit lebih banyak manajemen (pemeliharaan partisi lama, reindex partisi baru), namun sangat efektif menjaga latency saat hanya sebagian data dibaca.

4. Kesimpulan dan Panduan Debug Praktis

Mengatasi query lambat karena pertumbuhan data berarti memahami situasi yang berubah. Jangan berpikir model perlu "mengingat" semua; cukup sediakan konteks terkini: query apa yang digunakan, indeks mana yang relevan, dan bagaimana data dibagi.

Untuk debugging cepat:

  • Identifikasi query lambat paling sering dijalankan.
  • Jalankan EXPLAIN untuk melihat apakah indeks digunakan.
  • Periksa statistik indeks dan hapus yang tidak terpakai.
  • Implementasikan pagination cursor dan pertimbangkan partitioning logis.

Dengan pendekatan situasional seperti di atas, backend engineer bisa langsung bertindak saat data tumbuh tanpa menunggu "memory" lama. Fokus pada konteks: pertumbuhan data, pola akses, dan kebutuhan performa saat ini.