PostgreSQL Full-Text Search: tsvector, GIN Indexes and Ranking

Before adding Elasticsearch or another search engine, check whether PostgreSQL’s built-in full-text search is enough. It handles stemming (“installing” matches “install”), stop words, ranking, phrase search and highlighting, with fast GIN indexes, all inside the database you already run. For blogs, documentation, product catalogs and admin search, it’s often all you need.

The building blocks

tsvector: a document processed into normalized words (lexemes) with positions.
tsquery: a search query of lexemes with operators (& and, | or, ! not, <-> followed by).
@@: the match operator.

SELECT to_tsvector('english', 'Installing Nginx on Ubuntu servers');
-- 'instal':1 'nginx':2 'server':5 'ubuntu':4

SELECT to_tsvector('english', 'Installing Nginx on Ubuntu servers')
       @@ to_tsquery('english', 'install & server');
-- true

Parse user input safely

Don’t pass raw user input to to_tsquery: syntax errors break the query. Use websearch_to_tsquery, which understands search-engine style input:

SELECT websearch_to_tsquery('english', 'nginx "reverse proxy" -apache');
-- 'nginx' & 'revers' <-> 'proxi' & !'apach'

Quotes become phrases, - excludes words, and or works too. plainto_tsquery simply ANDs all words.

Store the search vector

Compute the tsvector once, in a generated column (PostgreSQL 12+), and index it:

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 idx_articles_search ON articles USING gin (search);

Weights (A highest to D lowest) make title matches rank above body matches.

Search and rank

SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', 'docker compose postgres') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

ts_rank scores by frequency and weight. ts_rank_cd also considers how close the words are to each other. Check that the GIN index is used with EXPLAIN ANALYZE.

Highlight matches

SELECT title,
       ts_headline('english', body, q, 'MaxWords=35, MinWords=15, StartSel=<mark>, StopSel=</mark>') AS snippet
FROM articles, websearch_to_tsquery('english', 'reverse proxy') AS q
WHERE search @@ q
ORDER BY ts_rank(search, q) DESC
LIMIT 10;

ts_headline is relatively slow, as it processes the full text. Run it only on the final page of results, not on every matching row.

Languages

Configurations handle stemming and stop words per language: english, german, french, spanish, russian, and others (\dF in psql lists them). Use simple for no stemming (codes, names). For multilingual content, store the language per row, and use it:

to_tsvector(lang::regconfig, body)

Chinese, Japanese and Korean need extra tokenizers (for example the zhparser or pgroonga extensions), because words aren’t separated by spaces.

Typos and partial words

Full-text search matches whole lexemes. “ngin” won’t match “nginx”, and “ubunto” won’t match “ubuntu”. Combine it with pg_trgm for fuzzy matching and autocomplete:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_articles_title_trgm ON articles USING gin (title gin_trgm_ops);
SELECT title FROM articles WHERE title % 'ubunto' ORDER BY similarity(title, 'ubunto') DESC LIMIT 5;

Prefix search also works in tsquery: to_tsquery('english', 'ngin:*'). See PostgreSQL extensions.

When to use a dedicated search engine

PostgreSQL FTS is great up to millions of documents. Consider Meilisearch, Typesense, Elasticsearch or OpenSearch when you need:

typo tolerance and instant search-as-you-type as core features;
facets and complex relevance tuning at large scale;
search load that would compete with your transactional database.

See Elasticsearch vs Meilisearch and installing Meilisearch.

Performance tips

Always search a stored, indexed tsvector column, not to_tsvector(body) computed per row.
GIN indexes are larger and slower to update than B-tree indexes. Bulk loads are faster with the index created afterwards.
Paginate with LIMIT, and avoid count(*) of all matches on every request.

Quick reference

Task	SQL
Document → vector	`to_tsvector('english', text)`
User input → query	`websearch_to_tsquery('english', input)`
Match	`search @@ query`
Index	`CREATE INDEX … USING gin (search)`
Rank	`ts_rank(search, query)`
Snippet	`ts_headline(…)`
Fuzzy	pg_trgm `%` and `similarity()`

PostgreSQL
Search
Database