Designing Routing Keys for Database Sharding in an Order Platform

Overview After splitting databases and tables, the first problem is selecting a routing key that exists uniquely in each table and ensures even data distribution. For large‑scale archival workloads, timestamps can serve as routing keys, creating tables per month or quarter to keep historical data less frequently accessed.

Supported Scenarios In a food‑order platform, users need real‑time order status, while merchants require order information for analysis and decision‑making. The system must handle queries that may span multiple tables due to sharding.

Routing Strategy Random insertion into tables forces queries to scan all tables, which is inefficient. By defining deterministic routing rules—such as hashing a known key—both insertion and query can target the appropriate table, reducing complexity.

User‑Side Routing Key Using the user ID as the routing key ensures that all data for a given user resides in the same table. In a single‑database setup, the user_id is hashed and modulo‑ed by the number of tables to determine the target table.

In a multi‑database, multi‑table architecture, the hash of user_id first determines the database, then the hash (or division) determines the specific table within that database.

Merchant‑Side Routing Key A separate set of tables is designed for merchants (B‑side). The merchant ID serves as the routing key, following the same hashing/modulo logic as the user side, ensuring consistent routing for both parties.

The complete data flow diagram illustrates how orders are routed from user creation, through message queues, to the appropriate merchant database, using identical routing strategies on both sides.

The key takeaway is that routing keys should be closely related to business entities (e.g., user ID, merchant ID) and processed via hash modulo operations to achieve balanced distribution across shards.