Shopify replaced Redis with MySQL for inventory reservations–and it scaled

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

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.

This is absolutely fascinating. I enjoy real life stories like this. I went to a Node meetup in 2013 when Target had just switched to Node from PHP and it was a similar experience to see their metrics and hear their strategy.

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 1000 rows limit and replenishment process. Instead of dealing with N rows, you always deal with a single row.

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

"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 fascinating that in order to do this, they had to remove 50% of reads and 33% of transactions from the main DB.

The lengths companies will go to avoid running different pieces of software...

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

They were really so proud of that AI image that they just had to tack it on at the end? Did nothing but make the blog post feel like cheap mass produced slop

outside the slop, i liked this post that was linked on innodb locking: https://jahfer.com/posts/innodb-locks/

[dead]

[flagged]

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