Zero-Downtime Schema Migrations: Expand and Contract, Locks and PostgreSQL Pitfalls

We learned how to deploy an application without downtime: multiple replicas, health checks, rolling updates. Then the first schema change arrives and a single ALTER TABLE throws all of that away. During a rollout the old and the new version look at the same database at the same time. If the schema only fits the new code, the old replicas start failing. If it only fits the old code, the new replicas never come up. Zero-downtime schema migration has exactly one rule: at every moment the database must be able to serve both versions. This article shows how to apply that rule on PostgreSQL and where it will trip you up.

Three approaches, one that works

Maintenance window. Stop traffic, run the migration, release the new version. Honest and simple. Still defensible for a single-tenant internal tool. But a product serving several time zones has no window, and every "3 a.m. deploy" tires the team and multiplies mistakes.

Hold the lock, be fast. Run the migration in the same step as the deploy and hope it is quick. It works on small tables. Then the table grows and one day the ALTER TABLE takes minutes instead of seconds. During those minutes every query against the table waits in the lock queue. The connection pool fills up, the backend times out, users see errors.

Expand and contract. Split every change into two backward-compatible halves: expand the schema first, teach the code to understand both shapes, then remove the old shape. More steps and more discipline. In return it works regardless of table size and can be rolled back at every stage. For a product running in production this is the right choice; the rest of the article is its detail.

Worked example: renaming a column

RENAME COLUMN is atomic, but the old code cannot see the column one second later. Instead you spread the change over at least three deployments.

The first deployment expands the schema and switches the code to dual writes:

ALTER TABLE orders ADD COLUMN customer_ref text;

The application now writes both customer_id and customer_ref and still reads the old one. Old replicas do not know the new column, and that does not bother them because the column accepts nulls.

The second step is backfilling existing rows. A single UPDATE orders SET customer_ref = customer_id over millions of rows means a long lock and a WAL burst. Move in small batches by primary key range:

UPDATE orders
   SET customer_ref = customer_id::text
 WHERE id BETWEEN 1 AND 10000
   AND customer_ref IS NULL;

Each batch commits in its own transaction; leave a short pause between batches so replication lag does not balloon.

The second deployment switches reads to the new column and keeps writes dual. Waiting a week here to confirm everything is healthy is not a sign of weakness.

The third deployment contracts: dual writes go away and the old column is dropped. Dropping a column is its own trap, covered below.

What locks really cost

Most forms of ALTER TABLE in PostgreSQL take an ACCESS EXCLUSIVE lock. Acquiring the lock is fast, but acquiring it requires every open transaction on the table to finish first. One long-running report query or a forgotten idle-in-transaction session holds your migration back. The real disaster is this: a migration waiting for the lock also blocks every ordinary SELECT queued behind it. One slow query brings down the whole application this way.

The defense has two layers. Set a short lock timeout on the connection that runs the migration:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN customer_ref text;

If the lock cannot be acquired within three seconds the statement fails; your migration tool retries and the application never notices. The second layer is having the migration tool do that retry itself. Most tools do not do it out of the box; you wrap them.

Rather than memorizing which statement takes which lock and whether it rewrites the table, measure. Since version 11, adding a column with a constant default does not rewrite the table. Adding a NOT NULL constraint requires a full scan; the fix is to add CHECK (col IS NOT NULL) NOT VALID first and run VALIDATE CONSTRAINT in a separate transaction. Validation takes only a SHARE UPDATE EXCLUSIVE lock and does not block reads or writes.

Indexes

CREATE INDEX blocks writes to the table. In production always use the concurrent form:

CREATE INDEX CONCURRENTLY idx_orders_customer_ref ON orders (customer_ref);

This statement has two habits. It cannot run inside a transaction block; if your migration tool wraps every migration in BEGIN automatically, you must disable that for this migration. Second, if it is interrupted it leaves behind an index marked INVALID. That index costs maintenance but serves no queries. Before rerunning the migration, find rows in pg_index with indisvalid = false and drop them. Teams that skip this spend weeks later on the "index exists but the query is slow" puzzle.

Why dropping a column bites back

DROP COLUMN holds its lock briefly and does not rewrite data. The problem is not in the database but in the application. Many ORMs read a table with SELECT * or with the column list from the model definition. The moment the column disappears, still-running old replicas get "column does not exist". So the order is: first ship a version whose code never mentions the column, confirm every replica has rolled over, and only then drop it. Most ORMs let you mark a column as ignored; that is exactly the job of the intermediate release.

The application side: speaking both shapes

The hard half of a schema migration is not SQL. It is making the application behave correctly against both schemas. Patterns that work in practice:

• Make the code path that writes the new column switchable by configuration. If you see an error you flip the switch instead of rolling back a deploy.
• Run a consistency check during the dual-write period: a query that counts rows where the old and new columns disagree, with an alert threshold. Dual writes you do not measure are not trustworthy.
• Keep migration files and application code in the same repository and the same review. If a separate "database team" runs migrations, responsibility for keeping the two in sync becomes nobody's.

Scale problems in multi-tenant schemas

If you use a schema or a database per tenant, every migration runs hundreds of times. Three extra rules apply. The migration must be idempotent: if it dies halfway in one tenant and is rerun, it must not fail the second time. Track which tenant is on which version in one place; "all done" is only true if it was measured. And a tenant that signs up while the migration round is in progress must still receive the correct version; otherwise the round finishes, the new tenant is left on the old schema, and its first request fails.

When not to choose this path

The expand and contract cycle means three deployments and a week of waiting per change. On a system with no production users yet that burden is pointless; drop the table and recreate it. The same goes for single-tenant internal tools with an accepted maintenance window. The discipline pays off where replicas come and go and downtime lands directly on users.

One last warning: "this table is small, it will be fine" is among the most expensive sentences in operations. The small table grows, but the migration habit stays the same. Make lock timeouts, concurrent indexes and batched backfills a habit on day one, so there is nothing to change when you grow.