PostgreSQL Full-Text Search Explained: tsvector and tsquery

PostgreSQL full-text search is a built-in search capability that goes beyond LIKE '%term%' pattern matching — it normalizes text into searchable tokens, understands word variants through stemming, ranks results by relevance, and can be indexed for fast lookups at scale, all without adding a separate search engine to your stack.

Why LIKE isn’t full-text search

A WHERE description LIKE '%running%' query does substring matching: it won’t match “run” or “runs,” can’t rank results by relevance, and can’t use a standard index efficiently for anything but a prefix match. Full-text search solves a different problem — matching meaningful words, accounting for stems and common variants, and returning results ordered by how relevant they are to the query, not just whether they match.

tsvector: documents as searchable tokens

PostgreSQL represents searchable text as a tsvector — a sorted list of normalized lexemes (word stems) with position information. You produce one with to_tsvector():

SELECT to_tsvector('english', 'The runners were running quickly');
-- 'quickli':5 'run':2,4 'runner':2

Notice “runners” and “running” both reduce to the stem run, and stop words like “the” and “were” are dropped entirely — this is what lets a search for “run” match a document containing “running” without an exact string match. The 'english' argument selects a text search configuration, which controls the stemming rules and stop word list for a given language.

tsquery: the search side

The query side uses the mirror-image type, tsquery, typically built with to_tsquery() or the friendlier plainto_tsquery() and websearch_to_tsquery():

SELECT to_tsquery('english', 'run & quick');
-- 'run' & 'quick'

&, |, and ! combine terms as AND, OR, and NOT. websearch_to_tsquery() accepts more natural input — quoted phrases and bare words separated by spaces — and is generally the right choice for search boxes fed directly by end users, since it tolerates malformed input rather than erroring.

Matching and ranking

The @@ operator tests whether a tsvector matches a tsquery:

SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'run & quick');

For relevance ordering, ts_rank() scores each match based on how often and how prominently the query terms appear:

SELECT title, ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'run & quick') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC;

ts_headline() goes a step further, returning the matched text with query terms highlighted — useful for showing search result snippets without a separate templating pass.

Indexing with GIN

Computing to_tsvector() on every row for every query doesn’t scale. The standard approach is a generated column holding the precomputed tsvector, backed by a GIN index — the same index type covered in B-tree, GIN, and GiST index types:

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

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

With the index in place, the @@ query above becomes an index lookup instead of a sequential scan over every row — check the difference directly with EXPLAIN ANALYZE before and after adding the index on a nontrivial table.

Weighting different fields

Real search rarely treats every field equally — a match in a title usually matters more than the same word buried in a long body. setweight() tags portions of a tsvector with a priority label (A through D) before they’re combined, and ts_rank() takes those weights into account when scoring:

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

A query matching a term in the title now ranks higher than the identical term appearing only in the body, without any extra logic in the application layer — the weighting lives entirely in the generated column definition.

Multiple languages and configurations

The 'english' configuration argument used throughout this article is a named text search configuration bundling a stemming algorithm and a stop-word list for that language; Postgres ships configurations for a range of languages out of the box, and default_text_search_config sets what a session uses when none is specified explicitly. For an application serving content in more than one language, storing a language tag alongside the content and passing the right configuration per row at write time (to_tsvector(lang_config, body)) keeps stemming accurate — English stemming rules applied to French text will produce lexemes that don’t match a French query at all, silently breaking search rather than raising an error.

Where it fits — and where it doesn’t

Built-in full-text search is a strong default when:

• You’re already storing the data in Postgres and don’t want to sync it into a separate search index.
• Your search needs are keyword-driven — product names, article titles, exact terms — rather than conceptual similarity.
• Query volume and dataset size are moderate; a well-indexed tsvector column comfortably handles a lot before it becomes a bottleneck.

It’s a weaker fit once you need faceted search, typo tolerance, or search that understands meaning rather than literal word stems — a query for “affordable laptop” won’t surface a document that only says “budget notebook.” For that kind of semantic matching, see vector search vs full-text search, including how the two are often combined as hybrid search. At larger scale or with more advanced ranking needs, a dedicated search engine becomes worth the operational overhead of syncing a second system — but for a huge share of applications, tsvector and a GIN index cover the need without adding infrastructure. Postgres’s jsonb support is a natural neighbor here too: it’s common to full-text index one column while storing flexible metadata in a jsonb column right beside it, and both compete with MySQL’s own full-text options when the database choice itself is still on the table.

The takeaway

PostgreSQL’s full-text search normalizes text into tsvector documents, matches them against tsquery queries with the @@ operator, and ranks results with ts_rank() — all backed by stemming and stop-word handling built into the database. A generated tsvector column with a GIN index turns full-table scans into index lookups, making it a practical, dependency-free search layer for keyword-driven search at moderate to fairly large scale, right up until the point you need semantic matching or faceted search that a dedicated search engine or vector index handles better.