Full-Text Search

tsvector, tsquery, ranking, generated columns and GIN indexes — search without a separate search engine.

LIKE '%word%' cannot use normal indexes and does not understand language: 'errors' should match 'error'. PostgreSQL's full-text search turns documents into normalized word lists (tsvector) and queries into tsquery, with stemming, stop words, ranking and fast indexes.

Searching engineering notes

to_tsvector shows the normalization: words are lower-cased and stemmed ('carry' -> 'carri', 'identifiers' -> 'identifi'), and stop words like 'and' disappear. The numbers are word positions.

can & error matches the document containing both words, even though it says 'errors' and 'CAN'.

ts_rank orders matches by relevance.

A generated tsvector column with a GIN index is how this is done in production; websearch_to_tsquery accepts Google-like input ('-battery' excludes).

Notes

WHERE body ILIKE '%error%' WHERE search @@ to_tsquery('english', 'error')

ILIKE with a leading wildcard scans the whole table and does not match 'errors'. Full-text search is linguistic and indexable.

What do to_tsvector and to_tsquery produce?

to_tsvector: a sorted list of normalized lexemes with positions from a document. to_tsquery: a search condition of lexemes combined with & | ! and <->. @@ tests whether they match.