PostgreSQL Full Text Search Explained: Advanced Queries Made Simple


Full text search is an essential feature for any app dealing with rich textual content. PostgreSQL’s native Full Text Search (FTS) gives you tools to perform fast and accurate searches across multiple fields—with language-aware matching and relevance ranking.
Key Concepts
tsvector
stores normalized searchable datatsquery
represents a structured query@@
checks if vector matches queryts_rank()
gives relevance scores
Setup Steps
Add a generated column:
ALTER TABLE products ADD COLUMN tsv tsvector GENERATED ALWAYS AS (
to_tsvector('english', name || ' ' || description)
) STORED;
Create an index:
CREATE INDEX tsv_idx ON products USING GIN(tsv);
Run your search:
SELECT * FROM products
WHERE tsv @@ to_tsquery('english', 'casual & wear');
Phrase & Proximity Matching
-- Match words next to each other
SELECT * FROM products
WHERE tsv @@ phraseto_tsquery('english', 'perfect fit');
Ranking
SELECT id, name, ts_rank(tsv, to_tsquery('english', 'wear')) AS score
FROM products
ORDER BY score DESC;
Bonus Functions
ts_headline()
– highlights matchessetweight()
– gives priority to title/descriptionts_debug()
– inspect how parsing works
FAQ
PostgreSQL FTS vs. Elasticsearch?
PostgreSQL is great for built-in search features; Elasticsearch is better for large-scale, standalone search systems.
Is it fast?
Yes, especially with tsvector
columns and GIN indexes.
Does it support multiple languages?
Yes, including custom dictionaries for accurate stemming.
How is ranking handled?
Using ts_rank()
and setweight()
to prioritize certain fields.
Conclusion
PostgreSQL Full Text Search is powerful, fast, and fully integrated. For most apps, it offers everything you need for contextual search—without adding complexity.
Read the PostgreSQL Full Text Search: The Definitive Guide for more details.
Subscribe to my newsletter
Read articles from DbVisualizer directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by

DbVisualizer
DbVisualizer
DbVisualizer is the database client with the highest user satisfaction. It is used for development, analytics, maintenance, and more, by database professionals all over the world. It connects to all popular databases and runs on Win, macOS & Linux.