ALTER TABLE, 5 Million Rows, and the Deploy That Took Down the Site

-- Step 1: Looks fine
ALTER TABLE orders ADD COLUMN region VARCHAR(50);

-- Step 2: The backfill that locks the table
UPDATE orders SET region = 'us-east-1' WHERE region IS NULL;

-- Step 3: Now add the constraint
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

The backfill in step 2 is where it goes wrong. A single UPDATE touching 5 million rows acquires row locks across the entire table, so concurrent writes block, and autovacuum can't reclaim the dead tuples the backfill creates while the transaction stays open. If you have foreign key references, those tables might lock too.

At scale, a 5M-row backfill UPDATE can take 30+ seconds and block all concurrent writes to the table for the duration.

The safer pattern:

-- Batch the backfill
UPDATE orders SET region = 'us-east-1'
WHERE id IN (SELECT id FROM orders WHERE region IS NULL LIMIT 10000);
-- Repeat until no rows remain

-- Acquires a SHARE lock — blocks writes for the entire build
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

On 100 rows it finishes instantly, but on 10 million rows it holds a SHARE lock for the entire index build, so every INSERT, UPDATE, and DELETE on that table queues behind it.

The safer pattern:

-- Builds the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY doesn't block writes, but it takes roughly 2-3x longer and can fail if there are concurrent schema changes. It also can't run inside a transaction block, which means most migration frameworks need special handling.

-- Widening an integer primary key that's about to overflow
ALTER TABLE events ALTER COLUMN id TYPE BIGINT;

Every growing team eventually runs this one, an integer ID column approaching its ~2.1 billion ceiling. It reads like a one-line metadata tweak, but because integer and bigint have different on-disk representations, PostgreSQL has to rewrite the entire table while holding an ACCESS EXCLUSIVE lock, so a 5M-row table stays fully locked until it finishes.

The catch is that not every type change rewrites. Widening a varchar, or going from varchar to text, is binary-coercible, so PostgreSQL changes only the catalog and returns instantly even on 50 million rows, while id INTEGER to id BIGINT rewrites the whole table.

The safer pattern for a true rewrite adds a new column, backfills in batches, swaps with a rename, and drops the old column, which is more steps but never holds an extended lock.

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

Adding a foreign key makes PostgreSQL validate every existing row, a full table scan over 5 million orders while it holds a lock on both orders and customers. If customers is also large, you've locked two critical tables at once, and it only works if every order already points at a real customer, the kind of referential integrity that production data drifts away from over time.

The safer pattern:

-- Add the constraint without validating existing rows
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- Validate in a separate step (holds a weaker lock)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

NOT VALID adds the constraint for new rows immediately, and then VALIDATE checks the existing rows under a less aggressive lock whose duration still tracks the row count.

ALTER TABLE orders DROP COLUMN legacy_status;

In PostgreSQL, DROP COLUMN doesn't rewrite the table; it only marks the column as invisible. That sounds fast, and usually it is, but the operation still takes an ACCESS EXCLUSIVE lock, so if long-running queries are reading from the table, the DROP waits for them to finish, and while it waits, every new query queues behind it. A 1ms metadata operation can block the table for minutes when a slow SELECT is running.

At scale, the lock acquisition time becomes unpredictable, driven by whatever concurrent query workload happens to be running when the DROP fires.

The fix is to run your migration against production-scale data first. Seedfast generates realistic, relational data straight from your schema, so you can stand up a table at production row counts, without copying production rows or maintaining seed scripts, and time the migration against it.

# Match your production table sizes
seedfast seed --scope "seed 5 million orders with customers, order items, and payments"

The generated rows come out FK-valid and realistically distributed, with proportions that match the scope you asked for (batching and scope sizing for the very largest runs are covered in the large-volume seeding guide):

Seeding Plan:
  public.customers    — 500,000 records
  public.orders       — 5,000,000 records
  public.order_items  — 12,000,000 records
  public.payments     — 4,800,000 records

Total: 22,300,000 records across 4 tables

Approve? (Y/n)

# Time the migration
time psql $DATABASE_URL -f migrations/20260225_add_region_column.sql

# Or with your migration framework
time flyway migrate
time rails db:migrate
time alembic upgrade head

While the migration runs, open another terminal and monitor locks:

-- See which queries are blocked and what's blocking them
SELECT
  blocked.pid          AS blocked_pid,
  blocked.query        AS blocked_query,
  blocking.pid         AS blocking_pid,
  blocking.query       AS blocking_query,
  now() - blocked.query_start AS waiting_time
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Queries stacking up behind your migration are exactly what production will do, only with real user traffic behind them; PostgreSQL's explicit locking documentation explains which lock modes conflict.

If the naive migration locks the table for too long, implement the safer pattern and measure again:

# Reseed a fresh database (or use a separate test database)
seedfast seed --scope "seed 5 million orders with customers"

# Run the batched migration
time psql $DATABASE_URL -f migrations/20260225_add_region_column_safe.sql

Now you have concrete numbers your team can decide on: "The naive migration locks orders for 38 seconds. The batched version takes 2 minutes total but never holds a lock for more than 200ms."

Run both candidate migrations against the same populated copy and you can see which lock pattern actually holds up (the seeding guide covers a first run).

Wire this into your pipeline so it stops being a manual exercise.

name: Migration Benchmark

on:
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  benchmark-migration:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: bench
          POSTGRES_USER: bench
          POSTGRES_PASSWORD: bench
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Apply base schema
        run: |
          for f in migrations/*.sql; do
            psql $DATABASE_URL -f "$f" 2>/dev/null || true
          done
        env:
          DATABASE_URL: postgres://bench:bench@localhost:5432/bench

      - name: Seed test data
        run: seedfast seed --scope "seed 1 million orders with customers and payments" --output plain
        env:
          SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
          DATABASE_URL: postgres://bench:bench@localhost:5432/bench

      - name: Benchmark new migration
        run: |
          NEW_MIGRATIONS=$(git diff --name-only origin/main -- migrations/ | sort)
          for f in $NEW_MIGRATIONS; do
            echo "--- Benchmarking: $f ---"
            time psql $DATABASE_URL -f "$f"
          done
        env:
          DATABASE_URL: postgres://bench:bench@localhost:5432/bench

      - name: Check migration duration
        run: |
          echo "Review migration timing above. Migrations over 30s need batching or CONCURRENTLY."

Now every PR with a migration gets benchmarked against realistic data, so a 200-row staging pass no longer vouches for a change. For running it unattended, the CI/CD database seeding guide covers non-interactive mode and spinning up a database per PR.

Even without full CI integration, you can add a one-liner to your PR template:

## Migration Checklist
- [ ] Tested against production-scale data (`seedfast seed --scope "..."`)
- [ ] Migration completes in under 30 seconds on 1M+ rows
- [ ] No ACCESS EXCLUSIVE locks held for more than 5 seconds
- [ ] Uses CONCURRENTLY for index creation (if applicable)
- [ ] Backfills are batched (if applicable)

A slow migration is just one of the bugs that only real test data catches; the same volume blind spot behind slow queries and N+1s hides slow migrations too. A few more that trip teams up at scale:

Transaction-wrapped migrations lock longer than you think. Most frameworks run each migration inside a transaction, which means the lock acquired at the start isn't released until the whole file finishes, backfills and constraint validations and index builds included.

Because CONCURRENTLY can't run in a transaction, a framework that wraps migrations by default (Rails, Flyway, Alembic) makes CREATE INDEX CONCURRENTLY fail until you configure that specific migration to run outside the transaction block.

For zero-downtime rewrites like a column-type change, pg_repack rebuilds a table in the background with minimal locking, though on a 50M-row table it still takes significant time and I/O, so test it against real volume first.

Autovacuum can fall behind after a large backfill. The dead tuples an UPDATE leaves behind cause table bloat that slows subsequent queries if autovacuum can't keep up, so test the migration and then check bloat with pg_stat_user_tables.

Checklist for any migration touching a table with more than 100K rows:

• Seed production-scale data locally

seedfast seed --scope "seed [your production row count] [table] with related records"

• Time the migration. If it takes more than 10 seconds, consider batching or alternative approaches.
• Monitor locks during execution. An ACCESS EXCLUSIVE lock held for more than a few seconds will impact production traffic.
• Test the rollback too. A 30-second migration with a 5-minute rollback is a risky deploy.
• Run it during low traffic if the lock duration is unavoidable, and know exactly how long that is, in seconds.
• Check CONCURRENTLY support in your migration framework for index operations.
• Validate constraints separately using NOT VALID + VALIDATE CONSTRAINT for foreign keys and check constraints.
• Batch backfills — never UPDATE millions of rows in a single statement.

You can't measure lock duration on 50 rows, benchmark a backfill on an empty table, or catch CONCURRENTLY failing inside your framework's transaction wrapper without production-scale data.

• Review SQL Migrations in 30 Seconds — the data-shape half of the same check: catching NULLs, duplicates, and silent truncation at small volume during code review
• Load Testing Data: Why Your Benchmarks Lie — the other half of production-scale testing, for query latency and throughput rather than migration locks
• PostgreSQL Test Data: A Syntax Cookbook — the raw SQL patterns for generating volume and realistic distributions by hand
• Small Data, Big Lies: 6 Bugs Only Real Test Data Catches — why a 50-row development database hides the failures that only appear at scale
• Circular Foreign Key Seed — handling FK cycles when you seed the large, related datasets a migration test needs
• Your Staging Database Is a Compliance Violation Waiting to Happen — generating realistic test data instead of copying and anonymizing production
• Get started with Seedfast — connect your database and seed production-scale data for your next migration test

Get Started | Documentation | Pricing

Seed a test database to production row counts with Seedfast and time your next migration against volume that behaves like the real thing.