Migrating a Large Table Without Locking Production

GoCardless once took around 15 seconds of unexpected API downtime from a planned migration. The tables being changed were empty. The statement was fast. It didn't matter, because the migration needed a lock on a heavily used parent table, a long-running read was already holding a conflicting lock, and every API query that arrived after that queued up behind the migration until clients timed out. They wrote it up in Zero-downtime Postgres migrations: the hard parts.

That story is the best correction I know to the way this topic usually gets framed. "Tables too big to lock" suggests the danger is size: a billion rows, a rewrite that takes forty minutes. Size is a real problem, but it's the second one. The first is that any ALTER TABLE that has to wait for its lock can freeze a table of any size, including an empty one, for as long as it waits.

So there are two separate things that go wrong, and they need separate fixes:

1. The wait. Your DDL can't get its lock, and it blocks everyone queued behind it while it waits. Table size is irrelevant.
2. The hold. Your DDL gets the lock and then does work proportional to table size while holding it: a full rewrite, or a full scan to prove a constraint. This is where size matters.

A safe migration handles both, in that order. The target is PostgreSQL, and every lock level below comes straight from the official docs.

The lock queue, precisely

Postgres has eight table-level lock modes. The one that matters most here is ACCESS EXCLUSIVE, and the explicit locking docs are blunt about it: it conflicts with every other mode, and "only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement." It's also the default for ALTER TABLE. The ALTER TABLE docs say "an ACCESS EXCLUSIVE lock is acquired unless explicitly noted," and most subcommands aren't noted.

Here's the part people miss. A plain SELECT takes ACCESS SHARE, which only conflicts with ACCESS EXCLUSIVE. So you'd think a stream of reads can't be blocked by anything except a DDL that's actually holding its lock. Wrong. When a backend requests a lock, Postgres checks it against locks that are held and against locks already waiting in the queue. A waiting ACCESS EXCLUSIVE request conflicts with everything, so new readers line up behind it.

You can reproduce it with three psql sessions:

-- Session A: an innocent analytics query, or just a forgotten open transaction
BEGIN;
SELECT count(*) FROM orders;   -- holds ACCESS SHARE on orders until COMMIT/ROLLBACK
-- (transaction left open)

-- Session B: the migration
ALTER TABLE orders ADD COLUMN note text;   -- needs ACCESS EXCLUSIVE, waits for A

-- Session C: normal app traffic
SELECT * FROM orders WHERE id = 42;   -- ACCESS SHARE, but queues behind B's waiting request

Session C doesn't conflict with Session A at all. It's stuck because of B, and B is stuck because of A. Every request your app sends to orders joins C in the line. The ALTER TABLE itself is a catalog-only change that would finish in milliseconds. It never gets the chance.

If you've read what SELECT ... FOR UPDATE actually blocks, this is the flip side. Row locks almost never block plain reads. A queued table lock blocks all of them.

Rule zero: never wait for a lock without a deadline

The fix for the wait problem is one setting. lock_timeout aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock." The default is zero, which means wait forever, which means your migration is allowed to hold the whole table hostage for as long as the slowest open transaction lives.

Set it on every migration session:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;
-- ERROR:  canceling statement due to lock timeout

Failing is the point. A migration that errors out after two seconds cost you two seconds of queued traffic. A migration that waits until an idle-in-transaction session finally closes costs you however long that is. Xata's write-up on schema changes and the lock queue puts it as "values of less than 2 seconds are common." Pick the number your p99 latency budget can absorb, not a round one.

Then retry, because the lock will usually be free a moment later. Nikolay Samokhvalov's lock_timeout and retries post on postgres.ai uses a very short timeout plus capped exponential backoff with jitter, all inside a DO block. A trimmed version of that pattern:

DO $$
DECLARE
  attempt int;
BEGIN
  PERFORM set_config('lock_timeout', '100ms', false);
  FOR attempt IN 1..20 LOOP
    BEGIN
      ALTER TABLE orders ADD COLUMN note text;
      RETURN;  -- success
    EXCEPTION WHEN lock_not_available THEN
      -- full jitter: sleep a random slice of an exponentially growing window, capped at 10s
      PERFORM pg_sleep(random() * least(10, 0.01 * 2 ^ attempt));
    END;
  END LOOP;
  RAISE EXCEPTION 'could not acquire lock on orders after 20 attempts';
END $$;

The lock_not_available condition is what lock_timeout raises, so this catches exactly the "couldn't get the lock" failure and nothing else. Most migration frameworks have their own version: GitLab's migration helpers wrap risky statements in with_lock_retries, and their avoiding downtime guide notes that "without lock retries, this lock can time out and require a manual retry."

Two related settings are worth knowing. idle_in_transaction_session_timeout kills sessions that sit idle inside an open transaction, which is the classic Session A. And since Postgres 17 there's transaction_timeout, which caps total transaction duration. The docs recommend against setting transaction_timeout globally in postgresql.conf, so treat it as a per-role or per-session tool.

When a migration keeps timing out, find out who's in the way instead of raising the timeout:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       now() - xact_start    AS xact_age,
       left(query, 80)       AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND xact_start IS NOT NULL
ORDER BY xact_start;

The oldest xact_age at the top is usually your answer. Very often it's a BI tool, a psql session someone left open, or a worker that begins a transaction and then makes an HTTP call.

Know which DDL is actually cheap

With the wait under control, the question becomes what happens once you have the lock. A lot of ALTER TABLE forms take ACCESS EXCLUSIVE but only touch the catalog, so they hold it for milliseconds. Others rewrite the entire table and every index while holding it. The difference is invisible in the SQL.

Here's the map, from the current (Postgres 18) docs:

Operation	Lock	Work under the lock
ADD COLUMN nullable, no default	ACCESS EXCLUSIVE	Catalog only
ADD COLUMN ... DEFAULT <non-volatile>	ACCESS EXCLUSIVE	Catalog only since Postgres 11
ADD COLUMN ... DEFAULT clock_timestamp() (volatile)	ACCESS EXCLUSIVE	Full table and index rewrite
DROP COLUMN	ACCESS EXCLUSIVE	Catalog only, space not reclaimed
ALTER COLUMN TYPE varchar(n) to larger n, or to text	ACCESS EXCLUSIVE	No rewrite (binary coercible)
ALTER COLUMN TYPE int to bigint	ACCESS EXCLUSIVE	Full table and index rewrite
SET NOT NULL	ACCESS EXCLUSIVE	Full scan, unless a valid CHECK proves it
ADD CONSTRAINT ... CHECK	ACCESS EXCLUSIVE	Full scan (skip with NOT VALID)
ADD FOREIGN KEY	SHARE ROW EXCLUSIVE on both tables	Full scan (skip with NOT VALID)
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	Full scan, but reads and writes continue
CREATE INDEX	SHARE	Full build, writes blocked
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	Two scans, reads and writes continue
RENAME COLUMN	ACCESS EXCLUSIVE	Catalog only, but breaks every query using the old name

A few rows deserve a sentence each.

Fast defaults. Before Postgres 11, ADD COLUMN ... DEFAULT 'x' rewrote every row. Since 11, the default is evaluated once and stored in pg_attribute.attmissingval; existing rows get it on read. The docs describe this as "very fast even on large tables." The catch is the word non-volatile. now() is fine because it's stable within a transaction. clock_timestamp() or gen_random_uuid() as a default is volatile, and the docs say that "will cause the entire table and its indexes to be rewritten." Same for adding a stored generated column or an identity column.

Type changes. The docs spell out the exception: no rewrite when the old type is "binary coercible to the new type" and USING doesn't change the contents. Widening varchar(50) to varchar(200) has been rewrite-free since Postgres 9.2. int to bigint is not binary coercible, so it rewrites. On a big table that's the forty-minute lock from the top of this post, and it gets its own section below.

DROP COLUMN is cheap for the database and dangerous for the app. The column becomes invisible immediately; the bytes stay on disk until rows get rewritten. The real risk is any running app instance that still selects that column by name, which is why GitLab's process spreads a column drop across three releases, starting with ignore_column in the model.

Foreign keys got cheaper, not free. GoCardless's outage involved adding a foreign key, which back then took ACCESS EXCLUSIVE on both tables. Postgres 9.5 reduced the lock level, and today ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on the table and on the referenced table. That still conflicts with ROW EXCLUSIVE, the lock every INSERT, UPDATE, and DELETE takes. So a queued FK addition won't freeze reads anymore, but it will freeze writes on both tables. Still needs lock_timeout.

Constraints: add now, prove later

Adding a CHECK or foreign key normally scans the whole table under the lock to prove existing rows comply. NOT VALID splits that in two:

SET lock_timeout = '2s';

-- Step 1: brief lock, no scan. New writes are checked from this moment on.
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- Step 2: the scan, under a lock that allows reads and writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

The docs explain why step 2 can be relaxed: "the validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock." For a foreign key, it also takes ROW SHARE on the referenced table. There's a bonus the docs point out: if the table already has violating rows, the NOT VALID constraint stops new ones while you clean up the old ones, and VALIDATE succeeds when you're done.

SET NOT NULL is the one that trips people up, because it has no NOT VALID clause of its own before Postgres 18. The workaround uses a detail that landed in Postgres 12: SET NOT NULL skips its full scan "if a valid CHECK constraint exists ... which proves no NULL can exist."

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  CHECK (region IS NOT NULL) NOT VALID;          -- brief ACCESS EXCLUSIVE, no scan

ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;   -- scan, SHARE UPDATE EXCLUSIVE

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;   -- brief ACCESS EXCLUSIVE, scan skipped
ALTER TABLE orders DROP CONSTRAINT orders_region_not_null;   -- redundant now

On Postgres 18 you can skip the CHECK detour. The 18 release notes add NOT VALID for not-null constraints, so this works directly:

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null NOT NULL region NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

Indexes: CONCURRENTLY, and what it doesn't tell you

A plain CREATE INDEX takes a SHARE lock. Reads keep working; every INSERT, UPDATE, and DELETE blocks until the build finishes. On a large table that's the whole outage, just quieter. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead, which allows writes.

The CREATE INDEX docs list the price. It does "two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate." Three consequences bite in practice:

• It can't run in a transaction block. Most migration frameworks wrap each migration in a transaction by default, so the statement fails. Rails has disable_ddl_transaction!, Django has atomic = False on the migration class. You need the escape hatch, and you need to know you're now running without a rollback safety net.
• It waits on old transactions too. The long-running transaction that ruins your ALTER TABLE will also stall a concurrent build. The build doesn't block your traffic while it waits, but it won't finish either.
• Failure leaves debris. A deadlock or uniqueness violation mid-build leaves an index marked INVALID. The docs say it's "ignored for querying purposes" but "will still consume update overhead." You have to drop it and try again (or REINDEX INDEX CONCURRENTLY it). A migration that retries CREATE INDEX CONCURRENTLY IF NOT EXISTS after a failure will happily skip because the invalid index exists. Check pg_index.indisvalid.

Need a unique constraint or a new primary key? Build the unique index concurrently, then attach it:

CREATE UNIQUE INDEX CONCURRENTLY orders_external_ref_key ON orders (external_ref);
ALTER TABLE orders ADD CONSTRAINT orders_external_ref_key UNIQUE USING INDEX orders_external_ref_key;

The USING INDEX form is fast, with one exception worth reading twice: for a PRIMARY KEY, if the columns aren't already marked NOT NULL, the command runs SET NOT NULL on them, which "requires a full table scan." Do the NOT NULL dance from the previous section first.

When the table really has to be rewritten: expand, backfill, swap

Some changes have no catalog-only shortcut. The canonical one is an int primary key approaching 2,147,483,647. ALTER COLUMN id TYPE bigint rewrites the table and every index under ACCESS EXCLUSIVE, for as long as that takes. On a table big enough to be running out of ids, that's not a maintenance window, that's an outage.

GitLab has a documented process for exactly this, and it spans four releases: add the new column with a sync trigger and start a batched background backfill, swap the columns in a post-deployment migration, drop the trigger and the old column, then remove the ignore rules. Crunchy Data published a pure-SQL version of the same idea in January 2026. Here's the shape, on a hypothetical orders table with id serial PRIMARY KEY and no inbound foreign keys (we'll come back to those).

Expand: new column plus a sync trigger

SET lock_timeout = '2s';

ALTER TABLE orders ADD COLUMN id_new bigint;   -- nullable, no default: catalog only

CREATE FUNCTION orders_sync_id_new() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END $$;

CREATE TRIGGER orders_sync_id_new
  BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_id_new();

CREATE TRIGGER takes SHARE ROW EXCLUSIVE, which blocks writes while it's queued, so it gets the timeout too. From this point on, every new or updated row carries its own id_new. Only the historical rows are missing it.

Backfill: many small transactions

The mistake here is a single UPDATE orders SET id_new = id. That's one transaction touching every row: it holds row locks on the entire table until it commits, generates a dead tuple per row that vacuum can't clean until it ends, and dumps a huge burst of WAL on your replicas. Break it up. Postgres 11 procedures can COMMIT between batches:

CREATE PROCEDURE backfill_orders_id_new(batch_size int, pause_seconds float8)
LANGUAGE plpgsql AS $$
DECLARE
  cursor_id bigint := 0;
  max_id    bigint;
BEGIN
  SELECT max(id) INTO max_id FROM orders;
  WHILE cursor_id < max_id LOOP
    UPDATE orders
       SET id_new = id
     WHERE id >  cursor_id
       AND id <= cursor_id + batch_size
       AND id_new IS NULL;
    cursor_id := cursor_id + batch_size;
    COMMIT;                          -- each batch is its own transaction
    PERFORM pg_sleep(pause_seconds); -- give vacuum and replicas room to breathe
  END LOOP;
END $$;

CALL backfill_orders_id_new(10000, 0.1);

The batch size and pause there are placeholders, not recommendations. Tune them by watching replication lag and query latency while it runs, and start small. Ranges on the primary key beat OFFSET pagination, which gets slower with every page. Rows inserted after max_id was read don't need the backfill; the trigger already covered them.

Two things to watch while it runs. Dead tuples: every updated row leaves one, so either let autovacuum keep up or run VACUUM orders periodically during the backfill, which Crunchy's write-up recommends for large production moves. And replicas: a backfill that outruns replication turns into stale reads for everyone hitting a read replica.

Prepare: index and not-null, both online

CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_key ON orders (id_new);

ALTER TABLE orders ADD CONSTRAINT orders_id_new_not_null
  CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_not_null;

Everything expensive is now done, and none of it blocked traffic.

Swap: one short transaction

BEGIN;
SET LOCAL lock_timeout = '2s';

ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ALTER COLUMN id DROP NOT NULL;           -- old column must accept NULLs from now on
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;

ALTER SEQUENCE orders_id_seq AS bigint;
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;
ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval('orders_id_seq');

ALTER TABLE orders ALTER COLUMN id SET NOT NULL;            -- scan skipped: valid CHECK proves it
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_key;
ALTER TABLE orders DROP CONSTRAINT orders_id_new_not_null;

DROP TRIGGER orders_sync_id_new ON orders;
COMMIT;

Every statement in there is catalog-only, so the ACCESS EXCLUSIVE lock is held for milliseconds. If the lock isn't available within two seconds, the whole thing rolls back and you retry. A few lines exist because of gotchas that are easy to miss:

• ALTER SEQUENCE ... AS bigint. Per the numeric types docs, a serial column is shorthand for CREATE SEQUENCE ... AS integer. Change the column and forget the sequence, and it still stops at 2,147,483,647. The ALTER SEQUENCE docs confirm the max moves with the type as long as it was still sitting at the old type's limit.
• DROP NOT NULL on the old column. The old id was NOT NULL with a default. After the swap, inserts stop filling it, so without this line every insert fails.
• The trigger has to go in the same transaction. Its function references NEW.id_new, which no longer exists after the rename.

Drop id_old in a later release, once nothing reads it. DROP COLUMN is catalog-only, so that step is cheap.

The part that makes it take four releases

Foreign keys. If order_items.order_id references orders.id, dropping orders_pkey fails unless you CASCADE, which drops the FK. And order_items.order_id is also an int, so it needs the same expand-backfill-swap treatment first. Crunchy's guide orders it that way: update child FK columns before the parent swap. Then recreate the FKs with NOT VALID and validate them. On a schema with a dozen tables pointing at the one you're converting, this is where the calendar time goes, and it's why GitLab's helpers exist at all.

Or let a tool do it

Hand-rolling this is educational once. After that, there are tools with the same mechanics built in:

• pg_repack rebuilds a table online using a log table, triggers, and a swap. It holds ACCESS EXCLUSIVE only "for a short period during initial setup" and again "during the final swap-and-drop phase." It needs a primary key or a unique index on a NOT NULL column, and roughly twice the table-plus-indexes size in free disk. It's built for removing bloat and reordering, not for arbitrary schema changes.
• pg-osc is inspired by pg_repack and Percona's pt-online-schema-change: build a shadow table with the new schema, copy data over while triggers capture ongoing changes, then swap names.
• pgroll from Xata takes a different route. It "works by creating virtual schemas by using views on top of the physical tables," follows expand/contract, and keeps old and new schema versions live at the same time so old and new app versions can both run. complete drops the old version; rollback drops the new one.

My take: for column type changes on a handful of tables, the hand-rolled version above is fine and you'll understand every lock it takes. Shadow-table tools copy the whole table to change one column, which is a lot of I/O and disk for an int to bigint. pgroll earns its keep when you're doing this regularly and want the app-versioning side enforced rather than remembered. Whichever you pick, it still needs the same thing the manual version needs: no hour-long transactions sitting on the table when it's time to swap.

The app is half of the migration

Every technique here assumes something the database can't enforce: that your application can live with both the old and new schema for a while. Renaming a column in place is catalog-only and instant, and it still breaks every running instance of the old code that references the old name. That's why the GitLab guide treats a rename as rename_column_concurrently in one release and cleanup in the next, and why pgroll bothers with versioned views.

The order that holds up:

1. Expand the schema so old and new code both work (new nullable column, new table, new index).
2. Deploy code that writes both shapes and reads the old one.
3. Backfill in small batches.
4. Deploy code that reads the new shape.
5. Contract: drop the old column, trigger, and constraints in a later release.

Skip steps and you're back to needing a maintenance window, no matter how clever the SQL is.

Tip
Before you press enter on any production DDL, answer three questions: which lock does this take (look it up, don't guess), does it rewrite or scan while holding it, and is lock_timeout set in this session? If you can't answer the first two from the docs, run it against a production-sized copy with \timing on first.

The billion-row table gets all the attention, but it's the forgotten open transaction and the missing lock_timeout that turn a two-millisecond ALTER TABLE into fifteen seconds of timeouts. Fix the wait first. Then deal with the size.

---

Originally published at andriiboyko.com.