6 Postgres for full-text search
This chapter shows how to go beyond simple text operators by using Postgres’s built-in full-text search to deliver relevant, language-aware results. Postgres converts documents into lexemes through tokenization and normalization (case-folding, stemming, and stop-word removal) controlled by language-specific configurations, and stores them in the tsvector type. Application queries are normalized into tsquery, then matched with @@ using boolean and proximity operators. The chapter develops these ideas through a movie recommendation service, turning titles and descriptions into searchable vectors to power precise, responsive search.
To prepare data, to_tsvector generates lexemes with position information, which can be stored in a generated column and kept in sync automatically. Queries are turned into tsquery via functions like plainto_tsquery for simple input, to_tsquery for boolean and phrase logic (including distance operators), and related helpers for web-style or phrase searches. Relevance ranking uses ts_rank and ts_rank_cd (frequency, position, and density), and weighting (A–D) via setweight lets titles outrank descriptions; ranks can also be normalized by document length. For presentation, ts_headline extracts and highlights matching fragments, with options to control the number, size, and delimiters of snippets.
Performance hinges on indexing the tsvector column. GIN is the default choice: it indexes actual lexemes and yields very fast lookups, at the cost of larger indexes and slower maintenance. GiST uses compact signatures to organize documents, building smaller indexes more quickly but sometimes requiring row rechecks due to signature collisions; signature length trades precision for size. Both dramatically accelerate @@ searches compared to scans, and the chapter explains how to read the resulting plans. If your workload often relies on positional data within the index itself, consider alternatives that store positions directly; otherwise, pick GIN for fastest queries or GiST when index size and update speed matter most.
A sample GIN index showing that the “fight” and “histor” lexemes appear in three distinct movies
A sample GiST index highlighting all the index entries visited during a search for documents containing the "histor" lexeme (00000001)
Summary
- Postgres comes with built-in full-text search capabilities for applications that need to analyze and query the contents of text documents.
- Like other full-text search engines, Postgres performs tokenization and normalization of original documents to produce tsvector lexemes.
- The database provides various functions and operators that let us easily query tsvector lexemes, with the ability to rank and highlight search results.
- GIN and GiST are the two primary index types used to speed up full-text search queries in Postgres.
FAQ
How is Postgres full-text search different from LIKE/regex matching?
Basic operators like =, LIKE/ILIKE, and ~/~* compare raw text and don’t handle language features. Full-text search tokenizes and normalizes text into lexemes, removes stop words, stems words to roots, supports relevance ranking, and uses specialized indexes (GIN/GiST) for fast, relevant results.What are tokenization and normalization, and how can I inspect them?
Tokenization splits a document into tokens (words, numbers, etc.). Normalization converts tokens to lexemes (root forms), removes stop words, and records positions. Use ts_debug to see the process and dictionaries in action:SELECT * FROM ts_debug('english','5 explorers are traveling...');What is tsvector and how do I generate lexemes from text?
tsvector stores normalized lexemes with positional data. Generate it with to_tsvector, optionally passing a config:to_tsvector('english', 'The explorers must save...'). Stop words are removed but their positions are preserved for operators like <->.How should I store lexemes in a table, and why use a generated column?
Add a tsvector column and compute it from source text with a stored generated column so it stays in sync:ALTER TABLE omdb.movies
ADD COLUMN lexemes tsvector GENERATED ALWAYS AS (
to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,''))
) STORED;Pass the config explicitly because generated expressions must be immutable.How do I convert user input into a tsquery for searching?
Use: - plainto_tsquery for simple queries (inserts ANDs, handles stop words):plainto_tsquery('a computer animated film').
- to_tsquery for advanced logic with AND &, OR |, NOT !, FOLLOWED BY <->, distances <N>, and grouping.
- phraseto_tsquery inserts <-> between terms.
- websearch_to_tsquery handles web-style input (AND/OR/quotes) robustly.How do I run a full-text search and filter by phrases or distance?
Compare tsvector to tsquery with@@: ... WHERE lexemes @@ plainto_tsquery('computer animated film');Phrase and distance examples: to_tsquery('''The Lion King''') -- becomes 'lion' <-> 'king'
to_tsquery('return <3> king')How can I rank results by relevance, and what’s the role of weights?
Use ts_rank (or ts_rank_cd for term density) to sort by frequency/positions:SELECT ..., ts_rank(lexemes, to_tsquery('ghosts')) AS rank
ORDER BY rank DESC;Label important parts (e.g., title) with higher weights via setweight and optionally pass custom weights: setweight(to_tsvector('english', name),'A') ||
setweight(to_tsvector('english', description),'B')Can I restrict searches or ranking to specific weighted parts (like titles)?
Yes. Store weights with setweight (A highest, D lowest). Restrict search to a weight in tsquery, e.g., only title:WHERE lexemes @@ to_tsquery('ghosts:A'). ts_rank also respects stored weights.How do I highlight matched terms in text snippets?
Use ts_headline to return fragments with matches highlighted (default tags). Example with options:ts_headline(
description, to_tsquery('pirates'),
'MaxFragments=3, MinWords=5, MaxWords=10, FragmentDelimiter=<ft_end>'
)Options control fragment count/length and delimiters.
Just Use Postgres! ebook for free
