Full-Text Search in PostgreSQL

When users need to search text, the reflex is to reach for a separate search engine like Elasticsearch. For a lot of applications that's premature. PostgreSQL has capable full-text search built in, and it handles the "search articles by keyword" or "find products matching this phrase" cases well without adding another system to run, sync, and keep consistent. Knowing where Postgres search is enough, and where it isn't, saves you a lot of infrastructure.

This is part 27 of the PostgreSQL Masterclass, following arrays, enums, and composite types.

Why LIKE isn't search

The naive approach is WHERE body LIKE '%postgres%'. It works for tiny data and fails as real search in two ways. It can't use a normal index with a leading wildcard, so it scans the whole table. And it's literal: searching "running" won't match "run," "ran," or "runs," and it doesn't understand relevance or word boundaries. Full-text search fixes both by understanding language.

The two core types: tsvector and tsquery

Postgres full-text search rests on two types. A tsvector is a document processed into normalized search tokens (lexemes), with stop words removed and words reduced to their root. A tsquery is a processed search query. You match them with the @@ operator:

SELECT to_tsvector('english', 'The cats are running fast');
-- 'cat':2 'fast':5 'run':4
--   ^ 'the' and 'are' dropped as stop words, words stemmed to roots

SELECT to_tsvector('english', 'The cats are running fast')
       @@ to_tsquery('english', 'run');   -- true: 'running' stemmed to 'run'

That stemming is the point. Both the document and the query pass through the same language configuration (english here), so "running" in the text matches "run" in the query. This is real search behavior, not literal substring matching.

Making it fast with a stored column and GIN index

Computing to_tsvector on every row at query time would be a full scan, so you store the vector and index it. The clean modern way is a generated column that stays in sync automatically, plus a GIN index on it:

ALTER TABLE articles
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX articles_search_gin ON articles USING gin (search);

The generated column recomputes whenever title or body changes, so it's never stale, and the GIN index makes matching against it fast. Now a search is an index lookup:

SELECT id, title
FROM articles
WHERE search @@ plainto_tsquery('english', 'postgres indexing');

Query functions: plainto, phraseto, websearch

Postgres gives you several ways to turn user input into a tsquery, and the right one depends on how much syntax you want to expose:

• plainto_tsquery('postgres indexing') treats the input as words that must all match (AND).
• phraseto_tsquery('postgres indexing') requires the words adjacent, in order, as a phrase.
• websearch_to_tsquery('postgres -mysql "full text"') accepts Google-style syntax: quotes for phrases, - to exclude, or for alternatives. This is usually the best choice for a user-facing search box, because it handles messy input safely.
• to_tsquery('postgres & index:*') is the raw form with full operator control, including :* for prefix matching, but it errors on malformed input, so don't feed it raw user text.

For most search boxes, websearch_to_tsquery is the right default: it gives users familiar syntax and never throws on weird input.

Ranking results by relevance

Search isn't just matching, it's ordering the best matches first. ts_rank scores how well each document matches the query, so you can sort by relevance:

SELECT id, title,
       ts_rank(search, websearch_to_tsquery('english', 'postgres indexing')) AS rank
FROM articles
WHERE search @@ websearch_to_tsquery('english', 'postgres indexing')
ORDER BY rank DESC
LIMIT 20;

ts_rank_cd is a variant that also considers how close the matched terms are to each other, which often ranks phrase-like matches higher. Pair ranking with ts_headline, which returns a snippet of the document with the matched terms highlighted, and you have the makings of a real search results page.

Weighting fields and other refinements

You often want a title match to count more than a body match. setweight tags parts of the vector with weights A through D, and ts_rank respects them:

ALTER TABLE articles
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B')
  ) STORED;

Now a keyword in the title contributes more to the rank than the same keyword buried in the body, which matches how people expect search to behave.

When to graduate to a dedicated engine

Postgres full-text search is enough for a great many applications: searching articles, products, documentation, support tickets. It saves you running a second datastore and keeping it in sync. You should consider a dedicated engine like Elasticsearch or a managed search service when you need things Postgres doesn't do well: typo tolerance and fuzzy matching out of the box, faceted search at large scale, complex relevance tuning, or search across hundreds of millions of documents with sub-100ms latency and heavy query volume. Until you hit one of those, the built-in search is likely enough, and it keeps your architecture simpler.

The pattern to remember: a tsvector generated column, a GIN index, websearch_to_tsquery for user input, and ts_rank with setweight for relevance. That's a complete, production-quality search feature that lives entirely inside the database you already run.

Next, we move from querying data to reshaping how it's presented, starting with views and when they help versus when they quietly hurt.