License
-
MySQL's license is under GPL
-
PostgreSQL is a liberal Open Source license similar to the BSD or MIT licenses.
GPL is a copyleft license, for example, one of the reasons why Apple's Mac OS is not built on top of Linux is because Linux is GPL, which means that OSX would have been forced to be opened sourced, too.
Even though MySQL adopts GPL, some people still are concerned that MySQL is owned by Oracle. It's also the reason that MariaDB is forked from MySQL.
Connection Model
Postgres uses process per connection where each connection spawns a new process. MySQL uses thread per connection where each connection spawns a new thread. Thus Postgres provides better isolation, e.g. an invalid memory access bug only crashes a single process instead of the entire database server. On the other hand, the process model consumes more resources. Thus for Postgres production deployment, it's recommended to proxy the connection via a connection pooler such as PgBouncer or pgcat.
Performance
For most workloads, the performance between Postgres and MySQL is comparable with at most 30% variations. On the other hand, regardless of which database you choose, if your query misses an index, it could be 10x ~ 1000x degradation.
Saying that, MySQL does have an edge over Postgres for extreme write-intensive workloads. You can read following articles for details:
Unless your business reaches Uber-like scale, the sheer database performance is not a deciding factor. Companies like Instagram, Notion also able to herd Postgres at super scale.
Features
JSON
Both Postgres and MySQL support JSON column. Postgres supports more features:
-
More operators to access JSON features.
-
Allow to create index on JSON fields.
Query Optimizer
Postgres has a better query optimizer.
Security
Both Postgres and MySQL support RBAC.
Postgres supports the additional Row Level Security (RLS) out of the box, while MySQL needs to create extra views to emulate this behavior.
Usability
Postgres is more rigorous while MySQL is more forgivable:
-
MySQL allows to include non-aggregated columns in a SELECT that uses the GROUP BY clause. Postgres doesn't.
-
MySQL is case-insensitive by default. Postgres is case-sensitive by default.
MySQL allows to join tables from different databases. Postgres can only join tables inside a single database, unless using the FDW extension.
Conculsion
| Postgres | MySQL | |
|---|---|---|
| License | Postgres License(MIT alike) | GPL |
| Connection | Porcess per connection | Thread per connection |
| Performance | Internet scale | Comparable with Prosgres, better in extreme write-intensive workloads |
| Features | More advanced in transaction, security, query optimizer, JSON, CTE, window functions | Capable |
| Extensibility | PAM + Extensions | PAM |
| Usability | Rigorous and follow standard | Forgivable and follow convention |
| Operability | Good, a bit higher learning curve | Good, easy to use and operate |
| Ecosystem | Thriving community and more hosting providers | Large install base |