Full-Text Search in PostgreSQL

Why Full-Text Search in PostgreSQL?

PostgreSQL has built-in full-text search that handles stemming, ranking, phrase matching, and custom dictionaries. For many applications, it eliminates the need for Elasticsearch or Solr, keeping your stack simpler while providing powerful search capabilities.

tsvector & tsquery Basics

tsvector is a sorted list of normalized lexemes (words). tsquery is a search expression with boolean operators.

-- Convert text to tsvector (normalizes, stems, removes stop words)
SELECT to_tsvector('english', 'The quick brown foxes jumped over lazy dogs');
-- Result: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- Create a search query
SELECT to_tsquery('english', 'quick & fox');
-- Result: 'quick' & 'fox'

-- Match: does the document match the query?
SELECT to_tsvector('english', 'The quick brown fox')
    @@ to_tsquery('english', 'quick & fox');
-- Result: true

Adding Search to a Table

-- Option 1: Compute on the fly (simple but slower)
SELECT id, title FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ to_tsquery('english', 'postgresql & performance');

-- Option 2: Stored generated column + GIN index (recommended)
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 idx_articles_search ON articles USING GIN (search_vector);

-- Fast search using the index
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & performance')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'postgresql & performance')) DESC
LIMIT 20;

Ranking & Highlighting

-- Rank results by relevance
SELECT
  id,
  title,
  ts_rank(search_vector, query) AS rank,
  ts_rank_cd(search_vector, query) AS rank_cd
FROM articles, to_tsquery('english', 'database & optimization') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- Highlight matching terms in results
SELECT
  id,
  ts_headline('english', body, to_tsquery('english', 'postgresql'),
    'StartSel=, StopSel=, MaxWords=50, MinWords=20'
  ) AS snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');

Phrase Search & Advanced Queries

-- Phrase search: words must appear adjacent
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'machine learning');

-- Prefix matching: find words starting with...
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'post:*');

-- Boolean operators: & (AND), | (OR), ! (NOT), <-> (followed by)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english',
  '(postgres | postgresql) & !mysql & performance'
);

-- Websearch-friendly syntax (PG11+)
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english',
  '"full text search" postgresql -mysql'
);