PostgreSQL Outperforms MySQL 2-6x in High-Performance Benchmarks: Architectural Reasons

The article compares MySQL 8.0 and PostgreSQL 13 on identical hardware (4 cores, 16 GB RAM) using primary-key SELECT, UPDATE, and single-row INSERT workloads. Results show PostgreSQL achieves double the SELECT throughput, 4-5x INSERT throughput, and 5-6x UPDATE throughput. Hot-row updates are particularly striking: MySQL manages only about 1/8 of PostgreSQL's performance, with latency increasing 7-fold.

Architectural Differences Driving Performance

Storage model: PostgreSQL uses heap tables; MySQL uses index-organized tables (clustered indexes). Heap tables allow PostgreSQL to support larger data volumes and avoid secondary-index lookups for non-primary-key columns.

HOT UPDATE: PostgreSQL's Heap-Only Tuples (HOT) optimization avoids index updates when non-indexed columns change, giving an order-of-magnitude advantage on single-row updates.

Replication: PostgreSQL employs physical (WAL-based) replication, which provides stronger consistency, higher replication performance, and lower primary-node overhead compared to MySQL's logical binlog replication.

Transaction isolation & optimistic locking: MySQL's REPEATABLE READ cannot prevent common concurrent-update anomalies without explicit locking (pessimistic) or manual optimistic-lock implementation. PostgreSQL's REPEATABLE READ includes a hidden version column per row, delivering correct concurrent updates with optimistic-lock performance by default.

Use-Case Guidance

MySQL suits simpler applications (e-commerce, blogs, small-to-medium systems) where ecosystem maturity and lower learning curve matter. It handles up to tens/hundreds of millions of rows but struggles under high throughput/low-latency demands.

PostgreSQL fits complex data structures, advanced applications, and large-scale datasets. The author notes one caveat: PostgreSQL's query planner occasionally picks a suboptimal index, and unlike MySQL's FORCE INDEX, there is no straightforward hint to override it.

PostgreSQL Advantages Summary

Significantly higher throughput and lower latency across read/write workloads.

HOT UPDATE provides order-of-magnitude gains on single-row updates.

More complete and rigorous SQL-standard implementation.

Heap-table architecture supports larger data volumes.

Physical replication offers better consistency, performance, and lower primary impact.

Built-in hidden version column enables correct, performant optimistic locking at default isolation.

PostgreSQL Drawbacks

System catalog complexity makes metadata queries and operations more involved.

Query planner chooses wrong index more often in practice; no FORCE INDEX equivalent.

Requires tuning of VACUUM parameters for specific workloads.