12 ms·
The Great Migration from MongoDB to PostgreSQL
- p-e-w 3y agoAt some point during the past 10 years or so, the world realized that document databases are actually a bad idea, and relational databases can do the same job better with just a few easy-to-implement QoL improvements (such as JSON operators in SQL). Meanwhile MongoDB's creators thought this was a great time to make Mongo non-free software, presumably with the goal of making a quick buck from cloud operators. The result is pretty much the landscape we are seeing today, with Postgres reigning supreme, and even SQLite being experimented with in server setups, while Mongo is on life support and MySQL not far behind.
- vmatsiiako 3y agoI wouldn't say "Mongo is on life support" – it's actually a very successful business growing 30% YoY on a massive scale. Yet, I agree that the license switch has definitely damaged their long-term ecosystem.
- smt88 3y agoThey grew 30% if you look at revenue, but that's not how people determine whether a business is healthy. EBITDA is a better metric, and that number was getting dramatically worse every year until 2023. But even in 2023, EBITDA was -$202M. I don't see it ever approaching $0. So it's still a shit business on top of a shit product that no one ever really needed.
- otabdeveloper4 3y agoMongoDB is great if what you need is a replicated log or queue. It's what Kafka should have been.
- smt88 3y agoAs others have said, you can just use your RDBMS for that now. Mongo is a feature of better products.
- otabdeveloper4 3y agoPostgres doesn't have a sane and working replication story. (Mysql does, but it's full of legacy warts, of course.) Mongodb is foolproof and works out of the box. The other alternative here isn't Mongodb, it's Couchdb, but that isn't an RDBMS.
- basil-rash 3y agoWhy do you choose EBITDA? Last I heard it was utter nonsense”, at least according to Warren Buffet. https://m.youtube.com/watch?v=tvnKylAyLbQ https://m.youtube.com/watch?v=tvnKylAyLbQ
- phonon 3y agoTheir "Comprehensive Losses" for the last 3 years are (in thousands) (171,150) (343,375) (309,090) So there you go...
- librasteve 3y agothey lost 300 million in one year?
- christkv 3y agoStock compensation cash flow was positive meaning they added cash to their account.
- jeltz 3y agoAre they literally setting money on fire?
- roenxi 3y agoYou'd probably save time if you directly suggest what income metric you want to use. In the video, Buffet is basically saying he cares about income after interest, tax, depreciation and amortisation. In other words, that he cares about actual income. That'd be very on-brand for Buffet. That measure won't be good for MongoDB Inc. either; they've been millions in the red for years. Losing $100 million in a year would be the best result they've achieved in a while now.
- _v7gu 3y agoso by Buffet's metric, Mongo is doing even worse
- 3y ago
- Joel_Mckay 3y agoI think anyone who administers mongodb would agree it has a list of very dangerous behaviors for novices. Try to upgrade a running instance node between some versions, and one may thrash object keys or worse. Best to dump and re-load your data every major upgrade to recover all set properties... and there still may be slight differences in the query language later. Wait, your data sets are 1.5TiB per node.... that is a lot of downtime... The cursor, projection, and re-format queries can be very good at reducing reporting traffic. However, the 3 page long syntax can be unreadable to the uninitiated. The json essentially jams a bunch of equivalent SQL queries in 1 transaction, but you still have to be careful as there is no real guarantee of ordering without auto-indices in clusters. mongodb is not as clean as SQL, but works for storing constantly changing meta data. i.e. if you find your SQL schema ends up with tuples that have a lot of poorly designed null entries, table edits, and or simply implements an object storage class abstraction in massive meta-data and object-class catch-all tables. And yes, the mongodb new licensing model telegraphs an unfavorable policy posture. However, I do understand not wanting fortune 500 companies exploiting your teams time for free. Profit is not an indication of quality, but rather fiscal utility. ;-)
- DrNash17 3y agowhat is mongo's new licensing model?
- rrr_oh_man 3y agoI was totally expecting someone to come up with some vanity metric here, but a negative EBITDA of several HUNDRED MILLION? Jesus. How did that happen. B2B business should be a symphony of ka-chings for them.
- dangtony98 3y agoI'm actually very bullish on MongoDB. While the article and significant sentiment in threads suggest a push off MongoDB toward PostgreSQL, I do think MongoDB has its own place in the stack and that it won't be obsoleted. I've personally had many pleasant experiences working with it in past projects. Regarding business metrics, I may have a slight bias coming from the startup world but we often value revenue the most, especially in the earlier stages of a company; we can reduce costs and perform various optimizations in the future but what stays at the foundation is whether or not there is strong product market fit such that more customers keep coming in and coming back to use the underlying product — I would say MongoDB's current growth trajectory is in line with that. I'm hopeful overall that they can turn profitable (maybe not this year or next but eventually so).
- smt88 3y agoHN should be full of Mongo's users and advocates, and yet this site loathes it. That by itself is a sign that it's a zombie. The reason we don't just look at revenue is that you can "buy" revenue with ads and sales teams. But if that revenue growth costs more than it pays because your customers aren't sticky, then your business doesn't work. You can't apply startup principles to this large, aging public company.
- mushi 3y ago“This site” loved ReThinkDB. How is it doing?
- deleted 3y ago[deleted]
- MrBuddyCasino 3y agoI‘m not sure that is the case. What I‘m seeing is that people use the cloud-native document stores such as DynamoDB instead of Mongo. The only thing zoomers hate more than traditional SQL databases is XML.
- cwbriscoe 3y agoDoesn't everybody hate XML?
- tgv 3y agoDocument databases aren't an inherently bad idea. They have their uses. I'm using mongodb because it fits my use case: the app is very front-end oriented, and the data the user needs on a specific page fits neatly in one document. There are (quite a lot) related documents, but they are only needed in specific parts of the app, and that's when those are retrieved. Pretty simple. I could argue that in-server SQLite is a bad idea: if you ever need to share the database or distribute tasks, you're fucked. But for some use cases it just works. "The world" hasn't realized shit. It jumps from fad to fad, fueled by hyped promises and lack of experience.
- eddd-ddde 3y agoWhat about a Postgres database where you store your documents in a key value table of json objects? What mongodb benefits would you be missing?
- tgv 3y agoWhat benefit does switching to postgresql bring? When mongodb stops being maintained, I'll consider postgres, but until then, I now only see downsides.
- eddd-ddde 3y agoLot's of useful features? You can join data when needed, proper transactions, schemas where you need schemas. For example you start with a basic (key, json) table. Once you begin to stabilise your schema, add columns generated from your json objects, now you can get proper joins, indexes, validations, on said columns.
- winrid 3y agoI don't doubt there are real benefits for them to switch but BTW this is not true: > Difficulty configuring database transactions: With MongoDB, setting up transactions was not trivial because it required running MongoDB in cluster mode with various configuration overhead; this made it extremely difficult, for instance, for customers to run a simple POC of Infisical because it required a production setup of MongoDB. You can run single instance in clustered mode, just a single-instance replica set. You get the oplog, transactions, etc. No advanced configuration required.
- vasco 3y agoAnd this has been possible for many years.
- blue_pants 3y agoAre there any disadvantages to using transactions with a single-instance replica set?
- ammo1662 3y agoWe run a single instance replica set for three years in a small application for its transaction function. The only difference we noticed is the additional replica set setting for a new instance. No other disadvantages are found.
- deleted 3y ago[deleted]
- CoastalCoder 3y agoObligatory parody link: https://youtu.be/HdnDXsqiPYo https://youtu.be/HdnDXsqiPYo
- anticristi 3y ago:))) I find the super-plain voice of the annoyed character further amplifies the humor.
- WuxiFingerHold 3y agoTalking to AI 12 years ago :-)
- paulmd 3y agoIn this world you either evolve to Elastic/Cassandra/etc or return to Postgres :crab: You really gotta search your soul and ask yourself: your cool idea really a database, or just a plugin for Postgres? And the answer is often the latter.
- winrid 3y agoThe problem is that postgres is not a replacement. The JSON operations are not atomic which is terrible. If you wanna use PG that's cool, but I'd suggest just avoiding JSON.
- mulmboy 3y agoHow are JSON operations not atomic? Genuinely curious
- bandrami 3y agoI think the sense is not "atomic by field" or whatever you'd call that. If you're going to ignore the fact that PG is an actual ORDB and just store gigantic blobs of JSON in it, it will write the entire object whenever you update part of it, because the whole point is you're supposed to store it as a multi-field record.
- SonOfLilit 3y agoIsn't that true for Mongo as well?
- rb12345 3y agoYes, but as MongoDB is a document database, storing and updating giant blobs of JSON as a single operation as opposed to breaking the JSON down into individual fields is intended behaviour. This works in Postgres too, of course, but then you lose the relational database advantages on top of the large-single-field issues. All this really comes down to is picking the right database type for the problem you're trying to solve.
- renegade-otter 3y agoThe irony of it all is that MongoDB actually matured and you do not have to jump through hoops to have, say, transactions. I am ALL for Postgres, I even wrote a post about the importance of being fluent in relational databases: https://renegadeotter.com/2023/11/12/your-database-skills-are-not-good-to-have.html https://renegadeotter.com/2023/11/12/your-database-skills-ar... What grinds my gears is the whiplash caused by this frantic stampede from one hype to the next. I am just waiting for the AI hype to blow over and for everyone to rediscover Hadoop, or something.
- sph 3y agoPostgres is no hype, it was already in plateau of productivity when MongoDB came to the web scale scene. Hadoop just like Mongo had their hyped time in the sun, but RDBMS are far too advanced and versatile than any of them were.
- renegade-otter 3y agoI am not calling Postgres hype - it should have never been NOT hype. It's a reasonable default for most problems, Now all I read about is how Postgres is awesome, as if it's this great new thing. I guess that makes sense, as the new generation of engineers is rediscovering stable, reliable, lean technologies after a decade of excesses with "exotic" tech. For grey beards, it's all very odd. Like, "where have you all been?"
- rendall 3y ago...and, continuing the cycle next season, devs will (re)discover NoSQL and document DBMS. I do wonder if this phenomenon of trendy tech is somehow integral to advancing the state of the industry, the SWE equivalent of early adopters. "Rediscovering the old" is a side effect, as old tech is used and reinterpreted in new ways. Kind of neat, really.
- Retric 3y agoThere’s an entire industry of people selling knowledge of the “next great thing/the one true way," but they need a new thing every few years as people learned whatever the old thing was.
- bsaul 3y agois it still difficult to create a cluster of pg dbs. either for redundancy or speed ? Last time i advocated for using pg vs mongodb, the person replied that mongodb clustering was super easy.
- baq 3y agoMongo absolutely can be the right answer if you need to horizontally scale to tens or hundreds of TBs. Anything below that I’d rather have a small cluster of big Postgres instances. There’s value in a SQL RDBMS which you just don’t get anywhere else.
- Ozzie_osman 3y agoRead replicas are pretty easy on postgres using replication. That said, you need to be careful about replica lag. If you want to distribute your writes, that's a little trickier. There are options like Citus and such. But still not natively supported.
- KingOfCoders 3y agoSecond that. A client hat PG replica lag, was doing backups from the replica and found out during restoring a backup, that some hours of data were missing.
- jononor 3y agoOuch? Are there standard tools to monitor that? Any best practice to avoid? I am considering exactly tha kind of setup...
- 5Qn8mNbc2FNCiVV 3y agoI don't know about there being a standard tool but you can track the LSNs together with a timestamp on the write node and from there calculate the replica lag by checking what LSN the replica is at
- namaria 3y ago> As part of that stack, we chose MongoDB + Mongoose ORM because the combination presented least overhead and allowed us to ship quality features quickly. As Sir Tony Hoare states, “premature optimization is the root of all evil,” and there was certainly no need for further optimization at the time. I find it interesting that one sentence claims they made an optimal choice for feature delivery speed, and the next one that they rationalized it as "non optimal as prescribed by Hoare". Never mind the fact that three sentences down the original quote Hoare said "It is often a mistake to make a priori judgments about what parts of a program are really critical, since the universal experience of programmers who have been using measurement tools has been that their intuitive guesses fail."
- dangtony98 3y agoThe nature of the use-case evolved and, at one point, the MongoDB + Mongoose ORM combination was in fact optimal judging from various aspects including tooling familiarity with most to the primary use-case being the managed cloud service. As the situation evolved, it no longer became optimal and was superseded by various stack-wide adjustments. Regarding tooling familiarity: In the earlier stages of a company, I'd argue it to be important to pick what you can ship with fastest. You don't want to prematurely, for example, setup a complex microservices setup and deployment with K8s for an idea that is in its infancy and that might not even get that much traffic to begin with. Instead, deploying a monolothic app to Heroku might be the optimal choice (YC has a more extreme term for this they call "duct tape engineering"). As the company grows and there is clearer product market fit, it makes sense to further optimize the stack and perform adjustments as needed. Regarding the use-case changing, it wasn't entirely obvious in the beginning that a lot of users would self-host the product, so it was designed more-so with the cloud product / managed service in mind; in hindsight things seem more obvious but not in the moment. As it became clearer that more and more people were going to self-host this product, the team shipped more features to accommodate for a simpler self-hosting experience and, as part of that, the PostgreSQL migration.
- glimshe 3y agoFriends don't let friends use MongoDB. In my previous job, I got a big promotion in part for getting rid of MongoDB and virtually eliminating our database problems.
- deleted 3y ago[deleted]
- pydry 3y agoMost of the time I've tried to convince people not to move away from mongo i failed. My only success was pushing back on switching from postgres to azure cosmos after Microsoft salespeople convinced some of our managers that we were making a mistake by not using their hot new toy. This industry...
- rglover 3y ago> Missing out on relational features: With MongoDB, we lost out on many nice features from the relational world like CASCADE which, when specified, deletes all referenced resources across other tables whenever a target resource is deleted; this hurt in particular because our data was very much relational. I'd be very curious what their data model was in relation to this problem. I wonder if denormalization of the data would have solved the problem without a need for a full database hop.
- onetimeuse92304 3y agoAh, the old "I used a wrong product for my problem and now I complain the product is bad because it does not suit my case." defence. MongoDB is a document database. It is not supposed to be good at relations. Also lacking support with cloud providers and lacking experience with MongoDB is not MongoDB's problem, it is your poor decisionmaking. If you value those things, you should have taken it into account when you were choosing MongoDB in the first place. Now, I am not a big lover of MongoDB. Some years ago I was forced to use it and I had very low opinion of it as quite immature product. It is still far behind in maturity compared to something like Oracle database or PostreSQL, but in the meantime I learned to appreciate some of the things MongoDB is good at. I also admit that MongoDB's transactions are a total joke. It should be prominently placed in the documentation that you are using them at your own risk. I don't use MongoDB transactions anymore because there are better ways to architect your application with MongoDB without using transactions. I like MongoDB for the ease of use when rapidly prototyping things. I like the concept of a no-schema, document database and some of the additional features it provides to deal with the document. I like its reactive java driver which is a breeze to use to construct data pipelines quickly. I like change streams. In the end, I think it is good to have a selection of tools that are good at doing different things. It is our responsibility to chose the right tool for the job. If you chose poorly, don't try to fault the tool for it.
- thih9 3y agoTo be fair, Mongodb’s pr team did their best to present it as the solution to all problems. > In 2012, 10gen’s VP of Corporate Strategy Matt Asay argued “there will remain a relatively small sphere of applications unsuitable for MongoDB … the majority of application software that developers write will be in use cases that are better fits for MongoDB and other NoSQL technology … Those functions that really help a company innovate and grow revenue [will be NoSQL].” He would note that we were living in the post-transactional future. https://news.ycombinator.com/item?id=15124306 https://news.ycombinator.com/item?id=15124306
- onetimeuse92304 3y agoTo be fair, almost all product PR teams do the same. I just ignore most of what they say in favour of my own opinion that I try to build based on reading actual technical documentation, experimenting with the product, etc. Anybody who makes tech decisions based solely on what PR teams say is naive and incompetent at best.
- thih9 3y agoLoosely relevant, “the marketing behind mongodb” https://news.ycombinator.com/item?id=15124306 https://news.ycombinator.com/item?id=15124306
- CSMastermind 3y agoMan, that thread feels prophetic. Since 2017 whenever this comment was written: > 100% of my friends who have used Mongo/similar NoSQL have given up and had a nasty rewrite back to pgSQL... I've led three migrations at different companies from Mongo to Postgres, spending months of my life and millions of dollars. There really should be a sociology study of why so many people/companies used Mongo even well after the downsides were clear.
- h1fra 3y agoFunnily enough, the one feature I don't recommend in Postgres is Cascade. Unless you have a very small controlled set of FK it's not a good idea. Batch delete is less problematic. I wish Postgres would do something about it, like elasticsearch deleteByQuery that can run in the background.
- endisneigh 3y agoTake away is this has less to do with technical reasons and most to do with licensing, which is fair. The other takeaway is the fact that they saw huge gains switching as a result of query optimizations with joins shows that they’re data wasn’t properly modeled for use with a key value store, which probably added to them switching to the right type of store to begin with.
- ako 3y agoAnd that’s the core problem with key value stores, your data needs usually grow beyond key value scenarios. In the beginning it might fit, but then you add more pages with different needs, reporting/dashboards with different needs, APIs with different needs, and ETL processes with different needs. Trying to force everything into key value is short term thinking.
- endisneigh 3y agoWhat you’re describing isn’t an inherent problem with a key value store. Forcing everything to OLTP SQL is also short term thinking. Pick the right tool for the job. It’s funny you mention growth as the limiting factor for key value stores, if anything that’s the one area where they’re objectively superior. Which is why most search databases and caches are key value stores.
- ako 3y agoNot if you use a rdbms to store your key/values. Then you can do arbitrary queries with joins over multiple tables, add views, stored procedures, etc.
- endisneigh 3y agoSure, but that’s not related to what you said prior. Again, right tool for the job.
- ako 3y ago
- jpgvm 3y agoI have done this migration twice and rethinkdb to PostgreSQL once. At this point I think document DBs are as good as dead for new projects. They will live for a really long time still but are in contraction and rent seeking mode now. Expect MongoDB licensing and hosting to increase in price and languish in terms of feature development from here on out.
- Beefin 3y agomongodb is a fortune 100 company and blows through expectations every quarter. doc dbs are far from dead.
- jpgvm 3y agoI didn't say it wouldn't make a ton of money, there is a huge number of MongoDB instances out there in the wild in Fortune 500 due to the MEAN stack days (just like RoR before it). I fully expect the company to keep blowing away expectations by increasing how much revenue they extract from that existing customer base. That said they -still- aren't profitable, they might be profitable next quarter in fact I expect them to be with the current environment but to only reach profitability now after their glory growth days are behind them seems... well pretty garbage. If anything it reminds of me of GoPro except instead of cheap Chinese clones it's PostgreSQL and JSONB that is coming for their lunch.
- unmole 3y ago> mongodb is a fortune 100 company Excuse me? MongoDB Inc. posted a mere $1.68 billion in revenue (with a $177 million loss). Coco-Cola posted $45 billion in revenue and ranks at #100. Forget Fortune 100, MongoDb isn't even in the reckoning for Fortune 500.
- forgotmyinfo 3y agoHuh, I didn't realize database performance correlates so strongly with stock price. Are there Prometheus metrics for this?
- Jolter 3y agoMaybe you’re just experiencing the trough of disillusionment, from the Gartner curve? I think it’s fair to call document databases a mature technology now, rather than “dead”.
- KingOfCoders 3y agoSome years ago we had so much problems with MongoDB that we also ditched it for Postgres. Never been happier. Not the reason, but very annoying: Why doesn't (perhaps this has changed?) MongoDB use SQL but Javascript with endless $s? It was always a pain writing ad-hoc queries.
- cj 3y ago> Why doesn't MongoDB use SQL Perhaps not the right answer, but SQL literally stands for Structured Query Language. I suppose you'd need an USQL?
- KingOfCoders 3y agoGoogle says: 'The original full name of SQL was SEQUEL, which stood for "Structured English Query Language". A language which has been structured in English or lets say English-Like Query language which follows the syntax structure of English.'
- globular-toast 3y agoI started my career doing web development using PHP and mySQL. Then pivoted to something completely different for about 6-7 years as I got sick of spending all my time making stuff work in IE6. I heard it the whole nosql thing, didn't understand it but thought it sounded silly. Then came back to web dev 5 years ago and found postgres was all the hype and nobody talked about nosql any more. Glad I skipped it!
- cpursley 3y agoThe one unsolicited piece of tech advice I always give is “Just use Postgres”. Postgres Is Enough: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f... There’s some Mongo/json alternatives in the list if you really need unstructured data. And there’s a huge plug-in ecosystem as well for just about anything you could imagine.
- kqr 3y agoBut isn't there a point before which "just use sqlite" is more appropriate advice?
- cpursley 3y agoSure, if you simply need a dumb store just for storing basic types that’s simple to operate. But SQLite is not at feature parity with Postgres.
- lysecret 3y agoI love sqlite as much as the next guy but as soon as you are working with multiple write processes (which you quickly will) you will have to move off. So, why not just start with Postgres?
- __s 3y agosqlite is great for what it is But I recently ended up using sqlite on a site, & it's clear it makes a lot of trade offs to keep itself minimalist. I wish I had just used postgres That minimalism can be appropriate if you want something that works with a single file to track state & no external process
- tristan957 3y agoHave you tried using something like Turso? Curious how that experience would be for you. (I do not work at Turso)
- 3y ago
- zulban 3y agoIf you like this kind of thing, I also migrated from mongodb to postgresql and wrote about it here: https://blog.stuartspence.ca/2023-05-goodbye-mongo.html https://blog.stuartspence.ca/2023-05-goodbye-mongo.html My post is more technical, with examples and graphs, and less business-y.
- bberrry 3y agoI like your custom prompt. I'll be trying it out for a while!
- iambateman 3y agoI use MySQL for everything - always have. Can someone hit me with a few reasons why you would use Postgres over MySQL? I don’t have any familial affinity to any database, but I’m not sure what the benefits to Postgres are relative to MySQL.
- gkbrk 3y agoNot worrying about Oracle suing you is a good reason to use PostgreSQL or MariaDB.
- kqr 3y agoBack when I selected which database to get more fluent in one of the concerns I had about MySQL is that it was decribed as playing loose and fast with type casts, at least compared to Postgres.
- derekperkins 3y agoThat's thrown around by people who have been around a long time, but hasn't been true by default for a decade, since 5.7 was released.
- andybak 3y agoPostGIS was way ahead of the MySQL equivalent last time I checked. The plugin ecosystem is pretty astonishing. Foreign Data Wrappers... I'm not hands on so much any more but there were a lot of things back when I was.
- wlll 3y agoI do scaling and performance work, mostly with Rails apps, but a significant amount of the work is database level and not language specific. I've used both postgres and MySQL (and a few other databases) going back to 2000. The best thing I can hear from a company when I start is "We use Postgres". If they're using postgres then I know there's likely a far smoother path to performance than with MySQL. It has better tooling, better features, better metadata.
- 3y ago
- akie 3y agoDid anyone here ever see a migration _from_ PostgreSQL/MySQL _to_ MongoDB? I've only ever seen startups pick Mongo, then regret it a few months or years down the line and migrate to a relational database. Did anyone ever see the opposite?
- djbusby 3y agoI watched a team go from a sloppy MySQL that they never tried tuning to Mongo (cause it's web-scale). It was another thing that almost killed the company. Now they are on PostgreSQL.
- harry_ord 3y agoI work with the aftermath of going from postgres to couchdb. The migration was done years before I joined. There are still customer records showing postgres ids. It didn't seem like it made things better since there were issues with dates and lost data. Working with it now isn't fun either.
- hibikir 3y agoI've seen a couple of successful ones, but there were extremely good reasons: Most of the time we don't need scaling, but when your target Mongo cluster has a couple hundred machines, and your queries really are very simple, NoSQL is not a crazy idea. Same with DynamoDB: Sometimes it really is a very good choice, but you need a use case of the right shape. If you have an extremely good use case for your table to be in 5+ regions, the dataset has billions of rows and you have no hot keys, maybe running your own replication system really is more effort than letting AWS do it all for you. It's just far more common for some tech lead in small company to imagine that they are going to be bigger than Netflix, than for someone to start conservatively with a RDBMS, and realize that suddenly they have hundreds of millions of users and some kinds of data where the relational bits don't matter.
- willio58 3y agoNo, but I am currently in a startup that mainly uses DyanomoDB (similar to Mongo) with support from Elastic for more search-based queries. We've found DyanmoDB to be great and we don't feel hindered by the lack of relational-ness. I will say in case anyone doesn't understand how this might be possible, relational querying is possible in document databases, but they essentially require just different ideas to achieve. In Dynamo, you might need to create secondary indices for example. Personally I love Postgres and would not mind if we had gone that direction but the more I use DynamoDB the less I feel like Postgres is the "only way"
- adhamsalama 3y agoI pushed the company I work for to use Postgres instead of Mongo in all new services and refactors. Now I'm leaving it to join a new company, where they use Mongo. Sigh. I'll have to do this all over again.
- arusahni 3y agoThis seems to gloss over the actual rewrite. How did you ensure queries were equivalent? How did you configure the product to be able to read from and write to both databases? Did migrating reveal any bugs? Were you largely able to port your queries 1:1?
- rullopat 3y agoThat's what happens when you choose the next product / framework / methodology because of fashion or because FAANG is giving you the stuff for free, without thinking if it really fits your use case and that 99.9999% of the companies will not have the problems Google, Facebook, etc. have.
- pmarreck 3y agoCould have told most people this back when everyone was going to NoSQL. "You'll be back". Relational capability is just too useful beyond a certain point
- agentultra 3y agoHoo boy. I did this once. Only it was CouchDB. And they didn’t use any schema. Data had accumulated in it for years. Then a customer said they wanted their data exported to PowerBI. I used a library that generated a type that could parse any JSON document you threw at it by feeding it examples. So I wrote some tools to scan through the database, generate parse test cases, etc. I then wrote a library to version and migrate records. With this I wrote some tools to start extracting meaningful bits out of the big-blob type. I could scan the database, download examples that didn’t work, make test cases, fix those, ask the experts in the system, etc. Then eventually it started spitting out structured data into Postgres and from there into PowerBI. Decent article.
- j45 3y agoIt always struck me as odd to see the hard work put into NoSQL databases to make them relational. Somehow, the work to do this seemed less work than learning SQL.
- jrochkind1 3y ago> we felt that it already delivered significant benefits by virtue of not being MongoDB Well then, tell us how you really feel.
- antfarm 3y agoI remember well how in the early 2000s everybody wanted to get away from their relational systems and NoSQL (Not only SQL) databases were the latest fashion. Looks like we have come full cycle.
- Dudelander 3y agoA fad that set back the industry by a decade.
- trynumber9 3y agoNot sure on that, it made some of the SQL solutions innovate quite a bit.
- forgotmyinfo 3y agoWhat did relational databases need from all the document DBs du jour? Broken transactions? Lack of ACID guarantees? Devil-may-care schemas?
- rsanek 3y agoyep, the last one -- Postgres' support for json / jsonb is way better now.
- jpalomaki 3y agoRelational databases have also evolved. With JSON types you can now easily mix relations and documents.
- Aloisius 3y agoCouldn’t you do that with the XML type? I believe that was added to Postgres before MongoDB existed.
- deleted 3y ago[deleted]
- EMM_386 3y ago> With MongoDB, we lost out on many nice features from the relational world ... > ... this hurt in particular because our data was very much relational. That is my best attempt at the summary. The write-up is good, but after you've been around a long (long) time ... you know what this is likely going to involve. Glad relational SQL is still going strong 50 years later.
- deleted 3y ago[deleted]
- Cyberdog 3y agoWhat is the point of using a query builder if you’re only going to support a single RDBMS? Why not write straight SQL and avoid the unnecessary abstraction layer? Is the query builder really going to be easier to learn to use (particularly for non-trivial queries) than SQL?
- callalex 3y agoIf your DB schema is expressed as an object/class it means your IDE will have robust autocompletion. It’s just a convenience to avoid having to look up the schema for whatever tables you’re interacting with. It helps cut down on trivial bugs during the writing phase, before you even get to testing.
- hello_computer 3y agoobligatory reference: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs
- Semionilo 3y agoGeneral speaking: don't use mongodb. It was super shitty when it came out and using postgres gives you a better and faster solution which you can also use in other projects. I'm always impressed when I see mongodb still making money.
- b9b10eb736 3y agoWhile I'd agree with the general stance of avoiding MongoDB for any new project, I find the statement that postgres always gives better and faster solution dubious. They don't really solve the same problems. If you happen to really need horizontal scaling, actual HA (not failovers) or documents with many field-level atomic operations, MongoDB might still be a better fit than postgres. For on-premise hosting, not having any HA out-of-the-box can be a major painpoint.
- Semionilo 3y agoSharding does exist with postgres. What type of ha can mongo do postgres can't?
- jacobyoder 3y ago"this hurt in particular because our data was very much relational." Umm... why would you not choose a relational engine to start with? This isn't said with the benefit of hindsight. I worked with Lotus 123 back in the early 90s. I get that there's value in document databases, but even then, there were limitations, and the need for the ability to have structured/related data in an engine that easily allowed for ad-hoc queries out of the box was apparent. I watched the entire nosql/mongo movement arise and evolve over time, and it rarely made sense to me. Even when mongo made sense for a problem... it only makes sense for that problem, and not as the primary basis for your entire application. Relational/SQL, with nosql/mongo/etc as secondary/ancillary data store has always made the most sense (imo). JSON columns in major databases now tend to provide a pragmatic balance of "good balance for many use cases". Justifying something other than that as a starting point is possible, but I've rarely seen cases where it makes sense to avoid a decent SQL engine as the core data store for your business apps.
- CSMastermind 3y ago> Umm... why would you not choose a relational engine to start with? There's typically two answers to this question: When they first started things were changing so quickly they didn't want to commit to a schema and felt like doing real data modeling would slow their velocity down so they dumped everything into Mongo and called it a day. Or they had someone on the team who insisted that Nosql was the way to go. They'll say something like, "I worked on xyz team at Amazon and we just used DocumentDB for everything and never had a problem. Those horror stories about Mongo are all from people who didn't use it right."
- jacobyoder 3y agoInterestingly... I've never encountered the first scenario as a justification. I know it must exist, as I've read people describe it. Every time I've been in a group where nosql was desired, it was because they saw it at a conference, or watched a youtube video, or read some blog extolling nosql virtues, and... yeah - that second option. They'd either never actually used nosql, or didn't realize what they were getting into, possibly because even if they'd used it, the larger architecture had SQL in it someplace, just outside what they could see.
- zachmu 3y agoPostgreSQL really is eating the database world. Although in this case, the authors originally chose an architecture that was poorly suited to their data model. They had relational data and put it in a non-relational store. This was obviously always going to cause problems.
- chrisjc 3y agoIn my experience, it's always come down to either using a specific database type for the wrong job, or using a database type the wrong way. - Treating an OLAP like an OLTP. - Using a document store for relational data bc managing relations in SQL is tedious and slows development/progress/features. - Using OLTP for analytics. - and so on... And the usual reaction ensues. Conversations begin about migrating to another product while not really paying attention to interaction patterns and the intent/reason for the various workloads. Of course such migrations only lead to either... - Place-shifting the issues, or - Resolving the issues that lead to the migration while creating new ones that didn't exist before. Workloads that were suited to the existing DB are now mismatched to the new one. However, Postgres seems to be a pretty safe bet when such migrations are undertaken due to how versatile it is. Personally, I don't care which database is used (within reason of course). Just use the right database type for the job.
- SOLAR_FIELDS 3y agoOver the years I’ve learned two things when it comes to picking a database when starting a new project 1 - most data is inherently relational 2 - Postgres is pretty good at basically any problem you throw at it, pretty scalably. It will fall over at bonkers amount of scale in some cases but you probably won’t have bonkers amount of scale Thus, if you want to pick any new data store that isn’t Postgres for your project, the default is Postgres. You have to specifically convince me that Postgres is not good enough for this specific use case if you want me on board for anything else as a data store.
- zachmu 3y agoIt's a pretty good default stance, yeah. We have been trying to convince people to use our new database [1] for several years and it's an uphill battle, because Postgres really is the best choice for most people. They really have to need our unique feature (version control) to even consider it over Postgres, and I don't blame them. [1] https://github.com/dolthub/dolt https://github.com/dolthub/dolt
- bit_flipper 3y agoI've run Postgres at large scale (dozens of machines) at multiple companies. I've also run MongoDB at large scale at multiple companies. I like both generally. I don't really care about data modelling differences - you can build the same applications with approximately the same schema with both if you know what you're doing. I don't understand how folks seemingly ignore Postgres' non-existent out of the box HA and horizontal scaling support. For small scale projects that don't care about these things, fair enough! But in my experience every Postgres installation is a snowflake with cobbled together extensions, other third party software, and home-rolled scripts to make up for this gap. These third party pieces of software are often buggy, half-maintained, and under-documented. This is exacerbated by Postgres' major version file format changes making upgrades extremely painful. As far as I can tell, there is no interest in making these features work well in Postgres core because all of the contributors' companies make their money selling solutions for HA/sharding. This is an area where MySQL is so significantly better than Postgres (because so many large Internet companies use MySQL) that it surprises me people aren't more unhappy with the state of things. I don't really want to run another Postgres cluster myself again. For a single node thing where I don't care about HA/scaling I do quite like it, though.
- deleted 3y ago[deleted]
- klysm 3y agoI feel like I have read this exact comment before verbatim
- jmuguy 3y agoThis depends on what level you consider HA and horizontal scaling to be required. I could make the same argument, based on my personal experience, that postgis ought to be included out of the box. Of course, I'll assume most people don't need it :)
- switch007 3y agoI guess some people really, really dislike Oracle (understandably). And MariaDB is lagging behind, less and less compatible with MySQL etc leading to various projects dropping support for it - notably Azure. I wouldn't pick it for a new project.
- kcartlidge 3y agoI've used (and introduced) MongoDB in production. Though I much prefer PostgreSQL, SQLite, MySQL, or SQL Server, for some use cases a document database is fine. However as I discovered myself, once you realise you need to use Mongoose with it you should usually take that as a prompt to consider going relational. Don't get me wrong, Mongoose is a good package. But the things it solves could likely be better fixed by moving away from MongoDB - I'd go so far as to say that in most cases Mongoose should only be added to help an existing project; if you need it from the start you probably should go relational. (YMMV and there will be exceptions.)
- fakedang 3y agoCan somebody please explain to me the use cases in which someone would use Postgres vs MySQL vs something like Cassandra or Convex? I know the latter two have their own types of SQL implementations, so why aren't they used as much, even if they are NoSQL databases?
- fl0ki 3y agoI inherited a troubled project using MongoDB and learned a lot about what is, and isn't, really a problem with MongoDB [for this kind of project]. BSON is a terrible format, but I genuinely like that I can use the same schema all the way through the JSON APIs to the database. If they have to deviate in future for some reason, you can deal with it then. If you are clever with generics, you can compose type-agnostic DB code with type-safe in-memory data structures, without needing any schema generation or ORM. It's a natural fit for data-oriented programming in my workhorses Go and Rust. Unfortunately, the Rust library is very poorly designed in many ways, making many basic things extremely inefficient, and that's not what you want in Rust. The Go library has really slow reflection-based (de)serialization, but other than that, it's very flexible and robust. If you keep your queries simple, it's easy to mock out and combine with techniques like snapshot testing. Since BSON has a lossless representation in the form of extJSON, you can have human-readable snapshots, diff them easily in review, etc. Doing bulk writes is easy and efficient, and doing sessions/transactions is easy, but you should basically never combine the two. The server can have its own configured timeout for a session, and if your bulk write exceeds that, it fails and retries, fails again, etc. This is a really serious design flaw that shouldn't have made it past review. If they wanted to enforce a maximum bulk write size, that should be known up-front, not depend on how many seconds it happened to take each time. Writing data to a replica set is extremely slow, and every index you add makes it slower. This compounds further with the above. I have concluded that MongoDB only makes sense for read-mostly workloads. If you're going to do your own in-memory data structures and validation anyway, writing out small infrequent changes, MongoDB doesn't do much for you but it also doesn't get in your way very much.
- XCSme 3y agoI currently use MariaDB for my self-hosted application. Would I gain big advantages switching to PostgreSQL? My main reason for going with MySQL/MariaDB is the availability and ease of installation on most hosting providers.
- derekperkins 3y agoNo. In almost no circumstances will it be worthwhile to switch either direction. While people on HN love to nit-pick on the margins, the core functionality for both is 95% overlapped.