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

The Postgres-vs-MySQL flame war is a tedious feature of database Twitter. The honest practitioner view, after running both engines under load on customer infrastructure for years, is that they are both excellent and the right choice depends on your workload. The bad reason to migrate is "Postgres is better." The good reasons exist, and this post is about distinguishing between them.

We manage both MySQL and Postgres across AWS, GCP, Azure, and DigitalOcean. We've migrated databases both directions. This post is the framework we use to advise customers on whether to migrate, and the runbook we follow when the answer is yes.

When migrating is the right call

Genuine reasons we've seen warrant the migration, in rough order of how common they are:

1. The application has outgrown MySQL's feature set. Specifically: you've hit walls around partial indexes, expression indexes, materialised views, JSONB indexing with GIN, full-text search beyond MySQL FULLTEXT, or LATERAL joins. These are all things you can work around in MySQL, but the workarounds accumulate and eventually the application looks like Postgres-shaped logic running on a MySQL-shaped store.

2. You need stronger transactional guarantees on the schema. Postgres has transactional DDL. You can BEGIN; ALTER TABLE foo; CREATE INDEX bar; COMMIT; and roll it back atomically. MySQL's DDL is committed implicitly. For teams running complex migration tooling or schema-versioning frameworks, this is a real difference.

3. Geospatial, JSON, or analytical workloads. PostGIS has no MySQL equivalent. JSONB is materially better than MySQL JSON for indexed querying. Window functions and CTEs are more mature in Postgres (though MySQL 8.x narrowed the gap considerably).

4. The team is moving to a Postgres-heavy ecosystem. Logical replication, Timescale, Citus, Crunchy, EDB — the Postgres ecosystem in 2026 is materially richer for analytical and time-series adjacent workloads. If the rest of the platform is going Postgres, the MySQL outlier becomes a tax.

When migrating is the wrong call

Equally important. We've talked at least as many customers out of migrating as into it.

1. "Postgres is faster." It depends entirely on the workload. MySQL with InnoDB is faster than Postgres on many simple OLTP workloads, especially short-row primary-key lookups. Postgres wins on complex queries, parallel scans, and large analytical workloads. A blanket "Postgres is faster" claim is usually wrong.

2. "Our consultant said so." If the recommendation is coming without a workload analysis, ignore it.

3. The MySQL deployment is well-tuned and the team knows it. Migration costs measured in person-months of work, plus the risk of a cutover incident. If MySQL is serving the workload and the team has the operational muscle for it, the migration ROI is negative. We've seen teams burn nine months on a Postgres migration that delivered no measurable benefit because the original MySQL setup was fine.

4. The application uses MySQL-specific features extensively. Stored procedures in MySQL's procedural language, replication topology, triggers using MySQL syntax, INSERT ... ON DUPLICATE KEY UPDATE everywhere — these all translate to Postgres but the porting work is substantial.

The data type gotchas

Assuming the decision is "yes, migrate," these are the data type mismatches that bite every team. They're worth mapping out before you start, not during the migration.

Unsigned integers

MySQL: UNSIGNED INT, UNSIGNED BIGINT. Postgres: doesn't have them. You'll need to decide:

• Use the next size up (BIGINT in Postgres for UNSIGNED INT in MySQL)
• Use a CHECK (col >= 0) constraint and accept the same numeric range with signed types
• Use NUMERIC if you genuinely need the full unsigned range

Most applications don't actually need unsigned. The use is usually historical.

DATETIME vs TIMESTAMP

MySQL: DATETIME (no timezone) and TIMESTAMP (stored in UTC, converted to session timezone on read). Postgres: TIMESTAMP (no timezone) and TIMESTAMPTZ (with timezone semantics, but stored as UTC internally).

The recommendation: migrate everything that should have timezone semantics to TIMESTAMPTZ. Migrate everything that should be a "wall clock time" (birthdays, store opening hours) to TIMESTAMP. The fact that MySQL conflates these is a source of bugs; migration is a good time to fix it.

TINYINT(1) as boolean

MySQL: BOOLEAN is an alias for TINYINT(1). Postgres: actual BOOLEAN type. Most migration tools handle this automatically, but ORMs sometimes don't — check that your application code is reading true/false, not 0/1.

ENUM

MySQL ENUMs port to Postgres ENUMs, but Postgres ENUMs are immutable in awkward ways (you can't easily reorder values). We typically migrate ENUMs to TEXT with a CHECK constraint, or to a small lookup table — both more flexible.

TEXT and BLOB sizes

MySQL has TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT and the equivalent BLOB variants, each with a fixed maximum size. Postgres has a single TEXT type (effectively unlimited up to 1GB per row) and BYTEA for binary. Migrate all variants to TEXT / BYTEA.

Auto-increment

MySQL: AUTO_INCREMENT. Postgres: GENERATED ALWAYS AS IDENTITY (preferred in modern Postgres) or SERIAL / BIGSERIAL (older syntax, still works). Migration tools usually convert this correctly. Verify the sequence value is set correctly post-migration — sequences set to the wrong value cause duplicate-key errors on the first insert.

Character sets and collation

MySQL: utf8 is actually 3-byte UTF-8 (broken for emoji); you want utf8mb4. Postgres: just UTF-8, no surprises. The migration is a good time to verify you don't have any utf8 columns silently truncating 4-byte characters.

Collation behaviour differs. MySQL's default utf8mb4_general_ci is case-insensitive; Postgres' default is case-sensitive. Queries doing case-insensitive string comparison that worked implicitly in MySQL break in Postgres. Fix at the query level (LOWER(col) = LOWER(?)) or use the CITEXT extension in Postgres.

The pgloader workflow

pgloader is the right tool for most MySQL-to-Postgres migrations. It handles schema conversion, data load, and a remarkable amount of the type-mapping work automatically. It is not magic — you'll iterate on the configuration — but it's much better than building the pipeline yourself.

A representative pgloader command file:

LOAD DATABASE
  FROM mysql://user:pass@mysql-prod.internal/myapp
  INTO postgresql://user:pass@pg-target.internal/myapp
 
WITH include drop, create tables, create indexes, reset sequences,
     foreign keys, downcase identifiers,
     batch rows = 5000, prefetch rows = 5000,
     workers = 8, concurrency = 4
 
SET PostgreSQL PARAMETERS
     maintenance_work_mem to '512MB',
     work_mem to '64MB'
 
CAST type tinyint when (= precision 1) to boolean
     drop typemod keep default keep not null,
     type datetime to timestamptz drop default drop not null
     using zero-dates-to-null,
     type date drop not null drop default using zero-dates-to-null
 
INCLUDING ONLY TABLE NAMES MATCHING
  ~/^(users|orders|order_items|products|sessions)$/
 
EXCLUDING TABLE NAMES MATCHING
  ~/^(audit_log|temp_.*)$/
 
BEFORE LOAD DO
  $$ CREATE SCHEMA IF NOT EXISTS app; $$
 
AFTER LOAD DO
  $$ ANALYZE; $$;

Notes on this configuration, all hard-won:

• downcase identifiers — MySQL is case-insensitive on identifiers (on Linux), Postgres is case-sensitive. Without downcase you'll end up with "Users" requiring quotes everywhere. Don't skip this.
• zero-dates-to-null — MySQL allows '0000-00-00' dates by default; Postgres rejects them. Either fix the source data first or have pgloader translate them on load.
• CAST tinyint... to boolean — explicit, because the default behaviour depends on column metadata that's sometimes ambiguous.
• INCLUDING ONLY TABLE NAMES — for big migrations, do tables in waves. The migration of 200 tables in one pgloader run is harder to debug than five runs of 40 tables.
• batch rows and workers — tune for your target Postgres. Defaults are conservative; on a modern instance you'll want to bump these.

Typical throughput: 50,000-200,000 rows/second per worker on commodity cloud instances. A 100GB MySQL database with ~500M rows takes 30-90 minutes of bulk-load time, plus the time to rebuild indexes and ANALYZE.

The cutover

The bulk migration is the easy part. The cutover — moving the production application from MySQL to Postgres without losing transactions — is where the work is.

The shape we use, in order:

1. Run pgloader against a recent MySQL snapshot. Validate schema, fix any data-type issues, time the load. This is iterative; expect 5-10 rounds.

2. Set up logical replication MySQL → Postgres. Tools: mysql-fdw + custom replication, Debezium → Kafka → Postgres, or pgloader's continuous mode (less mature). For our Azure-hosted migrations and similar, Debezium with a Kafka topic per table is the most operationally robust shape.

3. Run the application in dual-write mode for a period. The application writes to both MySQL and Postgres, reads from MySQL. This is the only way to truly verify Postgres can handle the workload before you cut reads over.

4. Cut reads over progressively. Start with low-stakes endpoints (search, reporting). Move to higher-stakes endpoints once you have a few days of clean operation.

5. Cut writes over with a brief read-only window. Stop writes to MySQL, drain the replication lag, switch the connection string, resume writes against Postgres. This window is typically 30 seconds to 5 minutes depending on lag.

6. Keep MySQL warm for rollback for 1-2 weeks. Keep replication running in the other direction so you can fall back if something goes wrong. Don't decommission the MySQL infrastructure until you've held production traffic on Postgres through at least one peak load period.

Application-side changes

Even with pgloader handling the schema, the application will need changes. The common ones:

• INSERT ... ON DUPLICATE KEY UPDATE → INSERT ... ON CONFLICT DO UPDATE. Same semantics, different syntax.
• REPLACE INTO → INSERT ... ON CONFLICT DO UPDATE. REPLACE INTO is roughly DELETE+INSERT; the Postgres equivalent has different trigger and foreign-key behaviour.
• LIMIT n, m → LIMIT m OFFSET n. MySQL's offset-first syntax doesn't exist in Postgres.
• Backtick-quoted identifiers → double-quoted. Most ORMs handle this; raw SQL needs fixing.
• Case-sensitive comparisons. As discussed above. LOWER() or CITEXT.
• GROUP BY strictness. Postgres requires every non-aggregate column in the SELECT to appear in the GROUP BY. MySQL is loose about this by default. Queries that worked in MySQL fail to compile in Postgres.
• Connection-pool sizing. Postgres is more expensive per-connection (each connection is a process, not a thread). You'll typically want PgBouncer in transaction mode in front of any Postgres deployment with >50 concurrent connections.

We have a checklist of about 30 items we walk through during the application audit phase. The translation work is usually 2-4 weeks for a moderately complex application, plus QA and rollback rehearsal.

What the migration actually costs

A representative 200GB MySQL → Postgres migration, application of medium complexity, internal team available to do the application code changes:

• Discovery and assessment: 1 week
• Schema conversion and data-type cleanup: 2 weeks
• Application code changes: 2-4 weeks
• Dual-write setup and validation: 2 weeks
• Cutover rehearsals: 1 week
• Production cutover and stabilisation: 1-2 weeks
• Decommission MySQL: 1-2 weeks after cutover

Eight to twelve weeks elapsed time, typically. Not a weekend project. The dollar cost on infrastructure during the migration is roughly 2x normal — you're running both databases plus the replication infrastructure — for the duration of the dual-write phase.

What we do for customers

For customers considering the migration, we typically run a 1-week assessment first: workload analysis, identification of MySQL-specific features in use, schema conversion preview, estimated effort. The output is a concrete go/no-go recommendation with reasoning.

Approximately half of those assessments end with "stay on MySQL" — usually because the MySQL deployment is well-suited to the workload and the migration cost isn't justified. The other half proceed to migration, where we run the data-pipeline side (pgloader, Debezium, monitoring) while the customer's team handles the application-side code changes.

If you're staring at a MySQL deployment and wondering whether Postgres would be better, reach out for an assessment. We'll give you an honest answer, including the "don't migrate" answer if that's what the workload analysis says.

Sudhanshu K. is a Senior Cloud Database Engineer at EdgeServers (RemotIQ Pty Ltd, ABN 91 682 628 128). She has personally run MySQL-to-Postgres migrations both ways and has equally strong opinions about when each is the right call.