The migration that took six weeks.

Because the staging cluster lied to us, and we let it. pg_basebackup restores produce a database with statistics that look right and bloat that looks normal because there is no bloat: it has never been written to in anger. Our dry-runs ran in 38 hours not because the migration was 38 hours of work but because staging was, in effect, a brand-new database. We mistook that for performance.

The fix, going forward, is simple and unfun: dry-runs must be done on a clone that has been receiving production write traffic for at least seven days. Yes, it is expensive. Yes, it is the only honest way.

We considered it for a week. Postgres 14 logical replication into a reshaped target requires either a custom decoding plugin or a row-by-row apply via something like pgoutput plus an application-level transformer. We estimated the engineering cost at twelve days and chose lhm instead, which we estimated at three days.

If we did this again on Postgres 16, we would seriously evaluate logical replication with pglogical or the newer built-in features. Not because lhm failed — it didn't — but because logical replication gives you a built-in catch-up window that lhm has to simulate with trigger plumbing.

memberships_workspace_account_role, a three-column btree with INCLUDE on the role column. The first CREATE INDEX CONCURRENTLY ran for 34 hours and then aborted because a long-running transaction held an open snapshot. The transaction belonged to a weekly reporting job we'd been told had been deprecated in October.

The lesson is not that one cron got missed. The lesson is that we trusted a verbal answer to a question that needed a grep. After this incident, the cutover checklist gained a single new line: find every cron, in every repo, with an actual search.

It was the planner regression on the night of March 09, described in §06. P99 latency on the auth path went from 4 ms to 612 ms for 11 minutes. We classified it as Sev-2 because the auth path is shared with checkout and we saw a 0.4% bump in 5xx responses on /checkout during the window.

No customer ticket was filed. No data was lost. The postmortem ran 9 pages.

Because nobody used it. We added it during planning because the old tenants_v1 had a similar pg_trgm index that supported workspace search. When we audited the queries in week six, we found that the search feature itself had been moved to Elasticsearch in November 2024 and the index had been dead weight in production for fifteen months.

This is the single line item in this migration that paid for itself: −4.4 GB on disk and one fewer index for the planner to consider.

We watched pg_stat_user_tables.seq_scan and idx_scan on each partition for seven days. Apac went to zero on day one. EU dropped to zero on day two. US, weirdly, kept registering 4-6 sequential scans per hour until Wednesday, which we tracked to a single cached query plan in a long-lived service that we restarted at 14:00 UTC. After that, zero across all partitions for four consecutive days, and we dropped.

If you do not have seq_scan and idx_scan on, turn them on before your next migration. They cost almost nothing and they answer the only question that matters.

Three things. First: dry-run on a write-warmed clone, not a fresh restore. Second: read every cron in every repo before any CONCURRENTLY build, and write the grep into the runbook. Third: set statistics targets on the new tables to 1000 on every join column, by default, before the first cutover read.

Everything else — the tooling, the dual-write, the slow drain — we would do again, in the same order.