MySQL vs PostgreSQL: In‑Depth Feature Comparison and Choosing the Right Database

Overview

This document compares the technical characteristics of MySQL and PostgreSQL, focusing on licensing, transaction guarantees, SQL compliance, replication, concurrency control, performance, high‑availability tooling, external data integration, and storage architecture.

1. Open‑source licensing

PostgreSQL is released under a permissive BSD/MIT license, allowing unrestricted copying, modification, and redistribution with only a copyright notice required. MySQL is distributed under the GPL (with a commercial Oracle‑owned edition); modifications that are distributed must be open‑sourced under the same license.

2. ACID support

PostgreSQL provides full ACID compliance for all storage engines. MySQL achieves ACID only with the InnoDB engine; other engines lack full transactional guarantees, and MySQL’s dual‑mode configuration (e.g., “dual‑primary”) can affect consistency.

3. SQL standard compliance

PostgreSQL implements almost the entire SQL standard and offers a rich set of native data types (including JSON, JSONB, XML, HSTORE, and PostGIS extensions). MySQL supports a subset of the standard, resulting in fewer built‑in types and limited advanced features.

4. Replication

MySQL uses binary‑log‑based asynchronous logical replication; true synchronous replication is not available. PostgreSQL supports synchronous, asynchronous, and semi‑synchronous replication, as well as logical replication that enables table‑level publish/subscribe and integration with streaming platforms such as Kafka.

Common replication topologies for both systems include single‑master, multi‑master, cascading, circular, and logical replication.

5. Concurrency control

PostgreSQL employs MVCC based on transaction IDs (XIDs). Old and new row versions coexist in the heap, requiring periodic VACUUM to reclaim space; this can generate additional I/O and lock overhead, especially when long‑running transactions prevent vacuuming. MySQL’s InnoDB also uses MVCC but relies on rollback segments rather than a global vacuum, avoiding the VACUUM step. However, InnoDB’s gap locks can increase contention under certain workloads.

6. Performance characteristics

PostgreSQL excels in workloads with complex queries, mixed OLTP/OLAP patterns, and large‑scale data warehousing (e.g., Greenplum). It benefits from advanced optimizer features, parallel query execution, and extensive index types. MySQL performs well for simple, read‑heavy web transactions but may experience degradation under heavy analytical loads or intricate join operations.

7. High‑availability implementations

PostgreSQL options include streaming replication (async/sync), tools such as repmgr, Patroni + etcd, and middleware like pgpool, pgcluster, and Slony. MySQL provides binlog‑based master‑slave and master‑master setups, MHA, LVS + KEEPALIVE, InnoDB Cluster (available from MySQL 8.0), and middleware such as Mycat.

8. External data sources

PostgreSQL supports Foreign Data Wrappers (FDW) to query external systems (Oracle, Hadoop, MongoDB, SQL Server, Excel, CSV, etc.) as foreign tables. MySQL lacks a comparable built‑in mechanism.

9. Data storage and table types

PostgreSQL stores data in heap tables, allowing large datasets and flexible row layout similar to Oracle. MySQL uses index‑organized tables that require a primary key; secondary index lookups involve two passes (primary‑key lookup followed by secondary index scan).

10. Extensibility and ecosystem

PostgreSQL’s extension framework enables add‑ons such as PostGIS (geospatial), JSONB indexing, and custom procedural languages. Its source code is under a permissive license, facilitating commercial redistribution without licensing constraints. MySQL’s plugin architecture allows multiple storage engines (InnoDB, MyISAM, etc.) but introduces additional complexity in concurrency handling.