We moved an 800-million-row events table to monthly range partitions with a cutover lock of under a second. The trick was to attach the existing table as one big legacy partition, with every slow step done beforehand under weak locks.
We switched our semantic search to a cheaper 1024-dimension embedding model and every insert and query failed against the 1536-dimension column. Worse, the half-finished fix would have mixed incompatible vectors. How we migrated with versioned columns, a backfill and dual reads.
A one-off data backfill filled our Postgres RDS instance, storage autoscaling grew it from 400 GB to 2 TB, and after we deleted the temporary tables the bill stayed. RDS allocated storage cannot be reduced in place. We shrank it with a blue/green switchover and capped autoscaling.
A one-line ALTER TABLE in a deploy hung for eleven minutes and took the whole site down with it. An idle-in-transaction worker held a lock, the ALTER queued behind it, and every query queued behind the ALTER. Here is the diagnosis and the timeouts we now set.