Case: Search Products by Name
"Search products by name" is a SQL query until a requirement says otherwise. The case runs the why ladder for Elasticsearch honestly: the first version is a filtered query, the second is a database full-text index, and a dedicated search engine is justified by requirements the store may or may not ever have.
The situation, the reflex, and why it stalls
Every lesson starts where being stuck starts: someone has a problem, and the first move that comes to mind feels like progress.
The store needs "search products by name", and the first suggestion is a search engine. What is the sequence of solutions from a WHERE clause upward, and which requirement moves you from one to the next?
The founder typed a product name into the store and nothing happened, because there is no search box. "We need search" is now the top item, and the first answer in the room was Elasticsearch. You know the catalog is a few thousand rows in one table and you suspect the answer is smaller than that — but you also know that "search" grows into typo tolerance, ranking and facets, and you do not want to rebuild it three times.
Set up the search engine. Search is a known hard problem with a known solution, running one is a well-documented afternoon, and the index will handle typos, ranking and facets when they arrive. It feels like solving the problem once instead of three times.
The search engine is a second copy of the catalog that has to be kept in sync with the first, and the sync is now a feature: a product edited in the admin shows the old name in search until the index catches up, and nobody wrote the requirement that allowed that.
- The search engine is a second copy of the catalog that has to be kept in sync with the first, and the sync is now a feature: a product edited in the admin shows the old name in search until the index catches up, and nobody wrote the requirement that allowed that.
- It solves typo tolerance, ranking and facets — none of which has been asked for. The requirement is "find the product whose name I typed", and a filtered query answers it in one line.
- The engine is a new operational surface — a process to run, memory to size, an index to rebuild after a schema change — for a catalog that fits in the database's cache.
- The three rebuilds happen anyway, because the requirements that arrive — search by category, then by attributes, then across descriptions — each need different index configuration, and the engine did not make them free; it moved them.
The move
Precisely enough to apply it to a problem you have never seen — not a slogan.
- Take the requirement at its word: "search products by name" is a query with a predicate on one column. Write it, index the column, ship it, and measure. That is the first version, and for many stores it is the last (The Simplest Thing That Could Work).
- List the requirements that would make the first version wrong, each with the observation that would show it: partial matches on any word; typo tolerance; ranking by relevance; searching descriptions; facets; result latency under load. Each is a real requirement that may arrive, and each has a cheaper next step than a search engine (What Can I Ignore for Now?).
- Climb the ladder one rung at a time as requirements arrive: prefix or contains match → a trigram or full-text index inside the database → a dedicated engine. Each rung is justified by a named requirement the previous rung failed, observed rather than predicted (Add Complexity Only When Required).
- Run the why ladder for the engine honestly, including the case where it is right: multi-field relevance ranking, facets over large catalogs, typo tolerance at scale, and search load that the primary database should not carry. Those are real, and a store that reaches them should have an engine (The Why Ladder).
The same requirement, asked three ways
"We need search" is the vague form, and it is why the first answer in the room was a product name. The best form names the observation that decides the next rung, which is what makes the ladder climbable on evidence instead of anticipation.
why The best form makes "do we need an engine?" answerable from the log rather than the meeting, makes "does the database's full-text index meet it?" a prototype with a pass condition, and tells V1 to record queries and result counts from its first day.
"We need Elasticsearch"
The ladder as it was run, with the rung where the honest answer was "the database already does this" — and the rung it would have climbed to if the readings had been different. The device's last field is the point: the engine is right for real requirements, and the store should be able to name them.
“We need Elasticsearch for product search.”
- ↓Why a search engine? Because customers misspell product names and get no results.
- ↓Why does that need a new system? Because a contains match cannot tolerate typos, and the full-text index we added only handles word order and stemming.
- ↓What does the database we already run offer? Trigram similarity indexes, which match misspellings; a prototype against the real catalog handled the test set with acceptable latency.
- ↓What would the engine add that the prototype did not? Relevance ranking across several fields, facets, and moving search load off the primary — none of which the readings currently ask for.
the claim was right when Search must rank by relevance across name, description, category and attributes; users need facets over a large catalog; or search query load is measured as a burden the primary database should not carry. Then the engine is the simpler thing, and the sync design is written before it is installed (Search Is a Different Contract Than Filtering in API design covers the interface).
The rungs, side by side
The matrix scores the three rungs on the axes an engineering decision actually moves. The numbers are deliberately coarse; the caveat says what they cannot express, which is the requirement that decides between them.
| Option | Simplicity | Performance | Reliability | Cost | Maintainability | Note |
|---|---|---|---|---|---|---|
| Filtered query on name | One line; no sync; fails on word order, typos and relevance. | |||||
| Database full-text or trigram index | A migration and a query; no sync; index cost on writes; limited relevance tuning. | |||||
| Dedicated search engine | Relevance, facets, fuzzy matching at scale; a second copy of the catalog, a sync, and an operational surface. |
caveat The scores say nothing about which requirement you have, and that is the only thing that decides. An engine scored 2 on simplicity is the right rung when facets and relevance are requirements, and the filtered query scored 5 is the wrong rung the moment they are.
How to do it
Most important first.
- V1: a search box that runs a case-insensitive contains query on product name, with a plain index if the plan needs it. Write down what it cannot do.
- Instrument search from the first version: the query typed, the result count, whether the user clicked a result. Zero-result queries are the requirement discovery mechanism for search.
- When "no results for a misspelling" appears in the log often enough to matter, read what the database itself offers — full-text search, trigram similarity — and prototype it against the real catalog before adding a system (JSONB, Full-Text Search and Extensions in Database covers the built-in option).
- When the requirement is relevance across several fields, facets, or search load the primary cannot carry, then evaluate an engine — and write the sync design first, because the sync is where the engine's failure modes live (Keeping a Search Index in Sync in Backend).
- Keep a ledger row for the engine with its trigger, so the decision is made on a reading rather than in a meeting (The Complexity Ledger).
Worked on a concrete problem
The move has to produce something. This is what it produced.
- V1, shipped in an hour. The search box runs a contains match on name, ordered by name. Founder types the product name, the product appears. The log records the query and the result count. What it cannot do is written down: word-order-insensitive matching, typos, relevance, descriptions.
- The first requirement the log surfaced. Zero-result queries were mostly two-word searches in the wrong order and plural forms. The contains match failed them; a database full-text index with stemming matched them, and it was a migration plus one query change — no new process. The prototype ran against the real catalog and the zero-result rate in the test set dropped to a handful, all of them genuine misspellings.
- The why ladder for Elasticsearch, run when misspellings were the next complaint. Claim: we need a search engine. Why? Typo tolerance. Why not the database? The full-text index does not do fuzzy matching. What did the prototype say? A trigram similarity index in the same database matched the misspellings in the test set with acceptable latency on the catalog's size. Real requirement: find the product despite a typo. Simpler: the trigram index. Justified when: relevance ranking across name, description and category with facets over a large catalog, or search load the primary should not carry — neither of which the readings showed.
- The case where the engine was right — the marketplace, later. Thousands of sellers, millions of listings, search by any attribute with facets and relevance, and a search load that dominated the primary database. The ladder ended at the engine that time, and the sync design — what happens when a listing changes and the index has not caught up — was written before the engine was installed, because the ladder had made the failure mode visible (External Systems Fail).
How you know it worked
What now exists that did not before, and what question you can now ask.
- Search works today with a query you can read, and the list of what it cannot do is written down next to it.
- Every step up the ladder was triggered by an observation in the search log, not by a prediction in a meeting.
- You can state the requirement that would justify a dedicated engine here, and you can say whether the readings show it.
- If the engine is ever added, its sync design exists before it does.
The questions you can now ask
The field this whole domain exists for. After this lesson, these are the questions to put to an unfamiliar problem.
- ?What is the WHERE-clause version of this requirement, and what exactly can it not do?
- ?Which observation in the log would show that the current rung has failed?
- ?What does the database I already run offer before I add a system I do not?
- ?If I add the engine, what is the sync design, and what does a user see between an edit and the index catching up?
What can go wrong
- V1 ships without logging, so the requirements that would have justified the next rung are never observed, and search stays bad in a way nobody can see. Measure-first needs a measurement.
- The ladder is climbed in anticipation — "we will need typo tolerance eventually" — and the store pays for the engine's sync problems years before the typo requirement appears.
- The database's own search features are dismissed because "real search needs a real engine". They handle a surprising share of catalogs, and they have no sync problem.
- The engine is refused when its requirements are actually present. The marketplace case exists to say that the ladder has a top rung and some systems reach it.
- Climbing one rung at a time means rewriting the search query two or three times. Each rewrite is small, but each is a change to a feature users are already using.
- Database full-text and trigram indexes cost write time and index size on the catalog table, and they scale with the catalog; the engine moves that cost to a separate system at the price of the sync.
- A store that waits for the reading before adding an engine will have a period of worse search than one that added it early. The bet is that the period is short and the operational cost of the early engine is long.
- "Search engines are over-engineering." They are the right rung for a specific set of requirements — relevance across fields, facets, scale — and the case includes the system that reached them. What is over-engineering is the rung without the requirement.
- "The database can do everything a search engine does." It cannot; relevance tuning, facets and fuzzy matching at large scale are what the engine is for. The database does enough for many catalogs, which is a different claim.
- "Once we add the engine the search problem is solved." It moves: the sync, the index rebuild, the stale result after an edit, and the second copy of the catalog are now the search problem.
Where this applies
Problem-solving advice is stated as universal far more often than it is. These labels say what each method is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view.
- GENERALThe rung-by-rung climb applies to any capability with a well-known dedicated system — search, caching, queuing, analytics; the rungs differ, the discipline of naming the requirement that fails each one does not.
- SCALE-SPECIFICFor a catalog of a few thousand products the database rungs carry the store a long way; for a marketplace with millions of listings and faceted search from the outset, the engine is the first rung because the readings are predictable. The case runs both to show where it flips.
- CONTESTEDA strong opposing view: search quality compounds — relevance, synonyms and facets are what make a store's search usable, users abandon bad search silently, and a database contains-match "shipped in an hour" trains customers that search does not work. On that view an engine with sensible defaults is the correct first rung for any customer-facing store, and the sync cost is a known, bounded price for search that actually finds things.
- ILLUSTRATIVEThe catalog size, the zero-result findings, the trigram prototype's outcome and the marketplace's scale are invented for the shape of the argument; no latency figures or hit rates are being reported.
Where the depth lives
This domain asks the question and hands the answer off by name.
- — The technology decision tool at /thinking/decide asks the same rung questions for a search engine, a cache and a queue; each leaf names the simpler thing and how the recommendation fails.