MySQL vs PostgreSQL: Which Open‑Source Database Wins for Your Projects?

Overview

This article introduces a feature‑by‑feature comparison of MySQL and PostgreSQL, aiming to help readers understand the strengths and weaknesses of each and make a more informed choice. In China, MySQL enjoys broader adoption, while PostgreSQL’s newer capabilities may win over new users.

1. Open Source Licensing

PostgreSQL is released under the permissive BSD/MIT license, allowing anyone to use, copy, modify, and redistribute the code with only a copyright notice. This flexibility lets companies sell PostgreSQL‑based products without extensive code changes.

MySQL is distributed under the GPL or a commercial license owned by Oracle. The GPL permits free use but forbids releasing derived code as closed‑source software; Oracle also offers a commercial Enterprise edition with additional features.

2. ACID Support

PostgreSQL fully implements ACID properties, providing strong transactional consistency.

MySQL supports ACID only through the InnoDB engine; other storage engines lack full transactional guarantees. MySQL can be tuned (e.g., double‑write mode) to improve durability, but its ACID support is generally considered weaker than PostgreSQL’s.

3. SQL Standard Compatibility

PostgreSQL adheres to almost the entire SQL standard and offers a rich set of data types.

MySQL implements only a subset of the standard, resulting in more limited type support.

4. Replication

MySQL uses binlog‑based asynchronous replication, which cannot provide true synchronous replication. Common replication modes include:

Master‑slave

Master‑multiple slaves

Cascade replication

Ring (circular) replication

Master‑master

Data‑flow advantages can be achieved by subscribing to binlog changes with Canal and forwarding them to Kafka.

PostgreSQL supports synchronous, asynchronous, semi‑synchronous, and logical replication, enabling table‑level publish/subscribe. Typical replication modes are:

Master‑slave

Master‑multiple slaves

Cascade replication

Hot standby / streaming

Logical replication

Logical replication also allows direct data streaming to Kafka.

5. Concurrency Control

PostgreSQL relies on an MVCC implementation based on XIDs, storing old and new rows together. This requires periodic VACUUM to reclaim space, which can generate extra I/O, lock overhead, and table bloat; pending transactions may further delay vacuuming.

MySQL’s InnoDB also uses MVCC, but its gap‑locking mechanism can cause more extensive row locking under certain workloads.

6. Performance

PostgreSQL excels in large‑scale systems that demand high read/write speed and strong consistency. It offers advanced performance features (e.g., geospatial support, no read‑lock concurrency) and performs well on complex queries, OLTP/OLAP workloads, data‑warehouse scenarios, and BI applications.

MySQL is a popular choice for web‑centric projects with simple transactional needs. It delivers solid read/write performance for OLTP, handles high concurrency well, and integrates reliably with BI tools, though it may struggle with heavy loads or complex queries.

7. High‑Availability Implementations

PostgreSQL options include:

Streaming replication (async/sync)

Keepalive

repmgr

Patroni + etcd

Shared‑storage HA (corosync + pacemaker)

Postgres‑XC / Postgres‑XL

Middleware such as pgpool, pgcluster, slony, plproxy

MySQL options include:

Master‑slave and master‑master replication

MHA

LVS + KEEPALIVE

MGR distributed DB (Paxos‑based)

PXC distributed DB (token‑ring protocol)

InnoDB Cluster (MySQL 8.0)

Middleware such as Mycat

8. External Data Sources

PostgreSQL’s Foreign Data Wrapper (FDW) extensions allow it to treat external systems (Oracle, Hadoop, MongoDB, SQL Server, Excel, CSV, etc.) as foreign tables, facilitating seamless integration with big‑data ecosystems.

MySQL provides no comparable built‑in mechanism.

9. Storage Engine & Data Types

PostgreSQL stores data in heap tables, similar to Oracle, supporting large volumes.

MySQL uses an index‑organized table model that requires a primary‑key index; secondary‑index lookups involve two passes (primary‑key then secondary index).

10. Comparative Advantages

PostgreSQL advantages over MySQL

More complete SQL‑standard implementation

Advanced stored‑procedure capabilities with plan caching

Richer join support and optimizer features

Supports a wider variety of index types

Physical replication offers higher consistency and lower performance impact

No proprietary storage‑engine lock complexities

Extensive external‑data‑source support via FDW

Native logical replication enables Kafka‑style data streaming

Three join algorithms (nested loop, hash, merge)

Cleaner, more readable source code

PostGIS extension for geospatial data

Highly extensible with many community‑contributed extensions

Robust JSON/JSONB support with indexing

BSD license allows unrestricted commercial use

MySQL advantages over PostgreSQL

InnoDB’s MVCC based on rollback segments avoids the overhead of PostgreSQL’s VACUUM process

Index‑organized tables excel at primary‑key lookups

Simpler optimizer suited for straightforward queries

Higher domestic popularity in China

Pluggable storage‑engine architecture (e.g., MyISAM for static data, InnoDB for transactions)

Conclusion

Both open‑source databases have trade‑offs; commercial solutions like Oracle remain more feature‑complete. PostgreSQL is better suited for strict enterprise scenarios (finance, telecom, ERP, CRM) and workloads that benefit from advanced data types (JSONB, PostGIS) and big‑data analysis. MySQL shines in simpler, high‑traffic internet applications where ease of use and widespread adoption are paramount. The author’s personal experience informs this comparison, and feedback is welcomed.