MySQL vs PostgreSQL: Overview, Performance Comparison, and Use Cases

1. Database Overview TL;DR

1.1 MySQL

MySQL claims to be the most popular open-source database, belonging to the most popular RDBMS (Relational Database Management System) applications. The "M" in LAMP stands for MySQL. Applications built on LAMP use MySQL.

MySQL was originally developed by MySQL AB, then sold to Sun for $1 billion in 2008, and Sun was acquired by Oracle in 2010. Oracle's acquisition created two versions: commercial and community. The community version is criticized because Oracle controls development.

1.2 PostgreSQL

PostgreSQL positions itself as the most advanced open-source database, an ORDBMS (Object-Relational DBMS) derived from the University of California's POSTGRES project, based on version 4.2, originally developed at UC Berkeley in 1985 as the successor to Ingres. PostgreSQL is a fully community‑driven open‑source project.

It provides a single complete feature set, unlike MySQL which offers multiple community, commercial, and enterprise editions. PostgreSQL is licensed under the permissive BSD/MIT license, allowing organizations to use, copy, modify, and redistribute the code with only a copyright notice.

Note: MySQL hierarchy: Instance -> Database -> Table PostgreSQL hierarchy: Instance -> Database -> Schema -> Table A schema can be understood as a namespace and does not affect usage.

2. Performance Comparison

Test Environment

MySQL:

Hardware: 4‑core CPU, 16 GB RAM

Version: MySQL 8.0

PostgreSQL:

Hardware: 4‑core CPU, 16 GB RAM

Version: PostgreSQL 13

The benchmark used SELECT by primary key, UPDATE by primary key, and INSERT of a single row. From the results we can draw several conclusions:

In throughput, PostgreSQL’s SELECT performance is about twice that of MySQL, INSERT is 4‑5 times faster, and UPDATE is 5‑6 times faster.

Average latency shows PostgreSQL outperforming MySQL by several times.

For hot‑row updates, MySQL achieves only about 1/8 of PostgreSQL’s performance, with latency increasing sevenfold.

3. Suitable Scenarios and How to Choose

MySQL is simpler than PostgreSQL, which gives it higher popularity and richer documentation and component support. It is suitable for small‑to‑medium enterprises and personal projects because of its low entry barrier.

But this does not mean MySQL is the best.

PostgreSQL’s growth is rapid and appears to be catching up with MySQL, whose popularity is gradually declining.

MySQL suitable scenarios

MySQL fits simple applications such as e‑commerce, blogs, websites, etc. It can handle systems ranging from millions to hundreds of millions of rows, but under high‑performance demands (fast response, high throughput) its performance may become a bottleneck, especially with complex queries.

PostgreSQL suitable scenarios

PostgreSQL is better for complex data structures, advanced applications, and large‑scale datasets. It can also be used for smaller data sets, but it may struggle with complex query conditions because its planner can sometimes choose suboptimal indexes.

4. Summary

Advantages of PostgreSQL over MySQL

Performance: PostgreSQL far outperforms MySQL in both latency and overall throughput.

Single‑row updates: With HOT UPDATE, PostgreSQL is an order of magnitude faster.

SQL standard compliance: PostgreSQL implements the standard more completely and rigorously.

Storage: PostgreSQL uses heap tables for primary tables, allowing larger data volumes than MySQL’s index‑organized tables.

Replication: PostgreSQL uses physical replication, offering more reliable consistency and higher performance than MySQL’s binlog‑based logical replication.

Concurrency: MySQL’s default repeatable read isolation cannot prevent common concurrent updates without locking, whereas PostgreSQL’s built‑in version column provides optimistic‑lock‑like behavior under repeatable read .

Disadvantages of PostgreSQL compared to MySQL

System catalog design is more complex, making certain statistics and operations harder.

Index selection can be trickier; PostgreSQL lacks a MySQL‑style force_index hint.

Requires periodic VACUUM maintenance, which must be tuned to the workload.