Zero-Downtime PostgreSQL Schema Migration in Practice — Deploying Column Renames to Type Changes Without Rollback Using the Expand-Contract Pattern

END;
$$;
 
CALL backfill_preferences();

-- 3. Add constraint quickly with NOT VALID (validates only new rows immediately)
SET lock_timeout = '2s';
ALTER TABLE users
  ADD CONSTRAINT users_preferences_not_null
  CHECK (preferences IS NOT NULL) NOT VALID;
 
-- 4. Validate existing rows (SHARE UPDATE EXCLUSIVE lock — no blocking of SELECT or most DML)
SET lock_timeout = '2s';
ALTER TABLE users VALIDATE CONSTRAINT users_preferences_not_null;
 
-- 5. PG 12+: Reuse validated CHECK to convert to actual NOT NULL (no full scan)
SET lock_timeout = '2s';
ALTER TABLE users ALTER COLUMN preferences SET NOT NULL;
 
-- 6. Drop CHECK constraint since NOT NULL is now guaranteed (optional)
SET lock_timeout = '2s';
ALTER TABLE users DROP CONSTRAINT users_preferences_not_null;

The NOT VALID + VALIDATE CONSTRAINT combination is especially powerful in PostgreSQL 12+. Because VALIDATE CONSTRAINT only acquires a SHARE UPDATE EXCLUSIVE lock, it blocks neither SELECT nor most DML. And in PostgreSQL 12+, if a validated CHECK constraint already exists, step 5's SET NOT NULL is processed without a full scan.

Scenario 4: Adding an Index

CREATE INDEX acquires a table lock, but CREATE INDEX CONCURRENTLY runs in the background.

-- Wrong approach: causes a table lock
CREATE INDEX idx_users_email ON users(email);
 
-- Correct approach: run outside a transaction block
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

There are two things to watch out for. First, CONCURRENTLY cannot be run inside a transaction block. Second, if it fails, an INVALID index is left behind, so you need to check and clean it up.

-- Check for INVALID indexes
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'idx_users_email'::regclass;
 
-- If INVALID, drop first then recreate
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Which Strategy Should You Use? Decision Flow

The definition of a "large table" is not absolute. Up to hundreds of thousands of rows, you can try ALTER COLUMN TYPE directly with lock_timeout set, but beyond millions of rows it is better to follow the full Expand-Contract procedure. In addition to table size, write frequency, transaction length, and concurrency all factor into the decision.

Pros and Cons

Advantages

Disadvantages and Caveats

Common Mistakes in Practice

Orphaned triggers: If you drop the column before removing the trigger during the Contract phase, the trigger referencing the deleted column remains alive and causes errors on every INSERT/UPDATE. In your checklist or migration script, always delete triggers and functions before dropping the column.

Trigger overwrite: If you write only to the new column without dual-writing while leaving the trigger in place, the trigger will overwrite the new column with the old column's previous value. Triggers must remain alive until just before Contract, and during that time you must keep writing to both columns.

Staging environment data scale: A migration that takes 2 seconds on a development DB can take 2 hours in production. Always validate in a staging environment with a data scale similar to production.

Batch size decision: Too large and each batch holds a lock for too long; too small and the total time grows. It is generally safe to start in the range of 10,000–100,000 rows and adjust from there.

ORM integration: If you use an ORM, your model may need to recognize both columns during the Expand phase. Handling differs by framework, so verify this in advance.

Tool Comparison

pgroll automates the Expand-Contract pattern and serves both old and new schemas simultaneously via versioned views during the migration window. Teams with many complex migrations may find it worth evaluating.

Closing Thoughts

Schema changes are an area where a small mistake can lead to a major outage. That said, they are not so difficult that you need to schedule a maintenance window every time. Once the Expand-Contract pattern is established within your team, schema changes can be handled the same way as any other feature deployment.

Three key takeaways:

First, always set SET lock_timeout before any DDL. This alone can prevent a significant portion of lock-queue cascade failures.

Second, the key to type changes is avoiding a Full Table Rewrite. Follow this order: add new column → trigger sync → batch backfill → code cutover (maintain dual-write) → drop trigger → drop old column.

Third, batch backfill should always be range-based, using a PROCEDURE to process each batch in an independent transaction. Changing millions of rows in a single UPDATE can cause an outage just as severe as a DDL lock.

Three steps to get started right now:

1. Integrating squawk into CI to block unsafe DDL patterns at the PR stage is the fastest first step.
2. Review your existing migration scripts to find DDL running without lock_timeout and fix it.
3. Apply Expand-Contract to your next schema change and experience phased deployment firsthand — after that, you will naturally approach future changes the same way.

At first, having more deployments may feel cumbersome. But the increase in deployment complexity is matched by a corresponding increase in service availability and team confidence.

References

• Zero-Downtime PostgreSQL Schema Migrations: Expand/Contract vs Blue-Green Deployment — DEV Community
• Database Migrations in Production: Zero-Downtime Schema Changes (2026 Guide) — DEV Community
• Database Migrations Without Downtime — Expand-Contract, Shadow Tables, and Feature Flags | datasops Blog
• Zero-Downtime PostgreSQL Migrations: Expand/Contract, Backfill and Rollback Strategies | Michal Drozd
• Using the expand and contract pattern | Prisma's Data Guide
• Zero-downtime Postgres schema migrations need this: lock_timeout and retries | PostgresAI
• Schema changes and the Postgres lock queue — Xata Blog
• pgroll — Zero-downtime, reversible, schema changes for PostgreSQL
• Schema changes and the power of expand-contract with pgroll — Xata Blog
• How to perform Postgres schema changes in production with zero downtime — Xata Blog
• Applying migrations safely | Squawk — a linter for Postgres migrations
• Top Open Source Postgres Migration Tools in 2026 | Bytebase
• Database Migrations Without Drama: Expand/Contract in Practice
• Database Migration Strategies for Zero-Downtime Deployments | DeployHQ
• Change management tools and techniques — PostgreSQL Wiki