Zero Downtime Postgres Migrations: Which of 21 Statements Block Reads and Writes

Zero-downtime Postgres migrations come down to one fact: most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, so the outage usually comes from the queue behind the lock, not from the change. That holds even for statements that finish in a millisecond. In our synthetic test on PostgreSQL 18.6, a metadata-only ADD COLUMN waited 4,503 ms behind an idle transaction the test held open for 5 seconds, and all 10 reader connections queued behind the ALTER, not behind the transaction, until the commit released them.

Keplaris, a New York product engineering studio, ran 21 migration statements against a seeded 2,000,000-row table in one Docker container on an Apple M1 Max laptop, three times, with readers and writers running, and recorded which lock each one took, what it blocked and for how long. The matrix, the lock-queue timeline and the failure cases are below, and the script and unedited output are at the end. Schema changes on live SaaS databases are part of our API and SaaS development work.

This post is about schema changes on a live database. Major-version upgrades, host moves via logical replication, and managed-service upgrades (RDS, Aurora) are a different job and are out of scope. Changing the tenancy model is a project, which we cover in re-architecting a SaaS after product-market fit; this post is about each ALTER TABLE inside it. If your tenants live in separate schemas, the migration runs once per schema, so each lock below is paid once per schema; we compare tenancy layouts in multi-tenant SaaS architecture.

Zero downtime Postgres migrations: the short answer

To change a Postgres schema without downtime, cap how long each statement may wait for its lock with lock_timeout, use the CONCURRENTLY and NOT VALID forms, and ship app-visible changes as expand and contract. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. No form of ALTER TABLE runs without a lock: the safe forms take a weaker lock or hold it briefly, and lock_timeout stops a blocked one from waiting for long. The statement itself is often instant. The damage comes when it has to wait for that lock: while it waits, every query that touches the table waits behind it. In our run, 12 of the 17 ALTER TABLE statements took ACCESS EXCLUSIVE; the exceptions were the two foreign key adds and the three VALIDATE CONSTRAINT steps.

The rules we would ship with:

1. Set lock_timeout on the migration and retry on SQLSTATE 55P03. A migration that gives up after 500 ms and tries again is better than one that queues your app for as long as an idle transaction stays open.
2. Use the CONCURRENTLY and NOT VALID forms for indexes and constraints. They are the closest Postgres gets to non-blocking DDL, but they still take locks, so run each one behind lock_timeout and check what it leaves behind.
3. Add columns with no default or a non-volatile default. A volatile default such as clock_timestamp() rewrites the table under ACCESS EXCLUSIVE.
4. Change anything your app code can see with expand and contract: new column, dual write, backfill, switch reads, drop the old one, each as its own deploy.
5. Backfill in batches, one short transaction per batch, keyed by id range.

Why an instant ALTER TABLE can take your app down: the lock queue

A schema change that needs ACCESS EXCLUSIVE has to wait for every transaction already holding any lock on the table. While it waits, it holds a place in the lock queue, and later requests for even the weakest lock line up behind it, not beside the transaction it is waiting for. The stall lasts as long as the blocking transaction stays open: our test held that transaction open for 5 seconds, so the ALTER waited 4,503 ms.

The PostgreSQL explicit locking chapter says that ACCESS EXCLUSIVE "Conflicts with locks of all modes", and that, so long as no deadlock is detected, a transaction seeking a lock "will wait indefinitely for conflicting locks to be released." It doesn't describe how later requests line up behind a waiting one, so we measured it.

The idle transaction did nothing alarming on its own. It ran BEGIN; SELECT count(*) FROM orders WHERE id < 10; and then sat idle, holding the weakest lock there is. With only that transaction open, over 1,780 reads the slowest took 14 ms. Then the ALTER arrived, asked for ACCESS EXCLUSIVE, and could not get it. ACCESS SHARE doesn't conflict with ACCESS SHARE, but the readers' requests now had to queue behind the ALTER's pending one, so every read on orders stopped until the idle transaction committed at the 5 second mark.

That idle transaction is ordinary: a report query in a BEGIN block, a debugging session in psql, an ORM transaction waiting on an HTTP call. Without a lock_timeout, any of them turns a 1 ms ALTER into an outage that lasts as long as the transaction stays open.

Postgres migration locks: which statements block reads and writes

Of the 21 statements we ran on PostgreSQL 18.6, 12 blocked reads and 16 blocked at least some writes while they held their locks. Five blocked neither: CREATE INDEX CONCURRENTLY, the three VALIDATE CONSTRAINT steps and the batched UPDATE. Two rewrote the table: ADD COLUMN ... DEFAULT clock_timestamp() and int to bigint. For the rest, how long they blocked depended on how long they held the lock and whether anything was in front of them.

"Blocks reads" and "Blocks writes" come from the probe pass, where each statement ran in a transaction that kept its locks for 2 seconds. "Statement time" and "Slowest read / write" come from the timing pass, which ran each statement in autocommit with readers and writers running and nothing else in the way. pg_locks shows ACCESS EXCLUSIVE as AccessExclusiveLock, and the column below uses pg_locks names.

Measured on 2,000,000 seeded rows, PostgreSQL 18.6 in Docker on one Apple M1 Max laptop, three runs on 2026-11-02. "No query overlapped" means the statement finished before any reader or writer query arrived. Timings on your table, disk and load will differ.

Three things stand out:

• "Instant" doesn't mean "safe". Nine statements held ACCESS EXCLUSIVE for 4 ms or less, and each of them blocked readers and writers for about the 2 seconds its transaction stayed open in the probe pass. Behind an idle transaction held open for 5 seconds, that became the 4,503 ms stall above.
• Two rewrites held ACCESS EXCLUSIVE for more than a second on their own. DEFAULT clock_timestamp() and int to bigint both changed the table's filenode and kept readers and writers waiting between 1,325 and 1,533 ms on 2,000,000 rows across the runs. The varchar widening and the varchar to text change did not rewrite.
• The safe forms trade a long lock for a short one plus a scan. NOT VALID, VALIDATE CONSTRAINT and CONCURRENTLY still take locks; they move the slow part under a lock that lets reads and writes through.

We ran 21 migration statements against a busy 2-million-row table on PostgreSQL 18.6

Each of the 21 statements ran twice on fresh copies of a seeded 2,000,000-row table, with 10 readers, two writers, a customers updater and a pg_locks observer running, and repeated the whole test three times.

Method. This is a synthetic test on one local Docker container, not production, client or Keplaris product data. The script seeds a customers table with 10,000 rows and an orders table with 2,000,000 rows using setseed(0.20261102) and generate_series, plants one duplicate external_ref pair for the failing unique index, and runs VACUUM (ANALYZE). We picked 2,000,000 rows because a rewrite then takes more than a second on a laptop while the seed still builds in under 5 seconds. Every pass runs on a fresh copy of that database (CREATE DATABASE ... TEMPLATE), so no experiment depends on the one before. While each statement runs, 10 reader connections each select one order by random id and pause 20 ms, two writer connections alternate an INSERT and an UPDATE with a 20 ms pause, one connection updates a random customer and pauses 50 ms, and an observer polls pg_locks every 50 ms. A query counts as blocked when it took longer than 100 ms and the observer saw its pid with granted = false during it, so a query slowed by CPU contention isn't counted as a lock wait. None was slow without waiting. A changed pg_relation_filenode('orders') marks a rewrite.

Each statement ran twice, in separate passes. The probe pass wrapped it as BEGIN; SET LOCAL lock_timeout = '500ms'; <statement>; SELECT pg_sleep(2); COMMIT;, which holds the statement's locks long enough that "does it block reads" has a yes or no answer even for a 1 ms statement. The timing pass ran it in autocommit to measure how long it actually held things up. CREATE INDEX CONCURRENTLY can't run inside a transaction block, so both of its passes ran it bare.

The lock queue, measured

For the pile-up tests, an idle transaction began at +0 ms and committed at +5,000 ms, and ALTER TABLE orders ADD COLUMN flag int was sent at +500 ms:

Both writers queued in every test with an ALTER. Ten is the ceiling here because each reader connection waits for its answer before sending the next query: every reader connection in the test was stuck. An app with a connection pool in front would see its pool fill with waiting connections; we didn't run one.

Traced example: the queue in pg_locks

This is the observer's log for that ALTER with lock_timeout off, from the published run (times are after the idle transaction's BEGIN):

    +   42 ms  pid 1612 long tx   orders        AccessShareLock     granted=true  blocked_by=[] idle in transaction
    +  544 ms  pid 1608 writer    orders        RowExclusiveLock    granted=false blocked_by=[1613] active
    +  544 ms  pid 1603 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1613 migration orders        AccessExclusiveLock granted=false blocked_by=[1612] active

That is 4 of its 14 lines; the full list is in the output at the end. Pid 1612 is the idle transaction, holding AccessShareLock and showing idle in transaction. Pid 1613 is the ALTER, waiting for AccessExclusiveLock, blocked by 1612. The readers (pids 1598 to 1607) and writers (1608 and 1609) wait for their own ordinary locks, and pg_blocking_pids names 1613, the ALTER, not the idle transaction, as what blocks them. The idle transaction committed at +5,006 ms, the ALTER finished at +5,011 ms, and the queue drained.

What this test does not show

• It ran on one machine inside Docker Desktop's VM, with no replicas, so it says nothing about replication lag during a rewrite.
• We didn't run PgBouncer, a managed service (RDS, Aurora, Cloud SQL) or an ORM migration runner.
• Timings on 2,000,000 rows don't scale linearly to your table, your disk or your hardware. We tested one table size.
• The synthetic load is steady: about 400 reads a second from 10 connections (1,780 reads in the 4.5-second baseline window), plus two writers and one customers updater. Production traffic arrives in bursts and from more connections.
• We didn't run pg_repack, RENAME COLUMN or DROP COLUMN.
• We didn't isolate why each rewrite took the time it did; the numbers are what we measured, not a cost model.

Set lock_timeout and retry before any ALTER TABLE

Set lock_timeout on every migration so a blocked ALTER TABLE gives up instead of queuing your app, and retry on SQLSTATE 55P03 with backoff. The default, 0, disables the timeout, so the ALTER waits indefinitely. In our PostgreSQL 18.6 test, behind an idle transaction held open for 5 seconds, 500 ms cut the slowest read from 4,497 ms to 506 ms, and the retry succeeded on attempt 4.

With lock_timeout at 500 ms, the metadata-only ADD COLUMN waiting behind that idle transaction failed with SQLSTATE 55P03 ("canceling statement due to lock timeout") after 511 ms, and the column was absent afterwards, so the failed attempt changed nothing.

The PostgreSQL client configuration docs define lock_timeout as a limit that will "Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock", and add that "The time limit applies separately to each lock acquisition attempt." Its default, 0, disables it. Three more lines from that page shape how to use it:

• "if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first."
• "Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions." The page says the same about statement_timeout and transaction_timeout.
• transaction_timeout will "Terminate any session that spans longer than the specified amount of time in a transaction." It was added in PostgreSQL 17.

The retry loop we used, from the test script:

// The retry helper the post recommends: lock_timeout per attempt, back off on 55P03 only.
export async function withLockRetry(client, sql, { lockTimeout = "500ms", attempts = 10, backoffMs = 500, maxBackoffMs = 4000, onAttempt } = {}) {
  let wait = backoffMs;
  for (let attempt = 1; ; attempt++) {
    const t = performance.now();
    try {
      await client.query("BEGIN");
      await client.query(`SET LOCAL lock_timeout = '${lockTimeout}'`);
      await client.query(sql);
      await client.query("COMMIT");
      await onAttempt?.({ attempt, ok: true, ms: performance.now() - t });
      return attempt;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      await onAttempt?.({ attempt, ok: false, ms: performance.now() - t, err, nextWait: wait });
      if (err.code !== "55P03" || attempt >= attempts) throw err;
      await sleep(wait);
      wait = Math.min(wait * 2, maxBackoffMs);
    }
  }
}

On the run it printed four attempts: three 55P03 failures after 511, 512 and 514 ms, with waits of 500, 1,000 and 2,000 ms between them, and success on attempt 4, 5,049 ms after the first try, because the idle transaction had committed in between. Across all four attempts, readers were blocked 30 times out of 1,403 reads, and no read took longer than 508 ms.

Where to put the setting:

• On the connection that runs the DDL, inside the migration's transaction. SET LOCAL lasts until that transaction ends, so it can't leak into other work.
• Not as a session SET through a pool. In transaction pooling, "a server connection is assigned to a client only during a transaction," per PgBouncer's features page, so SET LOCAL inside the migration transaction stays with the DDL. A session-level SET can land on a different server connection, and the same page lists SET/RESET as "Never" supported in that mode. The session SET that CONCURRENTLY needs is the case that breaks, so run those migrations on a direct connection. We didn't run PgBouncer for this post.
• For CONCURRENTLY, use a session SET and RESET it afterwards, because those statements can't run in a transaction block and SET LOCAL needs one.

The other end of the queue is the idle transaction. When the idle session had transaction_timeout = '3s', the server ended it at about 3 seconds with FATAL 25P04 terminating connection due to transaction timeout, and the waiting ALTER went through after 2,502 ms. That timeout belongs on application roles or sessions, where it cuts off a forgotten transaction, not on the migration. idle_in_transaction_session_timeout ends a session that sits idle inside an open transaction for too long; we didn't test it.

How to add a column, a default or NOT NULL without downtime

Add the column with no default or a non-volatile default, and make it NOT NULL with a constraint you validate separately. The CHECK route does not avoid ACCESS EXCLUSIVE; it moves the scan out from under it.

Defaults: constant and now() are metadata, clock_timestamp() rewrites

The PostgreSQL 11 release notes (2018-10-18) added ADD COLUMN with a non-null default without a rewrite: "This is enabled when the default value is a constant." The PostgreSQL 18 ALTER TABLE docs say that with a non-volatile DEFAULT the value is "stored in the table's metadata", and that "Adding a column with a volatile DEFAULT (e.g., clock_timestamp()) ... will cause the entire table and its indexes to be rewritten."

In our runs, DEFAULT 'us' and DEFAULT now() left the table's filenode unchanged and finished in 1 to 3 ms. DEFAULT clock_timestamp() changed the filenode, the server's debug output included rewriting table "orders", and it held ACCESS EXCLUSIVE for 1,472 ms in the published run while all 10 readers and both writers waited. One guide we read lists the volatile-default rewrite under "older Postgres versions". On 18.6 it still rewrote.

We didn't run the combined form, ADD COLUMN ... NOT NULL DEFAULT 'us', so the following is untested by us: the PostgreSQL 18 ALTER TABLE docs say that "When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default value is evaluated at the time of the statement and the result stored in the table's metadata," and their list of additions that rewrite the table does not include a NOT NULL constraint.

If you need a per-row value such as a creation timestamp, add the column nullable, set the value in the app for new rows, and backfill old rows in batches.

NOT NULL: three routes

The direct ALTER COLUMN status SET NOT NULL scanned the whole table under ACCESS EXCLUSIVE (the server reported verifying table "orders") and took 65 ms in the published run, 65 to 89 ms across runs. That is short on 2,000,000 rows, and it grows with the table.

The CHECK route takes four statements:

ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID;  -- ACCESS EXCLUSIVE, 2 ms
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;                                -- SHARE UPDATE EXCLUSIVE, 86 ms
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;                                    -- ACCESS EXCLUSIVE, 1 ms
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;                                    -- ACCESS EXCLUSIVE, 1 ms

The scan moved into VALIDATE CONSTRAINT, which blocked no reader or writer. The final SET NOT NULL still took ACCESS EXCLUSIVE, but skipped the scan, and the server said so: existing constraints on column "orders.status" are sufficient to prove that it does not contain nulls. The docs describe this: "if a valid CHECK constraint exists ... which proves no NULL can exist, then the table scan is skipped." The scan skip arrived in PostgreSQL 12, per its release notes.

PostgreSQL 18 shortens this to two statements. Its release notes say: "Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints".

ALTER TABLE orders ADD CONSTRAINT orders_status_not_null NOT NULL status NOT VALID;  -- ACCESS EXCLUSIVE, 3 ms
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;                      -- SHARE UPDATE EXCLUSIVE, 82 ms

After the VALIDATE, pg_attribute showed attnotnull = true for status and pg_constraint showed convalidated = true. On PostgreSQL 12 to 17, use the CHECK route. PostgreSQL 14 reaches end of life on November 12, 2026, per the versioning policy.

CREATE INDEX CONCURRENTLY, and the invalid index a failure leaves behind

A failed CREATE INDEX CONCURRENTLY doesn't roll back cleanly: it left an index marked INVALID, under the name you asked for, which a plain retry trips over and IF NOT EXISTS keeps. Build indexes concurrently, then check that they are valid.

A plain CREATE INDEX took ShareLock: readers were fine and writers waited up to 427 ms of the 440 ms build. The CREATE INDEX docs say the same: "Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index build is finished." CREATE INDEX CONCURRENTLY took ShareUpdateExclusiveLock, ran 667 ms, and blocked nobody. The docs add that it "cannot" run in a transaction block, unlike a regular CREATE INDEX.

It waits for transactions that wrote to the table

We ran the concurrent build behind two kinds of idle transaction:

The read-only idle transaction didn't stall the build, which matches the docs' wording: "Before each table scan, the index build must wait for existing transactions that have modified the table to terminate," and after the second scan it waits for transactions "that have a snapshot ... predating the second scan." Readers and writers kept going in all three cases.

What a failed build leaves

Three different failures in our runs left the same thing: an index with indisvalid = false and indisready = false.

• Cancelled by lock_timeout while waiting on the writing transaction.
• A duplicate in a unique build: ERROR 23505 could not create unique index "orders_external_ref_uidx" DETAIL: Key (external_ref)=(ref-0999999) is duplicated.
• Cancelled by statement_timeout = '300ms' in the middle of the build: ERROR 57014 canceling statement due to statement timeout.

The docs say such an index "will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead." The pg_index docs define indisready = false as "the index must be ignored by INSERT/UPDATE operations", and that matches what we saw: after the failed unique build, an INSERT with an existing external_ref value succeeded. The CREATE INDEX docs also warn that if a failure happens in the second scan, the invalid index "continues to enforce its uniqueness constraint afterwards". None of our failures happened that late, so we didn't see that case.

The retry is where a failed build hurts. After the lock_timeout cancel and the idle transaction's commit, the script tried what a migration runner might:

CREATE INDEX CONCURRENTLY orders_created_at_cidx ...                ERROR 42P07 relation "orders_created_at_cidx" already exists
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_created_at_cidx ...  OK, NOTICE relation "orders_created_at_cidx" already exists, skipping
                                                                    (index still valid=false)
DROP INDEX CONCURRENTLY orders_created_at_cidx                      OK
CREATE INDEX CONCURRENTLY orders_created_at_cidx ...                OK, valid=true

That table is a summary of the run's lines, which are in the output at the end. The IF NOT EXISTS form is the trap: the migration reports success and the index stays invalid. One guide we read says the concurrent build "can fail silently", then explains that the error leaves an INVALID index behind. That second part is the real risk: in our runs every failure raised an error, and the quiet part was the INVALID index that stayed, especially after an IF NOT EXISTS retry.

The docs give two recoveries: "drop the index and try again", or rebuild it with REINDEX INDEX CONCURRENTLY. We tried both. DROP INDEX CONCURRENTLY and a fresh build worked after the lock_timeout failure, and REINDEX INDEX CONCURRENTLY turned the statement_timeout failure into a valid index in 643 ms, with no reader or writer blocked.

Run this after every concurrent build, and in CI against staging:

SELECT indexrelid::regclass AS index, indrelid::regclass AS table_name, indisvalid, indisready
FROM pg_index
WHERE NOT indisvalid;

Foreign keys and CHECK constraints: add NOT VALID, then VALIDATE

Adding a foreign key locks the parent table too: in our test, updates to customers blocked while the constraint was added to orders. Add it NOT VALID, commit, then VALIDATE CONSTRAINT in a separate transaction.

The PostgreSQL 18 ALTER TABLE docs say "ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE lock on the referenced table, in addition to the lock on the table on which the constraint is declared," and that validation "acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.)" pg_locks showed those modes, alongside AccessShareLock on both tables:

• Validated ADD FOREIGN KEY: ShareRowExclusiveLock on both tables. Readers were fine. In the timing pass it ran 176 ms, writes to orders waited up to 163 ms, and updates to customers waited up to 132 ms.
• ADD FOREIGN KEY ... NOT VALID: the same ShareRowExclusiveLock on both tables, for 1 ms. One guide we read calls this step "only updates catalog". That's true of the work but not of the locks: in the probe pass, while its transaction stayed open, writes to both tables waited. Give it a lock_timeout and commit it straight away.
• VALIDATE CONSTRAINT: ShareUpdateExclusiveLock on orders and RowShareLock on customers. It ran 202 ms and blocked no read, write or customers update, in either pass.

There is no ADD FOREIGN KEY CONCURRENTLY: in the ALTER TABLE syntax, CONCURRENTLY appears only on DETACH PARTITION. NOT VALID plus VALIDATE CONSTRAINT is the route. The same split works for CHECK constraints, as the NOT NULL section shows; NOT VALID is "currently only allowed for foreign-key, CHECK, and not-null constraints."

Renames, type changes and drops: expand and contract

Expand and contract ships any change your app code can see as a sequence of deploys, not one migration: add the new shape, move data and readers to it, then remove the old one. It is also called parallel change: Danilo Sato's Parallel Change article on martinfowler.com (2014-05-13) says it is "also known as expand and contract" and names three phases, expand, migrate and contract. It is the same additive-first rule as an API, which we apply to API contracts in API versioning best practices: add, migrate readers, then remove.

The worked example is orders.amount_cents from int to bigint, because the direct ALTER COLUMN ... TYPE bigint rewrote the table and held ACCESS EXCLUSIVE for 1,342 to 1,464 ms on 2,000,000 rows in our runs.

Each row is its own deploy, and the app has to run correctly against the schema before and after each step. Step 2 matters more than it looks: during our backfill tests, the writers inserted 28 to 32 rows during the single-statement backfill and 212 to 219 during the batched one, across the three runs, after the backfill read its upper bound, and those rows were left NULL in the new column. A backfill covers the rows that existed when it started; dual writes cover the rest.

When two services share the schema, every change to that table is a coordinated release across both, the situation we describe in monolith vs microservices for startups.

A rename follows the same steps: add the new column, dual write, backfill, switch reads, drop the old one. RENAME COLUMN itself is a catalog change with "no effect on the stored data", but once it commits, app instances that still use the old name start failing. We didn't run it in this test.

How to backfill a large table without locking it

Backfill a large table in id-range batches, one commit each. On 2,000,000 rows, a single UPDATE backfill ran as one 5,040 ms transaction and writer updates waited up to 4,680 ms behind its row locks, while in 10,000-row batches the slowest writer update took 12 ms. Neither blocked reads or inserts.

Across the three runs, writer updates waited up to 4,471 to 5,020 ms behind the single UPDATE and up to 5 to 13 ms during the batches. Both passes left 0 rows with region still NULL below the starting maximum id.

Batching did not reduce bloat in these 5-second runs: both versions roughly doubled the table and left close to 2,000,000 dead tuples. We didn't check whether autovacuum ran during either pass. What batching changes is lock time and transaction length. We didn't test throttled batches with a VACUUM between them; on a real table, pause between batches and watch autovacuum and replica lag.

Getting the space back is a separate job. The PostgreSQL 18 routine vacuuming docs say the standard form of VACUUM, which is what autovacuum runs, "marks the space available for future reuse" but "will not return the space to the operating system", except when pages at the end of the table become entirely free. VACUUM FULL writes "a complete new version of the table file with no dead space", and it "requires an ACCESS EXCLUSIVE lock on the table it is working on", so reads and writes wait for the whole rebuild. pg_repack is an online option: it rebuilds the table and, per its docs, needs "one exclusive lock at the beginning as well as one exclusive lock at the end". We didn't run either.

The batch shape we used, keyed by id range with a fixed upper bound:

SELECT max(id) FROM orders;  -- read once, before the first batch: 2,000,044 in the published run
-- then, for lo = 1, 10001, 20001, ... up to that max, one autocommit statement per batch:
UPDATE orders SET region = 'us' WHERE id >= $1 AND id < $2 AND id <= $3;  -- $1 = lo, $2 = lo + 10000, $3 = the max

Keying by id range means the loop ends only when it has walked every id up to the bound. A loop of UPDATE ... WHERE region IS NULL LIMIT n with SKIP LOCKED can stop early when a batch comes back short because rows were locked, and leave rows unfilled; we didn't test that loop. Finish with a count of rows still NULL up to the bound; ours was 0.

Online schema migration tools for Postgres

Three kinds of tool help: linters that flag risky statements before they ship, runners that serve the old and new schema at once while a change rolls out, and pg_repack, which rebuilds a bloated table online after something like a big backfill. Each description below is from the tool's own repository or registry page, read on 2026-11-02. We didn't run any of them for this post.

Our judgment, from the tools' documented mechanisms and our matrix rather than from running them: use a linter when most of your risk is someone writing the blocking form of a statement, because the forms it flags are the rows at the top of our matrix, and it costs a CI step. Consider a dual-schema runner such as pgroll or Reshape when you ship app-visible changes often enough that hand-run expand and contract becomes the bottleneck, and accept views, triggers and one more piece of infrastructure in exchange. Neither replaces lock_timeout: a linter can't know an idle transaction will be open when the migration runs.

If you use an ORM's migration runner, check whether it wraps each migration file in a transaction, because CREATE INDEX CONCURRENTLY can't run inside one, and check how it lets you set lock_timeout on the connection it uses.

A pre-deploy checklist for Postgres migrations

Run these eight checks before any schema change ships to a busy Postgres database.

1. Look up each statement's lock in the matrix above or the ALTER TABLE docs. Anything that takes ACCESS EXCLUSIVE and scans or rewrites gets the safe form.
2. Put SET LOCAL lock_timeout in the migration transaction (we would start at 500ms), keep it below statement_timeout, and wrap the statement in a retry on 55P03 with backoff.
3. Prove there's no rewrite in staging: compare pg_relation_filenode('your_table') before and after.
4. Split constraints into NOT VALID and VALIDATE CONSTRAINT, in separate transactions.
5. Run index builds CONCURRENTLY, outside a transaction, and check indisvalid afterwards.
6. Ship app-visible changes as expand and contract, one deploy per step.
7. Backfill in id-range batches with a fixed upper bound and a final NULL count.
8. Before you start, look for old open transactions, and keep the lock-wait query below running in a second session while the migration runs.

Old open transactions, the ones that will make your migration queue:

SELECT pid, now() - xact_start AS xact_age, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

Who is waiting on whom, while the migration runs:

SELECT a.pid, a.state, l.mode, l.granted, pg_blocking_pids(a.pid) AS blocked_by,
       now() - a.xact_start AS xact_age, left(a.query, 60) AS query
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass
ORDER BY a.xact_start;

We ran both queries against the test container with an idle transaction, a waiting ALTER and a waiting SELECT; the second one returned the same chain as the traced example.

If you have a schema change to ship on a live SaaS database and want it planned as a sequence of safe deploys, that's part of what the Keplaris API and SaaS development team does.

The full test script and output

Save the script as lock-test.mjs in an empty directory, start a throwaway Postgres 18 container, install the one dependency and run it:

docker run -d --name kep-zdm-pg -e POSTGRES_PASSWORD=zdm-throwaway -p 54329:5432 postgres:18
npm install --save-exact pg@8.23.1
PGPASSWORD=zdm-throwaway node lock-test.mjs
docker rm -f kep-zdm-pg

It drops and creates databases named zdm_seed and zdm_exp, so point it only at a server you can throw away. A run takes about three minutes.

Run metadata: seed setseed(0.20261102), Node v24.20.0, pg (node-postgres) 8.23.1, PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2, aarch64) from the official postgres:18 image with digest postgres@sha256:3a4264520e121176619f54ff867d12774bc81e95e7b84db58d63bccdb6144b81, default server settings, Docker Desktop 4.90.0 with a VM of 10 CPUs and 8,319,504,384 bytes of memory, on an Apple M1 Max (10 cores, 64 GB RAM) running macOS 27.0.1. Run three times on 2026-11-02. Across the three runs, the seed fingerprint, every lock mode, every blocks yes or no, every rewrite yes or no, every SQLSTATE and error message, every index state and the retry's attempt number were identical; pids, timings, the number of rows the writers inserted and the order in which one lock list printed its two tables differed, and the timing ranges are the ones in the tables above. The three counted runs came after fixing script bugs in earlier attempts.

// lock-test.mjs: which Postgres migration statements block reads and writes on a live table.
// Synthetic, seeded data on a local server. Needs Node 24+, one npm install (pg) and a
// throwaway Postgres you can drop databases on. Run:
//   docker run -d --name kep-zdm-pg -e POSTGRES_PASSWORD=zdm-throwaway -p 54329:5432 postgres:18
//   npm install --save-exact pg@8.23.1
//   PGPASSWORD=zdm-throwaway node lock-test.mjs
// Optional env: PGHOST, PGPORT, ZDM_IMAGE_DIGEST and ZDM_DOCKER_VM (printed in the header),
// ZDM_JSON (path for a machine-readable copy of the results).
import pg from "pg";
import os from "node:os";
import { readFileSync, writeFileSync } from "node:fs";
import { performance } from "node:perf_hooks";

const SEED = 0.20261102; // setseed() for the data, and the seed of the JS id generator
const CUSTOMERS = 10_000;
const ORDERS = 2_000_000;
const READERS = 10; // connections, each: SELECT one order by id, then pause READ_GAP_MS
const WRITERS = 2; // connections, each: alternate INSERT and UPDATE, then pause WRITE_GAP_MS
const READ_GAP_MS = 20;
const WRITE_GAP_MS = 20;
const PARENT_GAP_MS = 50; // one connection updating customers
const OBSERVE_GAP_MS = 50; // pg_locks poll interval
const LONG_TX_MS = 5000; // the idle transaction commits this long after BEGIN
const DDL_DELAY_MS = 500; // the migration starts this long after the idle transaction's BEGIN
const HOLD_S = 2; // probe pass: keep the migration's locks this long before COMMIT
const BLOCKED_MS = 100; // blocked = slower than this AND seen waiting (granted = false) in pg_locks
const WARMUP_MS = 1000;
const CIC_TIMEOUT = "300ms"; // statement_timeout for the cancelled CREATE INDEX CONCURRENTLY (E9); the build takes longer

const pkg = JSON.parse(readFileSync(new URL("./node_modules/pg/package.json", import.meta.url), "utf8"));
const base = {
  host: process.env.PGHOST ?? "127.0.0.1",
  port: Number(process.env.PGPORT ?? 54329),
  user: process.env.PGUSER ?? "postgres",
  password: process.env.PGPASSWORD,
};
const sleep = (ms) => new Promise((r) => setTimeout(r, Math.max(0, ms)));
const ms = (x) => Math.round(x);
const results = {};

function rng(seed) {
  // mulberry32, so the ids each session touches are reproducible
  let a = Math.floor(seed * 2 ** 32) >>> 0;
  return () => {
    a = (a + 0x6d2b79f5) >>> 0;
    let t = a;
    t = Math.imul(t ^ (t >>> 15), t | 1);
    t ^= t + Math.imul(t ^ (t >>> 7), t | 61);
    return ((t ^ (t >>> 14)) >>> 0) / 4294967296;
  };
}

async function connect(database) {
  const c = new pg.Client({ ...base, database });
  c.notices = [];
  c.errors = []; // the server's own message arrives first, the socket close second
  c.on("notice", (n) => c.notices.push(n));
  c.on("error", (e) => c.errors.push(e));
  await c.connect();
  c.pid = (await c.query("SELECT pg_backend_pid() AS pid")).rows[0].pid;
  return c;
}

// ---------- seed (once per run) and a fresh copy per pass ----------

async function seed() {
  const admin = await connect("postgres");
  await dropDb(admin, "zdm_exp");
  if ((await admin.query("SELECT 1 FROM pg_database WHERE datname = 'zdm_seed'")).rowCount) {
    await admin.query("ALTER DATABASE zdm_seed WITH IS_TEMPLATE false ALLOW_CONNECTIONS true");
    await admin.query("DROP DATABASE zdm_seed WITH (FORCE)");
  }
  await admin.query("CREATE DATABASE zdm_seed");
  const db = await connect("zdm_seed");
  const t = performance.now();
  await db.query(`SELECT setseed(${SEED})`);
  await db.query(`
    CREATE TABLE customers (id int PRIMARY KEY, name text);
    INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, ${CUSTOMERS}) g;
    CREATE TABLE orders (
      id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      customer_id int,
      status text,
      amount_cents int,
      note varchar(50),
      external_ref text,
      created_at timestamptz
    );
    INSERT INTO orders (id, customer_id, status, amount_cents, note, external_ref, created_at)
    SELECT g,
           1 + floor(random() * ${CUSTOMERS})::int,
           (ARRAY['pending', 'paid', 'shipped', 'refunded'])[1 + floor(random() * 4)::int],
           floor(random() * 100000)::int,
           'note ' || g,
           'ref-' || lpad(g::text, 7, '0'),
           timestamptz '2026-01-01 00:00:00+00' + random() * interval '300 days'
    FROM generate_series(1, ${ORDERS}) g;
    UPDATE orders SET external_ref = 'ref-0999999' WHERE id = 1000000; -- the one planted duplicate
    SELECT setval(pg_get_serial_sequence('orders', 'id'), ${ORDERS});
    CREATE EXTENSION pgstattuple;
  `);
  await db.query("VACUUM (ANALYZE) customers, orders");
  const fp = (
    await db.query(`SELECT (SELECT count(*) FROM orders) AS orders, (SELECT count(*) FROM customers) AS customers,
      (SELECT sum(amount_cents) FROM orders) AS amount_sum,
      (SELECT string_agg(status || '=' || n, ' ' ORDER BY status) FROM (SELECT status, count(*) n FROM orders GROUP BY 1) s) AS statuses,
      (SELECT count(*) FROM (SELECT external_ref FROM orders GROUP BY 1 HAVING count(*) > 1) d) AS dup_refs,
      pg_size_pretty(pg_total_relation_size('orders')) AS orders_size`)
  ).rows[0];
  const seedMs = performance.now() - t;
  await db.end();
  await admin.query("ALTER DATABASE zdm_seed WITH IS_TEMPLATE true ALLOW_CONNECTIONS false");
  await admin.end();
  return { ...fp, seedMs };
}

async function dropDb(admin, name) {
  await admin.query(`DROP DATABASE IF EXISTS ${name} WITH (FORCE)`);
}

async function freshDb(fn) {
  const admin = await connect("postgres");
  await dropDb(admin, "zdm_exp");
  await admin.query("CREATE DATABASE zdm_exp TEMPLATE zdm_seed STRATEGY FILE_COPY");
  await admin.query("CHECKPOINT");
  try {
    return await fn("zdm_exp");
  } finally {
    await dropDb(admin, "zdm_exp");
    await admin.end();
  }
}

// ---------- the load: readers, writers, parent writer, observer ----------

async function startLoad(db, tag) {
  const t0 = performance.now();
  const now = () => performance.now() - t0;
  const L = { now, samples: [], polls: [], first: new Map(), waits: new Map(), roles: new Map(), running: true, loops: [], clients: [] };
  L.setRole = (pid, role) => L.roles.set(pid, role);
  const r = rng(SEED + tag.length / 1000);
  let ins = 0;
  const loop = async (c, role, gap, next) => {
    while (L.running) {
      const { sql, params, kind } = next();
      const start = now();
      let err = null;
      try {
        await c.query(sql, params);
      } catch (e) {
        err = e;
      }
      const end = now();
      L.samples.push({ role, kind, pid: c.pid, start, end, lat: end - start, err: err && `${err.code} ${err.message}` });
      await sleep(gap);
    }
  };
  for (let i = 0; i < READERS; i++) {
    const c = await connect(db);
    L.clients.push(c);
    L.setRole(c.pid, "reader");
    L.loops.push(loop(c, "reader", READ_GAP_MS, () => ({ kind: "select", sql: "SELECT * FROM orders WHERE id = $1", params: [1 + Math.floor(r() * ORDERS)] })));
  }
  for (let i = 0; i < WRITERS; i++) {
    const c = await connect(db);
    L.clients.push(c);
    L.setRole(c.pid, "writer");
    let flip = false;
    L.loops.push(
      loop(c, "writer", WRITE_GAP_MS, () => {
        flip = !flip;
        if (flip) {
          ins++;
          return {
            kind: "insert",
            sql: "INSERT INTO orders (customer_id, status, amount_cents, note, external_ref, created_at) VALUES ($1, 'pending', $2, 'new', $3, now())",
            params: [1 + Math.floor(r() * CUSTOMERS), Math.floor(r() * 100000), `ins-${tag}-${i}-${ins}`],
          };
        }
        return { kind: "update", sql: "UPDATE orders SET note = $2 WHERE id = $1", params: [1 + Math.floor(r() * ORDERS), `upd ${ins}`] };
      })
    );
  }
  {
    const c = await connect(db);
    L.clients.push(c);
    L.setRole(c.pid, "parent");
    L.loops.push(loop(c, "parent", PARENT_GAP_MS, () => ({ kind: "update", sql: "UPDATE customers SET name = $2 WHERE id = $1", params: [1 + Math.floor(r() * CUSTOMERS), `customer v${ins}`] })));
  }
  const obs = await connect(db);
  L.clients.push(obs);
  L.obs = obs;
  L.loops.push(
    (async () => {
      while (L.running) {
        const s = now();
        const { rows } = await obs.query(`
          SELECT l.pid, l.relation::regclass::text AS rel, l.locktype, l.mode, l.granted,
                 pg_blocking_pids(l.pid) AS blockers, a.state
          FROM pg_locks l JOIN pg_stat_activity a USING (pid)
          WHERE l.pid <> pg_backend_pid()
            AND (l.relation IN ('orders'::regclass, 'customers'::regclass)
                 OR (l.locktype IN ('virtualxid', 'transactionid') AND NOT l.granted))`);
        const t = (s + now()) / 2;
        const waiting = new Set();
        for (const x of rows) {
          const key = `${x.pid}|${x.rel}|${x.locktype}|${x.mode}|${x.granted}`;
          if (!L.first.has(key)) L.first.set(key, { t, ...x });
          if (!x.granted) {
            waiting.add(x.pid);
            if (!L.waits.has(x.pid)) L.waits.set(x.pid, []);
            L.waits.get(x.pid).push(t);
          }
        }
        L.polls.push({ t, waiting: [...waiting] });
        await sleep(OBSERVE_GAP_MS - (now() - s));
      }
    })()
  );
  L.stop = async () => {
    L.running = false;
    await Promise.all(L.loops);
    for (const c of L.clients) await c.end().catch(() => {});
  };
  await sleep(WARMUP_MS);
  return L;
}

function pct(a, p) {
  if (!a.length) return 0;
  const s = [...a].sort((x, y) => x - y);
  return s[Math.min(s.length - 1, Math.floor((p / 100) * s.length))];
}

function summarize(L, a, b) {
  const seen = (pid, s, e) => (L.waits.get(pid) ?? []).some((t) => t >= s && t <= e);
  const out = {};
  for (const [name, f] of [
    ["readers", (q) => q.role === "reader"],
    ["writers", (q) => q.role === "writer"],
    ["inserts", (q) => q.role === "writer" && q.kind === "insert"],
    ["updates", (q) => q.role === "writer" && q.kind === "update"],
    ["parent", (q) => q.role === "parent"],
  ]) {
    const qs = L.samples.filter((q) => f(q) && q.start <= b && q.end >= a);
    const s = { n: qs.length, blocked: 0, blockedMax: 0, slowNotWaiting: 0, max: 0, p50: pct(qs.map((q) => q.lat), 50), errors: 0 };
    for (const q of qs) {
      s.max = Math.max(s.max, q.lat);
      if (q.err) s.errors++;
      if (q.lat > BLOCKED_MS) {
        if (seen(q.pid, q.start, q.end)) {
          s.blocked++;
          s.blockedMax = Math.max(s.blockedMax, q.lat);
        } else s.slowNotWaiting++;
      }
    }
    out[name] = s;
  }
  let peak = { readers: 0, writers: 0, parent: 0, all: 0 };
  for (const p of L.polls.filter((p) => p.t >= a && p.t <= b)) {
    const c = { readers: 0, writers: 0, parent: 0, all: p.waiting.length };
    for (const pid of p.waiting) {
      const role = L.roles.get(pid);
      if (role === "reader") c.readers++;
      if (role === "writer") c.writers++;
      if (role === "parent") c.parent++;
    }
    for (const k of Object.keys(peak)) peak[k] = Math.max(peak[k], c[k]);
  }
  out.peakWaiting = peak;
  return out;
}

function heldBy(L, pid) {
  const m = {};
  for (const x of L.first.values()) {
    if (x.pid !== pid || !x.rel || x.locktype !== "relation") continue; // table locks only, not extend or tuple locks
    const k = x.granted ? x.rel : `${x.rel} (waiting)`;
    (m[k] ??= new Set()).add(x.mode);
  }
  return Object.entries(m)
    .map(([rel, modes]) => `${rel} ${[...modes].join("+")}`)
    .join(", ");
}

function waitsOf(L, pid) {
  return [...L.first.values()]
    .filter((x) => x.pid === pid && !x.granted)
    .map((x) => `${x.locktype}${x.rel ? " " + x.rel : ""} ${x.mode} blocked_by [${x.blockers.map((b) => `${b} ${L.roles.get(b) ?? "?"}`).join(", ")}]`)
    .join("; ");
}

const fmtRole = (s, name) => !s.n ? `${name}: no query overlapped` : `${name} blocked ${s.blocked}/${s.n}${s.blocked ? ` (max ${ms(s.blockedMax)} ms)` : ""}, max ${ms(s.max)} ms${s.slowNotWaiting ? `, slow but not waiting ${s.slowNotWaiting}` : ""}${s.errors ? `, errors ${s.errors}` : ""}`;
const fmtLoad = (S, parent) => [fmtRole(S.readers, "readers"), fmtRole(S.writers, "writers"), ...(parent ? [fmtRole(S.parent, "customers updates")] : [])].join(" | ");
const errText = (e) => `ERROR ${e.code ?? ""} ${e.message}${e.detail ? ` DETAIL: ${e.detail}` : ""}`.replace(/\s+/g, " ");
const debugNotes = (c) => [...new Set(c.notices.filter((n) => n.severity === "DEBUG").map((n) => n.message))];
const filenode = async (c) => (await c.query("SELECT pg_relation_filenode('orders') AS f")).rows[0].f;
const indexes = async (c) =>
  (await c.query("SELECT indexrelid::regclass::text AS idx, indisvalid, indisready FROM pg_index WHERE indrelid = 'orders'::regclass ORDER BY 1")).rows
    .map((r) => `${r.idx} valid=${r.indisvalid} ready=${r.indisready}`)
    .join(", ");

// The retry helper the post recommends: lock_timeout per attempt, back off on 55P03 only.
export async function withLockRetry(client, sql, { lockTimeout = "500ms", attempts = 10, backoffMs = 500, maxBackoffMs = 4000, onAttempt } = {}) {
  let wait = backoffMs;
  for (let attempt = 1; ; attempt++) {
    const t = performance.now();
    try {
      await client.query("BEGIN");
      await client.query(`SET LOCAL lock_timeout = '${lockTimeout}'`);
      await client.query(sql);
      await client.query("COMMIT");
      await onAttempt?.({ attempt, ok: true, ms: performance.now() - t });
      return attempt;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      await onAttempt?.({ attempt, ok: false, ms: performance.now() - t, err, nextWait: wait });
      if (err.code !== "55P03" || attempt >= attempts) throw err;
      await sleep(wait);
      wait = Math.min(wait * 2, maxBackoffMs);
    }
  }
}

async function openLongTx(db, L, { write = false, txTimeout = null } = {}) {
  const c = await connect(db);
  L.setRole(c.pid, "long tx");
  if (txTimeout) await c.query(`SET transaction_timeout = '${txTimeout}'`);
  const tBegin = L.now();
  await c.query("BEGIN");
  await c.query(write ? "UPDATE orders SET note = 'long tx' WHERE id = 1" : "SELECT count(*) FROM orders WHERE id < 10");
  const done = (async () => {
    await sleep(tBegin + LONG_TX_MS - L.now());
    const r = { pid: c.pid, tBegin };
    try {
      await c.query("COMMIT");
      r.commitAt = L.now();
    } catch (e) {
      r.commitError = e.message;
    }
    r.fatal = c.errors.map((e) => `${e.severity ?? ""} ${e.code ?? ""} ${e.message}`.trim()).join(", then ");
    await c.end().catch(() => {});
    return r;
  })();
  return { c, pid: c.pid, tBegin, done };
}

// ---------- pile-up: one idle transaction, then an instant ALTER ----------

const PILE_SQL = "ALTER TABLE orders ADD COLUMN flag int";

async function pileup(id, label, { lockTimeout, txTimeout, retry, ddl = true } = {}) {
  return freshDb(async (db) => {
    const L = await startLoad(db, id);
    const long = await openLongTx(db, L, { txTimeout });
    await sleep(long.tBegin + DDL_DELAY_MS - L.now());
    const r = { id, label, lockTimeout: lockTimeout ?? "0 (off)" };
    let a = L.now();
    let b;
    let mig;
    if (ddl) {
      mig = await connect(db);
      L.setRole(mig.pid, "migration");
      a = L.now();
      if (retry) {
        r.attempts = [];
        try {
          r.succeededOn = await withLockRetry(mig, PILE_SQL, {
            lockTimeout,
            onAttempt: async (x) => {
              const row = { attempt: x.attempt, ok: x.ok, startedAt: L.now() - x.ms - long.tBegin, ms: x.ms, err: x.err && errText(x.err), nextWait: x.ok ? null : x.nextWait };
              if (x.attempt === 1) {
                r.firstEnd = L.now();
                r.columnAfterFirst = (await L.obs.query("SELECT count(*)::int AS n FROM information_schema.columns WHERE table_name = 'orders' AND column_name = 'flag'")).rows[0].n;
              }
              r.attempts.push(row);
            },
          });
        } catch (e) {
          r.error = errText(e);
        }
      } else {
        try {
          await mig.query("BEGIN");
          await mig.query(`SET LOCAL lock_timeout = '${lockTimeout ?? "0"}'`);
          await mig.query(PILE_SQL);
          await mig.query("COMMIT");
          r.outcome = "OK";
        } catch (e) {
          await mig.query("ROLLBACK").catch(() => {});
          r.outcome = errText(e);
        }
      }
      b = L.now();
      r.ddlMs = b - a;
      r.ddlStart = a - long.tBegin;
      r.ddlEnd = b - long.tBegin;
      r.held = heldBy(L, mig.pid);
      r.waitedOn = waitsOf(L, mig.pid);
    } else {
      b = long.tBegin + LONG_TX_MS;
      await sleep(b - L.now());
    }
    r.long = await long.done;
    await sleep(300);
    r.column = (await L.obs.query("SELECT count(*)::int AS n FROM information_schema.columns WHERE table_name = 'orders' AND column_name = 'flag'")).rows[0].n;
    await L.stop();
    if (mig) await mig.end();
    r.window = summarize(L, a, b);
    if (retry && r.firstEnd) r.firstWindow = summarize(L, a, r.firstEnd);
    r.trace = [...L.first.values()]
      .filter((x) => x.t >= long.tBegin && x.t <= b + 200 && (L.roles.get(x.pid) === "long tx" || L.roles.get(x.pid) === "migration" || !x.granted))
      .sort((x, y) => x.t - y.t)
      .map((x) => ({ ...x, t: x.t - long.tBegin, role: L.roles.get(x.pid) ?? "?" }));
    r.longPid = long.pid;
    r.migPid = mig?.pid;
    return r;
  });
}

function printPileup(r, showTrace) {
  console.log(`\n${r.id} ${r.label}`);
  const W = r.window;
  if (r.migPid === undefined) {
    console.log(`  window +${DDL_DELAY_MS} to +${LONG_TX_MS} ms after the idle transaction's BEGIN, no DDL`);
  } else {
    console.log(`  sql: ${PILE_SQL}, lock_timeout ${r.lockTimeout}`);
    if (r.attempts) {
      for (const x of r.attempts)
        console.log(`  attempt ${x.attempt} at +${ms(x.startedAt)} ms: ${x.ok ? "OK" : x.err} after ${ms(x.ms)} ms${x.ok ? "" : `, next wait ${x.nextWait} ms`}`);
      console.log(`  column present after attempt 1: ${r.columnAfterFirst ? "yes" : "no"} | succeeded on attempt ${r.succeededOn ?? "none"}${r.error ? ` (${r.error})` : ""}`);
      console.log(`  attempt 1 window: readers peak waiting ${r.firstWindow.peakWaiting.readers}/${READERS} | ${fmtLoad(r.firstWindow)}`);
    } else {
      console.log(`  outcome: ${r.outcome}`);
    }
    console.log(`  ALTER sent at +${ms(r.ddlStart)} ms, finished at +${ms(r.ddlEnd)} ms, elapsed ${ms(r.ddlMs)} ms | held: ${r.held || "none seen"} | waited on: ${r.waitedOn || "none seen"}`);
  }
  const lg = r.long;
  console.log(`  idle transaction pid ${lg.pid}: ${lg.commitAt !== undefined ? `COMMIT at +${ms(lg.commitAt - lg.tBegin)} ms` : `COMMIT failed: ${lg.commitError}`}${lg.fatal ? ` | session error: ${lg.fatal}` : ""}`);
  console.log(`  peak waiting pids: readers ${W.peakWaiting.readers}/${READERS}, writers ${W.peakWaiting.writers}/${WRITERS}, all ${W.peakWaiting.all}`);
  console.log(`  ${fmtLoad(W)} | reader p50 ${ms(W.readers.p50)} ms`);
  console.log(`  column flag exists at the end: ${r.column ? "yes" : "no"}`);
  if (showTrace) {
    console.log(`  pg_locks first sightings (time after the idle transaction's BEGIN; granted rows shown for the idle transaction and the ALTER only):`);
    for (const x of r.trace)
      console.log(`    +${String(ms(x.t)).padStart(5)} ms  pid ${x.pid} ${x.role.padEnd(9)} ${(x.rel ?? x.locktype).padEnd(13)} ${x.mode.padEnd(19)} granted=${String(x.granted).padEnd(5)} blocked_by=[${x.blockers.join(",")}] ${x.state}`);
  }
}

// ---------- statements: a probe pass (locks held 2 s) and a timing pass (autocommit) ----------

const STATEMENTS = [
  { id: "E3", label: "ADD COLUMN, no default", steps: ["ALTER TABLE orders ADD COLUMN flag int"] },
  { id: "E3a", label: "ADD COLUMN DEFAULT 'us'", steps: ["ALTER TABLE orders ADD COLUMN region text DEFAULT 'us'"] },
  { id: "E3b", label: "ADD COLUMN DEFAULT now()", steps: ["ALTER TABLE orders ADD COLUMN seen_at timestamptz DEFAULT now()"] },
  { id: "E4", label: "ADD COLUMN DEFAULT clock_timestamp()", steps: ["ALTER TABLE orders ADD COLUMN created_v timestamptz DEFAULT clock_timestamp()"] },
  { id: "E5a", label: "ALTER COLUMN TYPE bigint (from int)", steps: ["ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint"] },
  { id: "E5b", label: "ALTER COLUMN TYPE varchar(100) (from 50)", steps: ["ALTER TABLE orders ALTER COLUMN note TYPE varchar(100)"] },
  { id: "E5c", label: "ALTER COLUMN TYPE text (from varchar)", steps: ["ALTER TABLE orders ALTER COLUMN note TYPE text"] },
  { id: "E6", label: "CREATE INDEX", steps: ["CREATE INDEX orders_created_at_idx ON orders (created_at)"] },
  { id: "E6b", label: "CREATE INDEX CONCURRENTLY", steps: [{ sql: "CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at)", noTx: true }] },
  { id: "E10a", label: "ADD FOREIGN KEY (validated)", parent: true, steps: ["ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id)"] },
  {
    id: "E10b",
    label: "ADD FOREIGN KEY NOT VALID, then VALIDATE",
    parent: true,
    steps: ["ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID", "ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk"],
  },
  { id: "E11a", label: "SET NOT NULL (direct)", steps: ["ALTER TABLE orders ALTER COLUMN status SET NOT NULL"] },
  {
    id: "E11b",
    label: "CHECK NOT VALID, VALIDATE, SET NOT NULL, DROP CHECK",
    steps: [
      "ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID",
      "ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn",
      "ALTER TABLE orders ALTER COLUMN status SET NOT NULL",
      "ALTER TABLE orders DROP CONSTRAINT orders_status_nn",
    ],
  },
  {
    id: "E11c",
    label: "Postgres 18: NOT NULL NOT VALID, then VALIDATE",
    steps: ["ALTER TABLE orders ADD CONSTRAINT orders_status_not_null NOT NULL status NOT VALID", "ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null"],
    check: `SELECT a.attnotnull, c.conname, c.convalidated FROM pg_attribute a LEFT JOIN pg_constraint c
            ON c.conrelid = a.attrelid AND c.contype = 'n' AND a.attnum = ANY (c.conkey)
            WHERE a.attrelid = 'orders'::regclass AND a.attname = 'status'`,
  },
];

async function statementPass(def, mode) {
  return freshDb(async (db) => {
    const L = await startLoad(db, `${def.id}-${mode}`);
    const steps = [];
    for (const st of def.steps) {
      const sql = typeof st === "string" ? st : st.sql;
      const noTx = typeof st !== "string" && st.noTx;
      const mig = await connect(db);
      L.setRole(mig.pid, "migration");
      await mig.query("SET client_min_messages = debug1");
      const before = await filenode(L.obs);
      const a = L.now();
      let outcome = "OK";
      try {
        if (mode === "probe" && !noTx) {
          await mig.query("BEGIN");
          await mig.query("SET LOCAL lock_timeout = '500ms'");
          await mig.query(sql);
          await mig.query(`SELECT pg_sleep(${HOLD_S})`);
          await mig.query("COMMIT");
        } else {
          if (mode === "probe") await mig.query("SET lock_timeout = '500ms'");
          await mig.query(sql);
        }
      } catch (e) {
        await mig.query("ROLLBACK").catch(() => {});
        outcome = errText(e);
      }
      const b = L.now();
      await sleep(OBSERVE_GAP_MS * 2);
      const after = await filenode(L.obs);
      steps.push({ sql, outcome, a, b, ms: b - a, rewrite: before !== after, pid: mig.pid, notes: debugNotes(mig) });
      await mig.end();
    }
    const check = def.check ? (await L.obs.query(def.check)).rows : null;
    await L.stop();
    for (const s of steps) {
      s.held = heldBy(L, s.pid);
      s.S = summarize(L, s.a, s.b); // after stop, so every query that overlapped the step has finished
    }
    return { steps, check };
  });
}

async function statementExperiment(def) {
  const probe = await statementPass(def, "probe");
  const timing = await statementPass(def, "timing");
  const r = { id: def.id, label: def.label, parent: !!def.parent, probe, timing };
  console.log(`\n${def.id} ${def.label}`);
  def.steps.forEach((st, i) => {
    const p = probe.steps[i];
    const t = timing.steps[i];
    console.log(`  sql: ${p.sql}`);
    console.log(`    probe (${typeof st === "string" ? `in a transaction, lock_timeout 500ms, locks held ${HOLD_S} s` : "no transaction block allowed, lock_timeout 500ms"}): ${p.outcome}, ${ms(p.ms)} ms`);
    console.log(`      locks held by the migration: ${p.held || "none seen"}`);
    console.log(`      ${fmtLoad(p.S, def.parent)}`);
    console.log(`    timing (autocommit): ${t.outcome}, ${ms(t.ms)} ms, rewrite ${t.rewrite ? "yes" : "no"}`);
    console.log(`      ${fmtLoad(t.S, def.parent)}`);
    if (t.notes.length) console.log(`      server DEBUG: ${t.notes.join(" | ")}`);
  });
  if (timing.check) console.log(`  after: ${timing.check.map((x) => `attnotnull=${x.attnotnull} ${x.conname} convalidated=${x.convalidated}`).join(", ")}`);
  return r;
}

// ---------- CREATE INDEX CONCURRENTLY behind an idle transaction, and its failures ----------

async function cicBehindLongTx(id, label, { write, lockTimeout }) {
  return freshDb(async (db) => {
    const L = await startLoad(db, id);
    const long = await openLongTx(db, L, { write });
    await sleep(long.tBegin + DDL_DELAY_MS - L.now());
    const mig = await connect(db);
    L.setRole(mig.pid, "migration");
    if (lockTimeout) await mig.query(`SET lock_timeout = '${lockTimeout}'`);
    const sql = "CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at)";
    const a = L.now();
    let outcome = "OK";
    try {
      await mig.query(sql);
    } catch (e) {
      outcome = errText(e);
    }
    const b = L.now();
    const lg = await long.done;
    const idx = await indexes(L.obs);
    const after = [];
    if (outcome !== "OK") {
      // the idle transaction has committed; try the retries a migration runner would try
      await mig.query("RESET lock_timeout");
      for (const q of [sql, sql.replace("CONCURRENTLY", "CONCURRENTLY IF NOT EXISTS"), "DROP INDEX CONCURRENTLY orders_created_at_cidx", sql]) {
        mig.notices.length = 0;
        let o = "OK";
        try {
          await mig.query(q);
        } catch (e) {
          o = errText(e);
        }
        const n = mig.notices.map((x) => `${x.severity} ${x.message}`).join(" | ");
        after.push(`${q}: ${o}${n ? ` (${n})` : ""} | then: ${await indexes(L.obs)}`);
      }
    }
    await L.stop();
    const S = summarize(L, a, b);
    console.log(`\n${id} ${label}`);
    console.log(`  sql: ${sql}${lockTimeout ? `, lock_timeout ${lockTimeout} (session SET)` : ", lock_timeout 0 (off)"}`);
    console.log(`  idle transaction: ${write ? "BEGIN; UPDATE orders SET note = 'long tx' WHERE id = 1" : "BEGIN; SELECT count(*) FROM orders WHERE id < 10"}, COMMIT at +${ms(lg.commitAt - lg.tBegin)} ms`);
    console.log(`  outcome: ${outcome} | sent at +${ms(a - long.tBegin)} ms, elapsed ${ms(b - a)} ms`);
    console.log(`  locks held by the build: ${heldBy(L, mig.pid) || "none seen"} | waited on: ${waitsOf(L, mig.pid) || "none seen"}`);
    console.log(`  ${fmtLoad(S)}`);
    console.log(`  indexes on orders after: ${idx}`);
    for (const x of after) console.log(`  retry after COMMIT: ${x}`);
    await mig.end();
    return { id, label, outcome, ms: b - a, held: heldBy(L, mig.pid), waited: waitsOf(L, mig.pid), S, idx };
  });
}

async function cicUniqueFailure() {
  return freshDb(async (db) => {
    const L = await startLoad(db, "E8");
    const mig = await connect(db);
    L.setRole(mig.pid, "migration");
    const sql = "CREATE UNIQUE INDEX CONCURRENTLY orders_external_ref_uidx ON orders (external_ref)";
    const a = L.now();
    let outcome = "OK";
    try {
      await mig.query(sql);
    } catch (e) {
      outcome = errText(e);
    }
    const b = L.now();
    const S = summarize(L, a, b);
    const idxAfter = await indexes(mig);
    const tryInsert = async (ref) => {
      try {
        await mig.query("INSERT INTO orders (customer_id, status, amount_cents, note, external_ref, created_at) VALUES (1, 'pending', 1, 'probe', $1, now())", [ref]);
        return "INSERT OK";
      } catch (e) {
        return errText(e);
      }
    };
    const insNew = await tryInsert("ref-new-0000001");
    const insDup = await tryInsert("ref-0000005");
    const used = (await mig.query("EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE external_ref = 'ref-0000005'")).rows.map((r) => r["QUERY PLAN"]).join(" / ");
    const d = L.now();
    await mig.query("DROP INDEX CONCURRENTLY orders_external_ref_uidx");
    const dropMs = L.now() - d;
    const idxDropped = await indexes(mig);
    await L.stop();
    console.log(`\nE8 CREATE UNIQUE INDEX CONCURRENTLY on a column with one duplicate pair`);
    console.log(`  sql: ${sql}`);
    console.log(`  outcome: ${outcome} | elapsed ${ms(b - a)} ms`);
    console.log(`  ${fmtLoad(S)}`);
    console.log(`  indexes on orders after the failure: ${idxAfter}`);
    console.log(`  INSERT with a new external_ref: ${insNew}`);
    console.log(`  INSERT with an existing external_ref (ref-0000005): ${insDup}`);
    console.log(`  plan for WHERE external_ref = 'ref-0000005': ${used}`);
    console.log(`  DROP INDEX CONCURRENTLY orders_external_ref_uidx: OK, ${ms(dropMs)} ms | indexes after: ${idxDropped}`);
    await mig.end();
    return { outcome, ms: b - a, S, idxAfter, insNew, insDup, used, dropMs, idxDropped };
  });
}

async function cicTimeout() {
  return freshDb(async (db) => {
    const L = await startLoad(db, "E9");
    const mig = await connect(db);
    L.setRole(mig.pid, "migration");
    const sql = "CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at)";
    await mig.query(`SET statement_timeout = '${CIC_TIMEOUT}'`);
    const a = L.now();
    let outcome = "OK";
    try {
      await mig.query(sql);
    } catch (e) {
      outcome = errText(e);
    }
    const b = L.now();
    await mig.query("RESET statement_timeout");
    const idxAfter = await indexes(mig);
    const c = L.now();
    let reindex = "OK";
    try {
      await mig.query("REINDEX INDEX CONCURRENTLY orders_created_at_cidx");
    } catch (e) {
      reindex = errText(e);
    }
    const d = L.now();
    const idxFixed = await indexes(mig);
    await L.stop();
    const S = summarize(L, c, d);
    console.log(`\nE9 CREATE INDEX CONCURRENTLY cancelled by statement_timeout ${CIC_TIMEOUT}, then REINDEX INDEX CONCURRENTLY`);
    console.log(`  outcome: ${outcome} | elapsed ${ms(b - a)} ms`);
    console.log(`  indexes on orders after the cancel: ${idxAfter}`);
    console.log(`  REINDEX INDEX CONCURRENTLY orders_created_at_cidx: ${reindex}, ${ms(d - c)} ms | ${fmtLoad(S)}`);
    console.log(`  indexes on orders after REINDEX: ${idxFixed}`);
    await mig.end();
    return { outcome, ms: b - a, idxAfter, reindex, reindexMs: d - c, idxFixed, S };
  });
}

// ---------- backfill: one UPDATE vs 10,000-row batches ----------

async function backfill(id, batched) {
  return freshDb(async (db) => {
    const setup = await connect(db);
    await setup.query("ALTER TABLE orders ADD COLUMN region text");
    await setup.end();
    const L = await startLoad(db, id);
    const mig = await connect(db);
    L.setRole(mig.pid, "migration");
    const startMax = Number((await mig.query("SELECT max(id) AS m FROM orders")).rows[0].m);
    const sizeBefore = (await mig.query("SELECT pg_size_pretty(pg_relation_size('orders')) AS s")).rows[0].s;
    const a = L.now();
    let batches = 0;
    let maxBatch = 0;
    if (batched) {
      for (let lo = 1; lo <= startMax; lo += 10_000) {
        const t = performance.now();
        await mig.query("UPDATE orders SET region = 'us' WHERE id >= $1 AND id < $2 AND id <= $3", [lo, lo + 10_000, startMax]);
        maxBatch = Math.max(maxBatch, performance.now() - t);
        batches++;
      }
    } else {
      await mig.query("UPDATE orders SET region = 'us'");
    }
    const b = L.now();
    await sleep(OBSERVE_GAP_MS * 2);
    const held = heldBy(L, mig.pid);
    await L.stop();
    const S = summarize(L, a, b);
    // pgstattuple first: the count(*) scans below prune dead tuples as they read each page
    const st = (await mig.query("SELECT dead_tuple_count, pg_size_pretty(pg_relation_size('orders')) AS size FROM pgstattuple('orders')")).rows[0];
    const dead = st.dead_tuple_count;
    const left = (await mig.query("SELECT count(*)::int AS n FROM orders WHERE region IS NULL AND id <= $1", [startMax])).rows[0].n;
    const newer = (await mig.query("SELECT count(*)::int AS n FROM orders WHERE region IS NULL AND id > $1", [startMax])).rows[0].n;
    const longest = batched ? maxBatch : b - a;
    console.log(`\n${id} backfill region = 'us' on ${startMax} rows, ${batched ? "id-range batches of 10,000, one commit each" : "one UPDATE, one transaction"}`);
    console.log(`  total ${ms(b - a)} ms${batched ? `, ${batches} batches, slowest batch ${ms(maxBatch)} ms` : ""}, longest transaction ${ms(longest)} ms | locks held: ${held || "none seen"}`);
    console.log(`  ${fmtRole(S.readers, "readers")} | ${fmtRole(S.updates, "writer UPDATEs")} | ${fmtRole(S.inserts, "writer INSERTs")}`);
    console.log(`  rows with region NULL and id <= ${startMax}: ${left} | rows inserted during the backfill, region NULL: ${newer} | dead tuples right after (pgstattuple): ${dead} | table size ${sizeBefore} before, ${st.size} after`);
    await mig.end();
    return { id, batched, startMax, totalMs: b - a, batches, maxBatch, longest, held, S, left, newer, dead, sizeBefore, sizeAfter: st.size };
  });
}

// ---------- run ----------

const run = async () => {
  const admin = await connect("postgres");
  const version = (await admin.query("SELECT version() AS v")).rows[0].v;
  const settings = (
    await admin.query(`SELECT name, setting, unit FROM pg_settings WHERE name IN
      ('lock_timeout','statement_timeout','transaction_timeout','idle_in_transaction_session_timeout','log_lock_waits','deadlock_timeout','shared_buffers','max_wal_size','autovacuum')
      ORDER BY name`)
  ).rows.map((r) => `${r.name}=${r.setting}${r.unit ? " " + r.unit : ""}`);
  await admin.end();
  console.log("lock-test (synthetic data, one local server, not production)");
  console.log(`run date: ${new Date().toISOString().slice(0, 10)}`);
  console.log(`server: ${version}`);
  console.log(`image: ${process.env.ZDM_IMAGE_DIGEST ?? "(not given)"} | docker vm: ${process.env.ZDM_DOCKER_VM ?? "(not given)"}`);
  console.log(`node: ${process.version} | pg: ${pkg.version} | machine: ${os.cpus()[0].model} ${process.platform} ${process.arch}`);
  console.log(`settings: ${settings.join(", ")}`);
  const s = await seed();
  console.log(`seed: setseed(${SEED}) | orders ${s.orders} (${s.orders_size}), customers ${s.customers} | amount_cents sum ${s.amount_sum} | ${s.statuses} | duplicate external_ref values ${s.dup_refs} | built in ${ms(s.seedMs)} ms`);
  console.log(`load: ${READERS} readers (SELECT by id, ${READ_GAP_MS} ms pause), ${WRITERS} writers (INSERT and UPDATE alternating, ${WRITE_GAP_MS} ms pause), 1 customers updater (${PARENT_GAP_MS} ms pause), pg_locks polled every ${OBSERVE_GAP_MS} ms`);
  console.log(`blocked = a query slower than ${BLOCKED_MS} ms whose pid pg_locks showed with granted = false during it`);
  console.log(`every pass runs on a fresh copy of the seed database (CREATE DATABASE ... TEMPLATE zdm_seed)`);

  console.log(`\n== pile-up: an idle transaction opens at +0 ms and commits at +${LONG_TX_MS} ms; the ALTER is sent at +${DDL_DELAY_MS} ms`);
  results.E0 = await pileup("E0", "baseline: idle transaction only, no DDL", { ddl: false });
  printPileup(results.E0);
  results.E1 = await pileup("E1", "ADD COLUMN behind the idle transaction, lock_timeout off", {});
  printPileup(results.E1, true);
  results.E1b = await pileup("E1b", "same, lock_timeout 2s", { lockTimeout: "2s" });
  printPileup(results.E1b);
  results.E2 = await pileup("E2", "same, lock_timeout 500ms with retry (10 attempts, backoff 500 ms doubling, cap 4 s)", { lockTimeout: "500ms", retry: true });
  printPileup(results.E2);
  results.E2b = await pileup("E2b", "same, lock_timeout off, idle session has transaction_timeout 3s", { txTimeout: "3s" });
  printPileup(results.E2b);

  console.log(`\n== statements: probe pass (locks held ${HOLD_S} s, decides blocks yes/no) and timing pass (autocommit, measured waits), no idle transaction`);
  for (const def of STATEMENTS) results[def.id] = await statementExperiment(def);

  console.log(`\n== CREATE INDEX CONCURRENTLY behind an idle transaction, and two ways it fails`);
  results.E7a = await cicBehindLongTx("E7a", "CIC behind an idle read-only transaction", { write: false });
  results.E7b = await cicBehindLongTx("E7b", "CIC behind an idle transaction that updated one row", { write: true });
  results.E7c = await cicBehindLongTx("E7c", "same as E7b, lock_timeout 500ms", { write: true, lockTimeout: "500ms" });
  results.E8 = await cicUniqueFailure();
  results.E9 = await cicTimeout();

  console.log(`\n== backfill`);
  results.E12a = await backfill("E12a", false);
  results.E12b = await backfill("E12b", true);

  console.log(`\n== matrix (probe pass decides blocks reads/writes; timing pass gives rewrite and the measured waits)`);
  console.log(["id", "statement", "locks held (pg_locks)", "blocks reads", "blocks writes", "rewrite", "elapsed ms", "reader max ms", "writer max ms"].join(" | "));
  for (const def of STATEMENTS) {
    const r = results[def.id];
    r.probe.steps.forEach((p, i) => {
      const t = r.timing.steps[i];
      const w = def.parent ? `${p.S.writers.blocked ? "yes" : "no"} (customers ${p.S.parent.blocked ? "yes" : "no"})` : p.S.writers.blocked ? "yes" : "no";
      console.log([def.id + (def.steps.length > 1 ? `.${i + 1}` : ""), p.sql.replace("ALTER TABLE orders ", ""), p.held, p.S.readers.blocked ? "yes" : "no", w, t.rewrite ? "yes" : "no", ms(t.ms), ms(t.S.readers.max), ms(t.S.writers.max) + (def.parent ? ` (customers ${ms(t.S.parent.max)})` : "")].join(" | "));
    });
  }
  for (const k of ["E12a", "E12b"]) {
    const r = results[k];
    console.log([k, r.batched ? "UPDATE in 10,000-row batches" : "UPDATE all rows (one statement)", r.held, r.S.readers.blocked ? "yes" : "no", `updates ${r.S.updates.blocked ? "yes" : "no"}, inserts ${r.S.inserts.blocked ? "yes" : "no"}`, "n/a", ms(r.totalMs), ms(r.S.readers.max), `updates ${ms(r.S.updates.max)}, inserts ${ms(r.S.inserts.max)}`].join(" | "));
  }

  const admin2 = await connect("postgres");
  await admin2.query("ALTER DATABASE zdm_seed WITH IS_TEMPLATE false ALLOW_CONNECTIONS true");
  await admin2.query("DROP DATABASE zdm_seed WITH (FORCE)");
  await admin2.end();
  if (process.env.ZDM_JSON) writeFileSync(process.env.ZDM_JSON, JSON.stringify(results, (k, v) => (v instanceof Set ? [...v] : v), 1));
};

run().catch((e) => {
  console.error(e);
  process.exit(1);
});

The unedited output of node lock-test.mjs from 2026-11-02 (run 1 of 3):

lock-test (synthetic data, one local server, not production)
run date: 2026-11-02
server: PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
image: postgres@sha256:3a4264520e121176619f54ff867d12774bc81e95e7b84db58d63bccdb6144b81 | docker vm: 10 CPUs 8319504384 bytes
node: v24.20.0 | pg: 8.23.1 | machine: Apple M1 Max darwin arm64
settings: autovacuum=on, deadlock_timeout=1000 ms, idle_in_transaction_session_timeout=0 ms, lock_timeout=0 ms, log_lock_waits=off, max_wal_size=1024 MB, shared_buffers=16384 8kB, statement_timeout=0 ms, transaction_timeout=0 ms
seed: setseed(0.20261102) | orders 2000000 (214 MB), customers 10000 | amount_cents sum 99963613385 | paid=498955 pending=500292 refunded=500705 shipped=500048 | duplicate external_ref values 1 | built in 4405 ms
load: 10 readers (SELECT by id, 20 ms pause), 2 writers (INSERT and UPDATE alternating, 20 ms pause), 1 customers updater (50 ms pause), pg_locks polled every 50 ms
blocked = a query slower than 100 ms whose pid pg_locks showed with granted = false during it
every pass runs on a fresh copy of the seed database (CREATE DATABASE ... TEMPLATE zdm_seed)

== pile-up: an idle transaction opens at +0 ms and commits at +5000 ms; the ALTER is sent at +500 ms

E0 baseline: idle transaction only, no DDL
  window +500 to +5000 ms after the idle transaction's BEGIN, no DDL
  idle transaction pid 1595: COMMIT at +5002 ms
  peak waiting pids: readers 0/10, writers 0/2, all 0
  readers blocked 0/1780, max 14 ms | writers blocked 0/356, max 14 ms | reader p50 5 ms
  column flag exists at the end: no

E1 ADD COLUMN behind the idle transaction, lock_timeout off
  sql: ALTER TABLE orders ADD COLUMN flag int, lock_timeout 0 (off)
  outcome: OK
  ALTER sent at +508 ms, finished at +5011 ms, elapsed 4503 ms | held: orders (waiting) AccessExclusiveLock | waited on: relation orders AccessExclusiveLock blocked_by [1612 long tx]
  idle transaction pid 1612: COMMIT at +5006 ms
  peak waiting pids: readers 10/10, writers 2/2, all 13
  readers blocked 10/10 (max 4497 ms), max 4497 ms | writers blocked 2/2 (max 4494 ms), max 4494 ms | reader p50 4493 ms
  column flag exists at the end: yes
  pg_locks first sightings (time after the idle transaction's BEGIN; granted rows shown for the idle transaction and the ALTER only):
    +   42 ms  pid 1612 long tx   orders        AccessShareLock     granted=true  blocked_by=[] idle in transaction
    +  544 ms  pid 1608 writer    orders        RowExclusiveLock    granted=false blocked_by=[1613] active
    +  544 ms  pid 1603 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1600 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1604 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1598 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1602 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1606 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1609 writer    orders        RowExclusiveLock    granted=false blocked_by=[1613] active
    +  544 ms  pid 1599 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1601 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1613 migration orders        AccessExclusiveLock granted=false blocked_by=[1612] active
    +  544 ms  pid 1605 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active
    +  544 ms  pid 1607 reader    orders        AccessShareLock     granted=false blocked_by=[1613] active

E1b same, lock_timeout 2s
  sql: ALTER TABLE orders ADD COLUMN flag int, lock_timeout 2s
  outcome: ERROR 55P03 canceling statement due to lock timeout
  ALTER sent at +515 ms, finished at +2527 ms, elapsed 2012 ms | held: orders (waiting) AccessExclusiveLock | waited on: relation orders AccessExclusiveLock blocked_by [1629 long tx]
  idle transaction pid 1629: COMMIT at +5001 ms
  peak waiting pids: readers 10/10, writers 2/2, all 13
  readers blocked 10/10 (max 2000 ms), max 2000 ms | writers blocked 2/2 (max 2000 ms), max 2000 ms | reader p50 2000 ms
  column flag exists at the end: no

E2 same, lock_timeout 500ms with retry (10 attempts, backoff 500 ms doubling, cap 4 s)
  sql: ALTER TABLE orders ADD COLUMN flag int, lock_timeout 500ms
  attempt 1 at +513 ms: ERROR 55P03 canceling statement due to lock timeout after 511 ms, next wait 500 ms
  attempt 2 at +1531 ms: ERROR 55P03 canceling statement due to lock timeout after 512 ms, next wait 1000 ms
  attempt 3 at +3043 ms: ERROR 55P03 canceling statement due to lock timeout after 514 ms, next wait 2000 ms
  attempt 4 at +5558 ms: OK after 4 ms
  column present after attempt 1: no | succeeded on attempt 4
  attempt 1 window: readers peak waiting 10/10 | readers blocked 10/10 (max 506 ms), max 506 ms | writers blocked 2/2 (max 506 ms), max 506 ms
  ALTER sent at +513 ms, finished at +5561 ms, elapsed 5049 ms | held: orders (waiting) AccessExclusiveLock | waited on: relation orders AccessExclusiveLock blocked_by [1647 long tx]
  idle transaction pid 1647: COMMIT at +5003 ms
  peak waiting pids: readers 10/10, writers 2/2, all 13
  readers blocked 30/1403 (max 508 ms), max 508 ms | writers blocked 6/280 (max 507 ms), max 507 ms | reader p50 5 ms
  column flag exists at the end: yes

E2b same, lock_timeout off, idle session has transaction_timeout 3s
  sql: ALTER TABLE orders ADD COLUMN flag int, lock_timeout 0 (off)
  outcome: OK
  ALTER sent at +512 ms, finished at +3014 ms, elapsed 2502 ms | held: orders (waiting) AccessExclusiveLock, orders AccessExclusiveLock | waited on: relation orders AccessExclusiveLock blocked_by [1664 long tx]
  idle transaction pid 1664: COMMIT failed: Client has encountered a connection error and is not queryable | session error: FATAL 25P04 terminating connection due to transaction timeout, then Connection terminated unexpectedly
  peak waiting pids: readers 10/10, writers 2/2, all 13
  readers blocked 10/10 (max 2500 ms), max 2500 ms | writers blocked 2/2 (max 2500 ms), max 2500 ms | reader p50 2500 ms
  column flag exists at the end: yes

== statements: probe pass (locks held 2 s, decides blocks yes/no) and timing pass (autocommit, measured waits), no idle transaction

E3 ADD COLUMN, no default
  sql: ALTER TABLE orders ADD COLUMN flag int
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2010 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 1998 ms), max 1998 ms | writers blocked 2/2 (max 1998 ms), max 1998 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped

E3a ADD COLUMN DEFAULT 'us'
  sql: ALTER TABLE orders ADD COLUMN region text DEFAULT 'us'
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2024 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 2022 ms), max 2022 ms | writers blocked 2/2 (max 2025 ms), max 2025 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped

E3b ADD COLUMN DEFAULT now()
  sql: ALTER TABLE orders ADD COLUMN seen_at timestamptz DEFAULT now()
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2011 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 2008 ms), max 2008 ms | writers blocked 2/2 (max 2007 ms), max 2007 ms
    timing (autocommit): OK, 3 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped

E4 ADD COLUMN DEFAULT clock_timestamp()
  sql: ALTER TABLE orders ADD COLUMN created_v timestamptz DEFAULT clock_timestamp()
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 3503 ms
      locks held by the migration: orders AccessExclusiveLock+ShareLock
      readers blocked 10/10 (max 3477 ms), max 3477 ms | writers blocked 2/3 (max 3477 ms), max 3477 ms
    timing (autocommit): OK, 1472 ms, rewrite yes
      readers blocked 10/10 (max 1450 ms), max 1450 ms | writers blocked 2/2 (max 1454 ms), max 1454 ms
      server DEBUG: building index "pg_toast_16729_index" on table "pg_toast_16729" serially | index "pg_toast_16729_index" can safely use deduplication | rewriting table "orders" | building index "orders_pkey" on table "orders" with request for 1 parallel workers | index "orders_pkey" can safely use deduplication

E5a ALTER COLUMN TYPE bigint (from int)
  sql: ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 3465 ms
      locks held by the migration: orders AccessExclusiveLock+ShareLock
      readers blocked 10/10 (max 3441 ms), max 3441 ms | writers blocked 2/2 (max 3440 ms), max 3440 ms
    timing (autocommit): OK, 1403 ms, rewrite yes
      readers blocked 10/10 (max 1384 ms), max 1384 ms | writers blocked 2/2 (max 1382 ms), max 1382 ms
      server DEBUG: building index "pg_toast_16743_index" on table "pg_toast_16743" serially | index "pg_toast_16743_index" can safely use deduplication | rewriting table "orders" | building index "orders_pkey" on table "orders" with request for 1 parallel workers | index "orders_pkey" can safely use deduplication

E5b ALTER COLUMN TYPE varchar(100) (from 50)
  sql: ALTER TABLE orders ALTER COLUMN note TYPE varchar(100)
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2013 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 2000 ms), max 2000 ms | writers blocked 2/2 (max 2000 ms), max 2000 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped

E5c ALTER COLUMN TYPE text (from varchar)
  sql: ALTER TABLE orders ALTER COLUMN note TYPE text
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2017 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 1999 ms), max 1999 ms | writers blocked 2/2 (max 1999 ms), max 1999 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped

E6 CREATE INDEX
  sql: CREATE INDEX orders_created_at_idx ON orders (created_at)
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2445 ms
      locks held by the migration: orders ShareLock
      readers blocked 0/1031, max 10 ms | writers blocked 2/2 (max 2433 ms), max 2433 ms
    timing (autocommit): OK, 440 ms, rewrite no
      readers blocked 0/190, max 3 ms | writers blocked 2/2 (max 427 ms), max 427 ms
      server DEBUG: building index "orders_created_at_idx" on table "orders" with request for 1 parallel workers | index "orders_created_at_idx" can safely use deduplication

E6b CREATE INDEX CONCURRENTLY
  sql: CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at)
    probe (no transaction block allowed, lock_timeout 500ms): OK, 685 ms
      locks held by the migration: orders ShareUpdateExclusiveLock
      readers blocked 0/302, max 17 ms | writers blocked 0/60, max 10 ms
    timing (autocommit): OK, 667 ms, rewrite no
      readers blocked 0/300, max 2 ms | writers blocked 0/60, max 4 ms
      server DEBUG: building index "orders_created_at_cidx" on table "orders" with request for 1 parallel workers | index "orders_created_at_cidx" can safely use deduplication

E10a ADD FOREIGN KEY (validated)
  sql: ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id)
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2170 ms
      locks held by the migration: orders ShareRowExclusiveLock+AccessShareLock, customers ShareRowExclusiveLock+RowShareLock+AccessShareLock
      readers blocked 0/870, max 9 ms | writers blocked 2/2 (max 2155 ms), max 2155 ms | customers updates blocked 1/1 (max 2123 ms), max 2123 ms
    timing (autocommit): OK, 176 ms, rewrite no
      readers blocked 0/80, max 2 ms | writers blocked 2/2 (max 163 ms), max 163 ms | customers updates blocked 1/1 (max 132 ms), max 132 ms
      server DEBUG: validating foreign key constraint "orders_customer_fk"

E10b ADD FOREIGN KEY NOT VALID, then VALIDATE
  sql: ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2012 ms
      locks held by the migration: orders ShareRowExclusiveLock+AccessShareLock, customers ShareRowExclusiveLock+AccessShareLock
      readers blocked 0/790, max 7 ms | writers blocked 2/2 (max 2002 ms), max 2002 ms | customers updates blocked 1/1 (max 1973 ms), max 1973 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped | customers updates: no query overlapped
  sql: ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2173 ms
      locks held by the migration: orders ShareUpdateExclusiveLock+AccessShareLock, customers RowShareLock+AccessShareLock
      readers blocked 0/891, max 13 ms | writers blocked 0/178, max 13 ms | customers updates blocked 0/41, max 8 ms
    timing (autocommit): OK, 202 ms, rewrite no
      readers blocked 0/90, max 8 ms | writers blocked 0/18, max 9 ms | customers updates blocked 0/4, max 4 ms
      server DEBUG: validating foreign key constraint "orders_customer_fk"

E11a SET NOT NULL (direct)
  sql: ALTER TABLE orders ALTER COLUMN status SET NOT NULL
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2074 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 2061 ms), max 2061 ms | writers blocked 2/2 (max 2060 ms), max 2060 ms
    timing (autocommit): OK, 65 ms, rewrite no
      readers blocked 0/10, max 50 ms | writers blocked 0/2, max 49 ms
      server DEBUG: verifying table "orders"

E11b CHECK NOT VALID, VALIDATE, SET NOT NULL, DROP CHECK
  sql: ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2010 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 1993 ms), max 1993 ms | writers blocked 2/2 (max 1993 ms), max 1993 ms
    timing (autocommit): OK, 2 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped
  sql: ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2080 ms
      locks held by the migration: orders ShareUpdateExclusiveLock
      readers blocked 0/850, max 9 ms | writers blocked 0/170, max 9 ms
    timing (autocommit): OK, 86 ms, rewrite no
      readers blocked 0/40, max 2 ms | writers blocked 0/8, max 2 ms
      server DEBUG: verifying table "orders"
  sql: ALTER TABLE orders ALTER COLUMN status SET NOT NULL
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2007 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 1999 ms), max 1999 ms | writers blocked 2/2 (max 1998 ms), max 1998 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped
      server DEBUG: existing constraints on column "orders.status" are sufficient to prove that it does not contain nulls
  sql: ALTER TABLE orders DROP CONSTRAINT orders_status_nn
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2019 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 2006 ms), max 2006 ms | writers blocked 2/2 (max 2001 ms), max 2001 ms
    timing (autocommit): OK, 1 ms, rewrite no
      readers: no query overlapped | writers: no query overlapped

E11c Postgres 18: NOT NULL NOT VALID, then VALIDATE
  sql: ALTER TABLE orders ADD CONSTRAINT orders_status_not_null NOT NULL status NOT VALID
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2009 ms
      locks held by the migration: orders AccessExclusiveLock
      readers blocked 10/10 (max 1991 ms), max 1991 ms | writers blocked 2/2 (max 1990 ms), max 1990 ms
    timing (autocommit): OK, 3 ms, rewrite no
      readers blocked 0/10, max 2 ms | writers blocked 0/2, max 3 ms
  sql: ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null
    probe (in a transaction, lock_timeout 500ms, locks held 2 s): OK, 2087 ms
      locks held by the migration: orders ShareUpdateExclusiveLock
      readers blocked 0/851, max 8 ms | writers blocked 0/170, max 9 ms
    timing (autocommit): OK, 82 ms, rewrite no
      readers blocked 0/31, max 2 ms | writers blocked 0/8, max 3 ms
      server DEBUG: verifying table "orders"
  after: attnotnull=true orders_status_not_null convalidated=true

== CREATE INDEX CONCURRENTLY behind an idle transaction, and two ways it fails

E7a CIC behind an idle read-only transaction
  sql: CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at), lock_timeout 0 (off)
  idle transaction: BEGIN; SELECT count(*) FROM orders WHERE id < 10, COMMIT at +5005 ms
  outcome: OK | sent at +513 ms, elapsed 666 ms
  locks held by the build: orders ShareUpdateExclusiveLock | waited on: none seen
  readers blocked 0/310, max 2 ms | writers blocked 0/62, max 4 ms
  indexes on orders after: orders_created_at_cidx valid=true ready=true, orders_pkey valid=true ready=true

E7b CIC behind an idle transaction that updated one row
  sql: CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at), lock_timeout 0 (off)
  idle transaction: BEGIN; UPDATE orders SET note = 'long tx' WHERE id = 1, COMMIT at +5005 ms
  outcome: OK | sent at +511 ms, elapsed 5164 ms
  locks held by the build: orders ShareUpdateExclusiveLock | waited on: virtualxid ShareLock blocked_by [2172 long tx]
  readers blocked 0/2042, max 17 ms | writers blocked 0/406, max 17 ms
  indexes on orders after: orders_created_at_cidx valid=true ready=true, orders_pkey valid=true ready=true

E7c same as E7b, lock_timeout 500ms
  sql: CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at), lock_timeout 500ms (session SET)
  idle transaction: BEGIN; UPDATE orders SET note = 'long tx' WHERE id = 1, COMMIT at +5008 ms
  outcome: ERROR 55P03 canceling statement due to lock timeout | sent at +510 ms, elapsed 509 ms
  locks held by the build: orders ShareUpdateExclusiveLock | waited on: virtualxid ShareLock blocked_by [2190 long tx]
  readers blocked 0/200, max 10 ms | writers blocked 0/40, max 9 ms
  indexes on orders after: orders_created_at_cidx valid=false ready=false, orders_pkey valid=true ready=true
  retry after COMMIT: CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at): ERROR 42P07 relation "orders_created_at_cidx" already exists | then: orders_created_at_cidx valid=false ready=false, orders_pkey valid=true ready=true
  retry after COMMIT: CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_created_at_cidx ON orders (created_at): OK (NOTICE relation "orders_created_at_cidx" already exists, skipping) | then: orders_created_at_cidx valid=false ready=false, orders_pkey valid=true ready=true
  retry after COMMIT: DROP INDEX CONCURRENTLY orders_created_at_cidx: OK | then: orders_pkey valid=true ready=true
  retry after COMMIT: CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at): OK | then: orders_created_at_cidx valid=true ready=true, orders_pkey valid=true ready=true

E8 CREATE UNIQUE INDEX CONCURRENTLY on a column with one duplicate pair
  sql: CREATE UNIQUE INDEX CONCURRENTLY orders_external_ref_uidx ON orders (external_ref)
  outcome: ERROR 23505 could not create unique index "orders_external_ref_uidx" DETAIL: Key (external_ref)=(ref-0999999) is duplicated. | elapsed 1329 ms
  readers blocked 0/610, max 3 ms | writers blocked 0/122, max 3 ms
  indexes on orders after the failure: orders_external_ref_uidx valid=false ready=false, orders_pkey valid=true ready=true
  INSERT with a new external_ref: INSERT OK
  INSERT with an existing external_ref (ref-0000005): INSERT OK
  plan for WHERE external_ref = 'ref-0000005': Gather /   Workers Planned: 2 /   ->  Parallel Seq Scan on orders /         Filter: (external_ref = 'ref-0000005'::text)
  DROP INDEX CONCURRENTLY orders_external_ref_uidx: OK, 1 ms | indexes after: orders_pkey valid=true ready=true

E9 CREATE INDEX CONCURRENTLY cancelled by statement_timeout 300ms, then REINDEX INDEX CONCURRENTLY
  outcome: ERROR 57014 canceling statement due to statement timeout | elapsed 301 ms
  indexes on orders after the cancel: orders_created_at_cidx valid=false ready=false, orders_pkey valid=true ready=true
  REINDEX INDEX CONCURRENTLY orders_created_at_cidx: OK, 643 ms | readers blocked 0/300, max 1 ms | writers blocked 0/58, max 3 ms
  indexes on orders after REINDEX: orders_created_at_cidx valid=true ready=true, orders_pkey valid=true ready=true

== backfill

E12a backfill region = 'us' on 2000044 rows, one UPDATE, one transaction
  total 5040 ms, longest transaction 5040 ms | locks held: orders RowExclusiveLock
  readers blocked 0/2350, max 5 ms | writer UPDATEs blocked 2/26 (max 4680 ms), max 4680 ms | writer INSERTs blocked 0/24, max 2 ms
  rows with region NULL and id <= 2000044: 0 | rows inserted during the backfill, region NULL: 28 | dead tuples right after (pgstattuple): 1995110 | table size 171 MB before, 358 MB after

E12b backfill region = 'us' on 2000044 rows, id-range batches of 10,000, one commit each
  total 4494 ms, 201 batches, slowest batch 39 ms, longest transaction 39 ms | locks held: orders RowExclusiveLock
  readers blocked 0/2100, max 3 ms | writer UPDATEs blocked 0/208, max 12 ms | writer INSERTs blocked 0/208, max 7 ms
  rows with region NULL and id <= 2000044: 0 | rows inserted during the backfill, region NULL: 212 | dead tuples right after (pgstattuple): 1879982 | table size 171 MB before, 358 MB after

== matrix (probe pass decides blocks reads/writes; timing pass gives rewrite and the measured waits)
id | statement | locks held (pg_locks) | blocks reads | blocks writes | rewrite | elapsed ms | reader max ms | writer max ms
E3 | ADD COLUMN flag int | orders AccessExclusiveLock | yes | yes | no | 1 | 0 | 0
E3a | ADD COLUMN region text DEFAULT 'us' | orders AccessExclusiveLock | yes | yes | no | 1 | 0 | 0
E3b | ADD COLUMN seen_at timestamptz DEFAULT now() | orders AccessExclusiveLock | yes | yes | no | 3 | 0 | 0
E4 | ADD COLUMN created_v timestamptz DEFAULT clock_timestamp() | orders AccessExclusiveLock+ShareLock | yes | yes | yes | 1472 | 1450 | 1454
E5a | ALTER COLUMN amount_cents TYPE bigint | orders AccessExclusiveLock+ShareLock | yes | yes | yes | 1403 | 1384 | 1382
E5b | ALTER COLUMN note TYPE varchar(100) | orders AccessExclusiveLock | yes | yes | no | 1 | 0 | 0
E5c | ALTER COLUMN note TYPE text | orders AccessExclusiveLock | yes | yes | no | 1 | 0 | 0
E6 | CREATE INDEX orders_created_at_idx ON orders (created_at) | orders ShareLock | no | yes | no | 440 | 3 | 427
E6b | CREATE INDEX CONCURRENTLY orders_created_at_cidx ON orders (created_at) | orders ShareUpdateExclusiveLock | no | no | no | 667 | 2 | 4
E10a | ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) | orders ShareRowExclusiveLock+AccessShareLock, customers ShareRowExclusiveLock+RowShareLock+AccessShareLock | no | yes (customers yes) | no | 176 | 2 | 163 (customers 132)
E10b.1 | ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID | orders ShareRowExclusiveLock+AccessShareLock, customers ShareRowExclusiveLock+AccessShareLock | no | yes (customers yes) | no | 1 | 0 | 0 (customers 0)
E10b.2 | VALIDATE CONSTRAINT orders_customer_fk | orders ShareUpdateExclusiveLock+AccessShareLock, customers RowShareLock+AccessShareLock | no | no (customers no) | no | 202 | 8 | 9 (customers 4)
E11a | ALTER COLUMN status SET NOT NULL | orders AccessExclusiveLock | yes | yes | no | 65 | 50 | 49
E11b.1 | ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID | orders AccessExclusiveLock | yes | yes | no | 2 | 0 | 0
E11b.2 | VALIDATE CONSTRAINT orders_status_nn | orders ShareUpdateExclusiveLock | no | no | no | 86 | 2 | 2
E11b.3 | ALTER COLUMN status SET NOT NULL | orders AccessExclusiveLock | yes | yes | no | 1 | 0 | 0
E11b.4 | DROP CONSTRAINT orders_status_nn | orders AccessExclusiveLock | yes | yes | no | 1 | 0 | 0
E11c.1 | ADD CONSTRAINT orders_status_not_null NOT NULL status NOT VALID | orders AccessExclusiveLock | yes | yes | no | 3 | 2 | 3
E11c.2 | VALIDATE CONSTRAINT orders_status_not_null | orders ShareUpdateExclusiveLock | no | no | no | 82 | 2 | 3
E12a | UPDATE all rows (one statement) | orders RowExclusiveLock | no | updates yes, inserts no | n/a | 5040 | 5 | updates 4680, inserts 2
E12b | UPDATE in 10,000-row batches | orders RowExclusiveLock | no | updates no, inserts no | n/a | 4494 | 3 | updates 12, inserts 7