Your product search does not need Elasticsearch

I added multi-word product search to a marketplace this week. The new infrastructure it required: one column.

Not a container. Not a sync pipeline. A generated column and an index, in the migration that already existed. Search queries now go to the same database that owns the products, inside the same transaction domain, and a product is findable the instant its edit commits.

I want to lay out what that actually buys you, what it costs you, and — the part I think gets skipped — why the consistency story is the strongest argument, not the resource savings everyone leads with.

The default answer, and what it really costs

Ask how to add product search and the answer arrives before the question finishes: Elasticsearch. Or Meilisearch, or Typesense, if the person is being budget-conscious. All three are good at what they do. All three also mean the same three things:

Another always-on process. My whole stack has a hard ceiling: it has to cold-start on a 6 GB VPS, because that is what someone running this will actually rent. Six JVMs at -Xmx256m, Postgres, Redis, Kafka and tracing already claim most of it. A search engine's few hundred megabytes is not a rounding error at that scale — it is the difference between "runs on the box you have" and "buy a bigger box."

Another sync pipeline. Products live in Postgres. The engine needs its own copy. Something has to move rows across: a change-data-capture stream, an outbox consumer, a cron reindex, application-level dual writes. Whatever you pick, you have written a distributed system whose only job is to make two stores agree.

Another way to be wrong. That pipeline can lag, die quietly, or apply events out of order. Now "the product page shows the new price but search shows the old one" is a bug class you own forever. Ask anyone who has run a search cluster in production what fraction of incidents were the engine itself versus the thing feeding it.

None of that is an argument against search engines. It is an argument for knowing what you are buying before you buy it — and for checking whether the database you already run can do the job.

What Postgres gives you already

Three pieces, all built in.

A generated tsvector column, maintained by the database rather than by your code:

CREATE TABLE product (
    id          BIGSERIAL PRIMARY KEY,
    slug        VARCHAR(160) NOT NULL UNIQUE,
    name        VARCHAR(240) NOT NULL,
    description TEXT,
    -- ...
    search_vec  tsvector GENERATED ALWAYS AS (
                  to_tsvector('english', name || ' ' || coalesce(description, ''))) STORED
);
CREATE INDEX idx_product_search ON product USING GIN (search_vec);

GENERATED ALWAYS AS ... STORED is the important half. There is no trigger to write, no UPDATE ... SET search_vec = ... to remember in every write path, no chance of an application forgetting. Insert or update a row and the search vector is already correct before the statement returns. It is not eventually consistent with the row; it is part of the row.

The GIN index makes the lookup fast. And then the query:

SELECT p.id, p.slug, p.name, p.price_cents
  FROM product p
 WHERE p.status = 'ACTIVE'
   AND p.search_vec @@ websearch_to_tsquery('english', ?)

websearch_to_tsquery is the function to know. Its predecessor, to_tsquery, demands real tsquery syntax — wireless & noise & cancelling — which means you get to write a parser that turns human input into operators and escapes everything that would otherwise be a syntax error. websearch_to_tsquery accepts what a search box actually receives: wireless noise cancelling, quoted phrases, -excluded terms. Bad input yields no results, not an exception.

So a shopper types three words into a form, the string goes to the API as a parameter, and the parameter reaches websearch_to_tsquery. There is no sanitising step in the middle, and no injection surface, because it never stops being a bound parameter.

English stemming comes along for free: "cancelling" matches "cancel", "headphones" matches "headphone". The analyzer is a per-column choice, so a second language is a second expression, not a second system.

What you give up

Being honest about this matters more than the pitch, because the failure mode of "just use Postgres" advice is someone adopting it for a problem it does not fit.

No typo tolerance. "hedphones" returns nothing. Postgres has pg_trgm for fuzzy matching and you can bolt similarity search on as a fallback, but it is not the same thing as a real engine's fuzziness, and combining the two into one ranked result set gets fiddly quickly. If "did you mean" is a product requirement, this is the wrong tool.

No faceting. Counts per category, per brand, per price bucket, all computed alongside the result set — engines do this natively. In SQL each facet is another aggregate over the filtered set, which is fine for a handful of dimensions and unpleasant beyond that.

Relevance tuning stops at ts_rank. You can weight fields and boost by recency. You cannot ship the kind of tuned scoring, synonym dictionaries and per-market rules that a mature search team lives in.

Scale has a ceiling. For a catalog in the thousands, GIN lookups are not the slow part of your page. In the hundreds of thousands, with heavy filtering and real relevance needs, you will feel it.

My catalog is small, the requirement is "find products by words in their name or description", and every one of those limits is one I can state out loud without wincing. That is the actual test.

The part nobody mentions: consistency

Here is the argument I find most convincing, and it is not about memory.

With a search engine, your search index is a second copy of your data with its own clock. The write path becomes: commit to Postgres, then somehow get it to the engine. Even done well — outbox table, event stream, idempotent consumer — there is a window where the two disagree. You do not get to eliminate the window; you get to make it small and observable.

With a generated column, there is no second copy and no window. The vector is computed by the same statement that writes the row, under the same transaction. Roll the transaction back and the search state rolls back with it. Restore from a backup and the index comes back consistent, because it is the data. There is no reindex job, no "search is behind again" dashboard, no bootstrap procedure for a new environment.

That property is worth real money in operational calm, and it is invisible in a feature comparison table. Feature tables compare fuzziness and faceting. They do not have a row for "cannot drift."

Where this leaves you

I wrote the decision down as an ADR, and the part I care most about is the last section: what would make me change my mind. Typo-tolerant search as a stated product requirement. Faceted navigation. A catalog two orders of magnitude larger with real relevance work. Multi-language analyzers beyond what per-column configuration handles.

Notice that none of those are "we grew." They are all "the requirement changed." And when one does, the blast radius is small on purpose: search is behind one endpoint, GET /catalog/products?q=. Swapping the executor behind it does not touch the storefront, the mobile client, or the admin console. The contract does not know what runs underneath — which is the same reason it was cheap to start here.

The pattern generalises past search. Every "you'll need X for this" reflex is worth one question: what does the thing I already run do here, and what exactly would I be buying? Sometimes the answer is that you genuinely need X. Often the answer is a column.

---

This is part of a series on building a multi-vendor commerce platform — Spring Boot services, an event-driven consistency story, and a hard rule that the whole thing cold-starts on a 6 GB box. The shared infrastructure lives in stallora-cloud-starter under Apache-2.0. Next up: splitting one shopper's order across several vendors without a distributed transaction coordinator.

Comments

[Mads Hansen]

The transaction-domain argument is the most convincing one here. One operational step I’d add is to make the future move to a dedicated engine a measured threshold rather than a gut feeling. Keep a small labeled query set and track Recall@K/MRR, zero-result rate, p95 latency, and EXPLAIN (ANALYZE, BUFFERS) under the real status/category filters. The GIN lookup is often fine while the candidate bitmap heap scan and facet aggregates become the actual bottleneck. For the current design, a weighted generated vector plus a partial GIN index for active products can buy a lot; pg_trgm can remain a tightly bounded fallback only when FTS returns zero results. Then the migration trigger is explicit: typo/relevance quality, facet cost, or OLTP impact crossed a budget—not simply catalog size.

[Qihu Zhang]

Thanks for taking the time to write this out. A few of these are things I should have done already.

The zero-result trigger is a better idea than what I wrote. I skipped pg_trgm because I didn't want to merge a similarity score with a ts_rank score, but if it only runs when FTS came back empty, there's nothing to merge. And zero-result rate is already the number I'd be watching, so it doubles as the trigger. I'll take that one.

You're also right that my "when would I switch" list has no numbers in it. It names conditions, like typo tolerance becoming a real requirement, but nothing is measured, so in practice it comes down to whoever gets annoyed first. A small labeled query set is cheap. I don't have a good excuse for not having one.

On the bottleneck, something I only noticed while going back through the query: for me it's count(*) before it's the heap scan. Each page request runs the same filter twice, once for the rows and once for the total, and only the rows part can stop at twenty. Facets would sit on that same set, so I think your ordering holds. It just starts one step earlier than I assumed.

Both index points are gaps. The vector concatenates name and description at equal weight, so a word buried in a long description counts the same as one in the title. Worse, I'm not ranking at all yet, ORDER BY is still created_at DESC. setweight and a partial GIN on active rows both look close to free, so those go on the list.