Choose PostgreSQL for a new project unless your framework, host or team already runs MySQL. PostgreSQL has the richer SQL (RETURNING, FULL OUTER JOIN, MERGE, partial indexes, schema changes inside transactions), a binary jsonb type with GIN indexes, arrays, and extensions such as PostGIS. The SELECT, JOIN and INSERT you write every day are nearly the same in both.
| PostgreSQL 17 | MySQL 8.4 | |
|---|---|---|
| License | PostgreSQL License (permissive) | GPLv2 Community, commercial editions from Oracle |
| Return changed rows | RETURNING | No |
| Upsert | INSERT ... ON CONFLICT, MERGE | INSERT ... ON DUPLICATE KEY UPDATE |
FULL OUTER JOIN | Yes | No |
| DDL inside a transaction | Rolls back | Commits the transaction |
| JSON | json and jsonb, GIN indexes | JSON, indexed through generated columns or functional indexes |
| Arrays, partial indexes | Yes | No |
| Default isolation | Read committed | Repeatable read |
| Default text comparison | Case-sensitive | Case-insensitive (utf8mb4_0900_ai_ci) |
Get the new row back after an insert
RETURNING gives back the rows an INSERT, UPDATE or DELETE touched, so you get the generated id without a second query:
It returns both rows, with ids 1 and 2. MySQL has no RETURNING. LAST_INSERT_ID() gives the first id generated by the last insert:
INSERT INTO users (email) VALUES ('asha@example.com');
SELECT LAST_INSERT_ID();
Insert or update in one statement
PostgreSQL names the conflicting key and reads the incoming row as excluded:
A-1 becomes 8 and B-2 is inserted with 7. The MySQL 8.4 form uses a row alias (the older VALUES(qty) function is deprecated):
INSERT INTO stock VALUES ('A-1', 3), ('B-2', 7) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;
The upsert page covers DO NOTHING and conditional updates.
Joins MySQL does not have
A FULL OUTER JOIN keeps unmatched rows from both sides. MySQL emulates it with a LEFT JOIN and a RIGHT JOIN combined by UNION.
free has no signups, team has no price, and both still appear, with NULL in the missing column.
Roll back a schema change
In PostgreSQL, CREATE, ALTER and DROP are part of the transaction, so a failed migration leaves no half-applied schema:
Only orders.id is left. In MySQL each DDL statement commits the open transaction, so a migration tool cannot undo the statements before the one that failed.
Store and query JSON
PostgreSQL's jsonb stores parsed JSON, and a GIN index serves its containment (@>) and key (?) tests. MySQL's JSON type is also binary. You query it with ->> paths and index one path at a time, through a generated column or a functional index:
SELECT name FROM users WHERE profile->>'$.plan' = 'pro';
The JSONB page shows the PostgreSQL operators.
Concurrent reads and writes
Both use multiversion concurrency control (MVCC), so readers do not block writers. PostgreSQL writes a new row version on every update and leaves the old one in the table until VACUUM reclaims it. Autovacuum does this in the background, and tables with heavy update traffic depend on it keeping up. InnoDB updates rows in place and keeps old versions in undo logs, which a purge thread clears.
The default isolation differs. PostgreSQL runs at read committed: each statement sees the data committed before it started. MySQL runs at repeatable read: every plain read in a transaction sees the same snapshot. Its UPDATE, DELETE and locking reads also lock the index ranges they scan (next-key locks).
Replication, failover and tools
PostgreSQL ships streaming replication, which keeps a physical copy of the whole server that can serve reads, and logical replication of chosen tables through publications and subscriptions. Automatic failover needs an external tool. MySQL replicates through its binary log and offers Group Replication, the base of InnoDB Cluster.
For clients, PostgreSQL has psql and pgAdmin; MySQL has the mysql client, MySQL Shell and MySQL Workbench. Both run as managed services on AWS, Google Cloud and Azure.
Port MySQL habits to PostgreSQL
| MySQL | PostgreSQL |
|---|---|
`order` | "order" |
AUTO_INCREMENT | GENERATED ALWAYS AS IDENTITY |
IFNULL(a, b) | COALESCE(a, b) |
LIMIT 20, 10 | LIMIT 10 OFFSET 20 |
WHERE name = 'ana' matches Ana | WHERE name ILIKE 'ana' |
TINYINT(1) | boolean |
Frequently Asked Questions
Is PostgreSQL faster than MySQL?
Neither is faster in general. The result depends on the workload, the schema, the indexes and the server settings, so benchmark your own queries on your own data before choosing for speed.
Is PostgreSQL harder to learn than MySQL?
The SQL you write every day (SELECT, JOIN, INSERT, UPDATE) is almost the same in both. PostgreSQL is stricter about types and case-sensitive by default, so some queries that MySQL accepts need an explicit cast or ILIKE.
Can I move a MySQL database to PostgreSQL?
Yes. Tools such as pgloader copy the schema and data. Then the queries need porting: backticks become double quotes, AUTO_INCREMENT becomes an identity column, ON DUPLICATE KEY UPDATE becomes ON CONFLICT, and IFNULL becomes COALESCE.
Are PostgreSQL and MySQL free?
Both are free to use. PostgreSQL is under the PostgreSQL License, a permissive license with no paid edition. MySQL Community Edition is GPLv2, and Oracle sells commercial editions with extra features and support.