The proposal shows up again, and it reads well:

We should move the primary off MySQL to Postgres. Ops get simpler, we lose the sharding workarounds, and the JSON and geo stuff we keep bolting on becomes native. It’s mostly a mechanical port.

This is the fourth version of that message in about two years. The technology pair rotates. One quarter it’s MySQL to Postgres, another it’s Redis to a real queue, another it’s Elasticsearch to Postgres full-text to retire a cluster. The shape is identical every time, and it usually lands a few days after a genuinely good engineering post made the move look like a two-sprint project.

And the proposal is usually right about the destination; the article that inspired it was probably good, with a real benchmark behind it. What it’s missing is a price. So rather than hold the same meeting a fifth time, this is the meeting, written down once: a migration you’d describe as “mostly a mechanical port” is five migrations wearing one name, each with its own cost, its own risk, and its own way of not finishing.

The reasons that don’t survive

The weak reasons share a tell: they price only the query surface. “Postgres is just better” is an aesthetic, not a requirement. A benchmark from a company with a different workload measures their access pattern, not yours. Your web framework changed its default adapter. The team knows the target engine better and it feels like home turf. None of these is dishonest, and each is a real pull; they just buy something smaller than what they cost once the other four migrations are on the invoice.

The model cuts both ways. In July 2016 Uber published a detailed write-up of switching from Postgres back to MySQL, citing write amplification, replication behavior, and connection-handling overhead under their workload. That post is both the caution (someone reads a famous migration story and wants to run it in reverse) and the proof that “forward” depends on whose workload you’re standing in. Which engine is better in the abstract is the wrong question; what matters is whether your reason survives your five layers.

The five migrations

Here they are, stacked from the one everyone estimates to the one nobody does.

Layer one: the query surface

This layer is semantic, not syntactic. The mistake is to picture a migration as a big find-and-replace over SQL text. Most queries do port cleanly, and the ones that throw a syntax error are the safe ones, because the migration stops and makes you fix them. The dangerous class is the queries that run on both engines and mean something different on each.

The clearest example is isolation. InnoDB defaults to REPEATABLE READ with next-key (gap) locking; Postgres defaults to READ COMMITTED with MVCC snapshots taken per statement. The same transaction, ported verbatim with no rewrite error, now takes different locks, sees a different snapshot of concurrent writes, and produces a different set of serialization failures under contention. Retry logic tuned against InnoDB’s gap-locking deadlocks meets a different population of conflicts on Postgres. Nothing in the port flags this. It surfaces as a behavior change under load, in production, weeks later.

Collation and equality are the next seam. A utf8mb4_general_ci column compares case-insensitively; a text column in Postgres does not. A UNIQUE index on an email column silently changes meaning across the move, from “no two emails differing only in case” to “exact-byte uniqueness,” and rows that collided on one engine become distinct on the other. The Postgres escape is citext or a nondeterministic ICU collation, both of which are a decision, not a default. (The same class of drift inside a single MySQL estate is its own long story, covered in Collation Drift.) Upsert is another: ON DUPLICATE KEY UPDATE fires on a collision with any unique key, while Postgres ON CONFLICT makes you name the arbiter constraint. On a table with two unique indexes those are simply different programs, and the port has to decide which one you meant, on every upsert.

GROUP BY belongs on the same list. MySQL spent years allowing a select list that names columns absent from the GROUP BY, returning a value from some row in the group, and schemas older than the ONLY_FULL_GROUP_BY default are full of these queries. Postgres rejects them outright, but the error message isn’t the work: the work is deciding what each query meant, because “a value from some row” was never a specification, and the report on top has been shipping whatever InnoDB happened to return. Uniqueness carries one more flavour past the collation problem: MySQL will enforce a UNIQUE key over a column prefix, UNIQUE (email(191)), so values that agree for the first 191 characters collide there and coexist happily on Postgres, where a unique index covers the whole value or, via an expression index, whatever slice you name explicitly. The NULL question, for once, ports cleanly: both engines treat NULLs as distinct in a unique index and will happily hold many of them, though Postgres 15 added NULLS NOT DISTINCT for anyone who wants the constraint to mean one.

The queries the application sends are also not the whole of this layer. A database that has been in production for years runs code of its own: triggers, stored procedures and functions, scheduled events, the ON UPDATE CURRENT_TIMESTAMP behaviors that are tiny triggers in disguise. None of it ports mechanically. MySQL’s stored-routine dialect and PL/pgSQL are different languages, so every routine has to be found, read, and rewritten by someone who can say what it was for, and some of them were written by people who left. Optimizer hints are the quiet member of the family: MySQL takes them inline, Postgres has none without an extension, and every query that was hand-steered toward a plan renegotiates with a new planner from zero.

Then strictness, which is where the data move and the query surface bleed together. MySQL, especially schemas that predate strict mode or ran with it relaxed, has tolerated silent truncation, out-of-range coercion, and zero-dates like '0000-00-00' for years. Postgres rejects all of it on insert. The backfill is the moment you discover exactly what the old system was quietly accepting, one constraint violation at a time, on data that’s been sitting there looking fine. Under that headline sit the smaller resets: NULL sorts low in MySQL ORDER BY and high in Postgres, so NULLS FIRST/NULLS LAST becomes load-bearing; there are no unsigned integers in Postgres, so a BIGINT UNSIGNED column becomes NUMERIC or a CHECK; AUTO_INCREMENT becomes an identity column backed by a sequence with its own restart and ownership semantics; and MySQL’s TIMESTAMP-versus-DATETIME timezone handling maps onto Postgres timestamptz-versus-timestamp in a way that changes what a stored instant means. Each is small. There are a dozen of them, and each one is a place a value can shift without an error.

Which sets the acceptance bar for this layer: a query hasn’t ported when it runs, it’s ported when it returns the same rows. Everything above fails silently, so the check that catches it is a diff, not a green test suite. Replay production reads against both engines and compare result sets, row counts and checksums first, ordering and edge values after.

Layer two: the coupling surface

This is the layer that doesn’t move with the database. Everything built on top of the current engine (the ORM, the query builders, the raw SQL living beside them, the fixtures, the serializers, the analytics jobs reading a replica) grew up expecting a particular database to be there. The schema is the innocent party; the code around it is where the exit gets expensive, and that argument has its own post: ORMs Are a Coupling, Not an Abstraction.

At least the application lives in a repo someone can grep. The coupling that goes unmapped is the part that never made it into version control: the crontab on a utility box calling mysqldump nightly, the retention script pruning old rows at 3am, the mysql -e one-liners inside deploy scripts on a bastion host three people know about. Reports are the class that hides best, because they run on a cadence. A month of watching the query log catches the weekly ones. The quarterly close job and the year-end export fire long after the audit ended, and the first anyone hears of them is when finance asks why the numbers stopped.

The database also feeds systems, on top of serving them. If the Elasticsearch index and the Redis cache are populated by CDC, that feed is reading the binlog; Postgres offers logical decoding instead, so the pipeline gets rebuilt rather than repointed, and every consumer downstream of it comes along. The infrastructure code is flavored too. The Terraform module that provisions the instance, the Helm values, the Ansible role, the backup tooling and its restore runbook, the monitoring templates: each says mysql somewhere, and each occurrence is its own small piece of work. Underneath all of it sits discovery, the DNS names and connection strings and proxy configs everything above uses to find the database, and the proxy itself usually changes species on the way (ProxySQL out, PgBouncer in), with different pooling semantics for every client that connected through it.

None of this is a reason to stop. It’s the rest of the map, and it’s usually the layer where the two-sprint estimate quietly doubles before a single row has moved.

Layer three: the data move

This is the layer with a live failure mode. Moving the schema is easy. Moving the data under continuous writes is the actual project: an initial backfill, a dual-write phase where the application writes to both engines, a reconciliation process that proves the two agree, and a cutover. Each phase has a way to go wrong, and the dual-write phase is the one that quietly becomes permanent.

Reconciliation is a whole subsystem, close kin to the cross-instance reconciliation jobs in Scale the Pattern, Not the Instance, and it needs an owner. The cutover is the easy-looking part that goes badly when the first four items were rushed, because cutover is where every deferred difference in layer one arrives at once.

Layer four: the operational reset

This layer sets your intuition back to zero. A team fluent in operating MySQL is, on the day of cutover, a junior team operating Postgres. InnoDB stores the table in primary-key order and every secondary index carries the primary key as its pointer, so index design has been shaped by clustering intuition. The multi-tenant habit of leading a composite primary key with the least selective column, (tenant_id, id), exists to pack a tenant’s rows into adjacent pages, and a decade of schema decisions can lean on that locality. Postgres tables are heaps, every index is a secondary index into the heap, and the primary key no longer dictates where rows live, so the ordering stops buying anything physically and often flips around, or shrinks to the surrogate key alone, once it’s chosen for lookup shape instead. (CLUSTER exists, but it’s a one-shot rewrite under an exclusive lock, not a property the table maintains.) Postgres forks a backend process per connection instead of MySQL’s thread-per-connection, which makes a connection pooler mandatory on day one rather than an optimization for later. VACUUM, bloat, autovacuum tuning, and transaction-ID wraparound are an operational surface MySQL refugees have simply never carried, and it doesn’t announce itself until it’s a problem. The migration-discipline toolchain changes too: the gh-ost and pt-online-schema-change muscle memory gives way to transactional DDL plus lock-timeout discipline and tools like pg_repack. Every dashboard, alert threshold, and piece of on-call pattern recognition is calibrated to the old engine’s failure signatures. None of it is unsolvable. It’s a team relearning operations while carrying a pager.

Layer five: the long tail

This is the default outcome rather than the failure case. The honest steady state of a large migration is 90% done, and staying there. The last 5% of traffic is the batch job someone wrote in 2019, the reporting query with a MySQL-specific function, the third-party integration hard-wired to the old connection string, the one service whose owner left. That last slice is disproportionately expensive because it’s the part with no clean port, and the economics of finishing it never quite beat the economics of leaving the old system running for just those callers. So the old database stays up. Two databases in production, permanently, was nobody’s plan and is the most common actual result. The migration didn’t fail. It just never ended.

Pricing it honestly

An estimate that prices all five layers looks different from the one in the proposal. It has the query port, plus a line for the semantic differences that need behavior testing under concurrency, not just a compile. It has the coupling-layer rewrite, often the largest single line, with the off-repo pieces (the crontabs, the reports on a quarterly cadence, the CDC feeds, the IaC) inventoried rather than discovered. It has the data move as its own project with a dual-write phase, a reconciliation service with an owner, and a cutover plan. It has an operational-readiness line: pooler, VACUUM tuning, new dashboards, on-call ramp. And it has an explicit answer to the long tail: which callers get migrated, which get deprecated, and what the drop-dead date is for the old system, with a name attached.

Priced honestly, some migrations still say go. The estimate kills the ones that were only ever justified by layer one, and it does so early, on a spreadsheet, instead of eighteen months later with two databases running and a reconciliation job nobody trusts.

The move that turns the estimate into evidence is to migrate one bounded subsystem first, end to end, through all five layers. Pick a schema with real writes but a small blast radius. Port the queries, cut the coupling, run the dual-write and reconciliation, operate the new engine in production for that slice, and actually finish the long tail for it. That first subsystem is price discovery. The number it produces (in engineer-weeks, in surprises, in how long dual-write really ran) is the multiplier for everything else, and it’s a real number rather than the one from the article that started the conversation.

The reasons that do

The reason that survives, and it is rarer than it sounds, is a ceiling: the current engine is out of road for the workload it carries. Out of road means the boring work is actually done. The queries are tuned, the schema has been through the redesigns it needed, the hardware has been scaled to the point where money stops helping, and what remains is a mismatch between the workload and the engine itself: a write path the storage engine can’t sustain, a replication model that can’t produce the topology the business needs. Most databases described as outgrown are nowhere near this point; they have years of headroom sitting in unglamorous query and schema work, which is its own series. The external version of the same reason is a forcing function, the way the Elastic and Redis relicensings in 2021 and 2024 made staying put stop being an option for reasons that had nothing to do with anyone’s workload. Either way the shape is the same: staying as-is has genuinely stopped being available, and a ceiling like that can dwarf all five layers combined.

A capability gap, on its own, doesn’t clear the bar. Needing PostGIS, or better full-text, or a vector index is a reason to add that capability, and adding it doesn’t require moving the system of record. The shape that holds up is a specialized store at the edge: fed from the primary by CDC or a sync job, serving the geo or search lookups, owning nothing authoritative. That has a real cost too (a second system to run, a sync path to own, a consistency lag to document), but it’s bounded, it’s reversible, and it leaves the primary’s write path untouched. Migrating the primary to acquire a feature buys the feature plus all five layers above; the lookup store buys the feature alone. If the day comes when the geo workload stops being a lookup and becomes the primary workload, that’s the ceiling case, and the migration conversation becomes the honest one to have.

So when the next version of that message arrives, grant the destination and skip the argument about it. Ask instead what the plan is for layer five: which callers are on the old engine the day you’d like to call it done, who owns turning the old system off, and what the date is. A proposal with a good answer there has usually thought about the other four. A proposal that hasn’t considered layer five is estimating layer one and calling it the migration, and it will get you a second database to operate rather than a replacement for the first.

The corners cut here also write the next proposal. A Postgres ported in a hurry, indexed on MySQL instincts, and operated by a team that never got the ramp time looks exactly like a database worth migrating away from. Give it two years, one good article about a shinier engine, and the message from the top of this post arrives again with the pair rotated.

That’s the whole map. Send this instead of taking the meeting.