What Shopify's Redis-to-MySQL Swap Actually Proves

Shopify moved one of the hottest paths in commerce — the inventory hold that happens on every checkout — off Redis and onto sharded MySQL. Then it rode out a Black Friday where merchants peaked at $5.1 million in sales per minute, part of a record $14.6 billion BFCM weekend. On its face that's a man-bites-dog story: the in-memory store replaced by the relational database, on purpose, for performance-critical work.

But read it as "MySQL is faster than Redis" and you've learned the wrong lesson. Redis didn't lose a benchmark. It lost an argument about where transactional data belongs — and the most interesting part of the story is that after all the clever schema work, the database was never the bottleneck anyway.

Fast but wrong

The old system kept reservations as counters in Redis — increment on hold, decrement on release — while the actual inventory ledger lived in MySQL. Two systems, no shared transaction. That gap is where the bugs lived: a payment could succeed while the ledger deduction failed, or the reverse, and you get overselling or ghost stock. During a flash sale, those races aren't theoretical; they're refunds and support tickets at $5 million a minute.

This is the classic failure mode of Redis-as-system-of-record. Redis is superb at ephemeral, loss-tolerant state — caches, rate limits, presence. Reservations are none of those things. They're short-lived, yes, but they must reconcile exactly with a ledger. Once you accept that the reservation and the ledger need to commit or roll back together, the conclusion writes itself: the reservation has to live in the same ACID boundary as the ledger. For Shopify, that meant MySQL, because that's where the ledger already was.

One row per unit

The naive port — a quantity column you decrement under FOR UPDATE — would serialize every buyer of a hot SKU behind one row lock. Shopify inverted the model: one row per sellable unit, so ten hoodies are ten rows, drawn from a bounded pool of up to 1,000 available rows per item and location. Reserving three units means locking three rows with SELECT ... FOR UPDATE SKIP LOCKED, moving them into a reserved table, and committing — all in one transaction. Concurrent buyers skip each other's locked rows instead of queueing behind them.

SELECT id FROM available_units
WHERE shop_id = ? AND item_id = ? AND location_id = ?
LIMIT 3
FOR UPDATE SKIP LOCKED;

If this looks familiar, it should. SKIP LOCKED is the canonical database-backed job-queue primitive — PostgreSQL shipped it in 9.5 back in 2016, and it powers queue libraries like Solid Queue and good_job today. MySQL only got it in 8.0, which is worth pausing on: this architecture was simply unavailable to Shopify for most of Redis's reign. Some "Redis replaced X" stories are really "the relational database grew a feature" stories, and this is one of them.

The supporting details are where the production scar tissue shows. Switching the primary key to a composite (shop_id, inventory_item_id, inventory_group_id, id) meant reserve queries lock one index instead of two, halving lock traffic on the hottest path. Dropping from InnoDB's default REPEATABLE READ to READ COMMITTED killed the gap locks that were blocking pool replenishment when a hot item ran dry. Standardized lock ordering — always delete before insert — ended deadlocks between the reserve and claim paths. None of this is exotic; all of it is the kind of thing you only learn by running the workload.

The bottleneck wasn't the database

Here's the part worth clipping and saving. After the schema work, throughput plateaued while query latency stayed fine and CPU stayed low. The constraint turned out to be connection exhaustion: other parts of the checkout flow were holding database connections across long transactions, starving the reservation path.

Shopify's fix was observability, not indexing. They tagged every connection with a SQL comment (/* conn_tag:checkout_completion */) and parsed the tags at the ProxySQL layer to attribute connection hold time to specific callers. The resulting cleanup removed 50% of reads and 33% of transactions from the primary. If you run Rails, you already have the tagging half of this for free in query log tags; almost nobody wires up the attribution half. At scale, connections are the scarce resource — not QPS — and most teams have no idea who's hoarding theirs.

The rollout was equally unglamorous and equally correct: shadow mode with Redis still authoritative while MySQL results were validated, then pod-by-pod promotion with a kill switch. During the peak, writer CPU stayed under 50% and readers under 16%.

Copy the reasoning, not the conclusion

Zoom out and this is another data point in the great database consolidation. Rails 8 dropped Redis from the default stack in favor of database-backed Solid Queue and Solid Cache; the "just use Postgres" crowd has been making the same case for queues, locks, and pub/sub for years. Shopify — the largest Rails shop there is — just demonstrated the argument at the most hostile scale available. The direction of travel seems clear: Redis is retreating to what it's genuinely best at, ephemeral caching, while anything that must reconcile with a ledger migrates back inside the transaction boundary.

That said, this is a case study, not a commandment. The design works because the ledger was already in MySQL, because MySQL 8's SKIP LOCKED exists, and because Shopify paid for it in operational complexity elsewhere: the row pool needs an inline replenishment process, which is a new thing to monitor and page on, and one-row-per-unit trades storage and table size for lock concurrency — bounded only because of that 1,000-row cap. HN skeptics arguing "Redis is fine for reservations" aren't entirely wrong, either; Redis plus careful reconciliation can work. It's just strictly more moving parts than one transaction, and reconciliation code is where correctness goes to die.

The transferable lessons are smaller and sharper. If you're building holds, seat allocation, or a work queue on a relational database, FOR UPDATE SKIP LOCKED with row-per-unit granularity is the pattern, and it's twenty lines of SQL. If your database "can't keep up" while CPU sits idle, audit connection hold times before you shard. And if a critical invariant spans two datastores, the bug already exists — you just haven't hit it at $5 million a minute yet.