Designing Real‑Time Sharding Index and Replication with Elasticsearch for High‑Performance Order Queries

As order volume grew, a single database could no longer handle the read/write load, prompting the team to shard the data and seek an efficient multi‑dimensional query solution.

Instead of relying on traditional database indexes, which become costly and complex at scale, the team evaluated open‑source search engines and chose Elasticsearch for its lightweight nature, ease of installation, and strong distributed support.

To avoid modifying dozens of existing applications, they rejected data‑access‑layer replication and implemented a real‑time database scan that periodically extracts changed rows and writes them in bulk to Elasticsearch, achieving zero coupling and high throughput.

Several write‑side optimizations were applied: using upserts, bulk requests, adjusting index.refresh_interval , pre‑defining mappings, disabling unnecessary norms, and setting index.translog.durability to async for tolerant workloads.

Read‑side performance was improved by routing queries through the _routing field, reducing shard scans, lowering date precision, and configuring field options to minimize indexing overhead.

A monitoring system periodically calls Elasticsearch CAT APIs and stores metrics in a dedicated cluster, providing a web dashboard for operational visibility.

Disaster recovery is achieved by configuring multiple replica shards and deploying a fully synchronized cluster in a separate data center, ensuring service continuity during site failures.

The overall project resulted in significant CPU and memory reductions on application servers while maintaining query latency comparable to the pre‑sharding state, demonstrating a practical, scalable solution for high‑throughput order processing.