Hacker News new | past | comments | ask | show | jobs | submit
manbash | on: Shopify replaced Redis with MySQL for inventory reservations–and it scaled
> Instead of one row per item with a quantity column, we use one row per sellable unit. An item with 10 units has 10 rows.

> But one row per unit for all inventory would break down at scale—an item with 50,000 units across 10 locations would mean 500,000 rows, and the reserve query would slow as it scans through them. Instead, we maintain a bounded pool of available rows, capped at 1,000 per item/location combination. Reservations consume rows from this pool; a replenishment process refills it from the inventory ledger.

Shouldn't I feel uncomfortable with such approach? It seems to create a backoff (pool) for lowering the chance of having a synchronization issue.

esjeon | parent | next
I would call this one-row-per-contract-type, and this is the most general model for the problem (e.g. the model cannot be further broken down into finer level), thus, the most scalable model given storage is dirt cheap.
sandeepkd | parent | next
Comes down to type of items, when you have physical inventory the number is limited so more manageable and interestingly enough the problem only applies to physical inventory.

You are just spending some more disk space to avoid synchronization issues. Denormalization for performance is a really common pattern, just that people do not start with it in the first place itself

bijowo1676 | parent | next
you should, their design is not the best. There is middle ground between "one row per SKU" and "1000 rows per SKU".

Its called one row per shopping cart*SKU combo.

if two people order 100 and 500 items of the same SKU, respectively, the table should have only two rows: for order1 and order2. Not 600 rows.

jakewins | root | parent
Can you explain how that works? With the row-per-item I can see how you’d use locking primitives etc easily to deal with multiple concurrent shopping carts claiming available inventory.. but how does your solution solve contention? There’d need to be some “number of items in inventory” row, wouldn’t there be contention on that?

The point of one row per item is that thousands of concurrent shoppers don’t need to block each other as they can each claim as many free rows as they need for themselves?

jbird99 | parent | next
I guess it depends on how the replenishment process works. Unless you're ordering over 1000 of an item, I doubt it would be a problem.
bijowo1676 | root | parent
replenishment is an unnecessary cludge that only exists due to poor design. an "algorithmical smell" if you wish
jghn | parent
Depends on the scale. Most companies don't approach the scale where this matters.