Compare Postgres and MySQL

According to 2023 Stack Overflow research (https://survey.stackoverflow.co/2023/), Postgres has replaced MySQL as the most admired, desired database.

As Postgres gains momentum, the choice between Postgres and MySQL becomes more difficult.

If you look at the number of installations, MySQL is probably still the largest open source database in the world.

Postgres prides itself on being the world’s most advanced open source relational database.

Bytebase works closely with a variety of databases and their derivatives because of the need to integrate with them, and Google Cloud SQL (https://cloud.google.com/sql), one of the largest cloud services hosting MySQL and Postgres, is one of the Bytebase founders’ creations.

In this article, we compare Postgres and MySQL on the following dimensions:

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

Unless otherwise noted, the following is based on the latest major version of Postgres 15 and MySQL 8.0 (using InnoDB). In the article, we use Postgres instead of PostgreSQL, even though PostgreSQL is the official name, which is considered a bad decision (https://www.craigkerstiens.com/2018/10/30/postgres-biggest-mistake/).

License

• MySQL Community Edition is licensed under the GPL.
• Postgres is released under the PostgreSQL license, a free open source license similar to BSD or MIT.

Even though MySQL is licensed under the GPL, there are concerns that MySQL is owned by Oracle, which is why MariaDB was forked from MySQL.

Performance

For most workloads, the performance of Postgres and MySQL is comparable, with at most a 30% difference. Regardless of which database is chosen, a query missing an index can result in a degradation of x10 ~ x1000.

That said, MySQL does have an advantage over Postgres for extreme write-intensive workloads. See below to learn more:

• Why Uber migrated from Postgres to MySQL: https://www.uber.com/en-SG/blog/postgres-to-mysql-migration/
• The PostgreSQL part we hate: https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most/

Unless your business is at the scale of Uber, sheer database performance is not a deciding factor. Companies like Instagram, Notion are also able to use Postgres at hyperscale.

Features

Object Hierarchy

MySQL uses a 4-level hierarchy:

1. instances
2. databases
3. tables
4. columns

Postgres uses a 5-tier structure:

1. instances (also known as clusters)
2. database
3. schema Schema
4. tables
5. columns

ACID transactions

Both databases support ACID transactions, with Postgres providing more robust transaction support.

Security

Both Postgres and MySQL support RBAC.

Postgres supports additional row-level security (RLS) out of the box, while MySQL requires the creation of additional views to emulate this behavior.

Query optimizer

Postgres has a much better query optimizer, see this https://news.ycombinator.com/item?id=29455852 for details.

Replication

Postgres’ standard replication uses WAL for physical replication, and MySQL’s standard replication uses binlog for logical replication.

Postgres also supports logical replication through its publish/subscribe model.

JSON

JSON is supported by both Postgres and MySQL; Postgres supports more features:

• More operators to access JSON functions.
• Allows indexes to be created on JSON fields.

CTE (Common Table Expression)

Postgres has more comprehensive support for CTEs:

• SELECT, UPDATE, INSERT, DELETE within a CTE.
• SELECT, UPDATE, INSERT, DELETE operations after a CTE.

MySQL Support:

• SELECT, UPDATE, INSERT, DELETE operations within a CTE.
• SELECT, UPDATE, DELETE after a CTE

Window Functions

Window Frame Type : MySQL supports only Row Frame type, which allows defining frames consisting of a fixed number of rows; Postgres supports both Row Frame and Range Frame types. Range Units : MySQL only supports UNBOUNDED PRECEDING and CURRENT ROW as range units; Postgres supports more range units, including UNBOUNDED FOLLOWING and BETWEEN. Performance : In general, the Postgres implementation of Window Functions is more efficient and performs better than the MySQL implementation. Advanced Functions : Postgres also supports more advanced Window Functions such as LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE().

Extensibility

Postgres supports several extensions. The best is PostGIS, which brings geospatial capabilities to Postgres; there is also the Foreign Data Wrapper (FDW), which supports querying other data systems. pg_stat_statements is used to keep track of planning and execution statistics, and pgvector is used to perform vector searches for AI applications.

MySQL has a pluggable storage engine architecture and gave birth to InnoDB. but today, InnoDB is the dominant storage engine in MySQL, so the pluggable architecture is only used as an API boundary and not for scaling purposes.

In terms of authentication, both Postgres and MySQL support Pluggable Authentication Modules (PAMs).

Usability

Postgres is more strict, while MySQL is more lenient:

• MySQL allows non-aggregate columns to be included in SELECT statements that use the GROUP BY clause; Postgres does not.
• MySQL is case insensitive by default; Postgres is case sensitive by default.
• MySQL allows JOINing tables from different databases; Postgres can only join tables within a single database unless the FDW extension is used.

Connection Model

Postgres works by spawning a new process on each connection. MySQL, on the other hand, spawns a new thread for each connection. As a result, Postgres provides better isolation, for example, an invalid memory access error will only cause a single process to crash, not the entire database server. On the other hand, the process model consumes more resources. Therefore, when deploying Postgres it is recommended to proxy connections through a connection pool such as PgBouncer or pgcat.

Ecosystem

Postgres and MySQL are well supported by common SQL tools, and the Postgres ecosystem has flourished in recent years due to Postgres’ extensible architecture, which is still owned by the community. Postgres is the platform of choice for every application that offers a hosted database service, from the early days of Heroku to the newer Supabase, render and Fly.io.

Operability

Postgres has the infamous XID wraparound problem under high load due to the underlying storage engine design.

For MySQL, we’ve encountered a number of replication errors when operating large MySQL clusters at Google Cloud.

These problems only occur under extreme loads. For normal workloads, both Postgres and MySQL are mature and reliable. The database hosting platform also offers integrated backup/recovery and monitoring.

Postgres or MySQL?

In 2023, choosing between Postgres and MySQL is still difficult and often hotly debated https://news.ycombinator.com/item?id=35906604.

Overall, Postgres has more features and a thriving community and ecosystem, while MySQL is easier to learn and has a large user base.

We observed the same industry trend as the Stack Overflow results, that Postgres is becoming more popular among developers. But in our experience, sophisticated Postgres sacrifices some convenience. If you’re not familiar with Postgres, it’s best to fire up an instance from your cloud provider and run a few queries to get started. Sometimes the added benefits may not be worth it, and it’s easier to go with MySQL.

It is also common for Postgres and MySQL to coexist within an organization.