Postgres Full-Text Search Before You Reach for Elasticsearch
DatabaseBackendPerformance

Postgres Full-Text Search Before You Reach for Elasticsearch

When a product needs search, teams often jump straight to Elasticsearch or a hosted search service. That means another system to run, sync, and pay for. For many apps, PostgreSQL full-text search is fast and relevant enough, and it lives right next to your data.

Why Not Just Use LIKE?

WHERE title ILIKE '%invoice%' works for a prototype, but it cannot use a regular index, it ignores word forms ("invoice" vs "invoices"), and it has no idea which results are most relevant. Full-text search fixes all three.

The Building Blocks

  • tsvector - a document turned into normalized search terms (lexemes)
  • tsquery - the search query in the same normalized form
  • @@ - the match operator between them
SELECT to_tsvector('english', 'Sending invoices to customers');
-- 'custom':4 'invoic':2 'send':1

SELECT to_tsvector('english', 'Sending invoices to customers')
       @@ websearch_to_tsquery('english', 'invoice customer');
-- true

websearch_to_tsquery understands the syntax users already know: quoted phrases, or, and -excluded words.

Store and Index the Search Vector

Computing the vector on every query is slow. Use a generated column and a GIN index:

ALTER TABLE articles
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search);

The weights make a match in the title count more than a match in the body. The column updates itself whenever the row changes, so there is nothing to keep in sync.

Rank the Results

SELECT id, title,
       ts_rank(search, query) AS rank
FROM articles,
     websearch_to_tsquery('english', 'background jobs') AS query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

Add ts_headline to show highlighted snippets in the results list. It is relatively expensive, so run it only on the rows you display.

Handle Typos and Partial Words

Full-text search matches whole words. For autocomplete and typo tolerance, combine it with the pg_trgm extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm_idx ON articles USING GIN (title gin_trgm_ops);

SELECT title FROM articles
WHERE title % 'kubernets'
ORDER BY similarity(title, 'kubernets') DESC
LIMIT 5;

Using It From Your App

In Rails, the pg_search gem wraps all of this. In Node, Prisma and Drizzle both let you drop into raw SQL for the search query. Keep the SQL in one place (a scope or a repository function) so ranking rules stay consistent.

When to Move to a Search Engine

Postgres search starts to strain when you need:

  • Search across many millions of documents with heavy concurrent query load
  • Advanced relevance tuning, synonyms, and per-language analyzers at scale
  • Faceted filtering with counts on large result sets
  • Vector and hybrid search across multiple data sources

Even then, starting in Postgres is not wasted work: you will know exactly which features you need from the next tool.

Conclusion

For most products, a generated tsvector column, a GIN index, and sensible ranking deliver good search in an afternoon, with no extra infrastructure.

Need help with search or database performance? Contact us.