Loading...
System Design and AI Engineering, by Roni Das. 974 interactive lessons.
Start LearningThe short answerMySQL and PostgreSQL are both free, mature relational databases that run most apps well, but PostgreSQL can undo table changes inside a transaction, has richer JSON and starts at READ COMMITTED, while MySQL starts at REPEATABLE READ and ignores case when comparing text by default, so pick the one your team and host already know unless one of those differences matters to you.
MySQL (InnoDB engine), owned by Oracle
the web's long-time default
PostgreSQL, often called Postgres
the strict, extensible one
Read across a row to compare one thing. Every word that may be new is explained just below the table.
| Aspect | MySQL | PostgreSQL |
|---|---|---|
| Default isolation level | REPEATABLE READ (InnoDB). Our run: REPEATABLE-READ. | READ COMMITTED. Our run: read committed. |
| Rereading inside one transaction | Keeps showing the first snapshot. We read 100, then 100 again. | Sees other sessions' commits at once. We read 100, then 150. |
| Upsert syntax | INSERT ... AS new ON DUPLICATE KEY UPDATE col = new.col | INSERT ... ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col |
| CREATE TABLE inside a transaction | Commits the open transaction first. ROLLBACK cannot undo it. | Part of the transaction. ROLLBACK removes the table. |
| JSON | JSON type, JSON_CONTAINS(), ->> paths like '$.city'. | json and jsonb types, @> containment, GIN indexes, ->> keys. |
| Text comparison by default | utf8mb4_0900_ai_ci: case and accents ignored, so 'asha' = 'Asha'. | Case counts, so 'asha' does not match 'Asha'. Use ILIKE or citext. |
| Extensions | Storage engines and plugins. No extension system like Postgres. | CREATE EXTENSION: PostGIS for maps, pgvector for vectors, and more. |
| Licence | GPL version 2 for the Community edition (the LICENSE file in Oracle's mysql-server repository). | The PostgreSQL License, a liberal licence similar to BSD or MIT. |
| Version we tested | MySQL 9.7.1 (Homebrew) | PostgreSQL 18.6 (Homebrew). 18 first shipped 25 September 2025. |
Real situations, and the pick we would make in each one.
Stay where you are. A migration costs weeks and the two are close enough for most work.
WordPress asks for MySQL 8.0 or newer, or its fork MariaDB 10.11 or newer, and these hosts ship it ready to use.
In PostgreSQL, BEGIN; ALTER TABLE ...; ROLLBACK really undoes the change. In MySQL each DDL statement commits on its own.
jsonb with a GIN index answers 'which rows contain this' without scanning the table. MySQL can do JSON, but with fewer index options.
The PostGIS extension adds real geographic types and indexes, and it is the usual choice for this.
MySQL's default collation already ignores case. In PostgreSQL you add citext, lower() with an index, or a non-deterministic collation.
we ran this, here is what happened
Most comparisons list features. We wanted to see the differences with our own eyes, so we started a fresh MySQL and a fresh PostgreSQL on one laptop and gave them the same table and the same three rows.
Then we ran six checks: the default isolation level, what a transaction sees after another session commits, each one's upsert (and the other one's, to see the error), a table created and then rolled back, a JSON search, and a lower-case name search.
Every value below is what the server actually sent back. We ran the whole script twice and got the same answers both times.
Where it ran: Apple M4, macOS 15.6, MySQL 9.7.1 and PostgreSQL 18.6 from Homebrew, both with default settings, fresh data folders, Python 3.13 with PyMySQL 1.1.1 and psycopg 3.2.10. Two runs on 4 October 2026.
# session A reads twice inside ONE transaction; session B commits a change in between
ca.execute("START TRANSACTION") # BEGIN on PostgreSQL
ca.execute("SELECT balance FROM users WHERE id = 1")
first = ca.fetchone()[0]
cb.execute("UPDATE users SET balance = balance + 50 WHERE id = 1") # autocommit: committed now
ca.execute("SELECT balance FROM users WHERE id = 1")
second = ca.fetchone()[0]
# a table created inside a transaction, then rolled back
c.execute("START TRANSACTION")
c.execute("INSERT INTO users VALUES (9, 'Temp', 1, '{}')")
c.execute("CREATE TABLE audit_log (id INT)")
c.execute("ROLLBACK")"non_repeatable_read": {
"mysql": { "first_read": 100, "second_read_same_txn": 100, "after_commit": 150 },
"postgres": { "first_read": 100, "second_read_same_txn": 150, "after_commit": 150 }
},
"upsert": {
"mysql": { "postgres_syntax": { "ok": false, "error": "ProgrammingError: (1064, \"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 'CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance' at line 1\")" } },
"postgres": { "mysql_syntax": { "ok": false, "error": "SyntaxError: syntax error at or near \"AS\"" } }
},
"transactional_ddl": {
"mysql": { "table_exists_after_rollback": [[1]], "row_9_after_rollback": [[1]] },
"postgres": { "table_exists_after_rollback": [[0]], "row_9_after_rollback": [[0]] }
},
"collation": {
"mysql": { "match": [[1, "Asha"]], "collation": [["utf8mb4_0900_ai_ci"]] },
"postgres": { "match": [], "ilike": [[1, "Asha"]] }
}| What we measured | MySQL | PostgreSQL |
|---|---|---|
| Default isolation levelSELECT @@transaction_isolation / SHOW default_transaction_isolation | REPEATABLE-READ | read committed |
| Second read, same transaction, after another commitboth are correct for their level | 100 (old value) | 150 (new value) |
| Rows left after ROLLBACK of INSERT + CREATE TABLEMySQL committed the insert when CREATE TABLE ran | table kept, row kept | both gone |
| WHERE name = 'asha' (stored as 'Asha')ILIKE found it on PostgreSQL | 1 row | 0 rows |
| JSON rows containing {"plan": "pro"}same answer, different syntax | 2 (JSON_CONTAINS) | 2 (@>, used the GIN index) |
What this shows
The surprise was the rollback. In MySQL, CREATE TABLE committed the open transaction first, so ROLLBACK kept not just the table but the row we had inserted before it. PostgreSQL threw both away. Neither server was wrong anywhere in this lab; each did exactly what its manual says. The danger is assuming one behaves like the other.
What this test does not show: Three rows on one laptop. This lab shows behaviour, not speed: it says nothing about which one is faster for your app. Defaults can be changed in both (isolation level, collation), and managed clouds sometimes change them for you, so check your own server. The script is scripts/labs/compare/mysql_vs_postgresql.sh in our repository.
"PostgreSQL is faster" (or "MySQL is faster").
Speed depends on your queries, indexes, settings and hardware. Test your own workload. Most published races compare a tuned server with an untuned one.
Wrapping a migration in BEGIN ... ROLLBACK on MySQL and trusting it.
Every CREATE, ALTER or DROP TABLE commits on its own in MySQL. Plan migrations so each step is safe alone, or test them on a copy first.
Copying an upsert from a blog post written for the other database.
MySQL wants ON DUPLICATE KEY UPDATE; PostgreSQL wants ON CONFLICT. Our run got error 1064 on MySQL and a syntax error on PostgreSQL. Also, MySQL's VALUES(col) form still works but now raises a deprecation warning.
Assuming email lookups ignore case everywhere.
They do on MySQL's default collation and do not on PostgreSQL. Moving between them can create duplicate accounts or failed logins.
Thinking both default to the same isolation level.
MySQL starts at REPEATABLE READ and PostgreSQL at READ COMMITTED, so the same report run inside one transaction can show different numbers.
Neither wins in general. PostgreSQL has more features (transactional DDL, jsonb with GIN indexes, extensions like PostGIS). MySQL is simpler to find on cheap hosting and runs a huge share of existing PHP and WordPress sites. Pick the one your team knows unless you need a specific feature.
Yes. It is still released and supported (we tested MySQL 9.7.1), WordPress still asks for it (or MariaDB), and tools like Vitess exist to spread it across many servers. Its popularity with new projects has moved toward PostgreSQL, but that does not make existing MySQL systems obsolete.
Mostly features and licence. It can roll back schema changes, it has strong JSON support and an extension system (PostGIS, pgvector), and it uses the permissive PostgreSQL License. Many managed clouds now offer it as a first-class choice.
There is no honest single answer. It depends on the workload, the indexes and the settings. We did not run a speed test because three rows on one laptop would prove nothing. Benchmark your own queries on your own data.
No. SQL is the language. PostgreSQL and MySQL are two databases that understand it, each with its own extra syntax. That is why our PostgreSQL upsert failed on MySQL and the MySQL one failed on PostgreSQL.
Yes, and many teams do, but plan for real work: upserts, JSON queries, case-sensitive text matching and isolation behaviour all change, as our lab shows. Test every query against the new database before switching.
From the System Design course, in the order we would read them.
Primary sources only: standards, vendor docs and our own runs. Each one shows its date and the day we opened it.
MySQL vs PostgreSQL is one row in a much bigger table. Our System Design course has 770 lessons on networks, databases, caching, scaling, messaging, security and reliability, each drawn step by step, so you can explain the trade-off in an interview and pick right at work. 18 lessons are free to read, with no card needed.
the hands-on parts are real runs, like this one
course 1
From absolute beginner to principal engineer, drawn step by step.