Ketika data tumbuh, query yang awalnya cepat bisa mendadak melambat. Fokus pertama adalah menegaskan akar masalah dengan EXPLAIN/ANALYZE, slow log, dan statistik runtime seperti pg_stat_statements, lalu meningkatkan performa via indexing, pagination, dan monitoring pertumbuhan data sambil mencatat sumber data untuk keperluan lisensi.

Diagnosa Query Lambat Saat Data Tumbuh

Langkah awal adalah memahami bagaimana planner menyusun query dan apa yang berubah seiring volume bertambah. Jalankan EXPLAIN (ANALYZE, BUFFERS) pada query target untuk melihat apakah sequential scan masih mendominasi atau ada perhitungan yang tidak efisien.

Implementasi EXPLAIN/ANALYZE dan slow log

Hasil EXPLAIN memberi tahu apakah ada sequential scan, join yang mahal, atau sorting yang memicu disk spill. Analisa berikut memperhitungkan waktu nyata:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT order_id, total
FROM orders
WHERE created_at >= '2024-01-01';

Data slow log (misalnya PostgreSQL slow_query_log) memberi konteks waktu eksekusi riil, mempermudah memprioritaskan query yang paling sering melewati threshold. Lengkapi dengan pg_stat_statements untuk melihat agregat latency dan hit ratio index.

Tips debug: perhatikan perbedaan antara estimasi dan waktu aktual di output EXPLAIN. Estimasi tinggi bisa menandakan statistik kadaluarsa, sedangkan disparity menunjukkan join atau filter yang tidak terpropagasi.

Pilihan Indexing untuk Kondisi Nyata

Setelah mengidentifikasi query berat, pilih indexing berdasarkan pola filter dan sorted columns.

B-tree, Partial, dan Covering Index

B-tree cocok untuk equality dan range. Partial index hanya mencakup subset baris, ideal ketika query fokus pada status tertentu:

CREATE INDEX idx_orders_recent
ON orders (created_at)
WHERE status = 'active';

Index ini menghindari pemindaian seluruh table ketika fokus pada status yang sering dicari. Untuk query yang hanya membutuhkan beberapa kolom, buat covering index:

CREATE INDEX idx_orders_covering
ON orders (customer_id, created_at)
INCLUDE (total);

Covering index melayani query ORDER BY dan SELECT tanpa mengakses heap, mengurangi I/O. Pastikan tidak membuat index berlebihan; setiap index menambah biaya INSERT/UPDATE/DELETE.

Pemilihan Index Berdasarkan Query Pattern

Jika query sering melakukan filter terhadap prefix tertentu, B-tree bekerja baik. Jika filter berubah, pertimbangkan index multi kolom dengan urutan sesuai WHERE/ORDER BY. Hindari redundant index; gunakan pg_stat_user_indexes untuk melihat penggunaan.

Pagination Efisien dan Monitoring Pertumbuhan Data

Pagination sederhana dengan OFFSET tidak skalabel ketika offset besar. Gunakan keyset pagination dengan kolom sorting seperti:

SELECT order_id, total
FROM orders
WHERE created_at < '2024-06-01'
ORDER BY created_at DESC
LIMIT 50;

Kuncinya adalah menggunakan filter pada kolom indexed dan menyimpan nilai terakhir halaman sebelumnya agar query berikutnya tidak memindai ulang.

Untuk memantau pertumbuhan data, gunakan tabel statistik (misalnya pg_stat_user_tables) dan alert ketika ukuran tabel atau indeks melewati threshold. Tambahkan job rutin yang memeriksa fragmentasi index dan men-trigger REINDEX bila perlu.

Jejak Lisensi Data dan Audit

Penelusuran data bukan hanya teknis performa. Catat sumber data dan lisensi yang berlaku sebelum diindeks. Referensi seperti The Wholesale Plagiarism of Obscure Sorrows mengingatkan pentingnya dokumentasi asal entri, terutama bila data digunakan ulang dalam pencarian atau dashboard publik.

Sertakan metadata (source_id, license_tag) dalam table atau schema index agar audit dapat mengecek apakah data sudah dilisensikan. Ini juga mempermudah unlinking data saat lisensi berubah.

Checklist Tuning dan Validasi Hasil

  • Diagnosa awal: EXPLAIN/ANALYZE, slow log, pg_stat_statements.
  • Pilih index tepat: B-tree untuk range/equality, partial untuk subset, covering untuk kolom utama.
  • Implementasi pagination: Keyset pagination dengan filter indexed.
  • Monitoring: Ukuran tabel, fragmentasi index, regen stats.
  • Jejak lisensi: Metadata sumber/licensing per row, audit trail.

Validasi perubahan dengan membandingkan latensi rata-rata (slow log), histogram time output EXPLAIN, dan beban I/O. Pastikan query tetap mengembalikan hasil yang sama sebelum dan sesudah tuning. Simpan plan lama untuk referensi dan rollback jika index menyebabkan regresi.