PostgreSQL Schema Changes Without Downtime: Locks, the Lock Queue, and Expand-Contract

In a multi-tenant product there is rarely a maintenance window that suits every customer, so schema changes happen while the application is serving traffic. Most PostgreSQL schema changes are quick. The outages come from the few that are slow, and from quick ones that have to wait for a lock and, while waiting, make everyone else wait too.

This guide explains which common changes block reads or writes, why a "2 millisecond" ALTER TABLE can still stall an application, and how to change a column in stages without breaking running code. Every lock statement below is taken from the PostgreSQL 18 documentation and linked to it. A lab then models the lock queue in simulated time: with no lock_timeout, one waiting ALTER TABLE caused 7,500 of 12,000 application queries to fail; with a 500 ms timeout and retries, none failed.

Which changes block what

"Briefly" deserves a warning. Many ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including the ACCESS SHARE lock a plain SELECT takes (see Explicit Locking). The change itself may take milliseconds. Acquiring the lock can take as long as the longest transaction currently touching the table.

The lock queue problem

Suppose a report has had a transaction open on invoices for two minutes. Your migration runs ALTER TABLE invoices ADD COLUMN notes text. It cannot get its lock until the report finishes, so it waits. Meanwhile, new queries on invoices arrive. They conflict with the waiting ACCESS EXCLUSIVE request, so they queue behind it. For two minutes, every query on that table waits, connection pools fill up, and the application starts timing out, all because of a change that would have taken milliseconds.

GitLab's migration style guide describes the same failure: the lock request "sits in the lock queue, blocking other queries". The fix is to refuse to wait for long:

SET lock_timeout = '3s';
ALTER TABLE invoices ADD COLUMN notes text;
-- If the lock is not granted within 3 seconds, the statement fails
-- and the queue behind it drains. Retry later, with backoff.

lock_timeout aborts a statement that waits longer than the given time to acquire a lock. Run the migration in a loop that retries a few times with a pause, and alert if it keeps failing, because something is holding long transactions on that table.

Do not set a short statement_timeout for the same purpose. It limits total run time, so it also cancels the operations that are supposed to run for a long time without blocking, such as CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT.

Lab: the lock queue, simulated

How bad is the queue, and how much does lock_timeout help? The program models the rule above in simulated time. A reporting transaction holds ACCESS SHARE on orders for its first 90 seconds. At 10 seconds a migration asks for ACCESS EXCLUSIVE. The application sends 100 queries per second, each holding ACCESS SHARE for 10 ms, and gives up on a query after 5 seconds. Locks are granted in arrival order, and a request waits if it conflicts with a held lock or with a request queued ahead of it, which is what makes ordinary queries wait behind a waiting ALTER.

It compares no timeout, a 3-second timeout with a retry 30 seconds later, and a 500 ms timeout with a retry 5 seconds later. Run it with go run .; it uses only the standard library. It is a model of the queueing rule, not a measurement of PostgreSQL: real queries vary in length, and real pools also run out of connections, which makes the no-timeout case worse.

// Lab: one waiting ALTER TABLE, and every query that queues behind it.
//
// A model of PostgreSQL's table lock queue in simulated time (1 ms steps):
//   - a reporting transaction holds ACCESS SHARE on "orders" from t=0 to t=90 s;
//   - a migration asks for ACCESS EXCLUSIVE at t=10 s and needs 5 ms once granted;
//   - the application sends 100 queries per second, each holding ACCESS SHARE
//     for 10 ms; a query fails if it has waited 5 s (its statement_timeout).
//
// Locks are granted in arrival order: a request waits if it conflicts with a
// held lock or with any request queued ahead of it. ACCESS SHARE conflicts only
// with ACCESS EXCLUSIVE. This is the rule that makes queries wait behind a
// waiting ALTER even though they do not conflict with the report.
package main

import "fmt"

type mode int

const (
	accessShare mode = iota
	accessExclusive
)

func conflicts(a, b mode) bool { return a == accessExclusive || b == accessExclusive }

type req struct {
	id, arrived, holdMs int
	mode                mode
	isMigration         bool
}

type held struct {
	mode  mode
	until int
}

type result struct {
	slow, failed, total int
	worstWait           int
	migrationDone       int
	attempts            int
}

// run simulates 120 s. lockTimeoutMs = 0 means wait forever; retryEveryMs is
// the pause before the migration tries again after a lock timeout.
func run(lockTimeoutMs, retryEveryMs int) result {
	const horizon = 120_000
	var r result
	r.migrationDone = -1
	locks := []held{{accessShare, 90_000}} // the report
	var queue []req
	nextID := 0
	migrationAt := 10_000
	for now := 0; now < horizon; now++ {
		// release finished locks
		kept := locks[:0]
		for _, l := range locks {
			if l.until > now {
				kept = append(kept, l)
			}
		}
		locks = kept
		// arrivals
		if now%10 == 0 {
			queue = append(queue, req{id: nextID, arrived: now, holdMs: 10, mode: accessShare})
			nextID++
			r.total++
		}
		if r.migrationDone < 0 && now == migrationAt {
			queue = append(queue, req{id: -1, arrived: now, holdMs: 5, mode: accessExclusive, isMigration: true})
			r.attempts++
		}
		// timeouts: application statement_timeout and migration lock_timeout
		kept2 := queue[:0]
		for _, q := range queue {
			switch {
			case !q.isMigration && now-q.arrived >= 5000:
				r.failed++
			case q.isMigration && lockTimeoutMs > 0 && now-q.arrived >= lockTimeoutMs:
				migrationAt = now + retryEveryMs // give up, retry later
			default:
				kept2 = append(kept2, q)
			}
		}
		queue = kept2
		// grant in arrival order
		var waiting []req
		for _, q := range queue {
			ok := true
			for _, l := range locks {
				if conflicts(q.mode, l.mode) {
					ok = false
				}
			}
			for _, w := range waiting {
				if conflicts(q.mode, w.mode) {
					ok = false
				}
			}
			if !ok {
				waiting = append(waiting, q)
				continue
			}
			wait := now - q.arrived
			locks = append(locks, held{q.mode, now + q.holdMs})
			if q.isMigration {
				r.migrationDone = now + q.holdMs
				continue
			}
			if wait > r.worstWait {
				r.worstWait = wait
			}
			if wait > 100 {
				r.slow++
			}
		}
		queue = waiting
	}
	return r
}

func main() {
	fmt.Println("120 s simulated; report holds ACCESS SHARE 0-90 s; ALTER TABLE arrives at 10 s; 100 queries/s")
	fmt.Printf("%-40s %10s %12s %10s %14s %9s\n", "migration setting", "queries", "waited>100ms", "failed", "worst wait", "ALTER done")
	for _, c := range []struct {
		name           string
		timeout, retry int
	}{
		{"no lock_timeout", 0, 0},
		{"lock_timeout 3 s, retry after 30 s", 3000, 30_000},
		{"lock_timeout 500 ms, retry after 5 s", 500, 5000},
	} {
		r := run(c.timeout, c.retry)
		done := "never"
		if r.migrationDone >= 0 {
			done = fmt.Sprintf("%.1f s", float64(r.migrationDone)/1000)
		}
		fmt.Printf("%-40s %10d %12d %10d %12d ms %9s  (%d attempts)\n",
			c.name, r.total, r.slow, r.failed, r.worstWait, done, r.attempts)
	}
}

Output recorded on November 3, 2026 (Go 1.26.5, windows/amd64):

120 s simulated; report holds ACCESS SHARE 0-90 s; ALTER TABLE arrives at 10 s; 100 queries/s
migration setting                           queries waited>100ms     failed     worst wait ALTER done
no lock_timeout                               12000          490       7500         4995 ms    90.0 s  (1 attempts)
lock_timeout 3 s, retry after 30 s            12000          867          0         2990 ms   109.0 s  (4 attempts)
lock_timeout 500 ms, retry after 5 s          12000          585          0          490 ms    92.5 s  (16 attempts)

What the output shows

• No timeout: 7,500 of 12,000 queries failed. From 10 to 90 seconds every query queued behind the waiting ALTER, and all but the last five seconds' worth hit the 5-second limit. The ALTER itself took 5 ms once it got the lock. This is how a "harmless" migration takes an application down.
• 3-second timeout: no failures. Queries arriving while the migration waited were delayed by up to 3 seconds, four times, and the migration finished at 109 seconds, after the report.
• 500 ms timeout with quick retries: no failures, worst wait 490 ms, and the migration finished at 92.5 seconds, 2.5 seconds after the report ended. Short timeouts with frequent retries cost the application little and still get the change in promptly.

Indexes

CREATE INDEX blocks writes for the whole build. On a large, busy table, use CREATE INDEX CONCURRENTLY. The documentation lists its costs: two table scans, waiting for existing transactions that could modify or use the index, and no transaction block. If it fails, for example on a deadlock or a uniqueness violation, it leaves an index marked INVALID. That index is not used for queries but is still updated on writes. The recommended recovery is to drop it and run the build again:

DROP INDEX CONCURRENTLY IF EXISTS idx_invoices_tenant_created;
CREATE INDEX CONCURRENTLY idx_invoices_tenant_created ON invoices (tenant_id, created_at);

Check for invalid indexes after every migration run with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;.

Changing a column without breaking running code

Renaming a column, changing its type, or splitting it cannot be done in one step while old application code is still running, because old code reads the old column. The usual answer is expand and contract, which Martin Fowler calls parallel change:

1. Expand. Add the new column, nullable.
2. Write both. Deploy code that writes the old and new columns and still reads the old one.
3. Backfill. Copy existing rows in small batches.
4. Read new. Deploy code that reads the new column (and still writes both, so you can roll back).
5. Enforce. Add constraints on the new column.
6. Contract. Deploy code that stops writing the old column, then drop it.

Each step can be deployed and rolled back on its own. The cost is time: a rename becomes several deploys over days.

Backfilling in batches

One UPDATE users SET email_address = email touches every row in one transaction: it holds row locks on all of them until commit, generates a large amount of write-ahead log at once, and can push replicas far behind. Update in batches by primary key instead, committing each batch:

UPDATE users
SET    email_address = email
WHERE  id IN (
    SELECT id FROM users
    WHERE  email_address IS NULL
    ORDER  BY id
    LIMIT  1000
);
-- repeat until 0 rows are updated

Choose the batch size by measuring how long one batch takes on production-sized data; aim for well under a second. Between batches, check replica lag and slow down if it grows.

Adding NOT NULL without a long lock

SET NOT NULL scans the whole table under an exclusive lock. To avoid that, add a check constraint first without validating it, validate it under a weaker lock, then set NOT NULL. PostgreSQL skips the scan when a valid check constraint already proves the column has no nulls:

ALTER TABLE users ADD CONSTRAINT users_email_address_not_null
    CHECK (email_address IS NOT NULL) NOT VALID;          -- brief lock, no scan
ALTER TABLE users VALIDATE CONSTRAINT users_email_address_not_null;  -- scan, SHARE UPDATE EXCLUSIVE
ALTER TABLE users ALTER COLUMN email_address SET NOT NULL; -- brief lock, scan skipped
ALTER TABLE users DROP CONSTRAINT users_email_address_not_null;

PostgreSQL 18 also allows not-null constraints themselves to be added as NOT VALID and validated later; the ALTER TABLE documentation describes both approaches.

Many tenant databases or schemas

If tenants live in separate schemas or databases, every migration runs many times. Treat each run as a job: record which tenants are on which schema version, run tenants in small parallel batches, stop on the first unexpected failure, and make application code tolerate both the old and new schema until every tenant is done. The isolation model guide discusses this cost when choosing a model.

See the lock queue yourself

The following script was not executed for this guide. Run it against a disposable PostgreSQL database with three psql sessions, in order, to watch the queue form and the lock_timeout fix work.

-- Session 1: an open transaction that has read the table
CREATE TABLE IF NOT EXISTS lockdemo (id int PRIMARY KEY, v text);
INSERT INTO lockdemo VALUES (1, 'a') ON CONFLICT DO NOTHING;
BEGIN;
SELECT * FROM lockdemo;           -- holds ACCESS SHARE until COMMIT

-- Session 2: the migration, without a lock timeout
ALTER TABLE lockdemo ADD COLUMN note text;   -- waits for session 1

-- Session 3: an ordinary query arriving after the migration
SELECT * FROM lockdemo;           -- waits behind session 2

-- Session 3 (in another tab): see who waits for whom
SELECT pid, state, wait_event_type, left(query, 40) AS query, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();

-- Session 1: COMMIT;  -> sessions 2 and 3 both proceed.
-- Repeat with  SET lock_timeout = '2s';  before the ALTER in session 2:
-- the ALTER fails after 2 seconds and session 3's SELECT runs immediately.

Where to start

Add SET lock_timeout with retries to your migration runner today; it is a one-line change that removes the most common migration outage. Then find long-running transactions in production with pg_stat_activity, because they are what turns quick changes into slow ones. Make CREATE INDEX CONCURRENTLY the default in code review, and plan column renames as expand-and-contract changes rather than single migrations. The row-level security guide covers which role should own tables so migrations and application traffic use separate privileges.

Frequently Asked Questions

How long should lock_timeout be?

Short enough that the queue behind the migration does not exhaust connection pools or breach latency objectives; a few seconds is common. Retry with backoff, and alert if retries keep failing, because that points to long-running transactions.

Is adding a column with a default safe now?

With a non-volatile default, PostgreSQL stores the default in metadata and does not rewrite the table. It still needs a brief exclusive lock, so the lock queue advice applies. Volatile defaults still rewrite the table.

Can I run CREATE INDEX CONCURRENTLY inside a migration transaction?

No. It cannot run inside a transaction block, so migration tools need a way to mark such migrations as non-transactional.

How do I find what is blocking a migration?

Query pg_stat_activity together with pg_blocking_pids(pid) to see each waiting session and the sessions blocking it. Long-running idle-in-transaction sessions are a common cause.

Should application code ever run migrations on startup?

It is risky with several instances starting at once and with migrations that take time. Run migrations as a separate, single job in your deployment pipeline, before or after the code that depends on them as the expand-contract stage requires.

Sources

Documents cited in this guide. Each link was opened and its title compared with the label below on the date shown. A source supports the specific statements it is linked from; design recommendations and lab results are Velynoriva's own.

• ALTER TABLE (PostgreSQL 18 documentation) (PostgreSQL Global Development Group) • Link checked 2026-11-03

  Rewrite rules for defaults, NOT VALID constraints, VALIDATE CONSTRAINT locks, and SET NOT NULL scan skipping.

• CREATE INDEX (PostgreSQL 18 documentation) (PostgreSQL Global Development Group) • Link checked 2026-11-03

  Standard builds lock out writes; concurrent builds, their waits, and invalid indexes.

• Explicit Locking (PostgreSQL 18 documentation, section 13.3) (PostgreSQL Global Development Group) • Link checked 2026-11-03

  Table-level lock modes and which ones conflict.

• Client Connection Defaults (lock_timeout, statement_timeout) (PostgreSQL Global Development Group) • Link checked 2026-11-03

  Behavior of lock_timeout and statement_timeout.

• Migration Style Guide (GitLab) • Link checked 2026-11-03

  Lock queue blocking and retrying with a short lock_timeout.

• Parallel Change (Danilo Sato, martinfowler.com) • Link checked 2026-11-03

  The expand, migrate, and contract pattern.

Revision history

• 2026-11-03 — Rewrote the lock table from the PostgreSQL 18 documentation. Removed the claims that adding a nullable column takes 2 or 3 ms with no locks and that CREATE INDEX takes an ACCESS EXCLUSIVE lock, replaced the advice to set a 3-second statement_timeout, explained the lock queue, and added the CHECK-constraint route to SET NOT NULL. Added a lock-queue simulation lab and an unexecuted PostgreSQL verification script, labeled as such.
• 2026-10-12 — Removed a fabricated DDL timing and universal timeout rules.