Zero-Downtime Postgres Migration Checklist for Production Deployments

Running a schema migration against a production Postgres database without causing downtime is not difficult — once you know the specific operations that acquire locks, how long those locks are held, and which migration patterns are safe to run against a live system. Most production incidents from migrations happen because an engineer ran a migration that looks harmless but holds an exclusive lock for minutes while the table rewrites.

This checklist covers every category of risk and gives you concrete steps to handle each one safely.

Before You Write the Migration

Understand what each DDL statement does under the hood. Some operations are instant. Others rewrite the entire table. Knowing which is which is the foundation of safe migration work.

• ADD COLUMN with no default or a volatile default: instant, no table rewrite
• ADD COLUMN with a non-null, non-volatile default (Postgres 11+): instant, stored as a catalog default
• ADD COLUMN NOT NULL without a default (Postgres 10 and below): full table rewrite — blocks all reads and writes
• DROP COLUMN: fast, marks column as dropped; table rewrite only if FULL is used
• ALTER COLUMN TYPE: usually a full table rewrite
• CREATE INDEX: blocks writes; use CONCURRENTLY to avoid this
• ADD CONSTRAINT NOT NULL: acquires an exclusive lock and scans the table

Review the generated SQL from your migration tool. ORMs and migration frameworks often generate SQL that looks safe but is not. Rails' add_index generates a blocking CREATE INDEX unless you pass algorithm: :concurrently. Prisma wraps migrations in transactions by default, which prevents CREATE INDEX CONCURRENTLY from working at all.

Print and read the raw SQL before running anything in production.

The Zero-Downtime Postgres Migration Checklist

Lock Management

• Set lock_timeout before every DDL statement. This prevents a blocked migration from queuing behind other queries and starving the table.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN notes TEXT;

If the lock cannot be acquired in 3 seconds, the migration fails fast rather than holding a queue that backs up your entire application.

• Set statement_timeout as a safety net:

SET statement_timeout = '30s';

• Use lock_timeout retry logic. A migration runner should catch the lock timeout error, wait a short interval, and retry. Do not simply fail the deploy on a timeout.

Adding Columns

• Add nullable columns without defaults whenever possible — they are instant.
• If you need a default value, add the column first, then set the default with a separate ALTER TABLE.
• For NOT NULL columns, use this three-migration sequence:
  1. Add the column as nullable
  2. Backfill existing rows
  3. Add the NOT NULL constraint using ALTER TABLE ... SET NOT NULL (Postgres 12+ performs a cheaper constraint scan if a CHECK (col IS NOT NULL) constraint was previously validated)

Creating Indexes

• Always use CREATE INDEX CONCURRENTLY in production:

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

• Never run CREATE INDEX CONCURRENTLY inside a transaction — it will fail.
• Configure your migration tool to run index creation outside of the transaction block.
• Verify the index was created successfully. A failed concurrent build leaves a INVALID index that consumes resources but serves no queries. Clean it up with DROP INDEX CONCURRENTLY.

Renaming Columns and Tables

Renaming is a two-application-version operation, never a single migration:

1. Add the new column (or table) alongside the old one.
2. Deploy application code that writes to both and reads from the old.
3. Backfill the new column.
4. Deploy application code that reads from the new column.
5. Remove writes to the old column.
6. Drop the old column.

Cutting over in a single migration while the application is live will break any in-flight requests reading the old column name.

Backfilling Large Tables

Never backfill in a single UPDATE:

-- Dangerous: holds a lock while updating millions of rows
UPDATE orders SET new_column = 'default' WHERE new_column IS NULL;

Instead, backfill in batches using a script that processes a few thousand rows at a time with a sleep between batches. This keeps lock duration short and leaves room for normal application traffic.

UPDATE orders SET new_column = 'default'
WHERE id BETWEEN 1 AND 10000 AND new_column IS NULL;
-- sleep, then next batch

Dropping Columns and Tables

• Remove all references to the column in application code and deploy that code before running the DROP COLUMN.
• Drop indexes on the column before dropping the column — this makes the intent clearer and avoids surprises from implicit index removal.
• For large tables, consider running VACUUM FULL on a read replica first to understand how much the table will shrink, before running it on primary.

Constraint Changes

• Validate constraints using NOT VALID and then validate separately:

-- Step 1: Add constraint without scanning existing rows (fast, no lock held long)
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  NOT VALID;
 
-- Step 2: Validate against existing rows in a separate migration
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

NOT VALID means new rows are checked immediately; existing rows are checked later during VALIDATE, which acquires only a ShareUpdateExclusiveLock instead of an exclusive lock.

Rollback Planning

• Every migration should have a corresponding down migration that is tested.
• For data-destructive operations (dropping columns, dropping tables), take a snapshot or logical backup before running.
• Test the rollback on a staging environment that mirrors production data volume. A rollback that takes 30 seconds on staging might take 30 minutes on production.

Post-Migration Verification

• Check for invalid indexes: SELECT indexname FROM pg_indexes JOIN pg_class ON relname = tablename WHERE indisvalid = false;
• Verify query plans have not regressed: re-run EXPLAIN ANALYZE on your critical queries.
• Monitor slow query logs for 30 minutes after the migration.

If your team runs database migrations manually and has experienced production incidents from schema changes, Clixo can help you build a safe, automated migration pipeline. Start a build and we can audit your current approach.