The myth of 'Zero Downtime' migrations

Every time a big database migration gets planned, someone in management reaches for the phrase 'zero downtime.' It's a noble goal, but for a certain class of migration, the ones involving actively-written transactional state, not stateless services, it's often a mathematical impossibility disguised as an engineering requirement. We've watched teams burn six weeks chasing an unachievable zero and end up with a worse outcome than if they'd just planned 30 seconds of downtime from day one.

The case that crystallized this for us was a client migrating their primary transactional Postgres instance from a single self-managed EC2 box to RDS Multi-AZ, with the database actively serving writes the entire time. No maintenance window the business would accept. The team's initial plan used logical replication to stream changes to the new instance, then a DNS cutover once replication caught up. The problem: logical replication lag under their peak write load never fully closed to zero, so any cutover point risked either dropping in-flight writes or briefly having two instances both believe they were primary.

This is the CAP theorem showing up in a change-management meeting, not a whiteboard. Under any replication lag greater than zero, you have exactly two options at cutover: accept a small window where you might lose or duplicate a write (an availability-favoring choice), or briefly stop accepting writes entirely until replication is provably caught up and you can atomically flip primary (a consistency-favoring choice). For a payments-adjacent transactional database, we weren't going to accept the first option, and no amount of clever tooling changes that tradeoff. It's structural, not a bug to be engineered away.

So we planned for the second option explicitly, and made it small instead of pretending it wouldn't exist. The playbook: run logical replication until lag was consistently under 500ms for a full hour of peak traffic, then execute a short, scripted cutover. Pause the application's write path (reads continued to serve from the old primary, slightly stale, which the product could tolerate), wait for replication lag to hit exactly zero confirmed via a synced sentinel row, promote the new instance, flip the connection string via a feature-flagged config (not DNS, which has its own propagation lag and caching problems), and resume writes. Total write-path pause: 28 seconds, scripted and rehearsed twice against a staging replica of production data before the real cutover.

The part that mattered more than the runbook was the SLO conversation beforehand. We didn't let 'zero downtime' survive as an unstated requirement. We got explicit sign-off from the client's leadership on a defined error budget: up to 60 seconds of write unavailability, communicated in advance via status page, during a specific low-traffic window (Tuesday 3am their primary user base's timezone). That turned an ambiguous, unachievable promise into a scoped, approved engineering decision with a named owner.

We also instrumented the cutover itself as if it were a production incident being actively monitored, not a one-off script: dashboards tracking replication lag, write error rate, and connection pool saturation, live during the 28-second window, with a scripted abort-and-rollback path if lag hadn't hit zero within a defined timeout. We never needed the rollback path, but not having tested it would have meant the actual failure mode, a stuck cutover with no way back, was the one thing we left untested.

The database migrated with zero data loss, zero corruption, and 28 seconds of scoped, communicated write unavailability at 3am against a workload that was already at its lowest traffic point. Nobody filed a support ticket. The alternative, chasing a genuine zero against active transactional writes, would have meant either accepting silent data loss risk at cutover, or extending the project by weeks trying to engineer around a theorem that doesn't have a workaround.

The generalizable lesson: 'zero downtime' is the right goal for stateless services behind a load balancer, where you can drain and cut over one instance at a time with no coordination problem. For actively-written transactional state, it's usually not achievable without accepting a consistency risk you shouldn't accept. The better question to bring to a migration isn't 'can we get to zero,' it's 'what's the smallest, most tightly-scoped downtime window we can define, test, and get explicit sign-off on,' and then engineering that window down as far as it will actually go. This is the same rigor we bring to any high-stakes cutover in our site reliability engineering work.