Shopify’s founder and their coo both fund far-right extremism, and its founder thinks only rich people should be able to vote. But anyway, they switched databases.
so this is interesting to me, im in retail i work closely with platforms ive used shopify ive used magento ive used smaller players ive helped implement various pieces of all of them.
and i was excited to get some insight, then i realized that this whole thing was written by AI and im going to guess the idea and implementation were probably very AI driven.
> The solution: SKIP LOCKED
> Core idea: one row per unit, bounded by design
cool, thanks claude.
Now I'm wondering what the engineering culture is even like at shopify.
Here's the thing. I like databases, I think there's a lot of shit in this space that went and smoked a shit ton their own good stuff to come up with these pure event driven designs that lock you into event workflows with no isolation and remove the ability to do broader bulk-functions.. and then do something even stupider and say "all you need for the interface is graphql" and such service/platform doesn't give you any other way to reconcile or do reporting for your org you have to warehouse from graphql.. this is crap. So seeing a headline where shopify says they want to kinda get behind a unified database strat behind the scenes even if it's not necessarily customer facing, like that's good imo. SQL is many decades of relational algebra that makes insane computations acrossed vast sets of data pure magic and one of the best query dml interfaces of all time.
..however i dont even agree with the claim their making here that redis isnt the tech for a reservation system. redis when used correctly feels like an insanely awesome way to do a reservation system, i lurv redis for stuff like that.
I'm just gonna go forward with the assumption that current and future shopify updates are pure vibeslop. I already hate their data interfaces, but compared to other saas offerings i appreciate that they do have bulk-features.
> 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. Reservations consume rows from this pool; a replenishment process refills it from the inventory ledger.
Shouldn't I feel uncomfortable with such approach? It seems to create a backoff (pool) for lowering the chance of having a synchronization issue.
Yeah, I'd be uncomfortable with that approach. One of their key design goals was to minimize underselling, recognizing that it results in lost revenue. But if a seller has 5k inventory in one location, has a spike of 2k orders, but only 1k of the orders can successfully reserve inventory, then isn't that an argument that you lost the revenue of the 2nd 1k orders that error out before the replenishment process succeeds?
I'm skeptical of this approach. Sure, row contention means that you cannot have a database transaction per customer order attempting to decrease inventory count by 1 each time. But you can have a batch transaction whereby the transaction decreases inventory by 100 (thus touching the high-contention inventory row once) and credits each of 100 different customer cart database rows (which are not under heavy contention and can be on a different disk entirely). Attempted customer orders are submitted to a reservation system put in charge of assembling the batches. Customers wait some short period of time - say, 15 seconds - for the reservation attempt to be batched and to be notified that they successfully locked a reservation. Arguing that "slow reservations trigger throttling and a worse buyer experience", without an actual number for what counts as "slow" to serve as an SLO and as a design target, is a cop-out inviting over-engineering.
1. Deduct the reservation from the inventory when the user starts to order, but in the same txn also maintain a separate row for the in progress order flow.
2. If the order flow is aborted or times out have a background process that returns these to the inventory.
That seems simpler than this approach and involves no locking. Though their presented approach is also reasonable, there must be some reason not to choose a simpler flow. It is not that difficult to have a gc service that scales, but may be they didn't want to separate that.
Makes sense... if you are counting something in MySQL and now your counter is in Redis that's already strange
But I guess the point is that even in the MySQL scenario the 'reserved_quantities' is almost like a temporary table so either way is not the 'Real' inventory
They were really so proud of that AI image that they just had to tack it on at the end? Did nothing but make the blog post feel like cheap mass produced slop
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.
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.
This is absolutely fascinating. I enjoy real life stories like this. I went to a Node meetup in 2013 when Target had just switched to Node from PHP and it was a similar experience to see their metrics and hear their strategy.
At that revenue, why not make your own filesystem, database and index structure? There is no way mysql is the best possible software for this use case. Why stop innovation and hand everything over to ops?
Could not they shard the inventory table by shop_id? As I understand, the order includes only items from one store, so there is no need to keep all the stores in a single table.
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
Is it really the right choice to drop Redis and go back to a disk based relational database just to wrap transactions into a single unit?
Redis handles tens of thousands of concurrent connections in a single event loop, while MySQL uses one thread per connection. No matter how I look at it, that seems like a step backward.
Of course, performance isn't everything. And if performance isn't a problem, having everything in one place does make it easier to reason about. But I'm worried that under spike traffic, this approach might actually cause more problems.
I think putting a scheduling layer in front of the DB would be a better approach. The application server could handle concurrent connections and only write to MySQL when correctness is actually needed. That seems like a cheaper way to do it. but is it different for large-scale enterprise distributed systems?
I had a client and they weren't to bothered if they sold the last item twice, they would call the customer, apologise, and offer a discount on an alternative and keep the sale.
Mostly unrelated but shopify is incredibly annoying. They introduced this delivery tracking app called "shop" and it has become unavoidable when buying electronics from china. Recently looked at it with mitmproxy and it ships home more than gets shipped to me.
That and they helpfully share your email with a company if you add something to your cart. You don't even have to checkout or submit a form or anything. I can't tell you the number of spam emails I've gotten from companies because they auto add everything they get from shopify to their mailing list and then nag you about not checking out
I never spent much time with the whole NoSQL movement, it always seemed something out of people that don't get how to optimise SQL queries, or suffer from SQL allergy, only to reinvent it badly in custom languages.
There are many good reasons to use NoSQL instead of a "full SQL database". OTOH I can relate to your experience, I remember colleagues switching to MongoDB because they couldn't get good performance on the (MS) SQL database. They didn't know enough about proper (multi-column) indexes, use proper isolation levels, etc.
I never spent much time with the whole SQL movement and related. It always seemed something out of people that don't get how to decently use a filesystem, only to reinvent it badly
Why is it so hard for many people to accept, that this is a solution for a specific problem of shopify? They did not say that Redis is bad and MySql is good. They only a solve their problem.
Why even have a blog when you can't be arsed to write the posts. This is so obviously LLM-written. I have a positive view of Shopify engineers, but this kind of made a dent in that confidence.
Ok? Who cares. This was very interesting to me. IDGAF if AI was used (or not) to put the words in the article, the bits of information in those words is why I'm reading it. It's almost 2027, people need to accept that articles, art, videogames, code, everything will be more and more "AI touched" and you can't stop it.
I mean I share all the people's worry about AI taking jobs and a handful of AI (and AI adjacent) corps sucking out massive amounts of resources, but reading under every 2nd or 3rd article how it's AI is beginning to be more annoying than the AI writing itself.
It's like now every interesting or weird or exceptional or controversional video of anything has (what seem to be) 80-100 IQ people spamming "AI" in the comments instead of making interesting observations, posting their own anecdotes, cracking jokes etc.
I too have a positive view of Shopify engineers. But no longer Shopify management. It went from bottom up trust basted to top down AI first and for everything based. It’s very hard to express how deeply and completely the internal workings and culture of the company has changed. (Source: I am a long time former developer there.)
49 comments
[ 0.24 ms ] story [ 23.1 ms ] threadhttps://www.techwontsave.us/episode/340_shopifys_leaders_are...
and i was excited to get some insight, then i realized that this whole thing was written by AI and im going to guess the idea and implementation were probably very AI driven.
> The solution: SKIP LOCKED > Core idea: one row per unit, bounded by design
cool, thanks claude.
Now I'm wondering what the engineering culture is even like at shopify.
Here's the thing. I like databases, I think there's a lot of shit in this space that went and smoked a shit ton their own good stuff to come up with these pure event driven designs that lock you into event workflows with no isolation and remove the ability to do broader bulk-functions.. and then do something even stupider and say "all you need for the interface is graphql" and such service/platform doesn't give you any other way to reconcile or do reporting for your org you have to warehouse from graphql.. this is crap. So seeing a headline where shopify says they want to kinda get behind a unified database strat behind the scenes even if it's not necessarily customer facing, like that's good imo. SQL is many decades of relational algebra that makes insane computations acrossed vast sets of data pure magic and one of the best query dml interfaces of all time.
..however i dont even agree with the claim their making here that redis isnt the tech for a reservation system. redis when used correctly feels like an insanely awesome way to do a reservation system, i lurv redis for stuff like that.
I'm just gonna go forward with the assumption that current and future shopify updates are pure vibeslop. I already hate their data interfaces, but compared to other saas offerings i appreciate that they do have bulk-features.
> 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. Reservations consume rows from this pool; a replenishment process refills it from the inventory ledger.
Shouldn't I feel uncomfortable with such approach? It seems to create a backoff (pool) for lowering the chance of having a synchronization issue.
I'm skeptical of this approach. Sure, row contention means that you cannot have a database transaction per customer order attempting to decrease inventory count by 1 each time. But you can have a batch transaction whereby the transaction decreases inventory by 100 (thus touching the high-contention inventory row once) and credits each of 100 different customer cart database rows (which are not under heavy contention and can be on a different disk entirely). Attempted customer orders are submitted to a reservation system put in charge of assembling the batches. Customers wait some short period of time - say, 15 seconds - for the reservation attempt to be batched and to be notified that they successfully locked a reservation. Arguing that "slow reservations trigger throttling and a worse buyer experience", without an actual number for what counts as "slow" to serve as an SLO and as a design target, is a cop-out inviting over-engineering.
1. Deduct the reservation from the inventory when the user starts to order, but in the same txn also maintain a separate row for the in progress order flow. 2. If the order flow is aborted or times out have a background process that returns these to the inventory.
That seems simpler than this approach and involves no locking. Though their presented approach is also reasonable, there must be some reason not to choose a simpler flow. It is not that difficult to have a gc service that scales, but may be they didn't want to separate that.
But I guess the point is that even in the MySQL scenario the 'reserved_quantities' is almost like a temporary table so either way is not the 'Real' inventory
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.
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.
This section is badly written. For example, it refers to different table names than those previously introduced.
The slop shows. While I appreciate the post, I wonder why they didn't bother using an LLM in a way that would at least ensure internal consistency.
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
Redis handles tens of thousands of concurrent connections in a single event loop, while MySQL uses one thread per connection. No matter how I look at it, that seems like a step backward.
Of course, performance isn't everything. And if performance isn't a problem, having everything in one place does make it easier to reason about. But I'm worried that under spike traffic, this approach might actually cause more problems.
I think putting a scheduling layer in front of the DB would be a better approach. The application server could handle concurrent connections and only write to MySQL when correctness is actually needed. That seems like a cheaper way to do it. but is it different for large-scale enterprise distributed systems?
Interestingly, ChatGPT was able to parse and explain it to me. I got a lot out of its presentation.
Ok? Who cares. This was very interesting to me. IDGAF if AI was used (or not) to put the words in the article, the bits of information in those words is why I'm reading it. It's almost 2027, people need to accept that articles, art, videogames, code, everything will be more and more "AI touched" and you can't stop it.
I mean I share all the people's worry about AI taking jobs and a handful of AI (and AI adjacent) corps sucking out massive amounts of resources, but reading under every 2nd or 3rd article how it's AI is beginning to be more annoying than the AI writing itself.
It's like now every interesting or weird or exceptional or controversional video of anything has (what seem to be) 80-100 IQ people spamming "AI" in the comments instead of making interesting observations, posting their own anecdotes, cracking jokes etc.
https://share.gemini.google/WxEi6Satj7mY
Happy to share the gem with anybody who's interested in reading articles like this without having to muddle through all the AI bulk.