[jerrytsai]

I will respond only to Q1, taking the perspective of analytics and data science.

Answer: PostgreSQL > MySQL

Postgres implementation of SQL includes a few useful clauses that are useful for analytics that MySQL does not support.

It used to be that MySQL had no window functions, and that made it wholly inferior to Postgres when it came to analytics. However, it seems MySQL began supporting window functions two years ago, so that is no longer a reason to choose one over the other.

There are at least two features supported in Postgres that are not available in MySQL that I use often enough to care:

• SELECT DISTINCT ON

• FULL OUTER JOIN

Having these saves dev time. It is possible to implement either using MySQL, but your code will be more verbose, creating more opportunities for error.

If you care about analyzing the data for data-scientific purposes, you would be better off using Postgres. It isn't just the couple of extra clauses. It's also having more (useful) indexing choices and little choices like being able to use two pipes (||) to concatenate strings instead of CONCAT().

[raymondgh]

Having consulted for hundreds of analysts writing SQL to explore their data and create reports, I would agree with this assessment. Additionally, I tend to avoid recommending MySQL because of a strange behavior when using Group By. In Postgres, every column must be either aggregated or grouped by when using a grouping expression. In MySQL, this requirement is not present, and the ambiguity of the query is resolved by returning basically random results which are quite often misunderstood to represent the desired report. In other words, you can screw up in MySQL easily and not notice, whereas in Postgres, you’ll get an error message.

https://stackoverflow.com/questions/1023347/mysql-selecting-...

[dawkins]

This was in older versions. From 5.7 ONLY_FULL_GROUP_BY is enabled by default.

[petre]

Not to mention TimescaleDB and PostGIS. If one ever needs analytics or GIS these are great additions. One doesn't have to support multiple DBs for that. Also Citus, but MySQL (well MariaDB and Percona) also has Galera cluster.

Also in MySQL alter table and drop table closes your transaction. One would argue it doesn't even have "real" transactions because of this.