The short version
MySQL vs PostgreSQL, when the question is where legacy data should land: PostgreSQL is the stricter and safer target. With default settings it rejects bad data that a permissive MySQL server stores silently, it rolls back a failed schema change, and it keeps JSON exactly as it arrived if you ask it to. MySQL is the easier target when the application already speaks MySQL, or depends on its case- and accent-insensitive string matching. In my small test, PostgreSQL also ran the aggregate several times faster, but that is one query on one laptop, not a benchmark.
I ran the same small suite on MySQL 8.4.11 and PostgreSQL 16.14, both with default settings, both in local containers on the same laptop. Everything below is pasted from those runs. MySQL went through the mysql client with --force, so errors print at the line they came from; I put each statement after mysql> and trimmed the (0.003 sec) timings. PostgreSQL went through a small psycopg2 script that echoes each statement after =>.
What changes depending on the target
A migration target decides what happens to data the old system let through. The tests here are the ones that change a migration plan: whether two strings count as the same value, what happens to a value that does not fit, whether a half-finished schema change can be undone, and whether a constraint is a promise or a comment.
| Test | MySQL 8.4 (defaults) | PostgreSQL 16 (defaults) |
|---|---|---|
'ana@' vs 'ANA@' in a primary key |
duplicate | two rows |
'Jose' vs 'José' in a unique key |
duplicate | two rows |
too-long string, bad date, '' into INT |
error (strict mode) | error |
same, with legacy sql_mode = '' |
stored, with a warning | not possible |
CREATE TABLE then ROLLBACK |
table stays | table is gone |
CHECK (qty >= 0) |
enforced | enforced |
| JSON with a duplicate key | last key kept | json keeps both, jsonb keeps last |
SELECT id FROM customers on table Customers |
table not found | works |
Case and accent: when are two strings equal?
MySQL's default character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, so comparisons are case-insensitive, and the _ai suffix means accent-insensitive too. A unique key follows the collation:
mysql> INSERT INTO users VALUES ('ana@example.com');
Query OK, 1 row affected
mysql> INSERT INTO users VALUES ('ANA@example.com');
ERROR 1062 (23000) at line 4: Duplicate entry 'ANA@example.com' for key 'users.PRIMARY'
mysql> INSERT INTO names VALUES ('Jose');
Query OK, 1 row affected
mysql> INSERT INTO names VALUES ('José');
ERROR 1062 (23000) at line 7: Duplicate entry 'José' for key 'names.name'
mysql> SELECT name FROM names WHERE name = 'JOSÉ';
+------+
| name |
+------+
| Jose |
+------+
The same statements on PostgreSQL, where the database collation is en_US.utf8 from libc:
=> SELECT 'Cafe' = 'café' AS accent_eq, 'ABC' = 'abc' AS case_eq;
accent_eq | case_eq
False | False
=> INSERT INTO users VALUES ('ANA@example.com');
INSERT 0 1
=> SELECT email FROM users WHERE email = 'ANA@EXAMPLE.COM';
email
=> INSERT INTO names VALUES ('José');
INSERT 0 1
For a migration this cuts both ways. Moving MySQL data to PostgreSQL is safe, since nothing that was unique becomes a duplicate. But the application's lookups stop matching, and new rows that MySQL would have refused now go in. Going the other way is where loads fail: a PostgreSQL table holding both ana@ and ANA@ will not load into a MySQL unique key.
PostgreSQL can match MySQL's behaviour with a nondeterministic ICU collation:
=> CREATE COLLATION ci_ai (provider = icu, locale = 'und-u-ks-level1', deterministic = false);
CREATE COLLATION
=> CREATE TABLE names2 (name varchar(50) COLLATE ci_ai UNIQUE);
CREATE TABLE
=> INSERT INTO names2 VALUES ('Jose');
INSERT 0 1
=> INSERT INTO names2 VALUES ('José');
ERROR: duplicate key value violates unique constraint "names2_name_key"
DETAIL: Key (name)=(José) already exists.
=> SELECT name FROM names2 WHERE name LIKE 'jo%';
ERROR: nondeterministic collations are not supported for LIKE
The last line is the catch. The PostgreSQL 16 docs say pattern matching is not possible with these collations and that they carry a performance penalty, so an application full of LIKE needs another plan, such as the lower(email) index in the MySQL to PostgreSQL step-by-step guide.
One trap in my own run: string literals in MySQL use the connection collation, and my MariaDB 11.8 client set it to utf8mb4_general_ci. Compare against columns when you test this, or the result comes from the client's setting.
Strictness on bad data
MySQL 8.4's default SQL mode includes STRICT_TRANS_TABLES and NO_ZERO_DATE, and with it all three bad inserts fail:
ERROR 1406 (22001) at line 9: Data too long for column 'code' at row 1
ERROR 1292 (22007) at line 10: Incorrect date value: '2024-02-30' for column 'd' at row 1
ERROR 1366 (HY000) at line 11: Incorrect integer value: '' for column 'n' at row 1
Legacy servers often ran without strict mode. In that case, the docs say, MySQL inserts adjusted values and produces warnings. With SET SESSION sql_mode = '':
| Warning | 1265 | Data truncated for column 'code' at row 1 |
| Warning | 1264 | Out of range value for column 'd' at row 1 |
| Warning | 1366 | Incorrect integer value: '' for column 'n' at row 1 |
+-------+------------+------+
| code | d | n |
+-------+------------+------+
| toolo | NULL | NULL |
| NULL | 0000-00-00 | NULL |
| NULL | NULL | 0 |
+-------+------------+------+
February 30th became 0000-00-00 and the empty string became 0. That is data you may already have, and it is what breaks on the way out. PostgreSQL has no switch to accept it:
=> INSERT INTO strict_t (code) VALUES ('toolong');
ERROR: value too long for type character varying(5)
=> INSERT INTO strict_t (d) VALUES ('2024-02-30');
ERROR: date/time field value out of range: "2024-02-30"
=> INSERT INTO strict_t (n) VALUES ('');
ERROR: invalid input syntax for type integer: ""
=> INSERT INTO strict_t (d) VALUES ('0000-00-00');
ERROR: date/time field value out of range: "0000-00-00"
The one exception PostgreSQL documents: a too-long value is truncated if the excess characters are all spaces, or if you cast it explicitly. The zero dates need a decision before the load; MySQL zero dates and where they break covers the options.
Can you roll back a schema change?
mysql> START TRANSACTION;
mysql> CREATE TABLE ddl_t (id INT);
mysql> ROLLBACK;
mysql> SHOW TABLES LIKE 'ddl_t';
+-------------------------------------------------------------+
| Tables_in_mysql_vs_postgresql_as_a_migration_target (ddl_t) |
+-------------------------------------------------------------+
| ddl_t |
+-------------------------------------------------------------+
=> BEGIN;
BEGIN
=> CREATE TABLE ddl_t (id int);
CREATE TABLE
=> ROLLBACK;
ROLLBACK
=> SELECT to_regclass('ddl_t');
to_regclass
null
MySQL documents that DDL such as CREATE TABLE implicitly ends the active transaction, and that a ROLLBACK does not undo an InnoDB CREATE TABLE. On PostgreSQL the table was gone after the rollback. For a migration that means a PostgreSQL schema script can run in one transaction and leave nothing behind if statement 40 of 60 fails. On MySQL a failed script leaves a half-built schema to clean up by hand.
Are CHECK constraints enforced?
Both enforce them now:
mysql> INSERT INTO chk VALUES (-1);
ERROR 3819 (HY000) at line 24: Check constraint 'chk_chk_1' is violated.
=> INSERT INTO chk VALUES (-1);
ERROR: new row for relation "chk" violates check constraint "chk_qty_check"
DETAIL: Failing row contains (-1).
The history matters for old schemas. Before MySQL 8.0.16, a CHECK clause was parsed and ignored. A schema written for MySQL 5.7 can carry CHECK clauses that its data has never had to satisfy, and the load into PostgreSQL or a current MySQL is the first time they apply. MySQL 8.4 also accepts NOT ENFORCED on a constraint, so look for that in the DDL too.
JSON vs JSONB: duplicate keys and key order
The same two documents into MySQL JSON and PostgreSQL json and jsonb:
mysql> SELECT doc FROM js;
+----------------------------------+
| doc |
+----------------------------------+
| {"a": 2, "b": 3} |
| {"id": 3, "zeta": 1, "alpha": 2} |
+----------------------------------+
=> SELECT doc::text, docb::text FROM js;
doc | docb
{"b": 1, "a": 2, "b": 3} | {"a": 2, "b": 3}
{"zeta": 1, "alpha": 2, "id": 3} | {"id": 3, "zeta": 1, "alpha": 2}
MySQL keeps the last duplicate key and sorts the keys, and warns that the order is not guaranteed across releases. PostgreSQL jsonb behaves the same way on this input. PostgreSQL json stores an exact copy of the input text, duplicates and key order included, and its functions treat the last value as the one that counts (doc->'b' returned 3). If the source documents get signed or hashed, or a consumer reads keys in order, json is the only one of the three that gives back the original bytes.
Identifier case
MySQL on Linux:
mysql> CREATE TABLE Customers (ID INT);
mysql> SELECT id FROM customers;
ERROR 1146 (42S02) at line 30: Table 'mysql_vs_postgresql_as_a_migration_target.customers' doesn't exist
mysql> SELECT id FROM Customers;
The second query worked: the column ID matched id, the table needed its exact case. MySQL's table name case depends on the operating system and lower_case_table_names, which defaults to 0 on Unix, 1 on Windows and 2 on macOS, and can only be set when the server is initialized. A database moved from a Windows MySQL to a Linux one can break on this alone.
PostgreSQL folds unquoted names to lower case and treats quoted ones as case-sensitive:
=> CREATE TABLE Customers (ID int);
CREATE TABLE
=> SELECT id FROM customers;
id
=> CREATE TABLE "Orders" ("OrderID" int);
CREATE TABLE
=> SELECT orderid FROM "Orders";
ERROR: column "orderid" does not exist
HINT: Perhaps you meant to reference the column "Orders.OrderID".
=> SELECT * FROM Orders;
ERROR: relation "orders" does not exist
A converted script that keeps MySQL's mixed-case names in double quotes produces the "Orders" case, and every query then needs the quotes. Lower-case the names during the migration.
MySQL vs PostgreSQL performance on one bulk load
One table, sales, with a bigint key, a region, a customer id, a decimal(10,2) amount and a date. One million rows generated in Python with a fixed seed, sent to both servers by the same script as 1,000 multi-row INSERT statements of 1,000 rows each, autocommit on (pymysql for MySQL, psycopg2 for PostgreSQL). Then three runs of one aggregate:
SELECT region, count(*), sum(amount), avg(amount)
FROM sales WHERE sold_on >= '2023-01-01'
GROUP BY region ORDER BY region;
This machine only: an Apple M1 Pro laptop, both servers at their default memory settings (128 MB InnoDB buffer pool, 128 MB shared_buffers). Two runs of the whole script:
MySQL 8.4.11
PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1)
mysql load 1,000,000 rows, 1,000 INSERTs of 1,000 rows: 6.17 s
postgres load 1,000,000 rows, 1,000 INSERTs of 1,000 rows: 5.62 s
mysql aggregate, 3 runs: 0.278 s, 0.269 s, 0.260 s
('central', 49868, Decimal('249407005.95'), Decimal('5001.343666'))
...
postgres aggregate, 3 runs: 0.041 s, 0.037 s, 0.039 s
('central', 49868, Decimal('249407005.95'), Decimal('5001.3436662789764980'))
...
postgres COPY FROM STDIN, 1,000,000 rows: 1.20 s
mysql LOAD DATA LOCAL: (3948, 'Loading local data is disabled; this must be enabled on both the client and server sides')
(second run)
mysql load 1,000,000 rows, 1,000 INSERTs of 1,000 rows: 5.97 s
postgres load 1,000,000 rows, 1,000 INSERTs of 1,000 rows: 5.60 s
mysql aggregate, 3 runs: 0.267 s, 0.304 s, 0.281 s
postgres aggregate, 3 runs: 0.044 s, 0.036 s, 0.037 s
postgres COPY FROM STDIN, 1,000,000 rows: 1.18 s
The INSERT loads were close. The aggregate gap is mostly parallel query: the plan was a Parallel Seq Scan under Gather Merge with Workers Planned: 2. With SET max_parallel_workers_per_gather = 0 the same query took 0.127 s, 0.094 s and 0.096 s, still faster than MySQL here. MySQL's plan was a table scan into a temporary table.
Two more things the numbers show. The sums agree to the cent, but avg() came back with 6 decimal places from MySQL and 16 from PostgreSQL, so a validation script that compares averages as text will report a difference that is not there. And neither INSERT path is how you would load for real. PostgreSQL's docs say COPY is almost always faster than INSERT, which the 1.2 s run agrees with. MySQL's equivalent is LOAD DATA, which the docs call usually 20 times faster than INSERT, but local_infile is disabled by default, and on this server it was, as the error shows. I did not change a shared server's settings to time it, so there is no LOAD DATA number here.
Which is better as a migration target?
Choose MySQL if
- the application is written against MySQL, or a framework's MySQL driver, and nobody is budgeted to rewrite queries;
- it depends on case- and accent-insensitive matching and uses
LIKEon those columns; - the source is an older MySQL or MariaDB with data that strict mode in PostgreSQL would reject, and you cannot clean it before the cutover (load into MySQL 8.4 with a permissive session
sql_mode, then fix it in place); - the team already runs MySQL in production and knows its backups and replication.
Choose PostgreSQL if
- you want the target to refuse bad data instead of storing an adjusted value with a warning;
- the schema migration has to be all-or-nothing, with one transaction and a clean rollback;
- you need JSON kept exactly as it arrived, or want both the exact and the indexed form;
- the target will also run reports over large tables, where parallel query helped in my test;
- the source names are a mix of cases from Windows and you want one rule for all of them.
If PostgreSQL is the answer, the MySQL to PostgreSQL step-by-step guide walks through the dump, the type mapping and the sequence reset.
Sources
- MySQL 8.4: Case Sensitivity in String Searches
- MySQL 8.4: Collation Naming Conventions
- MySQL 8.4: Server SQL Modes
- MySQL 8.4: Statements That Cause an Implicit Commit
- MySQL 8.0: CHECK Constraints
- MySQL 8.4: CHECK Constraints
- MySQL 8.4: The JSON Data Type
- MySQL 8.4: Identifier Case Sensitivity
- MySQL 8.4: Optimizing INSERT Statements
- MySQL 8.4: Security Considerations for LOAD DATA LOCAL
- PostgreSQL 16: Collation Support
- PostgreSQL 16: Character Types
- PostgreSQL 16: JSON Types
- PostgreSQL 16: Lexical Structure, Identifiers
- PostgreSQL 16: Parallel Query
- PostgreSQL 16: Populating a Database