How to Choose Effective Routing Keys for Database Sharding in Order Systems

Overview

After splitting databases and tables, the first problem is choosing a routing key that exists uniquely in every table. The routing strategy should aim for even data distribution.

For large‑scale archival workloads, time (e.g., creation timestamp) can serve as the routing key, creating a new table each month or quarter to keep historical data in less‑frequent tables.

Business‑related keys can also be designed to ensure each database handles traffic efficiently.

Supported Scenarios

In a food‑delivery order platform, users need real‑time order status, while merchants analyze orders for business decisions.

User Consumer = C‑side, Business = B‑side

After placing an order, the record may land in different tables, requiring queries across multiple tables.

Routing Strategy

If orders are inserted randomly, queries must scan all tables to guarantee accuracy.

When insertion follows a rule, queries can apply the same rule to locate the correct table, reducing complexity. Both user and merchant queries should follow the same routing strategy.

User‑Side Routing Key

Using the user ID as the routing key ensures all data for a user resides in a fixed table.

In a single‑database setup, hash the user_id and mod by the number of tables to determine the target table.

In a multi‑database, multi‑table environment, first hash the user_id to select the database, then hash again (or divide) to select the table within that database.

Merchant Routing Key

A separate set of tables is designed for the B‑side (merchant).

Merchants also use the merchant ID as the routing key, following the same hash‑based strategy as users. Orders are sent to a message queue; merchants consume the queue, retrieve order details, and insert them into their own tables using the same routing logic.

Below is the complete data flow diagram for both user and merchant sides.