Topic: PostgreSQL

A schema migration is not one SQL statement: expand, backfill, and observe before you contract

A PostgreSQL schema migration must account for existing rows, older workers, and newly deployed code. Use expand, repeatable backfills, and evidence about locks, indexes, and constraints before removing an old path.

Renaming a column, changing its type, or adding a constraint can look like one ALTER TABLE statement. In production, though, a schema belongs to more than the database. Deployed web code, still-running workers, queued jobs, and old rows all share the same contract.

The hard part of a migration is rarely SQL syntax. It is letting old and new contracts coexist briefly, then knowing when the old path is genuinely safe to remove. This article uses an abstract orders table: move from delivery_state to delivery_state_v2 without betting that every running process changed in the first deployment.

Expand first: add room for compatibility, not a reason to remove a column

Start by adding a column so old and new application versions can both work:

ALTER TABLE orders
ADD COLUMN delivery_state_v2 text;

A new column without a DEFAULT reads as NULL for existing rows. It does not rewrite every old row immediately. ALTER TABLE can still need an ACCESS EXCLUSIVE lock by default, however, so a small-looking statement is not automatically safe at any time. Set a lock_timeout for this connection. If it cannot get the lock, fail and retry in a planned window instead of letting a release wait indefinitely.

SET lock_timeout = '3s';

Then deploy compatible code: new writes fill both columns, while reads prefer delivery_state_v2 and fall back to delivery_state when it is null. That dual-write and fallback period may be longer than expected. It protects older workers, delayed jobs, and requests still reaching an older application version.

That is the value of the expand-contract pattern: make both versions safe first, then change code and data. Older code can keep reading the old column while newer code gradually fills the new one.

Backfill next: small, repeatable batches that do not overwrite new data

New code only covers future writes. Historical rows still need a backfill. Updating an entire large table in one transaction creates a long transaction, more write load, more pressure on autovacuum, and an awkward recovery point if it fails.

A conservative approach is to take a small batch of rows that have not been filled. Assuming id is the primary key, one batch worker can look like this:

WITH batch AS (
  SELECT id
  FROM orders
  WHERE delivery_state_v2 IS NULL
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders
SET delivery_state_v2 = delivery_state
FROM batch
WHERE orders.id = batch.id;

The example updates only rows where delivery_state_v2 IS NULL, so it is safe to rerun. Once compatible application code has written a new value, the backfill does not overwrite it. SKIP LOCKED lets multiple backfill workers skip rows another worker has locked instead of waiting. It fits work distribution, not a shortcut for ordinary queries that need a complete view.

There is no universal batch size. Start small, record affected rows, duration, retries, and errors, then adjust to the observed write load. Useful rollback is not hurried reverse data work. It is keeping the old read path until the new data and write behavior have proved stable.

Build indexes and enforce constraints in stages too

If the new query needs an index, a regular CREATE INDEX blocks writes on its table. Production work commonly uses:

CREATE INDEX CONCURRENTLY orders_delivery_state_v2_idx
ON orders (delivery_state_v2);

CONCURRENTLY keeps the table writable, but it usually takes longer, scans twice, and waits for existing transactions. It cannot run inside a transaction block. A failed build can also leave an INVALID index, so a release needs to check failure state and cleanup rather than treating a submitted command as proof of success.

Constraints can be staged as well. First make new writes follow the rule, then schedule verification of old rows as an observable operation:

ALTER TABLE orders
  ADD CONSTRAINT orders_delivery_state_v2_present
  CHECK (delivery_state_v2 IS NOT NULL) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_delivery_state_v2_present;

NOT VALID skips an immediate scan of the existing table, but the constraint still applies to new INSERT and UPDATE statements. The later VALIDATE CONSTRAINT checks older rows. Its lock is gentler than immediate validation, but it is still a table scan worth observing. Separating “add the rule” from “prove old data meets the rule” lets each risk live in an appropriate window.

Observability is not a dashboard. It is evidence for contracting.

Once the backfill begins, keep a small evidence record: migration ID, start and finish times, rows processed per batch, failures and retries, and whether application code still reads or writes the old column. That says more than “deployment succeeded,” because it answers the useful question: can we safely remove the fallback?

Two database views are especially practical:

• pg_stat_activity shows what sessions are waiting on and can reveal a long transaction that is holding a migration up.
• pg_stat_progress_create_index reports phase and progress for a concurrent index build, so “slow” can be separated from “waiting” or “failed.”

When a lock waits too long, lock_timeout makes that statement fail clearly. It does not resolve the lock. Find the blocking session, choose a quieter window, or rearrange the steps. On a large table, a clear failure is often safer than unbounded waiting.

Contract last, and do not rush to drop

I remove the old path only when all of these signals agree:

1. The compatible application version is fully deployed and has outlived the longest-running worker or queued job.
2. The backfill is complete and new writes keep satisfying the constraint.
3. There is no recorded read or write of the old column, and related indexes and constraints are healthy.
4. The observation window has no unusual lock waits, error rate, or latency.

Remove application fallback and dual writes first, then decide whether to drop delivery_state. Keeping an old column for another release cycle is often cheap insurance. A migration does not have to DROP COLUMN on the day it finishes. The real danger is closing the rollback path before proving that nobody still uses it.

Treating schema migration as a release with stages, observation, and recovery room matters more than memorizing another ALTER TABLE option. SQL is one piece. Compatibility and evidence determine whether it is safe to ship.

What I learned

• I can split a migration into expand, backfill, validation, and contract instead of expecting one SQL statement to switch code and data together.
• lock_timeout is a safety rail that makes waiting fail clearly. It does not bypass a lock, so the blocker still needs investigation.
• CREATE INDEX CONCURRENTLY and NOT VALID constraints reduce release impact, but each has transaction, validation, and failure states to observe.
• I can use evidence from data and application behavior to prove an old path has retired before removing fallback code or an old column.

External references

• PostgreSQL: Modifying Tables
• PostgreSQL: ALTER TABLE
• PostgreSQL: CREATE INDEX
• PostgreSQL: Progress Reporting
• PostgreSQL: Monitoring Database Activity
• PostgreSQL: Client Connection Defaults
• Matthew Palma: Zero Downtime Database Migrations - The Expand-Contract Pattern
• Reddit: Retiring a PostgreSQL column while old workers may still run

Further learning

• PostgreSQL: Modifying Tables is the map for adding, changing, and removing table structure.
• PostgreSQL: ALTER TABLE documents locks, NOT VALID, and VALIDATE CONSTRAINT precisely.
• PostgreSQL: CREATE INDEX explains CONCURRENTLY, its waits, limits, and failure states.
• Matthew Palma: Zero Downtime Database Migrations gives a visual review of the expand-contract release order.