Full-text search in PostgreSQL

For many products, Postgres full-text search (FTS) is enough: no extra cluster to operate, transactional consistency with your rows, and good-enough relevance with tuning.

tsvector and tsquery#

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

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Query:

SELECT id, title, ts_rank(search_vector, websearch_to_tsquery('english', 'react hooks')) AS rank
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'react hooks')
ORDER BY rank DESC
LIMIT 20;

Language and stemming#

Pick a text search config per language column. Mixed-language content may need separate columns or simple config to avoid bad stemming.

When not Postgres#

Faceted search across huge catalogs, typo tolerance at Google scale, or heavy aggregations often warrant Meilisearch, Typesense, or OpenSearch. Start in SQL; migrate when metrics demand.

Summary#

GIN + generated tsvector columns give fast good-enough search with minimal moving parts. Rank with ts_rank_cd for finer control and measure p95 query latency under production data volumes.