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. The app code still expects a column that a migration just renamed or removed. So every query that touches it starts failing 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's worth naming, because teams skip it often enough to cause real outages. Never make a change in one step that the app code and the database schema must both 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. If you update every row in a large table in one transaction, it holds a lock for as long as it runs. That causes the soft 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 cleanly. No instance can end up on a schema version the running code doesn't 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 rule. It also spots any instance that falls out of sync and raises an alert, instead of assuming every rollout worked. That is a much larger operational surface than migrating one shared database. It is part of the single-tenant trade. A database per client buys you isolation and a full export. It also calls for rollout discipline that grows with the number of clients.