Migrating from SQL Search to Elasticsearch: What Retailers Need to Know

LIKE '%query%' works, right up until it doesn’t. Most retail platforms start their product search life as a SQL query against the products table — a LIKE clause, maybe a few JOINs, maybe a basic relevance hack bolted on later. It’s fine at low volume and low complexity. Then the catalogue grows, the traffic grows, “search for trainers” needs to understand that someone probably meant “trainers” even if they typed “trainres,” and the database that’s supposed to be handling checkouts and stock levels is also now straining to answer every search query in real time. That’s usually the point retailers start looking at Elasticsearch.

This isn’t a theoretical migration path. It’s the one we’ve actually done, at real retail scale, more than once.

Why SQL search runs out of road

Relational databases are built to answer precise questions reliably — “what is the stock level of SKU 48213” is exactly what SQL is for. They’re not built to answer fuzzy, ranked, human questions — “what did this person probably mean, and in what order should the results appear” — and bending SQL into doing that job tends to produce the same recognisable problems:

• Relevance that doesn’t actually rank by relevance. Most SQL-based search either returns results in a fairly arbitrary order or needs increasingly elaborate manual scoring logic bolted onto the query — logic that gets harder to maintain every time someone asks “why does this product show up third instead of first.”
• Typo and synonym handling that barely exists. “Trainers” and “trainres” are different strings to a LIKE clause. So are “trainers” and “sneakers,” unless someone has manually built and maintained a synonym table — which most teams haven’t, because it’s tedious and easy to let drift out of date.
• Faceted filtering that gets slow fast. Filter by size, colour, brand, price band, and availability at the same time, across a catalogue of real size, and the query plan behind that often turns ugly — multiple joins and COUNT operations the database wasn’t designed to do quickly at that shape.
• Search load competing with transactional load. The same database handling checkouts, stock, and orders is also absorbing every search query — including the heavy, unpredictable ones from Black Friday traffic or a product going viral — at exactly the moment it can least afford to be slow.

Elasticsearch exists specifically to solve the problem SQL wasn’t designed for: fast, ranked, fuzzy, faceted search over large, frequently queried datasets, running as its own system rather than competing for the same database connections as everything else.

What the migration actually involves

This is where a lot of advice online gets vague, so here’s the real shape of it:

1. Indexing strategy, not just data export. The products table isn’t the index. Deciding what gets indexed, how it’s structured (nested objects for variants, flattened fields for filtering, which fields are analysed text versus exact-match keywords) is the design work that determines whether search is actually good once it’s live — get this wrong and you’ve just rebuilt the SQL problem on different infrastructure.

2. Keeping the index in sync with the source of truth. The product database stays the system of record; Elasticsearch becomes a purpose-built copy shaped for search. That means a real sync mechanism — event-driven updates when stock or pricing changes, not a nightly batch job that leaves search results stale for hours. Getting this architecture right is as much an integrations problem as a search problem.

3. Relevance tuning, which is never really finished. Field boosting (should a match in the product name outrank a match in the description?), synonym handling, typo tolerance, and ranking by factors like availability or popularity all need deliberate configuration — and then ongoing adjustment once real query data starts coming in and shows you what people are actually searching for versus what you assumed they’d search for.

4. Running it in parallel before cutting over. Replacing live product search in one move is how retailers end up with a very public, very embarrassing search outage. The safer path is running Elasticsearch alongside the existing SQL search, comparing results, and cutting over once confidence is actually earned rather than assumed.

5. Infrastructure and ongoing care. Elasticsearch is a distributed system with its own operational considerations — cluster sizing, shard strategy, monitoring, re-indexing strategy as the schema evolves. It’s a real piece of infrastructure to own, not a drop-in plugin.

What this actually changes for the business

Done properly, the shift from SQL to Elasticsearch search shows up as:

• Product search that returns genuinely relevant results instead of roughly-matching ones, even when customers misspell or use different terms than the catalogue does.
• Faceted filtering (size, colour, price, availability) that stays fast as the catalogue and the traffic both grow.
• A transactional database no longer straining to serve every search query as well as every checkout — which matters most exactly when it matters most, during peak traffic.
• A foundation that can actually support “search as you type,” better autocomplete, and more sophisticated product discovery later, rather than more layers bolted onto a system that was never designed for it.

Where this experience actually comes from

This isn’t textbook knowledge. Dave spent 14 years inside schuh’s technology team, where search infrastructure serving a significant share of national retail traffic was migrated from SQL-based search to Elasticsearch as part of a wider programme of ecommerce, loyalty and retail platform work supporting more than a million loyalty members. That’s the difference between advice written from documentation and advice written from having actually carried a pager for the system in question.

It’s also why Search Technology is a named service line at BettaSites rather than something bundled generically under “web development” — product and site search at real scale is its own discipline, with its own failure modes, and it’s worth treating as one.

Is this the right move for your business?

Not every retailer needs Elasticsearch. If your catalogue is small and your current search is keeping up fine, migrating is solving a problem you don’t have yet. The signals worth watching for: search feels slow or returns results that don’t make sense, filtering is noticeably sluggish once more than one or two facets are applied, your database is visibly under more load during traffic spikes than it should be, or the business is asking for search features — autocomplete, “did you mean,” better ranking — that the current setup genuinely can’t support without a rebuild.

If any of that sounds familiar, talk to us about what a migration would actually involve for your platform specifically — not a generic migration checklist, but an honest assessment from someone who’s done this exact move at real retail scale, not just read about it.