How to Handle Database Migrations Safely

Q: What is the most dangerous common migration?

Creating an index without CONCURRENTLY on a large, active table. It holds a SHARE lock that blocks all writes for the duration of the index build, which can take minutes to hours on large tables.

Q: How do I know if a migration will rewrite the table?

In PostgreSQL, ALTER TABLE ALTER COLUMN TYPE (with actual type change), ALTER TABLE SET TABLESPACE, and CLUSTER cause table rewrites. Adding a column with a volatile default triggers a rewrite on every version, including current, as do stored generated columns, identity columns, and domain types with constraints. What changed in PG11 is that a *non-volatile* default no longer rewrites; on PostgreSQL 10 and earlier, *any* ADD COLUMN ... DEFAULT rewrote the table. Test on a clone if unsure.

Q: Should I use a migration linting tool?

Yes. Tools like squawk (PostgreSQL migration linter) catch unsafe patterns before they reach production. They flag non-concurrent index creation, missing lock timeouts, and table-rewriting operations.

Quick answer: Most migration outages come from a lock, not from the change itself. Know which category each statement falls in before you run it, set a short lock_timeout and retry so you never queue behind a long query, use CREATE INDEX CONCURRENTLY and NOT VALID plus VALIDATE for constraints, and split anything that rewrites a large table into expand, backfill, and contract steps.

Safe database migrations require avoiding table locks that block queries, validating schema changes against production data volumes, and having a tested rollback plan. The most dangerous migrations are those that look harmless in development, adding a column with a default, creating an index, changing a column type, but lock production tables for minutes or hours. PostgreSQL-specific strategies like CREATE INDEX CONCURRENTLY, adding columns without defaults first, and using multi-step migrations eliminate the risk of downtime during schema changes.

Migrations that lock tables

Understanding lock levels

PostgreSQL uses multiple lock levels. The dangerous one for migrations is ACCESS EXCLUSIVE, which blocks all reads and writes on the table.

Operation	Lock Level	Blocks Reads?	Blocks Writes?
`SELECT`	`ACCESS SHARE`	No	No
`INSERT/UPDATE/DELETE`	`ROW EXCLUSIVE`	No	No
`CREATE INDEX`	`SHARE`	No	Yes
`CREATE INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`	No	No
`ALTER TABLE ADD COLUMN` (no default)	`ACCESS EXCLUSIVE` (brief)	Yes (brief)	Yes (brief)
`ALTER TABLE ADD COLUMN DEFAULT` (PG 11+)	`ACCESS EXCLUSIVE` (brief)	Yes (brief)	Yes (brief)
`ALTER TABLE ADD COLUMN DEFAULT` (PG < 11)	`ACCESS EXCLUSIVE` (full rewrite)	Yes (long)	Yes (long)
`ALTER TABLE ALTER COLUMN TYPE`	`ACCESS EXCLUSIVE` (full rewrite, unless the old type is binary-coercible to the new, e.g. `varchar(50)` → `text`, in which case no rewrite is needed, though indexes may still be rebuilt)	Yes (long)	Yes (long)
`ALTER TABLE SET NOT NULL`	`ACCESS EXCLUSIVE` (full scan; skipped if a valid CHECK proves no NULLs)	Yes (long)	Yes (long)
`ALTER TABLE ADD FOREIGN KEY`	`SHARE ROW EXCLUSIVE` (on both the referencing and the referenced table)	No	Yes
`ALTER TABLE VALIDATE CONSTRAINT`	`SHARE UPDATE EXCLUSIVE`	No	No

The development vs. production gap

A migration that takes 20ms on a development database with 1,000 rows might take 20 minutes on a production database with 50 million rows. During that time, every query against the table is blocked.

Safe migration patterns

Adding a column

Safe (PostgreSQL 11+):

-- Fast: adds column metadata without rewriting the table
ALTER TABLE orders ADD COLUMN priority integer DEFAULT 0;

PostgreSQL 11+ handles ADD COLUMN with a non-volatile DEFAULT without a table rewrite. A volatile default (e.g. clock_timestamp()), a stored generated column, an identity column, or a domain type with constraints still rewrites the entire table and its indexes. The default is stored in the catalog and applied on read.

Safe (any version):

-- Step 1: Add column without default (instant)
ALTER TABLE orders ADD COLUMN priority integer;

-- Step 2: Set default for new rows
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;

-- Step 3: Backfill existing rows in batches
UPDATE orders SET priority = 0 WHERE id BETWEEN 1 AND 100000;
UPDATE orders SET priority = 0 WHERE id BETWEEN 100001 AND 200000;
-- ... continue in batches

Creating an index

Dangerous:

-- Locks the table for writes until the index is built
CREATE INDEX idx_orders_status ON orders (status);

Safe:

-- Does not block reads or writes (takes a SHARE UPDATE EXCLUSIVE lock; slower to build, but no downtime)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

Important: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Most migration frameworks need special handling for this. Three more caveats matter operationally:

A failed build leaves an INVALID index behind. Queries ignore it, but it still carries write overhead on every INSERT, UPDATE, and DELETE. Find leftovers and drop them before retrying:
```
SELECT indrelid::regclass AS table_name,
       indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid
  AND indrelid = 'orders'::regclass;   -- scope to the table you are repairing
```
Scope it, and read the names before dropping anything. An unqualified WHERE NOT indisvalid also returns indexes that are invalid on purpose: CREATE INDEX ON ONLY <partitioned_table> deliberately leaves the parent index invalid until every partition's index is attached. Drop one of those and you destroy the partitioned index you were part-way through building. Only drop the failed build you are actually retrying.
It is not supported on partitioned parent tables. Build the index CONCURRENTLY on each partition individually, then create the index on the parent non-concurrently, the parent index build is a catalog operation that attaches the existing partition indexes.
Only one concurrent build can run per table at a time. Queue index builds on the same table rather than launching them in parallel.

Adding a NOT NULL constraint

Dangerous:

-- Scans entire table while holding ACCESS EXCLUSIVE lock
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

Safe (PostgreSQL 18+):

-- PostgreSQL 18+: add the NOT NULL constraint itself as NOT VALID (brief ACCESS EXCLUSIVE, no scan)
ALTER TABLE orders ADD CONSTRAINT orders_priority_nn NOT NULL priority NOT VALID;

-- Then validate it (scans the table under SHARE UPDATE EXCLUSIVE; reads and writes continue)
ALTER TABLE orders VALIDATE CONSTRAINT orders_priority_nn;

PostgreSQL 18 lets you declare a NOT NULL constraint as NOT VALID directly, which collapses the older four-step CHECK dance into two steps and leaves no redundant constraint to clean up.

Safe (PostgreSQL 12–17):

-- Step 1: Add a CHECK constraint as NOT VALID (instant, no scan)
ALTER TABLE orders ADD CONSTRAINT orders_priority_not_null
    CHECK (priority IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint (scans table but allows reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_priority_not_null;

-- Step 3: Now set the column NOT NULL.  The validated CHECK proves no NULLs exist,
-- so PostgreSQL skips the full table scan -- but this still takes a brief
-- ACCESS EXCLUSIVE lock, so run it under a short lock_timeout.
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

-- Step 4: Drop the redundant check constraint
ALTER TABLE orders DROP CONSTRAINT orders_priority_not_null;

Changing a column type

Dangerous:

-- Full table rewrite while holding ACCESS EXCLUSIVE lock
ALTER TABLE orders ALTER COLUMN amount TYPE numeric(12,2);

Safe approach, use a new column:

-- Step 1: Add new column
ALTER TABLE orders ADD COLUMN amount_new numeric(12,2);

-- Step 2: Backfill in batches
UPDATE orders SET amount_new = amount::numeric(12,2) WHERE id BETWEEN 1 AND 100000;

-- Step 3: Start writing to both columns in application code

-- Step 4: Once backfill is complete, swap columns
ALTER TABLE orders RENAME COLUMN amount TO amount_old;
ALTER TABLE orders RENAME COLUMN amount_new TO amount;

-- Step 5: Stop writing to old column, drop it after verification
ALTER TABLE orders DROP COLUMN amount_old;

Renaming a column

Dangerous in most frameworks because the application code must be deployed simultaneously with the migration.

Safe pattern:

-- Step 1: Add new column
ALTER TABLE orders ADD COLUMN order_total numeric;

-- Step 2: Backfill in batches by id range.  Never run this as one unbatched
-- UPDATE across the whole table -- a single statement holds row locks and
-- generates WAL for every row at once, and bloats the table in one shot.
UPDATE orders SET order_total = amount
    WHERE order_total IS NULL AND id BETWEEN 1 AND 100000;
UPDATE orders SET order_total = amount
    WHERE order_total IS NULL AND id BETWEEN 100001 AND 200000;
-- ... continue in batches, committing between each

-- Step 3: Deploy application code that reads from both columns

-- Step 4: Deploy application code that writes to both columns

-- Step 5: Deploy application code that reads only from new column

-- Step 6: Drop old column
ALTER TABLE orders DROP COLUMN amount;

Dropping a column

Safe approach:

-- Step 1: Stop reading the column in application code (deploy first)
-- Step 2: Stop writing the column in application code (deploy)
-- Step 3: Drop the column
ALTER TABLE orders DROP COLUMN legacy_field;

Dropping a column is instant in PostgreSQL (it marks the column as dropped in the catalog without rewriting the table), but the application must stop using it first. Two caveats:

The space is not reclaimed immediately. The old values stay in each row's on-disk tuple until that row is rewritten, space comes back gradually as rows are updated, or all at once via a rewriting operation such as VACUUM FULL or CLUSTER (both of which take ACCESS EXCLUSIVE for their full duration).
It still takes a brief ACCESS EXCLUSIVE lock. Catalog-only does not mean lock-free: the statement must wait for every conflicting lock on the table, so guard it with a short lock_timeout like any other ALTER.

Migration safety checklist

Before running any migration in production:

Check	How
What locks does it take?	Test in a transaction and check `pg_locks`
How long does it take on production-size data?	Run on a database clone with production data volume
Does it block concurrent queries?	Run concurrent SELECTs/INSERTs during the migration on a test instance
Is there a rollback plan?	Write the rollback migration and test it
Does the application work before AND after?	Deploy application changes that handle both states
Can it run in a transaction?	Some operations (CONCURRENTLY) cannot
Is there a lock timeout set?	`SET lock_timeout = '5s'` prevents waiting indefinitely for locks

Set lock timeout

Always set a lock timeout for migrations to prevent cascading failures:

SET lock_timeout = '5000ms';, Fail fast rather than wait for locks
ALTER TABLE orders ADD COLUMN ...;

If the migration cannot acquire its lock within 5 seconds, it fails instead of blocking all other queries while it waits.

lock_timeout applies to each lock acquisition attempt, not to the statement as a whole. A single ALTER TABLE with several subcommands, or one that must also lock a referenced table (for example ADD FOREIGN KEY, which locks both tables), can wait up to lock_timeout more than once. Budget the worst case as lock_timeout × number of locks the statement takes, not as a flat five seconds.

Because a timeout is an expected outcome rather than a failure, wrap the statement in a retry loop that catches lock_not_available (SQLSTATE 55P03) and backs off:

DO $$
DECLARE
    attempt int := 0;
BEGIN
    LOOP
        attempt := attempt + 1;
        BEGIN
            SET LOCAL lock_timeout = '5s';
            ALTER TABLE orders ADD COLUMN priority integer;
            RETURN;, succeeded
        EXCEPTION WHEN lock_not_available THEN
            IF attempt >= 5 THEN
                RAISE;, give up, let the migration fail loudly
            END IF;
            RAISE NOTICE 'lock not available, retry %', attempt;
            PERFORM pg_sleep(2 * attempt);, back off
        END;
    END LOOP;
END $$;

Retrying with backoff means a migration that lost a race against a long-running query gets another chance a few seconds later instead of requiring manual re-runs.

Multi-Step migration strategy

Complex migrations should be broken into multiple deployments:

Phase 1: expand

Add new columns, create new indexes (concurrently), add new constraints (NOT VALID).

Phase 2: migrate

Backfill data in batches. Deploy application code that writes to both old and new schemas.

Phase 3: contract

Validate constraints. Deploy application code that reads only from the new schema. Drop old columns and indexes.

Each phase is independently deployable and rollback-safe.

Framework-Specific tips

Django

# Use RunSQL for concurrent index creation
from django.db import migrations

class Migration(migrations.Migration):
    atomic = False  # Required for CONCURRENTLY

    operations = [
        migrations.RunSQL(
            "CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);",
            reverse_sql="DROP INDEX CONCURRENTLY idx_orders_status;",
        ),
    ]

The reverse_sql above uses DROP INDEX CONCURRENTLY, which carries its own restrictions: it accepts exactly one index name per statement, does not support CASCADE (so it cannot drop an index that backs a UNIQUE or PRIMARY KEY constraint, drop the constraint instead), cannot run inside a transaction block, and is not supported on partitioned tables.

Rails

# Use disable_ddl_transaction! for concurrent operations
class AddStatusIndexToOrders < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :orders, :status, algorithm: :concurrently
  end
end

Prisma

Prisma generates standard SQL migrations. For safe production migrations, review the generated SQL and modify it to use CONCURRENTLY where needed.

How DBGorilla helps

DBGorilla connects to your database read-only and gives your AI coding agent (Claude Code, Cursor) the context a safe migration needs, the table's row count and size, which indexes exist, and what is holding locks right now, so it can warn that an ALTER will rewrite a 400-million-row table, or that it is about to queue behind a long-running query, before you run it.

For a migration you are unsure about, the agent can run it as an experiment against a clone and report what actually happened, whether it rewrote the table, how long it held its lock, what it cost, which is the rehearsal this article argues every risky migration deserves.

It surfaces, explains, and rehearses; it does not execute migrations against production, take locks there, or roll anything back. Get started free →

Frequently asked questions

What is the most dangerous common migration? Creating an index without CONCURRENTLY on a large, active table. It holds a SHARE lock that blocks all writes for the duration of the index build, which can take minutes to hours on large tables.

Can I run migrations during peak traffic? Safe migrations (add column without rewrite, create index concurrently, add constraint NOT VALID) can run at any time. Unsafe migrations (table rewrites, non-concurrent index creation) should run during low-traffic windows.

How do I know if a migration will rewrite the table? In PostgreSQL, ALTER TABLE ALTER COLUMN TYPE (with actual type change), ALTER TABLE SET TABLESPACE, and CLUSTER cause table rewrites. Adding a column with a volatile default triggers a rewrite on every version, including current, as do stored generated columns, identity columns, and domain types with constraints. What changed in PG11 is that a non-volatile default no longer rewrites; on PostgreSQL 10 and earlier, any ADD COLUMN ... DEFAULT rewrote the table. Test on a clone if unsure.

Should I use a migration linting tool? Yes. Tools like squawk (PostgreSQL migration linter) catch unsafe patterns before they reach production. They flag non-concurrent index creation, missing lock timeouts, and table-rewriting operations.

How long should I set lock_timeout? For DDL operations on active tables, 3-5 seconds is a good starting point. If the lock cannot be acquired in that time, it is better to fail and retry during a quieter period than to block all queries while waiting.

Migrations that lock tables​

Understanding lock levels​

The development vs. production gap​

Safe migration patterns​

Adding a column​

Creating an index​

Adding a NOT NULL constraint​

Changing a column type​

Renaming a column​

Dropping a column​

Migration safety checklist​

Set lock timeout​

Multi-Step migration strategy​

Phase 1: expand​

Phase 2: migrate​

Phase 3: contract​

Framework-Specific tips​

Django​

Rails​

Prisma​

How DBGorilla helps​

Frequently asked questions​

Related​

Migrations that lock tables

Understanding lock levels

The development vs. production gap

Safe migration patterns

Adding a column

Creating an index

Adding a NOT NULL constraint

Changing a column type

Renaming a column

Dropping a column

Migration safety checklist

Set lock timeout

Multi-Step migration strategy

Phase 1: expand

Phase 2: migrate

Phase 3: contract

Framework-Specific tips

Django

Rails

Prisma

How DBGorilla helps

Frequently asked questions

Related