Online schema change: lock levels, CONCURRENTLY, gh-ost

What it is

Changing a table's structure on a live database without blocking reads or writes long enough to cause an outage. The difficulty is entirely about locks, and the answer differs by engine because their locking models differ.

POSTGRES     Most DDL takes ACCESS EXCLUSIVE, which blocks
             everything including SELECT. Many operations
             have been made fast or lock-free in recent
             versions; the rest need a workaround.

MYSQL        InnoDB online DDL has three algorithms
             (INSTANT, INPLACE, COPY) with different locking
             and different support per operation. Where it
             falls back to COPY, you use an external tool.

EXTERNAL     gh-ost and pt-online-schema-change build a
TOOLS        shadow table, backfill it, keep it in sync, and
             swap. Used when the native path is not online.

Commonly confused with the deploy sequencing. This page is about the DDL itself; the application-side sequencing is expand and contract, and you need both: a lock-free ALTER that ships alongside code using the new column still breaks the old pods during a rolling update.

Also commonly confused: "it took 200 milliseconds so it was safe". A short lock is not a safe lock if acquiring it queues behind a long-running transaction, which is the failure that takes production down.

The problem it solves

A schema change on a large table is one of the few operations that can take a healthy system down instantly, and the mechanism is not obvious.

THE LOCK QUEUE FAILURE, which is the one to know

  1. A long-running SELECT holds ACCESS SHARE on `orders`.
  2. Your ALTER TABLE requests ACCESS EXCLUSIVE.
     It cannot proceed, so it QUEUES.
  3. *** Every subsequent query on `orders` queues BEHIND
     the ALTER, because lock requests are ordered. ***
  4. The application's connection pool fills with waiters.
  5. Total outage on that table, and the ALTER itself has
     not started.

The ALTER was going to take 200 ms. The outage lasted as
long as the original SELECT, plus recovery.

That inversion is the whole reason lock_timeout matters: the danger is not the operation's duration, it is the queue that forms while it waits.

Mechanics

Postgres: know which operations are safe

SAFE (metadata only, brief ACCESS EXCLUSIVE)
  ADD COLUMN with no default, or with a non-volatile default
    (PG 11+ stores the default in catalog rather than
     rewriting the table)
  DROP COLUMN                    (marks it dropped; space
                                  reclaimed by VACUUM later)
  ADD CONSTRAINT ... NOT VALID   then VALIDATE separately
  RENAME COLUMN / TABLE
  SET STATISTICS

DANGEROUS (rewrites the table, holds the lock throughout)
  ALTER COLUMN TYPE              (most conversions)
  ADD COLUMN with a VOLATILE default
  SET NOT NULL                   (pre-PG 12: full scan)
  ADD PRIMARY KEY
  CLUSTER, VACUUM FULL

NEEDS THE SPECIAL FORM
  CREATE INDEX                -> CONCURRENTLY
  DROP INDEX                  -> CONCURRENTLY
  REINDEX                     -> CONCURRENTLY (PG 12+)

Two patterns that turn a dangerous operation into a safe sequence:

-- CONSTRAINT: validate without holding the lock during the scan.
ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive CHECK (total_cents > 0)
  NOT VALID;                       -- brief lock, no scan

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_positive;
                                   -- scans under SHARE UPDATE
                                   -- EXCLUSIVE: reads and writes
                                   -- continue

-- NOT NULL on PG 12+: add a validated CHECK first, then the
-- NOT NULL can use it and skip the scan.
ALTER TABLE users ADD CONSTRAINT users_email_nn
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;  -- no scan
ALTER TABLE users DROP CONSTRAINT users_email_nn;

`CREATE INDEX CONCURRENTLY`, and what it costs

It builds in two table scans plus a wait, without blocking
writes.

  1. Take SHARE UPDATE EXCLUSIVE (blocks other DDL, not DML)
  2. Wait for all transactions that could see the pre-index
     state
  3. First scan: build the index
  4. Second scan: catch rows that changed during scan 1
  5. Wait again for concurrent transactions

COSTS
  Roughly 2 to 3x the duration of a normal build.
  Cannot run inside a transaction block, so it cannot be
  part of a migration transaction, which matters for
  migration tooling.
  On failure it leaves an INVALID index that must be
  dropped explicitly, and which still costs write
  maintenance while it sits there.

-- Always check afterwards. An invalid index is invisible to
-- the planner and expensive on every write.
SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE NOT indisvalid;

The invalid-index leftover is the operational trap, because the migration "failed" cleanly, someone re-ran it, and the invalid index sits there for months slowing every write.

The two settings that prevent the outage

-- Do not queue for more than 2 seconds. If the lock is not
-- available, fail rather than blocking everything behind us.
SET lock_timeout = '2s';

-- Do not let the operation itself run away.
SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN referral_code text;

lock_timeout is the single most important line in any migration script, and its absence is the difference between a failed migration and an outage. The failure mode without it is exactly the lock queue above.

And the retry loop that makes it usable:

# A short lock_timeout means the migration often fails on a
# busy table. Retry with backoff rather than raising it.
for attempt in range(20):
    try:
        with conn.begin():
            conn.execute("SET LOCAL lock_timeout = '2s'")
            conn.execute(ddl)
        break
    except LockNotAvailable:
        time.sleep(2 ** min(attempt, 5))

Retrying a 2-second timeout twenty times is much safer than one attempt with a 60-second timeout, because each attempt's queue is short-lived.

MySQL: the three algorithms

ALGORITHM=INSTANT   Metadata only. Milliseconds regardless of
                    table size. Supported for: adding a column
                    (8.0.12+), renaming, changing a default,
                    and a growing list.

ALGORITHM=INPLACE   Rebuilds the table or index in place.
                    LOCK=NONE means DML continues. Duration
                    scales with table size.

ALGORITHM=COPY      Copies the whole table. Blocks writes
                    unless a tool manages it. This is the one
                    to avoid.

-- Assert what you expect rather than discovering it.
-- If INSTANT is not supported for this change, this FAILS
-- rather than silently falling back to COPY and blocking
-- writes for an hour.
ALTER TABLE orders
  ADD COLUMN referral_code VARCHAR(32),
  ALGORITHM=INSTANT;

Specifying the algorithm explicitly is the discipline, because the default is to pick the best available and silently fall back, and "silently falls back to COPY on a 400 GB table" is an outage discovered at runtime.

gh-ost and pt-online-schema-change

When the native path is COPY, an external tool does the same thing with control.

BOTH TOOLS
  1. Create a shadow table with the new schema.
  2. Copy rows in chunks, throttling on replica lag.
  3. Keep the shadow in sync with ongoing changes.
  4. Atomic rename swap.

THE DIFFERENCE, and it matters
  pt-online-schema-change uses TRIGGERS on the original
    table to propagate changes.
    -> Triggers run inside the original write transaction,
       so they add latency to every write and can amplify
       lock contention. On a hot table this is felt.

  gh-ost reads the BINLOG instead.
    -> No triggers, no added write latency, and it can run
       on a replica and swap on the primary.
    -> Also pausable and throttleable interactively, and it
       can be resumed.

gh-ost's binlog approach is why GitHub built it, and the interactive throttle is the operationally important part: you can pause a migration when a spike arrives and resume afterwards, which a trigger-based tool cannot do cleanly.

gh-ost \
  --alter="ADD COLUMN referral_code VARCHAR(32)" \
  --table=orders \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=100 \
  --chunk-size=1000 \
  --max-lag-millis=1500 \
  --allow-on-master \
  --postpone-cut-over-flag-file=/tmp/postpone \
  --execute

--postpone-cut-over-flag-file is the feature worth knowing: the copy runs to completion and then waits for you to remove the flag file, so the disruptive moment happens when a human is watching rather than at 3am when the copy happens to finish.

The backfill, which is where the outages actually happen

Adding a column is fast. Populating it is where the danger is.

-- CATASTROPHIC on a large table:
UPDATE orders SET referral_code = derive(source);
--   holds a long transaction
--   generates enormous WAL
--   blocks autovacuum from cleaning up dead tuples
--   lags replicas, possibly past their retention
--   and rolling it back is as expensive as running it

-- Batched, with a pause. The pause is not politeness: it is
-- what lets replicas catch up and autovacuum run.
DO $$
DECLARE last_id BIGINT := 0; n INT;
BEGIN
  LOOP
    WITH batch AS (
      SELECT id FROM orders
      WHERE id > last_id AND referral_code IS NULL
      ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
    )
    UPDATE orders o SET referral_code = derive(o.source)
    FROM batch b WHERE o.id = b.id;
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    SELECT max(id) INTO last_id FROM (
      SELECT id FROM orders WHERE id > last_id ORDER BY id LIMIT 5000) s;
    COMMIT;
    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

FOR UPDATE SKIP LOCKED in the batch selection means the backfill never blocks on a row a user transaction is holding; it skips it and picks it up on a later pass.

And the check that must gate the next step:

-- Before any code reads the new column, prove the backfill
-- is complete. This gate is not optional.
SELECT count(*) FROM orders WHERE referral_code IS NULL;

A worked example: a migration that caused an outage

THE CHANGE
  Add an index on orders(customer_id, created_at) to fix a
  slow query. 180M rows.

WHAT HAPPENED
  The migration ran `CREATE INDEX` (not CONCURRENTLY) inside
  the deploy pipeline's migration transaction, with no
  lock_timeout.

  1. A nightly analytics query held ACCESS SHARE on orders.
  2. CREATE INDEX requested ACCESS EXCLUSIVE and queued.
  3. All subsequent queries on orders queued behind it.
  4. The connection pool filled in ~40 seconds.
  5. Checkout, order history and fulfilment all failed.
  6. Duration: 22 minutes, until the analytics query
     finished and the index build then ran to completion,
     blocking throughout.

WHAT WOULD HAVE PREVENTED IT, in order of value
  a. lock_timeout = '2s'. The migration would have failed
     harmlessly and been retried. ONE LINE.
  b. CREATE INDEX CONCURRENTLY. No ACCESS EXCLUSIVE at all,
     and it cannot run in a transaction block, which would
     have forced (c).
  c. Running migrations OUTSIDE the deploy transaction, so a
     failed migration does not roll back a deploy and a
     long migration does not hold a transaction open.
  d. A pre-flight check for long-running transactions on the
     target table:

     SELECT pid, now() - xact_start AS age, query
     FROM pg_stat_activity
     WHERE state != 'idle' AND now() - xact_start > interval '1 min';

THE POLICY THAT CAME OUT OF IT
  Every migration script starts with SET lock_timeout, index
  creation is always CONCURRENTLY, migrations run outside
  the deploy transaction, and any migration touching a table
  over 10M rows requires a written plan naming the lock
  level it takes.

The finding worth stating: the index build was going to take 40 minutes and that was never the problem. The problem was 22 minutes of total unavailability caused by a queue, which a two-second timeout would have prevented entirely.

Production evidence

PostgreSQL's documentation on ALTER TABLE locking levels is the authoritative reference for which operations take which locks, and it is worth reading rather than trusting a blog post, because the list changes between versions as operations are optimised.

PostgreSQL 11's addition of non-volatile-default ADD COLUMN without a rewrite, and version 12's ability to use a validated CHECK to skip the SET NOT NULL scan, are examples of operations moving from dangerous to safe between versions, which is why the version matters.

GitHub's gh-ost announcement and design documentation explain the binlog-versus-trigger choice explicitly: triggers add latency inside the original write transaction, and the binlog approach also enables the interactive throttle and postponed cut-over.

Percona's pt-online-schema-change documentation is candid about the trigger overhead and about the foreign-key handling complications, which is the honest comparison.

MySQL's online DDL documentation lists per-operation algorithm support and is explicit that omitting ALGORITHM allows a silent fallback, which is the basis for specifying it.

Strong Migrations and similar linters exist as tooling that rejects dangerous DDL in code review, which is convergent evidence that this class of mistake is common enough to automate against.

The debate

The case for native online DDL: no extra tooling, no shadow table, no swap risk, and modern Postgres and MySQL handle most common operations without a rewrite. Adding gh-ost to a stack that does not need it is operational surface for nothing.

The case for always using a tool: predictable behaviour regardless of the operation, throttling on replica lag, pausability, and a postponed cut-over so the risky moment is supervised. On a large table the native path's behaviour varies by operation and version in ways that are easy to get wrong.

The case for a maintenance window: simplest, obviously safe, and for many organisations a Sunday morning window costs less than the engineering to avoid it. Increasingly unavailable as an option, and worth naming rather than dismissing.

My position: lock_timeout on every migration, CONCURRENTLY for every index, migrations outside the deploy transaction, and an external tool only when the native path would rewrite a large table.

lock_timeout is the one I would treat as non-negotiable, because the danger is the queue, not the operation. In the worked example a 40-minute index build caused 22 minutes of total unavailability before it even started, and one line would have turned that into a harmless failed migration. It is the highest ratio of protection to effort available anywhere in this topic.

Running migrations outside the deploy transaction matters for two reasons that compound: a migration inside a transaction cannot use CONCURRENTLY at all, and a long migration inside the deploy holds a transaction open across the whole deploy, which blocks vacuum and can lag replicas.

On tooling, I would not add gh-ost by default. The question is whether the specific operation on the specific table would rewrite it, and for ADD COLUMN on modern Postgres or INSTANT on modern MySQL the answer is no, so the tool adds risk rather than removing it. Where the native path is a copy on a 400 GB table, gh-ost's binlog approach, throttling and postponed cut-over are worth the operational surface, and I would prefer it over the trigger-based alternative because triggers add latency inside every write transaction on the original table.

The thing I would insist on beyond the DDL itself is the backfill discipline, because that is where the outages actually happen. Adding the column is milliseconds; the single UPDATE over 180 million rows holds a long transaction, generates enormous WAL, blocks autovacuum and lags replicas. Batched with SKIP LOCKED and a short sleep, with a completeness check gating the next step.

Where I would push back on a proposal: specify the lock level you expect and assert it. In MySQL that is ALGORITHM=INSTANT failing rather than falling back to COPY; in Postgres it is knowing which lock the operation takes and saying so in the migration plan. "It worked in staging" is not evidence, because staging has neither the row count nor the concurrent long-running transaction.

Follow-up Q&A

"How do you add a column to a 200 million row table safely?" The ADD COLUMN itself is usually the easy part: on Postgres 11 and later, adding a column with no default or a non-volatile default is a catalog change, so milliseconds regardless of size. What I would put first is SET lock_timeout = '2s' in the migration, because the danger is not the operation's duration, it is that it queues behind a long-running transaction and then every subsequent query queues behind it. Then the backfill, batched, and only then the code that reads it.

"Explain the lock queue failure." A long-running SELECT holds ACCESS SHARE. Your ALTER requests ACCESS EXCLUSIVE and cannot proceed, so it queues. And because lock requests are ordered, every subsequent query on that table now queues behind the ALTER. The connection pool fills, and you have a total outage on that table before the ALTER has even started. In a case I worked, a 40-minute index build produced 22 minutes of unavailability that way, and a two-second lock_timeout would have prevented all of it.

"What does CREATE INDEX CONCURRENTLY cost?" Roughly two to three times the duration, because it does two table scans plus two waits for concurrent transactions. It cannot run inside a transaction block, which means migrations have to run outside the deploy transaction. And on failure it leaves an INVALID index behind, which the planner ignores while it still costs write maintenance, so checking pg_index for NOT indisvalid afterwards is part of the procedure. That leftover sitting for months is the common operational trap.

"How do you make SET NOT NULL safe on a large table?" On Postgres 12 and later, add a CHECK (col IS NOT NULL) as NOT VALID, which takes a brief lock and no scan, then VALIDATE CONSTRAINT, which scans under SHARE UPDATE EXCLUSIVE so reads and writes continue. Then SET NOT NULL can use the validated constraint and skips its own scan, and you drop the check afterwards. The same NOT VALID then VALIDATE pattern is the general answer for constraints.

"When would you use gh-ost rather than native DDL?" When the native path would rewrite a large table, which on MySQL means the operation falls back to ALGORITHM=COPY. I would not add it by default, because for the common operations modern engines are already online and the tool is operational surface for nothing. When I do use it, gh-ost over pt-online-schema-change, because it reads the binlog rather than installing triggers, so it adds no latency inside the original write transaction, and because it can be throttled and paused interactively.

"What's the gh-ost feature you'd actually rely on?" The postponed cut-over flag file. The copy runs to completion and then waits for you to remove the file, so the disruptive rename happens when a human is watching rather than whenever the copy finishes, which might be 3am. Combined with --max-load and --max-lag-millis for throttling, that is what makes a multi-hour migration on a hot table supervisable.

"Where do these migrations actually go wrong?" The backfill, not the DDL. A single UPDATE over 180 million rows holds a long transaction, generates enormous WAL, blocks autovacuum from cleaning dead tuples, and lags replicas possibly past their retention. Batched in five thousand row chunks with FOR UPDATE SKIP LOCKED so it never blocks on a row a user holds, with a short sleep to let replicas catch up. And a completeness check, counting remaining nulls, gating the deploy that starts reading the column.

"What about MySQL specifically?" Specify ALGORITHM explicitly rather than letting it pick, because the default is to choose the best available and silently fall back, and "silently falls back to COPY on a 400 gigabyte table" is an outage discovered at runtime. Asserting ALGORITHM=INSTANT means the statement fails if instant is unavailable, which is exactly what you want in a migration script.

"How does this relate to expand-contract?" They are two halves and you need both. This is the DDL: which lock it takes, how to avoid the queue, how to backfill. Expand-contract is the application-side sequencing: write both, read old, then read new, then stop writing old, then drop. A perfectly lock-free ALTER shipped alongside code that uses the new column still breaks every pod that has not been replaced yet during a rolling update.

Common misconceptions

"It only takes 200 milliseconds, so it's safe." Duration is not the risk. Queueing behind a long-running transaction is, and every subsequent query queues behind you.

"CONCURRENTLY is free." It is two to three times slower, cannot run in a transaction, and leaves an invalid index on failure that costs write maintenance until dropped.

"Adding a column is the risky part." On modern engines it is a catalog change. The backfill is where the outages are.

"MySQL online DDL handles it." Only for operations that support INSTANT or INPLACE. Without an explicit ALGORITHM it silently falls back to COPY.

"It worked in staging." Staging has neither the row count nor the concurrent long-running analytics query, which are the two things that cause the failure.

Interview delivery note

Lead with the lock queue, because it is the mechanism people do not know and it reframes the whole question: "The danger isn't how long the operation takes. It's that if it can't get its lock immediately it queues, and because lock requests are ordered, every subsequent query on that table queues behind it. So a forty-minute index build can produce total unavailability before it has even started."

Then the one-line fix: "Which is why SET lock_timeout is the most important line in any migration script. Two seconds, and retry with backoff. A short timeout retried twenty times is much safer than one attempt with a sixty-second timeout, because each attempt's queue is short-lived."

Show engine-specific knowledge concretely: "On Postgres I'd know which operations rewrite the table: adding a column with a non-volatile default doesn't since version 11, SET NOT NULL can skip its scan since 12 if you add a validated CHECK first, and index creation always goes CONCURRENTLY, which costs two to three times the duration and can't run inside a transaction. On MySQL I'd specify ALGORITHM=INSTANT explicitly so it fails rather than silently falling back to COPY."

Point at where the outages actually are: "And I'd say that the DDL usually isn't where these go wrong, the backfill is. A single UPDATE over a hundred and eighty million rows holds a long transaction, generates enormous WAL, blocks autovacuum and lags replicas. Batched with SKIP LOCKED and a short sleep, with a completeness check gating the deploy that reads the column."

Close by connecting it to the other half: "and this is only the database side. The application sequencing is expand-contract, and you need both, because a perfectly lock-free ALTER shipped alongside code that uses the new column still breaks every pod that hasn't been replaced yet."

The Staff & Team Lead Interview Reference