scribase
Guide

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.

Published November 16, 20267 min read

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.

sql
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.

sql
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.

sql
-- 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.

sql
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;
FAQ

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.