Use Postgres full-text search before you reach for a search cluster

The claim

Below roughly a million searchable rows, PostgreSQL's built-in full-text search is not a stopgap. It is the correct answer, and adding a separate search cluster at that scale buys you a synchronisation problem, a second operational surface, and a monthly bill in exchange for relevance improvements your users will not notice.

What you actually get in the box

Postgres ships stemming, stop words, ranking, prefix matching, phrase search, and multi-language dictionaries. Here is a working implementation for a products table, generated column and all:

ALTER TABLE products ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(sku, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'C')
  ) STORED;

CREATE INDEX products_search_idx ON products USING GIN (search);

The generated column means there is no trigger to maintain and no possibility of the index drifting out of sync with the row — the two failure modes that make hand-rolled search painful. Query it:

SELECT id, name, ts_rank(search, q) AS rank
FROM products, websearch_to_tsquery('english', $1) q
WHERE search @@ q
ORDER BY rank DESC, id
LIMIT 20;

websearch_to_tsquery is the function to use for anything a human types. It accepts quoted phrases, or, and a leading minus for exclusion, and it does not throw a syntax error on unbalanced input the way to_tsquery does. Passing raw user input to to_tsquery is the single most common cause of 500 errors on search pages.

The number that matters

On a 4 GB instance with a GIN index over 300,000 product rows, a two-term query of this shape typically returns in single-digit milliseconds once the index is in cache. Check yours rather than trusting that sentence:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

You are looking for a Bitmap Index Scan on the GIN index rather than a Seq Scan, and for shared read counts near zero on a warm cache. If you see a Seq Scan, the planner has decided your table is small enough not to bother, which is also fine.

Handling typos without a cluster

The usual reason people leave Postgres is fuzzy matching. That is available too:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT name FROM products
WHERE name % $1
ORDER BY similarity(name, $1) DESC
LIMIT 5;

The pragmatic pattern is a two-stage search: run the tsvector query first, and if it returns fewer than three rows, fall back to the trigram query and label the results as suggestions. This handles "chansaw" and "vacum" without any additional infrastructure.

Where Postgres genuinely runs out

Be honest about the ceiling. Move to a dedicated engine when you hit one of these, not before:

• Faceted counts over millions of rows. Counting matching documents per category per query is exactly what inverted-index engines are optimised for and exactly what makes Postgres do extra work.
• Relevance tuning as a product feature. If someone's job includes adjusting boost factors weekly, they need tooling Postgres does not provide.
• Search traffic competing with transactional traffic. When search queries start affecting checkout latency on the same instance, the isolation is worth the operational cost. A read replica is the cheaper first move.
• Multi-language collections in one index with per-document language detection. Possible in Postgres, unpleasant.

The synchronisation tax

The cost people forget is not the cluster's monthly bill, which is often modest. It is that you now have two sources of truth. Every write path must update both, every deploy must consider index mappings, and every incident review includes the question of whether search was stale. Teams routinely spend more engineering hours on the sync pipeline in the first quarter than the search quality improvement was worth over the following year.

A defensible sequence

1. Ship the generated tsvector column and a GIN index. An afternoon.
2. Log every query string and its result count. A week of that data tells you what is actually failing.
3. Add trigram fallback for the zero-result queries.
4. Add weighting and a business-rule boost for in-stock items.
5. Only if the logs still show a meaningful failure rate, evaluate a dedicated engine — with a specific number to beat.

Most teams never reach step five, and the ones that do arrive with evidence rather than a hunch.