PostgreSQL vs MySQL: practical differences for developers who need to choose

Most projects that start with MySQL do so because "that's what the tutorial used" or "everyone uses it." Rarely is it an informed decision about what the system will actually need. That works fine until you try to write a recursive CTE, discover that MySQL's JSON_EXTRACT has the performance of a wet paper bag, or realize that GROUP BY was silently accepting non-aggregated columns for years without complaining.

This is the comparison I wish I'd read before a few projects I won't name.

What they have in common (and why that confuses the decision)

Both are relational databases, both speak SQL, both support ACID transactions, B-tree indexes, replication, and run fine on any cloud. For a straightforward CRUD app — users table, orders table, authentication — either one works and you'll likely never notice the difference in production.

The problem is that "simple CRUD" is the easy part. What differentiates them are the edges: SQL standard conformance, the concurrency model, support for complex data types, and the extension ecosystem.

If you want the broader context on when to use relational vs. NoSQL in general, the relational vs. NoSQL post covers that decision. This one focuses specifically on what changes within the relational world.

Data types: PostgreSQL wins without argument

MySQL has INT, VARCHAR, TEXT, DATETIME, FLOAT, and a handful of less-used types. It covers most use cases. PostgreSQL has all of that plus:

• Native arrays: INTEGER[], TEXT[], UUID[]. No pivot table just to store a list of tags.
• JSONB: binary JSON with GIN indexing. Not "JSON support" — a first-class type with operators, functions, and partial indexes.
• UUID as a native type: no CHAR(36) polluting your indexes.
• Range types (tsrange, daterange, int4range): for data with a start and end, with built-in overlap, contains, and adjacent operators.
• Geometric types and PostGIS: point, line, polygon, circle — and with PostGIS you get a complete geospatial database without installing anything extra.
• Typed enums: CREATE TYPE status AS ENUM ('active', 'inactive', 'pending') — validated at the database level, not in the application.
• HSTORE: flat key-value inside a column, lighter than JSONB when the data is simple.

-- PostgreSQL: tags column without an auxiliary table
CREATE TABLE articles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  tags TEXT[],
  metadata JSONB,
  active_period tsrange
);

-- Query with arrays and JSON in the same statement
SELECT title
FROM articles
WHERE 'postgresql' = ANY(tags)
  AND metadata->>'author' = 'rafael'
  AND active_period @> NOW()::TIMESTAMPTZ;

In MySQL, this schema would require a separate tags table, JSON columns without efficient field-level indexing, and manual date range logic.

JSON: JSONB vs JSON_EXTRACT

This is where the difference becomes most visible in real projects.

In MySQL, the JSON type stores the document and gives you functions like JSON_EXTRACT() and the -> operator. It works. But indexing a field inside the JSON requires creating a generated column:

-- MySQL: indexing a JSON field requires a virtual column
ALTER TABLE events
  ADD COLUMN user_id VARCHAR(36) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id'))) STORED;
CREATE INDEX ON events(user_id);

In PostgreSQL, JSONB has a native GIN index that covers queries on any field in the document — without needing to know in advance which fields you'll query:

-- PostgreSQL: one index covers any field
CREATE INDEX ON events USING GIN (payload);

-- Both of these queries use the same index
SELECT * FROM events WHERE payload->>'user_id' = '123';
SELECT * FROM events WHERE payload @> '{"type": "purchase", "amount_above": 1000}';

For applications that use JSON heavily — event sourcing, semi-structured schemas, third-party API integrations — JSONB with GIN is a practical difference, not a theoretical one.

Concurrency: MVCC behavior differences

Both databases implement MVCC (Multi-Version Concurrency Control), but with important differences.

PostgreSQL uses MVCC on every write operation, without exception. Reads never block writes and writes never block reads — each transaction sees a consistent snapshot of the database from the moment it started.

MySQL/InnoDB also uses MVCC but has behaviors that surprise people coming from PostgreSQL:

• SELECT inside a transaction with REPEATABLE READ (MySQL's default) won't see changes from other committed transactions after yours started — which seems obvious but creates surprises with certain upsert patterns and counters.
• SELECT ... FOR UPDATE in MySQL uses gap locking, not just row locking — in certain situations this can cause deadlocks where PostgreSQL would not.
• MySQL has a historical quirk with ENUM: value order is by creation position, not alphabetical, and reordering values in production requires an ALTER TABLE that can be slow on large tables.

For systems with high write concurrency — queues, counters, inventory systems — PostgreSQL's behavior is more predictable. Not that MySQL is inadequate, but its locking model has more edge cases.

SQL conformance: MySQL still slips

MySQL has historically been more permissive than the SQL standard — which seems like a feature until you need to port a query or rely on specific behavior.

Classic example: for years, MySQL accepted GROUP BY with non-aggregated columns in SELECT without complaining, returning an arbitrary value for those columns. This was "fixed" with ONLY_FULL_GROUP_BY mode, which has been the default since MySQL 5.7, but legacy databases still have this disabled.

-- This query is invalid in standard SQL and in PostgreSQL
-- MySQL accepted it silently before 5.7
SELECT user_id, email, MAX(created_at)
FROM orders
GROUP BY user_id;
-- Which email is returned? Any one from the group. Non-deterministic.

Other conformance differences:

• CTEs: PostgreSQL has supported CTEs since version 8.4, with controlled materialization. MySQL only added CTE support in version 8.0 (2018).
• Window functions: available in both from reasonably recent versions, but PostgreSQL has more functions and standardized behavior.
• LATERAL joins: PostgreSQL supports them; MySQL has limited support.
• RETURNING in INSERT/UPDATE/DELETE: PostgreSQL supports it; MySQL doesn't — you need a separate query to retrieve the ID of the inserted record or the updated value.

-- PostgreSQL: INSERT with RETURNING avoids a round-trip
INSERT INTO users (name, email)
VALUES ('rafael', 'rafael@example.com')
RETURNING id, created_at;

Extensions: PostgreSQL's ecosystem

This is the difference that matters most over the long term.

PostgreSQL has an extension system that turns the database into something well beyond a generic RDBMS:

MySQL has a smaller plugin ecosystem. Functional, but the gap is significant.

When to use MySQL

I'm not going to pretend MySQL doesn't have cases where it makes more sense:

Read-heavy workloads with simple schemas: MySQL has a reputation for being faster on read-heavy workloads with simple queries. For a high-traffic blog where most queries are SELECT * FROM posts WHERE slug = ?, the performance difference in MySQL's favor is measurable.

Legacy ecosystem: if you inherit a system in MySQL with an ORM, migrations, and a team that knows the database, the cost of migrating rarely justifies the technical gain. "PostgreSQL is better" isn't a justification for a six-month migration project.

Managed databases in constrained environments: MySQL on some cloud platforms is still cheaper or easier to provision than PostgreSQL. That argument is shrinking, but it exists.

Compatibility with specific stacks: some BI tools, ERPs, and legacy systems have more mature MySQL support. Worth checking before deciding.

When to use PostgreSQL

Basically everything else. Specifically:

• Financial, transactional data requiring robust ACID guarantees
• Semi-structured JSON data with query and indexing needs
• Geospatial data (PostGIS)
• Time series (TimescaleDB)
• Embeddings and semantic search (pg_vector)
• New systems where you don't yet know exactly how the data will grow
• Teams that will write complex queries — CTEs, window functions, lateral subqueries

Frequently asked questions

Is PostgreSQL slower than MySQL?

For simple reads with a fixed schema, MySQL can be marginally faster. For complex queries, high-concurrency writes, and analytical workloads, PostgreSQL tends to perform better. The myth that PostgreSQL is heavier comes from conservative default configuration — tuning shared_buffers, work_mem, and effective_cache_size in postgresql.conf usually eliminates any perceived difference.

Can I migrate from MySQL to PostgreSQL in production?

Yes, but don't underestimate the work. SQL differences (ONLY_FULL_GROUP_BY mode, data types, database-specific functions), dump formats, and auto-increment vs. sequence behavior all require case-by-case review. Tools like pgloader automate part of the migration, but queries and stored procedures need manual revision.

Didn't MySQL 8 close the gap with PostgreSQL?

It closed some of it. CTEs, window functions, and stricter SQL modes were added. The distance has shrunk. But the extension ecosystem, native data types (arrays, ranges), JSONB with GIN, and stronger SQL conformance still put PostgreSQL ahead for most new use cases.

Which database for a new SaaS project?

PostgreSQL as the default, without hesitation. The only reason to choose MySQL for a new project is platform constraints or strong team preference. On feature quality, conformance, and ecosystem, PostgreSQL delivers more — and the performance cost for simple cases is negligible with proper configuration.

PostgreSQL as default, MySQL when there's a concrete reason

My heuristic: start with PostgreSQL. If a concrete reason for MySQL appears — legacy ecosystem, platform constraint, team with exclusive expertise — consider it. If the reason is "it's more popular" or "the tutorial used it," that doesn't count.

For exploring and formatting queries as you evaluate a migration or model a new schema, I use the SQL Formatter — especially useful when queries start growing with CTEs and subqueries that need to stay readable for the next dev who maintains the code.