Menyimpan data semi-terstruktur pada kolom JSONB di PostgreSQL umum digunakan saat skema data sering berubah. Masalah timbul ketika volume tabel melampaui ratusan ribu baris: pencarian field dinamis menggunakan operator containment (@>) mendadak lambat. PostgreSQL default-nya mengeksekusi Sequential Scan, membaca seluruh page tabel dari disk ke memori hanya untuk mencocokkan segmen JSON.

Solusinya adalah mengimplementasikan index GIN (Generalized Inverted Index) dengan operator class jsonb_path_ops, lalu mengintegrasikannya ke layer aplikasi Actix Web via query terparameter SQLx.

Akar Masalah: Sequential Scan pada Kolom JSONB

Saat menjalankan query filter seperti WHERE payload @> '{"status": "processed"}' tanpa index spesifik, perencana query PostgreSQL (query planner) tidak memiliki referensi posisi data. Akibatnya, database mengeksekusi Seq Scan: setiap baris dibaca, kolom JSONB didekompresi, dan path dievaluasi satu per satu.

Metrik pada tabel berisi 1.000.000 baris sebelum dioptimasi:

EXPLAIN ANALYZE
SELECT id, payload FROM user_events
WHERE payload @> '{"status": "completed", "tier": "premium"}';

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on user_events  (cost=0.00..38542.00 rows=980 width=128) (actual time=0.112..285.450 rows=1020 loops=1)
  Filter: (payload @> '{"tier": "premium", "status": "completed"}'::jsonb)
  Rows Removed by Filter: 998980
Planning Time: 0.145 ms
Execution Time: 285.612 ms

Latensi ~285 ms pada level basis data memblok thread async worker dan menghabiskan connection pool Actix Web di bawah beban konkurensi tinggi.

Memilih Operator Class: jsonb_ops vs jsonb_path_ops

Index GIN standar di PostgreSQL menyediakan dua operator class:

  • jsonb_ops (default): Membuat entri index terpisah untuk setiap key, value, dan path elemen. Mendukung operator @>, ?, ?|, dan ?&. Ukuran index lebih besar dan proses build lebih lambat.
  • jsonb_path_ops: Menghitung hash CRC32 untuk setiap kombinasi path dan value (misal: hash tunggal untuk status/completed). Ukuran index hingga 50% lebih kecil dibanding default dan operasi baca @> berjalan lebih cepat. Kekurangannya: tidak mendukung query berbasis keberadaan key saja (operator ?).

Jika kebutuhan API hanya memfilter kesamaan nilai (equality match) pada objek JSONB, gunakan jsonb_path_ops.

Migrasi SQLx: Menambahkan Index GIN

Buat migrasi menggunakan SQLx CLI. Gunakan CREATE INDEX CONCURRENTLY agar tabel tidak terkena lock eksklusif selama pembuatan index.

-- migrations/20260330000001_create_gin_index_on_user_events.sql
-- Disable transaction block for CONCURRENTLY
-- Step Up:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_user_events_payload_path_ops
ON user_events USING gin (payload jsonb_path_ops);

-- Step Down:
-- DROP INDEX CONCURRENTLY IF EXISTS idx_user_events_payload_path_ops;
Catatan: Jalankan sqlx migrate run. Jika menggunakan CONCURRENTLY di dalam migration runner otomatis, pastikan tool migrasi tidak membungkus statement di dalam blok transaksi (BEGIN ... COMMIT) karena PostgreSQL menolak pembuatan index konkuren di dalam transaksi.

Implementasi Handler pada Actix Web

Gunakan macro compile-time check sqlx::query_as! untuk memastikan tipe data aman. Filter parameter JSON dikirim via serde_json::Value agar diproses sebagai prepared statement yang aman dari SQL injection.

use actix_web::{web, HttpResponse, Responder};
use serde::{Deserialize, Serialize};
use sqlx::PgPool;
use uuid::Uuid;

#[derive(Debug, Serialize, Deserialize, sqlx::FromRow)]
pub struct UserEvent {
    pub id: Uuid,
    pub payload: serde_json::Value,
}

#[derive(Debug, Deserialize)]
pub struct QueryFilter {
    pub status: String,
    pub tier: String,
}

// ponytail: jika query hanya memfilter 1 key statis, gunakan generated column + btree.
pub async fn get_filtered_events(
    pool: web::Data<PgPool>,
    filter: web::Query<QueryFilter>,
) -> impl Responder {
    let criteria = serde_json::json!({
        "status": filter.status,
        "tier": filter.tier
    });

    let result = sqlx::query_as!(
        UserEvent,
        r#"
        SELECT id, payload
        FROM user_events
        WHERE payload @> $1
        "#,
        criteria
    )
    .fetch_all(pool.get_ref())
    .await;

    match result {
        Ok(events) => HttpResponse::Ok().json(events),
        Err(err) => {
            eprintln!("Database error: {}", err);
            HttpResponse::InternalServerError().finish()
        }
    }
}

Alternatif lebih ringkas jika pola query hanya menargetkan 1 property konstan: buat PostgreSQL generated column (payload->>'status') STORED dan pasang B-Tree index standar.

Metrik Validasi: EXPLAIN ANALYZE Pasca-Optimasi

Jalankan kembali profiling query setelah index aktif:

EXPLAIN ANALYZE
SELECT id, payload FROM user_events
WHERE payload @> '{"status": "completed", "tier": "premium"}';

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on user_events  (cost=24.32..1842.10 rows=980 width=128) (actual time=0.421..2.115 rows=1020 loops=1)
  Recheck Cond: (payload @> '{"tier": "premium", "status": "completed"}'::jsonb)
  Heap Blocks: exact=812
  ->  Bitmap Index Scan on idx_user_events_payload_path_ops  (cost=0.00..24.08 rows=980 width=0) (actual time=0.280..0.280 rows=1020 loops=1)
        Index Cond: (payload @> '{"tier": "premium", "status": "completed"}'::jsonb)
Planning Time: 0.180 ms
Execution Time: 2.310 ms

Eksekusi terpangkas dari 285.6 ms menjadi 2.3 ms (peningkatan kecepatan ~124x). PostgreSQL beralih dari Seq Scan ke Bitmap Index Scan langsung ke leaf node index GIN.

Trade-off: Write Overhead dan Index Bloat

Meskipun mengakselerasi proses baca, index GIN memiliki karakteristik arsitektural yang berdampak negatif pada beban tulis tinggi (write-heavy):

1. Amplifikasi Biaya INSERT/UPDATE

Setiap dokumen JSONB dipecah menjadi beberapa item posting list pada struktur GIN. Menulis satu baris baru berarti menulis ke banyak node tree. PostgreSQL memitigasi ini menggunakan fastupdate (pending list). Namun, ketika batas gin_pending_list_limit (default 4MB) terlampaui, transaksi insert berikutnya akan menanggung beban pemindahan data ke struktur index utama (flushing), memicu lonjakan latensi (latency spikes).

2. Index Bloat

Penghapusan atau pembaruan baris JSONB tidak langsung memadatkan struktur leaf node GIN. Tabel dengan churn data tinggi (banyak UPDATE/DELETE) mengalami pembengkakan ukuran index (bloat) yang menghabiskan memori cache (buffer pool). Gunakan REINDEX CONCURRENTLY idx_user_events_payload_path_ops; secara berkala lewat maintenance scheduler jika rasio fragmentation memburuk.