Changing a live schema without downtime

A schema change that renames a column, drops a table, or runs a blocking operation on a large table can take an application offline in two ways. The first is a hard failure. Application code still expects a column that a migration just renamed or removed, so every query touching it starts erroring at once. The second is a soft failure. Some schema operations, such as adding a column with a default on a very large table in older Postgres versions, hold a lock that blocks reads and writes for the duration. Nothing errors, but it is an outage all the same.

Both failures are avoidable. The technique is decades old and well understood in database operations. It is worth naming because it gets skipped often enough to still cause real outages: never make a change in one step that both the application code and the database schema must update at the same moment.

A schema change that renames a column is really three changes, not one. First, add the new column beside the old one. Both exist, and the running application keeps working unmodified. Second, deploy application code that writes to both columns and reads from whichever one is populated. During this dual-write period, a background job backfills the new column for rows that only have the old one. Third, once the backfill is complete and the application no longer reads the old column, drop it. That is safe, because nothing depends on it anymore.

The backfill needs its own care. Updating every row in a large table in one transaction holds a lock for as long as the transaction runs, which causes the soft-failure downtime described above. Backfilling in small batches, with pauses between them, keeps each lock short enough that normal traffic is never blocked. The backfill takes longer in wall-clock time. For a table anyone is actively using, that is almost always the right trade.

All of the above describes migrating one database safely. Single-tenant architecture, one Postgres project per client, adds another dimension. A schema change is not deployed once. It has to reach every client's separate project, correctly, without any instance ending up on a schema version the running code does not expect. A migration that is safe in principle can still cause an incident if it succeeds on most projects and silently fails, or partly applies, on one. That client's instance is then in a state nothing was tested against.

So a single-tenant product needs migration tooling that tracks the schema version of every client project independently. It applies changes with the same additive-first discipline, and it detects and alerts on an instance that falls out of sync, rather than assuming every rollout succeeded uniformly. That is a much larger operational surface than migrating one shared database. It is part of the honest cost of the single-tenant trade. The isolation and export guarantees a dedicated database per client buys come with a rollout discipline that has to scale with the number of clients.