Menjawab akar masalah query lambat pada DID terdistribusi

Query lambat pada data DID terdistribusi sering muncul karena volume besar, pola pencarian kompleks, dan indeks yang salah konfigurasi. Pendekatan hybrid—gabungan partial, multicolumn, dan pengindeksan berdasarkan kardinalitas—memungkinkan penyempurnaan tanpa menduplikasi seluruh tabel. Artikel ini menjelaskan langkah-langkah praktis untuk menemukan bottleneck SQL, mendesain struktur indeks yang sesuai, mengelola pagination untuk pertumbuhan data, serta menghubungkan keputusan teknis dengan wawasan Steve Klabnik tentang nilai DID.

Mengidentifikasi bottleneck SQL di data DID

Langkah pertama adalah menangkap query lambat melalui slow query log atau tracing distribusi. Fokus di tabel event DID yang menampung ribuan perubahan per hari. Contoh query yang sering tampil pada log:

SELECT did, event_type, created_at
FROM did_events
WHERE namespace = $1
  AND created_at BETWEEN $2 AND $3
ORDER BY created_at DESC
LIMIT 50;

Gunakan EXPLAIN (ANALYZE, BUFFERS) untuk memahami apakah query melakukan seq scan, seberapa banyak baris diproses, dan apakah disk I/O menjadi hambatan. Dengan distribusi data, gabungkan hasil dari beberapa shard agar gambaran bottleneck lebih utuh.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Perhatikan apakah planner memilih indeks, berapa banyak baris dibaca, dan apakah ada temporary files akibat sort besar. Bila planner memilih sequential scan karena filter tidak terindeks, langkah berikutnya adalah merancang indeks hybrid.

Strategi indeks hybrid untuk DID

Partial index untuk filter umum

Partial index menambahkan indeks hanya pada subset baris. Jika banyak query hanya melihat event aktif (misal status = 'confirmed'), definisikan partial index:

CREATE INDEX idx_did_events_confirmed
ON did_events (namespace, created_at DESC)
WHERE status = 'confirmed';

Partial index tetap kecil dan membantu planner karena cardinality filter rendah. Gunakan opsi ini bila skenario query terbatas pada subset data tertentu.

Multicolumn index dengan urutan akses

Kalau query mengurut berdasarkan created_at DESC dan memfilter namespace, urutan kolom menentukan apakah indeks bisa dipakai untuk sort. Index multikolom bisa dirancang sebagai:

CREATE INDEX idx_did_events_ns_created
ON did_events (namespace, created_at DESC);

Indeks ini mempercepat filtering sekaligus sorting tanpa perlu ORDER BY tambahan. Perhatikan bahwa PostgreSQL hanya memanfaatkan indeks jika urutan column di query sesuai definisi. Hindari menambahkan kolom tambahan yang jarang dipakai karena akan memperbesar biaya penulisan.

Cardinality-aware indexing

Untuk kolom dengan kardinalitas tinggi seperti did, manfaatkan index-only scan dengan menambahkan kolom data yang terbaca terus-menerus:

CREATE INDEX idx_did_events_did_namespace
ON did_events (did, namespace)
INCLUDE (event_type, created_at);

Index INCLUDE menjaga data yang sering dibaca agar dapat disajikan tanpa kembali ke tabel. Namun, perhatikan trade-off: setiap penulisan harus memperbarui indeks tambahan, jadi gunakan untuk kolom yang memang sering ditanyakan.

Pagination efisien saat data DID tumbuh

Pertumbuhan data DID membuat pagination berbasis offset mahal karena database harus melewati jutaan baris. Ganti dengan pagination keyset menggunakan created_at atau kunci unik lain:

SELECT did, event_type, created_at
FROM did_events
WHERE namespace = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Strategi ini memanfaatkan indeks yang sudah ada (namespace + created_at + id) dan menjaga performa saat pengguna menelusuri halaman jauh. Jika layanan terdistribusi, pastikan token pagination menyertakan informasi shard agar tidak terjadi duplikasi.

Simulasikan pertumbuhan data dengan script batch insert. Perhatikan bahwa data DID sering mengalami ekspansi eksponensial; lakukan eksperimen sederhana:

INSERT INTO did_events (...)
SELECT ... FROM generate_series(1, ?)

Setelah menambah 10 juta baris, jalankan query pagination untuk mengobservasi waktu respon. Hal ini mengukur apakah indeks tetap efektif atau perlu dioptimalkan ulang.

Observasi performa nyata dan fallback ketika indeks belum cukup

Observasi awal menunjukkan: partial index untuk status “confirmed” mempercepat query sebesar 40% dibandingkan seq scan, sedangkan keyset pagination mengurangi waktu respon di halaman >100 dari 3 detik ke 200 ms. Namun, indeks tidak menyelesaikan semua. Saat definisi query berubah (misal filter baru pada event_type), planner bisa kembali melakukan seq scan.

Fallback praktis:

  • Materialized view untuk snapshot data yang jarang berubah, diperbarui secara berkala.
  • Pre-aggregated table untuk query analitik, sehingga operasi rumit tidak membebani tabel utama.
  • Denormalisasi pada kolom yang sering digunakan bersama, sehingga penggabungan table dapat dihindari.

Selalu pantau slow query log pasca perubahan indeks; seringkali planner memilih indeks lain karena statistik berubah. Gunakan ANALYZE did_events setelah bulk insert untuk membantu planner.

Referensi Steve Klabnik menekankan pentingnya memahami mengapa DID penting dan bagaimana arsitektur menyokongnya; keputusan indeks harus selaras dengan nilai DID yang dimaksud—apakah kecepatan, integritas, atau auditabilitas yang paling bernilai.

Kesimpulan

Indexing hybrid pada dataset DID terdistribusi berarti pertama-tama menemukan query lambat, kemudian menyusun kombinasi partial, multicolumn, dan indeks berdasarkan kardinalitas agar planner tetap efisien. Pagination keyset menjaga kelincahan meskipun data terus tumbuh. Ketika indeks belum menyelesaikan seluruh kebutuhan, fallback seperti materialized view atau pre-aggregation menyelamatkan performa. Dengan observasi nyata dan pemahaman terhadap konteks DID, langkah-langkah ini membantu menjaga pengalaman pengguna tanpa mengorbankan konsistensi data.