Full-Text Search in PostgreSQL

PostgreSQL has built-in full-text search that handles stemming, ranking, and language-aware tokenization. For many applications, it eliminates the need for an external search engine like Elasticsearch.

Core Concepts: tsvector and tsquery

-- tsvector: a processed document (stems words, 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

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

-- Match a document against a query
SELECT to_tsvector('english', 'The quick brown fox')
    @@ to_tsquery('english', 'quick & fox');
-- Result: true

to_tsvector breaks text into normalized tokens (lexemes). to_tsquery creates a search pattern. The @@ operator checks for a match.

Searching a Table

-- Basic search on a text column
SELECT id, title, body
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ to_tsquery('english', 'postgresql & performance');

-- Using plainto_tsquery for user input (no special syntax needed)
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ plainto_tsquery('english', 'database optimization tips');

Adding a Stored tsvector Column with GIN Index

-- Add a pre-computed search column
ALTER TABLE articles
ADD COLUMN search_vector tsvector;

-- Populate it
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || COALESCE(body, ''));

-- Create a GIN index for fast lookups
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Keep it updated with a trigger
CREATE OR REPLACE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    to_tsvector('english', NEW.title || ' ' || COALESCE(NEW.body, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

Pre-computing the tsvector column and indexing it with GIN makes full-text search very fast, even on tables with millions of rows.

Ranking Results

SELECT
  id,
  title,
  ts_rank(search_vector, query) AS rank
FROM
  articles,
  plainto_tsquery('english', 'postgresql tutorial') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Common Use Cases

• Site-wide search for articles, products, or documentation
• Autocomplete and typeahead suggestions
• Filtering support tickets or comments by keyword
• Replacing simple ILIKE queries with language-aware, ranked search