PostgreSQL vs MySQL: the differences that matter

Architecture: processes and threads

PostgreSQL uses what its documentation calls a "process per user" model. A supervisor process accepts each new connection and spawns a dedicated backend process for it.

MySQL associates each client connection with a thread inside a single server process, and keeps a thread cache so that threads are reused. A thread is cheaper to create and holds less private state than a process. A true thread pool, where a small number of threads serve many connections, exists as a plugin in MySQL Enterprise Edition but not in the Community Edition.

The practical consequence is that PostgreSQL needs a connection pooler sooner. Thousands of mostly idle connections are costly in PostgreSQL, so production deployments normally put PgBouncer or a similar pooler in front. MySQL tolerates more direct connections, although a pooler such as ProxySQL still helps at high counts. See How do I fix "FATAL: sorry, too many clients already" in PostgreSQL?.

Storage: heap with row versions, or clustered index with undo

This is the largest difference between the two, and most performance and maintenance differences follow from it.

PostgreSQL stores a table as a heap: rows go wherever there is free space, in no particular order. Every index, including the primary key index, holds pointers to physical row locations. An UPDATE does not overwrite the row. It writes a complete new row version and marks the old one as expired. Old versions stay in the table until no transaction can still see them, then VACUUM (normally autovacuum) marks their space reusable. See How PostgreSQL MVCC and VACUUM actually work.

InnoDB stores the table inside the primary key index. The leaf pages of that B+tree hold the full rows in primary key order. Secondary indexes store the indexed columns plus the primary key value, so a secondary index lookup that needs other columns makes a second descent through the clustered index. An UPDATE modifies the row in place and writes the previous values to an undo log. Readers that need the older version rebuild it from undo. Background purge threads discard undo records once no transaction needs them. See InnoDB and the clustered primary key.

What follows from this:

• Primary key lookups and primary key range scans read one structure in InnoDB and two (index, then heap) in PostgreSQL, unless PostgreSQL can use an index-only scan.
• Primary key choice matters more in InnoDB. A wide or random key enlarges every secondary index and fragments the table.
• PostgreSQL tables and indexes can bloat when vacuum falls behind, and transaction ID wraparound has to be prevented by freezing. InnoDB has the equivalent problem in a different place: a long-running transaction stops purge, the undo history grows and reads slow down.

Transactions and isolation levels

Both databases implement multiversion concurrency control, so ordinary reads do not block writes and writes do not block reads. The defaults differ.

First, under InnoDB's default, a transaction that runs the same plain SELECT twice sees the same data even if another session committed in between. Under PostgreSQL's default, the second SELECT sees the new data.

Second, InnoDB's snapshot applies to plain SELECT statements and not necessarily to writes. The MySQL manual states that an UPDATE or DELETE in a REPEATABLE READ transaction can affect rows committed by another transaction after the snapshot was taken. PostgreSQL's REPEATABLE READ refuses instead and raises a serialization error, which the application must catch and retry.

InnoDB's gap locks are also a common source of lock waits and deadlocks on range updates and on inserts into the same index range. Many MySQL deployments switch to READ COMMITTED to reduce them. How do I fix "Lock wait timeout exceeded" goes through the diagnosis.

Schema changes

PostgreSQL DDL is transactional. You can create a table, alter two others and backfill data in one transaction, and a failure rolls all of it back:

BEGIN;
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
CREATE INDEX orders_shipped_at_idx ON orders (shipped_at);
ROLLBACK;  -- neither change exists

A few commands cannot run inside a transaction block, notably CREATE INDEX CONCURRENTLY, which is the one you use on a busy table.

In MySQL, each DDL statement is atomic since 8.0: it either completes or leaves nothing behind, and the data dictionary, storage engine and binary log stay consistent. The manual is explicit that atomic DDL is not transactional DDL. Any DDL statement implicitly commits the open transaction, so a migration with three ALTER TABLE statements that fails on the third leaves the first two applied.

MySQL is stronger at changing large tables in place. InnoDB online DDL reports for every operation whether it can run as ALGORITHM=INSTANT (metadata only), INPLACE (no table copy, concurrent writes usually permitted) or COPY. Adding a column is instant. PostgreSQL also adds a column with a constant default without rewriting the table, and builds indexes without blocking writes using CONCURRENTLY, but several changes, such as most column type changes, rewrite the table under an ACCESS EXCLUSIVE lock. How do I add an index or column to a big PostgreSQL table without downtime? lists the safe patterns.

Data types and SQL features

Both support recursive CTEs, window functions, generated columns, foreign keys, CHECK constraints, table partitioning and SELECT ... FOR UPDATE SKIP LOCKED. The differences are at the edges.

JSON. PostgreSQL has two types. json stores the text as given. jsonb stores a decomposed binary form and is the one to use, because it supports GIN indexes that answer containment queries (@>) on any key without declaring the keys in advance. MySQL's JSON type is also stored in a binary format that allows direct lookup of keys. It is not indexed directly. You index a specific path through a generated column or a functional key part, or index the elements of a JSON array with a multi-valued index.

Materialized views. PostgreSQL stores the result of a query as a materialized view and recomputes it with REFRESH MATERIALIZED VIEW, optionally CONCURRENTLY so readers are not blocked. Refresh is always a full recomputation, not incremental. MySQL has no equivalent statement.

Index types

A partial index declared with WHERE deleted_at IS NULL or WHERE status = 'pending' stays small and can enforce uniqueness over a subset of rows, and MySQL has no equivalent. An invisible index lets you test whether dropping an index is safe and restore it instantly if a query regresses, and PostgreSQL has no equivalent.

When an index exists and the planner still does not use it, the reasons are similar on both sides. See Why is my query still slow when the column has an index?.

Replication and high availability

PostgreSQL has two built-in mechanisms. Physical streaming replication ships the write-ahead log to standbys, which replay it to stay byte-for-byte identical to the primary. It covers the whole cluster, requires the same major version, and standbys can serve read-only queries. It can be asynchronous or synchronous, and standbys can cascade. Logical replication publishes row changes for chosen tables to subscribers, which can run a different major version and have their own indexes. Its restrictions matter: schema changes and DDL are not replicated, and sequence data is not replicated either.

PostgreSQL does not include failover management. The documentation says it "does not provide the system software required to identify a failure on the primary and notify the standby". That role is filled by a tool such as Patroni, or by a managed service.

MySQL replication is based on the binary log, which records row changes in commit order. Because it is logical, replicas can run a newer version. Global transaction identifiers (GTIDs) give every transaction a unique ID across the topology, which makes repointing a replica to a new source straightforward. Replication is asynchronous by default, with semisynchronous replication available as a plugin, and replicas apply transactions with multiple worker threads by default.

On top of that, Group Replication provides a group of servers that agree on transaction order by majority and fail over automatically, in single-primary or multi-primary mode. InnoDB Cluster packages Group Replication with MySQL Shell for administration and MySQL Router for client routing. This is a complete high availability stack shipped by the vendor, which PostgreSQL does not have in core.

Extensions and ecosystem

PostgreSQL's extension system lets third parties add data types, operators, index access methods, procedural languages and foreign data wrappers without forking the server. PostGIS (geospatial), pgvector (vector similarity search), pg_stat_statements (query statistics), Citus (distributed tables) and TimescaleDB (time series) are all extensions loaded with CREATE EXTENSION.

MySQL extends through plugins, components and pluggable storage engines. Its ecosystem is strongest around operations at scale: Vitess for sharding, ProxySQL for routing and pooling, gh-ost and pt-online-schema-change for schema changes on very large tables, and Percona XtraBackup for physical backups.

Licensing and governance

PostgreSQL is released under the PostgreSQL License, a permissive license similar to BSD and MIT. No company owns the project. The PostgreSQL Global Development Group ships a major version about once a year and supports each for five years.

MySQL is owned and developed by Oracle. The Community Edition is licensed under GPLv2, and Oracle sells commercial licenses and an Enterprise Edition with additional features such as the thread pool. The GPL places conditions on distributing software, so the license question arises mainly when you ship MySQL itself as part of a product, which is the case Oracle sells commercial licenses for. Releases follow two tracks: LTS series (8.4, then 9.7) that receive only fixes, and Innovation releases with new features. MariaDB and Percona Server are separately governed forks and builds.

Tooling

For reading PostgreSQL plans, see EXPLAIN ANALYZE in PostgreSQL: reading plans without guessing. For MySQL's legacy three-byte utf8 alias, see Why does MySQL say "Incorrect string value" when I save an emoji?.

Decision summary

1. With no existing constraints, choose PostgreSQL. Transactional DDL, partial and GIN indexes, jsonb, richer types and extensions reduce the number of other systems you need.
2. If your team already runs MySQL competently, stay on MySQL. Operational knowledge is worth more than any feature in this comparison.
3. If the workload is mostly primary-key reads and writes at very high volume, InnoDB's clustered index and in-place updates fit it well.
4. If you want vendor-supplied automatic failover without extra software, MySQL's InnoDB Cluster provides it. With PostgreSQL, plan for Patroni or a managed service.
5. If you expect to shard, compare the paths: Vitess on MySQL, Citus on PostgreSQL.
6. On either, set the isolation level deliberately, keep transactions short and plan connection pooling early.

References

• PostgreSQL: How Connections Are Established
• PostgreSQL: Transaction Isolation
• PostgreSQL: Index Types
• PostgreSQL: JSON Types
• PostgreSQL: Logical Replication Restrictions
• PostgreSQL: Failover
• PostgreSQL License and Versioning Policy
• MySQL 8.4: Connection Interfaces
• MySQL 8.4: InnoDB Multi-Versioning
• MySQL 8.4: Transaction Isolation Levels and Consistent Nonlocking Reads
• MySQL 8.4: Atomic Data Definition Statement Support
• MySQL 8.4: Online DDL Operations
• MySQL 8.4: Group Replication
• MySQL 8.4: MySQL Releases: Innovation and LTS
• MySQL commercial licensing

Why is my query still slow when the column has an index?

The index exists, but the plan still shows a scan. Nine common reasons the optimizer can't (or shouldn't) use your index, and how to fix each one.

#execution-plans#indexing#performance