Part 12 · 2 chapters · ~12 min
Expert Mode
Postgres against MySQL argued in both directions with mechanism, the war stories (wraparound, bloat, the autovacuum that never ran, the slot that filled the disk), and where Postgres is going with asynchronous and direct IO and pluggable table access methods.
31
Postgres vs MySQL, both directions
| argument for Postgres | mechanism | argument for MySQL | mechanism |
|---|---|---|---|
| richer types and indexes | extensible catalogue: GIN, GiST, BRIN, ranges, JSONB, PostGIS | cheaper primary key lookups and range scans by PK | clustered index: the row is in the PK leaf |
| transactional DDL | catalogue changes are MVCC rows; roll back a migration | no vacuum operations to tune | undo logs and purge clean up versions |
| a smarter planner for complex SQL | merge join, extended statistics, parallel query | update-heavy tables bloat less | updates in place; secondary indexes unchanged unless their columns change |
| per-transaction durability | synchronous_commit per transaction | cheap connections | threads, not processes |
| logical decoding with plugins | pgoutput, wal2json | mature large-scale sharding tooling | Vitess |
32
War stories, and where Postgres is going
| story | cause | lesson |
|---|---|---|
| wraparound shutdown | an abandoned replication slot held the xmin horizon for months; nobody alerted on age(datfrozenxid) | alert on xid age and slot lag; set max_slot_wal_keep_size |
| the 400 GB table holding 40 GB of data | default autovacuum scale factor on a hot update table; vacuum never caught up | per-table autovacuum settings; pg_repack to recover |
| the autovacuum that never ran | an idle-in-transaction connection from a pooled app since last Tuesday | idle_in_transaction_session_timeout |
| the slot that filled the disk | a CDC connector stopped consuming; WAL grew until the primary went read-only | slot lag alerts, retention caps, runbooks |
| the migration that took the site down | ALTER TABLE queued behind a long query (part 10) | lock_timeout on every migration |
Where it is going: an asynchronous IO subsystem (18) with io_uring on Linux, ongoing work toward direct IO, pluggable table access methods (columnar and undo-based storage experiments such as OrioleDB), better logical replication (sequences, DDL, failover slots), and continued planner work such as skip scans. The architecture's two big costs, vacuum and processes, are where most of the innovation is aimed.