Origin: https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres
MySQL:
Purely relational DB. Extremely fast for read-heavy workloads with the old MyISAM engine, sometimes at the cost of concurrency when mixed with write operations. With InnoDB(which allows transactions, key constraints and other important features). Heavy data write has been optimized to reduce the gap.
Postgres:
Object-relational DB, feature-rich, extendable and standards-compliant. balanced performance between reading and writing, capable of writing large amounts of data more efficiently, and handles concurrency better.
Performance shouldn’t be a factor when choosing between Postgres and MySQL, they are good enough for most run-of-the-mill apps. So considering other advantages is what we might want to do.
Postgres is object-relational, so it includes features like table inheritance and function overloading, also adheres more closely to SQL standards. Handles concurrency better than MySQL for:
Postgres implements Multiversion Concurrency Control(MVCC) without read locks, Postgres supports parallel query plans tha can use mutiple CPUs/cores. Postgres can create indexes in a non-blocking way(through the CREATE INDEX CONCURRENTLY syntax), and it can create partial indexes(for example, if you have a model with soft deletes, you can create an index that ignores records marked as deleted) Postgres is known for protecting data integrity at the transaction level. This makes it less vulnerable to data corruption.
When to Use MySQL
Despite all of these advantages, there are still some small drawbacks to using Postgres that you should consider.
Postgres is still less popular than MySQL (despite catching up in recent years), so there’s a smaller number of 3rd party tools, or developers/database administrators available.
Postgres forks a new process for each new client connection which allocates a non-trivial amount of memory (about 10 MB).
Postgres is built with extensibility, standards compliance, scalability, and data integrity in mind — sometimes at the expense of speed. Therefore, for simple, read-heavy workflows, Postgres might be a worse choice than MySQL.
These are only some of the factors a developer might want to consider when choosing a database. Additionally, your platform provider might have a preference, for instance, Heroku prefers Postgres and offers operational benefits to running it. Your framework may also prefer one over the other by offering better drivers. And as ever, your coworkers may have opinions!