How does full-text search work in Postgres?
Postgres has full-text search built in: to_tsvector turns a document into a searchable list of normalized lexemes (stemmed, stripped of stop words), to_tsquery turns a search string into the same form, and the @@ operator matches one against the other. For a table of any real size, store the tsvector in a generated column and back it with a GIN index — plain to_tsvector(...) @@ to_tsquery(...) with no index re-derives the vector and scans every row.
1.Match a query against text directly
to_tsvector converts text to the searchable form, to_tsquery converts the search terms, and @@ tests the match. This form recomputes the tsvector on every row for every query and does not use an index — fine to try, not what you ship.
select title from articles
where to_tsvector('english', body) @@ to_tsquery('english', 'postgres & index');2.Add a generated tsvector column and a GIN index
A generated column keeps the tsvector automatically in sync with the source columns on every insert and update, so the query only has to read it, not recompute it — and a GIN index on that column turns the match into an index scan.
alter table articles
add column search_vector tsvector
generated always as (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) stored;
create index on articles using gin (search_vector);
select title from articles
where search_vector @@ to_tsquery('english', 'postgres & index');3.Multi-word queries and plainto_tsquery / websearch_to_tsquery
to_tsquery requires operators (& for AND, | for OR, <-> for "followed by") and errors on plain text with spaces. plainto_tsquery turns free text into an AND of its terms; websearch_to_tsquery (Postgres 11+) additionally understands quoted phrases and a leading minus for exclusion, which is the closer match to how users actually type a search box.
-- user types: rust async runtime
select title from articles
where search_vector @@ websearch_to_tsquery('english', 'rust async runtime');
-- user types: "exact phrase" -excluded
select title from articles
where search_vector @@ websearch_to_tsquery('english', '"exact phrase" -excluded');4.Rank results with ts_rank
A match is boolean; ts_rank scores how relevant a matching row is based on term frequency and (with ts_rank_cd) proximity, so results can be ordered best-first instead of in arbitrary row order.
select title, ts_rank(search_vector, query) as rank
from articles, websearch_to_tsquery('english', 'rust async runtime') as query
where search_vector @@ query
order by rank desc
limit 20;Common questions
Do I need an external search engine for Postgres full-text search?
Not for most apps. Postgres has to_tsvector/to_tsquery, GIN indexing, and ts_rank built in, which covers stemming, stop words, phrase search, and relevance ranking. Reach for a dedicated search engine only once you need features Postgres does not have, like typo-tolerant fuzzy matching at scale or faceted search across millions of rows.
What is the difference between to_tsquery and plainto_tsquery?
to_tsquery expects search-operator syntax (&, |, <->) and errors on plain text with spaces. plainto_tsquery accepts plain text and ANDs the terms together. websearch_to_tsquery goes further and also understands quoted phrases and a leading minus for exclusion, closer to a real search box.
Why is my full-text search query slow?
Usually because to_tsvector(...) is being recomputed on every row on every query with no index behind it. Store the tsvector in a generated column and add a GIN index on that column so the match becomes an index scan instead of a full-table recompute.
How do I rank full-text search results by relevance?
Pass the tsvector and tsquery to ts_rank (or ts_rank_cd, which also weighs how close together the matched terms appear) and order by the result descending. A plain @@ match alone only tells you yes/no, not how good a match it is.
Ready to try it?
Start a free project and follow this guide against a real backend, or move an existing project in with a dry run first.