18 ms·
Inventory management in MongoDB: A design philosophy I find baffling
- bsg75 9y agoDid I read that right? A document per item in inventory? This seems horribly inefficient.
- lilbobbytables 9y agoAs someone not versed in Mongo or anything similar, what is the alternative? A large array of docs that has to be loaded in to memory to work with?
- pjungwir 9y agoFrom the article: > The example is that if you have 10 rakes in the stores, > you can only sell 10 rakes. The approach that is taken > is quite nice, by simulating the notion of having a > document per each of the rakes in the store and allowing > users to place them in their cart. In other words there are 10 documents in Mongo, not 1 document with a `"quantity": 10` attribute.
- codedokode 9y agoImagine that every of your items in a warehouse has a unique barcode to track it. In this case you have to keep a record in a database for every item. And with current amounts of RAM on servers there would be no problems even if you have tens of millions of items.
- icebraining 9y agoImagine that every of your items in a warehouse has a unique barcode to track it. In this case you have to keep a record in a database for every item. You could have a list inside a single document.
- codedokode 9y agoAnd what about tracking item status and location? Another list?
- icebraining 9y agoLists can contain items with complex structures, not just strings.
- codedokode 9y agoThen it would be difficult to add a reference to a specific items somewhere else (or one would even have to duplicate the data). The benefits and disadvantages of normalization vs denormalization are long known and this (denormalization) is possible with classic SQL DBMS too.
- wolco 9y agoA products table with a document per product. A user table. An order table with line items in an array of embedded objects. One would subtract product from amout field in products table to set new inventory level. A new order document gets created with all information needed to describe an order. Fields like total, subtotal, date would sit at the root level and line items with product descriptions and prices would be embedded as an array of objects. Then a user object with user_id, name address would be embedded. Dealing with documents is a different but being able to contain the entire dataset with some relations is nice.
- mrlinx 9y agoWhat else would you suggest?
- bildung 9y ago{"product":"rake", "in_stock":10}
- deleted 9y ago[deleted]
- paulddraper 9y agoWhat facilities are those at? What is their current status? We have a defect report; what is the case number?
- bildung 9y agoIf you want to use that in the real world, I'd say there is no way around a real relational database.
- BjoernKW 9y agoIt depends. If you use the event sourcing design pattern and CQRS this can be very efficient, especially if you have huge number of purchase and sale events.
- valarauca1 9y agoTLDR: Using a database that doesn't offer ACID, in a manner that requires ACID has non-trivial associated costs. This may also leave you open to a number of strange situations, where inventory quantities are unknown or incorrect.
- moxious 9y agoNew families of DB technologies generally traded certain things off (like ACID guarantees and transactions) in exchange for other things like scalability or flexibility. When someone comes back, and in user space reimplements the things that the DB intentionally traded off, you get the worst of all worlds. It's quite a bit like flattening one end of a screwdriver so that you can make it work to drive nails. Yes, you can make that work, and in some rare circumstances where you're trapped on a desert island that might be your only option. The rest of us will just use a hammer.
- deleted 9y ago[deleted]
- binocarlos 9y agoI remember writing documentation for a Perl system in 2001 - it was using MySQL MyISAM tables and the main developer had a few hundred thousand lines of Perl that acted as the same kind of client attempt at transactions. It was a mess and huge amounts of money were spent on trying to get the thing to work. A few months later InnoDB came along and made it apparent that trying to write transaction logic in the client was a very bad idea, which seems to be the point of this article.
- tacostakohashi 9y agoThat was a terrible time in history - lots of Perl web apps built using MySQL or mSQL, both of which lacked transactions, right at a time when e-commerce was taking off. Although Oracle, Sybase, SQL server and friends all transactions at that time, somehow the the mindset was that it was a complicated enterprise marketing gimmick, MySQL/mSQL are faster and simpler, and we can work around it in the client side. Seems like not much has changed.
- gaius 9y agoThat was a terrible time in history - lots of Perl web apps built using MySQL or mSQL It was made worse by the MySQL team actively advocating against features they didn't have "you don't need transactions, do it in your application", "you don't need foreign keys, do it in your application" blah blah. 20+ years later they're still struggling to shoehorn it in.
- z3t4 9y agolock tables
- mindcrash 9y agoParticularly fitting comment: "Mongodb, the ultimate Maybe monad. With a built in fromMaybe mempty call for your convenience." Per Hmemcpy and Michael Snoyman on Twitter.
- PeCaN 9y agoMakes it great for building Snapchat clones though!
- russdpale 9y agoMongoDB has purpose, but inventory management is not one of those purposes.
- gaius 9y agoMongoDB has purpose It literally does not.
- paulddraper 9y agoI think you're trolling but in case you aren't: structured logging, distributed cache, heterogenous analytics. You might prefer other solutions to each just as I prefer technologies other than Perl, but "no purpose" is incorrect.
- gaius 9y agoThere are better solutions to all of those use cases. Logging? ELK. Cache? Redis. Not sure what you mean by "heterogenous analytics" but that's usually a layer on top of whatever data store you choose, and we've established that MongoDB just isn't a good data store.
- user5994461 9y agoIt's not trolling. MongoDB really doesn't have a purpose. There is nothing it is good at. https://thehftguy.com/2017/03/29/whats-the-best-nosql-database-cassandra-vs-mongodb-vs-redis-vs-elasticsearch/ https://thehftguy.com/2017/03/29/whats-the-best-nosql-databa... logging => elasticsearch distributed cache => memcache analytics => any SQL database for low volume (< 1TB). any data warehouse database for high volume (> 10TB).
- paulddraper 9y ago"Memcache" Requires holding all data in RAM. Loses all data on a restart. Can't cache values more than 1mb. (Not that these are necessarily terrible; they're limitations that may not be appropriate for all uses.) "Don't use mongo, use any data warehouse database" That's what I suggested; you just turned it into an abstract recommendation.
- ojosilva 9y agoJust to make it clear, the issue the OP is pointing out is with whoever wrote this inexcusable piece of code in the book, not MongoDB itself. Even if the authors later clarify there's the possibility of error, just publishing such misleading and grotesque solution for creating transactions in a non ACID database is very poor judgement. MongoDB should not be used for an online ordering system, period. But if the programmer had no better alternative than Mongo, then please use Mongo's atomic operations [1] and nested documents to make sure nasty Bad Things don't happen. [1] https://docs.mongodb.com/manual/tutorial/model-data-for-atomic-operations/ https://docs.mongodb.com/manual/tutorial/model-data-for-atom...
- tyingq 9y agoIn the case of an ecommerce site, where multiple products can go in a cart, I don't see any real right way to do things with MongoDB. Atomic for one row just doesn't help much, and you can't nest arbitrary cart product mixes. As you say, bad idea in the first place.
- electricEmu 9y agoI was part of a team that successfully did inventory in mongo. The cart makes zero difference if Mongo is only storing the inventory. It's fine to atomically change a single inventory row. Operations that span multiple rows can be safely performed with a bus in front of it. You can't judge the idea. I don't think you've quite grasped it. I agree the book example isn't great, but that's not the technology's fault.
- tyingq 9y agoI am assuming a system where available inventory is debited when the CC transaction completes. How do you do that with single row only atomic transactions? And without charging $100 for what the customer thought was qty 3 $25 rakes and qty 1 $25 shovel...but is now something less than that due to concurrent purchases from other customers?
- 9y ago
- nwatson 9y agoMy prior startup tried to do a security product with backend in Mongo. It really needed transactions and to avoid N+1 issues. DB team insisted on writing "DAOs" that ended up pulling 1GB+ of data back from Mongo to merge in EACH of 100+ data points from a scanned machine. Similar issues in UI presentation. With multiple threads doing each of these things simultaneously there were many out of memory dumps. I analyzed these multiple times and told the DK VP Engineering what the problems were, and they didn't follow up for 6 months. He was gone soon after.
- united893 9y ago> DB team insisted on writing "DAOs" that ended up pulling 1GB+ of data That DB team shouldn't be allowed near any database. Why on earth would they go for such a moronic abstraction?
- weddpros 9y agoRegarding "SQL is better suited to this use case because it has transactions" comments: Before we had 3-tier architectures, people would have designed a shopping cart use-case as a single SQL transaction that would last maybe 10 minutes. The DB would make sure everything stays consistent until the final commit. The GUI would keep an open connection to the DB the whole time. In the web age, you want stateless services and HA. It means a transaction can't last more than a single web page. It becomes more challenging to design a shopping cart, because the DB can't handle a long-running transaction anymore. Writing a correct system that reserves the items you put in a shopping cart and doesn't leak items and doesn't sell the same item twice is not easy. A transaction Rollback will not do the cleanup for you, because there's no long running transaction anymore. So SQL transactions can't help as much as you think. Mongodb doesn't have transactions, but updates are atomic, which allows CAS and optimistic locking use cases. I agree it's less than ideal when you need to provide ACID behavior, but don't believe it's easy with SQL transactions. It's not. The author regrets the book's suggestion of putting each object in stock in its own document, and I agree it's probably a recipe for disaster. Atomic updates make this design absurd. You could easily db.products.update({_id: productId}, {$inc: {inStock: -5}, $addToSet: {pendingCarts: {cartId: cartId, quantity: 5, timestamp: new Date()}}}). This has the exact same atomic behavior as a SQL transaction to remove 5 from the stock and add a new "shopping cart entry" in another table. (you still need to expire cancelled shopping carts, and you may need a transactional way of completing the order: it's also manageable if designed as an idempotent operation) Anyway don't over-simplify this use case and believe "a single big SQL ACID transaction would handle the problem". That's just not true.
- electricEmu 9y agoThis is very close to how a team I was on solved this issue at Amazon. We took money, held inventory, had a shopping cart, and it worked out fine. A service bus was necessary, but the actual atomic transactions in MongoDB didn't fail us. We didn't lose data. While the nay-sayers discounted Mongo, we were raking in cash on top of it.
- gaius 9y agopeople would have designed a shopping cart use-case as a single SQL transaction that would last maybe 10 minutes This problem was solved in 1965 by CICS for the use case of "you're on the phone to a travel agent and they're finding you a ticket on their terminal". No "10 minute single transactions" anywhere... In the web age, you want stateless services and HA Those who forget history are doomed to repeated it.
- united893 9y agoShould have a disclaimer, founder is the founder of RavenDB and it's clear he's cherry picking things and blaming it on the database vendor, instead of whomever wrote that example.
- icebraining 9y agoWhere did he blame it on the database vendor?
- codedokode 9y agoThey should not try to emulate SQL databases, there are other ways to manage inventory without transactions. One way is to add a field to an item that show its status: whether it is in a warehouse, in someone's cart, ordered or sold. Then adding an item to a cart means updating those fields. There probably is a way to do several similar updates atomically. Another way is to use append-only collection, that keeps a list of events, like "Item X added to cart Y", "Item X sent to delivery". But I guess when there are more entities and relations this would become too complex to manage. While SQL databases have no problems with hundreds of tables and thousands of columns.
- elmigranto 9y ago> Then adding an item to a cart means updating those fields. There probably is a way to do several similar updates atomically. Atomicity is document level, not collection level. So you can't update multiple documents atomically. Or do you plan on having `{status: 'in-cart', cartOwner: 'customer-id' | null}` and single document for every stocked item (like 1k copies of the same book would be 1k db documents and you also have all those sold from before)? > Another way is to use append-only collection How does it help with overselling? To decide if it's okay to append, you have to know if current number of items is greater than 0 (don't forget to lock other clients out of appending this whole process, so they wait for you to finish).
- twothamendment 9y agoThanks for bringing up nightmares. Inventory on the web is one thing, think about how you'd do this for inventory in person at the store when a customer has product A in their hand and cash in the other. The inventory system says there aren't any in stock - should you sell them one? Of course. Are you tracking individual lots of inventory and the costs you paid for them? Against which lot did you sell this one? You don't have any - so how do you calculate the margin for this item you sold but don't know how much it cost or where it came from? If it is returned, do you restock that inventory? Mongo, SQL - they have there differences, but doing inventory management is tricky no matter what technology you use.