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

21–30 of 280 posts

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

#21

Post by isignal »

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

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

#22

Post by paytonjjones »

zhivota wrote: post #6

"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.

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

#23

Post by vxxzy »

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?

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

#24

Post by sandeepkd »

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…

The moment you added a background process you just replaced the complexity.

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

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

#25

Post by sandeepkd »

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…

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

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

#26

Post by firasd »

Makes sense... if you are counting something in MySQL and now your counter is in Redis that's already strange

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

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

#27

Post by peyton »

paytonjjones wrote: post #22
zhivota wrote: post #6

"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.

Who knows. I wish ant harshly penalized speaking litotically because it’s essentially reward hacking as it can often be read multiple ways.

It’s also annoying as a human because Claude et al rate their own writing very highly, putting humanLLM interactions at a disadvantage to human->LLMLLM interactions.

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

#29

Post by firasd »

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…

My understanding is: your proposal is not very different from what Shopify is doing except they are tracking 'reserved units' (one per row) and you are proposing tracking 'orders' as the temporary state to then reconcile back with inventory quantities.

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

#30

Post by soontimes »

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…

Can you clarify why this involves no locking? There can still be 2 actors fighting for the same row.
Post reply on HN