Search at 100M Requests a Day

Four rewrites of one query on a private-markets data platform, from a database LIKE through a hand-tuned lexical engine to a hybrid retriever fusing BM25 with 768-dimension embeddings.

Overview

The search path on a private-markets data platform got rebuilt four times over about five years. Same feature, same search box, four different implementations underneath. None of the rebuilds happened because anyone wanted to modernise. Each one happened because something specific had broken.

By the end the cluster held 129 indices, 769 million documents and around 5.3 TB, and search was serving over 100 million requests a day.

Rewrite one: get off the database

Search ran against the primary databases at first, MySQL and MongoDB, which is where most search starts and where it stays until the corpus gets big enough to hurt. The thing a database will not give you is relevance ranking. A LIKE tells you that a row matched. It cannot tell you how well it matched, so it cannot tell you what to put first.

So we moved search onto Elasticsearch. The migration itself was ordinary. What mattered was the package we built on top of it, which let a Django or MongoEngine model declare its own index: which fields to ship, how to map them, how to fetch the things the ORM could not reach on its own. Before that, adding search to a feature meant hand-writing Elasticsearch JSON, and teams mostly did not bother.

They bothered afterwards. It grew to 63 index modules and roughly 41,000 lines across the platform, and every one of those indices inherited the analyzer work, the boost tuning and the reindex tooling for free, because all of it lived in one place.

Rewrite two: make one field match five ways

Once search works, people rely on it, and then they start telling you precisely how it is wrong.

Nobody complained about empty result pages. The complaints were near misses. Someone searching ecommerce missed everything written as e-commerce. Someone searching pharma missed Pharmaceuticals. Someone searching a company’s full legal name found it ranked below a competitor whose about-us page happened to mention it more often.

Those are all the same bug. One analyzer decides what counts as a token, and any query that disagrees with that decision quietly fails. So we stopped choosing. Each searchable field is indexed four ways at once: verbatim through a keyword tokenizer, normalized with lowercasing and accent folding, edge-ngrammed from 2 to 15 characters in both directions to catch prefixes and suffixes, and run through a character filter that strips separators so e-commerce, e commerce and ecommerce collapse to one token.

A search term fans out across all four of those, in five clause shapes, against every searchable field, each with its own boost. The boosts come from three tiers. A product or revenue-segment field sits near the top at 30, a company’s display name at 27, a free-text description at 1, on the reasoning that a term showing up in a company’s list of disclosed products means something quite different from the same term buried in a paragraph of prose.

Two things from this rewrite I would keep in anything I build again.

The first is that every clause is named. Elasticsearch lets you attach a _name to any clause and tells you which ones matched, so every generated clause carries one: display_name.match_phrase_standard_0, hsn_codes.match_partial_0. When someone asks why a result ranked where it did, you look it up instead of spending an afternoon guessing.

The second is that quotes turn the fuzziness off. A quoted term drops the three loose clause shapes and keeps only the two phrase ones, which gave power users a precision escape hatch without making the default behaviour stingy for everyone else.

Rewrite three: one index, many truths

The third rewrite had nothing to do with relevance. Different client organisations were entitled to see different financial numbers for the same company: the public filing for most, a separately curated set for some. A flat document holds one set of numbers and cannot express that at all.

The fix was to move the financial fields into a nested array where each entry records which organisations are allowed to see it. At query time the user’s organisation becomes a filter, filters on financial fields are rewritten to point inside the array, and the matching entry is flattened back to the top level on the way out, so nothing downstream ever learns the document changed shape.

That is the part of this work I find most interesting, and it needs more room than a section here, so I wrote it up separately as One Index, Many Truths, including the diff harness we used to prove the migration had not quietly changed anyone’s numbers.

Rewrite four: teach it meaning

By this point the lexical engine was about as good as hand-tuning gets. Then I read the search logs properly and found that we had spent years optimising the wrong thing.

The most-searched terms were fintech, pharma, healthcare, gaming, it services, saas, packaging, family office, education, agri. Of the fifty most frequent queries, two were the name of a company. The rest were concepts.

No boost table solves that. A company’s filings do not contain the sentence “we are a fintech”. They contain a corporate identification number, a list of disclosed products, an auditor’s report, and a business activity description written to satisfy a regulator. Token matching against that finds every company with the word in its registered name and none of the companies that actually are one.

The workarounds were visible in the logs too. One recurring query was "manufacturer"||"manufacturing"||"manufacture", which is somebody hand-writing a boolean OR across three inflections of a single word because the engine would not connect them. Users compiling their own queries is a fairly direct statement about what the engine cannot do.

So the fourth rewrite added a second retriever alongside the first rather than replacing it.

What gets embedded is a composed semantic profile for each company, a summary of what the business actually does, rather than the raw document concatenated, which embeds badly. The model is BAAI/bge-base-en-v1.5 at 768 dimensions. ELSER would have been the path of least resistance inside Elastic, but it needs a Platinum licence we did not have, and semantic_text works against any inference endpoint rather than only Elastic’s own. bge-base sits near the top of the MTEB retrieval benchmark and runs on CPU, which mattered more than it sounds, because the whole thing had to be provable on a laptop before anyone would fund GPU capacity for it.

The two legs are combined with Reciprocal Rank Fusion, and the existing BM25 query carries over unchanged as the lexical leg. RRF works on ranks rather than scores, which is what makes it safe here. BM25 scores and cosine similarities are not on the same scale, and no amount of normalising makes them comparable.

Fusion is less dramatic than the marketing around it suggests, and the widget above is built so you can check that rather than take my word for it. A document appearing on both lists beats a document ranked first on only one, so the literal name matches do not disappear. On family office, where the two retrievers agree on nothing, RRF has nothing to reinforce and simply alternates between them. Rank fusion lifts a weak lexical leg without overruling it, and weighting the two legs against each other is the next thing I would have tuned.

I built all of it locally, Elasticsearch in Docker on a laptop, embedding the full corpus, so that the cost of being wrong was an evening rather than a procurement cycle.

It reached a validated local index over the whole corpus. The production rollout was still ahead of it, which I would rather state than leave ambiguous.

What I would do differently

Read the query logs in year one rather than year five. Every decision in rewrite two was a reasonable guess about what users wanted, and the answer was sitting in a table the entire time.

Delete failed experiments properly, too. There is still a fuzziness: 2 commented out in that query builder, from an attempt to fix the near-miss problem with edit distance before we fixed it with analyzers instead. Backing it out was the right call, since fuzzy matching across 37 fields is expensive and turns up things nobody meant. But leaving the corpse in the file taught the next reader nothing that a line in the commit message would not have.