Full-Text Search

Why LIKE isn't full-text search#

WHERE body LIKE '%database%' finds literal substrings, but it has three real limitations once "search" is an actual product feature rather than a quick filter: it can't use a normal B-tree index (a leading wildcard forces a sequential scan), it has no concept of word variants (database won't match databases or databasing), and it has no way to rank results by relevance — every match is equally "found," whether the word appears once in a footnote or ten times in the title. PostgreSQL's built-in full-text search solves all three.

tsvector and tsquery#

Full-text search in PostgreSQL revolves around two special types:

• tsvector — a document reduced to a sorted list of normalized lexemes (word stems, roughly), with position information. to_tsvector('english', 'Running databases quickly') produces something like 'databas':2 'quick':3 'run':1 — notice "Running" became run, and "databases" became databas: this is stemming, matching a word to its root form so a search for "run" also matches "running" or "ran".
• tsquery — a search query, parsed into the same normalized lexeme form, optionally combined with & (AND), | (OR), and ! (NOT). to_tsquery('english', 'database & queries') searches for documents containing both stems.

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

CREATE TABLE articles (
    id    SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body  TEXT NOT NULL
);

INSERT INTO articles (title, body) VALUES
    ('Getting started with PostgreSQL', 'PostgreSQL is a powerful open-source relational database used by teams everywhere.'),
    ('Indexing strategies', 'Indexes speed up queries by letting the database avoid full table scans.'),
    ('Baking sourdough bread', 'A good sourdough starts with a healthy, active starter and patience.');

SELECT title FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'database & queries');

title
Indexing strategies

Only the second row matches — it's the only one containing stems of both "database" and "queries." Note it matched despite the text saying "queries" and the search term being written the same way; had the search been to_tsquery('english', 'query'), it would still match, because both "query" and "queries" stem to the same lexeme.

Indexing a tsvector for real performance#

Calling to_tsvector() fresh on every row for every query (as above) still has to process the full text of every document on every search — fine for a demo, far too slow once a table holds any real volume. The fix is storing the computed tsvector in an indexed column, using a generated column so it stays in sync automatically:

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

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

This is the same GIN index type covered on the indexes-and-query-planning page for JSONB and array containment — full-text search is another case where a value is really a set of things (lexemes) to search within, exactly what GIN is built for. Queries now hit the indexed column directly:

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'database');

Parsing user input safely: plainto_tsquery and websearch_to_tsquery#

to_tsquery() expects its input already in query syntax (&, |, !) — passing raw user input like "how do I speed up queries?" directly into it raises a syntax error. Two friendlier alternatives parse ordinary text instead:

• plainto_tsquery('english', 'speed up queries') — treats the input as plain words, ANDing them all together (speed & queri roughly), ignoring any special characters entirely.
• websearch_to_tsquery('english', 'speed OR "up queries"') — understands a small, web-search-like syntax (quoted phrases, OR, - to exclude a word), the best default for a search box a real user types into.

SELECT title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgres indexing');

Ranking results#

A match is binary with @@ alone — either a document matched or it didn't, with no sense of "how well." ts_rank() scores a match based on how often and how prominently the search terms appear, letting the best matches surface first:

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
LIMIT 10;

ts_rank_cd ("cover density") is a variant that also weighs how close together the matched terms appear in the document — generally a better fit for longer documents where "both words appear, right next to each other" should outrank "both words appear, in unrelated paragraphs."

Common mistakes#

• Calling to_tsvector() on a plain, unindexed column inside WHERE on every query — this still scans and reprocesses the full text of every row every time; store it as an indexed generated column instead.
• Using to_tsquery() directly on raw, unsanitized user input — a search box that lets someone type &, |, or unbalanced parentheses will throw a syntax error; websearch_to_tsquery() is the safer default for user-facing search.
• Mismatching the language configuration between where the tsvector was built and where the tsquery is parsed ('english' vs 'simple', for instance) — stemming rules differ between configurations, so a mismatch can silently cause expected matches to fail.
• Expecting full-text search to handle typos — stemming normalizes word forms ("running" → "run"), not misspellings ("databse" won't match "database"). Typo-tolerant fuzzy matching is a different tool, covered on the extensions page next.