Playbook · Migrations

Updated

What breaks in a MySQL to PostgreSQL migration?

In a MySQL to PostgreSQL migration, the rows are the easy part. What breaks is behaviour: MySQL compares text without caring about case by default, PostgreSQL does not, and the SQL for upserts, quoting and dates differs. Most of the work is in the application queries.

MySQL’s default server collation is utf8mb4_0900_ai_ci: accent-insensitive and case-insensitive. So in MySQL, "a", "A" and "à" compare as equal by default. In PostgreSQL they don’t.
Source: MySQL 8.4 Reference Manual, read 17 Nov 2026

From my work

A move is not the end of the database work. On one project that runs on PostgreSQL, the client saw a spike in the AWS bill. CloudWatch led me to queries against an old logs table of more than 24 GB. Most queries pulled by date, so I partitioned the table by quarter. I also found and deleted EC2 instances nobody used any more.

Reasons to switch databases

Usually for the features: richer types, JSON support, extensions, row-level security, and a licence with no commercial edition behind it. Sometimes because the rest of the company already runs PostgreSQL and one database is cheaper to run than two, and easier to be on call for.

The differences that break queries

  • Case in comparisons. A login lookup that matched "Ana@x.com" against "ana@x.com" in MySQL stops matching. PostgreSQL offers citext or nondeterministic collations for this. Its own docs say nondeterministic collations are often the better choice.
  • Case in names. PostgreSQL folds unquoted names to lower case, and quoted names become case-sensitive. A table created as "UserAccounts" must be quoted that way forever.
  • Quoting. MySQL backticks become double quotes. A find-and-replace job, until a backtick turns up inside a string.
  • Upserts. INSERT ... ON DUPLICATE KEY UPDATE becomes INSERT ... ON CONFLICT ... DO UPDATE, which needs a named conflict target.
  • Booleans and dates. TINYINT(1) flags become booleans, and MySQL zero dates such as 0000-00-00 have no PostgreSQL value.

Tools: pgloader and what it leaves to you

pgloader reads the MySQL schema and builds matching PostgreSQL tables, indexes and foreign keys. Its default rules turn TINYINT(1) into boolean, zero dates into NULL, and AUTO_INCREMENT into serial or bigserial. It lower-cases names by default. Triggers are not migrated, so any logic in triggers, and in stored procedures, has to be rewritten by hand. First someone has to remember the triggers are there.

Steps for a MySQL to PostgreSQL migration

  • Inventory the SQL: ORM queries, raw queries, reports, triggers, procedures. Include the report nobody owns but finance still opens every month.
  • Run pgloader into a staging PostgreSQL and run the full test suite against it. Expect red. That is what staging is for.
  • Fix the case, upsert and date issues in the application.
  • Plan the cutover: a short write freeze and a final sync, or a change-data-capture tool if you can’t stop writes.
  • Keep MySQL read-only for a while after the switch, as a way back.

What drives the cost

How much raw SQL the app has, how much logic lives in triggers and procedures, and how long you can freeze writes. I found no reliable market price for MySQL to PostgreSQL work, so I don’t quote one. The 14-day Trial ($995) covers the assessment and the first fixes, and ends with a written plan for your database.

Read next

Sources

Start

Tell me where the product is stuck.

In 30 minutes we look at where things stand, what you need from a CTO right now, and which option fits. Either way, you leave with a clear next step.

How the call goes

  1. 01You tell me about the product, the team and the next 6 months.
  2. 02I ask about the code, the stack and what keeps breaking.
  3. 03We pick the option that fits, or agree that none does.

09:00 to 20:00 CET

Founding rate: the first 3 clients get 20% off, in return for a named testimonial and a case study. On a monthly plan, the 20% applies to the first month.