A Developer's Guide to PostgreSQL Full Text Search

Looking to move beyond simple LIKE queries in SQL? PostgreSQL offers native full text search, a powerful feature for matching and ranking text intelligently. It supports stemming, stop words, ranking, and multiple languages—all within your database.

This guide walks you through how it works, how to implement it, and how to make it fast and accurate using tsvector, tsquery, and GIN indexes.

Basic Flow

1. Text → tsvector
2. Query → tsquery
3. Use @@ to match
4. Add GIN index for speed
5. Use ts_rank() to sort by relevance

Setup

ALTER TABLE products ADD COLUMN tsv tsvector GENERATED ALWAYS AS (
  to_tsvector('english', name || ' ' || description)
) STORED;

CREATE INDEX tsv_idx ON products USING GIN(tsv);

Search

SELECT * FROM products
WHERE tsv @@ to_tsquery('english', 'sneaker & casual');

Phrase queries:

SELECT * FROM products
WHERE tsv @@ phraseto_tsquery('english', 'everyday wear');

Rank by relevance:

SELECT name, ts_rank(tsv, to_tsquery('english', 'wear')) AS score
FROM products;

Pro Tips

• setweight() to boost titles
• ts_headline() to highlight matches
• Combine tsvectors from multiple fields

FAQ

Can I search across multiple columns?

Yes, combine them using || or setweight() into one tsvector.

How do I match full phrases?

Use phraseto_tsquery() for phrase-based searches.

How do I rank results?

Use ts_rank() and assign weights to important fields.

Is it better than LIKE?

Yes — FTS is more powerful, accurate, and performance-optimized.

Conclusion

PostgreSQL Full Text Search is the easiest way to build a search engine directly in SQL. Fast, powerful, and production-ready.

Read the PostgreSQL Full Text Search: The Definitive Guide for more details.