Shopify replaced Redis with MySQL for inventory reservations–and it scaled
https://shopify.engineering/scaling-inventory-reservations> 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.
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
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.
1. Deduct the reservation from the inventory when the user starts to order, but in the same txn also maintain a separate row for the in progress order flow. 2. If the order flow is aborted or times out have a background process that returns these to the inventory.
That seems simpler than this approach and involves no locking. Though their presented approach is also reasonable, there must be some reason not to choose a simpler flow. It is not that difficult to have a gc service that scales, but may be they didn't want to separate that.
I disagree with the other posters about the bg process, if you have any bg processing already you should be able to handle the few edge cases without too much trouble.
I don't think you really need that even. An indexed lookup is fast and you don't need to store a computed quantity generally.
1. Backgrounds process can back up
2. They need context of the user and need to switch context per user
3. What if they fail, you create some DLQ or another process to handle the failure
4. Who looks on those failure and how do they act
TLDR; there is always a cost
Instead of having 1000 rows per shop*SKU, why not just have one row per shopping cart*SKU?
That way a single row would represent a single cart, and will hold info of multiple items of the same SKU.
No need a cludge with 1000 rows limit and replenishment process. Instead of dealing with N rows, you always deal with a single row.
One would think semantic density would win out in training.
It’s also annoying as a human because Claude et al rate their own writing very highly, putting human<>LLM interactions at a disadvantage to human->LLM<>LLM interactions.
No, it's not. Honestly, the more I see these complaints, I honestly just think people are bad writers and shit readers.
Simply put, if that's hard to parse for you, and you are a native-English speaker, pick up a book for once in your life.
First, "the hardest lesson". What lesson? It is out of context. Nobody was talking about lessons before this.
Second, "the bottleneck wasn't what we were measuring and observing". Of course the bottleneck itself wasn't that. They couldn't discover what the bottleneck was using the information in their measurements and observations.
It is a clunky and frankly incorrect passage in an otherwise well written article.
But I guess the point is that even in the MySQL scenario the 'reserved_quantities' is almost like a temporary table so either way is not the 'Real' inventory
and i was excited to get some insight, then i realized that this whole thing was written by AI and im going to guess the idea and implementation were probably very AI driven.
> The solution: SKIP LOCKED > Core idea: one row per unit, bounded by design
cool, thanks claude.
Now I'm wondering what the engineering culture is even like at shopify.
Here's the thing. I like databases, I think there's a lot of shit in this space that went and smoked a shit ton their own good stuff to come up with these pure event driven designs that lock you into event workflows with no isolation and remove the ability to do broader bulk-functions.. and then do something even stupider and say "all you need for the interface is graphql" and such service/platform doesn't give you any other way to reconcile or do reporting for your org you have to warehouse from graphql.. this is crap. So seeing a headline where shopify says they want to kinda get behind a unified database strat behind the scenes even if it's not necessarily customer facing, like that's good imo. SQL is many decades of relational algebra that makes insane computations acrossed vast sets of data pure magic and one of the best query dml interfaces of all time.
..however i dont even agree with the claim their making here that redis isnt the tech for a reservation system. redis when used correctly feels like an insanely awesome way to do a reservation system, i lurv redis for stuff like that.
I'm just gonna go forward with the assumption that current and future shopify updates are pure vibeslop. I already hate their data interfaces, but compared to other saas offerings i appreciate that they do have bulk-features.
For example if it takes 20ms to write a batch to the WAL then if you do 5 updates to the same row then that is a minimum of 100ms. But without waiting on locks if you can batch all the WAL writes together then this could be just 20ms.
I don’t think holding locks while waiting for WAL is strictly necessary. There is definitely some anomalies that can happen if you don’t wait for WAL to be durable because transactions that don’t write WAL can observe non-durable writes in some situations. So for example conditional updates that don’t perform work. But I assume this can be fixed by making these wait on the commit for dependent transactions to become durable if they are empty. There is also the problem of failing writes that reveal information about non-durable writes which is more tricky. For example you try to insert into a unique index and it fails, but the duplicate was due to a non-durable write that is lost.
Pure reads should be fine when using MVCC because you just show the latest durable version of the DB. I know some other replication systems will run all transactions including reads through the WAL/replicated log in order to not have anomalies.
https://www.techwontsave.us/episode/340_shopifys_leaders_are...