Postgres vs. MySQL: A Complete Comparison

The 2023 Stack Overflow survey shows that Postgres has taken over the first place spot from MySQL and become the most admired, desired database.

As Postgres gains more momentum, it becomes harder to pick between Postgres and MySQL. MySQL is probably still the world's most popular open-source database by install base.

While Postgres positions itself as the world's most advanced open-source relational database.

At Bytebase, we work with both databases extensively since the Bytebase product needs to integrate with both databases as well as their derivatives. Our founders also build Google Cloud SQL, one of the largest hosted MySQL and Postgres cloud services.

Based on our operating experience, below we give an extensive comparison between Postgres and MySQL from the following dimensions:

• License
• Performance
• Features
• Extensibility
• Usability
• Connection Model
• Ecosystem
• Operability

Unless otherwise specified, the comparison below is between the latest major release, Postgres 15 vs. MySQL 8.0 (using InnoDB). We also use Postgres instead of PostgreSQL throughout the article, though we know the latter is the official name, which is considered the biggest mistake in Postgres History.

License

• MySQL community edition is licensed under GPL.
• Postgres is released under the PostgreSQL license, which is a liberal Open Source license similar to the BSD or MIT licenses.

Even though MySQL adopts GPL, some people are still concerned that MySQL is owned by Oracle. It's also the reason that MariaDB is forked from MySQL.

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 the following articles for details:

• Why Uber Engineering Switched from Postgres to MySQL
• The Part of PostgreSQL We Hate the Most

Unless your business reaches an Uber-like scale, the sheer database performance is not a deciding factor. Companies like Instagram and Notion are also able to herd Postgres at a super scale.

Features

Object Hierarchy

MySQL employs a 4 level system:

1. Instance
2. Database
3. Table
4. Column

Postgres employs a 5 level system:

1. Instance (also called cluster)
2. Database
3. Schema
4. Table
5. Column

ACID Transaction

Both databases provide ACID transactions. Overall, Postgres provides stronger transaction support:

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.

Query Optimizer

Postgres has a better query optimizer—more details in this rant.

Replication

For Postgres, the standard replication is physical replication using WAL. For MySQL, the standard replication is logical replication using binlog.

Postgres also supports logical replication via its Publish/Subscribe mode.

JSON

Both Postgres and MySQL support JSON column. Postgres supports more features:

• More operators to access JSON features.
• Allow creating index on JSON fields.

CTE (Common Table Expression)

Postgres has more comprehensive support for CTE:

• SELECT, UPDATE, INSERT, DELETE inside a CTE.
• SELECT, UPDATE, INSERT, DELETE following a CTE.

MySQL supports:

• SELECT inside a CTE.
• SELECT, UPDATE, and DELETE following a CTE.

Window Functions

Window Frame Types: MySQL only supports the ROWS frame type, which allows you to define a frame consisting of a fixed number of rows. Postgres, on the other hand, supports both ROWS and RANGE frame types.

Range Units: MySQL only supports the UNBOUNDED PRECEDING and CURRENT ROW range units, while Postgres supports more range units, including UNBOUNDED FOLLOWING, and BETWEEN.

Performance: In general, Postgres implementation of window functions is considered more efficient and performant than MySQL implementation.

Advanced Functions: PostgreSQL supports more advanced window functions, such as LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE().

Extensibility

Postgres supports extensions. The most outstanding one is PostGIS which brings Geospatial capabilities to Postgres. Besides, there is Foreign Data Wrapper (FDW) to allow querying into other data systems, pg_stat_statements to track planning and execution statistics, and even pgvector to perform vector search for AI applications.

MySQL has a pluggable storage engine architecture and gives the birth of InnoDB. But today, InnoDB has become the dominant storage engine in MySQL, so the pluggable architecture just serves as an API boundary rather than being used for extension purposes.

For auth, both Postgres and MySQL support a pluggable authentication module (PAM).

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 you to join tables from different databases. Postgres can only join tables inside a single database unless using the FDW extension.

Connection Model

Postgres uses a process per connection where each connection spawns a new process. MySQL uses a 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.

Ecosystem

All standard SQL tools support both Postgres and MySQL well. Because of Postgres' extensible architecture and the fact that it's still owned by the community, the Postgres ecosystem is more thriving in recent years. For every application platform offering a hosted database service, they all choose Postgres. From the Heroku in the early days to the new Supabase, render, Fly.io.

Operability

Due to the underlying storage engine design, Postgres has an infamous XID wraparound issue under heavy load.

For MySQL, we encountered a few replication bugs when operating a huge MySQL fleet at Google Cloud.

Those issues only occur in extreme loads. For typical workloads, both Postgres and MySQL are mature and reliable. Database hosting platforms also provide integrated backup/restore, monitoring.

Postgres or MySQL

It's the year 2023, and picking between Postgres and MySQL is still hard and often causes heated debate.

Overall, Postgres has more features, a thriving community, and an ecosystem. While MySQL has an easier learning curve and a larger user base.

We observe the same industry trend as the stack overflow result that Postgres is becoming more desired among developers. But from our operating experience, the sophistication of Postgres does cost some handiness. If you are not familiar with Postgres, we suggest you spin up an instance from the cloud provider and run a couple of queries to get a feel. Sometimes, those extra goodies are not worthwhile, and MySQL would be an easier choice.