CSE Roadmap

Core tier · about 4 hours · 8 parts

Part 4 of 8 · 3 min read

Orders

A key for data that is read by customer, by order and by time.

Think of an online shop. The customer opens "my orders". The warehouse looks up an order by its number. The finance team sums sales per day. Three readers, three keys.

Case: an orders table with order_id, user_id, created_at, total, status, plus order lines (order_id, product_id, quantity).

Candidate keys:

Key "My orders" (by user) "Order 77" "All orders yesterday" Write spread Notes
order_id (hash) All shards 1 shard All shards Even Lines can follow order_id
user_id (hash) 1 shard All shards unless order_id carries the user All shards Even, except heavy buyers Orders of one user together
created_at (range) All shards All shards 1 shard One hot shard Poor for writes
(user_id, created_at) compound 1 shard, in time order All shards All shards Even Like the book's (user_id, update_timestamp)

The most frequent customer-facing query is "my orders". Shard by user_id with orders sorted by time inside the shard (the compound key). Then:

  • "My last 20 orders": one shard, a short range scan.
  • Order lines: keep the lines on the same shard as their order, by using the user's key. A join between an order and its lines stays on one shard.
  • "Order 77" by the warehouse: put the user id inside the order id, for example order_id = user_id + sequence, so that the shard is known from the id. Or keep a lookup table (order_id → user_id).

A worked example of the order-id trick. An order id is built as a 64-bit number: the first 40 bits hold the user_id and the last 24 bits hold a per-user counter. Given order 77's id, the router reads the user bits and finds the shard without a lookup. The cost: the id is longer and reveals the user id (consider whether that is safe).

The finance report "sales per day" needs all shards. Run it on a copy in a warehouse, or keep a running total per day in a separate small store, updated by each order (a counter, hot key risk: 100 orders a second on one key; split into sub-keys, module 4).

The book "System Design Interview" warns that once the database is sharded, joins across shards are hard; a common workaround is denormalisation so queries read one table. Here, copying the product name and price into the order line is such a step: the order line is self-contained, and no join with a products table on another shard is needed. The copy also records the price at the time of purchase, which is what the business wants.

A heavy buyer problem: a business customer places 50,000 orders a day, all on one shard. Detect the hot customer and spread them: key (user_id, day) so that each day goes to a different shard, at the price of a 7-day report touching 7 shards.

Query Shards touched with (user_id, created_at)
My last 20 orders 1
Order 77 (id carries user) 1
Order 77 (no user in id, no lookup) All
Sales per day All (use a warehouse)

Check yourself:

  • Why keep order lines on the same shard as their order?
  • What does putting the user id inside the order id buy, and what does it cost?