Menu

PostgreSQL vs MySQL: Differences and Which to Choose

PostgreSQL and MySQL are both free relational databases. PostgreSQL has the richer SQL, JSONB and transactional DDL; MySQL is simpler to run where it is already the default.

This page includes runnable editors. Edit, run, and see output instantly.

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 17MySQL 8.4
LicensePostgreSQL License (permissive)GPLv2 Community, commercial editions from Oracle
Return changed rowsRETURNINGNo
UpsertINSERT ... ON CONFLICT, MERGEINSERT ... ON DUPLICATE KEY UPDATE
FULL OUTER JOINYesNo
DDL inside a transactionRolls backCommits the transaction
JSONjson and jsonb, GIN indexesJSON, indexed through generated columns or functional indexes
Arrays, partial indexesYesNo
Default isolationRead committedRepeatable read
Default text comparisonCase-sensitiveCase-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

MySQLPostgreSQL
`order`"order"
AUTO_INCREMENTGENERATED ALWAYS AS IDENTITY
IFNULL(a, b)COALESCE(a, b)
LIMIT 20, 10LIMIT 10 OFFSET 20
WHERE name = 'ana' matches AnaWHERE 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.

Coddy programming languages illustration

Learn to code with Coddy

Get started