Full-text search in Postgres, without Elasticsearch

Understand the two types#

Postgres full-text search is two data types and one operator, and everything else is detail.

tsvector is a document, processed: lowercased, split into lexemes, stemmed, and stripped of stop words.

SELECT to_tsvector('english', 'The cats were running quickly through gardens');
-- 'cat':2 'garden':7 'quick':5 'run':4

Six words became four lexemes. "The" and "were" are stop words, gone. "cats" stemmed to "cat", "running" to "run", "quickly" to "quick". The numbers are positions, which is how phrase search works later.

tsquery is a search expression over the same lexemes:

SELECT websearch_to_tsquery('english', 'running cats');
-- 'run' & 'cat'

And @@ asks whether the document matches the query. Because both sides get the same stemming, a search for "running" finds a document containing "ran", which is the entire point.

SELECT to_tsvector('english', 'The cat ran') @@ websearch_to_tsquery('english', 'running cats');
-- true

Store the vector in a generated column#

Computing to_tsvector at query time means a sequential scan over every row — fine for 500 rows, unusable at 500,000. Store it, and let Postgres keep it current.

CREATE TABLE articles (
  id         bigserial PRIMARY KEY,
  title      text NOT NULL,
  summary    text,
  body       text NOT NULL,
  tags       text[] NOT NULL DEFAULT '{}',
  published_at timestamptz
);
 
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(array_to_string(tags, ' '), '')), 'B') ||
    setweight(to_tsvector('english', coalesce(summary, '')), 'C') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'D')
  ) STORED;
 
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Three things are doing work here.

GENERATED ALWAYS AS ... STORED means Postgres recomputes the column on every insert and update, automatically. No trigger to write, no application code to remember, no possibility of drift. This replaced the trigger-based approach everyone used before Postgres 12 and it is strictly better.

setweight tags each lexeme with a weight class. A term in the title (A) scores far higher than the same term in the body (D) — by default A is worth about four times D. This single feature accounts for most of the difference between "search that feels right" and "search that returns whatever is longest."

coalesce is not optional. Concatenating a NULL tsvector produces NULL, which silently makes the entire row unsearchable. One nullable column without coalesce and you'll spend an afternoon wondering why some articles never appear.

The GIN index is what makes @@ fast. It's slower to update than GiST and much faster to query, which is the right trade for search.

Query with the syntax users already know#

SELECT
  id,
  title,
  ts_rank_cd(search_vector, query) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS query
WHERE search_vector @@ query
ORDER BY rank DESC, published_at DESC
LIMIT 20;

Use websearch_to_tsquery. It parses the syntax people already type into search boxes:

Critically, it never throws on malformed input. to_tsquery will happily raise a syntax error on an unbalanced quote, which means user input has to be sanitized before it reaches the database. websearch_to_tsquery handles garbage gracefully — pass user input straight in.

For ranking, ts_rank_cd (cover density) beats plain ts_rank on multi-word queries because it accounts for how close the matched terms sit to each other. A document with "connection pool" adjacent outranks one where "connection" appears in paragraph 2 and "pool" in paragraph 40.

The ORDER BY rank DESC, published_at DESC tiebreak matters more than it looks: without it, equally-ranked rows come back in whatever order the planner chose, which changes between runs and makes pagination inconsistent.

Return highlighted snippets#

Search results need context showing why each row matched.

SELECT
  id,
  ts_headline('english', title, query,
    'StartSel=<mark>, StopSel=</mark>, HighlightAll=true') AS title_html,
  ts_headline('english', body, query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15, '
    'ShortWord=3, MaxFragments=2, FragmentDelimiter= … ') AS snippet,
  ts_rank_cd(search_vector, query) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS query
WHERE search_vector @@ query
ORDER BY rank DESC, published_at DESC
LIMIT 20;

MaxFragments=2 returns two separate matching passages joined by the delimiter rather than one contiguous window — much more useful when the query terms appear in different sections.

One performance note: ts_headline runs on the original text, not the index, so it is genuinely expensive. It runs on every row the query returns, which is why you want it inside a LIMITed query rather than applied before ranking. If it shows up in your slow query log, compute it only for the page being displayed:

WITH ranked AS (
  SELECT id, body, ts_rank_cd(search_vector, query) AS rank, query
  FROM articles, websearch_to_tsquery('english', $1) AS query
  WHERE search_vector @@ query
  ORDER BY rank DESC LIMIT 20
)
SELECT id, rank,
       ts_headline('english', body, query, 'MaxFragments=2') AS snippet
FROM ranked;

Add typo tolerance and autocomplete#

Full-text search matches lexemes exactly. A user typing "postgers" gets nothing. Two extensions cover the gap.

Trigram matching for typos and partial words:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
 
-- Fuzzy fallback: similarity above a threshold.
SELECT id, title, similarity(title, $1) AS score
FROM articles
WHERE title % $1              -- uses pg_trgm.similarity_threshold, default 0.3
ORDER BY score DESC LIMIT 10;

The useful pattern is a two-stage query: run full-text search first, and only if it returns nothing, fall back to trigram similarity. Users get precise results when their spelling is right and forgiving results when it isn't.

Prefix matching for autocomplete:

-- The ':*' suffix makes the last term a prefix match.
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postg:*')
LIMIT 10;

To build that query safely from user input, strip non-word characters and append :* to the final term. Never interpolate raw input into to_tsquery — that one does throw on bad syntax.

Confirm the index is actually used#

The most common failure here is silent: everything works, but on a sequential scan, and nobody notices until the table grows.

EXPLAIN ANALYZE
SELECT id FROM articles, websearch_to_tsquery('english', 'postgres index') AS query
WHERE search_vector @@ query
ORDER BY ts_rank_cd(search_vector, query) DESC
LIMIT 20;

You want Bitmap Index Scan on articles_search_idx in the plan. If you see Seq Scan on articles, one of these is true:

• The table is small enough that the planner correctly prefers a scan. Test with realistic data volume before concluding anything.
• Your WHERE clause doesn't match the indexed expression — a common cause is computing to_tsvector(body) inline instead of querying the stored column.
• The language config in the query differs from the one in the generated column. 'english' in the column and 'simple' in the query means the index cannot be used.

Note that ranking is not indexed — ts_rank_cd runs on every matching row. If a common query matches 100,000 rows, ranking dominates the query time. Narrow the candidate set with additional indexed predicates (date range, category, workspace) before ranking becomes the bottleneck.