Full-Text Search in PostgreSQL

tsvector and tsquery

PostgreSQL ships its own full-text engine. Text is converted into a tsvector of normalized lexemes, then matched against a tsquery.

Basic Match

SELECT title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('database & indexes');

Ranking Results

SELECT title,
       ts_rank(to_tsvector('english', body), to_tsquery('performance')) AS rank
FROM articles
ORDER BY rank DESC;

Indexing for Speed

CREATE INDEX idx_articles_fts
  ON articles USING GIN (to_tsvector('english', body));

A GIN index makes searches fast and supports prefix matching with a colon suffix.

Key Points

• to_tsvector converts text and @@ tests the match.
• ts_rank orders results by relevance.
• GIN indexes make full-text search fast.
• Choose a dictionary matching your content language.