We replaced Redis with MySQL for inventory reservations and it scaled
shopify.engineering
shopify.engineering
So I'm sort of curious, did you not like the AI writing style or just rejecting AI in general? I myself am very conflicted, I think AI in its current form is the wrong tech at the wrong time yet I don't mind reading AI text and sort of mooch off of googles free tier.
Also I'm starting to notice lots of AI smearing, I read folks online describing someone elses contribution as "obviously AI" and I'm suspecting in some cases these could be false accusations.
I'm thinking about starting a blog, so when my AI gf writes posts, do I ask her to try to "not read like an AI"? Anybody try that? I'm gonna try that. Hehe for all you know I already did hehe
Obviously the blue pill.
I figured it was written by a marketing person on behalf of an engineer or something but after reading comments it was clearly the ai trying to dress up the technical info in some kinda low level appeal to everyone regardless of their technical prowess and that makes no sense to me since non technical people won't care about the article at all at best its basically an ad for people to sell on their platform but im not sure why it would be posted here instead of facebook or twitter or anywhere you might reach the kinda people who sell on shopify.
Instead they took a subject matter that would appeal to the hn audience and then dressed it up in a way that would annoy that same audience. If your going to bother using ai to make your ad pretty you could at least use the ai to target the correct audience in the way they would appreciate.
As software engineers we are constantly told that we need to heavily use these tools for our daily work. So is it surprising that software engineers use the same tools as writing aids? Using AI does not mean no human effort was involved.
Oversell happens because Shopify doesn't decrement inventory until payment is confirmed. And they apparently would rather die than change that invariant.
I could see a historical moment where Shopify, sans that invariant, massively fucked up inventory counts from uncompleted transactions... then had to unwind all that when a bunch failed to clear payment.
Ergo, now there's an invariant.
For example this subheading:
> "The real bottleneck: connections, not CPU"
That's two AI smells. AI likes to say vacuous punchy statements like "the final takeaway" or "here's the rub". Then contrastive parallelism "connections, not CPU". Contrastive parallelism should scarcely exist in technical writing, regardless of whether it's AI generated.
I wonder if the average reader is also getting annoyed like this or whether they just don’t care - especially seeing what seems to get upvoted on your run of the mill social media sites. They probably collectively shape things more than I do.
They can change it but it will be there.
I do like that I have a term for that “this, not that” phrasing that’s nails on the chalkboard for me after several years of reading slop.
Your worst complaint has nothing to do with the overall content or accuracy of the post.
Just style bashing.
- Our oversell protection was a gross hack that broke ACID.
- MySQL has a new feature that allows us to remove parts of the gross hack.
- Question: To what extent does the hack still exist?
Instead you get garbage like "The answer is often in the plumbing, not the engine." and "Crucially, this wasn't about making reservations fast. It was about making them safe neighbors."
But this crap is what Lütke wants, so they deliver.
Aren't the LLMs trained on a massive corpus of human written texts? If that stands, then they are doing what they were asked, kind of? I am also not a fan of the fluff words that Claude pollutes the context with, it's a bit much, I could live 30% less but it doesn't want to read its instructions in the .md
If the author wanted, they could ask their "AI" to write the article as a caveman, no? I, simply for fun, slop coded up a skill so that I can ask Claude to reply as Samual L. Jackson, or Kramer, etc.
I also wonder if there is an "AI language barrier", likely not in this particular article, but let's say there was a published article by a researcher whose native language isn't English. What happens with the translation? I realize I am asking naive things :)
As I understand it, there is (or was) a step where they ask people what's the 'better' response.
These linguistic forms sound good the first time you hear themz even if they are rare in real speech, so rapidly got trained in.
Now they distill off previous models, I imagine these weird linguistic forms are quite hard to get rid of.
And you know what? That's not annoying--that's brave.
I think you might be interested in reading in training data generation, training, and post training papers/articles. I think you might be surprised at how much intervention there is on some of these levels.
Is this post AI generated?
The training these LLMs got, was from endless human-slop, on sites like LinkedIn, and marketing copy, everywhere. In fact, don't be surprised, if we start learning from AI; reversing the process.
But I think that it's only a matter of time, before almost everything will be at least touched by AI.
I posted this, yesterday[0]. It wasn't a particularly popular comment, but I stand by it.
EDIT
I also read your linked comment and agree 100%.
Every time this claim is made I ask for a link to a pre-2022 blog or article that has all those AI tells.
I'm still waiting for it. I don't think I will ever receive it.
People on the Internet (especially here), seem to think that it's OK to make rude demands of complete strangers, and expect them to spend considerable time, meeting them.
I'd rather some rando think they "won the Internet," than spend a bunch of my time, working for free, just to satisfy them. I have better things to do with my time, and I don't really have a lot of investment in what complete strangers think of me.
Sometimes, the fox ain't worth the chase.
It's been asked about 30 times now. If it existed, someone would have posted it. Maybe you.
One of the things that I learned, about the time I lost my baby teeth, is that making rude demands of people, instead of working with them, is likely to result in exactly the opposite.
Chances are good, that you learned this, as well, which might explain the rude demands. They are unlikely to be met, so the matter stays open.
I mean, even the SOTA models couldn't pull up examples when asked, so it's no surprise that HN readers can't find any examples either.
They just don't exist...
Have a great day!
You dont ask people on the internet to provide you with some kinda proof because nobody can be bothered, you dont even ask them to prove you wrong because they cant be bothered. You have to confidently and boldly assert your false claim and that will enrage some poor autists somewhere into doing what you want.
The thing is that I LIKE writing code. It's fun.
I don't LIKE coding with an LLM. It isn't fun.
We can't go back to a place where the professional code is all hand written. But at the same time I'm sad and tired. The joy has been utterly sapped and I don't think I'll be able to do this as a career for much longer.
That may have something to do with the way that I use an LLM.
I use it as a “pair partner,” not as an agent.
Most of the code in my apps is mine, but I do ask the LLM to take care of some of the “overhead” tasks, such as minor utility functions.
In fact, I just got done, ripping out a bunch of code the LLM wrote, in my current app, and replacing it with my own work. It seems that I can’t really ask the LLM to do much beyond function level, without it going sideways.
I simply can’t understand people shipping entire, vibe-coded apps. If I let the LLM write my whole app, it would be hot garbage.
I think we need the moderation team to set up some policy. In which direction idk.
https://www.theguardian.com/technology/2026/jul/31/ai-labels...
Spotify's defense will then be that this post is obviously AI assisted garbage.
This is a really strange, clunky way of writing.
I would rather read the original thoughts of the engineer, grammar mistakes and stylistic imperfections included, than prompt generated AI slop.
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.
> 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.
You are just spending some more disk space to avoid synchronization issues. Denormalization for performance is a really common pattern, just that people do not start with it in the first place itself
Its called one row per shopping cart*SKU combo.
if two people order 100 and 500 items of the same SKU, respectively, the table should have only two rows: for order1 and order2. Not 600 rows.
The point of one row per item is that thousands of concurrent shoppers don’t need to block each other as they can each claim as many free rows as they need for themselves?
Also you might not understand the original problem. Imagine if 100 customers want to buy product A. One thread starts a transaction, searches for amount of product A and UPDATE's it and goes searching for other products. The database locks the row until the end of transaction and other 99 treads cannot continue until first transaction commits (they can read but cannot update the rows).
This is why they made a row per item. In this case, transaction 1 hopefully locks only several rows with items of product A. Transaction 2 instead of waiting for lock release skips them (due to SKIP LOCK) and locks several next rows. And so on.
Obviously you do not need to make a row per item - if the available amount is really large (10 000 items), you could have for example 100 rows having 100 items each. In this case each transaction locks the whole row (100 items) even if it wants to reserve just one item. The problem though is that now every row might have different amount of available items and you have to do more work to reserve the amount you want.
in shopify's design, it is a user who was the first to lock the row and have successful payment. Sounds good, but how often does it happen ? It's a rare and extreme case and they model their entire system after the rare even, and incur the overhead of 1000 rows per SKU per shop for all combination of SKU and shop_id for all the normal items that are not sold out in flash sale.
the same outcome could be achieved without locking and without creating 1000 rows:
1. keep track of all active carts at the checkout in a table
2. for each cart, record the timestamp in nanoseconds when user clicked Pay (but I would prefer timestamp of clicking Checkout)
3. that timestamp will decide who gets the last available item.
4. in a shopping cart, have explicit field for each SKU: inventory_reserved.
5. this decision mechanism is now explicit via global monotonic non-decreasing counter. It is no longer tied to payment processing gateway timeouts, not opaque and implicit mechanism relying on database internals and quirks of how DB engine locks and releases some placeholder rows.In case with shopify, they want to decide whether the user may place order or not, at the moment when the user clicks "Pay" or some other button. If the user cannot place an order, they are shown the error, if they can, the items are reserved and the user is redirected to the payment page. So payment is processed only after successful reservation, and reservation is made only if the user wants to pay. The similar system works for buying train tickets online in my country, for example.
In you case, when user A clicks a button, following happens (as I understand):
1 the server increments the counter
2 the server calculates available amount as (amount_in_stock - amount reserved by carts with time < counter)
3 if the amount is large enough, the server updates the "time" field for user's cart thus reserving the item
Imagine that at step 2 the user A sees that there is one item left. However before user A does step 3, another user B might reserve the item (complete all 3 steps), and proceed to the payment. Then user A then completes step 3 and proceeds to the payment too. Now we end up with both user A and B paying for the last remaining item which doesn't solve the stated problem. Shopify's solution doesn't have such issues.
This is a classical TOCTTOU situation. There were exploits against Linux kernel based on similar issues.
but again, my idea was:
1) do not use throwaway placeholder rows to imitate a single item
2) do not rely on db engine to decide which transaction gets committed first (which customer gets the last item)
3) model queue explicitly by introducing counter field that sorts and prioritizes customers' orders and decides which order gets fulfilled and which customers gets the last item> do not use throwaway placeholder rows to imitate a single item
The point of using multiple rows for one product is to distribute the locks.
Developers (and everyone else really) need to think about systems, with the system taking inputs like "number of locations", and producing outputs like "available inventory", and when the input parameters change outside of the designed scope, without the system itself changing, then you should expect things to break.
I'm a bit surprised about the scalability case against a simpler solution. This is not about Shopify's scale. We're talking about contention for a specific SKU of a specific seller at a specific warehouse location.
How many shopping carts are competing for a single SKU at the payment stage at peak hours? Can this really be too much lock contention for a single database row?
I realise Shopify engineers are neither stupid nor inexperienced. Hence my surprise. I would have liked to hear more about that specific problem.
Basically this is an incredibly rare case but a feature that they want to support.
Seems like a lot of places can do it for it not to be a solved problem.
The other thing that bothers me - why not real stable, but maybe „too old, medieval” solution with Redis as the main source of through - without any sync with SQL at all in terms of stock… it worked in my previous job with much higher traffic (1000s/s). Yup, we ended up with app-side sharding, but it was stupid-simple.
As they say in TFA they used Redis for the cart reservation but then you need to sync the two stores.
When you consider how many other (often poorly-crafted) DB queries and service calls are being performed while holding the row locked, yes, it can rapidly add up. If the entire pipeline takes 500 msec, congratulations, you can’t sell more than 2 units per second of that SKU. If the item is popular and being actively hyped, then yes, that can be a problem.
I don't get it, Wouldn't that still only be 50,000 rows, just divided across locations?
item1_location1
item1_location2
...
item2_location1
item2_location2
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.
They explicitly cover this in the article, saying they do reservation inline. It does increase latency for these orders, but it doesn’t result in an error
> 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).
They mention this as well, checkout batching increased implementation complexity.
> 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.
True. The article would’ve been better if they included such numbers. However the fact that they didn’t mention this doesn’t imply they haven’t done research. I haven’t found anything related to checkout specifically, however there are in general articles, indicating that increased latency correlates with revenue drop.
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.
1. Backgrounds process can back up
2. They need context of the user and need to switch context per user
3. What if they fail, you create some DLQ or another process to handle the failure
4. Who looks on those failure and how do they act
TLDR; there is always a cost
I don't think you really need that even. An indexed lookup is fast and you don't need to store a computed quantity generally.
I disagree with the other posters about the bg process, if you have any bg processing already you should be able to handle the few edge cases without too much trouble.
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.
At what point that row is inserted?
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).
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.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
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
It resolves with skip locked. Assuming we have only 1 item left. First query scans the buffer table, locks as many rows as needed (1 in our case), and moves rows to another table. Second query scans the table, finds no rows (even if first one hasn’t finished yet, the row is locked and ignored), checks if it can increase buffer, finds out that it’s fully sold and aborts. Db guarantees that you can’t oversold.
> 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.
I can’t evaluate whether it’s the same or not, because you still haven’t clarified when exactly you’re going to insert the row. In the article they’re inserting in the same transaction. Would you also do it in the transaction? Because if you’ll introduce a separate global mechanism to resolve conflicts, on a high level it would be the same as their approach with redis (you need to have 2 systems)
EDIT: wording
now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ?
shopify's design relies on DB to lock rows for transaction as a way to "decrement the counter" of available units. What I am suggesting, is you can just decrement counter by updating a single row, no need to lock 900 rows. Shopify moved from one extreme (single global variable in redis) to another extreme (1000 rows in db) and forgot about the middle ground.
The dance with moving rows per each item between tables is completely unnecessary, it's like counting numbers one by one in a for loop, when you can just substract number directly.
if I were to solve the problem, I would have solved it differently, at the Checkout state, before user clicks PAY. This removes the race condition at the user UI level, before any request lands in backend/db:
1. Have a table with active shopping carts (cart_id, cart_status, sku, quantity)
2. when cart_status changes to 'Checkout' run inventory availability check
3. If inventory availability check fails, show error to user (before he clicks Pay) and suggest replacement items.
4. If inventory availability succeeds, proceed to charge cc
more transactions can commit at the same time, but with one counter they would conflict (as it did in the Redis case)
they should use CRDT (and trying to model that with this 1000 row workspace, no?)
still, eventually at some point they need to do the math
In order to avoid races you need to insert reservation and decrement availability atomically. Your proposed approach is not atomic. For it to be atomic you will need to lock whole range, to make sure no new rows appeared between the points “check for availability” and “record reservation”. Actors will be effectively competing for the single aggregate row. This is the same as having a single inventory row with quantity field, which they rejected in the beginning of the article
> now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ?
In the proposed schema nobody is waiting for these locks, they’re skipped by concurrent queries. In your schema actors would have to wait before they can insert without breaking invariants.
Still I think their solution is a bit weird. I'd want to commit the reservation transaction with inventory decrement along with a payment key and then use a different transaction to drop the reservation when the transaction completes. If the transaction does not complete in a timely manner you probably need to query external systems anyway to resolve whether the payment actually occurred or not.
They talk about lock contention in this case, but I also wonder about latch contention since these rows are adjacent. If it's a small transaction that's not interactive, does mysql resolve it with just the latches on the needed tables?
That’s a bold overconfident statement. Cart abandonment is real. People never clear their carts they just walk away
Shopify purposefully chooses to do it at payment time because doing it earlier results in lost sales as people “reserve” items and then walk away causing other to see out of stock and then also walk away
Whoever puts up the money first gets the item
That’s the design constraint they chose you can’t just say “their solution is wrong because they solved the wrong problem”. Each design is a different user experience and I think it’s safe to say they chose which experience they want consciously.
Ok, let's accept the design goal that whoever paid first wins. You can use the same metric (how many milliseconds ago did user click PAY) and impose a global monotonic non-decreasing counter to distribute the scarce inventory. This is how order matching engines work at stock exchanges with HFT orders (FIFO logic).
the goal is to know with 100% certainty, before sending payment request to payment processor, who will have item and who won't, and you dont need to move mountains of rows for that.
the payment processor should be just a binary answer: payment succeeded or not, but currently it combines Inventory availability check & payment processing, which is the root cause of confusion. For clarity it is better to make that stage of order processing an explicit separage stage, instead of coupling it with payment stage.
some stores split payment into two stages: Payment and Final order confirmation. at the Payment stage you can pre-authorize money at cc and do inventory availability, and at final confirmation you capture $$
https://docs.stripe.com/payments/place-a-hold-on-a-payment-m...
https://support.authorize.net/knowledgebase/Knowledgearticle...
Looking at your solution, if i understand it, is instead of decreasing the inventory count for each sku as orders are processed, you are comparing the current warehouse quantity against the sum of all carts to see if there's availbale quantity.
You'll have to also include the sum of all completed orders so far.
Honestly seems almost worse? Arn't you trading contention on a single counter (inventory) for a large read across all pending and completed orders? Even indexed you're ingesting a ton more data? And you'll still need a lock here as you have to ensure two orders do this check at the same time.
Naive design
- Single inventory row per warehouse sku - All orders compete on a lock for all inventory sku rows in their order to deduct/claim their items
Shopify design
- Unroll warehouse inventory to thousands of rows per sku - Order processing races to find sufficient unlocked rows for all items in order - If insufficient rows are found then orders block behind slower "restock" process that creates more rows
Your design
- Warehouse inventory row is static/read-only (restocking out of scope for now that's fine). - Order processing computes the sum of all completed orders to ensure there is sufficient quantity - This would have to be under a lock as well, otherwise two or more racing orders will think there is quantity left.
So sounds like in your solution, you still have a single point of contention for who is computing the sum of completed orders, and while holding that lock you are doing a sum of all completed orders for each sku in your order. That sounds... worse?
You reserve the product by creating an "active_cart" entry. Your solution has a problem, that when you run the check, it might say the product is available, but before you create an "active_cart" to reserve it from thread A, another thread B reserves it and you end up reserving a product that is not available anymore. You end up with SUM(active_cart.quantity) > inventory.available_units.
That is exactly why the database has locks - to prevent this situation. With locks, thread A decrements inventory.available_units and that row is locked until the end of transaction. Other threads (if they do SELECT FOR UPDATE instead of SELECT) cannot see the old, invalid value until thread A either commits and the value is updated or rollbacks. However, locks cause performance issues and that is why shopify uses the architecture from the article - instead of 100 users fighting for the lock on the same row with available amount, each user locks only rows with units they plan to buy.
Interestingly, MySQL docs has the documentation page with a similar case: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-lo...
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.
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
I would really appreciate it if you could write this up as an article. It would be an extremely interesting and valuable read
> LMAX Disruptor
If anything this article shows that concurrency is a big issue. It is such a big issue you have to write in-memory single threaded processor with custom journaling. If you have established workflows with MySQL and a team knowing how to work with it, throwing all that to do LMAX is not cost efficient. While there are domains where such approach is suitable and even required due to strict transaction ordering, Shopify case doesn’t look like one of them.
you have an open Sell 1 APPL for $100.0. Millions of other HFT orders rush to scalp your single order. How do you think exchange matches your Sell to HFT's Buy orders? which Buy order gets fulfilled first?
There's even this bit where they discover a remarkable trick:
> Each round trip to the database has a cost. For carts with multiple line items, we batch reservation queries using UNION ALL so we fetch all needed units in one round trip
Insights like that really don't read like senior level output, and of course, it's LLM output. I'm not sure it's presumptuous to question it.
What is there to disagree with? It's not making a statement or taking a stance, it's a technical writeup and I included a technical quote from the article which would be weird whether or not it was AI written.
It's not post edited by AI, it's written whole cloth by AI. No human cared enough to write it. Possibly if it was better quality you could make the case that's ok, but I suspect that you didn't read the article at all.
But the real world is different
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.
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
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...
> 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.
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?
No persistence means the data gets lost if machine shuts down or process crashes. Furthermore, after restart you will need to regenerate the data which can take time. That's why Redis is a cache and not a database. You can fix the persistence issue (Redis can write WAL log, don't remember if it does fsync or not), but then Redis won't be able to handle those thousands of concurrent connections.
Redis (and other NoSQL storages) don't have some magic architecture that gives them advantages over SQL databases. They just cut corners on ACID guarantees and skip fsync. Once you start doing fsync, your transaction throughput will drop to SQL database level.
Redis also doesn't have transactions which means every app error damages the data. You will spend engineer hours investigating and fixing the problems. Transactions save so much time and worries.
But I think using only MySQL is unnecessarily expensive, just to get single transaction tracking for bug tracing. So the article's argument seems to be:
'Use only MySQL as a solution to the distributed transaction consistency problem between two different storage systems, Redis and MySQL!'
But I think using Redis is much more elegant. It's easier to scale. I'd even argue that something like Saga would be a better approach. Of course, we might just have different opinions. But in my experience, reducing layers always ends up making things more complicated in the long run.
p.s. We have different views, but I do think some of your points are valid, so I upvoted your comment
RDB snapshots can cause multiple page faults due to use of fork() and CoW.
> It's easier to scale
The company in question manages online stores and they could easily scale by allocating a separate database for each store (sharding).
> But I think using Redis is much more elegant.
I cannot agree because I think using a single database for all the data is more elegant, than multiple different databases and there are less problems to deal with. I dislike microservice-style architecture strongly and believe it is mostly good for wasting company's money.
> 'Use only MySQL as a solution to the distributed transaction consistency problem between two different storage systems, Redis and MySQL!'
I read it as "do not create unnecessary work by using a single database".
Still, I respect your perspective and your experience. We clearly have different values, but I think you have a mature engineering mindset. Ultimately, I think only real measurements can settle this. Have a great day.
Of course one could optimize this - for example, while fsync is being executed, we could accept the queries from other clients and execute them, and once previous flush finishes, flush multiple transactions at once. However, I am not sure if Redis can do this due to being single-thread.
And obviously maybe there are problems with drivers, or with my consumer-level SSD and maybe "professional" SSDs can do more flushes per second.
Writing the code took less than a minute. I often do microbenchmarks now because it is so easy.
Redis AOF isn't one fsync per record—Redis can use group commit to share fsync across multiple commands. In other words, that could mean a difference of tens of times in your benchmark. I see it differently.
fio --rw=write --ioengine=sync --fdatasync=1 --directory=test-data --size=220m --bs=2300 --name=mytest
more often then not, fsync/fdatasync is overlooked by programmers
Furthermore, the RDB snapshot mechanism (when Redis forks and forked process writes the snapshot) can double memory consumption and cause thousands of page faults in Redis process if there are many writes happening.
The docs contains corresponding warnings. "Cloud backups" are marketing terms and not ACID guarantees.
As one more disadvantage, Redis has no SQL and you cannot easily view the data.
As for transactions, indeed it seems to have them, but their execution is serialized, i.e. when MySQL can prepare 100 transactions in parallel, Redis will execute them sequentially.
[1] https://redis.io/docs/latest/operate/oss_and_stack/managemen...
Perhaps each shopping cart would have its own workflow, and the inventory item would have one as well. Then, whenever a customer put an item in their cart, their cart workflow would send a signal to the inventory item workflow and wait for the response. The inventory item workflow would maintain a ledger controlling to which cart each unit goes, and it could batch the writes to this table. This way, even if 100k customers try to purchase the same item in the same second, it should handle the load.
After the batch is written to the ledger, the inventory item workflow would reply signals to each cart workflow confirming that the reservation was completed. The end-to-end latency from the consumer point of view would be a fraction of a second, without needing the 1000-row hot-inventory heuristic.
It's web-scale.
gun = smoking
insight = key
gap = closed
summary = executived
One would think semantic density would win out in training.
It’s also annoying as a human because Claude et al rate their own writing very highly, putting human<>LLM interactions at a disadvantage to human->LLM<>LLM interactions.
Close out previous paragraph. Segue to completely different topic.
How else are you supposed to go on a tangent?
Both sucks in French but at least chatgpt prose is readable while Claude is awful.
Training could not address semantic density unless it was for the very specific pattern you need for this very specific article.
I guess the people generating the article don’t really care if is correct or easy to read, as long as it gets indexed in Google and linked by prominent sites like HN it has done its job (seo).
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
> Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
This requires a row per item unit, doesn’t it? If you have 50k units you’ll have to track status of every item, meaning 50k rows. They also mention this as a rationale to use at most 1k rows, and treat it as a buffer.
0: https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-version...
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
Using off the shelf software means you mostly design how to plumb things together and how to make them correct , safe and scalable.
The things you mention, on the other hand, carry the same requirements but are also much complex to develop AND to maintain.
https://www.techwontsave.us/episode/340_shopifys_leaders_are...
It's famously a fully remote company.
For instance, retarded is now considered a slur. If OP is saying "my director said the R word," then I would question whether OP spared the gory details out of concern for polite company or if they're being oblique in service of their point.
And if managers at Shopify are dropping N-bombs, I want to know that too.
https://www.ctvnews.ca/business/article/shopify-ceo-draws-cr...
So like, idk, people over 60yrs old voting against climate action? Or maybe people making over $500k/year voting against a social safety net?
Those seem like far more consequential groups exerting influence than the very poor (who as a demographic don’t actually vote much anyway).
But anyway, whatever group you’re talking about disenfranchising, the fact that you’re talking about disenfranchising anybody makes you an enemy of democracy.
But here's another link if you need something that includes the phrase "far right": https://pressprogress.ca/shopify-executives-right-wing-media...
Anyway, if you think a country having a low gdp per capita is how you measure if it should suspend voting rights for disabled people and stay at home parents, then I suspect you're not actually reading any of this.
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.
https://www.shopify.com/careers/disciplines/engineering-data
Pair programming and forced AI, that sounds like absolute hell. Glorification of Lütke who didn't do that much in open source and now props up his ego by thinking "AI can do it so it wasn't all that difficult all along."
I don't think he ever worked on complex parts of Ruby. The people he now oppresses did.
Ruby should note that this company is actively repelling people from using the language. I really want to switch, but then I see Claude contributions in Ruby core, the influence of this slop company, and think it isn't worth it.
Oh, and they bought DHH in 2024 for his 180° turnaround on AI. He is now an AI booster, so Rails is out of the question as well.
All the biggest proponents of AI seem to be fascists.
Weird.
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.
I really just disagreed with the assessment that redis is not good enough for the job for a reservation system. I use sql database all the time, I prefer them. But I'm seeing a claude written article here that seems to heel turn on a proven technology, it would at most be insightful if there was human content in here from actual engineers at shopify who want to vouch for and explain the challenges they were up against with redis rather than just expect me to take claudes word for it. Anyone who's been dabbling with AI knows damn well that you can convince claude to write up a dissertation on any hill you want to die on.
In the end, they found a way to make it all work on MySQL, which solved some of the inherent complexity of using multiple databases side by side (but again this isn’t a criticism of Redis, the same problem would exist for any pair of databases).
For example if it takes 20ms to write a batch to the WAL then if you do 5 updates to the same row then that is a minimum of 100ms. But without waiting on locks if you can batch all the WAL writes together then this could be just 20ms.
I don’t think holding locks while waiting for WAL is strictly necessary. There is definitely some anomalies that can happen if you don’t wait for WAL to be durable because transactions that don’t write WAL can observe non-durable writes in some situations. So for example conditional updates that don’t perform work. But I assume this can be fixed by making these wait on the commit for dependent transactions to become durable if they are empty. There is also the problem of failing writes that reveal information about non-durable writes which is more tricky. For example you try to insert into a unique index and it fails, but the duplicate was due to a non-durable write that is lost.
Pure reads should be fine when using MVCC because you just show the latest durable version of the DB. I know some other replication systems will run all transactions including reads through the WAL/replicated log in order to not have anomalies.
Now you only need MySQL expertise and maintenance rather than Redis and MySQL