3 ms·

MySQL works fine if you chiefly interact with your DB through an ORM or using basic queries. Postgres shines if you need advanced functionality: recursive quer

MySQL works fine if you chiefly interact with your DB through an ORM or using basic queries. Postgres shines if you need advanced functionality: recursive queries, lateral joins, window functions, foreign data wrappers, partial indexes, advanced types... The list of things MySQL doesn't do as well or at all, be it user facing or under the hood, is fairly long. There are plenty of examples of both DBs scaling successfully in the wild. IMO it's a coin toss decision for most CRUD apps. For more advanced apps, you might want to pick Postgres over MySQL for the same reason some companies prefer niche languages over Java or PHP: the pool of competent engineers is smaller but tends to be of higher quality.

pg314 10y ago
> MySQL works fine if you chiefly interact with your DB through an ORM or using basic queries. I ran into an issue [1] with MariaDB a year and a half ago: 'SELECT * FROM table' failed to return a large fraction of the rows of that table. Selecting them in a number of different queries with ranges of ids worked fine. Nothing fancy, no transactions, on an idle database. I might have been unlucky, but I will pick PostgreSQL over MySQL/MariaDB any day if I have the choice. Having had a cursory look at the MariaDB source code and a more in-depth look at the PostgreSQL source code confirms that preference. [1] https://jira.mariadb.org/browse/MDEV-9347?page=com.atlassian.jira.plugin.system.issuetabpanels%3Achangehistory-tabpanel https://jira.mariadb.org/browse/MDEV-9347?page=com.atlassian...
- falcolas 10y ago
  MariaDB != MySQL They're protocol compatible, but MariaDB has been drifting away from the core storage engines and optimizer for some time. For good and ill (I think you hit the latter).
jjt-yn_t 10y ago
Those statements echo many other "which of the two" I have read over the past eleven years or so.
evanelias 10y ago
Personally, I'd reframe this as MySQL being better than Postgres at OLTP workloads, and Postgres being better (much, much better) than MySQL at OLAP workloads. It isn't really a question of how advanced your app is, unless your app completely relies on some specific advanced pg feature. There are areas where MySQL is substantially more advanced than Postgres, such as replication options, which in turn affects operational maintainability at scale. > There are plenty of examples of both DBs scaling successfully in the wild. This is true, but there are far, far more examples of ludicrous-scale MySQL than there are for pg. Compare the list of largest sites/apps using MySQL (see some of my comments from a few months ago) vs a similar list for pg and the discrepancy is quite noticeable.
- rpedela 10y ago
  MySQL has wider adoption historically so of course you will see more large site/apps using it because you have to be around awhile to get so large. I expect that to change because PG has improved replication and performance over the last few years and now a lot of companies are choosing PG for new projects. Those stats will likely change more in favor of PG in 5-10 years.
  - evanelias 10y ago
    That's a very good point. I suspect it will depend a lot on what the largest PG users contribute back, in order to make it scale. For MySQL, the largest users were (and still are) the biggest drivers of scalability-related features... and that's despite MySQL being far less of a community-driven open source effort than PG. From my perspective, the biggest roadblocks for PG adoption at high-volume OLTP use-cases are its historical weakness in replication options, its process-per-conn model, and its lack of clustered index support. PG replication is improving at a great rate, especially with the addition of logical replication, but the overall spread of options is still behind MySQL (re: multiple kinds of logical replication; multiple options for async vs semi-sync vs sync replication; large ecosystem of tools to interact with the replication stream). The other two issues are a bit more fundamental. But given the number of excellent engineers working on PG, I'm sure that solutions will be developed in the near future.

pg314 10y ago

> MySQL works fine if you chiefly interact with your DB through an ORM or using basic queries. I ran into an issue [1] with MariaDB a year and a half ago: 'SELECT * FROM table' failed to return a large fraction of the rows of that table. Selecting them in a number of different queries with ranges of ids worked fine. Nothing fancy, no transactions, on an idle database. I might have been unlucky, but I will pick PostgreSQL over MySQL/MariaDB any day if I have the choice. Having had a cursory look at the MariaDB source code and a more in-depth look at the PostgreSQL source code confirms that preference. [1] https://jira.mariadb.org/browse/MDEV-9347?page=com.atlassian.jira.plugin.system.issuetabpanels%3Achangehistory-tabpanel https://jira.mariadb.org/browse/MDEV-9347?page=com.atlassian...

falcolas 10y ago
MariaDB != MySQL They're protocol compatible, but MariaDB has been drifting away from the core storage engines and optimizer for some time. For good and ill (I think you hit the latter).

jjt-yn_t 10y ago

Those statements echo many other "which of the two" I have read over the past eleven years or so.

evanelias 10y ago

Personally, I'd reframe this as MySQL being better than Postgres at OLTP workloads, and Postgres being better (much, much better) than MySQL at OLAP workloads. It isn't really a question of how advanced your app is, unless your app completely relies on some specific advanced pg feature. There are areas where MySQL is substantially more advanced than Postgres, such as replication options, which in turn affects operational maintainability at scale. > There are plenty of examples of both DBs scaling successfully in the wild. This is true, but there are far, far more examples of ludicrous-scale MySQL than there are for pg. Compare the list of largest sites/apps using MySQL (see some of my comments from a few months ago) vs a similar list for pg and the discrepancy is quite noticeable.

rpedela 10y ago
MySQL has wider adoption historically so of course you will see more large site/apps using it because you have to be around awhile to get so large. I expect that to change because PG has improved replication and performance over the last few years and now a lot of companies are choosing PG for new projects. Those stats will likely change more in favor of PG in 5-10 years.
- evanelias 10y ago
  That's a very good point. I suspect it will depend a lot on what the largest PG users contribute back, in order to make it scale. For MySQL, the largest users were (and still are) the biggest drivers of scalability-related features... and that's despite MySQL being far less of a community-driven open source effort than PG. From my perspective, the biggest roadblocks for PG adoption at high-volume OLTP use-cases are its historical weakness in replication options, its process-per-conn model, and its lack of clustered index support. PG replication is improving at a great rate, especially with the addition of logical replication, but the overall spread of options is still behind MySQL (re: multiple kinds of logical replication; multiple options for async vs semi-sync vs sync replication; large ecosystem of tools to interact with the replication stream). The other two issues are a bit more fundamental. But given the number of excellent engineers working on PG, I'm sure that solutions will be developed in the near future.