PostgreSQL vs MySQL: Real Differences, Tested Side by Side

PostgreSQL vs MySQL is the most common database decision in web development, and most comparisons answer it with a feature checklist. Both databases tick most of the same boxes now, so the checklist tells you little. The differences that matter show up when you run the same SQL against both and read what comes back.

The short verdict: choose PostgreSQL for a new application unless you have a specific reason not to. It is stricter about your data, its schema changes are transactional, and its SQL and extension ecosystem go further. Choose MySQL when your team, hosting, or tooling is already built around it, when you want Vitess-style sharding, or when you run software that expects it, such as WordPress. MySQL is a good database. PostgreSQL is the safer default.

Every example below was run in Docker against postgres:18 (PostgreSQL 18.6) and mysql:8.4 (MySQL 8.4.11) with default settings. I reran the MySQL examples on MySQL 9.7.2, the LTS release from April 2026, and the output was identical.

PostgreSQL vs MySQL on schema changes

This is the difference I would put first, because it decides what happens when a migration fails halfway through.

PostgreSQL runs DDL inside transactions. MySQL commits implicitly before and after most DDL statements, so CREATE TABLE, ALTER TABLE, and CREATE INDEX cannot be rolled back.

Here is a migration that adds a column and then a unique index. The table already contains a duplicate email, so the second step fails. In PostgreSQL:

CREATE TABLE users (id int PRIMARY KEY, email text);
INSERT INTO users VALUES (1, 'ada@example.com'), (2, 'ada@example.com');

BEGIN;
ALTER TABLE users ADD COLUMN plan text DEFAULT 'free';
CREATE UNIQUE INDEX users_email_key ON users (email);
COMMIT;

ERROR:  could not create unique index "users_email_key"
DETAIL:  Key (email)=(ada@example.com) is duplicated.
ROLLBACK

The COMMIT turns into a ROLLBACK, and \d users shows only id and email. The plan column never happened.

The same migration in MySQL, wrapped in START TRANSACTION and followed by ROLLBACK:

ERROR 1062 (23000) at line 6: Duplicate entry 'ada@example.com' for key 'users.users_email_key'
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   | PRI | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
| plan  | varchar(20)  | YES  |     | free    |       |
+-------+--------------+------+-----+---------+-------+

The plan column stayed. The database is now half migrated, and the migration tool has to work out how to clean up. It gets stranger: in MySQL, START TRANSACTION; CREATE TABLE invoices (...); INSERT INTO invoices ...; ROLLBACK; leaves both the table and the row behind, because the CREATE TABLE committed and ended the transaction before the insert ran.

MySQL teams work around this with small, one-statement migrations and online schema change tools. PostgreSQL teams wrap each migration in a transaction and move on.

Type strictness and silent conversion

Both databases reject bad data on insert in their default configuration. MySQL 8.4 ships with STRICT_TRANS_TABLES in its default sql_mode, so the old stories about MySQL truncating strings are mostly about older setups. Inserting a 15-character code into a varchar(10) fails in both:

PostgreSQL: ERROR:  value too long for type character varying(10)
MySQL:      ERROR 1406 (22001): Data too long for column 'code' at row 1

The strictness gap shows up in expressions, where MySQL still converts types to make a query succeed. Take a coupons table with codes WELCOME and SPRING, and a bug where the application passes 0 instead of a code:

SELECT code FROM coupons WHERE code = 0;

PostgreSQL refuses to compare text with an integer:

ERROR:  operator does not exist: character varying = integer
LINE 1: SELECT code FROM coupons WHERE code = 0;
                                            ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

MySQL converts each code to a number. Non-numeric strings become 0, so every row matches:

+---------+
| code    |
+---------+
| SPRING  |
| WELCOME |
+---------+
Warning | 1292 | Truncated incorrect DOUBLE value: 'SPRING'
Warning | 1292 | Truncated incorrect DOUBLE value: 'WELCOME'

The warnings only appear if you run SHOW WARNINGS, and most drivers never do. The same pattern holds elsewhere:

Strict mode is also a session setting in MySQL, and some applications and older frameworks still turn it off. With SET SESSION sql_mode = '', the oversized insert succeeds:

Warning | 1265 | Data truncated for column 'code' at row 1
Warning | 1366 | Incorrect integer value: 'lots' for column 'uses' at row 1

| BLACKFRIDA |    0 |

BLACKFRIDAY2026 was stored as BLACKFRIDA, and 'lots' became 0. PostgreSQL has no mode that allows this.

Case and accent sensitivity

MySQL 8's default collation is utf8mb4_0900_ai_ci, which is accent insensitive (ai) and case insensitive (ci). PostgreSQL compares strings exactly by default. This changes query results and unique constraints:

INSERT INTO customers (name, email) VALUES ('Zoë Müller', 'zoe@example.com');
SELECT name FROM customers WHERE name = 'zoe muller';
INSERT INTO customers (name, email) VALUES ('Zoe', 'ZOE@example.com');

MySQL finds Zoë Müller and rejects the second email as a duplicate:

| Zoë Müller   |
ERROR 1062 (23000) at line 5: Duplicate entry 'ZOE@example.com' for key 'customers.email'

PostgreSQL returns zero rows for the search and accepts both emails as distinct values.

Neither default is wrong. MySQL's behaviour suits user-facing search and email uniqueness. PostgreSQL's suits identifiers, tokens, and anything where a and A differ. Each database can do the other's job. In PostgreSQL, a nondeterministic ICU collation makes a column case insensitive:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE TABLE accounts (email text COLLATE case_insensitive UNIQUE);
INSERT INTO accounts VALUES ('zoe@example.com');
INSERT INTO accounts VALUES ('ZOE@example.com');

ERROR:  duplicate key value violates unique constraint "accounts_email_key"
DETAIL:  Key (email)=(ZOE@example.com) already exists.

In MySQL, use a _bin or _as_cs collation on columns that need exact matching. The risk is in moving between them without noticing: a query ported from MySQL to PostgreSQL can return fewer rows with no error.

Identifiers differ too. PostgreSQL folds unquoted names to lower case, so a table created as "Orders" fails with ERROR: relation "orders" does not exist when queried as Orders. Keep table names lower case in PostgreSQL and the problem disappears.

Upserts and RETURNING

Both databases have an atomic insert-or-update. The syntax differs, and PostgreSQL returns the result in the same statement:

-- PostgreSQL
INSERT INTO stock (sku, qty) VALUES ('KB-01', 3)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty
RETURNING sku, qty;

  sku  | qty
-------+-----
 KB-01 |   8

-- MySQL 8.0.19 and later
INSERT INTO stock (sku, qty) VALUES ('KB-01', 3) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

If you learned the older VALUES(qty) form, MySQL 8.4 still runs it but warns:

Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

MySQL has no RETURNING clause and no MERGE, so you read the row back with a second query. Both statements fail with a syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNING sku' at line 1

PostgreSQL 17 and 18 extend MERGE with RETURNING and merge_action(), which tells you what happened to each row:

 merge_action |  sku  | qty
--------------+-------+-----
 UPDATE       | KB-01 |  10
 INSERT       | MS-02 |   7

Our PostgreSQL ON CONFLICT guide covers the upsert syntax in more depth.

JSON support

Both databases store and index JSON, and both handle typical document columns well. PostgreSQL's jsonb type with a GIN index supports containment queries across any key:

CREATE INDEX events_payload_gin ON events USING gin (payload);

SELECT id, payload->>'plan' AS plan
FROM events
WHERE payload @> '{"tags":["beta"]}';

MySQL indexes JSON through generated columns, functional indexes, and multi-valued indexes for arrays. You declare which path to index up front:

CREATE TABLE events (
  id bigint AUTO_INCREMENT PRIMARY KEY,
  payload json NOT NULL,
  INDEX tags_idx ((CAST(payload->'$.tags' AS CHAR(20) ARRAY)))
);

SELECT id, payload->>'$.plan' AS plan
FROM events
WHERE 'beta' MEMBER OF (payload->'$.tags');

EXPLAIN on the MySQL query shows key: tags_idx and type: ref, so the index is used. The practical difference: one GIN index in PostgreSQL covers ad hoc queries on any key, while MySQL needs an index per path you plan to query. PostgreSQL also has JSON path queries (jsonb_path_query) and a richer set of operators. If JSON is a large part of your data, PostgreSQL is the stronger choice. For a comparison with a document database, see MongoDB vs PostgreSQL.

Constraints, booleans, and dates

MySQL ignored CHECK constraints until 8.0.16. Current versions enforce them, and both databases rejected a negative price:

PostgreSQL: ERROR:  new row for relation "products" violates check constraint "products_price_check"
            DETAIL:  Failing row contains (KB-01, -5.00).
MySQL:      ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.

Advice to "avoid CHECK constraints in MySQL" is out of date. Name your constraints in both databases so the error is readable.

Booleans are a real type in PostgreSQL. In MySQL, BOOLEAN is an alias for tinyint(1):

mysql> CREATE TABLE flags (enabled boolean);
mysql> INSERT INTO flags VALUES (5);
mysql> SELECT * FROM flags;
+---------+
| enabled |
+---------+
|       5 |
+---------+

PostgreSQL rejects the same insert with ERROR: column "enabled" is of type boolean but expression is of type integer.

MySQL's TIMESTAMP type stops at 2038-01-19 03:14:07 UTC. Inserting a session expiry in 2040 fails:

ERROR 1292 (22007): Incorrect datetime value: '2040-01-01 00:00:00' for column 'expires_at' at row 1

Use DATETIME for dates past 2038 in MySQL. PostgreSQL's timestamptz accepted the value. Our guides to PostgreSQL data types and MySQL data types list the rest.

SQL features one side lacks

MySQL 8 closed much of the gap by adding window functions, common table expressions, and CHECK constraints. Several things are still PostgreSQL only. Each of these failed with ERROR 1064 in MySQL 8.4 and 9.7:

• FULL OUTER JOIN (emulate it with a UNION of left and right joins)
• Partial indexes, such as CREATE INDEX ... WHERE uses = 0
• RETURNING on INSERT, UPDATE, and DELETE
• MERGE

PostgreSQL also has arrays, range types, exclusion constraints, and transactional DDL. Version 18 added uuidv7() for time-ordered UUIDs and made generated columns virtual by default. MySQL's UUID() returns a version 1 UUID.

MySQL has a few things PostgreSQL does not: UNSIGNED integer types, a choice of storage engines, and index-organised tables by default (InnoDB clusters rows by primary key, which makes primary key range scans cheap).

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

Concurrency and connections

Both use multiversion concurrency control, so readers do not block writers. The defaults differ. PostgreSQL uses read committed, where each statement sees data committed before it started. MySQL's InnoDB uses repeatable read, where a transaction sees a consistent snapshot from its first read:

PostgreSQL: SHOW default_transaction_isolation;  -> read committed
MySQL:      SELECT @@transaction_isolation;      -> REPEATABLE-READ

The storage design differs too. PostgreSQL writes a new row version on every update and cleans up old versions with VACUUM. Autovacuum handles this, but update-heavy tables need tuning. InnoDB updates rows in place and keeps old versions in undo logs, which avoids table bloat but can build up long history lists under long-running transactions.

PostgreSQL starts a new operating system process for every connection. MySQL uses a thread per connection. Both need pooling at scale, but PostgreSQL needs it sooner: applications with hundreds of short-lived connections, such as serverless functions, should put PgBouncer or a managed pooler in front of PostgreSQL.

Extensions and ecosystem

Extensions are PostgreSQL's biggest advantage. The official postgres:18 image lists 46 available extensions before you install anything, and third-party ones add geospatial queries (PostGIS), vector search (pgvector), time series (TimescaleDB), and sharding (Citus). Hosted platforms such as Supabase and Neon offer many of these as one-line installs.

MySQL's equivalents are thinner. MySQL 9.x added a VECTOR column type, and on 9.7.2 it stored and returned vectors correctly. The DISTANCE() function used for similarity search was not there:

ERROR 1305 (42000) at line 5: FUNCTION shop.DISTANCE does not exist

MySQL's documentation says DISTANCE() is available only in MySQL HeatWave on OCI and MySQL AI, not in the Community or Commercial distributions. Vector similarity search on self-hosted MySQL is not a practical option today. MySQL 8.4 does not recognise the VECTOR type at all.

Replication and scaling

Both databases scale reads with replicas and handle large single-server workloads. The scaling stories diverge at write sharding.

MySQL has built-in asynchronous replication, Group Replication, and InnoDB Cluster for high availability. For horizontal sharding, Vitess (the system behind PlanetScale's MySQL product) is mature and runs some of the largest MySQL deployments. TiDB offers a MySQL-compatible distributed database as another route.

PostgreSQL has streaming replication and logical replication built in. For sharding, Citus distributes tables across nodes, and distributed PostgreSQL-compatible databases exist, which we compare in CockroachDB vs PostgreSQL.

Most applications never need write sharding. A single well-indexed server of either database handles more than most products will see.

Performance

There is no general winner, and I have not run a benchmark that would justify claiming one. Published benchmarks tend to measure the tuning of the person who ran them. Some structural points hold:

• InnoDB's clustered primary key makes primary key lookups and range scans efficient, which suits simple read-heavy web workloads.
• PostgreSQL's planner handles complex joins, subqueries, and analytical queries well, and version 18 added asynchronous I/O and skip scans on multicolumn B-tree indexes.
• Both are usually slow for the same reasons: missing indexes, bad queries, and too many connections.

If you have a slow database now, start with how to fix slow Postgres queries or how to fix slow MySQL queries before considering a switch.

Licensing and stewardship

PostgreSQL uses the PostgreSQL License, a permissive licence similar to MIT. No company owns it, and development is run by the PostgreSQL Global Development Group. A new major version ships each autumn and is supported for five years.

MySQL is owned by Oracle. The Community Edition is GPLv2, and Oracle sells an Enterprise Edition with extra features and support. Since 2023, MySQL has shipped LTS releases (8.4 in April 2024, 9.7 in April 2026) alongside short-lived innovation releases. MySQL 8.0 reached end of life in April 2026, so anyone still running it should plan an upgrade. The GPL matters mainly if you distribute MySQL inside a product. If ownership by Oracle is a concern, MariaDB is the community fork, compared in MySQL vs MariaDB.

Hosting

Both run on every major cloud: Amazon RDS and Aurora, Google Cloud SQL, and Azure Database for PostgreSQL and MySQL. PostgreSQL has more serverless and developer platforms (Neon, Supabase, and others), while MySQL has PlanetScale's Vitess product and TiDB Cloud. For AWS specifics, see Aurora vs RDS. For local development, both run in one Docker command, covered in Postgres in Docker and MySQL with Docker Compose.

When to choose PostgreSQL

• You are starting a new application and have no constraint pulling you elsewhere.
• Data integrity matters more than tolerance of sloppy input.
• You want migrations that roll back cleanly when they fail.
• You need JSON queries, geospatial data, vector search, or another extension.
• Your queries include reporting, complex joins, or window functions over large sets.

When to choose MySQL

• You run software built for it: WordPress, many PHP applications, or an existing codebase.
• Your team knows MySQL operations well, and that knowledge is worth more than PostgreSQL's features.
• You plan to shard with Vitess or PlanetScale.
• Your workload is simple primary key reads and writes at high volume.
• Case-insensitive matching by default suits your data.

If you are weighing either against Microsoft's database, see MySQL vs SQL Server and PostgreSQL vs SQL Server. For other MySQL-compatible options, see MySQL alternatives.

Migrating from MySQL to PostgreSQL

Tools such as pgloader move schema and data across. The data copy is the easy part. The work is in the behaviour differences above:

1. Audit string comparisons. Queries that relied on case-insensitive matching return fewer rows in PostgreSQL. Add ICU collations, citext, or lower() indexes where needed.
2. Fix implicit casts. Comparisons like code = 0 now fail, which is good, but each one needs a code change.
3. Replace ON DUPLICATE KEY UPDATE with ON CONFLICT, and tinyint(1) with boolean.
4. Replace AUTO_INCREMENT with identity columns and reset sequences after the data load.
5. Add connection pooling before production traffic arrives.

Run your test suite against PostgreSQL early. The errors it throws are the bugs MySQL was hiding.

The verdict

For a new project, PostgreSQL is the better default. The differences in this post all point the same way: PostgreSQL refuses ambiguous input, rolls back failed migrations, and gives you more SQL and more extensions to grow into.

MySQL remains a reasonable choice, and on MySQL 8.4 or 9.7 with strict mode on, it is far better than its reputation from the 5.x era. Pick it when the ecosystem around your project already runs on it.

Whichever you choose, a good client makes the differences easier to see. DB Pro connects to both, and you can read more on the PostgreSQL client and MySQL client pages.