MySQL vs PostgreSQL: The Differences That Actually Change Your Design

The short answer

Both are mature, open-source, ACID-compliant relational databases, and almost every feature-checklist difference from a decade ago has closed — MySQL has window functions and CTEs, PostgreSQL has decent replication. What is left is architectural, and architecture is what bites you in year two:

• MySQL/InnoDB keeps old row versions in a separate undo log and stores the table itself inside the primary key index. A background purge thread cleans up.
• PostgreSQL keeps old row versions in the table, in an unordered heap, and relies on VACUUM to reclaim them. Indexes are always separate structures.

Everything else — why Postgres tables bloat, why a wide MySQL primary key inflates every index, why Postgres can roll back a schema migration and MySQL cannot — falls out of those two sentences. Licensing differs too: PostgreSQL ships under the permissive PostgreSQL Licence, MySQL Community Server under GPLv2 with Oracle offering a commercial licence alongside it.

Here are the five differences that actually change a design decision.

1. MVCC: undo logs vs in-table row versions

MVCC — multi-version concurrency control — is how both engines let a reader see a consistent snapshot of the data while a writer is changing it. Neither blocks the other. They achieve it in opposite ways.

InnoDB updates the row in place and pushes the previous version into an undo log stored in undo tablespaces. A reader with an older snapshot walks backwards through those undo records to reconstruct the row as it looked. Once no transaction can still need a given old version, background purge threads discard it — innodb_purge_threads defaults to 4.

PostgreSQL does not have an undo log. An UPDATE writes an entirely new copy of the row into the table and marks the old copy dead. The dead copy occupies its page until VACUUM reclaims the space. This is bloat: a table where you have updated every row ten times contains eleven versions of each row until something cleans up.

Autovacuum is on by default and does the cleaning, but its default trigger is worth doing arithmetic on. A table is vacuumed when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × live_tuples, and those default to 50 and 0.2.

Worked example: the 10-million-row orders table

You run an order pipeline. orders holds 10 million live rows, and each order gets its status column updated four times as it moves through the workflow — say 400,000 updates an hour at peak.

The autovacuum trigger for that table is 50 + 0.2 × 10,000,000 = 2,000,050 dead tuples. At 400,000 updates an hour you reach it in five hours. For those five hours the heap grows: two million dead row versions, at whatever your row width is, spread across pages that also hold live rows. Every sequential scan now reads those pages. Every index still has entries pointing at dead tuples.

Then autovacuum fires. autovacuum_max_workers defaults to 3 and autovacuum_naptime to 1 minute, so if two other large tables are already being vacuumed, yours waits. When it does run it reads the whole table and its indexes — a large I/O burst during peak traffic, because peak traffic is exactly what triggered it.

The fix is a per-table override, not a global one:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

Now the trigger is 1000 + 0.01 × 10,000,000 = 101,000 dead tuples: roughly every fifteen minutes, in small cheap passes instead of one five-hourly storm. The default of 0.2 is tuned for small tables; on large hot tables it is almost always wrong.

Warning

PostgreSQL transaction IDs are 32 bits — about 4 billion values — so they wrap around. To prevent that, autovacuum_freeze_max_age defaults to 200 million transactions, and when a table's oldest unfrozen ID hits that age an anti-wraparound vacuum is forced even if you disabled autovacuum on the table. Disabling autovacuum does not avoid vacuum; it only defers it to the worst possible moment.

InnoDB has the same root hazard with a different symptom. A long-running read transaction holds a snapshot, purge cannot discard undo records newer than that snapshot, and the undo tablespace grows. You watch the history list length instead of dead tuple counts, but the cause is identical: an old open transaction. On both engines, the reporting query someone left running in a psql or mysql session over lunch is the number one cause of storage incidents.

2. Storage layout: clustered index vs heap

InnoDB stores the table data itself inside a B+tree ordered by the primary key — the clustered index. The leaf pages are the rows. If you declare no primary key, InnoDB uses the first UNIQUE index whose columns are all NOT NULL; failing that it invents a hidden 6-byte row ID you cannot query.

The consequence people miss: every secondary index entry stores the primary key columns as its pointer back to the row. So a UUID primary key stored as a 36-character string is copied into every entry of every secondary index, and a lookup by a secondary index that needs a non-indexed column costs two B+tree descents — one to find the PK, one to fetch the row.

PostgreSQL has no clustered index. Rows sit in an unordered heap and every index, primary key included, is a separate structure pointing at a heap tuple ID. CLUSTER physically reorders a table once, and the ordering decays as soon as you write to it again.

That symmetry costs Postgres something on index-only scans. An index-only scan answers a query from the index alone without touching the table — but the index does not record whether a row version is visible to your transaction. Postgres consults the visibility map, a bitmap marking pages where every row is visible to everyone. If the page is not marked all-visible, it fetches the heap tuple anyway. VACUUM maintains that map, so index-only scan performance depends on how recently the table was vacuumed. An InnoDB covering index has no equivalent dependency.

Where Postgres pulls ahead is index variety: it ships six access methods — B-tree, Hash, GiST, SP-GiST, GIN and BRIN — plus partial and expression indexes. InnoDB gives you B+tree, FULLTEXT and SPATIAL, with functional and descending indexes added in MySQL 8.0.

For capacity arithmetic: InnoDB's default page is 16KB (innodb_page_size = 16384); PostgreSQL's default block is 8KB, fixed at compile time.

3. Isolation defaults differ — and that changes your code

	MySQL / InnoDB	PostgreSQL
Default isolation	REPEATABLE READ	READ COMMITTED
Lock wait timeout	innodb_lock_wait_timeout = 50s	lock_timeout = 0 (disabled)
Statement timeout	—	statement_timeout = 0 (disabled)

Same BEGIN, different semantics. On MySQL, two SELECTs in one transaction return the same snapshot. On PostgreSQL, the second one sees rows committed in between. Code written against one default and moved to the other does not error — it just behaves differently, which is worse.

Under REPEATABLE READ, InnoDB uses next-key locks for locking reads: a lock on the index record plus a gap lock on the gap before it, blocking inserts into ranges you scanned. That prevents phantom rows and produces deadlock patterns Postgres developers never encounter. PostgreSQL's answer to the same problem is SERIALIZABLE via Serializable Snapshot Isolation, which does not block — it aborts. A transaction can fail with a serialization_failure (SQLSTATE 40001), and your application must retry it. That is not an edge case to handle later; it is the contract.

Note the timeouts too. A blocked statement on MySQL gives up after 50 seconds. On PostgreSQL, with both timeouts disabled by default, it waits forever. Set statement_timeout and lock_timeout per-role or per-connection before you go live.

4. Transactional DDL

PostgreSQL runs schema changes inside transactions. This is real, and it changes how you write migrations:

BEGIN;
ALTER TABLE orders ADD COLUMN region text;
UPDATE orders SET region = 'IN' WHERE country_code = 'IN';
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ROLLBACK;  -- schema and data both back to where they started

Swap ROLLBACK for COMMIT and either all three steps land or none do. BEGIN covers DDL as well as DML.

MySQL cannot do this. CREATE TABLE, ALTER TABLE and DROP TABLE cause an implicit commit: they commit whatever transaction is open and cannot themselves be rolled back. MySQL 8.0 added atomic DDL, which makes a single statement's data-dictionary update, storage-engine work and binlog write all-or-nothing — a real improvement over half-created tables after a crash — but a DDL statement still cannot join a multi-statement transaction.

So a three-statement migration that fails on statement two leaves MySQL with statement one applied and no way back. That is why the MySQL ecosystem grew forward-only migrations and online schema-change tools like gh-ost and pt-online-schema-change, while Postgres shops wrap whole migrations in one transaction.

Common mistake

Assuming a Postgres migration inside BEGIN is therefore safe to run at any time. It is atomic, but ALTER TABLE still takes a heavy lock, and holding it in a long transaction blocks every reader and writer for the duration. Atomic is not the same as non-blocking — set lock_timeout and retry.

5. Replication: logical events vs physical WAL

MySQL writes logical events to the binary log — in MySQL 8.0 binary logging is on by default and binlog_format defaults to ROW, which has been the default since 5.7.7. Replicas replay those events. Replication is asynchronous by default; semisynchronous needs the rpl_semi_sync plugins installed. The built-in consensus option is Group Replication, which underpins InnoDB Cluster.

PostgreSQL streams the physical write-ahead log — the byte-level record of page changes — to standbys, which replay it block for block. wal_level defaults to replica and max_wal_senders to 10. Logical replication via publications and subscriptions arrived in PostgreSQL 10 and is what you use for selective replication and major-version upgrades.

The durability defaults deserve a careful read, because "synchronous" means different things:

Setting	Default	What it guarantees
innodb_flush_log_at_trx_commit	1	Redo log flushed to disk at each commit
sync_binlog (8.0)	1	Binlog synced to disk before each commit
synchronous_commit	on	Local WAL flushed before commit returns
synchronous_standby_names	empty	No standby is synchronous

synchronous_commit = on sounds like it means "wait for the replica". It does not — it means wait for the local disk. Cross-node synchronous replication only happens when synchronous_standby_names names at least one standby. Out of the box, both engines can lose recently committed transactions on a failover.

Neither ships automatic failover in core Postgres — Patroni, repmgr or a managed service supplies it. MySQL bundles more of the story via MySQL Shell and Router with InnoDB Cluster. This is the same "the runtime is one thing, orchestrating it is another" split you meet in Docker versus Kubernetes or in Kafka's rebalance protocol: the data plane is solved, the control plane is a choice you make.

Types, dialect and how to choose

PostgreSQL's type system is the reason a lot of teams move: first-class array columns, composite types, range and multirange types (multiranges since 14), native UUID and INET, and user-defined types. It offers two JSON types: json stores the input text exactly and reparses on every use; jsonb stores a decomposed binary form that is slower to write, faster to query, and indexable with GIN. MySQL's JSON is binary, and MySQL 8.0.17 added multi-valued indexes for indexing array elements inside it.

Two dialect traps worth memorising. MySQL only started enforcing CHECK constraints in 8.0.16 — before that they were parsed and silently ignored, so an inherited schema may have constraints that were never actually applied. And MySQL's legacy utf8mb3 (long aliased as utf8) holds at most three bytes per character and cannot store emoji; utf8mb4 can, and became the default in 8.0 with collation utf8mb4_0900_ai_ci — 5.7 defaulted to latin1. Postgres sets encoding per database.

Identifiers differ quietly: PostgreSQL folds unquoted identifiers to lower case — MyTable becomes mytable unless you double-quote it — while MySQL's table-name case sensitivity depends on lower_case_table_names and the filesystem underneath.

Extensibility is the other axis. Postgres extensions install into a database with CREATE EXTENSION and can add types, operators and whole index access methods — PostGIS, pgvector, pg_stat_statements. MySQL's extensibility runs through pluggable storage engines (InnoDB is the default since 5.5) and the plugin/component APIs.

Choose MySQL for high-throughput primary-key lookup workloads, existing LAMP/WordPress estates, and teams who already know how to operate it. Choose PostgreSQL for complex queries, rich types and JSONB, geospatial, analytics-adjacent work, and anything that wants an extension. If you are picking infrastructure to sit around it, the same evaluate-the-operational-cost logic applies as when choosing between Lambda and EC2.

If you are considering migrating an existing MySQL system, budget for the parts that are not features: mapping AUTO_INCREMENT to identity columns, cleaning zero-dates and other data that only survived under lax sql_mode, and rewriting INSERT ... ON DUPLICATE KEY UPDATE as INSERT ... ON CONFLICT DO UPDATE. The real cost of switching is operational knowledge — knowing which metric predicts your next outage — not the feature list.

Interview tip

Interviewers ask this as "why do PostgreSQL tables bloat?" The answer that lands: because an UPDATE writes a new row version into the heap rather than into a separate undo area, dead versions stay in the table until VACUUM reclaims them. InnoDB avoids in-table bloat by pushing old versions to the undo log — and pays for it with a growing undo tablespace when purge is blocked by a long transaction.