Full-Text Search (tsvector/tsquery)
advancedpostgresql
2 min read
What is Full-Text Search?
PostgreSQL's full-text search (FTS) lets you search text columns for words and phrases in a linguistically aware way. It uses tsvector (document representation) and tsquery (search query) types.
Unlike LIKE or ILIKE, FTS understands stemming (run/running/ran), stop words, and relevance ranking.
Syntax
-- Convert text to tsvector
SELECT to_tsvector('english', 'The quick brown fox');
-- Create a search query
SELECT to_tsquery('english', 'quick & fox');
-- Search
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & optimization');When to Use
- Building search features for text content
- When LIKE/ILIKE is too slow or imprecise
- Searching across multiple text columns
- When you need relevance ranking
Key Points
- tsvector — A sorted list of lexemes (normalized words) from the document.
- tsquery — A Boolean search expression with & (AND), | (OR), ! (NOT).
- @@ Operator — Matches a tsvector against a tsquery.
- GIN Index — Create a GIN index on tsvector columns for fast searches.
- ts_rank — Rank results by relevance: ts_rank(tsvector, tsquery).
- Language Config — 'english', 'simple', 'spanish', etc. control stemming and stop words.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Full-Text Search (tsvector/tsquery) Challenge
Write a query that find courses whose name matches a search term.
Expected result
Courses with 'database' (or its stems) in the name.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is Full-Text Search?
Initializing database...Each run starts from fresh sample data.
More Examples
Full-text search with ranking
Search and rank results by relevance.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
How do I make full-text search fast?
Create a GIN index: CREATE INDEX idx_fts ON courses USING gin(to_tsvector('english', name)); Or add a stored tsvector column.
Can I search across multiple columns?
Yes. Concatenate columns: to_tsvector('english', name || ' ' || description) or use setweight() for different column priorities.