Elasticsearch for Everyday Search

Swapping a naive SQL LIKE search for a properly tuned Elasticsearch index took one of our platforms from 2.1s to 200ms per query. That’s roughly a 10x improvement, and it didn’t come from throwing more hardware at the database — it came from using the right tool for the job.

Where the 2.1 seconds was going

The original implementation was a fairly typical growth-path mistake: a search box backed by WHERE column LIKE '%term%' across a handful of text columns, on a table that had grown from a few thousand rows in staging to several million in production. Every search:

• Did a full table scan, because a leading wildcard (%term%) can’t use a standard B-tree index.
• Re-ran that scan across multiple joined tables for multi-field search (title, description, tags).
• Competed for the same connection pool and I/O as every other write-heavy query on the primary database.

None of that is a bug, exactly — it’s just what happens when a feature that started as “let people filter a short list” becomes “let people search a growing catalog” without anyone revisiting the approach.

Why Elasticsearch, specifically

Elasticsearch (and search engines like it — Postgres full-text search or Meilisearch would apply the same logic at smaller scale) solves this with an inverted index: instead of scanning rows for matching text, it pre-computes which documents contain which terms, so a search becomes a lookup instead of a scan. A few things mattered more than the headline speed number:

1. Relevance scoring out of the box. BM25 ranking meant results weren’t just “contains the term,” they were ordered by how well they matched — something a LIKE query can’t do at all without hand-rolled scoring logic.
2. Multi-field search without joins. Title, description and tags could be indexed together with per-field weighting (title matches ranked higher than description matches), replacing what had been a fragile multi-join query.
3. Typo tolerance and partial matching. Fuzzy matching and edge n-grams handled “elasitcsearch”-style typos and prefix search without extra application code.
4. It took load off the primary database. Search traffic stopped competing with transactional writes for the same I/O and connection pool — a side benefit that mattered as much as the raw speed.

What the migration actually looked like

The rollout was intentionally boring:

• Stood up an Elasticsearch cluster and defined an explicit mapping (not relying on dynamic field detection — that gets messy fast with mixed content types).
• Wrote a sync job that indexed changes on write (via an outbox-style event) rather than batch-reindexing on a cron, to keep search results from going stale.
• Ran the new search path behind a feature flag against a subset of traffic, comparing result quality and latency against the old query before cutting over fully.
• Kept the SQL query around as a documented fallback for a few weeks, then removed it once confidence was high.

The trade-off worth naming

Elasticsearch isn’t free complexity-wise — it’s another stateful service to run, monitor and keep in sync with the source of truth, and eventual consistency between the database and the index is something you have to design for explicitly (a write and an instant search-after-write can briefly disagree). For a small, low-traffic list, LIKE or Postgres’s built-in tsvector full-text search is genuinely the right call — don’t reach for a search cluster to filter 500 rows.

The lesson that generalized past this one project: search is one of the highest-leverage performance wins available in a typical web app, precisely because it’s so often left as an afterthought bolted onto the same database doing everything else. If your product has a search box and more than a few tens of thousands of rows behind it, it’s worth profiling that query specifically — the gap between “works in staging” and “times out in production” tends to show up there first.