Inventory

The problem

"Design the inventory system of a shop with 1 million products in 10 warehouses." It must know what's on the shelves, what's promised to orders that haven't shipped, and what the website can still sell; take in deliveries and shelf counts; and warn the buyers before a product runs out.

This practice problem reuses the pattern of Design a Flash Sale. What's new: the shape of the stock, changes from the warehouses, the read load from product pages, and reordering. Park picking routes and sales forecasts.

The numbers

From the interviewer: product pages are viewed about 1 billion times a day, each showing In stock or not, and orders reserve about 5 million items a day. Round a day to 100,000 seconds (86,400 really); busy hours run about 5× the average.

what           working
In stock       1 billion ÷ 100,000 s = 10,000 a second (11,600 with 86,400)
  busy         10,000 × 5 = 50,000 a second
reserves       5 million ÷ 100,000 s × 5 = 250 a second at busy hours
rows           1 million products × 10 warehouses = 10 million
size           10 million rows × about 500 bytes = about 5 GB

Stock is read 200 times for each change an order makes, and it all fits in one database.

The stock row: on hand and reserved

table   fields
stock   product_id, warehouse_id, on_hand, reserved
key     (product_id, warehouse_id)

On hand is what's on the shelves, the number staff can count. Reserved is what's promised to orders that haven't shipped. What can still be sold is available = on hand − reserved: warehouse B with 40 lamps, 12 of them reserved, has 28 available.

Flash Sale's single count, lowered at order time, doesn't fit: the row would say 28 while the shelf holds 40, so no count could be checked against it. One total per product fails too: it can't say which warehouse can send the lamp.

What changes what

event                         on hand   reserved
an order reserves 3           –         +3
the order is canceled         –         −3
a reservation runs out        –         −3
a truck delivers 50           +50       –
a count finds 2 fewer         −2        –
a broken lamp is thrown away  −1        –
an order's 2 lamps ship       −2        −2

Shipping lowers both counts, so available doesn't move when a box leaves: it went down when the order reserved. After 2 lamps ship, the lamp in B has 38 on hand and 10 reserved, still 28 available. A reservation is Flash Sale's conditional update, on available:

UPDATE stock SET reserved = reserved + 3
  WHERE product_id = 'lamp' AND warehouse_id = 'B'
    AND on_hand - reserved >= 3

A cancel releases its reservation at once, and a job releases the ones that ran out, as with Flash Sale's holds.

Deliveries and counts: a log of every change

Staff count the lamp shelf in B and find 36 where the row says 38. Overwriting on hand with 36 loses where the 2 lamps went, and any shipment made while they counted. Instead, every change is a line in an append-only log with its amount and a reason, written in the same transaction as the row:

#     change   reason
1     +30      delivery d4
2     −4       shipped o71
3     +20      delivery d7
4     −6       shipped o72
5     −2       shipped o80
6     −2       count
sum   36       on hand

On hand is the log's sum, so the row can be rebuilt at any time, and a report can tell lamps lost on the shelf from lamps sold. It's the ledger from Payment System, for stock: lines are added, never edited. Leaving the row wrong lets orders reserve lamps that aren't there.

In stock on the pages: a cache

Reading the rows for 50,000 page views a second is 10× what one database server answers, on the same rows orders reserve against. Keep each product's available count in a cache for a few seconds instead (say 5): one cache server answers about 100,000 a second. The page may be a little behind, which is fine because it never sells, like Flash Sale's sold-out flag. The order still runs the conditional update on the row, so if the last lamp went a second ago, checkout says sold out. Orders that trusted the cache would let two buyers take the last lamp.

Reorder alerts

Warehouse B sells about 4 lamps a day, and a delivery arrives 5 days after an order. The reorder point is what sells while the truck is on its way: 4 × 5 = 20. When available reaches 20, alert once, and the new lamps arrive as the last one goes. Set it per product and warehouse, since one number for everything is an hour of a fast seller and months of a slow one, and many teams add a safety margin. The stock service checks it on each change against available plus what's on order: the day after ordering 20, that's 16 + 20 = 36, so the alert fires once per order, not daily until the truck comes.

Same pattern as Flash Sale

A reservation is Flash Sale's conditional update, refusing to promise more than is available; reservations that run out are released by a job, like unpaid holds; and the cache plays the sold-out flag's part, saving work without deciding a sale. What's new: two counts instead of one, and stock that also changes from the warehouse side.

Summary

• Data: a row per (product, warehouse) with on hand and reserved.
• API: POST /reservations, POST /counts, GET /availability.
• Numbers: about 50,000 In stock checks and 250 reservations a second at busy hours.
• Reserving: available = on hand − reserved; shipping lowers both.
• Changes: an append-only log with reasons; on hand is its sum.
• Scale: In stock from a cache kept a few seconds; orders check the row.
• Reorder: alert at daily sales × delivery days, per product and warehouse.