Teams usually compare PostgreSQL and MySQL by feature lists: JSON support, window functions, extensions. Those gaps have mostly closed. What has not changed is the architecture underneath, and that is what shows up in production: how many connections you can hold, why one table bloats and another does not, which primary key choice hurts inserts, and how replicas fall behind.

We run both in production, PostgreSQL 18 for most services and MySQL 8.4 LTS for a few legacy ones, and we have migrated workloads in both directions. This post compares four areas that matter most to application developers and explains what each difference changes in practice.

Connections: processes versus threads

PostgreSQL forks a dedicated operating system process for every client connection. The postmaster accepts the connection and hands it to a new backend process that lives until the client disconnects. Each backend has private memory for sorting, hashing and catalog caches, and they coordinate through shared memory.

MySQL runs as a single process and serves each connection with a thread. Threads share the address space, so an idle connection costs less memory, and creating one is cheaper.

What this changes for applications:

  • Connection counts. A PostgreSQL server with a few hundred connections is normal; a few thousand is a problem, both in memory and in internal overhead that grows with the number of backends. MySQL tolerates more idle connections, though thousands of active ones still hurt.
  • Pooling. With PostgreSQL, an external pooler such as PgBouncer is close to mandatory once you run many app instances or serverless functions. With MySQL, application-side pools are often enough until much larger scale, and ProxySQL fills the same role when they are not.
  • Isolation of failures. A crashing PostgreSQL backend is a separate process, though the postmaster still restarts all backends to protect shared memory. In MySQL a fatal error in any thread takes the whole server down.

PostgreSQL 18 did not change the process model, but its new asynchronous I/O subsystem (io_method) moves some read I/O into worker processes or io_uring, which helps sequential scans and vacuum on fast storage.

MVCC: new row versions versus undo logs

Both databases use multi-version concurrency control so readers do not block writers. They store old versions very differently.

PostgreSQL writes a new version of the row (a new tuple) into the table on every UPDATE, and marks the old one as expired. Readers decide which version to see using the transaction ids stored in each tuple. Old versions stay in the table until VACUUM removes them.

InnoDB updates the row in place and writes the previous version into the undo log. A reader that needs an older version reconstructs it by following undo records. A background purge thread discards undo records no longer needed by any transaction.

Aspect PostgreSQL MySQL InnoDB
Where old versions live In the table itself In undo tablespaces
Cleanup VACUUM, usually autovacuum Purge threads
Cost of an update New tuple, plus index entries unless it is a HOT update In-place change plus undo record
Symptom of long transactions Table and index bloat Undo growth (history list length)

What this changes for applications:

  • Update-heavy tables need care in PostgreSQL. A counter row updated thousands of times per second creates thousands of dead tuples. Tune autovacuum per table, keep indexed columns out of frequent updates so HOT updates apply, and consider a lower fillfactor.
  • Long transactions hurt both, differently. In PostgreSQL an idle transaction prevents vacuum from cleaning up anything newer, so every busy table bloats. In MySQL the history list length grows and reads slow down as they walk longer undo chains.
  • Transaction id wraparound is a PostgreSQL-only concern. Autovacuum must freeze old tuples before the 32-bit transaction counter wraps; monitoring age(datfrozenxid) is part of running PostgreSQL.

Defaults differ too: PostgreSQL uses READ COMMITTED, InnoDB uses REPEATABLE READ. The same application code can behave differently under concurrency on the two engines, so test with the engine you deploy on.

Storage: clustered index versus heap

InnoDB stores the table as a B-tree ordered by the primary key, the clustered index. The row data lives in the leaf pages. Every secondary index stores the primary key value as its pointer, so a lookup through a secondary index finds the primary key and then descends the clustered index.

PostgreSQL stores rows in a heap, in no particular order. Every index, including the primary key, is a separate structure that points to a physical tuple location (block and offset). There is no table ordered by primary key, though CLUSTER can reorder a table once.

What this changes for applications:

  • Primary key choice matters more in MySQL. A random primary key such as a UUIDv4 causes page splits throughout the clustered index and bloats every secondary index, since each one stores the full key. Sequential or time-ordered keys keep inserts at the right edge. PostgreSQL also benefits from ordered keys in the primary key index, but the table itself is unaffected.
  • Range scans on the primary key are cheap in InnoDB because rows are physically together. In PostgreSQL, rows inserted at different times may be spread across the heap.
  • Wide primary keys cost more in InnoDB, because they are copied into every secondary index. A bigint key is usually the better choice than a composite natural key.
  • Index-only scans exist in both. PostgreSQL needs the visibility map to be current, which depends on vacuum. InnoDB secondary indexes include the primary key columns automatically, which makes some queries covering for free.

PostgreSQL 18 also added a native uuidv7() function, which generates time-ordered UUIDs. That narrows the gap for teams that want UUIDs without random insert patterns, on either engine.

Replication: WAL versus binlog

PostgreSQL writes every change to the write-ahead log (WAL), which describes physical changes to pages. Streaming replication ships WAL to standbys that replay it, producing a byte-identical copy of the whole cluster. Logical replication, built on logical decoding of the WAL, publishes row changes for selected tables to subscribers, which can run a different major version.

MySQL has two logs. InnoDB keeps its own redo log for crash recovery, and the server writes a binary log (binlog) of changes for replication. With the default row-based format, replicas apply row events. GTIDs give each transaction a global id, which makes failover and re-pointing replicas much simpler. Group Replication and InnoDB Cluster add consensus-based multi-node setups.

Aspect PostgreSQL MySQL
Default replica type Physical, whole cluster Logical row events, per server
Replica can differ in schema or version Only with logical replication Yes, within limits
Built-in consensus HA No, use Patroni or similar Group Replication
Replica writes Read-only Possible, but discouraged

What this changes for applications:

  • Replica lag shapes differ. A PostgreSQL standby replays one WAL stream and can be paused by conflicts with long queries on the standby. A MySQL replica applies transactions with a configurable number of parallel workers and lags when large transactions or tables without primary keys slow row lookups.
  • Tables without primary keys are a real problem for MySQL row-based replication, since each row event may need a full scan on the replica. In PostgreSQL logical replication they need REPLICA IDENTITY FULL and suffer the same way.
  • Major upgrades. PostgreSQL physical replicas must run the same major version, so upgrades use pg_upgrade or logical replication. MySQL replicas can run a newer version than the source, which allows rolling upgrades.
  • Change data capture. Both are well supported by tools such as Debezium, through logical decoding in PostgreSQL and the binlog in MySQL.

Picking with these differences in mind

Neither engine is better in general. A short guide from our own workloads:

  • Many short-lived connections from serverless or very wide fleets: MySQL is more forgiving; PostgreSQL works well with a pooler.
  • Heavy update churn on a few hot rows: InnoDB's in-place updates are easier to operate; PostgreSQL needs vacuum tuning.
  • Complex queries, rich types, extensions such as PostGIS or pgvector: PostgreSQL.
  • Primary-key-ordered access and simple read scaling with built-in clustering: MySQL with InnoDB Cluster.

Key takeaways

  • PostgreSQL uses a process per connection, MySQL a thread per connection; plan pooling accordingly.
  • PostgreSQL keeps old row versions in the table and relies on vacuum; InnoDB uses undo logs and purge.
  • InnoDB tables are clustered on the primary key, so key choice affects every index; PostgreSQL tables are heaps.
  • PostgreSQL replicates physical WAL by default, MySQL replicates logical binlog events, which changes lag behaviour and upgrade paths.
  • Long transactions and missing primary keys hurt both engines, in different ways.

References