While I generally like Postgres more, the comparison does not include replication. It looks like master-master replication is still better supported on MySQL (but I may be wrong).
Another important thing for me is table partitioning. Both databases support it, but I'd love a comparison. MySQL seemingly covered a lot of ground in this regard.
Having done quite a bit with MySQL's replication in a master-master scenario, dragons be here. It works if you know exactly what your application will be doing, but honestly, it's best to avoid it. Master-master replication is tricky with any system, but the particular implementation of MySQL's replication semantics make it even more prone to inconsistent data.
Thanks for sharing, great article. I think most of the folks singing the praises of Postgres over MySQL have never had to operationally support both databases in complex multi-DC replication environments. The answer to many replication issues with Postgres is 'rebuild the DB from scratch', which is a terrible answer when you have large databases.
I thankfully no longer have to support Postgres in production, but I can still vividly recall that terrible sense of dread I'd get in my stomach whenever I'd receive a replication alert for a Postgres database.
what sort of "replication issues"? if you use archive_command and ship/rsync wal files somewhere you will safeguard against falling too far behind (past an arbitrary wal_keep_segments limit). Also, in 9.4 you have replication slots so you can get rid of wal_keep_segments altogether: the master knows the replication progress/status of each slave and keeps required wal files around until they are no longer needed.
or were you referring to some other issue with failed replication? something that mysql handles better/differently?
Table Changes Without Locking
> MyIsam uses table-locking to gain speed. That’s fine if many sessions involve reading; but when writing to a table, the writing session gets exclusive access and other sessions must wait until its finished. But PostgreSQl and InnoDB both use row-level locking…so again, it’s much less of an issue.
I am not sure I can agree with this one as well, but at least postgres does normally not use row level locking anymore (in 10 years?) because of MVCC.
Postgres does use row level locking, it just doesn't lock for reads - so you still get write-write conflicts on the same row. The same is true for InnoDB.
a binary comarison of features does not properly compare the technologies.
the implementation makes a large difference in the quality of the software and it's use.. objectively you could compare a ferarri and a honda this way and they would look comparatively similar.
postgresql isn't as feature complete as oracle.. but comparing it to mysql now is almost a moot point.
if it's not going to kill your company- or you're making something new from scratch- you're really doing yourself a disservice by not using postgresql.
there's more than a few reasons for this but a few that come to mind is:
* Sane Connector libraries (especially in C++ where mysql is inconsistent, lock prone and buggy)
* SQL standards compliance, not my pgsqlisms (there are, but they have namespaces which are obvious)
* MySQL has a habit of being massively inconsistent or not running in an expected way (input validation is very lacking, along with things like, changing column widths which irrevocably alters data)[0]
I mean, personally I'd rather not have a relational database which eats my data.
"table change" looks to be changes to the table's data since it only mentions table or row locking.
If it were about schema change, I don't think there's any way to talk the result into a tie, that's really one of the things MySQL is absolutely terrible at.
See, things like these should've been in that article. The rest of it was "everything is a tie except for JOINs but they've been working fine in MySQL since 5.6.5 so I chose Postgres". I've personally suffered through hours of deployment in a legacy MySQL application with a few million records. Thanks for this tidbit, I'm that much more curious about Postgres now
I didn't feel like signing up to comment on the article, but he's wrong about the license.
It is rare that you are linking the database code with your code. Therefore, the difference between BSD and GPL code will rarely, if ever, matter for a database user.
If you think you'll have to modify the code of the database, the license would matter.
Yes, and you you absolutely have to ship proprietary software that connects to MySQL you can use libdrizzle[0] which is a BSD licensed cleanroom implementation of the client library. Alternately you can go dig up the old original public domain version of the client library that existed before Monty got the clever idea of using fear of the GPL to sell more MySQL client licenses.
Having used MySQL for every side-project and website I've ever worked on, last month I finally switched to Postgres.
The main motivation was that MySQL doesn't support a native UUID datatype (!!!) but, having switched, it seems that Postgres is much better thought-out and designed. For example, it supports a wider range of types and I find it less confusing to choose the right type compare to MySQL. It appears to support unicode better. Its system of privileges and roles seems more logical. Lots of small things like this add-up.
For one problem I had recently, I found MySQL a better fit.
It supports unsigned ints which Postgres doesn't and it has bitcount built in. I am aware these are probably rarely used, but I wanted them for what I was trying to do (compare large combinations of DNA sequence). I tried implementing it in Python / Numpy and Java and PG but MySQL was still the fastest. (I am fairly sure if I spent more time, I could improve any of the solutions, but for a similar level of effort MySQL (plus Python) won.
Postgres does have a bit/varbit which may have been closer to your use case, though you're correct that there's no native bitcount (assuming by "bitcount" you mean "count the number of bits set to 1 in the field).
Also even with CHECK constraints being available, the question of unsigned ints seems frequent enough that there's at least one extension implementing unsigned types: https://github.com/petere/pguint (no idea about the quality or production-readiness).
To be honest I could have probably used signed integers but conceptually representing DNA sequence (4 possibilities at each position) was far easier to think about that two's compliment. The idea was to make everything as small and lean as possible, to get the maximum amount into memory, and it seemed to work.
Bitcount / popcount / hamming distance as a CPU operation will have saved a lot of CPU cycles over the PG method I tried.
You sound like you know Postgres fairly well. How feasible would it be to write a CPU based popcount extension?
"Licensing hassles". That's silly. 99% of people using MySQL or PostgreSQL will never have to think about the license. LGPL is sufficient for making software that interacts with the database without having any obligation to provide source for anything other than changes you make to the database software. So, unless you're shipping the database itself as your product, you don't need to care about the license of MySQL/MariaDB or Postgres beyond knowing you can use them as a database for your products without encumbrance.
I consider PostgreSQL the better database most days, but license FUD is counter-productive.
Having used a lot of sql server management studio, I got used it this kind of UI where the focus is writing and executing sql. MySql has Devart Mysql Studio that is similar and got me really productive with mysql.
Is there anything of similar quality to postgres nowadays? I user pgadmin years ago, but it was quite crude in comparison even to Query Analyzer in sql 2000.
While I generally like Postgres more, the comparison does not include replication. It looks like master-master replication is still better supported on MySQL (but I may be wrong).
Another important thing for me is table partitioning. Both databases support it, but I'd love a comparison. MySQL seemingly covered a lot of ground in this regard.
Having done quite a bit with MySQL's replication in a master-master scenario, dragons be here. It works if you know exactly what your application will be doing, but honestly, it's best to avoid it. Master-master replication is tricky with any system, but the particular implementation of MySQL's replication semantics make it even more prone to inconsistent data.
A co-worker of mine actually wrote on replication in postgres recently:
http://peter.eisentraut.org/blog/2015/03/03/the-history-of-r...
Thanks for sharing, great article. I think most of the folks singing the praises of Postgres over MySQL have never had to operationally support both databases in complex multi-DC replication environments. The answer to many replication issues with Postgres is 'rebuild the DB from scratch', which is a terrible answer when you have large databases.
I thankfully no longer have to support Postgres in production, but I can still vividly recall that terrible sense of dread I'd get in my stomach whenever I'd receive a replication alert for a Postgres database.
what sort of "replication issues"? if you use archive_command and ship/rsync wal files somewhere you will safeguard against falling too far behind (past an arbitrary wal_keep_segments limit). Also, in 9.4 you have replication slots so you can get rid of wal_keep_segments altogether: the master knows the replication progress/status of each slave and keeps required wal files around until they are no longer needed.
or were you referring to some other issue with failed replication? something that mysql handles better/differently?
The ANSI standard was done in 1986. Since then there have been several ISO standards updates and AFAIK MySQL has lagged significantly.
I am not sure I can agree with this one as well, but at least postgres does normally not use row level locking anymore (in 10 years?) because of MVCC.
Postgres does use row level locking, it just doesn't lock for reads - so you still get write-write conflicts on the same row. The same is true for InnoDB.
a binary comarison of features does not properly compare the technologies.
the implementation makes a large difference in the quality of the software and it's use.. objectively you could compare a ferarri and a honda this way and they would look comparatively similar.
postgresql isn't as feature complete as oracle.. but comparing it to mysql now is almost a moot point.
if it's not going to kill your company- or you're making something new from scratch- you're really doing yourself a disservice by not using postgresql.
there's more than a few reasons for this but a few that come to mind is:
* Sane Connector libraries (especially in C++ where mysql is inconsistent, lock prone and buggy)
* SQL standards compliance, not my pgsqlisms (there are, but they have namespaces which are obvious)
* MySQL has a habit of being massively inconsistent or not running in an expected way (input validation is very lacking, along with things like, changing column widths which irrevocably alters data)[0]
I mean, personally I'd rather not have a relational database which eats my data.
[0] - https://www.youtube.com/watch?v=emgJtr9tIME
Kind of glossed over the whole "CREATE INDEX CONCURRENTLY" thing in the "Table Changes" section, no?
"table change" looks to be changes to the table's data since it only mentions table or row locking.
If it were about schema change, I don't think there's any way to talk the result into a tie, that's really one of the things MySQL is absolutely terrible at.
See, things like these should've been in that article. The rest of it was "everything is a tie except for JOINs but they've been working fine in MySQL since 5.6.5 so I chose Postgres". I've personally suffered through hours of deployment in a legacy MySQL application with a few million records. Thanks for this tidbit, I'm that much more curious about Postgres now
Yeah, the fact the DDL statements are transactionable in Postgres is a HUGE win IMHO.
I didn't feel like signing up to comment on the article, but he's wrong about the license.
It is rare that you are linking the database code with your code. Therefore, the difference between BSD and GPL code will rarely, if ever, matter for a database user.
If you think you'll have to modify the code of the database, the license would matter.
You still need some client libraries to connect to the database: http://stackoverflow.com/questions/2038881/gpl-and-libmysqlc...
Yes, and you you absolutely have to ship proprietary software that connects to MySQL you can use libdrizzle[0] which is a BSD licensed cleanroom implementation of the client library. Alternately you can go dig up the old original public domain version of the client library that existed before Monty got the clever idea of using fear of the GPL to sell more MySQL client licenses.
The license issue is FUD.
[0]: https://launchpad.net/libdrizzle
It is not FUD. libdrizzle as you mentioned still lacking of MySQL 5.6 support. (libdrizzle 5.1 stable version was released two years ago)
Having used MySQL for every side-project and website I've ever worked on, last month I finally switched to Postgres.
The main motivation was that MySQL doesn't support a native UUID datatype (!!!) but, having switched, it seems that Postgres is much better thought-out and designed. For example, it supports a wider range of types and I find it less confusing to choose the right type compare to MySQL. It appears to support unicode better. Its system of privileges and roles seems more logical. Lots of small things like this add-up.
I'd definitely recommend checking it out.
For one problem I had recently, I found MySQL a better fit.
It supports unsigned ints which Postgres doesn't and it has bitcount built in. I am aware these are probably rarely used, but I wanted them for what I was trying to do (compare large combinations of DNA sequence). I tried implementing it in Python / Numpy and Java and PG but MySQL was still the fastest. (I am fairly sure if I spent more time, I could improve any of the solutions, but for a similar level of effort MySQL (plus Python) won.
Postgres does have a bit/varbit which may have been closer to your use case, though you're correct that there's no native bitcount (assuming by "bitcount" you mean "count the number of bits set to 1 in the field).
Also even with CHECK constraints being available, the question of unsigned ints seems frequent enough that there's at least one extension implementing unsigned types: https://github.com/petere/pguint (no idea about the quality or production-readiness).
To be honest I could have probably used signed integers but conceptually representing DNA sequence (4 possibilities at each position) was far easier to think about that two's compliment. The idea was to make everything as small and lean as possible, to get the maximum amount into memory, and it seemed to work.
Bitcount / popcount / hamming distance as a CPU operation will have saved a lot of CPU cycles over the PG method I tried.
You sound like you know Postgres fairly well. How feasible would it be to write a CPU based popcount extension?
How come no one ever tries FirebirdSQL?, it is awesome.
Write up an article about the pros/cons compared to mysql and postgres and submit it.
edit: Seriously, the google results for firebirdsql vs postgresql are abysmal.
I never tried it because there is no native support for FirebirdSQL in django.
"Licensing hassles". That's silly. 99% of people using MySQL or PostgreSQL will never have to think about the license. LGPL is sufficient for making software that interacts with the database without having any obligation to provide source for anything other than changes you make to the database software. So, unless you're shipping the database itself as your product, you don't need to care about the license of MySQL/MariaDB or Postgres beyond knowing you can use them as a database for your products without encumbrance.
I consider PostgreSQL the better database most days, but license FUD is counter-productive.
Having used a lot of sql server management studio, I got used it this kind of UI where the focus is writing and executing sql. MySql has Devart Mysql Studio that is similar and got me really productive with mysql.
Is there anything of similar quality to postgres nowadays? I user pgadmin years ago, but it was quite crude in comparison even to Query Analyzer in sql 2000.
Is there a website like http://www.pg-versus-ms.com/ for this? pg-versus-my.com?