MySQL to Postgres migration — when it's worth it, the gotchas, and the pgloader workflow

We've migrated databases both directions: MySQL → Postgres for teams that need rich indexes, real ACID semantics on DDL, and the JSONB story; Postgres → MySQL for teams that found Postgres's connection model too expensive for their workload. The migration is a real piece of engineering either way, and the wrong reasons to do it ("we like it better") will burn months.

Here is the decision framework, the data-type traps, and the pgloader workflow we use when MySQL → Postgres is the right call.

The pgloader entry point

LOAD DATABASE
  FROM mysql://user:pass@source/dbname
  INTO postgresql://user:pass@dest/dbname
 
WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000
 
CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type tinyint when (= precision 1) to boolean using tinyint-to-boolean
;

pgloader handles 80% of the type translation automatically. The remaining 20% is where the engineering happens.

The full write-up covers:

• When migration is the right answer (and the three common bad reasons)
• The data-type traps: zero dates, tinyint(1) vs boolean, BLOB vs bytea, character set handling
• Sequence reset after data load
• Application-layer changes (case-sensitivity, LIMIT/OFFSET performance, RETURNING instead of SELECT LAST_INSERT_ID)
• The dual-write cutover pattern for live databases
• Verifying row counts and checksums between source and destination

Reach out if you're weighing this migration for a real workload.