Pick PostgreSQL when you want the richest SQL, extensibility and migrations that roll back cleanly. Pick MySQL when your platform, team or hosting already lives in the MySQL ecosystem and you value its replication tooling. Pick MariaDB when you want a community-governed MySQL relative and can accept that it is no longer a drop-in replacement. The rest of this article explains the differences that actually change day-to-day work.
Three engines, two families#
PostgreSQL is its own lineage: an object-relational database developed by an independent community under the permissive PostgreSQL License. MySQL is owned by Oracle and released as a GPL community edition alongside commercial editions. MariaDB started as a fork of MySQL and is developed by the MariaDB Foundation and MariaDB plc under the GPL. MySQL and MariaDB still share the wire protocol, most of the SQL dialect and the InnoDB heritage, which is why many drivers and tools treat them as one family. PostgreSQL shares nothing with them beyond SQL itself.
That family split matters more than any single feature. Moving between MySQL and MariaDB is a compatibility exercise. Moving between either of them and PostgreSQL is a rewrite of every non-trivial query, type and migration.
Data types and extensibility#
PostgreSQL has the broadest type system of the three: arrays, ranges, network addresses, UUID, a binary JSON type (jsonb) with indexing, geometric types, and user-defined types and operators. Extensions such as PostGIS add whole domains without forking the server. You can write functions in SQL, PL/pgSQL, Python or other languages and index the result of an expression.
MySQL has a compact, pragmatic type set: integer types with an UNSIGNED modifier, DECIMAL, DATETIME and TIMESTAMP, ENUM and SET, spatial types and a native binary JSON type. There is no user-defined type system comparable to PostgreSQL's.
MariaDB largely mirrors MySQL's types but differs in details that surprise people who assume equivalence. Its JSON is an alias for LONGTEXT with a validity check rather than a binary type, it has sequences as first-class objects, and it added system-versioned (temporal) tables. Some of these are strengths; all of them are divergences you must know about when a tool or an ORM claims to "support MySQL".
These differences also decide what an introspection tool reports. When you generate a diagram from your migrations, PostgreSQL reports a column declared as INT as int4 and VARCHAR(50) as varchar, while MySQL and MariaDB report INT and VARCHAR. Same DDL, different vocabulary; see choosing column types for the full mapping.
SQL conformance and query features#
PostgreSQL follows the SQL standard closely: common table expressions including recursive ones, window functions, LATERAL joins, full MERGE, deferrable constraints, partial and expression indexes, materialized views, table inheritance and declarative partitioning. When a standard feature exists, PostgreSQL usually implements it the standard way.
MySQL closed most of the gap in its 8.x line: CTEs, window functions, check constraints that are enforced, a transactional data dictionary and instant ADD COLUMN for many cases. Where it deviates it tends to be permissive rather than strict, which is convenient until it silently accepts something you meant to be an error. Run it with strict SQL mode, which has been the default for years.
MariaDB implements a similar feature set through its own code, including CTEs, window functions, INTERSECT and EXCEPT, RETURNING clauses on INSERT, DELETE and REPLACE, and an optimizer that has drifted from MySQL's. Query plans that are fast on one can be slow on the other.
Transactional DDL: the difference migrations feel most#
In PostgreSQL, CREATE TABLE, ALTER TABLE, CREATE INDEX and most other DDL run inside a transaction and roll back with it. A migration that fails on its third statement leaves the database exactly as it was. Flyway, Liquibase, Django, Alembic and most other tools use this to make each migration atomic.
MySQL and MariaDB do not offer that. A DDL statement causes an implicit commit, so a migration that fails halfway leaves the first half applied. MySQL 8.0 made individual DDL statements atomic (a crashed ALTER TABLE no longer leaves an orphan), and MariaDB followed with its own atomic DDL work, but neither lets you group several DDL statements into one transaction. On these engines you write smaller migrations, prefer idempotent statements, and keep a repair procedure ready. The mechanics are covered in Transactional DDL explained.
Replication, high availability and operations#
All three replicate well enough for the common cases, but their tooling differs in character.
PostgreSQL ships streaming physical replication and logical replication in the core server. Failover, connection routing and cluster management come from the ecosystem: Patroni, repmgr, pgBouncer, PgBackRest and friends. You assemble a stack, which is flexible and well documented but not turnkey.
MySQL's binary-log replication is mature, widely understood and supported by a large body of operational knowledge. Group Replication and InnoDB Cluster provide a more integrated high-availability story from the vendor itself. Many hosting providers and DBAs have run MySQL replicas for two decades.
MariaDB has its own GTID implementation, parallel replication and the Galera Cluster integration for synchronous multi-primary setups. The catch is compatibility: replicating between MariaDB and MySQL works only in limited configurations, and their GTID formats are not interchangeable.
Hosting and ecosystem#
Every major cloud offers managed PostgreSQL and MySQL. Managed MariaDB exists but is less universal, and some providers have retired or de-emphasized it, so check the offering before you commit. Shared web hosting still skews heavily toward MySQL and MariaDB, which is why PHP frameworks default to them.
Tooling is rich on all sides: psql and pgAdmin, the mysql client and MySQL Workbench, DBeaver and DataGrip for everything. Drivers exist for every language. ORMs and migration tools support all three, though MariaDB support is usually "MySQL mode with caveats", and features unique to MariaDB rarely have first-class ORM support.
A decision table#
| You need | Best fit | Why |
|---|---|---|
| Complex queries, analytics alongside OLTP, JSON documents with indexes | PostgreSQL | Type system, planner, expression and partial indexes, jsonb |
| Migrations that never leave a half-applied schema | PostgreSQL | Transactional DDL |
| Extensions such as geospatial, time series or full text in the same database | PostgreSQL | Extension mechanism |
| A large existing MySQL estate, DBAs and runbooks | MySQL | Operational continuity, replication tooling |
| Vendor-integrated HA and a commercial support path | MySQL | InnoDB Cluster, Oracle support |
| Community governance in the MySQL family, temporal tables, Galera | MariaDB | Foundation stewardship, system-versioned tables, synchronous clustering |
| Shared PHP hosting or a distro default | MariaDB or MySQL | Ubiquity on that platform |
What changes for your migration files#
Write DDL for the engine you actually run, not for "SQL". The three disagree on auto-increment (GENERATED ... AS IDENTITY or SERIAL versus AUTO_INCREMENT), on booleans (boolean versus TINYINT(1)), on schema semantics (PostgreSQL has schemas inside a database; MySQL and MariaDB use the words database and schema for the same thing) and on identifier case folding, which is why the same migration produces slightly different metadata on each. If you must support more than one engine, prefer a migration tool whose changelog format abstracts DDL, such as Liquibase's XML or YAML, or keep per-engine SQL directories and test all of them. The Flyway versus Liquibase comparison goes into that trade-off.
Whatever you choose, test migrations against a real instance of the version you deploy to, not against SQLite or an in-memory stand-in. Behaviour around DDL, defaults and constraints is exactly where the engines differ most.
Where SchemaDrawer fits#
If you are comparing engines or migrating between them, it helps to see what the same migrations produce on each. SchemaDrawer runs your migration files, unchanged, against a throw-away PostgreSQL, MySQL or MariaDB of the version you pick, reads the resulting tables, columns, primary keys and foreign keys back and returns an SVG diagram. Run the same Flyway or Liquibase set against PostgreSQL 18 and MariaDB 11.8 and the two diagrams show you, column by column, where the dialects diverge. It is free to use through the API and the GitHub Action.
Key takeaways#
- PostgreSQL is one lineage; MySQL and MariaDB are a family that is slowly drifting apart.
- PostgreSQL's transactional DDL is the single biggest quality-of-life difference for migrations.
- MariaDB is not a drop-in MySQL replacement any more; verify your tools and your replication plans.
- Choose by ecosystem and operations as much as by features, and test migrations on the real engine and version.