Aplikasi full-stack Nuxt 3 yang memanfaatkan Nitro engine sering kali berinteraksi langsung dengan PostgreSQL untuk menyimpan data atribut dinamis dalam kolom bertipe JSONB. Masalah performa muncul ketika endpoint API melakukan filtering terhadap kunci atau nilai di dalam dokumen JSONB tersebut tanpa strategi pengindeksan yang tepat. Tanpa indeks, PostgreSQL terpaksa melakukan Sequential Scan pada seluruh tabel, mengakibatkan latensi server route melonjak drastis seiring bertambahnya volume data.

Diagnosa Bottleneck dengan EXPLAIN ANALYZE

Ambil skenario tabel produk e-commerce dengan skema berikut:

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Ketika Nitro handler menjalankan query filter berbasis operator containment (@>) tanpa indeks pada tabel berisi 250.000 baris:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, attributes
FROM products
WHERE attributes @> '{"brand": "Logitech", "wireless": true}';

Hasil eksekusi PostgreSQL menunjukkan bottleneck utama:

Seq Scan on products  (cost=0.00..11842.00 rows=250 width=128) (actual time=0.082..148.312 rows=140 loops=1)
  Filter: (attributes @> '{"brand": "Logitech", "wireless": true}'::jsonb)
  Rows Removed by Filter: 249860
  Buffers: shared hit=4842 read=4500
Planning Time: 0.115 ms
Execution Time: 148.384 ms

Query tersebut memakan waktu ~148 ms karena PostgreSQL membaca setiap blok disk secara sekuensial (shared hit dan read tinggi) serta membuang 249.860 baris yang tidak cocok secara manual di CPU.

Implementasi GIN Index dengan jsonb_path_ops

PostgreSQL menyediakan Generalized Inverted Index (GIN) khusus untuk tipe data semi-terstruktur. Untuk operator containment (@>), gunakan operator class jsonb_path_ops ketimbang default jsonb_ops.

  • jsonb_ops (default): Mengindeks setiap key dan value secara terpisah. Mendukung operator ?, ?|, ?&, dan @>, tetapi ukuran indeks jauh lebih besar.
  • jsonb_path_ops: Melakukan hashing terhadap keseluruhan path dan nilai (contoh: hash dari {"brand": "Logitech"}). Ukuran indeks lebih kecil hingga 50% dan pencarian operator @> lebih cepat, meski tidak mendukung pengecekan keberadaan key tunggal (?).

Terapkan indeks melalui skrip migrasi:

-- Migrasi: create_gin_index_on_products_attributes.sql
CREATE INDEX CONCURRENTLY idx_products_attributes_path_ops
ON products USING gin (attributes jsonb_path_ops);

Gunakan klausa CONCURRENTLY pada environment production agar proses pembuatan indeks tidak mengunci (exclusive lock) operasi write pada tabel.

Implementasi Server Handler Nuxt 3 Nitro

Di Nuxt 3, buat server route pada server/api/products.get.ts. Endpoint ini memvalidasi query params, menyusun payload containment JSONB secara aman, dan mengeksekusi parameter query untuk mencegah SQL Injection.

// server/api/products.get.ts
import { defineEventHandler, getQuery, createError } from 'h3';
import { pool } from '~/server/utils/db'; // Instance pg.Pool singleton

interface FilterQuery {
  brand?: string;
  wireless?: string;
}

export default defineEventHandler(async (event) => {
  const query = getQuery<FilterQuery>(event);

  // Ponytail: Validasi minimal via dictionary; upgrade ke Zod/Valibot jika schema meluas
  const filterCriteria: Record<string, any> = {};
  if (query.brand) {
    filterCriteria.brand = String(query.brand);
  }
  if (query.wireless !== undefined) {
    filterCriteria.wireless = query.wireless === 'true';
  }

  if (Object.keys(filterCriteria).length === 0) {
    throw createError({
      statusCode: 400,
      statusMessage: 'Setidaknya satu filter atribut harus disertakan.',
    });
  }

  const client = await pool.connect();
  try {
    // Eksekusi operator containment @> dengan parameterized query
    const sql = `
      SELECT id, name, attributes
      FROM products
      WHERE attributes @> $1::jsonb
      LIMIT 50;
    `;
    
    const result = await client.query(sql, [JSON.stringify(filterCriteria)]);
    return {
      data: result.rows,
      count: result.rowCount,
    };
  } catch (error: any) {
    throw createError({
      statusCode: 500,
      statusMessage: 'Database query execution failed',
      data: error.message,
    });
  } finally {
    client.release();
  }
});

Metrik Eksekusi: Sebelum vs Sesudah Indexing

Jalankan kembali EXPLAIN ANALYZE setelah indeks idx_products_attributes_path_ops aktif:

Bitmap Heap Scan on products  (cost=16.25..512.30 rows=250 width=128) (actual time=0.045..1.210 rows=140 loops=1)
  Recheck Cond: (attributes @> '{"brand": "Logitech", "wireless": true}'::jsonb)
  Buffers: shared hit=148
  ->  Bitmap Index Scan on idx_products_attributes_path_ops  (cost=0.00..16.18 rows=250 width=0) (actual time=0.028..0.028 rows=140 loops=1)
        Index Cond: (attributes @> '{"brand": "Logitech", "wireless": true}'::jsonb)
        Buffers: shared hit=4
Planning Time: 0.120 ms
Execution Time: 1.285 ms

Perbandingan performa pada 250.000 baris data:

  • Execution Time: Turun dari ~148.38 ms menjadi ~1.28 ms (peningkatan kecepatan >110x).
  • Metode Scan: Beralih dari Seq Scan ke Bitmap Index Scan dilanjutkan Bitmap Heap Scan.
  • I/O Buffers: Buffers read turun drastis ke 0 (karena indeks muat di RAM) dan shared hit turun dari 4842 blok menjadi 148 blok.

Trade-off: Write Amplification dan Storage Overhead

Meskipun GIN index menyelesaikan masalah slow read query, pertimbangkan dampak berikut pada arsitektur sistem:

  1. Write Amplification: Setiap operasi INSERT atau UPDATE pada kolom JSONB memerlukan pembaruan pada banyak leaf node indeks GIN. Throughput penulisan tabel akan menurun jika frekuensi penulisan sangat tinggi.
  2. Ukuran Penyimpanan: Indeks GIN membutuhkan memori signifikan. Menggunakan jsonb_path_ops mengurangi ukuran indeks dibandingkan jsonb_ops, tetapi tetap menambah alokasi RAM pada shared_buffers.
  3. Maintenance (Pending List): GIN menggunakan pending list untuk mempercepat write bertahap yang kemudian dibersihkan via VACUUM atau ketika batas gin_pending_list_limit terlampaui. Bersihkan bloat secara berkala menggunakan autovacuum tuning.