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 data

• tsquery represents a structured query

• @@ checks if vector matches query

• ts_rank() gives relevance scores

Setup Steps

1. Add a generated column:

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

2. Create an index:

CREATE INDEX tsv_idx ON products USING GIN(tsv);

3. 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 matches

• setweight() – gives priority to title/description

• ts_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.