Full-Text Search

This chapter covers a genuinely built-in Postgres capability with no dedicated MySQL comparison to lean on — full-text search — and closes with an honest look at exactly where it stops being the right tool.

Why Full-Text Search Needs Its Own Feature

Ordinary LIKE '%word%' matching is a poor substitute for real search: it can't be indexed efficiently for arbitrary substrings, it doesn't understand word forms (searching "running" won't match a row containing only "run"), it produces no ranking of how well a result actually matches, and it has no concept of ignoring common, low-value words like "the" or "and." Full-text search is a genuinely different capability, built specifically to solve all four problems at once.

tsvector & tsquery

A tsvector is a preprocessed, normalized representation of a document's searchable text — it converts raw text into lexemes (normalized word forms), strips out stop words, and can optionally weight different parts of a document differently (a title mattering more than body text, for instance). A tsquery is a processed search query, converted into the same lexeme form, supporting boolean operators: & (AND), | (OR), ! (NOT), and <-> (phrase/proximity).

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

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

The @@ match operator tests whether a tsvector satisfies a tsquery, returning a simple true/false — but the real value shows up once these are combined into an actual search query.

A Worked Example

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  search_vector TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B')
  ) STORED
);

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- A user-friendly search using natural query syntax
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, websearch_to_tsquery('english', 'postgres indexing') query
WHERE search_vector @@ query
ORDER BY rank DESC;

setweight makes title matches rank higher than body matches; websearch_to_tsquery parses ordinary user search input (rather than requiring the user to type boolean operators directly); and ts_rank orders results by actual relevance, not just by whether they matched at all.

Honest Contrast — Postgres Full-Text Search vs. a Dedicated Search Engine

Postgres's own full-text search is a real, built-in capability with no extra infrastructure to deploy — a genuine advantage for small-to-medium applications that need "good enough" search without operating a whole separate system. But dedicated search engines like Elasticsearch/OpenSearch offer real things Postgres's own full-text search doesn't attempt to match: distributed horizontal scaling across a cluster, more sophisticated relevance-tuning and language analyzers, faceted search/aggregations as a first-class feature, and typo-tolerant fuzzy matching out of the box — because search is their entire purpose, not a feature layered onto a general-purpose relational engine.

The honest guidance: Postgres's own full-text search is the right choice when search is a secondary feature of a primarily-relational application at moderate scale. A dedicated search engine is the right choice when search itself is the primary product, or when scale and sophistication genuinely demand it. This is the same "when to choose each" judgment from postgres1-1, applied once more — this time between a built-in feature and an entirely separate system.

Hands-On Exercises