Full-Text Search with TSVector

Learning outcomes

• Implement fast full-text search without external search clusters
• Rank relevance using ts_rank and GIN indexing

Mental model

PostgreSQL native Full-Text Search (FTS) converts raw document text into normalized lexeme arrays (tsvector) and evaluates boolean search queries (tsquery) using GIN indexes without requiring external search clusters (Elasticsearch / Meilisearch).

Theory

• tsvector: Sorted list of distinct normalized lexemes (words reduced to base stems with positions).
• tsquery: Lexemes combined with boolean operators (& AND, | OR, ! NOT, <-> phrase proximity).
• ts_rank: Calculates document relevance based on lexeme frequency and weight positioning (Title 'A', Body 'B').

-- Convert text to tsvector and query with tsquery
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Generated column holding pre-computed tsvector with GIN index
ALTER TABLE articles ADD COLUMN search_vector tsvector 
    GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

CREATE INDEX idx_articles_fts ON articles USING gin(search_vector);

Alternatives and trade-offs

• LIKE / ILIKE (%search%): Slow sequential scan; cannot use standard B-Tree indexes for leading wildcards.
• PostgreSQL FTS (tsvector + GIN): Sub-millisecond text search, fully transactional (ACID), zero extra cluster infrastructure.
• Elasticsearch: Powerful multi-node fuzzy search; requires complex ETL pipelines and separate infrastructure management.

Failure modes and misconceptions

1. On-the-Fly to_tsvector() Calls: Calling to_tsvector('english', body) @@ ... inside SQL queries re-parses text for every row, bypassing GIN indexes. Always use pre-computed generated tsvector columns.
2. Missing Stopword Dictionaries: Failing to specify language dictionaries (e.g. 'english') results in indexing noise words ("the", "is", "at").

Decision scenario

Use PostgreSQL native tsvector with generated columns and GIN indexing for application search features (e.g. searching 500,000 articles or products) before introducing complex external search clusters.

Learning outcomes

• Build FTS pipelines with tsvector, tsquery, and phrase proximity operators.
• Optimize search performance using generated stored tsvector columns and GIN indexes.
• Rank search results using ts_rank and field weight positioning.

Trade-offs

Native PostgreSQL full-text search provides fast transactional search without extra infrastructure, but advanced typo-tolerant fuzzy matching requires extra extensions (pg_trgm).

Primary sources

• PostgreSQL 16 Architecture, MVCC & Query Optimization Manual — PostgreSQL Global Development Group, verified 2026-07-22