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).
Raw Text Document
Stemming & Stopword Removal (to_tsvector)
Match Query Operator (@@ to_tsquery)
GIN Inverted Index Scan
Rank Relevance (ts_rank)
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').
sql(12 lines)
1-- Convert text to tsvector and query with tsquery
2SELECT title, ts_rank(search_vector, query) AS rank
3FROM articles, to_tsquery('english', 'database & postgresql') query
4WHERE search_vector @@ query
5ORDER BY rank DESC;
6
7-- Generated column holding pre-computed tsvector with GIN index
8ALTER TABLE articles ADD COLUMN search_vector tsvector
9 GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
10
11CREATE 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
- On-the-Fly
to_tsvector()Calls: Callingto_tsvector('english', body) @@ ...inside SQL queries re-parses text for every row, bypassing GIN indexes. Always use pre-computed generatedtsvectorcolumns. - Missing Stopword Dictionaries: Failing to specify language dictionaries (e.g.
'english') results in indexing noise words ("the", "is", "at").
Reflect before revealing the guide
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
tsvectorcolumns and GIN indexes. - Rank search results using
ts_rankand 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).