Goodbye MongoDB, Hello PostgreSQL(developer.olery.com)
developer.olery.com
Goodbye MongoDB, Hello PostgreSQL
http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/
374 comments
Something that I need to call out here. In talking about how to handle the lack of fixed schemas within Mongo, the OP refers to using if/else blocks to get around field name changes. This shows a severe lack of understanding of how to work with Mongo at such a fundamental level. If using Mongoid (which he is) you simply need to run `Post.all.rename(title: :post_title)` to do the schema change. This may seem like a nitpick to some people, but honestly if that concept was lost on the developer then its clear that there is little to no credibility to be had in the reporting of all the other Mongo issues.
I'm glad that the OP is growing as a developer and starting to understand the merits of SQL and why having defined schemas is important. SQL is awesome and its not going to be replaced by NoSQL (at least not fully). However please make sure that you are building the foundation of your understanding by fully grasping the technologies that you are already using.
I'm glad that the OP is growing as a developer and starting to understand the merits of SQL and why having defined schemas is important. SQL is awesome and its not going to be replaced by NoSQL (at least not fully). However please make sure that you are building the foundation of your understanding by fully grasping the technologies that you are already using.
This post outlines some of the most expensive parts of deploying, operating, and maintaining an application that operates on "NoSQL" databases like Mongo, and in my experience, DynamoDB:
1) Implicit Schemas. We avoid this completely by doing production migrations and re-indexes on every new field we add. Its expensive, and we have to write/test/run scripts in production. At times I wish I could just write a Rails migration on an RDS instance and call it a day. N-1 compatibility isn't hard to accomplish with good code reviews.
2) Search. Want to search and join like in the old days? Good luck. Using a NoSQL DB as the primary authoritative store of record is great, but you'll need a secondary indexes for any searches you want to do. If you need to look up an object on a new field (or even one that already exists in all of your data) if you haven't built an index for it you will have to.
3) Serving Clients. Because of the schemaless-blobby nature of writing clients, things get real messy the minute you have multiple services or applications reading or writing to a DB. To get around this, you have to put a service in front of it and serve the data from some RPC technology, which is an extra step and requires more development and maintenance.
4) Administration. There aren't a long history yet of robust toolsets and "science" behind different schemaless NoSQL databases. Meaning which one you choose has a huge impact on your ability to fine tune it, debug consistency or other expectation issues, and do things like proper failover, backups, restores, etc. Knowledge between similar NoSQL DBs doesn't transfer as well so your mileage will vary more so than on SQL databases (MySQL vs PostgresSQL, for instance).
NoSQL has its place and purpose, but it is rarely as the "one database that rules them all" that many businesses end up with. I'd be interested in counter stories.
1) Implicit Schemas. We avoid this completely by doing production migrations and re-indexes on every new field we add. Its expensive, and we have to write/test/run scripts in production. At times I wish I could just write a Rails migration on an RDS instance and call it a day. N-1 compatibility isn't hard to accomplish with good code reviews.
2) Search. Want to search and join like in the old days? Good luck. Using a NoSQL DB as the primary authoritative store of record is great, but you'll need a secondary indexes for any searches you want to do. If you need to look up an object on a new field (or even one that already exists in all of your data) if you haven't built an index for it you will have to.
3) Serving Clients. Because of the schemaless-blobby nature of writing clients, things get real messy the minute you have multiple services or applications reading or writing to a DB. To get around this, you have to put a service in front of it and serve the data from some RPC technology, which is an extra step and requires more development and maintenance.
4) Administration. There aren't a long history yet of robust toolsets and "science" behind different schemaless NoSQL databases. Meaning which one you choose has a huge impact on your ability to fine tune it, debug consistency or other expectation issues, and do things like proper failover, backups, restores, etc. Knowledge between similar NoSQL DBs doesn't transfer as well so your mileage will vary more so than on SQL databases (MySQL vs PostgresSQL, for instance).
NoSQL has its place and purpose, but it is rarely as the "one database that rules them all" that many businesses end up with. I'd be interested in counter stories.
I haven't used MongoDB in production (the comments regarding reliability have been around for awhile), but playing around with it, I do like the json format and query structure.
The issue I have with SQL (MS sql in the case of work) is the amount of cleverness involved in some queries I have seen. Among the old timers, it seems almost a badge of honer to develop the longest, most clever SQL query that does everything in one step. Inevitably, there are problems, and people have trouble figuring out why because they can't debug parts of the statement in isolation. In this case, it's as if they have written an entire program in one line and can't test parts in isolation. No doubt this is abuse of the language... and admittedly my SQL skills are not world class, but I keep scratching my head and wondering why they do this to themselves. If a query is so complex you can't tell what it is doing, and it misbehaves, perhaps you would have been better off with some smaller queries you join in the program... nothing against joins... one or two or three of them... but really....
I keep asking myself if I'm missing something, but I sort of doubt it.
The issue I have with SQL (MS sql in the case of work) is the amount of cleverness involved in some queries I have seen. Among the old timers, it seems almost a badge of honer to develop the longest, most clever SQL query that does everything in one step. Inevitably, there are problems, and people have trouble figuring out why because they can't debug parts of the statement in isolation. In this case, it's as if they have written an entire program in one line and can't test parts in isolation. No doubt this is abuse of the language... and admittedly my SQL skills are not world class, but I keep scratching my head and wondering why they do this to themselves. If a query is so complex you can't tell what it is doing, and it misbehaves, perhaps you would have been better off with some smaller queries you join in the program... nothing against joins... one or two or three of them... but really....
I keep asking myself if I'm missing something, but I sort of doubt it.
I made a little bet with myself when I saw the topic of this post that a good 80% or more of the top comments would be of the "Shouldn't jump on the MongoDB/NoSQL bandwagon, nothing wrong with SQL" variety combined with various arguments to support that position or arguments against NoSQL in general or MongoDB specifically. I won.
Here's the thing; anyone that claims one is superior over the other without adding a specific use case or context is doing something akin to religious preaching. Databases are tools. Pick the tool you need for the job. If you don't understand exactly what a hammer is for and how it does what it does put effort into gaining that understanding. If after that research you think the tool has flaws that are important to you then don't use it.
Here's the thing; anyone that claims one is superior over the other without adding a specific use case or context is doing something akin to religious preaching. Databases are tools. Pick the tool you need for the job. If you don't understand exactly what a hammer is for and how it does what it does put effort into gaining that understanding. If after that research you think the tool has flaws that are important to you then don't use it.
As the post explained, one of the big problems of migration off of MongoDB is finding out the "effective" schema of the data, and then migrating it (according to that schema). By "effective" I mean the resulting structure of the data stored in the database.
I think it would very helpful to use here ToroDB (https://github.com/torodb/torodb). While being MongoDB-compatible, it stores data structured into PostgreSQL tables, automatically identifying the schema of the data. Then, just by looking at the created tables, you very easily have the schema (and the data migrated to that schema). It would make migration easier.
Disclaimer: I am a ToroDB developer
I think it would very helpful to use here ToroDB (https://github.com/torodb/torodb). While being MongoDB-compatible, it stores data structured into PostgreSQL tables, automatically identifying the schema of the data. Then, just by looking at the created tables, you very easily have the schema (and the data migrated to that schema). It would make migration easier.
Disclaimer: I am a ToroDB developer
It's very interesting to see the amount of negative articles about MongoDB, and at the same time see that its very popular on job boards. I'd honestly like to read an article about a success case
I also keep reading about how bad is MongoDB for lots of projects. I don't think MongoDB is worthless, but from my experience I also have concerns about it.
If so many people migrate to other technologies, why is MongoDB still so popular? Have a look at http://db-engines.com/en/ranking_trend, MongoDB has just passed PostgreSQL
If so many people migrate to other technologies, why is MongoDB still so popular? Have a look at http://db-engines.com/en/ranking_trend, MongoDB has just passed PostgreSQL
They don't write if they looked at other databases. I think there are a lot of alternatives that might have worked better for them than MongoDb and even Mongo 3.0 might have worked better.
Regarding the schemalessness for simple new properties they should probably have fixed that in the ORM layer, it can add default values if needed. That something is null can actually happen in sql also.
> For example, when defining a field as int(11) you can just happily insert textual data...
My eyes practically fell out of my head when reading this.
My eyes practically fell out of my head when reading this.
I'm also moving back to Postgres. Postgres was the first DB I used when I learned to code PHP. MySQL came later. Just recently, around a year or two ago, I got introduced to Mongo and all its surrounding hype. Mongo is good for small small apps that need no references and have a low requirement for speed and data integrity.
I feel like I'm missing something. So many comments in the article and here along the lines of, "just issues a warning and not an error."
Am I the only one here who's thinking that this is correct behavior on the part of the DBMS? Three result codes from an operation: 1) everything is okay, 2) I'm sorry Dave, I can't do that (error) and 3) Okay, if you insist, but I'm going to change your data to make it work.
Am I the only one who thinks: the programmer should be aware of and respond appropriately to ALL THREE, not just 1 and 2.? That anything else is just laziness?
Or is that just me? Am I missing some subtle consideration here that results in my thought process being naive? If I'm being naive I do want to understand what I'm missing, because getting schemas right and having my code react when improper data types are being used is sometimes a pain, ORM or not. But I've always thought it was the right thing to do.
Am I the only one here who's thinking that this is correct behavior on the part of the DBMS? Three result codes from an operation: 1) everything is okay, 2) I'm sorry Dave, I can't do that (error) and 3) Okay, if you insist, but I'm going to change your data to make it work.
Am I the only one who thinks: the programmer should be aware of and respond appropriately to ALL THREE, not just 1 and 2.? That anything else is just laziness?
Or is that just me? Am I missing some subtle consideration here that results in my thought process being naive? If I'm being naive I do want to understand what I'm missing, because getting schemas right and having my code react when improper data types are being used is sometimes a pain, ORM or not. But I've always thought it was the right thing to do.
Thank god for return of sanity.
Who doesn't want NoSQL with ACID properties? Can anyone tell me what is superior on MongoDB compared to PostgreSQL?
If anybody is going to talk about scalability, there are many good alternatives to scale PostgreSQL too.
If anybody is going to talk about scalability, there are many good alternatives to scale PostgreSQL too.
Not that im excusing mysql, but what I do when adding columns to a +10mil row tables is to create a new table with the new column and then insert into newtable from oldtable.
That changes those few hours into few seconds.
That changes those few hours into few seconds.
For some additional thoughts about schemaless vs. schema-enforcing datastores see
https://gist.github.com/neunhoef/9b6749089775e472d44cThis maybe related:
https://news.ycombinator.com/item?id=6712703
https://news.ycombinator.com/item?id=6712703
[deleted]
Is there anybody here who has run MongoDB at moderate scale with good results?
As in a few terabytes of data, >10k ops/second territory.
I've been really disappointed with its reliability and performance in situations where I've been around that.
As in a few terabytes of data, >10k ops/second territory.
I've been really disappointed with its reliability and performance in situations where I've been around that.
There is a mistake in the article, due to the OP not knowing an arguably basic notion about MySQL.
> when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it.
this is dependent on the SQL Mode, which is quite flexible. for example, the STRICT_ALL_TABLES will prevent strings to be inserted in INT fields:
mysql> create table example ( `number` int(11) not null );
mysql> insert into example (number) values ('wat'); Query OK, 1 row affected, 1 warning (0,00 sec)
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
mysql> insert into example (number) values ('wat'); ERROR 1366 (HY000): Incorrect integer value: 'wat' for column 'number' at row 1
There are certainly advantages in choosing PostgreSQL over MySQL... this is just not one of them :-)
> when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it.
this is dependent on the SQL Mode, which is quite flexible. for example, the STRICT_ALL_TABLES will prevent strings to be inserted in INT fields:
mysql> create table example ( `number` int(11) not null );
mysql> insert into example (number) values ('wat'); Query OK, 1 row affected, 1 warning (0,00 sec)
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
mysql> insert into example (number) values ('wat'); ERROR 1366 (HY000): Incorrect integer value: 'wat' for column 'number' at row 1
There are certainly advantages in choosing PostgreSQL over MySQL... this is just not one of them :-)
> Another way of handling this is defining a schema in your models. For example, Mongoid, a popular MongoDB ODM for Ruby, lets you do just that. However, when defining a schema using such tools one should wonder why they aren’t defining the schema in the database itself.
Bah. It's like they didn't know that schema-free data stores mean "there is no schema; different objects may have different fields". This is the whole point of MongoDB: you assume the responsibility of managing the schema. That's a "feature": you get greater flexibility by assuming more responsibility. Whether it's a useful feature, I won't say.
Their second reason is much more valid: MongoDB is not consistent/durable, all that. It's good for a cache, but not for long term data.
Here's a third reason I'd give against it as your primary data store: it's expensive. You have to keep your entire dataset in RAM, but that's not always necessary. My favorite example is from Foursquare. They had every single check-in ever in MongoDB in RAM. That's absolutely unnecessary, and quite silly to do so. Old check-ins are archived data. You don't need them. No user ever wants to know when/where they checked in three years ago. This is why at the time they were paying for 68 GB RAM Amazon boxes instead of 4-8 GB boxes. (I have no idea what they do now. I remember chatting with them on HN after a catastrophic out of memory failure when they filled up the entire 68 GB's).
Bah. It's like they didn't know that schema-free data stores mean "there is no schema; different objects may have different fields". This is the whole point of MongoDB: you assume the responsibility of managing the schema. That's a "feature": you get greater flexibility by assuming more responsibility. Whether it's a useful feature, I won't say.
Their second reason is much more valid: MongoDB is not consistent/durable, all that. It's good for a cache, but not for long term data.
Here's a third reason I'd give against it as your primary data store: it's expensive. You have to keep your entire dataset in RAM, but that's not always necessary. My favorite example is from Foursquare. They had every single check-in ever in MongoDB in RAM. That's absolutely unnecessary, and quite silly to do so. Old check-ins are archived data. You don't need them. No user ever wants to know when/where they checked in three years ago. This is why at the time they were paying for 68 GB RAM Amazon boxes instead of 4-8 GB boxes. (I have no idea what they do now. I remember chatting with them on HN after a catastrophic out of memory failure when they filled up the entire 68 GB's).
In the future we might also move our Rails applications over to Sequel, but considering Rails is so tightly coupled to ActiveRecord we’re not entirely sure yet if this is worth the time and effort.
Actually, with modern versions of Rails, using Sequel in place of ActiveRecord isn't bad at all. Nothing in Rails is really tied to ActiveRecord anymore. There are dependencies on ActiveModel, but you can easily make Sequel::Model objects conform to this interface. Sequel-rails helps with most of that: https://github.com/TalentBox/sequel-rails
It's really just the migration that's difficult, as that's tough to do piecemeal and requires good test coverage.
Here's a recent side project of mine that I switched from ActiveRecord to Sequel pretty quickly once I remembered how extremely limiting the querying capabilities of ActiveRecord can be: https://github.com/bgentry/portfolio-api
And if you really want to keep the option for schemaless data storage, Postgres can now do that with better performance than MongoDB, while keeping full indexing capabilities: http://blogs.enterprisedb.com/2014/09/24/postgres-outperform...
Actually, with modern versions of Rails, using Sequel in place of ActiveRecord isn't bad at all. Nothing in Rails is really tied to ActiveRecord anymore. There are dependencies on ActiveModel, but you can easily make Sequel::Model objects conform to this interface. Sequel-rails helps with most of that: https://github.com/TalentBox/sequel-rails
It's really just the migration that's difficult, as that's tough to do piecemeal and requires good test coverage.
Here's a recent side project of mine that I switched from ActiveRecord to Sequel pretty quickly once I remembered how extremely limiting the querying capabilities of ActiveRecord can be: https://github.com/bgentry/portfolio-api
And if you really want to keep the option for schemaless data storage, Postgres can now do that with better performance than MongoDB, while keeping full indexing capabilities: http://blogs.enterprisedb.com/2014/09/24/postgres-outperform...
I did a similar migration last year. Loving postgres.
As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well.
The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid.
And one more thing:
The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid.
And one more thing:
star = Sequel.lit('*')
User.select(:locale)
.select_append { count(star).as(:amount) }
.select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) }
.group(:locale)
.order(Sequel.desc(:percentage))
It's very tiring to read things like this. And that is not meant as support for Mongo, quite the contrary in fact.
Do anyone recognize the application used for the metrics screenshots?
Beware of "The Problem Of Schemaless" can occur with a relational DB when you start defining columns as nullable. You end up with the same problem.
Interesting to read both the article and comments. It's cool to hate Mongo nowadays but we use it successfully (for PEPS, open source: https://github.com/MLstate/PEPS).
The version of MongoDb used is not mentioned in the article. The changes between versions is a problem with Mongo but it improved much recently.
One problem clearly identified is the lack of model. There are solutions to this. For instance, with Opa (http://opalang.org) we use strong static typing to generate a database model and guarantee that the whole applications sticks to it. That leaves out model changes, but there are solutions for that.
Also, there is no need to have the whole collections in RAM, but clearly enough RAM for the "working set" helps.
In the end, Mongo is no magic so do SQL databases which have their share of problems too.
The version of MongoDb used is not mentioned in the article. The changes between versions is a problem with Mongo but it improved much recently.
One problem clearly identified is the lack of model. There are solutions to this. For instance, with Opa (http://opalang.org) we use strong static typing to generate a database model and guarantee that the whole applications sticks to it. That leaves out model changes, but there are solutions for that.
Also, there is no need to have the whole collections in RAM, but clearly enough RAM for the "working set" helps.
In the end, Mongo is no magic so do SQL databases which have their share of problems too.
I had a business based on another company's API years ago, and they started changing a lot of their systems to work with mongo. The data lost a few useful points, like IDs and the speed and reliability didn't seem to be improved. My thought at the time it was, how about you guys just use postgresql? My comment on the mailing list about that seemed to be taken as an insult or naïveté. However, sure enough two or three years later they said they had all sorts of problems with Mongo and were switching to… Postgres.
Developers, who reads the article beware of reality, you can look back 5 years now, not looking forward 5 years ahead. Don't waste time in dealing with database columns and schemas, instead build the business faster, I found Mongo DB or any Document DB is good fit for agility.
1000's of business move to Document DB because it is schema-less.
While working on Document DB, you should be master in writing stand-alone scripts in Python/Perl/Ruby to run every-time you break the structure, or fix inconsistency.
edit: fixed typo errors