4 ms·

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

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.

soontimes 2mo ago
> Instead of having 1000 rows per shopSKU, why not just have one row per shopping cartSKU? At what point that row is inserted?
- bijowo1676 2mo ago
  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 inventory ledger (source of truth). but that system could be easily improved to reserve item when user Adds item to a cart, to prevent scenario when user adds item to a cart, goes through checkout, and after initiating payment gets "soldout error": 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.
  - soontimes 2mo ago
    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
    - bijowo1676 2mo ago
      there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: 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 < current_order.quantity assuming there are indexes on sku field in both, results in efficient index seek and agg over 2 tables
      - soontimes 2mo ago
        I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
        bijowo1676 2mo ago
        how does current design resolve concurrent actors fighting for the last item ? there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items. my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items. but the problem as formulated, imho, is not even correctly defined. Shopify incorrectly formulated the very problem they are trying to solve. Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout. the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay. So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY
atomicnumber3 2mo ago
I have never worked anywhere where describing how their system actually works would pass the company's own system design interview
- maxrev17 2mo ago
  Hahaha best comment on the whole thing! So true!
idoubtit 2mo ago
> not the best design [...] So those engineers at Shopify worked hard for months on a more performant system, but they missed the obvious structure? They chose a complex denormalization for no good reason? It may be true, but I think it's presumptuous to belittle their work when we have only partial information. My guess is that they had good reasons to think that the more obvious ways would not scale. And from reading your comments in this thread, I believe your structure would fail at their scale. A SQL query that uses 2 sub-queries with "group by" is probably too heavy. From the post, at peaks there would be millions of active shopping carts. BTW, I suspect most orders are just for 1 or 2 of each item, so the denormalization is not as heavy as it seems.
- bijowo1676 2mo ago
  i also work in big tech and know that a lot of bullshit design creeps into system design and prod, because everyone is overworked, overstressed, wants to just get things done for the quarterly performance review as to not get shitcanned with severance re concurrency, it is not a big issue at all. stock exchanges deal with HFT traders and can easily deal with concurrency of orders. Same can be implemented with shopify, but I doubt they face the same level of concurrency as stock exchange anywhere near
  - soontimes 2mo ago
    > re concurrency, it is not a big issue at all I would really appreciate it if you could write this up as an article. It would be an extremely interesting and valuable read
    - bijowo1676 2mo ago
      Martin Fowler's overview of LMAX Disruptor is one of the best reads on this subject re high-load system design https://martinfowler.com/articles/lmax.html https://martinfowler.com/articles/lmax.html
      - soontimes 2mo ago
raverbashing 2mo ago
And that's why these interviews can be stupid, you can mention the real solution and interviewers might reject because it's not the textbook solution But the real world is different
matwood 2mo ago
Others are almost never as dumb as you hoped, and you’re rarely ever as smart as you think.
zer00eyz 2mo ago
Your mental model here is mapping too close to an actual cart in a retail, at a in person, setting. The assumption that a SKU maps 1 to 1 to a cart item is flawed. If the first item in the cart is a bundle of SKU-A and SKU-B, the second item is a bundle of SKU-A and SKU-C and the third item is 5xSKU-B where do you do you keep the re-agregation of the SKU-X's to track them? This is without accounting for item location in the reservation - and rules that may apply around that. You haven't even gotten to the part where different customers will have different rules around shipping from different locations - because that can eat into margins. You're also making a bunch of other assumptions around transaction flow and where carts are actually stored (and how they get converted to an invoice, with payment attached) that likely do not hold true. Could you do it more like what you're sugesting -- maybe -- but only in a single tenant system.
- bijowo1676 2mo ago
  Modeling after physical shopping process is actually the proper way to design a eshop system. Imagine you are at Walmart store and go to checkout stage, you would have to pick item from the shelf and take it from availability for other shoppers, before you pay for the item. What shopify did, is customer enters the store, heads straight to checkout and retail workers races back to shelves to pick up items for client. Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item. Re SKU storage, you will have multiple row entries per SKU, if I order two items, there will be two rows corresponding to the items in your Purchase order. The sum() aggregation check will run across all active orders per sku Re single tenant: shopify creates 1000 rows per SKU per shop(tenant!!). As long as tenant is on the same DB you can run it, just add shop_id to the group by field
  - zer00eyz 2mo ago
    If Walmart (in person) behaved like Walmart (on line) the isles would be so littered with half full carts and items that others could not buy you would be unable to move in the store. https://baymard.com/lists/cart-abandonment-rate https://baymard.com/lists/cart-abandonment-rate 70 percent of carts are abandoned. You dont want your inventory sitting in carts, when other people want to buy it. It only comes off the shelf (and gets put in a box) when you have money in your hand. There is an entire ecosystem around Shopify to reach out to abandon cart holders and attempt to convert them: https://apps.shopify.com/categories/marketing-and-conversion-marketing-abandoned-cart https://apps.shopify.com/categories/marketing-and-conversion... > Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item. This is better than A) taking their money and then telling them you dont have it. B) Them not being able to buy it because someone has it in their cart and is NEVER going to check out with it.

soontimes 2mo ago

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

bijowo1676 2mo ago
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 inventory ledger (source of truth). but that system could be easily improved to reserve item when user Adds item to a cart, to prevent scenario when user adds item to a cart, goes through checkout, and after initiating payment gets "soldout error": 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.
- soontimes 2mo ago
  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
  - bijowo1676 2mo ago
    there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: 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 < current_order.quantity assuming there are indexes on sku field in both, results in efficient index seek and agg over 2 tables
    - soontimes 2mo ago
      I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
      - bijowo1676 2mo ago
        how does current design resolve concurrent actors fighting for the last item ? there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items. my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items. but the problem as formulated, imho, is not even correctly defined. Shopify incorrectly formulated the very problem they are trying to solve. Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout. the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay. So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY

atomicnumber3 2mo ago

I have never worked anywhere where describing how their system actually works would pass the company's own system design interview

maxrev17 2mo ago
Hahaha best comment on the whole thing! So true!

idoubtit 2mo ago

> not the best design [...] So those engineers at Shopify worked hard for months on a more performant system, but they missed the obvious structure? They chose a complex denormalization for no good reason? It may be true, but I think it's presumptuous to belittle their work when we have only partial information. My guess is that they had good reasons to think that the more obvious ways would not scale. And from reading your comments in this thread, I believe your structure would fail at their scale. A SQL query that uses 2 sub-queries with "group by" is probably too heavy. From the post, at peaks there would be millions of active shopping carts. BTW, I suspect most orders are just for 1 or 2 of each item, so the denormalization is not as heavy as it seems.

bijowo1676 2mo ago
i also work in big tech and know that a lot of bullshit design creeps into system design and prod, because everyone is overworked, overstressed, wants to just get things done for the quarterly performance review as to not get shitcanned with severance re concurrency, it is not a big issue at all. stock exchanges deal with HFT traders and can easily deal with concurrency of orders. Same can be implemented with shopify, but I doubt they face the same level of concurrency as stock exchange anywhere near
- soontimes 2mo ago
  > re concurrency, it is not a big issue at all I would really appreciate it if you could write this up as an article. It would be an extremely interesting and valuable read
  - bijowo1676 2mo ago
    Martin Fowler's overview of LMAX Disruptor is one of the best reads on this subject re high-load system design https://martinfowler.com/articles/lmax.html https://martinfowler.com/articles/lmax.html
    - soontimes 2mo ago

raverbashing 2mo ago

And that's why these interviews can be stupid, you can mention the real solution and interviewers might reject because it's not the textbook solution But the real world is different

matwood 2mo ago

Others are almost never as dumb as you hoped, and you’re rarely ever as smart as you think.

zer00eyz 2mo ago

Your mental model here is mapping too close to an actual cart in a retail, at a in person, setting. The assumption that a SKU maps 1 to 1 to a cart item is flawed. If the first item in the cart is a bundle of SKU-A and SKU-B, the second item is a bundle of SKU-A and SKU-C and the third item is 5xSKU-B where do you do you keep the re-agregation of the SKU-X's to track them? This is without accounting for item location in the reservation - and rules that may apply around that. You haven't even gotten to the part where different customers will have different rules around shipping from different locations - because that can eat into margins. You're also making a bunch of other assumptions around transaction flow and where carts are actually stored (and how they get converted to an invoice, with payment attached) that likely do not hold true. Could you do it more like what you're sugesting -- maybe -- but only in a single tenant system.

bijowo1676 2mo ago
Modeling after physical shopping process is actually the proper way to design a eshop system. Imagine you are at Walmart store and go to checkout stage, you would have to pick item from the shelf and take it from availability for other shoppers, before you pay for the item. What shopify did, is customer enters the store, heads straight to checkout and retail workers races back to shelves to pick up items for client. Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item. Re SKU storage, you will have multiple row entries per SKU, if I order two items, there will be two rows corresponding to the items in your Purchase order. The sum() aggregation check will run across all active orders per sku Re single tenant: shopify creates 1000 rows per SKU per shop(tenant!!). As long as tenant is on the same DB you can run it, just add shop_id to the group by field
- zer00eyz 2mo ago
  If Walmart (in person) behaved like Walmart (on line) the isles would be so littered with half full carts and items that others could not buy you would be unable to move in the store. https://baymard.com/lists/cart-abandonment-rate https://baymard.com/lists/cart-abandonment-rate 70 percent of carts are abandoned. You dont want your inventory sitting in carts, when other people want to buy it. It only comes off the shelf (and gets put in a box) when you have money in your hand. There is an entire ecosystem around Shopify to reach out to abandon cart holders and attempt to convert them: https://apps.shopify.com/categories/marketing-and-conversion-marketing-abandoned-cart https://apps.shopify.com/categories/marketing-and-conversion... > Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item. This is better than A) taking their money and then telling them you dont have it. B) Them not being able to buy it because someone has it in their cart and is NEVER going to check out with it.