MySQL powers an enormous chunk of the web. WordPress runs on it, the early LAMP stack was built around it, and countless SaaS apps still use it today. It's relational like Postgres, and almost everything you learned in the SQL lessons works here unchanged. So this profile is mostly about the differences.
What it is
MySQL is an open-source relational database, now owned by Oracle, with a fully open-source fork called MariaDB that many teams use as a drop-in replacement. Like Postgres, it's tables, rows, SQL, transactions, and indexes. Its default storage engine, InnoDB, provides the transactions and row-level locking you'd expect.
Where it shines
Functional · what it does
- Read-heavy web apps. Decades of tuning for the classic "lots of reads, simpler queries" web workload.
- Huge ecosystem. Tooling, hosting, and replication setups are everywhere and well understood.
- Easy replication. Primary/replica read scaling is mature and widely deployed.
Non-functional · what it must survive
- Familiar and ubiquitous. Easy to hire for, easy to host, runs on everything.
- Fast simple queries. Lean and quick for straightforward CRUD.
- MariaDB option. A truly open-source fork if Oracle ownership is a concern.
MySQL vs Postgres
For most apps, either is fine. The rough split: Postgres leads on advanced SQL features (richer window functions, stricter standards compliance, JSONB, extensions like pgvector and PostGIS), while MySQL keeps a slight edge on plain read-heavy CRUD and has an even larger hosting footprint in some ecosystems.
The one difference worth understanding is how each handles concurrent writes. Postgres uses MVCC, where readers never block writers and writers never block readers. MySQL's InnoDB also does MVCC, but its default REPEATABLE READ isolation uses gap locking on range queries, and under heavy concurrent writes to nearby key ranges that can produce deadlocks your app has to catch and retry. It rarely bites a normal web app, but it's the reason teams with 100+ concurrent write connections often prefer Postgres.
If you're starting fresh with no constraint, this course (and most modern advice) leans Postgres for its feature depth. If you're joining a team that runs MySQL, you'll be productive immediately.
Dialect differences to watch
Your SQL transfers, but a few things differ when you move from Postgres to MySQL:
-- Auto-incrementing ids
-- Postgres: id SERIAL (or GENERATED ALWAYS AS IDENTITY)
-- MySQL: id INT AUTO_INCREMENT
-- Quoting identifiers
-- Postgres: "my_column" (double quotes)
-- MySQL: `my_column` (backticks)
-- Case sensitivity of string comparisons differs by collation;
-- MySQL is often case-insensitive by default, Postgres case-sensitive.
-- "Insert or update" syntax
-- Postgres: INSERT ... ON CONFLICT (id) DO UPDATE ...
-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE ...One historical footgun: storage engines
Older MySQL setups sometimes used the MyISAM engine, which does not support transactions or foreign keys. If you ever inherit a MySQL database, confirm tables use InnoDB (the modern default) so your transactions and referential integrity actually work. On a fresh modern install this is already the case.
The one-liner
MySQL is a solid, ubiquitous relational database, and your SQL skills carry straight over. Pick it if your team or platform already runs it; pick Postgres if you're starting fresh and want the deeper feature set. Either way, the core SQL you practised is the same.
Test yourself
Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.
QHow different is MySQL from Postgres for someone who knows SQL?+
Barely different for everyday queries: SELECT, WHERE, JOIN, GROUP BY, INSERT/UPDATE/DELETE all work the same. The differences are at the edges: auto-increment syntax, identifier quoting (backticks vs double quotes), upsert syntax, default case sensitivity, and which advanced SQL features each supports.
QWhat is InnoDB and why does it matter?+
InnoDB is MySQL's default storage engine, providing transactions, row-level locking, and foreign keys. It matters because the older MyISAM engine supports none of those, so on a legacy database you confirm tables use InnoDB to ensure your transactions and referential integrity actually work.
QMySQL or Postgres for a new project?+
Either works, but modern advice (and this course) leans Postgres for its richer feature set: stronger standards compliance, JSONB, window functions, and extensions like pgvector and PostGIS. Choose MySQL if your team or hosting already standardises on it, where you'll be productive immediately.
Comments
Loading comments…