Comment by manbash
3 hours ago
> 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.
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
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.
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?
explained below in https://news.ycombinator.com/item?id=49228432
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.
replenishment is an unnecessary cludge that only exists due to poor design. an "algorithmical smell" if you wish
Depends on the scale. Most companies don't approach the scale where this matters.