MySQL vs PostgreSQL: Overview, Performance Benchmark, and Use‑Case Guidance

This article provides a concise introduction to two popular open‑source relational databases, MySQL and PostgreSQL, outlining their histories, licensing, and architectural differences.

MySQL is described as the most widely used open‑source RDBMS, originally developed by MySQL AB, later acquired by Sun and then Oracle, resulting in commercial and community editions.

PostgreSQL is presented as a highly advanced open‑source ORDBMS originating from the University of California, Berkeley, fully community‑driven and released under a permissive BSD/MIT license.

Note: MySQL hierarchy: instance → database → table PostgreSQL hierarchy: instance → database → schema → table (schema is a namespace that does not affect usage).

Performance Comparison

The benchmark was conducted on identical hardware (4‑core CPU, 16 GB RAM) using MySQL 8.0 and PostgreSQL 13. The test workload consisted of SELECTs by primary key, UPDATEs by primary key, and single‑row INSERTs.

MySQL:
    - 硬件配置：4核心 16GB内存
    - 版本：MySQL 8.0

Postgres SQL:
    - 硬件配置：4核心 16GB内存
    - 版本：Postgres SQL 13

Key findings:

PostgreSQL delivers roughly twice the SELECT throughput of MySQL and 4–5× higher INSERT throughput; UPDATE performance is 5–6× better.

Average latency for all operations is several times lower on PostgreSQL.

For hot‑row updates, MySQL’s performance drops to about one‑eighth of PostgreSQL’s, with latency increasing sevenfold.

Suitable Scenarios and Selection Guidance

MySQL is praised for its simplicity, extensive documentation, and broad ecosystem, making it a good fit for small‑to‑medium projects, e‑commerce sites, blogs, and applications where ultra‑high performance is not critical.

PostgreSQL excels in complex data models, advanced features, and large‑scale workloads; it is recommended for applications requiring sophisticated queries, high concurrency, and robust replication, though it may be less convenient for certain index‑forcing tricks.

Advantages of PostgreSQL over MySQL

Significantly better performance in both latency and throughput.

HOT UPDATE provides an order‑of‑magnitude gain for single‑row updates.

More complete and standards‑compliant SQL implementation.

Heap‑based storage allows larger data volumes.

Physical replication offers higher data consistency and lower impact on the primary.

Built‑in optimistic‑lock version column simplifies concurrent updates.

Disadvantages of PostgreSQL compared to MySQL

System catalog design is more complex, making certain administrative tasks harder.

Index selection can be less straightforward, lacking MySQL’s FORCE_INDEX hint.

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

Overall, the choice between MySQL and PostgreSQL should be driven by the specific performance requirements, data complexity, and operational considerations of the project.