Splitting an Order Database into Hot and Cold Stores: Our Practical Approach

Why split the order database?

Single database could not handle millions of daily orders; storage was near exhaustion and QPS could not increase. Splitting by time into a hot database (latest six months) and a cold database (historical data) addresses the problem.

Overall design

Hot DB : stores the most recent six months, receives all inserts and updates, supports high‑frequency queries.

Cold DB : stores the full historical dataset, serves low‑frequency workloads such as backend statistics and paginated list queries.

Historical orders may still require modifications (e.g., refunds after six months). The rule is to move the required historical order back to the hot DB before applying any change, similar to retrieving an archived file for editing before re‑archiving.

Write path and cold‑data migration

All write requests (inserts and updates) are directed to the hot DB. If an update targets an order not present in the hot DB, the system automatically fetches the full order from the cold DB, syncs it to the hot DB, and then performs the modification. This adds latency, so users see a “processing, please refresh later” message. Frequently modified historical orders are pre‑warmed in the hot DB.

After a successful update, the order resides in the hot DB and will later be archived back to the cold DB.

Query routing

Exact order‑ID lookup: route to hot DB first, fall back to cold DB if not found; if absent in both, the order does not exist.

Aggregate statistics (count/sum): executed on the cold DB to avoid slowing the hot DB.

Backend pagination lists: served by the cold DB; the hot DB handles only real‑time business.

C‑end user order list: Elasticsearch indexes only the hot DB; default queries cover the recent six months, with links to the cold‑DB page for older orders.

Downstream Elasticsearch and data warehouse

Both Elasticsearch and the data warehouse originally depended on the single DB’s binlog. To avoid changing downstream code, only the hot DB emits binlog events; the cold DB is read‑only. Consequently, downstream systems need no modifications.

The data warehouse obtains full historical data by periodically pulling from the cold DB for offline analysis, which does not affect real‑time operations.

Consistency considerations

Orders consist of a master record and child records. During archiving, both must reside in the same database (either hot or cold) to prevent cross‑DB joins that would degrade performance and increase complexity. The master order’s creation time determines the target database, and child orders follow the master.

Before deleting data from the hot DB, a one‑week retention period is kept to accommodate possible delays in data warehouse extraction and Elasticsearch consumption, guaranteeing downstream systems have synchronized the data before physical deletion.

Risks and mitigations

Cold‑data migration may slow interfaces: mitigated by optimizing query and migration logic, providing user prompts, and pre‑warming frequently modified historical orders in the hot DB.

Cold‑DB query performance: addressed by partitioning by time, creating appropriate indexes, and using column‑store or OLAP engines for aggregation.

Data inconsistency during migration: handled with an atomic “read cold → write hot → modify” operation, retry on failure, alerting, and manual intervention if needed.

Deployment results

After a few days of gradual rollout, the hot DB’s load dropped noticeably, write latency stabilized, and downstream services observed no impact. Although modifying historical orders now includes a migration step, the proportion of such operations is very low, resulting in minimal overall user‑experience impact.

Final thoughts

Database sharding is a large undertaking, but with a clear strategy—unified writes, split reads, and unchanged downstream systems—the impact can be confined. Teams facing similar order‑DB pressure can reference this hot/cold approach to avoid common pitfalls.

Code example

---
关于作者 ---
刘志敏
· Java / TypeScript
全栈开发者
专注高并发系统设计
· AI
科技
·
技术分享
扫码关注公众号「牛流刘」，获取更多技术干货
关注后回复：‘666’ 获取更多技术资料
（基于互联网整理包含免费和付费的）。