ADD COLUMN: The Lock Queue Behind a Metadata-Only Migration

The migration is a single statement that adds one nullable column. It does not touch a row, it does not rewrite a page, and on a modern engine it completes in single-digit milliseconds. It is the cheapest statement in the schema.

It also took a production site offline for forty minutes, and both facts are true at the same time.

The lock queue is a first-class part of the engine’s behaviour, not a misfeature, so the same reasoning that resolves a deadlock by ordering your locks applies here: you cannot fix a queue by waiting, only by not joining it. What the engine is doing while it waits matters too, and a constraint it is trying to validate without an index is the slowest version of that story. Foreign keys and the index you forgot.

The metadata-only part is true, and irrelevant

Worth separating the cost of the operation from the cost of acquiring the right to perform it.

ALTER TABLE orders ADD COLUMN risk_score numeric;

Adding a nullable column with no default writes a few bytes into a system catalog. No heap page is touched. The entire cost of the work is a catalog update plus a transaction commit, which on a warm system is a few milliseconds.

The engine still needs a lock that guarantees no one is reading the table definition while it changes. That lock is ACCESS EXCLUSIVE, it is the most restrictive lock in Postgres, and it conflicts with everything including ACCESS SHARE, which is the lock a plain SELECT takes.

So the migration needs the most exclusive lock available, and the table is under continuous read traffic.

The queue is the entire problem

Here is the behaviour that turns a five millisecond statement into an outage. The lock queue in Postgres is ordered, and new requests join the back of it.

  t=0.00   long-running report starts
           SELECT ... FROM orders          takes ACCESS SHARE
           this is normal and would end in 30s

  t=1.00   migration runs
           ALTER TABLE orders ADD COLUMN  wants ACCESS EXCLUSIVE
           queued behind the report

  t=1.01   normal query arrives
           SELECT * FROM orders WHERE id = 1
           -> cannot overtake the queued ACCESS EXCLUSIVE
           -> queued behind the MIGRATION

  t=1.02   another query
           -> queued behind the migration

  t=1.50   connection pool is now full of queries waiting on the queue
           every request on this table is stalled

The critical detail is step three. A request that arrives after the migration is queued does not get the lock it could easily have had. It waits behind a statement that is itself waiting, because Postgres grants locks in order and does not let a reader jump a queued writer.

The result is that a query which would have taken one millisecond now waits for the length of a long transaction plus the migration. Under any real traffic, a handful of these long transactions exist at all times, so a migration that needs an exclusive lock is going to wait for the longest one, and everything that arrives while it waits is stuck behind it too.

Two amplifiers make this reliably catastrophic rather than merely bad.

Lock queue amplification. A low-traffic system has gaps between queries and the migration lands in one. A busy system has no gap, so the migration waits, and the waiting itself generates more queued work, which is the state in which the pool exhausts.

Long transactions. The wait is not bounded by the migration, it is bounded by the longest-running thing that holds a conflicting lock. A report that runs for thirty seconds, an export that streams rows, or a session left idle in transaction by a crashed process all do it. An idle in transaction session is the nastiest, because it holds its locks until it is terminated and nothing in the application is aware it exists.

-- find the thing that will hold up your migration
SELECT pid, state, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state LIKE 'idle in transaction%'
ORDER BY xact_start;

The fix is a bounded wait and a retry

The migration does not need to succeed immediately. It needs to succeed eventually, in a gap, without taking the table hostage while it waits.

lock_timeout is the setting that does that.

SET lock_timeout = '2s';

ALTER TABLE orders ADD COLUMN risk_score numeric;

If the lock is not acquired in two seconds the statement fails with 55P03 lock_not_available and does no work. Nothing is queued, nothing is blocked beyond that two-second window, and the migration can simply be attempted again.

The full pattern is a retry loop with a small random backoff.

SET lock_timeout = '2s';

DO $$
BEGIN
  LOOP
    BEGIN
      ALTER TABLE orders ADD COLUMN risk_score numeric;
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      PERFORM pg_sleep(0.2 + random() * 0.4);
    END;
  END LOOP;
END $$;

The damage from any single attempt is now bounded by the timeout rather than by the longest transaction on the table. With traffic arriving continuously there are always short windows where no lock is held, and the loop will find one.

The jitter matters more than the duration. Without it, every pod retrying on the same cadence collides with the same long transaction and then collides with each other. With it, the attempts spread out and one of them wins.

  without lock_timeout            with lock_timeout = 2s
  ----------                       --------------------
  ALTER waits for the 30s report   ALTER waits 2s, fails
  400 queries queue behind it      ~15 queries queue behind it
  pool exhausts in 4s              those 15 clear, loop retries
  40 minute outage                 migration lands in a gap, ~3s total

The same treatment applies to every other statement that needs an exclusive lock: ALTER TABLE ... TYPE, adding a foreign key, dropping a column, VACUUM FULL, CLUSTER, and a table rewrite. They are all the same problem with different durations.

When it is not metadata-only

Two variations turn the same statement into a long one, and the surprise is that the syntax is nearly identical.

A non-constant default rewrites the table. Postgres 11 added the ability to add a column with a constant default without touching the data, so this is fast.

ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'new';   -- metadata only

But the optimisation requires a non-volatile default that PostgreSQL can evaluate once and store in the catalog. A volatile function has to be evaluated per row, which means a rewrite.

ALTER TABLE orders ADD COLUMN created_on timestamptz DEFAULT now();
-- now() is STABLE, so current PostgreSQL versions use the fast-default path

ALTER TABLE orders ADD COLUMN observed_at timestamptz DEFAULT clock_timestamp();
-- clock_timestamp() is VOLATILE, so existing rows require a rewrite

-- i = immutable, s = stable, v = volatile
SELECT oid::regprocedure AS function, provolatile
FROM pg_proc
WHERE oid IN (
    'now()'::regprocedure,
    'clock_timestamp()'::regprocedure
);

provolatile reports the classification: i immutable, s stable, v volatile. now() is stable and returns the transaction start time; clock_timestamp() is volatile and changes while a statement is running. The database version matters here: PostgreSQL added the fast-default optimisation in version 11, so rehearse the exact statement on the major version you operate instead of reasoning from syntax alone.

A rewrite of a large table holds ACCESS EXCLUSIVE for its entire duration. That is not a retryable wait, it is a genuine multi-minute lock, and the retry loop above will not help because the statement is legitimately going to take that long.

The answer for a rewrite is not to retry it. It is to avoid it.

  add a NOT NULL column to a large table safely

  1. ADD COLUMN risk_score numeric                     -- nullable, fast
  2. backfill in batches, commit between batches      -- no long lock
  3. add a CHECK constraint NOT VALID                  -- brief lock
  4. VALIDATE CONSTRAINT                               -- no write lock
  5. SET NOT NULL                                     -- uses the validated check
  6. drop the check

Step two is where the time goes and it is allowed to take time, because it is a series of short transactions rather than one long one. Step four is the part people miss: validation scans the whole table but takes only a share lock, so it does not block writes.

A NOT NULL column with no default fails outright on a table with existing rows, because there is no value to put in them. The staged approach above is the way.

Migrations at application startup

The most common way to turn a four-line change into a forty-minute outage is not a long transaction. It is running the migration from the application.

// this is the bug, and it is extremely common
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

var app = builder.Build();

// every instance of this service does this
await using (var scope = app.Services.CreateAsyncScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();
}

app.MapGet("/", () => "ok");
app.Run();

Twenty pods rolling out means twenty concurrent attempts at the same DDL, and the consequences compound.

The first one to run takes the lock and does the work in milliseconds. The other nineteen queue behind it, and everything else on that table queues behind those nineteen. You have taken a four-line change and built a lock convoy.

Worse, the pod doing the migration cannot serve traffic until it finishes, so if the migration waits, that pod is also not ready, which means the rollout is gated on the lock queue, which means the deploy takes as long as the longest transaction on the table. A slow migration and a slow rollout become the same incident.

The other failure is an incompatible migration applied per-instance. If the code expects a column and the instance that receives traffic first has not run the migration yet, or if the migration runs on a pod that is then killed mid-deploy, you have a version of the code talking to a schema it does not expect.

The fix is to take migrations out of the startup path entirely. A migration is a deployment step, run once, by one thing, before any new version serves traffic.

# a separate job, run once, before the rollout
dotnet run --project src/Migrator -- --connection "$DATABASE_URL"

and if something will still run it concurrently, serialise it with an advisory lock so exactly one instance does the work.

-- any number of runners may start; exactly one migrates
SELECT pg_advisory_lock(7724413);   -- arbitrary but fixed per database

\i migrations/0007_add_risk_score.sql

SELECT pg_advisory_unlock(7724413);

This single lock is worth more than most schema review, because it makes the whole class of “two deploys at once” problems impossible rather than unlikely.

A production-ready architecture

  deployment pipeline
        |
        v
  +-----------------------------+  one runner, not N pods
  | migration job              |
  +-----------------------------+
        | acquires pg_advisory_lock, then:
        v
  +---------------------------------------------+
  | SET lock_timeout = '2s'                    |
  |                                             |
  |  will this statement rewrite the table?     |
  +---------------------------------------------+
     | no                        | yes
     v                          v
  +---------------+     +------------------------------+
  | retry loop    |     | split into steps:             |
  | bounded wait, |     |   nullable add               |
  | jitter        |     |   batched backfill           |
  +---------------+     |   NOT VALID check            |
                        |   VALIDATE                   |
                        |   SET NOT NULL               |
                        +------------------------------+

  +--------------------------------------------------+
  | never hold a lock across a long transaction       |
  | never migrate from application startup           |
  | kill idle in transaction sessions proactively    |
  +--------------------------------------------------+

A delivery checklist:

1. Set lock_timeout in every migration script, not in application code, so it applies to the statement that needs it.
2. Wrap lockable DDL in a retry loop with jitter rather than submitting it once and hoping.
3. Separate migrations into a deployment job. Do not run them from Program.cs.
4. Guard migrations with pg_advisory_lock so concurrent runners cannot collide.
5. Know which statements rewrite the table. Check provolatile for function defaults you did not write yourself, and verify the behavior on the PostgreSQL major version you operate.
6. Split a rewrite into a nullable add, a batched backfill, a NOT VALID check, and a validation.
7. Alarm on idle in transaction sessions with an age threshold and terminate them. They are the reason a migration waits forty minutes.
8. Set a per-statement statement_timeout on migrations so a genuinely stuck one fails instead of holding a lock.
9. Check the lock queue before running anything exclusive. SELECT ... FROM pg_locks WHERE NOT granted shows who is ahead of you.
10. Do the same lock discipline for the next thing, because CREATE INDEX CONCURRENTLY and DROP COLUMN are in the same category.

Failure stories worth testing

Hold a long transaction, then run the migration with and without lock_timeout

Open a SELECT in one session, run the migration in another, and measure how long the statements behind it take to complete. Then repeat with lock_timeout set. The difference between an unbounded stall and a bounded one is the entire fix, and it takes two minutes to see.

Start twenty migration runners against one database

Run the same migration script from twenty concurrent sessions. Watch the lock queue fill and watch how long the whole thing takes. This reproduces the startup-migration convoy with no deployment involved.

Add a column with a volatile default to a large table

Compare DEFAULT now() against DEFAULT clock_timestamp() on a table with real volume. On current PostgreSQL, the stable default uses the fast path while the volatile default rewrites existing rows. Inspect relation size, duration, WAL generation, and lock time instead of assuming every function call has the same volatility.

Leave a session idle in transaction, then run a migration

The migration waits for a transaction that is not doing any work at all. This is the forty-minute incident in its purest form, and it is invisible unless something is monitoring for idle transactions.

Validate a check constraint on a large table while writing to it

Time the VALIDATE CONSTRAINT and confirm writes continue. The contrast with ADD CONSTRAINT without NOT VALID is the reason the staged pattern exists, and it is a large enough difference to justify the extra steps.

Common mistakes

Mistake	What actually happens	Better decision
Believing the statement duration is the risk	Five milliseconds of work behind an unbounded lock wait	Bound the wait with lock_timeout
Assuming a queued writer does not block readers	New queries queue behind it, because the lock queue is ordered	Retry in gaps instead of waiting in the queue
Running migrations from application startup	One DDL attempt per pod, each queueing behind the others	A single migration job, run before the rollout
Not serialising concurrent migration runners	Two deploys can apply incompatible DDL in either order	pg_advisory_lock around migrations
Setting lock_timeout to 0	Waits forever, which is the default behaviour	A few seconds, then retry
Retrying without jitter	Every runner collides with the same long transaction	Random backoff between attempts
Adding a volatile default	Full table rewrite holding ACCESS EXCLUSIVE	A constant default, or the staged pattern
Adding NOT NULL with no default	Fails, or forces a rewrite	Nullable add, backfill, then set NOT NULL
Using ADD CONSTRAINT without NOT VALID	Full scan under a write lock	NOT VALID then VALIDATE separately
Ignoring idle in transaction sessions	A crashed process holds locks indefinitely	Alert on age, terminate automatically
Running an exclusive migration during peak traffic	The queue is continuous, so there is no gap to land in	Migrate in the quiet window, still with a timeout
Assuming a successful migration was applied once	Concurrent runners applied it in an unknown order	Advisory lock plus a migration history table

The complete story in one minute

Adding a nullable column with no default is genuinely metadata-only and genuinely fast, and that has never been the problem. The problem is the lock. The engine needs ACCESS EXCLUSIVE to change a table definition, that lock conflicts with everything including the ACCESS SHARE a plain read takes, and it must be granted in queue order. Which means the migration queues behind whatever is currently running, and every query that arrives after it queues behind the migration rather than overtaking it. So a statement that does four milliseconds of work can block a table for the length of the longest running transaction, and under continuous traffic there is no gap, so it waits for the worst one.

The fix is not to make the migration faster. It is to make the wait bounded. SET lock_timeout turns an unbounded wait into a failure after a few seconds, and a retry loop with jitter means the migration lands in one of the short windows where no lock is held. The jitter is what stops every runner colliding with the same transaction at the same moment. The same discipline applies to every statement needing an exclusive lock, and where a statement is a genuine full table rewrite, such as an added column with a volatile default, no amount of retrying helps and the answer is to decompose it into a nullable add, a batched backfill, a NOT VALID check, and a validation that allows ordinary reads and writes to continue.

The variant that turns a four-line change into a forty-minute outage is running migrations from application startup, because twenty pods rolling out means twenty concurrent attempts at the same DDL, each queueing behind the others while the pod holding the lock also gates the rollout. Take migrations out of the startup path, run them as a single deployment job before any new version serves traffic, and serialise them with an advisory lock so concurrent runners cannot apply DDL in an order nobody chose. And keep an eye on idle transactions, because an idle in transaction session left behind by a crashed process is the reason a five millisecond statement waited forty minutes.

Technical references

• PostgreSQL ALTER TABLE
• PostgreSQL table-level lock modes
• PostgreSQL client connection defaults, including lock_timeout
• PostgreSQL function volatility categories
• PostgreSQL constraint validation
• Npgsql EF Core migrations guidance