Piye cara full-text search nyambut gawe ing PostgreSQL?

Question

Accepted Answer

PostgreSQL nduwé **built-in full-text search** — nggoleki dokumen/teks kanggo tembung kanthi stemming, ranking, lan kesadaran basa, luwih kuat tinimbang `LIKE`. Iki nggunakake tipe **`tsvector`** (dokumen sing wis diproses) lan **`tsquery`** (query nggoleki), kanthi indeks GIN kanggo kecepatan.

## Masalah sing LIKE ora bisa nggatasi

```text
LIKE '%word%' → no stemming (won't match "running" for "run"), no ranking, no language
  awareness, and can't use an index well (leading wildcard) → slow and limited.
Full-text search → stems words, ignores stop words, ranks by relevance, indexable.
```

## tsvector lan tsquery

```sql
-- to_tsvector — process text into searchable lexemes (stemmed, stop-words removed)
SELECT to_tsvector('english', 'The cats are running fast');
-- → 'cat':2 'fast':5 'run':4  (stemmed: cats→cat, running→run; "the"/"are" removed)

-- to_tsquery / plainto_tsquery — parse a search query
SELECT to_tsvector('english', body) @@ to_tsquery('english', 'running')
FROM articles;   -- @@ = the match operator; matches "run" stem
```

`to_tsvector` ngowahi teks dadi **lexemes** sing ternormalisasi (stemming tembung, mbusak stop words), lan `to_tsquery` nglumpruk istilah nggoleki. Operator `@@` nyinaoni kecocokan — dadi "running" cocog kanthi dokumen sing ngandhut "run," "runs," etc.

## Kolom sing bisa dingoleki + indeks GIN (setup praktis)

```sql
-- add a generated tsvector column and index it with GIN
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- fast searches using the index
SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'machine learning');
```

## Ranking asil miturut relevansi

```sql
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC;          -- most relevant first
```

`ts_rank` menehi skor carane saben dokumen cocog, ngaksesi asil sing dipesen relevansi.

## Apa iku penting

Full-text search built-in PostgreSQL iku fitur sing berharga sing ngidini sampeyan ngimplemen **fungsi nggoleki ing basis data sampeyan** — tanpa kudu nggunakake mesin nggoleki kapisah (kaya Elasticsearch) kanggo akeh use cases — dadi ngerti carane iku migunani kanggo aplikasi sing butuh nggoleki teks.

Nilai utama yaiku yen luwih kuat katimbang `LIKE` (sing ora bisa stemming tembung, ranking asil, utawa diindeks kanthi efisien): full-text search nyedhiyakake **stemming tembung** (kecocokan "running" kanggo "run"), **mbusak stop-word**, **kesadaran basa**, **ranking relevansi**, lan kang penting bisa **diindeks** (kanthi indeks GIN kanggo nggoleki kanthi cepet) — mbuwakake kapabilitas nggoleki nyata (jenis sing dikarepake panganggo: kecocokan variasi tembung, ranking miturut relevansi) langsung ing Postgres.

Ngerti konsep utama — **`tsvector`** (teks dokumen sing wis diproses, stemming), **`tsquery`** (istilah nggoleki sing wis dianalisis), operator kecocokan **`@@`**, lan **`ts_rank`** kanggo ranking relevansi — plus setup praktis (kolom `tsvector` sing dibuat otomatis lan diindeks kanthi GIN kanggo performa) nyakup carane ngimplemen nggoleki kanthi efektif.

Sanjabaning mesin nggoleki khusus nyedhiyakake luwih akeh kanggo kabutuhan komplek, full-text search PostgreSQL cukup lan mudah kanggo akeh aplikasi (nyingkirake kompleksitas operasional sistem nggoleki kapisah lan nggawe data nggoleki tetep konsisten kanthi data relasional sampeyan).

Sisihan nggoleki teks iku requirement aplikasi sing umum, lan sisihan Postgres nyedhiyakake full-text search sing mampu (kuat, bisa diindeks, kanthi stemming lan ranking) sing asring cukup tanpa mesin nggoleki kapisah, ngerti iku — model tsvector/tsquery, setup sing diindeks, lan ranking — iku pengetahuan sing berharga, relevan kanthi praktek sing ngidini sampeyan nambah fungsi nggoleki nyata manganggo kapabilitas built-in PostgreSQL, fitur migunani sing asring ora dipikirake dhéwé-dhéwé supaya loro solusyon eksternal sing luwih komplek naliko Postgres bakal cukup.