Akar Masalah Sequential Scan pada JSONB Form Dinamis

Penerapan form dinamis pada aplikasi web memungkinkan entitas input berubah tanpa migrasi skema tabel secara berkala. Sejalan dengan evolusi engine rendering dan penanganan spesifikasi kontrol form web—seperti peningkatan parsing state dan handling atribut input dinamis pada engine browser Servo—aplikasi modern cenderung mengirimkan payload data fleksibel ke backend untuk disimpan langsung dalam kolom berformat jsonb di PostgreSQL.

Masalah muncul ketika volume tabel submisi membengkak hingga jutaan baris. Query pencarian atribut spesifik dalam payload sering kali mengeksekusi Sequential Scan. Kondisi ini terjadi karena PostgreSQL tidak memelihara histogram statistik mendalam untuk arbitrary key di dalam dokumen biner JSONB. Saat query filter dijalankan tanpa indeks yang kompatibel, database planner terpaksa memuat seluruh blok data tabel dari disk ke memory buffer untuk mengevaluasi ekspresi baris demi baris, memicu lonjakan I/O drastis dan latensi tinggi.

Studi Kasus Baseline

Ambil contoh tabel submisi form berikut:

CREATE TABLE form_submissions (
    id BIGSERIAL PRIMARY KEY,
    form_id UUID NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Simulasi 1.000.000 baris data
INSERT INTO form_submissions (form_id, payload, created_at)
SELECT 
    gen_random_uuid(),
    jsonb_build_object(
        'applicant_name', 'User ' || i,
        'status', (ARRAY['draft', 'submitted', 'approved', 'rejected'])[floor(random() * 4 + 1)],
        'score', floor(random() * 100)::int,
        'meta', jsonb_build_object('source', 'web_portal')
    ),
    NOW() - (i || ' minutes')::INTERVAL
FROM generate_series(1, 1000000) s(i);

Eksekusi query filter terhadap atribut status:

EXPLAIN ANALYZE
SELECT * FROM form_submissions
WHERE payload @> '{"status": "approved"}';

Hasil EXPLAIN ANALYZE sebelum indeks dibuat:

Seq Scan on form_submissions  (cost=0.00..38950.00 rows=250000 width=122) (actual time=0.045..245.812 rows=249811 loops=1)
  Filter: (payload @> '{"status": "approved"}'::jsonb)
  Rows Removed by Filter: 750189
Planning Time: 0.120 ms
Execution Time: 256.430 ms

Waktu eksekusi ~250ms pada 1 juta baris akan meningkat linier seiring pertambahan baris. Diperlukan strategi indeks terarah untuk mengubah rencana query menjadi Bitmap Index Scan atau Index Scan.

Perbandingan Strategi Indexing

1. GIN Index Default (jsonb_ops)

Secara default, indeks Generalized Inverted Index (GIN) pada JSONB menggunakan operator class jsonb_ops.

CREATE INDEX idx_submissions_payload_gin ON form_submissions USING gin (payload);
  • Mekanisme: Mengurai setiap key, value, dan elemen array di dalam dokumen menjadi token terpisah pada struktur inverted list.
  • Operator yang Didukung: @> (containment), ? (key exists), ?| (any key exists), dan ?& (all keys exist).
  • Kelemahan: Mengonsumsi kapasitas disk paling besar karena setiap leaf node dan node perantara disimpan terpisah. Write throughput akan terdegradasi secara signifikan saat intensitas INSERT/UPDATE tinggi.

2. GIN Index jsonb_path_ops

Jika kebutuhan pencarian hanya menggunakan operator containment (@>), operator class jsonb_path_ops merupakan opsi yang lebih efisien.

CREATE INDEX idx_submissions_payload_path_ops ON form_submissions USING gin (payload jsonb_path_ops);
  • Mekanisme: Tidak mengindeks key dan value secara terpisah, melainkan menghitung hash 32-bit untuk seluruh path tree (contoh: hash dari kombinasi status -> approved).
  • Kelebihan: Ukuran index bisa 30% hingga 60% lebih kecil dibandingkan jsonb_ops, menurunkan footprint cache di shared buffers dan mempercepat Bitmap Index Scan.
  • Kelemahan: Tidak mendukung operator eksistensi key tunggal seperti payload ? 'status'.

3. B-Tree Functional (Expression) Index

Untuk field form dinamis yang sering diakses sebagai parameter filtering utama, sorting (ORDER BY), atau perbandingan rentang (>, <, BETWEEN), GIN tidak optimal. Gunakan B-Tree index berbasis ekspresi.

-- B-Tree untuk exact match & sorting string
CREATE INDEX idx_submissions_payload_status ON form_submissions ((payload->>'status'));

-- B-Tree dengan type casting untuk range query angka
CREATE INDEX idx_submissions_payload_score ON form_submissions (((payload->>'score')::integer));

B-Tree functional index mengekstrak scalar value langsung dari dokumen JSONB saat data ditulis, menghasilkan struktur B-Tree standar yang mendukung sorting dua arah dan traversal binary tree dengan latensi sub-milidetik.

Evaluasi Hasil Optimasi (EXPLAIN ANALYZE)

Uji query menggunakan filter containment setelah menerapkan jsonb_path_ops:

EXPLAIN ANALYZE
SELECT * FROM form_submissions
WHERE payload @> '{"status": "approved"}';

Rencana eksekusi:

Bitmap Heap Scan on form_submissions  (cost=2412.30..28410.15 rows=250000 width=122) (actual time=14.120..52.310 rows=249811 loops=1)
  Recheck Cond: (payload @> '{"status": "approved"}'::jsonb)
  Heap Blocks: exact=13950
  ->  Bitmap Index Scan on idx_submissions_payload_path_ops  (cost=0.00..2349.80 rows=250000 width=0) (actual time=12.215..12.215 rows=249811 loops=1)
        Index Cond: (payload @> '{"status": "approved"}'::jsonb)
Planning Time: 0.145 ms
Execution Time: 62.115 ms

Uji query range dan sorting menggunakan B-Tree Expression Index:

EXPLAIN ANALYZE
SELECT id, payload->>'applicant_name', (payload->>'score')::integer
FROM form_submissions
WHERE (payload->>'score')::integer >= 95
ORDER BY (payload->>'score')::integer DESC
LIMIT 10;

Rencana eksekusi:

Limit  (cost=0.42..1.85 rows=10 width=44) (actual time=0.048..0.065 rows=10 loops=1)
  ->  Index Scan Backward using idx_submissions_payload_score on form_submissions  (cost=0.42..71420.50 rows=500000 width=44) (actual time=0.046..0.061 rows=10 loops=1)
        Index Cond: (((payload ->> 'score'::text))::integer >= 95)
Planning Time: 0.118 ms
Execution Time: 0.088 ms

Latensi terpangkas dari ratusan milidetik menjadi di bawah 0.1 ms berkat transisi langsung ke B-Tree Index Scan terbalik (Backward) tanpa proses sorting in-memory tambahan.

Mitigasi Bloat dan Menjaga Write Throughput

Index GIN rentan mengalami penumpukan data (bloat) saat operasi write berjalan kontinu, karena PostgreSQL tidak menggunakan mekanisme balanced tree biasa untuk memperbarui inverted list secara real-time.

1. Pemanfaatan Fastupdate dan Tuning Pending List

Secara default, GIN menggunakan mekanisme pending list (fastupdate = on). Item yang diinsert disimpan sementara di buffer non-terurut dan digabungkan ke struktur utama via proses vacuuming.

-- Sesuaikan limit pending list per tabel untuk menghindari spike saat auto-vacuum flush
ALTER INDEX idx_submissions_payload_path_ops SET (fastupdate = on, gin_pending_list_limit = '4MB');
Catatan: Nilai gin_pending_list_limit yang terlalu tinggi meningkatkan risiko query sewaktu-waktu mengalami stall, karena proses SELECT terpaksa membersihkan pending list jika ukurannya melebihi batas sebelum query dapat dijalankan.

2. Partial Indexing untuk Reduksi Ukuran

Jangan mengindeks seluruh dokumen jika pencarian hanya menyasar data aktif atau status tertentu:

-- Hanya mengindeks data yang berstatus submitted atau draft
CREATE INDEX idx_active_submissions ON form_submissions USING gin (payload jsonb_path_ops)
WHERE (payload->>'status') IN ('submitted', 'draft');

Langkah ini menekan ukuran index hingga fraksi kecil dari tabel utama dan mengisolasi overhead indexing dari baris data yang berstatus arsip.

3. Rekonstruksi Index Tanpa Lock Meja

Ketika bloat sudah terjadi pada tabel produksi berkepadatan tinggi, jalankan pembersihan tanpa mengunci operasi DML (Data Manipulation Language):

REINDEX INDEX CONCURRENTLY idx_submissions_payload_path_ops;

Metode CONCURRENTLY membangun struktur indeks baru secara paralel sebelum menukar referensi internal sistem katalog, memastikan sistem form dinamis tetap melayani input pengguna tanpa insiden locking.