The Database Changed Before the Deployment Finished

Renaming a database column looks like one line of SQL.

ALTER TABLE orders
RENAME COLUMN total_cents TO total_amount_cents;

The change is correct. The new name is clearer. The migration takes almost no time on a small table.

And it can still break production before the new application finishes deploying.

The problem is not the rename itself. The problem is that a deployment is not one instant. For a while, old application instances and new application instances may run at the same time. Requests continue arriving. Background jobs keep working. Someone may roll the application back while the database stays exactly where the migration left it.

That overlap changes the question.

We are no longer asking, "Does the new code work with the new schema?" We are asking whether every version that can still receive traffic works with the database as it exists right now.

Deployment is a period of mixed reality

Imagine three application instances behind a load balancer.

The deployment replaces them one at a time:

minute 0: old, old, old
minute 1: new, old, old
minute 2: new, new, old
minute 3: new, new, new

If the migration renames total_cents before minute one, every old instance immediately starts querying a column that no longer exists.

If the migration runs after minute three, every new instance may start querying total_amount_cents before the column exists.

Stopping all traffic, changing the database, and starting the application again avoids overlap. It also creates downtime and turns rollback into a much sharper operation. That may be acceptable for a small internal tool. It is a poor default for a checkout that people are using while we deploy it.

Zero-downtime migration is not a special PostgreSQL command. It is a sequence of changes where compatibility is preserved at every step.

Change the system so old and new versions can coexist before you ask either one to disappear.

Expand before you contract

The usual pattern has two phases: expand the schema to support both versions, then contract it after the old version is gone.

For our rename, the safe path begins by adding the new column without removing the old one:

ALTER TABLE orders
ADD COLUMN total_amount_cents INTEGER;

Old code still reads and writes total_cents. Nothing has broken.

Next, deploy code that understands both columns. During the transition, writes keep them synchronized:

await database.transaction(async (transaction) => {
  await transaction.orders.update(orderId, {
    totalCents,
    totalAmountCents: totalCents,
  });
});

Reads prefer the new value but can fall back while old rows are being migrated:

SELECT COALESCE(total_amount_cents, total_cents) AS total_amount_cents
FROM orders
WHERE id = $1;

Now old instances can keep using the old column, and new instances can begin using the new one. The database supports both realities.

Only after every row has the new value, every writer populates it, and no old application instance remains do we remove total_cents.

That final removal is the contract phase. It should be the least exciting part of the change because all meaningful traffic moved away from the old column earlier.

Backfill is production work

Adding an empty column is usually cheap. Filling it across a large table may not be.

This looks harmless:

UPDATE orders
SET total_amount_cents = total_cents;

On millions of rows, one giant update can hold locks, generate a large amount of transaction log, create replication lag, and compete with the queries serving users. The statement may finish successfully while making the application painfully slow.

Backfill in batches instead:

UPDATE orders
SET total_amount_cents = total_cents
WHERE id IN (
  SELECT id
  FROM orders
  WHERE total_amount_cents IS NULL
  ORDER BY created_at
  LIMIT 1000
);

Run a batch, commit it, observe the database, then continue. The exact size depends on the table, hardware, traffic, and replication setup. There is no heroic batch size that is correct everywhere.

The important shift is treating backfill as a workload, not as setup. It consumes the same database resources as customer requests. It needs progress metrics, rate control, and a way to resume after interruption.

Constraints can arrive later

The new column should eventually be NOT NULL. Adding that requirement immediately would reject writes from old instances that know nothing about the column.

So the order matters:

Add the nullable column.
Deploy dual-write code.
Backfill existing rows.
Verify that no null values remain.
Add the constraint.

In PostgreSQL, validating a constraint separately can reduce the risky part of the operation on a busy table:

ALTER TABLE orders
ADD CONSTRAINT orders_total_amount_present
CHECK (total_amount_cents IS NOT NULL) NOT VALID;

ALTER TABLE orders
VALIDATE CONSTRAINT orders_total_amount_present;

Once validated, the schema can be tightened further according to the database version and operational needs.

The point is not memorizing this exact syntax. It is separating "start enforcing this for new changes" from "prove every old row already satisfies it." Large production changes become safer when those are not forced into one moment.

Indexes have deployment behavior too

Suppose the new application also needs to find pending orders by creation time:

CREATE INDEX orders_pending_created_at_idx
ON orders (created_at)
WHERE status = 'pending';

Building a normal index can block writes depending on the database and operation. PostgreSQL provides CREATE INDEX CONCURRENTLY so the table can continue serving writes while the index is built:

CREATE INDEX CONCURRENTLY orders_pending_created_at_idx
ON orders (created_at)
WHERE status = 'pending';

The trade-off is that concurrent creation takes longer, does more work, and cannot run inside a normal transaction block. If it fails, it may leave an invalid index that has to be removed before retrying.

"Non-blocking" does not mean "free." Watch CPU, I/O, lock waits, and replication lag while it runs.

This is where migration tools can become misleading. A tool can generate valid SQL without knowing whether Tuesday afternoon is a safe time to run it against the largest table in the system.

Destructive changes deserve their own deployment

Dropping a column, narrowing a type, or removing an enum value is fundamentally different from adding something.

An additive change creates room. A destructive change removes a path that some code may still use.

I prefer to separate destructive cleanup into a later deployment. Not five minutes later. Later enough that we can confirm:

no old instances remain;
no background worker uses the old shape;
no scheduled job wakes up with old code;
no external consumer depends on the field;
rollback no longer requires the old schema.

That delay can feel untidy because the database carries two columns for a while. Temporary duplication is cheaper than a clean schema that breaks a rollback.

Rollback is a compatibility test

A good deployment plan asks what happens if the application must return to the previous version.

With the expanded schema, rollback is straightforward: old code still sees total_cents. New rows created during the deployment have both values because the transition code dual-wrote them.

After the old column is dropped, that rollback path disappears.

This is why database rollback rarely means "run the migration file backwards." Recreating a dropped column does not restore its lost data. Converting a type back may not recover the original representation. A migration can be reversible in syntax and irreversible in meaning.

For destructive changes, forward recovery is often safer: deploy a fix that works with the current schema instead of trying to rewind production data through time.

The frontend can be part of the overlap

Static frontend assets are cached. A user may keep a tab open for hours. A service worker may serve an older bundle after the backend deployment is complete.

That means API compatibility often outlives server-instance compatibility.

If a response field changes, the expand phase may need to return both old and new shapes long enough for active clients to age out. If an enum gains a value, older clients need a fallback. If a request becomes stricter, the backend may need to accept the old shape during the transition.

The browser is not automatically upgraded because the deployment dashboard turned green.

This is one reason I prefer additive API and schema evolution. It gives independently deployed pieces room to move without pretending they change together.

A migration needs evidence, not optimism

Before contracting the schema, I want evidence that the new path owns production traffic:

null count for the new column is zero;
old-column reads and writes have stopped;
backfill progress is complete;
query latency and lock waits stayed acceptable;
replicas caught up;
the new application version is stable;
rollback expectations are explicit.

Some of this comes from database queries. Some comes from logs and metrics. The important part is that removal is triggered by observed usage, not by "the deploy finished, so it should be fine."

The safest database migration is often boring and slightly slow: add, deploy, copy, observe, enforce, remove.

That is not ceremony around one line of SQL. It is how we change a shared truth while the system keeps using it.

The next article looks at a different kind of shared state. Redis is fast enough to hide many problems, which is exactly why it needs a clear job before we put it between the application and the database.

If a migration in your system still requires everyone to hold their breath during deployment, tell me which step feels dangerous. That point usually reveals the compatibility assumption we have not made explicit yet.