MySQL vs PostgreSQL

Two good engines, two dialects

Both engines are excellent, free, ACID and MVCC based, so readers do not block writers. Both give you foreign keys, triggers, stored procedures, views, CTEs, window functions, JSON and replication, so almost everything from chapter 1 to chapter 13 applies to both. This is not a "which is better" chapter: companies run both happily for decades. The differences live in four places, and this chapter is one table for each: vocabulary, defaults, capabilities and operations.

The translation table

This is the most useful thing in the chapter. Read the left column as what you say at work, and the middle as what a PostgreSQL colleague means. Bookmark it, and when you write a request for the other team, use their words.

What each engine can do

This table condenses chapters 7 to 13. "No" rarely means impossible; it usually means you emulate the feature, and the AI can write that for you. Read it to see which requests are one line on one engine and a small project on the other. PostgreSQL leads on what the database can do to itself, MySQL on what ships in the box.

The last row shapes design work. InnoDB stores every table sorted by its primary key, so a random UUID key scatters inserts across the whole table. PostgreSQL keeps rows in a heap and does not care.

Defaults that surprise you

Most porting bugs come from defaults: things one engine does silently and the other does differently. They matter more than missing features because they produce wrong numbers instead of error messages. MySQL's old reputation for accepting bad data is out of date, and strict mode has been the default since 5.7. Plain lower_snake_case table names avoid the identifier trap in both engines, and they keep a dump from a Linux server loading cleanly on your Windows one.

SELECT id, name FROM customers WHERE name = 'amira';
-- MySQL:      1 row (Amira), because the collation ignores case
-- PostgreSQL: 0 rows

-- PostgreSQL ways to get the MySQL behavior:
SELECT id, name FROM customers WHERE name ILIKE 'amira';
SELECT id, name FROM customers WHERE lower(name) = 'amira';

Running them: the operational differences

The SQL is nearly the same. The weekly chores are where you feel the gap.

Grafana and Percona Monitoring and Management cover both engines, which helps when you inherit a mixed estate. Every major cloud sells both, and a managed service takes backups, patching and failover off your plate. Everything else here stays yours. Linux advice also needs translating on Windows: there is no postgres OS user, so sudo -u postgres psql becomes psql -U postgres, and both engines run their scheduled jobs from Task Scheduler. See chapter 4 and chapter 5.

Porting SQL from one to the other

The same report written for both engines usually differs in three or four small places. Hand the AI the SQL, name both engines with versions, and ask it to list every assumption it made. Then run the result on both servers and compare row counts.

-- MySQL
SELECT c.name, GROUP_CONCAT(o.id ORDER BY o.id) AS order_ids
FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.name;

-- PostgreSQL: same rows, including Dev with no orders
SELECT c.name, STRING_AGG(o.id::text, ',' ORDER BY o.id) AS order_ids
FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.name;

Where to read more

• MySQL releases: Innovation and LTS

  How the 8.4, 9.7 and 26.x lines relate.

• PostgreSQL versioning policy

  Which majors are supported, and until when.

• MySQL Server SQL modes

  Strict mode, ANSI_QUOTES and PIPES_AS_CONCAT.

• PostgreSQL routine vacuuming

  Dead tuples, autovacuum and wraparound.