Full-Text Search

PostgreSQL has a built-in full-text search engine based on tsvector (a sorted list of lexemes with position information) and tsquery (a search query with Boolean operators). The to_tsvector function converts a text string into a tsvector, applying the language-specific dictionary to remove stop words and stem words. to_tsquery and plainto_tsquery convert search strings into tsquery values. The @@ operator performs the text search match. The ts_rank and ts_rank_cd functions compute relevance scores for ordering results. For production use, you should store the tsvector as a generated column (or update it via a trigger) and index it with GIN for fast search.

Example

-- Add a generated tsvector column for fast FTS
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;

-- GIN index on the generated column
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- Full-text search with ranking
SELECT
  id, title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- plainto_tsquery — no special operator syntax needed
SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'index performance');

-- websearch_to_tsquery — Google-style search syntax
SELECT title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgresql -mysql');

-- Highlight matching terms
SELECT
  title,
  ts_headline('english', body, to_tsquery('postgresql'), 'MaxFragments=2') AS excerpt
FROM articles
WHERE search_vector @@ to_tsquery('postgresql');

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned