Full-text search

Built-in full-text search covers many product search needs with no extra infrastructure.

LIKE matches characters; full-text search matches words, understanding stemming ("running" matches "run"), ignoring stop words, and ranking results.

A tsvector is a document processed into normalised lexemes with positions: to_tsvector('english', 'The cats are running') gives 'cat':2 'run':4.
A tsquery is a search condition: to_tsquery('english', 'cat & run'). For user input use websearch_to_tsquery, which understands "quoted phrases", or, and -excluded like a search box and never raises a syntax error.
@@ tests a match; ts_rank or ts_rank_cd scores it; ts_headline builds a snippet with highlighted terms.

For speed, store the tsvector in a generated column and index it with GIN, so it isn't recomputed per row at query time. Use setweight to rank title matches above body matches.

The text search configuration (english, simple, russian, ...) decides the parser, stemmer and stop words; use the same one for documents and queries. simple does no stemming and works for any language, including names and codes.

Limits: no typo tolerance (combine with pg_trgm similarity), basic relevance tuning, and no faceting engine. When you need those at scale, a dedicated search engine is the next step — fed from PostgreSQL.

Example

CREATE TABLE article (
    id    int PRIMARY KEY,
    title text NOT NULL,
    body  text NOT NULL,
    tsv   tsvector GENERATED ALWAYS AS (
            setweight(to_tsvector('english', title), 'A') ||
            setweight(to_tsvector('english', body), 'B')) STORED
);
CREATE INDEX article_tsv_idx ON article USING gin (tsv);
INSERT INTO article (id, title, body) VALUES
 (1, 'Running PostgreSQL in production', 'Backups, monitoring and tuning.'),
 (2, 'A guide to indexes', 'Why the database runs faster with the right index.');

SELECT id, title, ts_rank(tsv, q) AS rank
FROM article, websearch_to_tsquery('english', 'run index') AS q
WHERE tsv @@ q ORDER BY rank DESC;
-- 2 | A guide to indexes | ...   ('runs' and 'index' both match)

SELECT to_tsvector('english', 'The cats are running');   -- 'cat':2 'run':4

Remember

tsvector = processed document, tsquery = search condition, @@ = match
Store the tsvector in a generated column with a GIN index
websearch_to_tsquery safely parses user-typed searches

Watch out for

Using different text search configurations for the document and the query, so stems don't match.

Check yourself

Why does to_tsvector('english', 'running') match to_tsquery('english', 'runs')?

Official docs: www.postgresql.org/docs/current/textsearch-intro.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks