TL;DR — which one should I pick?

• Pick PostgreSQL by default in 2026. It has stricter SQL semantics, richer types (JSONB, arrays, ranges, geometry, full-text), real materialized views, transactional DDL, and the most permissive open-source licence.
• Pick MySQL when you (1) inherit a MySQL stack, (2) need the largest ecosystem of cheap managed-hosting and tooling (especially in PHP/WordPress), or (3) value its lower-overhead defaults for very simple read-heavy workloads.
• Pick MariaDB when you want MySQL's ergonomics with a more conservative governance model and a few additional engines (e.g. ColumnStore for analytics).
• For analytics at any meaningful scale, neither is the right answer alone — pair Postgres with DuckDB / ClickHouse, or use a column store. Both row-stores hit the same wall once tables exceed a few billion rows.

History, governance and licensing

• PostgreSQL — released 1996 (descendant of Postgres / 1986). Governed by a non-profit global development group. Licensed under the PostgreSQL License (a permissive BSD/MIT variant). No corporate owner; impossible to "buy" or relicense.
• MySQL — released 1995, acquired by Sun (2008), then by Oracle (2010). Dual-licensed: GPL v2 (community) and a commercial licence from Oracle. Most production MySQL deployments use the GPL community edition.
• MariaDB — forked from MySQL in 2009 by the original MySQL author after the Oracle acquisition. GPL v2 and LGPL. Drop-in replacement for MySQL up to 5.5; binary compatibility has diverged since.

Feature-by-feature comparison

Both engines now share most "modern SQL" features — window functions, CTEs, recursive CTEs, JSON, generated columns, partitioning. The differences are in implementation depth and surrounding ergonomics.

• SQL standards compliance — Postgres: very high; rejects implicit coercions, enforces strict typing. MySQL: relaxed by default; STRICT_ALL_TABLES sql_mode tightens it but is not universal.
• Window functions — both since MySQL 8.0 / Postgres 8.4. Postgres has slightly richer frame syntax (GROUPS frames, exclusion).
• CTEs — both. Postgres CTEs are MATERIALIZED by default pre-12, INLINED since 12 (with explicit override). MySQL 8.0+ inlines.
• JSON — Postgres JSONB (binary, indexable, GIN). MySQL JSON (binary, indexable via generated columns + functional indexes since 8.0.13). Postgres JSONB is richer in operators and faster for write-heavy JSON workloads.
• Arrays — first-class in Postgres (any type can be made into an array). MySQL has no array type; emulate with JSON.
• Materialized views — Postgres native (manual refresh). MySQL has no materialized views; emulate with periodic INSERT INTO summary tables.
• Transactional DDL — Postgres: yes (CREATE/ALTER/DROP inside a transaction can be rolled back). MySQL: no — DDL auto-commits and cannot be rolled back. This single difference makes Postgres migrations enormously safer.
• Full-text search — Postgres: native tsvector + GIN, configurable dictionaries. MySQL: native FULLTEXT on InnoDB since 5.6, simpler but less configurable.
• Stored procedures — both. MySQL has a more SQL-Server-like procedural dialect. Postgres has PL/pgSQL plus PL/Python, PL/Perl, PL/v8 etc.
• Replication — Postgres: streaming + logical replication (built in since 10). MySQL: native asynchronous and semi-synchronous primary-replica plus group replication.
• Geographic data — Postgres + PostGIS is the de-facto standard for GIS in any language. MySQL has SPATIAL but with much less mature operator and index support.

Data types: where the dialects differ most

• Booleans — Postgres: native BOOLEAN. MySQL: TINYINT(1) (BOOLEAN is a synonym).
• Strings — Postgres: TEXT and VARCHAR(n) have identical performance. MySQL: VARCHAR(n) for indexed columns; TEXT is stored off-page and is slower for indexed reads. Postgres VARCHAR has no length penalty; MySQL VARCHAR(255) is a historical sweet-spot.
• Timestamps — Postgres: TIMESTAMP (no TZ) and TIMESTAMPTZ (UTC, returned in session TZ). MySQL: TIMESTAMP (4 bytes, 1970–2038, UTC-converted) and DATETIME (8 bytes, 1000–9999, no TZ).
• JSON — Postgres JSONB (binary, indexable, deduplicated keys) vs MySQL JSON (binary, indexable via generated columns).
• Decimals — both use DECIMAL(p, s); identical semantics for money.
• UUIDs — Postgres: native UUID type (16 bytes). MySQL: BINARY(16) or CHAR(36); use UUIDv7 to keep the InnoDB clustered index from fragmenting.
• Enums — Postgres: typed CREATE TYPE ... AS ENUM, painful to alter. MySQL: per-column ENUM, similar tradeoffs. Both are usually beaten by a small lookup table + FK.

Syntax differences that bite during migration

-- Auto-incrementing primary keys
-- Postgres
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- MySQL
id BIGINT AUTO_INCREMENT PRIMARY KEY

-- String concatenation
-- Postgres (ANSI)
SELECT first_name || ' ' || last_name FROM users;
-- MySQL (default || is OR; CONCAT works everywhere)
SELECT CONCAT(first_name, ' ', last_name) FROM users;

-- Limit / pagination
-- Postgres (ANSI + Postgres)
SELECT * FROM events ORDER BY id LIMIT 50 OFFSET 100;
SELECT * FROM events ORDER BY id OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY;
-- MySQL (only the first form)
SELECT * FROM events ORDER BY id LIMIT 100, 50;  -- (offset, count) variant

-- INSERT ... ON CONFLICT (UPSERT)
-- Postgres
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- MySQL
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Returning the inserted row
-- Postgres (ANSI)
INSERT INTO users (name) VALUES ('Alice') RETURNING id, created_at;
-- MySQL (8.0.21+ for INSERT...RETURNING in MariaDB; in MySQL itself: SELECT LAST_INSERT_ID())
INSERT INTO users (name) VALUES ('Alice');
SELECT LAST_INSERT_ID();

-- Boolean literals
-- Postgres: TRUE / FALSE
-- MySQL: TRUE / FALSE accepted, stored as 1 / 0

-- Identifier quoting
-- Postgres / ANSI: "double_quoted" identifiers, 'single quoted' strings
-- MySQL: `backticked` identifiers; double quotes only with ANSI_QUOTES sql_mode

-- Case sensitivity of identifiers
-- Postgres: lowercases unquoted identifiers; preserves case of quoted ones
-- MySQL: case-sensitivity of table names depends on lower_case_table_names + filesystem

JSON: PostgreSQL JSONB vs MySQL JSON

• Both store JSON in a binary form (Postgres JSONB, MySQL JSON since 5.7). Both have indexable extraction operators.
• Postgres operators: -> (JSON), ->> (text), @> (containment), ? / ?| / ?& (key existence). GIN index with jsonb_path_ops accelerates @> queries directly.
• MySQL operators: -> (JSON), ->> (text), JSON_EXTRACT, JSON_CONTAINS. To index a JSON path, create a generated column on the extracted value and index that column.
• Containment query speed (e.g. WHERE doc @> '{"action": "purchase"}'): Postgres GIN handles it natively. MySQL requires a generated column per query path.
• Document mutability: both have JSON_SET / jsonb_set, but Postgres requires writing the whole JSONB on update (no partial in-place modification). MySQL since 5.7 supports partial updates of small JSON columns in-place.
• Verdict: Postgres JSONB is more ergonomic for genuine document-style workloads; MySQL JSON is fine when JSON is a small adjunct to mostly-relational data.

Transactions and isolation levels

• Both use MVCC for non-blocking reads. Different implementations: Postgres keeps row versions in the heap (cleaned by VACUUM); MySQL InnoDB keeps the latest version in-place and prior versions in the undo log.
• Default isolation: Postgres READ COMMITTED; MySQL InnoDB REPEATABLE READ.
• Postgres SERIALIZABLE — true serializable snapshot isolation (SSI). Detects serialization conflicts and aborts the offending transaction.
• MySQL SERIALIZABLE — implemented as REPEATABLE READ + implicit shared lock on every read. Strong, but coarser and lock-heavy.
• Transactional DDL: Postgres yes (huge migration safety win). MySQL no — every CREATE/ALTER/DROP auto-commits and cannot be rolled back. Plan migrations accordingly.
• Bloat: Postgres can suffer dead-row bloat if VACUUM does not keep up; MySQL's undo-log model can suffer "long undo segments" under long-running transactions. Both need attention at scale.

Replication and high availability

• Postgres streaming replication — physical (block-level) async or sync replicas. Bytes-for-bytes copies; replicas serve read-only traffic.
• Postgres logical replication (since 10) — row-level change events; supports cross-version replication, partial-table replication, multi-master via Patroni / pglogical.
• MySQL primary-replica (formerly master-slave) — async, semi-sync or fully-sync via Group Replication / InnoDB Cluster. Statement-based, row-based, or mixed.
• MySQL Group Replication / InnoDB Cluster — multi-primary built-in, paxos-style consensus.
• Failover ecosystem: Postgres has Patroni, repmgr, pgbouncer + HAProxy stacks. MySQL has Orchestrator, ProxySQL, MySQL Router. Both are mature.
• Cloud managed services: Amazon RDS, Aurora, Cloud SQL, Azure Database — all support both. Aurora and AlloyDB are particularly strong rewrites of the storage layer.

Performance: when does each one win?

In modern benchmarks (sysbench OLTP, TPC-C-like) the two engines are within 10–20% of each other on most workloads. The differences come from query shape, schema design and operational tuning more than from raw throughput.

• Simple read-heavy OLTP — MySQL InnoDB has a slight edge for very simple lookup workloads (PK reads dominate); the adaptive hash index helps.
• Complex queries / analytical SQL — Postgres usually wins. The planner is smarter, parallel query is more mature, and CTE / window-function / aggregate optimisation is broader.
• Write-heavy / many secondary indexes — Postgres tends to win because HOT updates avoid index churn when no indexed column changes; MySQL has to update every secondary index that points to the row.
• Bulk loads — both are fast with COPY (Postgres) / LOAD DATA INFILE (MySQL); Postgres pg_bulkload and MySQL's parallel-insert paths close any gap.
• Connection overhead — Postgres connections are heavyweight (one OS process each); always front with PgBouncer in production. MySQL connections are threads, much cheaper.
• Memory tuning — different parameters but similar budgets: Postgres shared_buffers + work_mem; MySQL innodb_buffer_pool_size + sort/join buffers.

Ecosystem, frameworks and tooling

• PHP / WordPress / Laravel — MySQL is the lingua franca. Postgres is fully supported in Laravel but the WordPress ecosystem assumes MySQL.
• Ruby on Rails / Django / Spring Boot — both are first-class. Django is famously Postgres-friendly (array fields, JSONField, full-text search).
• Node / TypeScript (Prisma, Drizzle, Knex) — both are first-class with broadly identical ergonomics.
• Elixir / Phoenix — Postgres is the default and most idiomatic.
• Cloud-native / Kubernetes — both have mature operators (Zalando Postgres Operator, CrunchyData; Percona Operator, Oracle MySQL Operator).
• Analytics extensions — Postgres FDWs (foreign data wrappers), DuckDB integration, Citus for distributed Postgres. MySQL has fewer extension points; MariaDB ColumnStore is the primary analytical option in the family.
• GUI / admin tools — Postgres: pgAdmin, DBeaver, Postico, TablePlus, DataGrip. MySQL: MySQL Workbench, DBeaver, TablePlus, DataGrip.

When to pick each engine — decision framework

Use this checklist on a greenfield project. Whichever side has more "yes" answers for your situation is the right pick.

• Pick PostgreSQL if: you need rich types (arrays, JSONB, ranges, GIS); you want safe migrations (transactional DDL); you value strict SQL semantics; you do anything with geographic data; you do any non-trivial analytical SQL inside the database; you anticipate multi-tenant SaaS with row-level security; you want logical replication for cross-version upgrades.
• Pick MySQL if: your team and tooling already speak MySQL; you run on a PHP / WordPress stack; your workload is dominated by simple primary-key lookups; you want the lowest connection overhead without a connection pooler; you need the very widest pool of cheap shared-hosting providers.
• Pick MariaDB if: you want a community-governed MySQL; you need ColumnStore or other engine-specific extensions; you want predictable BSL-free licensing.
• Pick a managed service of either if: you do not have dedicated DBAs and the workload fits a single large node — Aurora / AlloyDB and Cloud SQL / RDS take 90% of the operational pain away.

Migrating between MySQL and PostgreSQL

• Schema translation — pgloader is the standard tool for MySQL → Postgres; it handles type mapping, AUTO_INCREMENT → IDENTITY, ENUM → CREATE TYPE, and unsigned integers (which Postgres does not have).
• Data load — pgloader streams; for very large tables, dump + parallel COPY beats it.
• Application changes — concatenation (CONCAT vs ||), pagination syntax, UPSERT clause, RETURNING, identifier quoting (backticks vs double quotes), case sensitivity.
• Auto-increment behaviour — MySQL preserves AUTO_INCREMENT high-water mark across restarts in 8.0+; Postgres sequences are independent of row state.
• Date functions — DATE_FORMAT, STR_TO_DATE (MySQL) vs to_char, to_date (Postgres). Almost every ad-hoc reporting query will need rewriting.
• CASE / IF — MySQL has IF(cond, a, b) and IFNULL; Postgres has only standard CASE WHEN and COALESCE. Translate accordingly.
• Connection pooler — installing PgBouncer is mandatory before going live on Postgres if your app uses MySQL-style "many short-lived connections" patterns.

Practice: hands-on dialect lessons

Each engine's deep-dive lessons cover the features above with runnable examples and exercises across the HR, E-commerce and Banking schemas — both engines, side by side.