# Zero-Downtime Postgres Migration Checklist for Production Deployments

> A step-by-step checklist for zero-downtime Postgres migrations — covering lock timeouts, concurrent indexes, backfills, and rollback planning for production databases.

- **Published:** 2025-12-09
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** postgres, migrations, zero-downtime, production, database
- **Canonical URL:** https://clixo.sh/blog/zero-downtime-postgres-migration-checklist

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.

```sql
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:

```sql
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:

```sql
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.

```mermaid
flowchart TD
  A["Add new column alongside old column"] --> B["Deploy: write to both columns"]
  B --> C["Backfill new column from old data"]
  C --> D["Deploy: read from new column"]
  D --> E["Remove writes to old column"]
  E --> F["Drop old column"]
```

### Backfilling Large Tables

Never backfill in a single `UPDATE`:

```sql
-- 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.

```sql
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:

```sql
-- 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](https://clixo.sh/#contact) and we can audit your current approach.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)