Strategi Indexing dan Pagination diperlukan segera ketika query transaksi lambat mulai menghambat laporan waktu nyata. Artikel ini langsung memaparkan tanda-tanda query bermasalah pada tabel transaksi besar dan bagaimana indexing, partitioning ringan, serta pilihan pagination offset versus cursor bekerja bersama untuk meredam pertumbuhan latency.

Intinya, Anda akan melihat cara mengukur dampak pertumbuhan data, tuning indeks berdasarkan pola akses nyata, serta alternatif fallback seperti materialized view dan cache ketika indexing saja tidak cukup.

Identifikasi Query Transaksi Lambat

Query transaksi yang lambat biasanya muncul pada endpoint laporan, dashboard, atau API history. Cara praktis menegakkan diagnosis adalah:

  • Mengumpulkan slow query log atau output pg_stat_statements/equivalent DBMS.
  • Menggunakan EXPLAIN (ANALYZE, BUFFERS) untuk melihat apakah query melakukan sequential scan terhadap ratusan juta baris.
  • Mengukur latensi dari layer aplikasi untuk memahami frekuensi dan pola (hot filter, window waktu, nilai status).

Volume tabel transaksi yang terus bertambah memaksa indeks yang tidak sesuai kalah. Misalnya, query yang mengambil 100 baris terakhir berdasarkan created_at dan status harus terlihat apakah ada indeks komposit di kolom-kolom tersebut.

Metrik Mutlak untuk Dipantau

  • Time per query: rata-rata latency, persen 95, 99 untuk endpoint query berat.
  • Rows returned: jumlah baris besar menunjukkan scanning tanpa filter efektif.
  • Buffer hits/misses: banyak read IO menunjukkan kurangnya indeks atau partitioning.

Strategi Indexing yang Efektif

Tuning indeks harus mempertimbangkan pola WHERE, ORDER BY, dan JOIN query transaksi. Beberapa prinsip praktis:

  • Buat indeks komposit berurutan sesuai urutan filtering. Jika query Anda: WHERE status = 'settled' AND created_at >= '2024-01-01' ORDER BY created_at DESC, maka urutan indeks harus (status, created_at DESC).
  • Gunakan indeks partial bila hanya subset kecil data yang diakses, misalnya: 
    CREATE INDEX idx_transactions_settled ON transactions (created_at DESC) WHERE status = 'settled';
  • Hindari terlalu banyak indeks pada kolom update tinggi kecuali diperlukan karena biaya INSERT/UPDATE akan meningkat.

Jika query juga melakukan join ke tabel lain (misalnya customer_id), pastikan kolom join juga terindeks dan konsisten dengan tipe data.

Partitioning Ringan untuk Tabel Transaksi

Partitioning membantu mengurangi jumlah baris yang perlu dipindai. Untuk pertumbuhan moderat, pertimbangkan:

  • Range partition by date: bagi berdasarkan bulan atau minggu, sehingga query rentang waktu hanya menyentuh beberapa partition.
  • List partition by status bila query sering memfilter status tertentu.

Partitioning ringan seperti ini tidak harus mengganti arsitektur—cukup membuat partition baru saat bulan baru tiba, dan pastikan constraint exclusion aktif (PostgreSQL) agar planner memfilter partition yang relevan.

Catatan trade-off: partition menambah biaya pada DDL dan perlu automasi perawatan. Jangan lakukan partitioning jika query Anda tidak memakai filter yang bisa memanfaatkan partition key.

Pagination: Offset vs Cursor

Pagination berpengaruh besar terhadap waktu respons. Offset pagination (misalnya LIMIT 50 OFFSET 5000) membuat database memindai semua baris sebelum offset dan tidak ideal untuk tabel transaksi bertumbuh.

Prinsip yang lebih scalable adalah cursor atau keyset pagination yang menggunakan filter pada kolom terurut:

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

Keyset pagination hanya membaca dafar baris dari posisi terakhir, tidak tergantung offset besar dan langsung memanfaatkan indeks ORDER BY. Pastikan nilai kunci (misalnya created_at) tersedia di frontend sebagai parameter.

Gunakan offset pagination hanya untuk halaman awal yang terbatas dan ketika jumlah total halaman perlu ditampilkan. Jika pengguna sering melompat ke halaman ke-200, pertimbangkan menampilkan link ke titik waktu atau menggunakan caching.

Metrik Monitoring dan Langkah Tuning)

Langkah-langkah tunning yang harus rutin dilakukan:

  1. Analisis EXPLAIN ANALYZE untuk memastikan indeks digunakan.
  2. Periksa pg_stat_user_indexes untuk melihat indeks yang jarang digunakan dan hapus jika tidak perlu.
  3. Evaluasi seq_scan vs idx_scan untuk query target dan adjust planner statistik (ANALYZE).
  4. Monitoring I/O: jika banyak random read, pertimbangkan menambah buffer atau memperbaiki indeks.

Jika Anda tidak memperoleh perbaikan cukup dari indeks, evaluasi reorganisasi tabel (clustered index) atau menambah index bertingkat (covering index dengan kolom tambahan di SELECT). Jangan lupa membersihkan dead tuples dengan VACUUM jika ada banyak update.

Fallback: Materialized View dan Caching

Ketika indeks maupun partition tidak memadai akibat kompleksitas agregasi, pertimbangkan fallback berikut:

  • Materialized view untuk agregasi berat (jumlah transaksi per hari) yang diperbarui periodik atau via REFRESH MATERIALIZED VIEW CONCURRENTLY.
  • Cache hasil query di layer aplikasi (Redis, Memcached) untuk query yang jarang berubah tapi sering dibaca.

Materialized view dan cache harus disinkronkan dengan pipeline data. Buat mekanisme invalidasi berbasis event (misal: cache expired atau post-commit update) agar tidak menyajikan data usang.

Dengan kombinasi identifikasi query, tuning indeks yang sesuai, partitioning ringan, dan pagination keyset, Anda dapat mempertahankan latensi rendah meskipun tabel transaksi terus bertumbuh. Gunakan fallback materialized view dan caching hanya ketika operasi real-time tidak lagi memungkinkan.