PostgreSQL Full-Text Search Tanpa Elasticsearch: Yang Saya Gunakan di Proyek ERP

Proyek ERP saya membutuhkan pencarian — pencarian di seluruh pelanggan, faktur, produk, dan karyawan. Insting saya adalah menjangkau Elasticsearch. Kemudian saya menjalankan benchmark: untuk dataset di bawah 5 juta baris, full-text search PostgreSQL dengan GIN index pada tsvector cocok dengan latensi Elasticsearch untuk sebagian besar jenis kueri dan jauh lebih sederhana untuk dioperasikan. Tidak ada infrastruktur tambahan, tidak ada lag sinkronisasi, tidak ada cluster Elasticsearch untuk dikelola.

Cara Kerja Full-Text Search PostgreSQL

Sistem FTS PostgreSQL mengkonversi teks menjadi `tsvector` — daftar lexeme (batang kata yang dinormalisasi) yang diurutkan dengan informasi posisi. Kueri dikonversi menjadi `tsquery` — ekspresi boolean dari lexeme. Operator `@@` mencocokkan tsvector dengan tsquery. PostgreSQL dikirim dengan kamus untuk 20+ bahasa yang menangani stemming, stop words, dan aksen.

Kolom tsvector dan GIN Index

Pola produksi adalah menambahkan kolom `tsvector` yang dihasilkan ke tabel Anda dan membangun GIN index di atasnya. Kolom yang dihasilkan dipertahankan secara otomatis oleh PostgreSQL — tidak perlu trigger atau sinkronisasi eksternal. GIN (Generalized Inverted Index) adalah jenis index yang dirancang untuk tsvectors: menyimpan pemetaan dari setiap lexeme ke baris yang mengandungnya, membuat kueri full-text O(log N) daripada O(N).

Meranking Hasil dengan ts_rank

Fungsi `ts_rank()` PostgreSQL menilai setiap baris yang cocok berdasarkan relevansi. Keterbatasannya: ts_rank memerlukan pembacaan tsvector dari setiap baris yang cocok, yang mahal pada skala. Untuk 1 juta pencocokan pada kueri yang luas, ranking dapat memakan waktu detik. Optimisasi: tambahkan filter WHERE untuk mengurangi kumpulan pencocokan sebelum ranking.

PostgreSQL FTS Pipeline:
─────────────────────────────────────────────────────────
Text input: "Running PostgreSQL queries efficiently"
                    │
                    ▼ to_tsvector('english', text)
tsvector: 'effici':4 'postgresql':2 'queri':3 'run':1
  (stemmed, stop words removed, positions recorded)
                    │
                    ▼ stored in generated column + GIN index
GIN Index: lexeme → [row_ids]
  'postgresql' → [1, 5, 23, 44]
  'queri'      → [1, 3, 5, 12]
  'run'        → [1, 8, 19]
                    │
Query: plainto_tsquery('english', 'postgresql queries')
       → 'postgresql' & 'queri'  (AND logic)
                    │
                    ▼ GIN index intersection
Matching rows: [1, 5]  → O(log N), not O(N)
                    │
                    ▼ ts_rank() for ordering
Ranked results with relevance score

Performance benchmark (1M rows, GIN index):
  Simple phrase search:        ~8ms
  Boolean AND (2 terms):       ~12ms
  Autocomplete (prefix :*):    ~5ms
  Broad match (many results):  can be slow → use LIMIT early

Pola SQL yang Saya Gunakan di Produksi

Ini adalah pola produksi lengkap yang saya gunakan dalam sistem ERP NestJS + PostgreSQL. Pola ini mencakup: kolom tsvector yang dihasilkan menggabungkan beberapa bidang teks (dengan bobot berbeda menggunakan setweight), GIN index pada tsvector, fungsi pencarian menggunakan plainto_tsquery, ts_rank untuk pengurutan, dan ts_headline untuk menghasilkan cuplikan hasil pencarian dengan pencocokan yang disorot.

Pencarian Multi-Bidang Berbobot

Dalam konteks ERP, pencocokan nama pelanggan harus berperingkat lebih tinggi dari pencocokan di bidang catatan. Fungsi `setweight()` PostgreSQL menetapkan bobot A-D ke tsvectors sebelum menggabungkannya. Bobot A adalah yang tertinggi. Saya menggunakan: A untuk nama/judul/kode, B untuk deskripsi, C untuk tag/kategori, D untuk catatan/keterangan.

-- Production FTS setup for ERP (customers table)
-- 1. Add generated tsvector column with weighted fields
ALTER TABLE customers ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(code, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(email, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(notes, '')), 'D')
  ) STORED;

-- 2. GIN index for fast searching
CREATE INDEX idx_customers_search ON customers USING GIN(search_vector);

-- 3. Trigram index for autocomplete + fuzzy matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_name_trgm ON customers USING GIN(name gin_trgm_ops);

-- 4. Search query (used in NestJS service)
SELECT
  id, name, code, email,
  ts_rank(search_vector, query) AS rank,
  ts_headline('english', name, query, 'MaxWords=10, MinWords=5') AS snippet
FROM customers, plainto_tsquery('english', $1) AS query
WHERE
  search_vector @@ query
  OR similarity(name, $1) > 0.3  -- fuzzy fallback
ORDER BY rank DESC, similarity(name, $1) DESC
LIMIT 20;

-- 5. Autocomplete endpoint (prefix match)
SELECT id, name
FROM customers
WHERE to_tsvector('english', name) @@ to_tsquery('english', $1 || ':*')
LIMIT 10;

Mengimplementasikan Pencarian di NestJS

Dalam layanan NestJS, full-text search dengan SQL mentah memberikan lebih banyak kontrol daripada kueri yang diabstraksikan ORM. Saya menggunakan TypeORM DataSource atau klien pg mentah dengan kueri berparameter. Kuncinya adalah menggunakan `plainto_tsquery` daripada `to_tsquery` untuk pencarian yang menghadap pengguna.

Autocomplete dengan Prefix Matching

Standar `plainto_tsquery` tidak melakukan prefix matching — 'inv' tidak akan cocok dengan 'invoice'. Untuk autocomplete, gunakan `to_tsquery('english', $1 || ':*')` dengan operator `:*`, yang mencocokkan lexeme apa pun yang dimulai dengan prefix. Gabungkan ini dengan LIMIT 10 dan Anda memiliki endpoint autocomplete yang cepat.

Kapan Menambahkan Elasticsearch

PostgreSQL FTS mencakup 90% persyaratan pencarian ERP. Kasus di mana saya akan menambahkan Elasticsearch: dataset melebihi 5 juta baris dengan kueri broad-match, Anda memerlukan pencarian semantik/vektor, Anda memerlukan agregasi pencarian lintas layanan real-time, atau pemangku kepentingan Anda memerlukan fitur seperti koreksi ejaan 'did you mean?'.

Sumber & Bacaan Lebih Lanjut