Full-Text Search

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Full-Text Search
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

`ILIKE '%word%'` is basic substring matching and may not provide linguistic normalization, ranking, or scalable indexing. PostgreSQL full-text search turns a document into a lexeme-based `tsvector` and input into a `tsquery`. Language configuration controls stemming and stop words.

Connect it to a real scenario

Add a generated search-vector column and GIN index for English titles/descriptions. Built-in English stemming does not solve Burmese segmentation, so evaluate simple configuration, trigram matching, or external search separately with locale-specific test data.

A document becomes a tsvector
Input becomes a tsquery
Choose language configuration per locale

Try the working example

sql

ALTER TABLE app.tutorials
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(metadata->>'summary', ''))
) STORED;

CREATE INDEX tutorials_search_gin_idx
  ON app.tutorials USING gin (search_vector);

SELECT title, ts_rank(search_vector, q) AS rank
FROM app.tutorials, websearch_to_tsquery('english', 'postgres performance') AS q
WHERE search_vector @@ q
ORDER BY rank DESC;

You should see

You get ranked, index-supported full-text search results.

5-minute try-it

Combine two vectors with `setweight` so title matches rank above description matches.

One important caution

Do not assume the English configuration gives good Burmese search. Evaluate each locale with a representative query set.

PostgreSQL — Full Text Search — PostgreSQL Global Development Group

Easy traps

Do not assume the English configuration gives good Burmese search. Evaluate each locale with a representative query set.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Combine two vectors with `setweight` so title matches rank above description matches.

You'll know it worked when: You get ranked, index-supported full-text search results.