MySQL 8 vs PostgreSQL 10: Which Database Wins the Showdown?

Introduction

MySQL 8 and PostgreSQL 10 have been released, narrowing the long‑standing gap between the two open‑source relational databases. Both now support many modern SQL features and provide comparable replication capabilities.

Feature Comparison

Common Table Expressions (CTEs) and window functions, once exclusive to PostgreSQL, are fully supported in MySQL 8. PostgreSQL’s logical replication has been enhanced to allow zero‑downtime upgrades by creating a new cluster and switching over. MySQL 8 adds a similar logical replication feature that simplifies partition truncation and upgrade paths.

Key Differences

Process vs Thread Model

PostgreSQL creates a separate operating‑system process for each client connection, typically consuming up to 10 MiB of memory per process. MySQL uses a thread‑based model with a default 256 KiB stack on 64‑bit platforms. At high connection counts (e.g., >1 000 concurrent connections) the process model can become a dominant factor in capacity planning.

Clustered Index vs Heap Storage

InnoDB (MySQL) stores rows in a clustered primary‑key B‑tree, so a single I/O retrieves the entire row. PostgreSQL stores rows in a heap; the primary key is an index that points to the heap tuple, requiring an extra index lookup. PostgreSQL lacks native clustered indexes, but ample shared buffers can mitigate the extra I/O.

Page Structure and Compression

MySQL’s default page size is 16 KB; PostgreSQL uses 8 KB pages. PostgreSQL employs TOAST (a shadow table) to move large column values off‑page and compress them. MySQL offers transparent page compression that leverages SSD‑aware techniques and sparse files (supported by ext4, btrfs, etc.).

Update Overhead (MVCC)

Both systems implement Multi‑Version Concurrency Control. PostgreSQL keeps old row versions in the heap until a VACUUM removes them. It also provides HOT (Heap‑Only Tuple) optimization to avoid index updates when possible, but frequent large updates can cause tuple bloat beyond the 8 KB page size. MySQL performs in‑place updates and moves old versions to a separate rollback segment, which a dedicated purge thread later cleans. This design eliminates the need for a VACUUM‑like process but introduces a purge thread that reclaims space.

Garbage Collection

PostgreSQL’s VACUUM competes for CPU and I/O on the heap, making it costly on tables with billions of rows. MySQL’s purge thread runs independently and generally does not impact read concurrency, keeping performance more predictable under heavy write workloads.

Logging and Replication

PostgreSQL uses a Write‑Ahead Log (WAL) for durability and logical replication. MySQL maintains two distinct logs: the InnoDB redo log for crash recovery and the binary log for replication and incremental backup. Both databases now support comparable asynchronous and semi‑synchronous replication configurations.

Conclusion

The analysis confirms that PostgreSQL still excels at analytical and append‑only workloads, especially when extended with TimescaleDB, which can ingest up to 1 million rows per second per server. MySQL has closed many feature gaps, but its process‑vs‑thread model, clustered‑index storage, and separate purge mechanism remain key differentiators.

Using UUIDs as primary keys can degrade locality and increase index size, leading to poorer performance.

PostgreSQL + TimescaleDB enables high‑throughput time‑series ingestion (≈1 M rows / s / server) and is the engine behind Amazon Redshift.