Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We replaced Redis with MySQL for inventory reservations and it scaled

shopify.engineering

Post reply on HN
Print view View original

91–100 of 281 posts

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#91

Post by bijowo1676 »

idoubtit wrote: post #90
bijowo1676 wrote: post #46

not the best design to have 1000 rows for each shop*SKU combination. If a candidate proposed this solution during Shopify's System Design interview, i doubt he would be vetted for Senior+ position. 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 10…

> not the best design [...] So those engineers at Shopify worked hard for months on a more performant system, but they missed the obvious structure? They chose a complex denormalization for no good reason? It may be true, but I think it's presumptuous to belittle their work when we have only partial information. My guess is that they had good reasons to think that the more obvious ways would not scale. And from readi…

i also work in big tech and know that a lot of bullshit design creeps into system design and prod, because everyone is overworked, overstressed, wants to just get things done for the quarterly performance review as to not get shitcanned with severance

re concurrency, it is not a big issue at all. stock exchanges deal with HFT traders and can easily deal with concurrency of orders. Same can be implemented with shopify, but I doubt they face the same level of concurrency as stock exchange anywhere near

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#94

Post by dbbk »

manbash wrote: post #8

> 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…

I'm familiar with the reserved row approach (I use SELECT FOR UPDATE SKIP LOCKED) and yeah this replenishing idea terrifies me.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#95

Post by dbbk »

vxxzy wrote: post #23
isignal wrote: post #21

It seems there could be a simpler solution. 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 reasonab…

now you have two problems. what happens when your reservation system backs up?

You don't sell stuff I guess

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#96

Post by raverbashing »

bijowo1676 wrote: post #46

not the best design to have 1000 rows for each shop*SKU combination. If a candidate proposed this solution during Shopify's System Design interview, i doubt he would be vetted for Senior+ position. 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 10…

And that's why these interviews can be stupid, you can mention the real solution and interviewers might reject because it's not the textbook solution

But the real world is different

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#97

Post by znpy »

nurettin wrote: post #93

At that revenue, why not make your own filesystem, database and index structure? There is no way mysql is the best possible software for this use case. Why stop innovation and hand everything over to ops?

Most likely? Time.

Using off the shelf software means you mostly design how to plumb things together and how to make them correct , safe and scalable.

The things you mention, on the other hand, carry the same requirements but are also much complex to develop AND to maintain.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#98

Post by post_below »

idoubtit wrote: post #90
bijowo1676 wrote: post #46

not the best design to have 1000 rows for each shop*SKU combination. If a candidate proposed this solution during Shopify's System Design interview, i doubt he would be vetted for Senior+ position. 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 10…

> not the best design [...] So those engineers at Shopify worked hard for months on a more performant system, but they missed the obvious structure? They chose a complex denormalization for no good reason? It may be true, but I think it's presumptuous to belittle their work when we have only partial information. My guess is that they had good reasons to think that the more obvious ways would not scale. And from readi…

You might not have noticed that essentially the entire blog post was AI written.

There's even this bit where they discover a remarkable trick:

> Each round trip to the database has a cost. For carts with multiple line items, we batch reservation queries using UNION ALL so we fetch all needed units in one round trip

Insights like that really don't read like senior level output, and of course, it's LLM output. I'm not sure it's presumptuous to question it.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#99

Post by hanikesn »

bijowo1676 wrote: post #89
admax88qqq wrote: post #83

Earlier quoted context omitted.

> Shopify incorrectly formulated the very problem they are trying to solve. That’s a bold overconfident statement. Cart abandonment is real. People never clear their carts they just walk away Shopify purposefully chooses to do it at payment time because doing it earlier results in lost sales as people “reserve” items and then walk away causing other to see out of stock and then also walk away Whoever puts up the mone…

that's why I mentioned active carts in my post, there are ways to define active cart to get rid of abandoned carts ( ignore carts where last user action was > N seconds ago). Ok, let's accept the design goal that whoever paid first wins. You can use the same metric (how many milliseconds ago did user click PAY) and impose a global monotonic non-decreasing counter to distribute the scarce inventory. This is how order…

Most payment methods in the world don't support separate authorization and capture.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#100

Post by sroussey »

jakewins wrote: post #62
bijowo1676 wrote: post #51

Earlier 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…

One other advantage is item serial numbers. Or something else that makes an item that seems the same but actually be unique (perhaps the warehouse it’s in?)
Post reply on HN