Shopify replaced Redis with MySQL for inventory reservations–and it scaled
Shopify engineered a monumental shift, replacing Redis with MySQL for its high-stakes inventory reservation system by leveraging MySQL 8's SKIP LOCKED and a clever "one row per unit" design. This detailed dive into their database migration reveals the intricate technical decisions required to achieve massive scale and atomicity, ultimately highlighting how the real bottleneck was not their chosen database but rather connection management across their entire checkout process. It's popular on HN for its practical, in-depth look at solving a critical distributed systems problem with a relational database, challenging common assumptions about when specialized NoSQL solutions are truly necessary.
The Lowdown
Shopify's engineering team recently detailed their journey of migrating a critical inventory reservation system from Redis to MySQL, demonstrating that traditional relational databases can handle extreme e-commerce scale when approached with sophisticated design and optimization. Faced with the challenge of preventing overselling at peak sales of over $5.1 million per minute, their existing Redis setup presented atomicity issues and lacked multi-location awareness, prompting a bold move towards a unified database strategy with MySQL.
- The Challenge: Ensure inventory items are correctly reserved and claimed during payment processing to prevent both overselling (angry customers) and underselling (lost revenue) at Shopify's immense transaction volumes. The previous Redis system struggled with atomic operations between reservations and the primary inventory ledger, and multi-location inventory support.
- The Solution: MySQL with
SKIP LOCKED: Moved reservations to MySQL, achieving ACID guarantees by co-locating with the inventory ledger. Leveraged MySQL 8'sSKIP LOCKEDfeature, enabling "one row per unit" (instead of one row per item quantity) to reduce contention during reservations. Implemented a "bounded pool" of reservation rows (e.g., 1,000 per item/location) to manage table size and query performance, with an inline replenishment mechanism for hot items. - Key Technical Deep Dives: Optimized lock behavior by using a composite primary key to reduce locks per row. Switched to
READ COMMITTEDisolation level to avoid gap locks and deadlocks during replenishment. Standardized lock ordering across transactions to prevent deadlocks between reserve and claim operations. Batched reservation queries usingUNION ALLto minimize database round trips and latency. - Unmasking the Real Bottleneck: Initial optimization efforts hit a throughput ceiling despite low CPU and acceptable query latency. Through custom SQL comment tags (
/* conn_tag:process_name */) and ProxySQL monitoring, they discovered database connection exhaustion was the culprit, caused by other, unoptimized parts of the checkout path holding connections too long. - Resolution and Learnings: Optimized other checkout processes, reducing database reads and transactions, and reconfigured MySQL's
innodb_thread_concurrency. The migration involved a safe "shadow mode" (dual-write to both Redis and MySQL) and a gradual rollout. Key takeaways included the importance of revisiting old architectural decisions given new database features (likeSKIP LOCKED), starting with minimal prototypes for direct observation, and instrumenting the entire system to find unexpected bottlenecks, emphasizing that system health and "safe neighbors" are as crucial as individual component speed.
This case study is a testament to the power of thorough engineering and deep database understanding, demonstrating that with careful design, a well-chosen relational database can elegantly solve complex scaling problems often attributed to specialized NoSQL systems. It also serves as a crucial reminder for engineers to look beyond superficial metrics and instrument their entire stack to uncover the true limiting factors in high-performance environments.