PostgreSQL Full-Text Search — tsvector, tsquery, GIN, Ranking
Fast recall
Quick revision
Core answer
@@ operator and indexed with GIN. to_tsvector('english', body) tokenizes, strips stop words, and stems; a GIN index turns search into sub-second lookups on millions of rows. `ts_rank` orders by relevance; ts_headline returns highlighted snippets. For datasets under ~10M documents with straightforward keyword search, PostgreSQL FTS removes the operational cost of Elasticsearch.Whether you can build a practical FTS pipeline end-to-end: store a tsvector, index it with GIN, query with the right tsquery variant, rank results, and integrate with Spring Data JPA via a native query.
Using `LIKE '%keyword%'` for search, or calling `to_tsvector` in the query predicate without indexing it — both cause a full table scan because the planner cannot use any index for those expressions.
Explain lexemes, stemming, and configurations; compare `to_tsquery` vs `plainto_tsquery` vs `phraseto_tsquery` vs `websearch_to_tsquery`; mention `pg_trgm` for fuzzy matching, `unaccent` for diacritics, and when Elasticsearch is still the right tool.
Interview-ready guide
Interview answer
PostgreSQL has native full-text search built around two data types and one operator. tsvector is a processed document of lexemes with stop words removed and stems applied. tsquery is a search expression with boolean operators. The @@ operator matches them.
The practical pipeline
- Store the tsvector as a generated column so it is always in sync:
GENERATED ALWAYS AS (to_tsvector('english', body)) STORED - Create a GIN index on the tsvector column — that is what turns full-table scans into sub-second lookups
- Use
plainto_tsqueryorwebsearch_to_tsqueryfor user-facing input,to_tsquerywhen you need boolean operators explicitly - Rank with
ts_rank_cd(vector, query)and highlight withts_headline
Weighting matters for quality
setweightattaches one of four weights to different fields so title hits score higher than body hitsts_rank_cduses those weights plus cover density, rewarding documents where query terms are close together
For multilingual content
- Store the language per row and build the tsvector with that per-row config so each row uses its own stemmer
- Install the
unaccentextension to fold diacritics socafématchescafe
Fuzzy matching
- The
pg_trgmextension supports trigram similarity and acceleratesILIKE '%substring%'when combined with agin_trgm_opsindex - Combine it with FTS for typo tolerance that the stemmer cannot provide
When Elasticsearch is still right
- Datasets over ~100M documents, rich faceted search, learning-to-rank, or autocomplete at very high throughput
- For everything else, PostgreSQL FTS removes the cost of operating a second search system
PostgreSQL Full-Text Search — tsvector, tsquery, GIN, Ranking
Direct Answer
PostgreSQL ships native full-text search via tsvector (processed document) and tsquery (search expression), matched with the @@ operator and indexed with GIN. to_tsvector('english', body) tokenizes, strips stop words, and stems; a GIN index turns search into sub-second lookups on millions of rows. ts_rank orders by relevance; ts_headline returns highlighted snippets. For datasets under ~10M documents with straightforward keyword search, PostgreSQL FTS removes the operational cost of Elasticsearch.
Code Example
-- 1. Store the processed document as a generated column (auto-updated)
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
language REGCONFIG NOT NULL DEFAULT 'english',
search_vector TSVECTOR GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 2. GIN index — mandatory for fast search
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);
-- 3. Query variants
-- to_tsquery: full syntax with & | ! :* (prefix)
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & (jsonb | index:*)');
-- plainto_tsquery: ANDs the words for you (user-friendly)
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres jsonb index');
-- phraseto_tsquery: exact phrase (ordered adjacency)
SELECT id, title
FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');
-- websearch_to_tsquery: Google-style: quotes, OR, -exclude
SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english',
'"full text search" postgres OR mysql -elasticsearch');
-- 4. Relevance ranking
SELECT id, title,
ts_rank_cd(search_vector, query, 32) AS rank
FROM articles,
plainto_tsquery('english', 'postgres jsonb') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
-- 5. Highlighted snippet
SELECT id, title,
ts_headline('english', body,
plainto_tsquery('english', 'postgres jsonb'),
'MaxWords=30, MinWords=15, ShortWord=3') AS snippet
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres jsonb')
LIMIT 20;
-- 6. EXPLAIN: Bitmap Index Scan on idx_articles_fts
EXPLAIN ANALYZE
SELECT id FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres');
-- Bitmap Heap Scan on articles
-- Recheck Cond: (search_vector @@ ...)
-- -> Bitmap Index Scan on idx_articles_fts
-- Index Cond: (search_vector @@ ...)
-- 7. Spring Data JPA native query
-- public interface ArticleRepo extends JpaRepository<Article, Long> {
-- @Query(value =
-- "SELECT id, title, ts_rank_cd(search_vector, q) AS rank " +
-- "FROM articles, plainto_tsquery('english', :q) q " +
-- "WHERE search_vector @@ q " +
-- "ORDER BY rank DESC LIMIT :limit OFFSET :offset",
-- nativeQuery = true)
-- List<Object[]> search(@Param("q") String q,
-- @Param("limit") int limit,
-- @Param("offset") int offset);
-- }
Common Mistakes & Pitfalls
Using LIKE '%keyword%' for search, or calling to_tsvector in the query predicate without indexing it — both cause a full table scan because the planner cannot use any index for those expressions.
Check your understanding
Answer these without looking back at the article.
- What is the difference between `to_tsquery`, `plainto_tsquery`, `phraseto_tsquery`, and `websearch_to_tsquery`?
- How do you handle multilingual content in PostgreSQL full-text search?
- When should you add `pg_trgm` on top of full-text search?
- At what scale does Elasticsearch become a better choice than PostgreSQL FTS?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.