I love postgres (I’m a postgres DBA), and really dislike mysql (due to a long story involving a patch-level release causing server crashes and data loss).
That said, there is still a technical reason to choose mysql over postgres. Mysql’s replication story is still significantly better than postgres’. Multi-master, in particular, is something that’s relatively straightforward in mysql, but which requires third-party extensions and much more fiddling in postgres.
Now, postgres has been catching up on this front. Notably, the addition of logical replication over the last couple major versions really expands the options available. There’s a possibility that this feature will even be part of postgres 11, coming out this year (it’s on a roadmap). But until it does, it’s a significant feature missing from postgres that other RDBMSes have.
Good point. I’d seen the feature freeze deadline, but wasn’t sure if it had actually happened, and what had made it in (I haven’t followed the -hackers mailing list for a while). I was mostly speculating based on the fact that they’d announced a multi-master beta for last fall.
I’m not surprised it’s taking a long time – it’s a hard problem – but it means that “clustering” is going to be a weak point for postgres for a while longer.
Once you take all the other potential issues and difficulties with MySQL into account though, surely Postgres is a better choice on balance, even with more difficult replication setup?
It really depends. If you need horizontally-scalable write performance, and it’s important enough to sacrifice other features, then a mysql cluster is still going to do that better than postgres. It’s possible that a nosql solution might fit better than mysql, but overall that’s a decision that I can’t make for you.
I’ll add that there are bits of postgres administration that aren’t intuitive. Specifically, bloat of on-disk table size (and associated slowdowns) under certain loads can really confuse people. If you can’t afford to have a DBA, or at least a dev who’s a DB expert, mysql can be very attractive. I’m not saying that’s a good reason to choose it, but I understand why some people do.
Honestly, I haven’t looked closely at MariaDB lately. The last time I did was just to compare json datatypes – at the time, both mysql and mariadb were just storing json as parsed/verified text blobs without notable additional functionality.
I have to assume it’s better than mysql at things like stability, data safety, and other boring-but-necessary features. That’s mostly because mysql sets such a low bar, though, that it would take effort to make it worse.
You clearly know more about databases than me, but I would question idea that MySQL is a good choice when you lack a DB expert. If anything, it is then when you shouldn’t use it. I still carry scars from issues caused by such lack of expertise at one of my previous employers.
MySQL’s boolean is actualy an alias for TINYINT(1). This is why query results show 0 or 1 instead of true or false. It’s also why you can set the value of an ostensibly boolean field to 2. Try it!
LOL.
With MySQL, you’re stuck with calling LAST_INSERT_ID() after you add a new record
I wonder if that thing respects transaction semantics…
I think there could still be reasons to pick MySQL – but I’m not sure they could be technical.
Yeah. Since forever, the biggest reason has been “my shared hosting offers MySQL and my PHP CMS depends on MySQL” :)
MySQL’s boolean is actualy an alias for TINYINT(1). This is why query results show 0 or 1 instead of true or false. It’s also why you can set the value of an ostensibly boolean field to 2. Try it!
What is it with databases I hate and BOOLEAN types? The contortions that Uncle Tom goes through to justify its lack in Oracle are hilarious, if you don’t have to deal with Oracle.
| “my shared hosting offers MySQL and my PHP CMS depends on MySQL” :)
“I started using mysql, got converted to mariadb with debian and am now used to the problems of my DBMS” ;)
I find mariadb quite a good improvement. Sometimes I’m amazed how well it can keep up with my horrible sql queries.
I love postgres (I’m a postgres DBA), and really dislike mysql (due to a long story involving a patch-level release causing server crashes and data loss).
That said, there is still a technical reason to choose mysql over postgres. Mysql’s replication story is still significantly better than postgres’. Multi-master, in particular, is something that’s relatively straightforward in mysql, but which requires third-party extensions and much more fiddling in postgres.
Now, postgres has been catching up on this front. Notably, the addition of logical replication over the last couple major versions really expands the options available. There’s a possibility that this feature will even be part of postgres 11, coming out this year (it’s on a roadmap). But until it does, it’s a significant feature missing from postgres that other RDBMSes have.
PG 11 is in feature freeze since April. I don’t think there was anything significant for multi-master committed before that.
Good point. I’d seen the feature freeze deadline, but wasn’t sure if it had actually happened, and what had made it in (I haven’t followed the -hackers mailing list for a while). I was mostly speculating based on the fact that they’d announced a multi-master beta for last fall.
I’m not surprised it’s taking a long time – it’s a hard problem – but it means that “clustering” is going to be a weak point for postgres for a while longer.
Once you take all the other potential issues and difficulties with MySQL into account though, surely Postgres is a better choice on balance, even with more difficult replication setup?
It really depends. If you need horizontally-scalable write performance, and it’s important enough to sacrifice other features, then a mysql cluster is still going to do that better than postgres. It’s possible that a nosql solution might fit better than mysql, but overall that’s a decision that I can’t make for you.
I’ll add that there are bits of postgres administration that aren’t intuitive. Specifically, bloat of on-disk table size (and associated slowdowns) under certain loads can really confuse people. If you can’t afford to have a DBA, or at least a dev who’s a DB expert, mysql can be very attractive. I’m not saying that’s a good reason to choose it, but I understand why some people do.
What are your thoughts on MySQL vs MariaDB, especially the newer versions?
Honestly, I haven’t looked closely at MariaDB lately. The last time I did was just to compare json datatypes – at the time, both mysql and mariadb were just storing json as parsed/verified text blobs without notable additional functionality.
I have to assume it’s better than mysql at things like stability, data safety, and other boring-but-necessary features. That’s mostly because mysql sets such a low bar, though, that it would take effort to make it worse.
You clearly know more about databases than me, but I would question idea that MySQL is a good choice when you lack a DB expert. If anything, it is then when you shouldn’t use it. I still carry scars from issues caused by such lack of expertise at one of my previous employers.
Link is to 5.7 docs, but that’s still the case in 8.0. Ridiculous. At least MariaDB does support them now.
LOL.
I wonder if that thing respects transaction semantics…
Yeah. Since forever, the biggest reason has been “my shared hosting offers MySQL and my PHP CMS depends on MySQL” :)
What is it with databases I hate and BOOLEAN types? The contortions that Uncle Tom goes through to justify its lack in Oracle are hilarious, if you don’t have to deal with Oracle.
| “my shared hosting offers MySQL and my PHP CMS depends on MySQL” :)
“I started using mysql, got converted to mariadb with debian and am now used to the problems of my DBMS” ;) I find mariadb quite a good improvement. Sometimes I’m amazed how well it can keep up with my horrible sql queries.
I’m just shocked to learn mysql finally has window functions.
This:
https://www.slideshare.net/anandology/ten-reasons-to-prefer-postgresql-to-mysql