<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Pavitar Kumar</title>
    <description>The latest articles on DEV Community by Pavitar Kumar (@kick933).</description>
    <link>https://dev.to/kick933</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F743321%2F7e3d3e18-258e-4fc5-88b1-d7d9b01c9604.png</url>
      <title>DEV Community: Pavitar Kumar</title>
      <link>https://dev.to/kick933</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/kick933"/>
    <language>en</language>
    <item>
      <title>Why Shopify Moved Inventory from Redis to MySQL? : Part 1: the problem and the schema</title>
      <dc:creator>Pavitar Kumar</dc:creator>
      <pubDate>Sun, 22 Nov 2026 10:01:28 +0000</pubDate>
      <link>https://dev.to/kick933/deep-dive-into-shopify-using-mysql-for-inventory-part-1-4a55</link>
      <guid>https://dev.to/kick933/deep-dive-into-shopify-using-mysql-for-inventory-part-1-4a55</guid>
      <description>&lt;h2&gt;
  
  
  Redis is faster than MySQL. So why would Shopify move inventory reservations to MySQL?
&lt;/h2&gt;

&lt;p&gt;Shopify Engineering Blog: &lt;a href="https://shopify.engineering/scaling-inventory-reservations" rel="noopener noreferrer"&gt;https://shopify.engineering/scaling-inventory-reservations&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Read it first.&lt;/p&gt;

&lt;p&gt;Short answer: Redis and MySQL can't be updated together atomically, so a crash between the two leaves them out of sync.&lt;/p&gt;

&lt;h2&gt;
  
  
  Problem
&lt;/h2&gt;

&lt;p&gt;Let's first list down the problem of Inventory reservation, which Shopify was trying to do better on.&lt;/p&gt;

&lt;p&gt;All E-commerce apps need to reserve inventory when you make a purchase. Normal purchase flow is Customer adding some X SKUs (items) in varying quantity to my cart. Customer clicks &lt;code&gt;Pay/Checkout&lt;/code&gt; and these items are reserved unless I complete the purchase or the transaction times out (let's say 15 minutes after I clicked buy now).&lt;/p&gt;

&lt;p&gt;This ain't the only way though. There are other ways to achieve this, go explore some books or ask your favourite AI agent.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Classic Pattern
&lt;/h2&gt;

&lt;p&gt;Before this migration, Shopify used the classic pattern of Redis as the quantity counter and SQL (mySQL here) database as ultimate source of truth. &lt;/p&gt;

&lt;p&gt;Reserving some items means you run a LUA script. Restore if the transaction didn't finish.&lt;/p&gt;

&lt;p&gt;One main issue with this is quite classic. How do you make the operation atomic? &lt;/p&gt;

&lt;p&gt;Let's try two approaches: &lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;When user checkout, Redis counter is permanently reduced. A transactionId is stored in database and a separate job keeps checking recent transactionId to restore related quantity. What if server died after decrementing inventory but transaction is not created.&lt;/li&gt;
&lt;li&gt;In reverse order, you created a transactionId in MySQL and your server died. Now, the background job may add more inventory than you have back to redis, allowing payment for a item that you cannot fulfill.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;See. It is not as easy as those Youtube videos seem to imply. &lt;/p&gt;

&lt;p&gt;But why? My single server eCommerce app has no such issues when I started to learn (way before AI, lucky me)&lt;/p&gt;

&lt;h2&gt;
  
  
  So, What to do?
&lt;/h2&gt;

&lt;p&gt;I mean, you can always get an ice-cream and forget that issues happen. We will care, when they happen. Let's just ship. &lt;/p&gt;

&lt;p&gt;Ok, seriously. What if mySQL could be used? But everyone knows that single row for each SKU doesn't scale. &lt;/p&gt;

&lt;p&gt;Why it doesn't scale? &lt;/p&gt;

&lt;p&gt;Each row need to be locked. Suppose 100 people simultaneously want Wired Headphones (I want one). The query would be something like:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="no"&gt;START&lt;/span&gt; &lt;span class="k"&gt;TRANSACTION&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Reduce the product quantity&lt;/span&gt;
&lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="n"&gt;product_table&lt;/span&gt;
&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;quantity&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;quantity&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;product_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="no"&gt;PRODUCT_ID&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt;
&lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;quantity&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Insert this quantity to some other table, likely an outbox table&lt;/span&gt;


&lt;span class="k"&gt;COMMIT&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This query looks innocent, but it isn't. Any UPDATE takes an exclusive lock on the rows it touches and holds it until the transaction commits. So if 100 people want the same SKU, they line up and update it one at a time. And if a purchase touches several SKUs, each one adds more waiting, plus a risk of deadlocks, where two transactions each hold a lock the other needs.&lt;/p&gt;

&lt;p&gt;(InnoDB's default isolation level, REPEATABLE READ, can lock even wider ranges of an index. We'll get into that in Part 2.)&lt;/p&gt;

&lt;p&gt;Sounds boring, right? We will see shortly.&lt;/p&gt;

&lt;h2&gt;
  
  
  First Mission: Transaction Contention
&lt;/h2&gt;

&lt;p&gt;Shopify Engineers did something clever. It is so obvious but you won't think of it unless you (not your AI model 😝) have deep knowledge of how Databases work under the hood (read some books, boring things). Shopify split the the SKU quantity into individual rows. &lt;/p&gt;

&lt;p&gt;Why does that help? Each unit is its own row with its own lock, so concurrent buyers stop queueing on a single row.&lt;/p&gt;

&lt;p&gt;But this is not without problems. Listing some obvious problems.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Table/Index Bloat&lt;/strong&gt; : Large keys in Index/Table with repeated values will take more storage. More rows means more data to be stored in Index as well as the table.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Page Buffers Overflow&lt;/strong&gt; : Database stores data in Pages (Fixed size chunks). Frequently accessed pages get stored in Page Cache (In RAM basically). This allows faster access. Larger the table/index becomes, harder it becomes to keep all the hot pages of the table/index in memory.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Before we get to how they solved some of these, let's understand their Database Schema for the product table.&lt;/p&gt;

&lt;p&gt;They have &lt;code&gt;shop_id&lt;/code&gt; , &lt;code&gt;inventory_item_id&lt;/code&gt; and &lt;code&gt;inventory_group_id&lt;/code&gt; as main fields mentioned.&lt;/p&gt;

&lt;p&gt;This is inventory management system. So, you need to have the following queries fast:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;List all products of a shop (list of inventory_item_id and details).&lt;/li&gt;
&lt;li&gt;Check if a item is out of stock.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;There is a obvious hierarchy here. First thing you will search with is &lt;code&gt;shop_id&lt;/code&gt; and ask for a specific &lt;code&gt;inventory_item_id&lt;/code&gt;. I don't have any clue about usage of &lt;code&gt;inventory_group_id&lt;/code&gt;, so I will just keep it at last.&lt;/p&gt;

&lt;p&gt;This is where Composite Primary Key fits in. It is created with &lt;code&gt;shop_id&lt;/code&gt; , &lt;code&gt;inventory_item_id&lt;/code&gt; and &lt;code&gt;inventory_group_id&lt;/code&gt; in the same order + ID field (why? Keep reading)&lt;/p&gt;

&lt;h2&gt;
  
  
  Side Quest: Clustered and Non-Clustered Indexes
&lt;/h2&gt;

&lt;p&gt;Let me speed this up a bit. All the rows need to have a natural order for database to store. If you don't provide a Primary key, it creates it's own internal Primary Key as it is needed to store the data in some order.&lt;/p&gt;

&lt;p&gt;This Primary Key is used to create Clustered Index. Clustered Index defines the order in which rows are ordered when stored (physically in the B-tree Pages).&lt;/p&gt;

&lt;p&gt;A non-clustered Index is just index on a specific column with pointers to Clustered Index. Thus, row insertion, deletion or update need to update the clustered index as well as the non-clustered indexes. (See, this is why indexes make write slower databases)&lt;/p&gt;

&lt;p&gt;Shopify team mentions that they tried using UUID before settling for the above Composite Primary Key.  &lt;/p&gt;

&lt;p&gt;Why wouldn't UUID work? Well, there are quite a few issues:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Append order is not predictable.&lt;/li&gt;
&lt;li&gt;Each item quantity may get scattered across multiple pages far away.&lt;/li&gt;
&lt;li&gt;Two locks needed. Why? Well, you lock the non-clustered index (&lt;code&gt;shop_id&lt;/code&gt; , &lt;code&gt;inventory_item_id&lt;/code&gt; and &lt;code&gt;inventory_group_id&lt;/code&gt;) and the clustered index (UUID)&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;So, the stage is set for a composite index. But why would the last part of composite key be &lt;code&gt;id&lt;/code&gt;?&lt;br&gt;
To satisfy the Uniqueness constraints of Clustered index. And to count the quantities, you cannot have more than a specific number of rows per item. Else, think about 10k rows for a low value high moving item. it will fill up so many pages.&lt;/p&gt;

&lt;h2&gt;
  
  
  Too much info. Summarise it
&lt;/h2&gt;

&lt;p&gt;I see. Too addicted to those summarisation by AI. Let's go through what we know:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Indexes are not always good. Know when not to use one. They speed up reads at the cost of writes and storage. &lt;/li&gt;
&lt;li&gt;Clustered Indexes and non-clustered Indexes. Time based IDs are better as primary key. (think UUIDv7, not random UUID)&lt;/li&gt;
&lt;li&gt;Composite Primary key need to be ordered properly. If &lt;code&gt;shop_id&lt;/code&gt; is leftmost part, index can be used as for shopId as well. If not, good luck creating new index to search by &lt;code&gt;shop_id&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Transaction Isolation Levels are great knobs in SQL databases. Know when to turn one.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Come Back for Part 2
&lt;/h2&gt;

&lt;p&gt;Too much info to digest, right? Let me give you a break and some reference resources to go through, before we get to the 2nd Part.&lt;/p&gt;

&lt;p&gt;My favorite is &lt;code&gt;Database Internals&lt;/code&gt; by &lt;code&gt;Alex Petrov&lt;/code&gt;. Read the first section. Take your time.&lt;/p&gt;

&lt;p&gt;If curious, read upto transaction management. You will understand deeply about how row locking work.&lt;/p&gt;

&lt;p&gt;As for how InnoDB locking work, Check out &lt;a href="https://jahfer.com/posts/innodb-locks/" rel="noopener noreferrer"&gt;this Blog&lt;/a&gt; (Mentioned by Shopify Blog)&lt;/p&gt;

&lt;h3&gt;
  
  
  Hi
&lt;/h3&gt;

&lt;p&gt;I am Pavitar. A Software Engineer with knack for understanding and owning systems. You will see me larping about Distributed systems in my LeetCode t-shirt, bragging about my &lt;a href="https://leetcode.com/u/the_hidden_leetcoder/" rel="noopener noreferrer"&gt;Leetcode&lt;/a&gt; achievements (not many yet), grinding on Codeforces and writing deep dives on Concurrency and Systems concepts. &lt;/p&gt;

&lt;p&gt;Feel free to ping me on  &lt;a href="https://www.linkedin.com/in/pavitrsheoran003/" rel="noopener noreferrer"&gt;LinkedIn&lt;/a&gt; if you are also intrigued by the intricacies of computer systems.&lt;/p&gt;

</description>
      <category>architecture</category>
      <category>database</category>
      <category>sql</category>
      <category>systemdesign</category>
    </item>
  </channel>
</rss>
