Full Text Searches

Di PostgreSQL, full-text search (FTS) berbeda dengan pencarian string biasa. FTS fokus pada kata / lexeme (unit makna setelah preprocess seperti stemming & stopword removal) — bukan pada pencocokan substring literal. Dengan FTS kita mencari apakah dokumen mengandung kata/kumpulan kata tertentu, dapat menggabungkan operasi boolean (AND/OR/NOT), memberi peringkat (ranking), dan mengekstrak snippet yang menonjolkan hasil (highlighting). Berikut penjelasan lengkap, langkah-langkah praktis, contoh kode, dan best practice.

1) Konsep dasar: tsvector, tsquery, lexeme, stemming, stop words

to_tsvector(config, text) → mengubah teks menjadi tsvector (kumpulan lexeme + posisi).
- Proses: tokenisasi → lowercase → menghapus stop words → stemming (mengubah kata ke bentuk dasar/akar).
- Contoh:
  SELECT to_tsvector('english', 'A car, I want a car. I would not even mind having many cars');
  Hasil (contoh):
  'car':2,6,14 'even':10 'mani':13 'mind':11 'want':4 'would':8
  - cars dan car distem menjadi car. many menjadi mani (contoh stemming bahasa Inggris).
to_tsquery(config, 'some & query') → membuat tsquery dari sintaks tsquery (membutuhkan operator & | ! <-> dsb.).
plainto_tsquery(config, 'plain text') → mengubah teks biasa menjadi tsquery dengan operator & antar kata (lebih mudah untuk input user yang bukan operator tsquery).
phraseto_tsquery(config, 'phrase') → untuk pencarian frase (kata berurutan).
@@ → operator BOOLEAN yang memeriksa apakah tsvector @@ tsquery (apakah dokumen cocok dengan query).
Lexeme = token yang distandarisasi (hasil stemming/dictionary). Contoh: want dan wanted → sama menjadi lexeme want.

Catatan: konfigurasi bahasa ('english', 'dutch', 'simple', dll.) mempengaruhi tokenisasi, stop words, dan stemming. Lihat pg_ts_config untuk daftar konfigurasi tersedia:

SELECT cfgname FROM pg_ts_config;

2) Contoh perilaku stemming & perbandingan bahasa

Inggris:

SELECT to_tsvector('english', 'A car, I want a car. I would not even mind having many cars');
-- => 'car':2,6,14 'even':10 'mani':13 'mind':11 'want':4 'would':8

Dutch (konfigurasi berbeda → tokenisasi/stopword/stemming berbeda):

SELECT to_tsvector('dutch', 'A car, I want a car. I would not even mind having many cars');
-- Output berbeda: some stopwords/morfologi lain, mis. 'many' tetap 'many' tergantung konfigurasi

3) Mencari & operator tsquery

Basic boolean dalam to_tsquery:
- & : AND
- | : OR
- ! : NOT
- <-> : FOLLOWED BY (adjacency / kata bersebelahan) — berguna untuk frase/proximity
Contoh:

SELECT to_tsvector('english', 'A car, I want a car...') @@ to_tsquery('english', 'wanted');
-- true (karena want/wanted distem menjadi lexeme sama)

SELECT to_tsvector('english', '...') @@ to_tsquery('english', 'wanted & bmw');
-- false (bmw tidak ada)

plainto_tsquery sangat cocok untuk input user biasa:

SELECT to_tsvector('english', comment) @@ plainto_tsquery('english', 'wanted bmw');

4) Indexing untuk Full-Text Search — dua pendekatan

Ada dua pola umum untuk mengindeks teks:

A. Functional index (lebih hemat ruang, lebih sederhana)

Buat index langsung dari ekspresi to_tsvector(...):

CREATE INDEX idx_fts_func ON t_fts USING gin (to_tsvector('english', comment));

Kelebihan: tidak menambah kolom, lebih ringkas dari segi schema.
Kekurangan: setiap query yang memanfaatkan index harus mengeksekusi ekspresi; update index saat row berubah tetap terjadi; kadang sedikit lebih lambat dibanding materialized column pada runtime.

B. Materialized tsvector column + trigger + index (lebih cepat pada query baca)

Tambah kolom ts bertipe tsvector:

ALTER TABLE t_fts ADD COLUMN ts tsvector;

Isi kolom untuk baris existing:

UPDATE t_fts SET ts = to_tsvector('english', coalesce(comment, ''));

Buat index pada kolom tersebut:

CREATE INDEX idx_fts_ts ON t_fts USING gin (ts);

Buat trigger supaya ts selalu ter-sync saat INSERT/UPDATE:

Pada versi PostgreSQL modern gunakan EXECUTE FUNCTION, pada versi sangat lama EXECUTE PROCEDURE.

CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE ON t_fts
FOR EACH ROW
EXECUTE FUNCTION tsvector_update_trigger(ts, 'pg_catalog.english', comment);

tsvector_update_trigger(target_column, config, column1, column2, ...)
Kelebihan: query baca lebih cepat (index langsung pada kolom).
Kekurangan: butuh ruang tambahan untuk kolom ts, trigger menambah overhead saat write.

5) Contoh query FTS lengkap (dengan ranking & snippet)

Cari & urutkan hasil berdasarkan relevansi (ts_rank) — contoh plainto_tsquery:

SELECT id, comment,
       ts_rank(ts, q) AS rank
FROM t_fts, plainto_tsquery('english', 'wanted bmw') q
WHERE ts @@ q
ORDER BY rank DESC
LIMIT 10;

Menggunakan weighting (beri bobot lebih besar ke kolom title vs body):

-- buat vektor tertimbang: title lebih penting (A), body kurang (B)
SELECT id,
  ts_rank(
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B'),
    plainto_tsquery('english', 'wanted bmw')
  ) AS rank
FROM articles
WHERE
  (setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
   setweight(to_tsvector('english', coalesce(body, '')), 'B')) @@ plainto_tsquery('english', 'wanted bmw')
ORDER BY rank DESC;

Ambil snippet / highlight hasil dengan ts_headline:

SELECT id,
  ts_headline('english', comment, plainto_tsquery('english', 'wanted bmw')) AS snippet
FROM t_fts
WHERE ts @@ plainto_tsquery('english', 'wanted bmw');

6) Debugging & Statistik

ts_debug(config, text) → menampilkan tokenisation/dictionary/lexeme untuk tiap token — berguna untuk mengetahui mengapa query match/tidak match.
```
SELECT * FROM ts_debug('english', 'go to www.cybertec-postgresql.com');
```
- Output menunjukkan token, jenis token, dictionary yang dipakai, dan lexeme hasil stemming.
ts_stat(query_sql) → kumpulan statistik kata/lexeme (berapa dokumen mengandung kata, frekuensi total):
```
SELECT * FROM ts_stat('SELECT to_tsvector(''english'', comment) FROM t_fts') ORDER BY ndoc DESC LIMIT 10;
```

7) Pilihan operator & sintaks pencarian lanjutan

to_tsquery('english', 'foo & bar') → match dokumen yang mengandung foo AND bar (lexeme setelah stemming).
plainto_tsquery('english', 'foo bar') → mempermudah: akan jadi foo & bar.
phraseto_tsquery('english', 'exact phrase') → membantu pencarian frase bersebelahan.
<-> dalam to_tsquery → adjacency (kata bersebelahan, urutan memakai lexeme): contoh 'foo <-> bar'.
Ada juga fungsi modern seperti websearch_to_tsquery (meniru sintaks search engines — dukungan pada versi PostgreSQL tertentu); ini memudahkan pengguna memakai kutipan/+, - dsb. (cek versi PostgreSQL Anda).

8) Index type: GIN vs GiST untuk FTS

GIN (Generalized Inverted Index) adalah yang paling umum dipakai untuk full-text search (USING gin(ts)).
- Kelebihan: sangat cepat untuk pencarian, efisien mencari kata/lexeme di dokumen besar.
- Kekurangan: update index bisa lebih berat (INSERT/UPDATE lebih mahal dibanding GiST), dan index bisa relatif besar.
GiST juga bisa digunakan (lebih fleksibel), tetapi untuk FTS GIN biasanya pilihan utama di production kecuali ada kebutuhan khusus.
Untuk trigram (pg_trgm), GIN sering lebih baik untuk dataset besar; GiST kadang lebih cepat saat update, trade-off tergantung workload.

9) Maintenance, konsistensi & hal yang harus diperhatikan

Jika Anda mengubah konfigurasi bahasa, aturan stemming, atau dictionary → Anda harus rebuild tsvector dan reindex (REINDEX) karena hasil stemming/dictionary berubah.
Pilihan antara functional index vs materialized column:
- Functional index: lebih hemat struktur, berguna jika update jarang.
- Materialized tsvector + trigger: lebih cepat untuk query baca intensif, tapi menambah overhead pada penulisan.
Autovacuum & VACUUM: GIN index maintenance bisa memerlukan perhatian (bloating). Monitoring & reindexing berkala mungkin perlu.
Untuk performa ranking, gunakan ts_rank atau ts_rank_cd dengan parameter normalisasi jika perlu.

10) Contoh alur kerja FTS (praktis, step-by-step)

Aktifkan extension (sekali per database):

CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- extension pg_trgm untuk n-gram / fuzzy, bukan FTS core; FTS core ada built-in

Tambah tabel / data:

CREATE TABLE documents (id serial PRIMARY KEY, title text, body text);

Tambah kolom ts (materialized approach):

ALTER TABLE documents ADD COLUMN ts tsvector;
UPDATE documents SET ts = 
  setweight(to_tsvector('english', coalesce(title,'')),'A') ||
  setweight(to_tsvector('english', coalesce(body,'')),'B');

Buat index:

CREATE INDEX idx_documents_ts ON documents USING gin (ts);

Buat trigger untuk keep-in-sync:

CREATE TRIGGER documents_tsvector_update
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION tsvector_update_trigger(ts, 'pg_catalog.english', title, body);

Query pencarian:

SELECT id, title,
  ts_rank(ts, q) AS rank,
  ts_headline('english', body, q) AS snippet
FROM documents, plainto_tsquery('english', 'search terms') q
WHERE ts @@ q
ORDER BY rank DESC
LIMIT 10;

11) Tips & best practice

Pilih konfigurasi bahasa sesuai teks (search dalam bahasa Indonesia? gunakan config yang sesuai atau simple jika tidak ada konfig bahasa lokal).
Gunakan plainto_tsquery untuk input raw user. to_tsquery mengharuskan sintaks tsquery.
Beratkan (weight) kolom penting (title > subtitle > body) dengan setweight agar hasil relevansi lebih alami.
Gunakan ts_headline untuk menampilkan potongan teks (highlight).
Monitoring: cek ukuran index, frekuensi update; GIN dapat bloat setelah banyak update.
Rebuild tsvector & reindex jika stop words / dictionaries diubah.
Gabungkan pg_trgm untuk fuzzy matching pada nama/kolom yang bukan cocok dengan FTS (mis. nama orang, alamat). pg_trgm mempercepat LIKE '%...%', regex, dan similarity search.

12) Debug & analisis kenapa suatu query tidak match

ts_debug → lihat tokenization & mapping dictionary untuk kata bermasalah.
ts_stat → lihat frekuensi lexeme untuk memahami kata umum vs jarang.
Jika query tidak match: kemungkinan stopword dihapus, stemming mengubah bentuk, atau config bahasa salah.

13) Singkat: Perbandingan pg_trgm vs Full-Text Search

pg_trgm (trigram)
- Tujuan: fuzzy matching / similarity / mempercepat LIKE '%...%' dan regex.
- Cocok untuk: nama, alamat, autocomplete, typo tolerance.
- Index: GiST (gist_trgm_ops) atau GIN (gin_trgm_ops) — GIN sering lebih baik di dataset besar.
Full-Text Search (FTS)
- Tujuan: pencarian dokumen/isi berbasis kata; mendukung stemming, stop words, ranking, phrase.
- Cocok untuk: artikel, komentar, dokumen panjang, konten umum.
- Index: GIN pada tsvector (pola umum).

Keduanya saling melengkapi: untuk search bar modern Anda mungkin menggunakan FTS untuk dokumen + pg_trgm untuk nama/slug/typo tolerance.

14) (Tambahan) Exclusion operators — contoh singkat (terkait indexing GiST)

Walaupun bukan bagian inti FTS, buku juga menyebut exclusion constraints yang memanfaatkan GiST. Contoh: mencegah double booking ruangan.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE t_reservation (
  room int,
  from_to tsrange,
  EXCLUDE USING gist (room WITH =, from_to WITH &&)
);
-- Insert valid:
INSERT INTO t_reservation VALUES (10, '[2017-01-01,2017-03-03]');
INSERT INTO t_reservation VALUES (13, '[2017-01-01,2017-03-03]');
-- Insert overlapping pada room 13 akan error:
INSERT INTO t_reservation VALUES (13, '[2017-02-02,2017-08-14]');
-- ERROR: conflicting key value violates exclusion constraint ...

EXCLUDE + GiST memberikan cara kuat untuk menjaga integritas yang bergantung pada operator seperti overlap (&&) atau geometric ops.

Penutup

Full-text search di PostgreSQL adalah fitur sangat kaya: preprocessing (tokenize, stopword, stemming), berbagai jenis query, ranking & headline, debugging tools, serta index (GIN/GiST) yang membuatnya skalabel di produksi. Untuk aplikasi nyata, pertimbangkan trade-off storage vs query speed (functional index vs materialized tsvector) dan pilih konfigurasi bahasa & opclass yang sesuai.

PreviousFuzzy Searching NextPostgreSQL 18 😲

Last updated 1 day ago