Zero-Downtime Schema Changes in PostgreSQL: Adding Columns and Indexes to a 200M-Row Table

You have a PostgreSQL table with 200 million rows. It's serving live traffic. Now you need to add a column or an index.

Run the wrong command and every INSERT, UPDATE, and DELETE hangs until it finishes. On a table this size, that can be hours. Here is how to do it safely.

The core problem: locks

Every DDL statement (CREATE, ALTER, DROP, TRUNCATE) takes a lock on the table. What matters is which lock and for how long.

Analogy: You need to install a divider on a busy highway. Closing the entire road (an ACCESS EXCLUSIVE lock) causes a traffic jam. Working one lane at a time (light locks, small batches) keeps traffic moving.

There is a second, sneakier danger: the lock queue.

If your ALTER TABLE waits behind a long-running query, every new query queues up behind your ALTER. A harmless migration can freeze the whole application.

Part 1: Adding a column

Case 1: Nullable column, no default (safest)

ALTER TABLE orders ADD COLUMN notes text;

This only changes catalog metadata. No rows are rewritten, so it finishes in milliseconds, even with 200M rows.

Case 2: Column with a default

On PostgreSQL 11+, a constant default is also instant:

ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';

Postgres stores the default in the catalog and serves it virtually when reading old rows.

Two caveats:

• On PG 10 or older, this rewrites the entire table. Use the batch approach below.
• Volatile defaults such as now() or gen_random_uuid() still trigger a full rewrite on any version.

Case 3: Adding a NOT NULL column

Don't do ADD COLUMN ... NOT NULL directly. Do it in steps:

-- 1. Add nullable column (instant)
ALTER TABLE orders ADD COLUMN region_id bigint;

-- 2. Start writing the value for new rows from your application

-- 3. Backfill old rows in batches (see below)

-- 4. Add a CHECK constraint without scanning the table
ALTER TABLE orders
  ADD CONSTRAINT region_id_not_null
  CHECK (region_id IS NOT NULL) NOT VALID;

-- 5. Validate it. This scans the table but does not block writes.
ALTER TABLE orders VALIDATE CONSTRAINT region_id_not_null;

-- 6. On PG 12+, SET NOT NULL is now instant because a validated CHECK exists
ALTER TABLE orders ALTER COLUMN region_id SET NOT NULL;

-- 7. Drop the redundant CHECK
ALTER TABLE orders DROP CONSTRAINT region_id_not_null;

Backfilling old rows

Never run one giant UPDATE on 200M rows. It creates a huge transaction, bloats the WAL, causes replication lag, and leaves a pile of dead tuples.

Update in small batches instead:

UPDATE orders
SET region_id = 1
WHERE id IN (
  SELECT id FROM orders
  WHERE region_id IS NULL
  LIMIT 10000
  FOR UPDATE SKIP LOCKED
);

Run it in a loop until zero rows are affected. Here's a Go example:

for {
    res, err := db.Exec(`
        UPDATE orders SET region_id = 1
        WHERE id IN (
            SELECT id FROM orders
            WHERE region_id IS NULL
            LIMIT 10000
            FOR UPDATE SKIP LOCKED
        )`)
    if err != nil {
        log.Fatal(err)
    }

    n, _ := res.RowsAffected()
    if n == 0 {
        break
    }

    time.Sleep(100 * time.Millisecond) // let autovacuum and replicas breathe
}

The short sleep between batches keeps replication lag and vacuum pressure under control.

Part 2: Adding an index

The wrong way

CREATE INDEX idx_orders_user_id ON orders(user_id);

This takes a SHARE lock, which blocks all writes for the entire build. On 200M rows, that can mean hours of effective downtime.

The right way: CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Postgres scans the table twice and picks up changes made in between, without blocking writes. It takes roughly 2-3x longer, but your app keeps running.

Rules for CONCURRENTLY

Rule	Why it matters
Cannot run inside a transaction block	Many migration tools wrap migrations in a transaction by default, so you must disable that
A failed build leaves an INVALID index	It slows writes and is useless for reads, so clean it up
Uses a lot of CPU and I/O	Run it off-peak

Laravel example (disable the wrapping transaction):

class AddUserIdIndexToOrders extends Migration
{
    public $withinTransaction = false;

    public function up()
    {
        DB::statement(
            'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id ON orders(user_id)'
        );
    }

    public function down()
    {
        DB::statement('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id');
    }
}

Cleaning up a failed build

-- Find invalid indexes
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- Drop and retry
DROP INDEX CONCURRENTLY idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Unique indexes

CREATE UNIQUE INDEX CONCURRENTLY idx_orders_ref ON orders(reference_no);

-- Optionally attach it as a constraint (instant)
ALTER TABLE orders
  ADD CONSTRAINT uniq_orders_ref UNIQUE USING INDEX idx_orders_ref;

Part 3: Production safety checklist

1. Always set lock_timeout

This is the single most important habit. It prevents the lock-queue disaster:

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

If the lock isn't acquired within 3 seconds, the ALTER fails on its own instead of freezing your app. Then you simply retry.

For a long CREATE INDEX CONCURRENTLY, also make sure statement_timeout won't kill it:

SET statement_timeout = 0;

2. Check for long-running transactions first

SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;

An old open transaction can make your DDL wait, and everyone queues behind it.

3. Monitor index build progress

SELECT phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

4. Check disk space and replication

• Make sure you have enough free disk. An index on 200M rows can be several GB or more.
• Watch replication lag if you have replicas.
• Temporarily raising maintenance_work_mem speeds up the build:

SET maintenance_work_mem = '2GB';

5. Rehearse first

Run the migration on a staging copy with production-sized data and time it before touching production.

Part 4: When the simple way isn't enough

Everything above works because PostgreSQL can do those changes online. But some changes force a full table rewrite that PostgreSQL cannot do without blocking writes: changing a column's data type, re-partitioning a table, or reclaiming heavy bloat.

For these, use the shadow table pattern (also called an online schema change). Tools like gh-ost, pg-osc, pgroll, and pg_repack are built on this idea.

Analogy: You want to renovate a shop that can't close. You build an identical new shop next door with the improvements, move the stock over gradually while mirroring every new sale into both shops, and then swap the signboards in one quick moment.

Step 1: Create the copy with the change applied

CREATE TABLE orders_new (LIKE orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE orders_new ADD COLUMN region_id bigint;  -- or change a column type, etc.

The table is empty, so this is instant. Don't create indexes yet.

Step 2: Mirror live changes with a trigger

Before copying any data, make sure every new write to orders is also applied to orders_new:

CREATE FUNCTION orders_sync() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    DELETE FROM orders_new WHERE id = OLD.id;
    RETURN OLD;
  ELSE
    INSERT INTO orders_new (id, user_id, total /* , ...all columns */)
    VALUES (NEW.id, NEW.user_id, NEW.total)
    ON CONFLICT (id) DO UPDATE
      SET user_id = EXCLUDED.user_id, total = EXCLUDED.total;
    RETURN NEW;
  END IF;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_trg
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_sync();

The trigger comes first so no change can slip through the gap while you copy. Note that the ON CONFLICT (id) clause requires a primary key or unique index on id in orders_new, which INCLUDING CONSTRAINTS does not copy. Create that one index up front.

Step 3: Backfill old rows in small batches

INSERT INTO orders_new (id, user_id, total)
SELECT id, user_id, total
FROM orders
WHERE id > :last_id
ORDER BY id
LIMIT 10000
ON CONFLICT (id) DO NOTHING;

Loop with a short sleep, moving last_id forward each time. ON CONFLICT DO NOTHING ensures the trigger's newer version of a row is never overwritten by an older copied one.

Step 4: Build the remaining indexes on the new table

It's faster to build indexes after the data is loaded. Nobody reads from orders_new yet, so a plain CREATE INDEX is fine here, with no CONCURRENTLY needed.

Step 5: Verify

Compare row counts and spot-check data. Run ANALYZE orders_new so the query planner has fresh statistics.

Step 6: Swap in one short transaction

BEGIN;
SET LOCAL lock_timeout = '3s';
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

This takes milliseconds. If it times out, just retry. Afterward, drop the trigger and keep orders_old for a few days as a rollback option.

What makes this tricky

• Sequences and ownership: the new table needs the same sequence for id, with correct ownership.
• Foreign keys: other tables pointing to orders must be re-pointed, and adding FKs can itself be slow (use NOT VALID, then VALIDATE).
• Views, permissions, triggers, partitions: all must be recreated on the new table.
• Write overhead: every write happens twice during the process.
• Disk space: you temporarily need roughly double the table size, plus indexes.
• Hard-coded table names or prepared plans in application code can be affected by the rename.

Should you use it?

For adding a plain column or index, no. It adds risk and cost with no benefit.

Situation	Best approach
Nullable column, or column with constant default	Plain ALTER TABLE (instant)
Add an index	CREATE INDEX CONCURRENTLY
Change a column's type, or any change that forces a full rewrite	Shadow table
Reclaim heavy table bloat	Shadow table (pg_repack works this way)
Re-partition a large table	Shadow table

The shadow table is the heavy tool for changes PostgreSQL can't do online by itself. Reach for it only when the simple way isn't enough.

Cheat sheet

Task	Safe approach
Nullable column	Plain ADD COLUMN (instant)
Column with constant default	Plain ADD COLUMN on PG 11+ (instant)
NOT NULL column	Add nullable, batch backfill, CHECK ... NOT VALID, VALIDATE, SET NOT NULL
Index	Always CREATE INDEX CONCURRENTLY
Any DDL	Set lock_timeout first
Bulk data change	Small batches with a short sleep, never one giant UPDATE
Column type change, re-partitioning, heavy bloat	Shadow table: copy, mirror with a trigger, swap names

Takeaway

Small locks, small transactions, small batches.

Zero-downtime migrations are not about clever tricks. They are about never holding a heavy lock for long, and never letting your DDL wait in a queue that blocks everyone else.

Comments

[Jessica Williams]

The lock queue point is the one that bites people most often. A harmless ALTER waiting behind a long transaction can stall everything, so setting lock_timeout and retrying is a habit worth copying. The note about disabling the migration wrapper for CONCURRENTLY is useful too.

[Md Abu Musa]

Thanks! Yes, the lock queue gets people most often. lock_timeout plus retry has saved me more than once.