2. Optimasi Query Pencarian & Laporan Transaksi Skala Besar: Kombinasi GIN Trigram Index (pg_trgm), Pre-Lookup Relasi O(1), dan Case-Variant B-Tree

Pada sistem pembayaran dan integrasi ERP dengan volume transaksi tinggi, fitur pencarian (search bar) di menu Daftar Transaksi dan Laporan Rekonsiliasi adalah fitur yang paling sering diakses oleh tim Finance, Customer Support, maupun Partner Merchant.

Tantangannya: pengguna mengharapkan satu kotak pencarian tunggal (unified search bar) yang mampu mencari transaksi berdasarkan 9 jenis identifier sekaligus:

• Kode pesanan utama (mendukung pencarian potongan kode / substring)
• 6 kolom referensi eksternal (ID transaksi internal, ID pembayaran klien, nomor referensi Payment Gateway, nomor Virtual Account, dll.)
• 2 atribut pada tabel relasi tagihan (Invoices), yaitu Nomor Faktur dan ID Pelanggan.

Ketika jumlah data transaksi membesar hingga jutaan baris, pendekatan query ORM konvensional yang menggabungkan ILIKE '%...%' di banyak kolom beserta filter relasi bersarang (nested relation filter) membuat waktu respons database melambung tinggi dan membebani CPU server.

Dalam artikel kedua dari seri portofolio engineering ini, saya akan membedah bagaimana saya merancang ulang arsitektur pencarian dan indexing di PostgreSQL menggunakan tiga teknik utama: Eliminasi Subquery EXISTS via Pre-Lookup Relasi O(1), Case-Variant B-Tree Matching, dan Targeted GIN Trigram Index (pg_trgm).

1. Anatomi Masalah: Mengapa Query Pencarian Multi-Kolom Sangat Lambat?

Ketika developer menulis query pencarian multi-kolom menggunakan operator case-insensitive substring (ILIKE '%keyword%') pada seluruh kolom sekaligus menggabungkan tabel relasi di dalam blok OR, terjadi dua bottleneck besar di PostgreSQL:

Bottleneck 1: B-Tree Index Lumpuh Akibat ILIKE '%keyword%' di Semua Kolom

Indeks B-Tree standar bekerja dengan mengurutkan string dari karakter paling kiri. Ketika kita menggunakan pencarian wildcard di awal string (%keyword%) pada 7 kolom berbeda, PostgreSQL tidak dapat menggunakan B-Tree index sama sekali dan terpaksa melakukan Sequential Scan (Full Table Scan) membaca seluruh baris tabel dari disk.

Bottleneck 2: Subquery EXISTS Per Baris Akibat Filter Relasi di Dalam Blok OR

Ketika kondisi filter ke tabel relasi (Invoices) disisipkan di dalam blok OR milik tabel utama (Transactions), query planner PostgreSQL sering kali menghasilkan eksekusi Correlated SubPlan (EXISTS) yang dievaluasi berulang-ulang untuk setiap kandidat baris transaksi.

2. Solusi Arsitektur: 3 Pilar Optimasi Query & Indexing

Untuk menyelesaikan masalah ini tanpa harus menambah beban infrastruktur eksternal (seperti cluster Elasticsearch), saya melakukan perombakan pada Query Builder dan Database Indexing Strategy.

Pilar 1: Eliminasi Subquery EXISTS dengan Pre-Lookup Relasi O(1)

Alih-alih memasukkan kondisi tabel relasi Invoices ke dalam blok OR tabel Transactions, kita memisahkan pencarian relasi menjadi langkah Pre-Lookup kecil di awal.

Karena kolom nomor faktur dan ID pelanggan pada tabel Invoices telah memiliki B-Tree index, mencari ID tagihan yang cocok hanya membutuhkan waktu < 2 milidetik. Jika ditemukan, daftar ID tersebut langsung disuntikkan ke query utama sebagai pemeriksaan foreign key sederhana (invoice_id IN (...)) tanpa JOIN!

Pilar 2: Strategi Hybrid — Case-Variant B-Tree (IN) vs GIN Trigram (pg_trgm)

Dari analisis pola pencarian pengguna di lapangan, saya menemukan fakta penting:

• Pengguna mencari potongan kode (partial match) hanya pada kolom kode pesanan (order_code).
• Untuk kolom referensi lain (seperti nomor referensi gateway atau kode eksternal), pengguna selalu menyalin-tempel (copy-paste) kode utuh, namun terkadang dalam huruf kecil (lowercase) atau huruf besar (uppercase).

Jika kita menggunakan pencarian case-insensitive bawaan ORM pada kolom-kolom tersebut, database akan mengeksekusi UPPER(col) = UPPER($1) yang menggagalkan pemakaian B-Tree index standar.

Solusi yang saya terapkan adalah teknik Case-Variant Array (IN [original, lower, upper]):

• Dengan mengirimkan variasi [keyword, keyword.toLowerCase(), keyword.toUpperCase()] ke dalam operator IN (...), PostgreSQL murni mengeksekusi B-Tree Index Scan dengan kecepatan penuh!
• Sementara untuk kolom order_code, kita menerapkan Trigram Threshold Guard: pencarian substring (ILIKE '%...%' yang di-cover oleh GIN Index pg_trgm) hanya diaktifkan jika panjang kata kunci $\ge 3$ karakter (karena trigram membutuhkan minimal 3 karakter). Jika kata kunci kurang dari 3 karakter, otomatis dialihkan ke IN (variants) agar tidak memicu Full Table Scan.

// Prototype Konseptual: Hybrid Search Filter Builder
async function buildOptimizedSearchFilter(rawKeyword: string) {
  const keyword = rawKeyword.trim();
  const caseVariants = Array.from(
    new Set([keyword, keyword.toLowerCase(), keyword.toUpperCase()]),
  );

  // 1. O(1) Pre-Lookup pada tabel relasi (menghindari subquery EXISTS per baris)
  const matchedBills = await db.invoices.findMany({
    where: {
      OR: [
        { invoiceNo: { in: caseVariants } },
        { customerNo: { in: caseVariants } },
      ],
    },
    select: { id: true },
    take: 20,
  });
  const billIds = matchedBills.map((b) => b.id);

  // 2. Guard minimal 3 karakter untuk GIN Trigram Index pada orderCode
  const orderCodeFilter =
    keyword.length >= 3
      ? { contains: keyword, mode: 'insensitive' } // Dipercepat oleh GIN pg_trgm
      : { in: caseVariants };

  // 3. Gunakan B-Tree Index Scan (IN variants) untuk seluruh kolom referensi lainnya
  return {
    OR: [
      { orderCode: orderCodeFilter },
      { transactionRef: { in: caseVariants } },
      { externalRef: { in: caseVariants } },
      { gatewayRef: { in: caseVariants } },
      ...(billIds.length > 0 ? [{ invoiceId: { in: billIds } }] : []),
    ],
  };
}

Pilar 3: Desain Skema Indeks Database yang Presisi

Agar setiap cabang pada klausa OR dan filter laporan harian memiliki jalur indeks (index path) di PostgreSQL, skema database dilengkapi dengan kombinasi indeks berikut:

-- 1. Aktifkan ekstensi Trigram & pasang GIN Index khusus untuk kolom substring search
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_orders_code_trgm
  ON transactions USING gin (order_code gin_trgm_ops);

-- 2. Single-column B-Tree Indexes untuk exact-variant lookup
CREATE INDEX CONCURRENTLY idx_orders_external_ref ON transactions (external_ref);
CREATE INDEX CONCURRENTLY idx_orders_gateway_ref ON transactions (gateway_ref);
CREATE INDEX CONCURRENTLY idx_orders_va_number ON transactions (va_number);

-- 3. Composite Indexes untuk filter laporan & rekonsiliasi berdasarkan status dan waktu
CREATE INDEX CONCURRENTLY idx_orders_status_paid_at ON transactions (status, paid_at DESC);

3. Ringkasan Perbandingan Arsitektur Query

4. Pelajaran Utama (Key Takeaways)

1. Jangan Menyamaratakan ILIKE '%...%' pada Semua Kolom:
   Identifikasi kolom mana yang benar-benar membutuhkan pencarian substring (dan pasang GIN pg_trgm index di sana), serta gunakan exact match berbasis B-Tree pada kolom kode referensi lainnya.
2. Waspadai Batas Minimum 3 Karakter pada Trigram (pg_trgm):
   Karena trigram bekerja dengan memecah string menjadi kelompok 3 huruf, pencarian 1 atau 2 huruf pada GIN pg_trgm tidak akan efektif. Selalu tambahkan guard keyword.length >= 3 di kode aplikasi Anda.
3. Gunakan Trik Case-Variant Array ([term, lower, upper]):
   Untuk kode referensi ID, menggunakan IN (original, lower, upper) jauh lebih ramah terhadap B-Tree index standar dibandingkan membungkus kolom dengan fungsi LOWER() atau ILIKE.
4. Pisahkan Filter Relasi dari Blok OR Utama (Pre-Lookup Pattern):
   Melakukan satu query kecil berindeks di awal untuk mengambil daftar foreign key ID jauh lebih cepat daripada membiarkan database mengeksekusi Correlated Subquery di jutaan baris.

Pada Part 3 selanjutnya, kita akan membedah bagaimana saya menangani masalah putusnya koneksi soket RPC ke Core ERP secara tiba-tiba (socket hang up / ECONNRESET) serta solusi untuk kasus unik "Late Payment" pada pembayaran Kartu Kredit yang melewati batas waktu kadaluwarsa lokal.