11 ms·
We replaced Redis with MySQL for inventory reservations and it scaled
- nurettin 1mo agoAt 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?
- znpy 1mo agoMost likely? Time. 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.
- kgeist 1mo agoThe social network VK internally uses highly specialized database engines per business domain. They don't use stock DBs. They have a DB engine for posts, a DB engine for likes, etc. They have a team of DB engineers. Their DB load was around 250 mln RPS 3 years ago. Stock DBs were harder to scale for them. I guess if you have immense highload, having a team of DB engineers can be cheaper because you can save a lot on servers. I reviewed their code. A DB engine's source code is pretty compact and simple (relatively speaking) because they deal with very specific domain entities, so they don't have to account for all the possible user query combinations that a general-purpose DB would have to support. It was mostly shards+binlog+snapshots+views in RAM. Considering that Telegram was founded by former VK engineers, I suspect they have something similar.
- REPLicated2 1mo ago> 3. Consistent lock ordering: avoiding deadlocks 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.
- eviks 1mo agoWhat way would that be?
- codedokode 1mo agoCould 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.
- soontimes 1mo agoThey never said they don’t shard it, however this doesn’t solve the problem they were facing. Even if they have a single store (therefore a single shard), the burst demand may be high for the item in that shop, which creates contention for “remaining item quantity” resource. Their solution spreads this contention across several 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.
- codedokode 1mo agoI now thought that "updating a row" might be more expensive than simply deleting because UPDATE is implemented as "mark row deleted" + "insert new version of a row" in a table which support multiple versions of a row (MVCC). So maybe using DELETE is actually faster - it just marks a row as "deleted in transaction X". Unless I forgot something.
- sgarland 1mo agoThat's how Postgres' (and perhaps others) MVCC works, yes. MySQL / InnoDB, however, updates tuples in-place [0], and uses the undo log to recreate older versions as needed. 0: https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-version...
- jdw64 1mo agoIs 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?
- codedokode 1mo agoRedis doesn't have transactions and persistence. 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.
- jdw64 1mo agoDoes Redis become that slow when you enable both AOF and RDB? Sure, there's a write cost, but it doesn't lose its ability to maintain tens of thousands of connections. Redis supports AOF and lets you choose the fsync policy. 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
- cloudie78 1mo ago[dead]
- arichard123 1mo agoI 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.
- sureglymop 1mo agoMostly 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.
- doublerabbit 1mo agoMood. It's not just over-seas. With a domestic courier they still use dark-ui to hide the tracker link via "shop".
- malfist 1mo agoThat 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
- sureglymop 1mo agoWow that's even worse! I use a catch all email and I recently even contacted a company I bought something from about potentially being compromised because I got a weird email. This here would explain what happened.
- welcomezhangjun 1mo ago[flagged]
- pjmlp 1mo agoI 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.
- szundi 1mo ago[dead]
- williebeek 1mo agoThere 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.
- _woland 1mo agoI 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
- progx 1mo agoWhy 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.
- gregoriol 1mo agoEven the good teams may make bad choices, that's why it is interesting to read their ideas and discuss them
- ramon156 1mo agoWhy 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.
- cowboylowrez 1mo agoI just wanted to drop a note that I almost never go into an article or blog wondering if its AI or not. I am trying to be a more picky reader and try to actually do something occasionally but I do browse database articles and this is a good one. So I wonder, am I becoming insensitive about AI writing? Am I being hypnotized into taking whatever color pill thats had a color representing a surrender to the AI "hive mind" whatever that is? 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
- ethbr1 1mo ago> whatever color pill thats had a color representing a surrender to the AI "hive mind" Obviously the blue pill.
- imthatsteve 1mo agoI honestly didnt notice it was ai until after i read the comments. I didnt really care for the article that much since it reminded me of a less knowledgeable person summarizing what a more knowledgeable person said. Like reading an article written by one of those game journalists who hasnt figured out how to use the game controls yet. 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.
- 21asHak 1mo agoThey are hiring with AI slop: https://www.shopify.com/careers/disciplines/engineering-data 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.
- pythonRon 1mo agoTobi Lütke also made some rather controversial statements lately, too, agreeing with a retired TD Bank CEO that more votes should be given to the rich. On the surface, this sounds awful, but what Eric Thor actually said was that the number of votes should be tied to the amount of income tax a person pays. Considering that (from what I've heard) billionaires pay no income taxes, I'd say it's not a bad idea. No tax: no vote.
- azuanrb 1mo agoPretty interesting read. One thing I’m curious about is the DB size trade off. Going from a quantity in Redis to one row per reservable unit seems like it could create a lot more rows, even with the 1,000 row cap per item/location.
- giovannibonetti 1mo agoI wonder how we could handle that in a simpler way with durable workflows (e.g. Temporal, Restante, DBOS) – which are similar to Erlang processes but with persistent disk storage. This could avoid the need to maintain the 1000 row inventory. 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.
- kandros 1mo agosimpler
- azuanrb 1mo agoDurable workflows are different, not necessarily simpler, imo. Unless the team is already familiar with them, I wouldn’t introduce one just for this. You also have to account for the infrastructure needed to run and manage the durable workflow itself, which adds complexity.
- xdotcommer 1mo ago[flagged]
- jhhh 1mo agoMy main takeaway from this post is that in 2026 we haven't developed enough technology to scalably and durably handle concurrently decrementing a single number. This has caused multiple organizations to develop database hacks (the multiple rows) or complex architectural solutions (redis) which destroy the atomicity of the process.
- winrid 1mo agoThe benefit of per row is that you can tag additional info like reserved user id etc, which you would have to track somewhere anyway.
- newsoftheday 1mo agoThe high contrast dark theme made my eyes squint and I started getting a headache within 60 seconds of trying to read the page. The war on light themes needs to end.
- sharno 1mo agoI think this could have used tigerbeetle instead?
- jamilbk 1mo agoI came here to make a similar comment. It seems like TigerBeetle was built for exactly the type of transactional processing this post is about?
- lordmoma 1mo agoI don’t get Why AI written technical article is a big thing here. Did we care if an article is typed or handwritten at a point?
- mtxeat 1mo ago[flagged]
- skullone 1mo ago[flagged]
- kennywinker 1mo agoShopify’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. https://www.techwontsave.us/episode/340_shopifys_leaders_are_pushing_right_wing_politics_in_canada_w_rachel_gilmore https://www.techwontsave.us/episode/340_shopifys_leaders_are...
- stiltzkin 1mo ago[dead]
- hdndjsbbs 1mo agoYeah it's an awful place to work unless you're a far-right bro. My old director used to use slurs and vape in the office. The founder hires pro gamers with no technical expertise because he thinks they're cool.
- derwiki 1mo agoAre you implying that vaping is far right?
- kennywinker 1mo agoI think that was part of the “bro” bit, not the far right bit
- nozzlegear 1mo agoSounded to me like they were saying people vape in the office, which would make a bad work environment on top of the far right bros.
- hamdingers 1mo ago> people vape in the office It's famously a fully remote company.
- 1mo ago
- zhivota 1mo ago"But the hardest lesson wasn't about database design. It was discovering that the real bottleneck wasn’t what we were observing and measuring."
- Horffupolde 1mo agoBut was it load bearing?
- HatchedLake721 1mo agoIt needs a ledger
- CoastalCoder 1mo agoEven better. It's web-scale.
- ares623 1mo agoload = bearing gun = smoking insight = key gap = closed summary = executived
- trueno 1mo agoso 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.
- __s 1mo agoExample of their culture: https://x.com/tobi/status/1909251946235437514 https://x.com/tobi/status/1909251946235437514
- trueno 1mo agoholy yikes
- tybit 1mo agoThey do heavily use AI, but you haven’t refuted their point that if inventory is in SQL, storing reservation in a second storage system increases complexity.
- manbash 1mo ago> 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.
- esjeon 1mo agoI would call this one-row-per-contract-type, and this is the most general model for the problem (e.g. the model cannot be further broken down into finer level), thus, the most scalable model given storage is dirt cheap.
- dbbk 1mo agoI'm familiar with the reserved row approach (I use SELECT FOR UPDATE SKIP LOCKED) and yeah this replenishing idea terrifies me.
- e12e 1mo agoMaybe the example numbers are just bad - but now you expect your system to fall down if you scale from 10 to 100 locations?
- 1mo ago
- tailscaler2026 1mo ago[dead]
- jbird99 1mo agoThe lengths companies will go to avoid running different pieces of software...
- deleted 1mo ago[deleted]
- kirici 1mo agoThe default should be that every additional piece needs to be justified
- matwood 1mo agoMost companies would be best served picking MySQL or PG and only adding something else if absolutely necessary. Every piece of software added increases complexity.
- anonymars 1mo agoIt can be easier and cheaper to solve problems via technology changes than operations and people Now you only need MySQL expertise and maintenance rather than Redis and MySQL
- shay_ker 1mo agooutside the slop, i liked this post that was linked on innodb locking: https://jahfer.com/posts/innodb-locks/ https://jahfer.com/posts/innodb-locks/
- isignal 1mo agoIt seems there could be a simpler solution. 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.
- vxxzy 1mo agonow you have two problems. what happens when your reservation system backs up?
- dbbk 1mo agoYou don't sell stuff I guess
- sieabahlpark 1mo ago[dead]
- sandeepkd 1mo agoThe moment you added a background process you just replaced the complexity. 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
- 0x696C6961 1mo agoThe design in the shoppify post already had a background process for the item replenishment.
- firasd 1mo agoMy 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.
- firasd 1mo agoMakes 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
- culi 1mo agoThey 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
- tayo42 1mo agoThe blog probably was.shopify was pretty early and publicly all in on using AI for everything
- nozzlegear 1mo agoThis is Shopify, the leadership is full steam ahead on AI in a big way and they review employee performance based on AI usage.
- throwatdem12311 1mo agoAnd Lutke is a fascist. All the biggest proponents of AI seem to be fascists. Weird.
- srcreigh 1mo agoIt’s fascinating that in order to do this, they had to remove 50% of reads and 33% of transactions from the main DB.
- bijowo1676 1mo agonot 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.
- idoubtit 1mo 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 1mo agoi 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 1mo 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
- mrloopex 1mo agoThis 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.