Full-Text Search

Full-Text Search

PostgreSQL has built-in full-text search that handles tokenisation, stemming, stop words, and relevance ranking — all without a separate search service. For many applications this is sufficient and avoids the operational overhead of Elasticsearch.

Core Concepts

• tsvector — a processed, normalised representation of a document (list of lexemes).
• tsquery — a search query of lexemes combined with & (AND), | (OR), ! (NOT).
• to_tsvector(config, text) — converts text to a tsvector using the named language config.
• to_tsquery(config, query) — parses a query string into a tsquery.
• plainto_tsquery — treats the input as plain words without operators (safer for user input).
• websearch_to_tsquery — supports Google-like syntax (-word, "exact phrase").

-- Basic FTS query
SELECT title
FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'postgres performance');

-- Add a generated tsvector column for performance
ALTER TABLE posts ADD COLUMN fts_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

-- Index it with GIN
CREATE INDEX idx_posts_fts ON posts USING GIN (fts_vector);

-- Query using the indexed column
SELECT title, ts_rank(fts_vector, query) AS rank
FROM posts, plainto_tsquery('english', 'postgres performance') query
WHERE fts_vector @@ query
ORDER BY rank DESC
LIMIT 10;