Postgres vs MySQL: A Practical Comparison

What and Why

Postgres and MySQL are the two most popular open-source relational databases. Both are excellent and both can run trillion-row systems. But they have different defaults, philosophies, and rough edges, and choosing without understanding those differences leads to migrations later.

This article gives you a working mental model so you can pick confidently and avoid the most painful gotchas.

Mental Model

Think of Postgres as a research-quality SQL engine that grew strong production legs. It is strict, feature-rich, and extension-driven. Think of MySQL as a pragmatic web-scale database that grew strong SQL features. It is forgiving by default, fast for simple workloads, and operationally well-understood.

The defaults reflect this: Postgres errors on bad data; MySQL historically silently coerced it. Postgres has one storage engine (heap + MVCC); MySQL ships InnoDB (and others) with different trade-offs.

Hands-on Example

Suppose you store user preferences as JSON and query them:

-- Postgres
SELECT id FROM users WHERE prefs->>'theme' = 'dark';
CREATE INDEX ON users ((prefs->>'theme'));

-- MySQL 8+
SELECT id FROM users WHERE prefs->>'$.theme' = 'dark';
CREATE INDEX idx_theme ON users ((CAST(prefs->>'$.theme' AS CHAR(20))));

Both work. Postgres has richer JSON operators (@>, ?, jsonb_path_query) and a true binary jsonb type. MySQL has solid JSON support but fewer operators and slightly clunkier indexing.

                       Postgres        MySQL (InnoDB)
Default isolation     Read Committed   Repeatable Read
MVCC                  Heap + xmin/xmax Undo log + rollback seg
JSON                  jsonb (binary)   JSON (binary)
Arrays                Native           No (JSON workaround)
Window functions      Since 8.4        Since 8.0
CTEs (recursive)      Yes              Yes (8.0+)
Full-text search      Built-in tsvector InnoDB FTS
Extensions            PostGIS, pg_vec  Plugins, limited
Replication           Logical + phys.  Async + group repl.
Stored procs language PL/pgSQL + more  SQL/PSM

For a typical CRUD app, both will serve you. The differences start to matter when you reach for advanced types (arrays, ranges, geometry), need strict type safety, or rely on extensions like PostGIS or pgvector.

Common Pitfalls

The biggest MySQL gotcha is character sets. Use utf8mb4, never utf8 - the latter is a three-byte subset that breaks on emoji and many Asian characters. Set it at the server, database, table, and connection level.

In Postgres, the biggest gotcha is connection management. Each connection is a process, and idle connections eat RAM. Always put PgBouncer in front of any nontrivial workload.

MySQL’s Repeatable Read default can surprise developers expecting Postgres semantics: phantom reads are prevented via gap locks, which can deadlock under heavy write contention. Postgres’ Read Committed default avoids that but allows non-repeatable reads inside a transaction.

Auto-increment behavior differs too. MySQL’s AUTO_INCREMENT can leave gaps after rollbacks and restarts. Postgres’ sequences behave similarly but are explicit objects, which surprises people coming from MySQL.

Finally, do not assume LIMIT ordering is stable in either database without an explicit ORDER BY. That bites everyone at least once.

Practical Tips

Pick Postgres when you need: rich types (arrays, JSON, ranges, geometry), strict correctness, complex analytics, extensions like PostGIS or pgvector, or logical replication for zero-downtime migrations.

Pick MySQL when you need: maximum read throughput for simple key lookups, a managed offering you already trust (RDS, Aurora, PlanetScale), or your team’s existing operational expertise lives there.

For both, learn the EXPLAIN output deeply, set up slow-query logging from day one, and back up with point-in-time recovery, not just nightly dumps.

If you are unsure, default to Postgres in 2026. The ecosystem momentum, JSON story, and extension library tilt the choice for most new projects. But “boring and well-operated” beats “exciting and misconfigured” every time.

Wrap-up

Postgres and MySQL are both production-grade and will not be the bottleneck of most applications. Choose based on the data types you need, the operational story you can run, and the extensions you cannot live without. Either way, invest in understanding your chosen engine’s MVCC, locking, and replication model - that is what separates a smooth scale-up from a painful one.