Postgres FTS vs Elasticsearch for product search

Earlier I wrote about adding Elasticsearch to a Rails app, and ended with a caution: check whether Postgres can do the job before you take on a second datastore. For a product catalog specifically, that question deserves a proper head-to-head, because search is central to a store — it is often the highest-converting path to a sale — and the choice between Postgres full-text search and Elasticsearch shapes both your relevance and your operations. Having shipped both, here is the honest comparison.

What Postgres full-text search actually gives you

Postgres FTS is more capable than people expect. It tokenises and stems text into a tsvector, matches it against a tsquery, ranks results, and indexes the whole thing with GIN for speed. Critically, it supports field weighting, which is exactly what product search needs — a match in the product name should outrank a match in the description:

-- a weighted search document: name is weight A, description weight B
ALTER TABLE products ADD COLUMN search_doc tsvector;
UPDATE products SET search_doc =
  setweight(to_tsvector('english', name), 'A') ||
  setweight(to_tsvector('english', coalesce(description,'')), 'B');
CREATE INDEX index_products_search ON products USING gin(search_doc);

-- ranked query
SELECT id, ts_rank(search_doc, query) AS rank
FROM products, to_tsquery('english', 'running & shoes') query
WHERE search_doc @@ query
ORDER BY rank DESC;

That is stemming, multi-field weighting, ranking, and indexed lookups — entirely inside the database you already run. Keep search_doc updated with a trigger or a model callback, and you have real search with zero extra infrastructure, transactional consistency, and no sync problem, because the search index lives in the same database as the data and updates in the same transaction.

For typo tolerance, pair it with pg_trgm (trigram similarity) so “manchestr” still finds “manchester” — covered in an earlier post. Between FTS for relevance and pg_trgm for fuzziness, Postgres handles a great deal of what a store needs.

Where Elasticsearch pulls ahead

So why does Elasticsearch dominate e-commerce search? Because at the demanding end, it does things Postgres FTS does not, and several of them are specifically what a large catalog wants:

• Faceted search / aggregations. “Show me boots, filtered by brand, colour, and price, with a live count next to each option.” Elasticsearch’s aggregations compute these facet counts efficiently as part of the search; doing the same in Postgres means a pile of separate aggregate queries that get slow and awkward. For a catalog with rich filtering, this alone often decides it.
• Relevance tuning depth. Function scoring, boosting by popularity or margin, decay functions (“prefer in-stock and recent”), synonym dictionaries (“trainers” = “sneakers”), per-language analyzers — Elasticsearch is a search engine with deep knobs for exactly this. Postgres FTS ranking is comparatively blunt.
• Typo tolerance and autocomplete built in. Fuzzy matching, edge-ngram autocomplete, and “search-as-you-type” are first-class; in Postgres you assemble them from pg_trgm and care.
• Scale and search-specific performance. For very large catalogs and high query volumes, a dedicated, horizontally-scalable search engine outperforms search bolted onto your primary database — and offloads that load from the database serving everything else.

The cost that decides it

The deciding factor is rarely a feature checkbox; it is operational cost versus need. Elasticsearch is a second datastore: another system to run, monitor, secure, and scale, and — the part that bites — to keep in sync with your database. As the earlier post laid out, that sync is the real ongoing work: indexing in background jobs, periodic full reindexes to repair drift, atomic alias swaps for rebuilds, handling bulk operations that bypass callbacks. It is not free, and it never stops costing.

So the decision framework we use:

• Start with Postgres FTS if search is important but not the centrepiece — a modest catalog, name-and-description matching, basic ranking, light filtering. You get real search with no new infrastructure and no sync problem. For a great many stores this is genuinely enough, and “no second datastore” is worth a lot.
• Move to Elasticsearch when search becomes a primary feature and you hit Postgres’s ceiling: you need rich faceted filtering with live counts, serious relevance tuning, synonyms, sophisticated autocomplete, or you are at a scale where dedicated search performance matters. At that point Elasticsearch earns its operational weight, because search is the product surface and its quality drives revenue.

The mistake in both directions is real. Reaching for Elasticsearch on day one for a small catalog doubles your operational burden for facets you do not need yet. Clinging to Postgres FTS when the business genuinely needs faceted, finely-tuned search produces a frustrating store and lost sales. The skill is matching the tool to where search sits in the product’s value.

A pragmatic middle path

In practice you can also grow into Elasticsearch. Build first on Postgres FTS, keep your search behind a clean interface (a ProductSearch object the rest of the app calls), and when you outgrow it, swap the implementation behind that interface for Elasticsearch without touching the callers. Designing the seam up front means the migration is a contained piece of work rather than a rewrite — the same “isolate the boundary” instinct that pays off everywhere.

Verdict

For product search, Postgres full-text search is far more viable than its reputation suggests: weighted, stemmed, ranked, indexed search with no extra infrastructure and no sync problem, and pg_trgm for fuzziness. Elasticsearch is genuinely better at the demanding end — faceted aggregations, deep relevance tuning, synonyms, autocomplete, and scale — but you pay for it with a second datastore you must run and forever keep in sync. Decide by where search sits in your product: default to Postgres while search is a feature, graduate to Elasticsearch when search becomes the feature, and hide the choice behind an interface so growing from one to the other is a swap, not a rebuild.