We replaced Redis with MySQL for inventory reservations and it scaled

Post by bijowo1676 » Sun, Aug 09, 2026, 3:54 AM UTC

manbash wrote: post #8

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

Post by treis » Sun, Aug 09, 2026, 3:58 AM UTC

isignal wrote: post #36

firasd wrote: post #29

Earlier quoted context omitted.

My understanding is: your proposal is not very different from what Shopify is doing except they are tracking 'reserved units' (one per row) and you are proposing tracking 'orders' as the temporary state to then reconcile back with inventory quantities.

Yes, at a high level. It doesn't rely on skip locked, which is not cheap at DB level. DB has to still typically run query and keep going until it finds an unlocked item. Deducting and checking inventory counts are simpler ops inside the DB.

Post by bijowo1676 » Sun, Aug 09, 2026, 4:12 AM UTC

soontimes wrote: post #54

bijowo1676 wrote: post #46

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 10…

> Instead of having 1000 rows per shop SKU, why not just have one row per shopping cart SKU? At what point that row is inserted?

  What is oversell protection?
  Reserve: When payment starts, we mark items as reserved (a short hold, e.g. several minutes).
  Claim: When payment succeeds, we permanently deduct quantity from the inventory ledger (source of truth).

  1. Let user add item to a cart by default (happy path)
  2. Initiate async check in the background for SKU and quantity
  2a. The check sums up rows for all SKUs and compares to Inventory table (very cheap check since its done to only active shopping carts)
  3. After few seconds the check comes back, and we let user know that item is soldout, before/the moment user goes to Checkout.

Post by mrloopex » Sun, Aug 09, 2026, 4:20 AM UTC

Post by bijowo1676 » Sun, Aug 09, 2026, 4:24 AM UTC

soontimes wrote: post #56

bijowo1676 wrote: post #55

Earlier quoted context omitted.

per my reading of the article, the protection is only needed for a few seconds, while payment is being processed by the payment system. so the row is inserted when Payment is initiated, and row is deleted when Payment succeeds What is oversell protection? Reserve: When payment starts, we mark items as reserved (a short hold, e.g. several minutes). Claim: When payment succeeds, we permanently deduct quantity from the…

Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have contention on that item. Them having a 1k buffer allows not to take a lock on a single row every time, and only do it when buffer is empty

  with current_order as (
    select $SKU1, $q2 as quantity
    union
    select $SKU2, $q2 as quantity
  ),
  with carts as (
    select sku, sum(quantity) as reserved
    from active_carts
    group by sku
  ),
  with warehouse as (
    select sku, available_units
    from inventory
    group by sku
  )
  select * from current_order
  inner join carts using (sku)
  inner join warehouse using (sku)
  where warehouse.available_units - carts.reserved