Full-Text Search in PostgreSQL

PostgreSQL has genuine full-text search built in — no external search engine required for many use cases. It understands word stemming (running matches run), ignores common "stop words," and can rank results by relevance.

The Basic Match Operator: @@

SELECT * FROM articles
WHERE to_tsvector(body) @@ to_tsquery('database');

• to_tsvector(text) converts a text column into a searchable, stemmed representation.
• to_tsquery(text) parses a search term into a query the @@ operator can match against.

Combining Search Terms

SELECT * FROM articles
WHERE to_tsvector(body) @@ to_tsquery('postgres & performance');
-- must contain BOTH "postgres" AND "performance" (in some stemmed form)

SELECT * FROM articles
WHERE to_tsvector(body) @@ to_tsquery('postgres | mysql');
-- contains EITHER term

SELECT * FROM articles
WHERE to_tsvector(body) @@ to_tsquery('database & !nosql');
-- contains "database" but NOT "nosql"

A Friendlier Query Parser: plainto_tsquery

Handles a plain search phrase without you needing to know the &/|/! syntax:

SELECT * FROM articles
WHERE to_tsvector(body) @@ plainto_tsquery('database performance tips');

Ranking Results by Relevance

SELECT title, ts_rank(to_tsvector(body), to_tsquery('postgres')) AS rank
FROM articles
WHERE to_tsvector(body) @@ to_tsquery('postgres')
ORDER BY rank DESC;

Adding a Generated Search Column + GIN Index

Computing to_tsvector() on every row for every search is wasteful — store it in a dedicated column instead, kept automatically in sync:

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

CREATE INDEX idx_search ON articles USING GIN (search_vector);

SELECT * FROM articles WHERE search_vector @@ to_tsquery('database');

Common Errors & Troubleshooting

• ERROR: syntax error in tsquery: "postgres database" — to_tsquery() expects &/|/! between terms, not plain spaces; use plainto_tsquery() instead for a natural, space-separated search phrase.
• A search that should match returning nothing — check that both the stored text and the search term go through to_tsvector()/to_tsquery() with the same language configuration ('english' by default) — a mismatch changes how stemming works.
• Full-text search working but painfully slow — this almost always means there's no GIN index on the searched column; computing to_tsvector() on the fly for every row, every query, doesn't scale — add the generated column + GIN index shown above.

Key Takeaways

• to_tsvector() prepares text for searching; to_tsquery()/plainto_tsquery() prepares the search term — @@ matches the two.
• Full-text search understands stemming, so a search for "run" also matches "running" or "ran".
• For any production use, store the tsvector in a generated column and index it with GIN — computing it on the fly for every query doesn't scale.