The estimate
This came out of the same platform as the product visualisation work, but it is a different kind of problem: no geometry, no rendering, just a data shape that would not fit the tool everyone assumed it would.
A large prospect wanted product options constrained to a whitelist of valid combinations: anything not on the list should never be offered to the end customer. In the scoping meeting they put the list in the millions. We had run SQL tables that size before and, indexed correctly, they were quick. I said it was possible, and I meant it.
Months later, with the prospect now a client and implementation underway, the real figure arrived. Billions. Same requirement, three orders of magnitude out, and no room left in the budget or the delivery date to start again.
What the data actually was
The first useful thing I learned was that there were never billions of rows. The file was about 100,000 records, each with roughly 30 columns, one per product option: colour, stock, finish, quantity and so on. Each cell held a comma-separated list of the values permitted for that option.
So a row is not a combination, it is a compressed set of them. Expand thirty columns of value lists against each other and a single row can stand for thousands of valid combinations, and across a hundred thousand rows you are describing billions.
That is also why nobody caught it earlier. Everyone in the room was saying "entries" and quietly meaning different things. The client meant combinations, I heard rows, and the two differ by a factor no one says out loud.
Why SQL could not answer the question
The question the interface asks is narrow: given what the customer has selected so far, which values are still available? Against this shape, a relational database has nothing useful to offer.
Every column is a delimited string, so answering means a
FIND_IN_SET or a LIKE across thirty columns on
every request. An index on a column does not apply to substrings inside
it, so none of them help. That leaves two options: scan a hundred thousand
rows on every keystroke, or normalise the data into a proper relational
model, at which point you genuinely do have billions of rows and the
original estimate becomes true in the worst possible way.
The search model
Splitting each cell into an array of keywords, one field per option, turns
the question into the kind of thing a search engine is built for: find
documents whose colour array contains the chosen value and whose
stock array contains the other chosen value, then return the union
of what remains across the fields still unset.
Structured that way, queries came back in single-digit milliseconds, and it shipped on time.
The query type was wrong, and I left it wrong
We used match. It should have been term inside
a filter context.
The reason is archaeology. The first version indexed the raw CSV string
as text, so match and its analyzer did the tokenising for
us. Once the values were split into proper keyword arrays that analysis
was redundant. What I wanted was exact matching with no scoring, which
is what a filtered term query gives you, and Elasticsearch
caches filter results where it does not cache scored ones.
It was fast enough that we never went back, which is roughly what "fast enough" tends to cost you.
The indexing pipeline
I split it into two stages on purpose. It is Laravel throughout, talking
to the cluster through the official elasticsearch-php client.
The client uploads a CSV or posts to an import endpoint, and the raw rows land in a SQL text table untouched. That table is the source of truth for indexing, not the uploaded file, which means a failed index run never costs you the input and a reindex never needs the client to send anything again.
The indexer then reads from SQL, splits the comma-separated cells into keyword arrays, and writes documents into a temporary index. Production keeps serving the existing index the entire time. Only once the temporary index completes cleanly is it promoted to live. It is the same shape as a zero-downtime deploy and for the same reason: a half-built index must never be reachable. A full rebuild ran daily.
What the interface asked for
Progressive queries, one per option the customer toggles, each narrowing the remaining valid set from the selections already made.
There is no caching layer, and that was deliberate rather than an omission. With a combination space that size the hit rate would have been close to zero, and the entire index was replaced daily with fresh data, so a cache would have spent more effort on invalidation than it ever saved on reads.
What I would do differently
- Use filtered term queries from the start. The analyzer was solving a problem that stopped existing the moment we split the values.
- Ask what the number counts. "Millions of entries" was never a lie, it was an unqualified unit, and the gap between rows and combinations was the entire risk in the project.
- Take the client's format seriously sooner. The comma-separated columns looked like a mess to be cleaned up, when they were a compression scheme that made the whole thing tractable. I spent time trying to normalise away the one property that saved it.
None of that is really about the database. A number quoted in a sales meeting is still an estimate, and the time to ask what it was measured against is before you agree the thing can be done.
No screenshots, assets or code from this work appear on this site. What is described here is the problem and the approach, which is what I can share.