Shopify replaced Redis with MySQL for inventory reservations – and it scaled
Shopify replaced Redis with MySQL for inventory reservations–and it scaled
Shopify rebuilt its oversell protection system on MySQL, using one row per inventory unit and the SKIP LOCKED feature to handle peak traffic. The move eliminated consistency issues between Redis and the inventory ledger. The real bottleneck wasn't CPU or slow queries, but connection exhaustion caused by other parts of the checkout path holding database connections too long. After tagging queries and cleaning up the checkout flow, they removed 50% of reads and 33% of transactions on the primary database, meeting their high-throughput targets.
The cleanup of the checkout path removed 50% of reads and 33% of transactions on the primary database.
- manbash
> 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.
- codedokode
Could not they shard the inventory table by shop_id? As I understand, the order includes only items from one store, so there is no need to keep all the stores in a single table.
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
- 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.
- jdw64
Is it really the right choice to drop Redis and go back to a disk based relational database just to wrap transactions into a single unit?
Redis handles tens of thousands of concurrent connections in a single event loop, while MySQL uses one thread per connection. No matter how I look at it, that seems like a step backward.
Of course, performance isn't everything. And if performance isn't a problem, having everything in one place does make it easier to reason about. But I'm worried that under spike traffic, this approach might actually cause more problems.
I think putting a scheduling layer in front of the DB would be a better approach. The application server could handle concurrent connections and only write to MySQL when correctness is actually needed. That seems like a cheaper way to do it. but is it different for large-scale enterprise distributed systems?
- zhivota
"But the hardest lesson wasn't about database design. It was discovering that the real bottleneck wasn’t what we were observing and measuring."
- mrloopex
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.
- 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
- bijowo1676
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.