How does row-level logical replication make a near-zero-downtime major-version upgrade of a relational database possible, and why can block/WAL-level physical replication not be used for the same purpose?

## The constraint that shapes everything An in-place major-version upgrade of a large database means downtime measured in the time it takes to convert catalog and possibly data structures — anywhere from minutes to many hours, during which the application is down. Businesses that cannot accept that window need a **side-by-side** upgrade: run the new version alongside the old, keep it continuously current, and switch traffic. "Keep it continuously current" means replication, and only one style can span versions. ## Why physical replication is disqualified Physical replay applies records like "in relation file 16384, block 912, apply this byte delta". Interpreting that requires the receiver to (a) understand the exact log-record format of the sender's version and (b) hold files whose page layout matches what the record assumes. Both are internal implementation details that change across major releases — new page header fields, changed tuple layouts, restructured system catalogs. So the version equality of a physical standby is not a policy restriction that could be relaxed; it is inherent to copying storage. A physical standby is an excellent failover target and a useless upgrade path. ## Why logical replication qualifies Logical replication decodes the same log into row-level events: table, operation, identifying key, new column values. That representation is about **data**, and data outlives storage formats. The target executes each event as an ordinary INSERT/UPDATE/DELETE using its own — newer — code, its own page format, its own indexes. That single property is what turns replication into a migration tool: the target may be a newer major version, on different hardware, with a different storage configuration, even in a different data centre. ## The migration, end to end **1. Prepare the target.** Install the new version; create roles, extensions and the schema. None of these replicate, so they are manual and must match. Verify the application's compatibility with the new version separately — the upgrade path does not excuse you from testing behaviour changes. **2. Ensure every table has a primary key.** Logical apply of UPDATE/DELETE needs row identity. Keyless tables must gain a surrogate key beforehand — on a live system that is itself a planned change, and it is the item most likely to push the timeline. **3. Start replication and take the initial snapshot.** The subscriber copies current contents, then streams changes from the snapshot point. For terabyte tables this runs for hours. Two things need watching: the copy's load on the source, and the retained change log accumulating on the source for the not-yet-caught-up subscriber. Staging the migration table-group by table-group keeps both bounded. **4. Freeze DDL.** Schema changes do not replicate, so any migration applied to the source during the window silently desynchronises the target — or stalls apply outright when a row event references an unknown column. A hard freeze, communicated and enforced in the deployment pipeline, is the standard control. Long freezes are politically expensive, which is another argument for keeping the copy window short. **5. Catch up and verify.** Once lag is small and stable, verify: row counts per table, checksums or sampled comparisons on the largest tables, and application smoke tests pointed at the new instance as a read-only target. Verification is also a rehearsal for the cutover runbook. **6. Cut over.** The only real downtime: - Stop or drain application writes (put the app in maintenance, or revoke write access). - Wait for lag to reach zero — confirm the subscriber has applied everything. - **Advance sequences** past the maximum value present, since sequence state does not replicate and skipping this produces immediate duplicate-key failures. - Repoint the application — connection string, DNS, or proxy — to the new instance. - Resume writes and watch error rates. Done well this is seconds to a couple of minutes, dominated by connection draining and DNS/proxy propagation rather than by the database. **7. Keep a rollback path.** Before opening writes on the new instance, consider configuring replication in the **reverse** direction, so changes made on the new primary flow back to the old one. If a serious regression appears an hour later, traffic can return to the old instance without losing the writes taken in between. This doubles the setup work and needs the same identity and DDL discipline in reverse — but without it, rollback means data loss, and "we cannot go back" changes the risk calculus of the whole cutover. ## The things that actually go wrong - **Initial copy longer than planned**, extending the DDL freeze and the retention exposure. - **Retained log filling the source's disk** while the snapshot runs — the failure that turns a migration into an outage on the system you were trying to protect. Alert on it with real headroom. - **Forgotten sequences**, producing duplicate keys within seconds of cutover. - **Missing roles, permissions, extensions or unpublished tables** on the target, discovered by the application rather than by verification. - **Long-running transactions on the source** delaying catch-up right when you want lag at zero. - **Behaviour changes in the new major version** — planner regressions, changed defaults, removed features — which are a version-upgrade risk the replication mechanism does nothing to mitigate. Load-test on the new version before the cutover, not after. ## The judgment being tested The mechanism is the easy half. What distinguishes a strong answer is treating it as a migration programme: a bounded copy window, an enforced DDL freeze, explicit verification criteria, a written cutover sequence with sequence advancement in it, a rollback plan, and named thresholds at which you abort. Interviewers ask this to see whether a candidate reasons about the failure modes of their own plan rather than reciting the happy path.