Full-Text Search in PostgreSQL

For many apps, PostgreSQL's built-in full-text search is good enough to skip running a separate search engine. It handles stemming ("running" matches "run"), ranking and boolean queries.

Core concepts

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

SELECT to_tsvector('english', 'Running tips for beginners') @@ websearch_to_tsquery('english', 'run beginner');
-- true

websearch_to_tsquery accepts search-box syntax: quotes for phrases, -word to exclude, or for alternatives.

A searchable table

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

CREATE INDEX articles_search_idx ON articles USING gin (search);

Weights make title matches rank above body matches.

Search with ranking and snippets

SELECT id, title,
       ts_rank(search, q) AS rank,
       ts_headline('english', body, q, 'MaxWords=30, MinWords=10') AS snippet
FROM articles, websearch_to_tsquery('english', $1) q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

ts_headline is relatively slow; run it only on the final page of results (as here, after LIMIT).

Typos and partial words

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm_idx ON articles USING gin (title gin_trgm_ops);

SELECT title FROM articles
WHERE title % 'postgress'                 -- similar enough
ORDER BY similarity(title, 'postgress') DESC
LIMIT 5;

Trigram search is great for autocomplete and "did you mean" on short fields like names and titles.

When to reach for a dedicated engine

• Very large corpora with heavy search traffic.
• Advanced relevance tuning, faceting at scale, many languages.
• Even then, keeping Postgres as the source of truth and syncing to the search engine is the usual design.