I'm a huge Postgres fan and yes I'm probably biased towards it. I do think it's probably the best open source database out there, but I also don't love these types of posts.
What makes something better than another things is a whole host of things, not just highlighting one really bad case from the other side. I'm sure if the MySQL world wanted they could point out ways that MySQL is so much better than Postgres. Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years. To take this one case and highlight it doesn't put the Postgres community in a positive light and it's a shame, because most I know within the community value good engineering work.
MySQL has had a lot of people that contribute to it and has some good things about it, it might be worthwhile for us to pay more attention to where they are better and just focus on improving Postgres and leaving things at that.
I remember ages ago (a decade? or so?) I started working at a company using Postgres for a project, and one of the things my boss asked me to do was to set up replication for the database. We had it on all of our MySQL databases, and everyone knew (myself included) that Postgres was a more serious, enterprise-y, "real" database.
Well, I googled around and couldn't find anything conclusive, so I hopped into the / channel for postgres and asked about it. "Is there any straightforward replication solutions for Postgres?"
It was like the early days of open source all over again. The first thing they did was challenge my needs. "Why do you need replication in the first place?" "Replication isn't a panacea you know." "Postgres doesn't have the issues that MySQL does that makes replication necessary." "Replication doesn't work well in the first place."
A ton of people jumped into what had been a quiet channel to make sure I knew that replication, which postgres didn't have, was something I didn't need in the first place, most of which happened before they even knew why I was asking.
After sorting through all that, a few people suggested "solutions". "Just have a cronjob rsync your data directory over to another machine every minute, and if your first server dies then you can just start postgres on the second one." Other solutions were much more convoluted. Patch postgres, add this software, manage it all yourself. And through it all, they kept telling me that I didn't need replication anyway, "but if you INSIST…".
In comparison, the MySQL channel on freenode was staffed with MySQL employees and volunteers who were always happy to help you solve your problem while teaching you what you were missing and giving you resources you needed. They'd answer questions, help your configuration, and even fix your SQL queries for you, all without being condescending about it or making you feel like an idiot.
And that's why I gave up on Postgres: the community. Postgres's was ridiculously hostile and self-important, verging on insecure and defensive. MySQL's was helpful, friendly, and full of resources. To this day I still hear great things about postgres, and I know it does a lot of things better than MySQL, but whenever someone writes a self-superior blog post about it I always think back to how much easier my life was by using a database where I could ask questions of the community and get useful answers.
Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years.
Coincidentally, using those two features together on MySQL can have fun results:
"Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE statements for the master and the slave to diverge. (...) An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)."
Not being sarcastic: this type of article always seems to be written with an audience of "people who love to pick a side and hate the other side, but haven't picked a side yet".
The article picks an extreme example and uses insinuation and condescencion to denigrate MySQL based on one example, but it goes further than that. For example:
> Maybe its wrong to start a flame war on this
So he knows that's what he's doing, and he's okay with that.
> but I need to say, at a personal level, that this is exactly the reason why I have spent years contributing to PostgreSQL and never once contributed to MySQL.
Maybe, but if I wanted to contribute to Postgres where would I start? There's no bug tracker to look through. With the MySQL family, there is.
Also, I'm willing to bet that most people who've contributed to MySQL haven't contributed to Postgres and vice-versa, so to act as though you're doing it from some kind of principled stance rather than just practicality makes no sense to me.
This entire blog post is a giant attack ad against MySQL for the purpose of making it look bad. "MySQL left a bug unfixed for 14 years while its users' data slowly corrupted. Vote Postgres for RDBMS, and I promise to keep your auto_increment fields consistent, day in and day out."
2nd Quadrant is a DBA-as-a-service company that does only Postgres, so it makes sense that the target audience is decision-makers trying to pick between databases who are themselves not DBAs (but probably in the position of hiring DBAs, either as normal employees or as a company to contract with).
The reality, for once, met my expectations in this article. Making imperative assumptions and backing them up with a poorly written 450 word count article that some angry (literally: "OMG sorry LMAO there is a 14,5 year old bug that was solved just now so the product must definitely be worse than the other one") developer wrote down in two minutes. That's what the title implied instantaneously.
Sometimes I don't understand the HN community. There is so much good stuff on this page, but at other times things that certainly don't deserve any attention make it to front page.
Perhaps a better title would have been: "One way in which PostgreSQL is better than MySQL". While I love Postgres, I have to admit that MySQL has its usefulness as well. I just did a side project where I started in node and postgres, realized I needed to hand it over to a guy who would want to end up hosting it on some 5 dollar a month PHP-esque plan. So, I bit the bullet, migrated the DB over to MySQL, and rewrote the backend in Laravel/PHP. It'll do just fine for his usage. My alternative was to host it on DigitalOcean, and then become the IT guy.... or perhaps spin up Heroku... but in the end, I just wanted to pass off a package and be done with it. MySQL was dead simple for my use.
In my experience writing software with generic SQL support the example shown here highlights something that's endemic in MySQL.
MySQL has a bug (eg. #199) or some deficiency (no subselects, no transactions, incorrect utf-8 support)[1]. The community's solution is a workaround (Here's how to restructure your select so that you don't need a subselect. Maybe you could use UUIDs instead of integer keys). Meanwhile the problem never existed in Postgres, which seemed to have been correctly and completely implemented from the start. After several years the issue is fixed in MySQL and the boosters ask that the score be reset and the clock re-started.
Postgres has its deficiencies and bugs but other than replication they never seemed core to the product in the way that they were for MySQL.
[1] These deficiencies span several decades and might seem unfair to bring up but I want to show that what the article is highlighting is just another round of the same problem.
This post actually makes me wonder... is there anyone that objectively still thinks MySQL is the better database of the two?
The only small advantage of MySQL I can think of (which is a great downside as well) is that it's really tolerant towards all sorts of erroneous usage and data.
The following should be a problem but are accepted by MySQL
- missing group by clauses
- all of the `ignore` commands (insert, alter table,
etc.)
- incorrect dates
Are you sure mysql doesn't enforce foreign key constraints? I recently did a project with mysql backend and it did threw constraint errors on invalid inputs that didn't match a parent key record.
My intuition is that, if there's anything MySQL does uniquely well on the "being an ACID datastore" front, then this would let you get the best of both worlds—essentially letting some of your Postgres tables use MySQL's storage engines et al.
It's possible, but FWDs are not really a viable solution for "pluggable storage". It also limits what the planner/optimizer can do, and (obviously) it introduces latency because there's network communications.
FWD are a great thing when you need to occasionally access remote systems, but it's not a drop-in replacement for storage.
For example, how exactly would you do consistent backups?
I had hoped that this article will illustrate the scientific/technical reasons for PostgreSQL's superiority over MySQL. Alas, it just mentions that there was a bug that got fixed after 14+ years in MySQL. The author doesn't even mentions the severity of the bug and the negative effects it had. Overall, for me this article is nothing but the author just "blowing his/her own horn".
What annoys me about MySQL is that it gets simple basic SQL wrong. For example
update foo set a = b, b = a
is supposed to swap the values in columns a and b, according to the SQL standard. Logically, all of the right hand sides are evaluated using the pre-update values from the columns, and then the results are assigned.
In MySQL, it logically does the assignments left to right, not evaluating an assignment's right hand sides until it gets to that assignment, and that evaluation uses the updated values from the already evaluated assignments.
In Python terms, it is supposed to be like this:
a, b = b, a
but it is instead like this:
a = b
b = a
If we had the time, I'd love to switch to something else just to get rid of annoyances like that.
My only take away from this article was PostgreSQL doesn't use a bug tracker. I didn't even know there are large projects out there not using some tracking system for bugs/issues
> If you use PostgreSQL in the cloud or from a services company, make sure you ask them what they have contributed to PostgreSQL and what their capability is to fix bugs and with what SLA. The answer to that question is what keeps open source well funded for these latest developments and future ones too.
Reasonable sounding theory. How true is it, how significant?
Does that story describe how 2ndquadrant gets business -- other vendors have customers asking for an SLA on PostgreSQL fixes, which would require PostgreSQL experts to meet?
I'm curious about the strategic thinking behind a post like this. I'd think that the serious money that can be gained by taking marketshare from another database for a company like 2ndquadrant would be in businesses using (or contemplating) Oracle Database, not Oracle (or some version of) MySQL.
Is it just me, or was that huge bug with serious implications if you don’t know it exists? I don’t often use MySQL, maybe its well know in the community.
if you're not keeping score you're only practicing
So the main point why postgres is better is that they don't use a bug-tracking system? Definitely that makes any qualified comparison impossible of such claims as the better "time to fix" or bug responsiveness. IMHO this post is trying to sell a weakness as a strength.
[+] [-] craigkerstiens|8 years ago|reply
What makes something better than another things is a whole host of things, not just highlighting one really bad case from the other side. I'm sure if the MySQL world wanted they could point out ways that MySQL is so much better than Postgres. Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years. To take this one case and highlight it doesn't put the Postgres community in a positive light and it's a shame, because most I know within the community value good engineering work.
MySQL has had a lot of people that contribute to it and has some good things about it, it might be worthwhile for us to pay more attention to where they are better and just focus on improving Postgres and leaving things at that.
[+] [-] danudey|8 years ago|reply
Well, I googled around and couldn't find anything conclusive, so I hopped into the / channel for postgres and asked about it. "Is there any straightforward replication solutions for Postgres?"
It was like the early days of open source all over again. The first thing they did was challenge my needs. "Why do you need replication in the first place?" "Replication isn't a panacea you know." "Postgres doesn't have the issues that MySQL does that makes replication necessary." "Replication doesn't work well in the first place."
A ton of people jumped into what had been a quiet channel to make sure I knew that replication, which postgres didn't have, was something I didn't need in the first place, most of which happened before they even knew why I was asking.
After sorting through all that, a few people suggested "solutions". "Just have a cronjob rsync your data directory over to another machine every minute, and if your first server dies then you can just start postgres on the second one." Other solutions were much more convoluted. Patch postgres, add this software, manage it all yourself. And through it all, they kept telling me that I didn't need replication anyway, "but if you INSIST…".
In comparison, the MySQL channel on freenode was staffed with MySQL employees and volunteers who were always happy to help you solve your problem while teaching you what you were missing and giving you resources you needed. They'd answer questions, help your configuration, and even fix your SQL queries for you, all without being condescending about it or making you feel like an idiot.
And that's why I gave up on Postgres: the community. Postgres's was ridiculously hostile and self-important, verging on insecure and defensive. MySQL's was helpful, friendly, and full of resources. To this day I still hear great things about postgres, and I know it does a lot of things better than MySQL, but whenever someone writes a self-superior blog post about it I always think back to how much easier my life was by using a database where I could ask questions of the community and get useful answers.
[+] [-] icebraining|8 years ago|reply
Coincidentally, using those two features together on MySQL can have fun results:
"Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE statements for the master and the slave to diverge. (...) An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)."
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate....
[+] [-] pmarreck|8 years ago|reply
https://eng.uber.com/mysql-migration/
[+] [-] erulabs|8 years ago|reply
Postgres is good because we have no open bug tracker whatsoever and we're always adding new features!
I can't figure out who this article is for - certainly not DBAs!
[+] [-] danudey|8 years ago|reply
The article picks an extreme example and uses insinuation and condescencion to denigrate MySQL based on one example, but it goes further than that. For example:
> Maybe its wrong to start a flame war on this
So he knows that's what he's doing, and he's okay with that.
> but I need to say, at a personal level, that this is exactly the reason why I have spent years contributing to PostgreSQL and never once contributed to MySQL.
Maybe, but if I wanted to contribute to Postgres where would I start? There's no bug tracker to look through. With the MySQL family, there is.
Also, I'm willing to bet that most people who've contributed to MySQL haven't contributed to Postgres and vice-versa, so to act as though you're doing it from some kind of principled stance rather than just practicality makes no sense to me.
This entire blog post is a giant attack ad against MySQL for the purpose of making it look bad. "MySQL left a bug unfixed for 14 years while its users' data slowly corrupted. Vote Postgres for RDBMS, and I promise to keep your auto_increment fields consistent, day in and day out."
[+] [-] geofft|8 years ago|reply
[+] [-] SoonDead|8 years ago|reply
[+] [-] arekkas|8 years ago|reply
Sometimes I don't understand the HN community. There is so much good stuff on this page, but at other times things that certainly don't deserve any attention make it to front page.
[+] [-] vijayagrawal18|8 years ago|reply
In the end, felt like click-baited
[+] [-] nobleach|8 years ago|reply
[+] [-] syncsynchalt|8 years ago|reply
MySQL has a bug (eg. #199) or some deficiency (no subselects, no transactions, incorrect utf-8 support)[1]. The community's solution is a workaround (Here's how to restructure your select so that you don't need a subselect. Maybe you could use UUIDs instead of integer keys). Meanwhile the problem never existed in Postgres, which seemed to have been correctly and completely implemented from the start. After several years the issue is fixed in MySQL and the boosters ask that the score be reset and the clock re-started.
Postgres has its deficiencies and bugs but other than replication they never seemed core to the product in the way that they were for MySQL.
[1] These deficiencies span several decades and might seem unfair to bring up but I want to show that what the article is highlighting is just another round of the same problem.
[+] [-] icebraining|8 years ago|reply
[+] [-] wolph|8 years ago|reply
The only small advantage of MySQL I can think of (which is a great downside as well) is that it's really tolerant towards all sorts of erroneous usage and data.
The following should be a problem but are accepted by MySQL
- missing group by clauses
- all of the `ignore` commands (insert, alter table, etc.) - incorrect dates
- not enforcing foreign key constraints
[+] [-] tyingq|8 years ago|reply
If I remember right, though, mostly because their use pattern was more like a key value store, and MySQL worked better for that pattern.
[+] [-] h2so4|8 years ago|reply
[+] [-] AlphaSite|8 years ago|reply
[+] [-] derefr|8 years ago|reply
My intuition is that, if there's anything MySQL does uniquely well on the "being an ACID datastore" front, then this would let you get the best of both worlds—essentially letting some of your Postgres tables use MySQL's storage engines et al.
[+] [-] pgaddict|8 years ago|reply
FWD are a great thing when you need to occasionally access remote systems, but it's not a drop-in replacement for storage.
For example, how exactly would you do consistent backups?
[+] [-] mannanali413|8 years ago|reply
[+] [-] tzs|8 years ago|reply
In MySQL, it logically does the assignments left to right, not evaluating an assignment's right hand sides until it gets to that assignment, and that evaluation uses the updated values from the already evaluated assignments.
In Python terms, it is supposed to be like this:
but it is instead like this: If we had the time, I'd love to switch to something else just to get rid of annoyances like that.[+] [-] jayflux|8 years ago|reply
[+] [-] mlinksva|8 years ago|reply
Reasonable sounding theory. How true is it, how significant?
Does that story describe how 2ndquadrant gets business -- other vendors have customers asking for an SLA on PostgreSQL fixes, which would require PostgreSQL experts to meet?
[+] [-] mlinksva|8 years ago|reply
[+] [-] unknown|8 years ago|reply
[deleted]
[+] [-] zilchers|8 years ago|reply
[+] [-] tatersolid|8 years ago|reply
I wonder how many private forks of MySQL had fixed that and never upstreamed a patch.
[+] [-] rompompel|8 years ago|reply
So the main point why postgres is better is that they don't use a bug-tracking system? Definitely that makes any qualified comparison impossible of such claims as the better "time to fix" or bug responsiveness. IMHO this post is trying to sell a weakness as a strength.
[+] [-] tkyjonathan|8 years ago|reply
[+] [-] metafunctor|8 years ago|reply
[+] [-] lwh|8 years ago|reply
[+] [-] unknown|8 years ago|reply
[deleted]
[+] [-] w0m|8 years ago|reply