PostgreSQL Full-Text Search
Full-text search (FTS) in PostgreSQL lets you search natural-language text properly — matching word stems, ignoring noise words, ranking results by relevance — using only the database you already have. It's far more capable than LIKE '%term%': it understands that "running" should match "run," that "the" and "a" are stopwords, and that a document mentioning your term five times is more relevant than one mentioning it once.
For a large share of applications, Postgres FTS is enough to skip a dedicated search engine entirely. You keep search results transactionally consistent with your data, avoid syncing to a separate system, and get solid relevance ranking — all in SQL. Knowing where it shines and where a specialized engine like Elasticsearch earns its keep is the practical skill this page builds.
TL;DR
- Postgres FTS does real linguistic search — stemming, stopword removal, and relevance ranking — not just substring matching.
- Two core types:
tsvector(a document processed into normalized lexemes) andtsquery(a search query). The@@operator matches them. - GIN-index the
tsvectorso searches are fast; without it, every row is reprocessed per query. - Rank results with
ts_rank/ts_rank_cd, and highlight matches withts_headline. - A generated
tsvectorcolumn (kept in sync automatically) is the clean, modern way to index searchable text. - Postgres FTS is enough for most in-app search; reach for Elasticsearch at large scale, for advanced relevance/typo-tolerance, or heavy analytics on text.
Quick Example
Turn text into a searchable tsvector, index it, and run a ranked search:
to_tsquery('english', 'database & indexing') matches documents containing both stemmed terms, and ts_rank orders them by relevance — real search, no external engine.
Core Concepts
tsvector and tsquery
FTS revolves around two types:
tsvector— a document converted into a sorted list of lexemes (normalized word roots) with their positions.to_tsvector('english', 'The cats are running')yields something like'cat':2 'run':4— "the" and "are" dropped as stopwords, "cats"→"cat", "running"→"run" via stemming.tsquery— a search condition of lexemes combined with operators:&(AND),|(OR),!(NOT),<->(followed by / phrase).to_tsquery('english', 'cat & run').
The @@ operator tests whether a tsvector matches a tsquery. Because both sides are normalized through the same configuration, "running cats" in a document matches a "cat & run" query.
Text Search Configurations
The 'english' argument is a text search configuration — it dictates the language rules: which stemmer, which stopwords, how tokens are parsed. Use the configuration matching your content's language so stemming and stopwords work correctly. The same word normalizes differently under different configurations, so document and query must use the same one.
Ranking and Highlighting
Matching is binary; ranking orders matches by relevance:
ts_rank(vector, query)— ranks by term frequency and weights.ts_rank_cd(vector, query)— cover-density ranking that also rewards matched terms being close together (good for phrase-like relevance).- Weights (A/B/C/D via
setweight) let you say "a match in the title matters more than in the body," so titles rank above body mentions. ts_headline(...)returns a snippet with the matched terms highlighted — the "…matched term in context…" excerpt for result lists.
Indexing for Speed
Without an index, every search reprocesses and scans all rows. A GIN index on the tsvector makes FTS fast:
The clean modern approach is a generated tsvector column (GENERATED ALWAYS AS (...) STORED) that Postgres keeps in sync as rows change, indexed with GIN. This replaces the older pattern of triggers maintaining a separate column. For combining fields with different importance, build the vector with weights:
Best Practices
Store a Generated, Indexed tsvector
Don't call to_tsvector on raw columns at query time — it can't use an index efficiently and reprocesses every row. Materialize a generated tsvector column, GIN-index it, and search against that. It stays consistent automatically and queries stay fast.
Match the Configuration to the Language
Use the text search configuration for your content's language on both the stored vector and the query. Mismatched configurations (English stemming on French text, or different configs for document vs query) produce wrong or missed matches.
Weight Fields by Importance
Use setweight so matches in important fields (title, tags) rank above matches in the body. Search relevance improves markedly when the ranking reflects where the term appeared, not just how often.
Use plainto_tsquery / websearch_to_tsquery for User Input
Raw user text isn't valid tsquery syntax. Use plainto_tsquery (treats input as AND-ed words) or websearch_to_tsquery (supports quotes and - like a web search box) to safely turn user queries into tsquery, instead of hand-building query strings.
Know When to Graduate to a Search Engine
Postgres FTS covers most needs, but a dedicated engine wins for: very large corpora with high query volume, advanced relevance tuning, fuzzy/typo tolerance and autocomplete at scale, faceting, and heavy text analytics. If search is a core product surface with those demands, evaluate Elasticsearch/OpenSearch.
Common Mistakes
to_tsvector at Query Time on Raw Columns
Feeding Raw User Input to to_tsquery
to_tsquery requires strict syntax and errors on arbitrary text (spaces, punctuation). Use plainto_tsquery or websearch_to_tsquery for user-entered search terms.
Using LIKE '%term%' and Calling It Search
Substring matching with leading wildcards can't use a normal index (scans every row), doesn't stem or rank, and matches inside words. It's not full-text search. Use tsvector/tsquery for real text search.
FAQ
How is full-text search different from LIKE?
LIKE '%term%' does raw substring matching — it can't stem ("running" won't match "run"), doesn't remove stopwords or rank by relevance, matches inside unrelated words, and (with a leading %) can't use an index, so it scans every row. Full-text search normalizes text into lexemes, understands word roots and stopwords, ranks results by relevance, and is fast with a GIN index. They solve different problems.
What are tsvector and tsquery?
tsvector is a document processed into normalized lexemes (stemmed word roots, stopwords removed, positions recorded) — the searchable representation of your text. tsquery is a search condition of lexemes combined with operators (&, |, !, <->). The @@ operator matches a tsvector against a tsquery; because both are normalized through the same configuration, related word forms match.
How do I make it fast?
Materialize the tsvector in a stored (ideally generated) column and create a GIN index on it, then search against that column. Computing to_tsvector on raw columns at query time can't use an index and reprocesses every row. The generated-column-plus-GIN-index pattern is the standard, fast setup.
How do I rank and highlight results?
Use ts_rank or ts_rank_cd to order matches by relevance (cover-density ts_rank_cd also rewards matched terms being close together), and apply field weights with setweight so title matches outrank body matches. Use ts_headline to produce highlighted snippets showing the matched terms in context for your result list.
When should I use Elasticsearch instead of Postgres FTS?
When search is a demanding, core product surface: very large corpora with high query volume, advanced relevance tuning, fuzzy/typo tolerance and rich autocomplete at scale, faceted search, or heavy text analytics. For typical in-app search over your data, Postgres FTS is simpler, transactionally consistent, and avoids running and syncing a second system. Graduate to Elasticsearch/OpenSearch when you outgrow it.
Related Topics
- PostgreSQL — The database this search is built into
- Elasticsearch — The dedicated search engine to graduate to
- Database Indexing — GIN indexing fundamentals
- pgvector — Semantic (vector) search, complementary to keyword FTS
- Agentic RAG & Advanced Retrieval — Hybrid keyword + vector search
- PostgreSQL Performance Tuning — Making search queries fast
- JSONB in PostgreSQL — Another semi-structured Postgres capability