Fast product search in a B2B ordering system with Elasticsearch and Redis

Product search in this B2B ordering system goes through four layers in order: in-process memory, Redis, Elasticsearch, and SQL Server if Elasticsearch is down. With 30,000 sample products, a first search averages 11 ms and a repeated search averages about 0.01 ms. The backend is a .NET 9 Web API with EF Core on SQL Server.

Those numbers need context. Timing happens inside the service with a Stopwatch and comes back on every response in an X-Search-Ms header, with the layer that answered in X-Search-Source. I measured on my own machine against a single-node Elasticsearch in Docker with a 512 MB heap. Network time isn't included, so from a browser you'll see larger numbers.

Why a SQL LIKE wasn't enough

The sample catalog is automotive spare parts: brake pads, brake discs, oil filters, spark plugs, from brands like Bosch and Brembo. The product names are in Turkish, and the dealers using this screen don't type them carefully. They type the first few characters of a part code. They skip Turkish letters and type "yag filtresi" for "yağ filtresi". They misspell "balata" as "blata".

ProductName.Contains(q) handles none of that. It has no idea about typos or that "ğ" should match "g", and it can't rank by relevance. It also scans the table on every search, which gets worse as the catalog grows.

How the chain works

The search service checks four places and stops at the first one that answers:

// Simplified sketch of the real service
long version = await cache.VersionAsync(tenantId);
string key = cache.Key(tenantId, version, request);

if (cache.TryMemory(key, out var hit)) return (hit, "memory");
if (await cache.TryRedisAsync(key) is { } r) return (r, "redis");

try
{
    var es = await index.SearchAsync(tenantId, request);
    await cache.WriteAsync(key, es);
    return (es, "elastic");
}
catch (HttpRequestException)
{
    return (await SearchSqlAsync(request), "sql");
}

The memory layer is .NET's IMemoryCache with a 60 second lifetime. Redis keeps results for 10 minutes and is the layer shared between app instances if there's more than one. Anything read from Redis is also written into memory, so the same search won't even reach Redis the second time.

That 0.01 ms figure is a memory hit.

If Redis is unreachable or throws, the code skips it and keeps going. If Elasticsearch can't be reached, a warning is logged and the same filters run against SQL. Losing any one layer makes search slower for the dealer, but they never see an error page.

The index settings do half the work

Most of what makes Elasticsearch useful here is how the index is built. Product names get a Turkish lowercase filter plus asciifolding, which is why "yag" and "yağ" end up as the same token. Product codes have a separate edge_ngram subfield (1 to 20 characters), so a dealer typing "FRN-0" immediately sees codes starting with that prefix. Names get a 2 to 15 character prefix subfield and fuzziness: AUTO, which is what turns "blata" into brake pads.

For ranking, an exact code match gets a boost of 10 and a code prefix match a boost of 5, so a dealer who knows the exact part code sees that part first. With no search term, results are sorted by name. The data is small enough that the index has one shard and no replicas.

Tenant isolation, enforced in three places

Several companies share the same database. A search result from one company must never reach another company's dealer, even when it comes out of a cache, so I didn't want that guarantee to depend on a single check.

The first place is the Elasticsearch query. The first clause in every query's filter block is a term filter on firmaId. Being in filter context, it doesn't affect scoring and can be cached by Elasticsearch. The tenant ID comes from a claim in the JWT. It is never read from a request parameter, so a dealer can't reach another company's products by editing the URL.

The second is the cache key, which looks like search|tenantId|version|query|brand|price range|stock|page|size. Two companies searching for the same word land on different keys.

The third is the SQL fallback. EF Core has a global query filter on Product: HasQueryFilter(x => x.FirmaId == ActiveTenantId). I got this wrong once and had to fix it. The filter has to read the tenant on every query, which means pulling the value from a property on the DbContext. If the filter is written so the value gets captured once and reused, isolation breaks without any error to tell you.

Keeping the index fresh

In an ordering system, stale search results cause real damage. If a sold-out part still shows as in stock, the dealer orders it and the problem surfaces in the warehouse.

I used EF Core's SaveChangesInterceptor for this. Just before a save, it records every product that was added, modified or deleted. If the save succeeds, that list goes to Elasticsearch in a single _bulk request and deleted products are removed from the index. If the save fails, the list is dropped and nothing touches the index. Code that updates products doesn't know search exists.

For cache invalidation I don't delete keys. Each tenant has a version counter in Redis. When a product changes, that tenant's counter goes up, and since the version is part of every cache key, old results are simply never read again and expire on their own. The version number itself is cached in memory for one second, so another app instance can lag by at most a second.

On startup, a background service checks whether the index exists. If it doesn't, it creates it and loads every product in batches of 2,000.

Where it falls short

There are three weak spots I know about.

When a write to Elasticsearch fails, all that happens is a warning in the log. The product is current in SQL and stale in the index, and the startup reload only runs when the index is missing entirely. The next step should be a queue that retries failed writes.

The SQL fallback searches with Contains. While Elasticsearch is down, a dealer typing "blata" gets nothing, and there's no Turkish character matching either. It keeps the system up, and search quality drops noticeably.

Bulk requests go out with refresh=true, so changes are searchable right away. For single product edits the cost doesn't show. For an import of thousands of products, forcing a refresh on every batch would be expensive, and that path will need its own setting.

Putting the measurement in code

The speed claim is tied to a script. It runs 25 search terms, 4 pages each, so 100 searches cold and then the same 100 warm. It reads X-Search-Ms and X-Search-Source from each response and prints average, P95 and worst time per layer. The terms deliberately include misspellings like "blata" and code prefixes like "FRN-0".

If you add a cache layer to your own project, return the layer that answered in a response header. You'll see which layers actually do the work, and if one quietly stops working you'll be the first to notice.