The Great Migration from MongoDB to PostgreSQL
infisical.com
infisical.com
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.
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.
When an application grows to such a scale that you need a partitionable datastore it's not something you can just turn on. If you've been expecting consistency and availability, there will be parts of your application that will break when those guarantees are changed.
When you hit the point that you need horizontally scalable databases you must update the application. This is one of the reasons that NewSQL databases like CockroachDB and Vitess are so popular. They expose themselves as a SQL database but make you deal with the availability/consistency problems on day 1, so as your application scales you dont need to change anything.
Context: I've built applications and managed databases on 10's of thousands of machines for a public saas company.
I’m not sure precisely what you mean by “HA”, but, in my experience, out-of-the-box support for the most basic replication setup in MySQL is pretty bad. Just to rattle off a few examples:
Adding a replica involves using mysqldump, which is, to put it charitably, not a very good program. And the tools that consume its output are even worse!
There is nothing that shops with MySQL that can help verify that a replica is in sync with its primary.
Want to use GTID (which is the recommended mode and is more or less mandatory for a reasonable HA setup)? Prepare for poor docs. Also prepare for the complete inability of anyone’s managed offering to sync to an existing replica set via mysqldump’s output. RDS will reject the output due to rather fundamental permission issue, and the recommended (documented!) workaround is simply incorrect. It’s not clear that RDS can do it right. At least Azure sort of documents that one can manually real and modify the mysqldump output and then issue a manual API call (involving the directives that you manually removed from the dump) to set the GTID state.
Want point-in-time recovery? While the replication protocol supports it, there is no first-party tooling. Even just archiving the replication logs is barely supported. Postgres makes it a bit awkward, but at least the mechanisms are supported out of the box.
But maybe the new-ish cluster support actually works well one it’s set up as long as you don’t try to add managed RDS-style nodes?
That's one path, but it is not the only way, and never has been.
MySQL 8.0.17 (released nearly 5 years ago!) added support for physical (binary) copy using the CLONE plugin. And MySQL Shell added logical dump/reload capabilities in 8.0.21, nearly 4 years ago.
Third-party solutions for both physical and logical copy have long been available, e.g. xtrabackup and mydumper, respectively.
And there was always the "shut down the server and copy the files" offline approach in a pinch.
I wouldn’t call xtrabackup or mydumper an out-of-the-box solution.
As for support in hosted cloud providers, that's a question for the cloud providers, no one else can answer this. But my best guess would be because they want you to use their in-house data management offerings, snapshot functionality, etc instead of porting MySQL's solution into the security restrictions of their managed environment.
Yes, xtrabackup and mydumper are third-party tools, as I noted. If you needed something out-of-the-box prior to CLONE, the paid MySQL Enterprise Edition has always included a first-party solution (MySQL Enterprise Backup, often abbreviated as MEB). Meanwhile Community Edition users often gravitated to Percona's xtrabackup instead as a similar FOSS equivalent, despite not being a first-party / out-of-the-box tool.
Especially since:
- Servers will keep getting better and cheaper with time.
- Data is not only in postgres, you probably have redis, clickhouse and others, so the charge is balanced. In fact you may have different dedicated postgres, like one for GIS tasks.
- Those hacky extensions are damn amazing. No product in the world is that versatile.
- Posgres has much better support from legacy frameworks like django/ror/laravel than nosql alternatives. People shits on ORM, but they enable a huge plugin well integrated ecosystem that makes you super productive, and PG is happily and transparently handling all that.
- If by some miracle you actually reach the point you need this, you'll have plenty of money to pay for commercial HA/sharding, or migrate. So why think about it now?
its less about the scale and more about HA and service interruption: your service will be down if server dies.
That situation raises a separate set of concerns, especially in the context of Microsoft's main database cash cow being SQL Server, not Postgres/Citus.
On average, HN leans anti-MySQL, with concerns about Oracle ownership frequently cited in these discussions (mixed in with some historic distrust of MySQL problems that were solved long ago). But I rarely see the same sentiment being expressed about Citus, despite some obvious similarities to their ownership situation.
Personally I don't necessarily think the ownership is a huge problem/risk in either case, but I can understand why others feel differently.
My post is more technical, with examples and graphs, and less business-y.
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.
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.
- 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.
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.
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.
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."
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.
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.
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.
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.
(171,150)
(343,375)
(309,090)
So there you go...
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.
Can someone tell me what their licensing changes are?
EBITDA is used in the industry because it is a proxy for operating cash flow. Sometimes you don't have all the available data needed to get to OCF, or you're looking at company guidance (for future EBITDA values) or analyst estimates. It's easier to keep the conversation at the EBITDA level because it requires fewer assumptions. Generally the revenue line is ~easy to estimate because you can conceptualize how to go from the current number of customers to some future number of customers, how many dollars per customers and so on and so forth.
Then as you work your way to EBIT (Operating Income) you still have to assume some gross margin, R&D expense, etc. These are pretty tangible. It should be pretty easy to get to estimated EBIT from what the company discloses in guidance or what analysts forecast. Since D&A is pretty linear over time, people generally assume it just remains constant as a % of revenue, so now you have EBITDA which is very much like cash flow
EBITDA is similar to cash flow because it adds back to EBIT the non-cash expense that is D&A. The reason it's good to look at it before interest and taxes is because you're also thinking about how much cash the whole enterprise generates, not how much cash goes to equity holders at the end (which is often called "Free" Cash Flow because it's not tied up with commitments to others)
Coming back to Buffett, in the industries he tends to pick stocks from, CAPEX is a major thing. Companies need to build factories, buy equipment, etc. So if you just look at future EBITDA without accounting for future CAPEX needs, you're fooling yourself.
Truth be told, in those industries everyone also looks at "EBITDA minus Capex". Maybe they do so now that he's bemoaned everyone for not doing it in the first place, but IMHO his criticism largely doesn't apply among valuation professionals. Maybe it does for stock traders, but not for valuation purposes like in an M&A context
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. ;-)
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).
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.
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.
What mongodb benefits would you be missing?
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.
Then checking which query doesn't return the same result set.
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.
Perhaps not the right answer, but SQL literally stands for Structured Query Language. I suppose you'd need an USQL?
'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.'
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.
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.
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.
PostGIS is one such extension, and I would argue that if your use case involves geospatial data, then PostGIS alone is enough of a reason to use PostgreSQL!
Apparently MySQL does not run delete triggers in case the rows are deleted due to a foreign key cascade.
Eveytime I used its slightly advanced features, I ran into such problems. With PostgreSQL I do not need to think if this would work.
- Create constraints as NOT VALID and later VALIDATE them. This allows you to create them without expensive locks.
- explain (analyze, buffers). I miss this so much.
- Row level security.
- TOAST simplicity for variable text fields. MySQL has so many caveats around row size and what features are allowed and when. Postgres just simplifies it all.
- Rich extension ecosystem. Whether it's full text search or vector data, extensions are pretty simple to use (even in managed environments, a wide range of extensions are available).
Is that (and more) enough for me to migrate a large MySQL to postgres? No. But I would bias towards postgres for new projects.
In Aurora, Postgres has Aurora Limitless (in preview) which looks pretty fantastic.
As far as running yourself, Postgres actually has some advantages.
Supporting both streaming replication and logical replication is nice. Streaming replication makes large DDL have much less impact on replica lag than logical replication. As an example, if building a large index takes 10 minutes then you will see a 10 minute lag with logical replication since it has to run the same index build job on the replica once finished on the primary. Whereas streaming replication will replicate as the index is built.
Postgres 16 added bidirectional logical replication, which allows very simple multi-writer configurations. Expect more improvements here in the future.
The gap really has closed pretty dramatically between MySQL and Postgres in the past 5 years or so.
Curious how do you use PG for key/value and queue - do you use regular tables or some specific extensions?
I can imagine kv being a table with primary key on “key” and for queue a table with generated timestamp, indexed by this column and peek/add utilising that index.
() note: when PHP was taking off, MySQL had a smaller install base. This has long since changed - PostgreSQL hasn't grown much over the years, and MySQL has, at least since the last time I worked on both circa 2015-ish.
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.)
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.
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.
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.
All this really comes down to is picking the right database type for the problem you're trying to solve.
I hear this today the first time. What exactly os not atomic, and is there a resource with more details?
That is, of course, if one doesn't want to deal with piles of shitty code and even more shitty data.
You can't do that with PG JSONB unless you lock the row for reading...
How do you increment a value in a column while other people write to the database? You don't grab the whole damn row and rewrite it...
> which is why database engines worth anything let you describe to them what your needs are
Contradicts your argument.
Mongo has atomic updates to update specific fields, or you can do replaceOne() etc to replace the whole document.
While PG only gives you "replace" with JSON.
So I guess postgres isn't worth anything. :)
> 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.
The only difference we noticed is the additional replica set setting for a new instance. No other disadvantages are found.
> 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.
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.
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."
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.
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.
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.
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.
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"
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.
Last time i advocated for using pg vs mongodb, the person replied that mongodb clustering was super easy.
If you want to distribute your writes, that's a little trickier. There are options like Citus and such. But still not natively supported.
"You'll be back". Relational capability is just too useful beyond a certain point
Well then, tell us how you really feel.
Somehow, the work to do this seemed less work than learning SQL.
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.
I find the super-plain voice of the annoyed character further amplifies the humor.
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.
What type of ha can mongo do postgres can't?
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.
> 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.
Anybody who makes tech decisions based solely on what PR teams say is naive and incompetent at best.
As it is their "duty" to twist the truth in this way, it is our role as the users of these technologies to present things as they are.
No, mongodb’s PR approach and results were largely uncommon at the time. They didn’t advertise directly but instead targeted and amplified dev communities - as detailed in the article linked earlier.
The result was that people not associated with mongodb talked about mongodb at various dev conferences and in blog posts. People didn’t want to listen to PR teams then too, but they followed their peers.
Testing out technology on important projects is reckless and is a risk a lot of people take but push away any responsibility for.
As a professional, before I suggest to use a technology at a project, at first at the very minimum I spend some time with it to learn the ins and outs. Every technology has cons. Just because you don't know them does not absolve you from responsibility for them.
The reality is that most applications are boringly similar once you define the problem in simple words. There is very rarely a need to use new technology. It is my experience that it is usually better to use technology that is not necessarily cutting edge but one that you know well and that you can easily hire people who know it well.
Because no technology is perfect, but it is much better to know the cons ahead of time and know you will have people who will know the problems and who can find reasonable solutions for.
I am personally sick and tired of people who first advocate for new technology to be added to the project, touting its supposed benefits, not knowing the cons, then don't want to take responsibility once we actually find the dead bodies.
only a year or two later, when teams that went all in on MongoDB at the behest of MongoDB's marketing department started realizing they'd been sold a bill of goods, did the slow and arduous march back to what continued to be the best generalized solution for 95% of data problems, the ACID compliant relational database, begin to occur. given that, this blog post seems really behind the times.
Once everyone realized its shortcomings as a general-purpose database, there was a gradual "oh shit this isn't working" migration back to SQL-based databases.
I think the big problem is that the MongoDB programming model and APIs are very nice; we really need a database that is still relational "under the hood" but has an API that's more like MongoDB.
I can just as easily rapidly prototype in postgres. If I want to store schemaless JSON in postgres I can easily do that.
>In the end, I think it is good to have a selection of tools that are good at doing different things.
In the end I think every tool needs a niche - something it is good at that its competing tools are not good at.
Mongo doesnt have that.
I reported the issue, and thankfully I changed job and never had to deal with ES again. After a few years they contacted me to ask if the issue was still there, I said I had no idea.
I have no idea if the issue is still there.
The article itself does not "complain the product is bad" but some other comments in this thread would certainly suggest so. Instead, the article says that the use-case evolved, states reasons for why MongoDB was no longer suitable (while at some point it was) and why PostgreSQL was chosen instead, and discusses the migration process involved in the transition and the results.
Regarding lacking support with cloud providers and lacking experience with MongoDB is indeed NOT MongoDB's problem. However, it is a problem for users of the platform that are trying to self-host it with MongoDB as a database dependency and therefore justifies the PostgreSQL migration with PostgreSQL being a better candidate for this use-case.
To be clear, the article is not saying that MongoDB is bad and no fingers are being pointed. I would in fact say that there are an array of use-cases where MongoDB is an excellent choice (e.g. I resonate with the point on rapid prototyping).
Please don't skew the words.
The biggest use case for MongoDB was for huMongous data. Obvs MongoDB was a good fit, because of the name.
I have a hard time justifying a document database in 2024 when I have 2024 Postgres; I don't know how large the window is where MongoDB will do something Postgres won't, but I know my systems are not in that window. (Assuming it even exists, which I wouldn't be surprised it doesn't, but for the sake of argument I will assume it does.) But a decade ago that would be a much harder decision if I truly had a document-based use case.
You're right, 9.4/9.5 were when JSONB was introduced and expanded (release 2013/2014).
That said, it's a big decision to go with two very very different technologies (relational vs Document store) just for a column type, storing JSON must have been a pretty major product feature?
In other words that you don't see a use case for JSON columns because you don't think it's good DB design.
I know I have used them after decades of RDBMS work, and it feels like I'm "cheating" a bit. But they really do make sense for some use cases that can be harder to achieve with traditionally normalized RDBMS alone. For instance, for configuration options or scenarios where different objects may have a few different attributes, but are largely the same.
If you've ever used an entity-attribute-value data model or otherwise ended up with table proliferation or a bunch of nullable columns to accommodate the latter use case, you might appreciate the simplification JSON columns offer.
(Mind I mean the discussion is misguided, not you)
The problem is that relational model is a one solution to certain problem of when you want to pay the price of enforcing the model of your data. Relational database is when you pay all the cost upfront.
With MongoDB I have other options.
It does not mean they are necessarily better options. But some of them are better in some situations, for example when rapid prototyping. Or when you need to preserve data in different formats as the application evolves.
Personally, if you can, you probably should model your data upfront. But it is good to have other options, sometimes.
Thank you. A one sentence clarification of the issues in Mongo vs RDBMS.
So can PostgreSQL be with JSON types and queries.
Looks like the tool did a great job while they were getting started. Mongo is very easy to hack something together with, later on it looks like they just needed to migrate to something more stable
If you're talking about a startup, you might not even get to the let's do it over stage. You might have six months to just hack something out so you can raise more funding, and in that case Mongo is a great choice.
The needs of your business might also just evolve over time.
So, while I agree with your main point - there is nothing in Postgres that prevents rapid prototyping, and I would chose it over Mongo any moment, I understand why some people might prefer the more "dirty" approach.
I had to look it up, apparently adding a column with a non-null default was "fixed" in PG 11 (2018), but with a null default it had been fast for a while:
https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-...
I think this is similar to when people say writing tests speed them up through TDD, then others don’t write any tests at all. What I take away is you can do it either way as long as you’re bought in and are familiar with the process.
For me it’s no trouble at all to define my schema in advanced. And Postgres provides me with the json column escape hatch. Combined with the massive benefit of the data validity guarantees later in the app’s lifecycle I pick Postgres every time.
Even for prototyping there are many other good choices since RDBMS like Postgres have implemented JSON support. Mongodb looks like a solution in search of a problem.
Think application forms etc.
Some people do like the MongoDB API, however.
That perspective doesn't work well for database products, in my opinion. There is a huge pressure for databases to evolve with your business and applications and to adapt to whatever your throw at it.
Swapping out a database product is less like changing tools and more like changing a foundation. You can't do it every time a new problem arises.
That's not to say you can't use a few different products if that makes sense. But that has its complications.
So it became one of those software dogma things and I remember having "debates" right here on HN, including around the necessity of transactions.
So it's kind of ironic to see people now chastising those who fell prey to the dogma. Full circle.
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-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.
Hadoop just like Mongo had their hyped time in the sun, but RDBMS are far too advanced and versatile than any of them were.
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?"
Now, my opinion of Martin in general is not especially high and he's a bit of a controversial figure, but it wasn't just the kids. And Martin is also all about reliable software, so that makes it even more surprising.
It's the same reason he's so pro Trump I think.
> Doors and wheels fall off airplanes. Hundreds of illegal immigrants assault and overrun border guards in Texas. We can’t even muster support for our allies in the security council. But at least our pronouns are in order.
https://twitter.com/unclebobmartin/status/177262869295654553...
You really need to be a special sort of individual to go from a collapsed bridge to ranting about pronouns with no prompting or any prior mention of that. What a complete spanner.
Pure evil, as Trump would call it.
Or he's a hyper-politicized idiot completely consumed and obsessed by some topics and needs to talk about them all the fucking time at the drop of a hat, whether appropriate or not.
It has nothing to do with "sides"; it has to do with the forceful injection of completely unrelated matters.
Agree. Out of control neo-feudal lords taking up all the slack out of every system they can get their tendrils into in the name of efficiency, thus endangering us all in order to extract as much profit as possible out of our entire economic system.
The "other side"'s solution tends to be to further deregulate industry and the economy, and to empower and lower the tax burden of these neo-feudal lords, all while pointing at gays, trans-folk, and immigrants as if they are the reason people can't afford rent and bridges are falling down.
Just one problem, huh?
This illustrates what I meant about black and white thinking.
This is not black and white reasoning. Each problem can be on a continuous plane, from "slightly bothersome" to "there is an absolutely horrible evil lurking here".
Not an actual quote but a factual statement
To me the difference was in recognizing that the architecture advice is implicit - its not actually written down in the code, rather the code exemplifies it - and the business domain is explicitly modeled. When developers name everything "use case" and "interactor" and go crazy with packages and folder structures with architectural names it's a recipe for terribleness.
https://twitter.com/unclebobmartin/status/107212575854868889...
Which means his opinions are more often than not uninformed opinions and should be taken with a grain of salt.
You don't need a college education to read research articles, books and journals. But you certainly need an open mind and some discipline to not try to reinvent the wheel all the time. You need to learn about the state of the art in your field.
Some ideas and algorithms are proven and battle tested, and people should learn about them, before trying to create something new and claim it is much better than the old thing. This is very noticeable in some fields like cryptography.
His modus operandi certainly seems to be on the other extreme, everything old is bad and the new thing around the corner is going to change everything. Which causes a lot of people who believe him to collectively waste millions of hours of their time.
About your comments on the degree... it really depends on how you got it. For most people, I would agree with you. A small group of people in academia are insanely smart and are focused on research.
Somehow the entire Mongo DB era passed me through and I never used it once. I used to use MySQL in the 2000s, and switched to PostgreSQL in the 2010s.
They had an ORM which made everything much slower, and the most common operation they did was to add items into lists, which causes the whole document to be resized and requires it to be moved.
It could have been done with 1 sql server, but we needed 4 really heavy mongodb servers instead. And of course our code was buggy so the schema was all over the place.
I love Postgres and it's been my go-to default since 2014 and you can pry it from my cold dead hands, but it's more contemporary with Perl 6 than Perl 5 if we're talking grey beards.
I do not actually use any of the others you listed (well, occasionally bitmap index scans, but it's not a killer). The main difference between 8.x and 15.x for me is the small stream of optimizer features and tweaks (e.g. at some point, it started being able to reorder outer joins).
Extensions in Postgres 8 (and earlier) were also half-baked compared to 9.1 (2011), which added CREATE EXTENSION and other quality-of-life improvements including standardization.
The latest complete implementations are what most people have in mind when talking about them.
Would I pick Postgres 15 over Postgres 8? Yes, any day of the week. But I would also readily pick Postgres 8 over most of today's databases (including e.g. the latest MySQL and MongoDB versions), for most projects, if that was the choice I had.
MySQL foot guns were mostly under control by 2010, and it was operationally efficient to manage a cluster of MySQL servers in diverse configurations with mostly-known boundaries to what could go wrong.
The Postgres foundations are the same today, but what you can build on top is very different today than it was pre-9.x.
This assumes you need replication, though. You can get pretty far without it, especially given how wide hardware you can get now.
> MySQL foot guns were mostly under control by 2010
Eh. :-) I think you would have to specify which footguns in particular.
Also replication can be good for other operational reasons, such as zero downtime major version upgrades. Again depends on the business need/expectations.
Risks to the business are more important to me than my enthusiasm for a technology or product.
To be frank, PostgreSQL has evolved a lot since the late nineties. There was a time where people preferred MySQL over it at it seemed to work faster, certain things were easier and so on.
The MySQL performance advantages were large, with the small caveat that it was fast as long as you were using MyISAM, which achieved its speed by throwing away many safety features, like working transactions and foreign key constraints. Unsurprisingly, when eventually it got an engine that didn't throw caution to the wind, the performance advantage went away.
I spent a lot of time in the early 2000s showing people whose that many of the issues they were facing with their e-commerce websites were ultimately down to the MySQL engine they had picked. But it's faster! Yes it is, but now your application code has to deal with all the things the engine has now offloaded to your application code.
It's not that Postgres' changes were key to increasing market share, but that as MySQL matured, it had no options but to lose its 'advantages'.
I say “was”. I hear it’s vastly better now.
Without these windowless years, PostgreSQL would be the most used RDBMS right now.
You often want to create expertise and achieve operational excellence with the tech that you start out.
It is shockingly hard to change your storage engine once you are serving customers, so you really want to get that right.
I get that working with Oracle and their army of lawyers is not cheap or pleasant but the db is still excellent.
While I am loathe to recommend any Microsoft product, SQL Server is at least coherent, and has actual transactions like Oracle (and not just "checkpoints")
What do you consider not actual transactions?
Postgresql keeps dead tuples in place until vacuumed, which seems optimized for rollbacks. But isn't so bad if you're inserting much more than deleting or updating.
I almost don’t see the point in non-nestable transactions
Kids at universities still use XAMMP to learn databases.
In the end postgres is the better one. With mysql the defaults are bad.
" I 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. "
Right now I would not choose MySQL over Postgres at all, ever. I can't think of a single way it is materially better.
I don't think that's true—all the old cases that were legitimate before are still legitimate. The price of both persistent storage and memory has simply come way down to the point where many computational workloads are viable under vertically scalable databases again. I suspect the pendulum will swing the other way one day yet again (and of course there is a rich ecosystem of ways to query horizontally, if not transactionally, with SQL that will likely temper this swing even further).
I use the term "nearly acceptable" because for a long time Mongo's data consistency was absolutely crap (https://jepsen.io/analyses)
Personally I think people used Mongo because it was new, shiny and exciting and it promised performance (though it didn't really deliver for most people).
We haven’t exactly circled the square on CAP but we’ve certainly bent it.
Or Cockroach DB or similar, if you still need relational capabilities.
Are transactions still limited to a single document in Mongo?
Postgres Is Enough: 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.
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
(I do not work at Turso)
Don't know, I lean pretty minimalist on things, like I'm using `hyper` directly on another website I work on, rather than picking axum or warp
Sqlite comes with the headache of managing an attached volume.
I’m actually trying to get Postgres to work like this right now.
SQLite being embedded is simpler to use and more performant in this environment as there is no IPC overhead.
In conclusion: I would always advice for Postgres over the approach of setting up a volume with sqlite.
The second you need to move on, you need to get that pesky file out of that volume. With Postgres, you just connect to the database from any psql compliant client.
If you need a networked database supporting many users, probably better off with Postgres.
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...
> ... 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.