Earlier quoted context omitted.
load = bearing gun = smoking insight = key gap = closed summary = executived
belt = suspended
boot = strapped
We replaced Redis with MySQL for inventory reservations and it scaled
61–70 of 281 posts
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#62> 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…
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?
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#63"But the hardest lesson wasn't about database design. It was discovering that the real bottleneck wasn’t what we were observing and measuring."
It's honestly weird Claude converges on this language because it's incredibly wordy and hard to parse. One would think semantic density would win out in training.
Why? This is a common transition that people use in speech and text.
Close out previous paragraph. Segue to completely different topic.
How else are you supposed to go on a tangent?
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#64Earlier quoted context omitted.
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…
explained below in https://news.ycombinator.com/item?id=49228432
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#65Earlier quoted context omitted.
It's honestly weird Claude converges on this language because it's incredibly wordy and hard to parse. One would think semantic density would win out in training.
[flagged]
Sure, it's not technically hard to read.
But it suuucks, making it hard to read, the same way (some) fast/junk food is hard to swallow.
They have access to a trillion dollar writing machine god, and they choose to publish that.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#66Earlier quoted context omitted.
Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have contention on that item. Them having a 1k buffer allows not to take a lock on a single row every time, and only do it when buffer is empty
there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: with current_order as ( select $SKU1, $q2 as quantity union select $SKU2, $q2 as quantity ), with carts as ( sel…
I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#67so this is interesting to me, im in retail i work closely with platforms ive used shopify ive used magento ive used smaller players ive helped implement various pieces of all of them. 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, bo…
I found Shopify’s post very easy to read, and learned about some features of MySQL. On the other hand, I didn’t get any value from reading your comment. You seem to have a bunch of opinions about how things should be done, but haven’t given any details about how you came to these conclusions.
I've done multiple large scale implementations with shopify paired with many flavors of order managmeent systems as well as competing offerings in the space. The only one i haven't touched that i'd like to get my feet wet with is commerce tools.
I really just disagreed with the assessment that redis is not good enough for the job for a reservation system. I use sql database all the time, I prefer them. But I'm seeing a claude written article here that seems to heel turn on a proven technology, it would at most be insightful if there was human content in here from actual engineers at shopify who want to vouch for and explain the challenges they were up against with redis rather than just expect me to take claudes word for it. Anyone who's been dabbling with AI knows damn well that you can convince claude to write up a dissertation on any hill you want to die on.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#68Earlier quoted context omitted.
there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: with current_order as ( select $SKU1, $q2 as quantity union select $SKU2, $q2 as quantity ), with carts as ( sel…
I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
how does current design resolve concurrent actors fighting for the last item ?
there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items.
my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process.
in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items.
but the problem as formulated, imho, is not even correctly defined.
Shopify incorrectly formulated the very problem they are trying to solve.
Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout.
the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay.
So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#69Shopify’s founder and their coo both fund far-right extremism, and its founder thinks only rich people should be able to vote. But anyway, they switched databases. https://www.techwontsave.us/episode/340_shopifys_leaders_are...
The link you shared is just a podcast and does not contain even contain “far right”. Can you provide specific concerns, otherwise I don’t see anything wrong with a CEO of the most successful tech company should not be concerned about a horribly performing country from a GDP perspective.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#70so this is interesting to me, im in retail i work closely with platforms ive used shopify ive used magento ive used smaller players ive helped implement various pieces of all of them. 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, bo…
They do heavily use AI, but you haven’t refuted their point that if inventory is in SQL, storing reservation in a second storage system increases complexity.
I mean I'm all for everything collapsing into sql. SQL all the things. Not really against it, I'd just rather not-AI write the challenges they were up against. It seems like these are all very behind-the-scenes scaling issues they faced, so it'd be cool to hear from them. Redis has great qualities, I don't use it often but I've also for years now understood why Redis was put in front of these use cases to handle them. Complexity be damned generally you're trying to enforce a first come first served or some level of idempotent behavior, so if sqls doing that now then hell yeah. It's just super off-putting to try and upend an important design pattern with an AI written article.