---
title: "Why a fast PostgreSQL migration blocked every query | Proofline"
description: "An ALTER TABLE that needs one millisecond can stop all reads on a table for as long as some other transaction stays open."
url: https://proofline.sh/learn/postgres-migration-lock-queue/
---

[Learning center](/learn/)

# Why a fast PostgreSQL migration blocked every query

An ALTER TABLE that needs one millisecond can stop all reads on a table for as long as some other transaction stays open. The migration waits for its lock, and every new query on the table waits behind the migration. Set a lock\_timeout so the migration gives up quickly, and retry it.

Breaking migrations The Proofline team November 20, 2026 3 min read

## Reproduce the queue with three sessions

You need three `psql` sessions on one scratch database. We ran this on PostgreSQL 16.15.

```
CREATE TABLE orders (id integer PRIMARY KEY, status text NOT NULL);
INSERT INTO orders VALUES (1, 'packed');
```

**Session 1** is a report that reads the table inside a transaction and keeps the transaction open. A slow report, an idle-in-transaction connection or a long `COPY` behaves the same way.

```
BEGIN;
SELECT count(*) FROM orders;
SELECT pg_sleep(6);
COMMIT;
```

**Session 2** is the migration. Adding a nullable column without a default changes only the catalog.

```
\timing on
ALTER TABLE orders ADD COLUMN note text;
```

**Session 3** is the application, reading one order.

```
\timing on
SELECT status FROM orders WHERE id = 1;
```

Start them a fraction of a second apart, in that order, and look at a fourth session while they wait:

```
SELECT application_name, state, wait_event_type,
       left(query, 45) AS query, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
```

| application\_name | wait\_event\_type | query | blocked\_by |
| --- | --- | --- | --- |
| report | Timeout | BEGIN; SELECT count(\*) FROM orders; ... | {} |
| migration | Lock | ALTER TABLE orders ADD COLUMN note text; | {report} |
| api | Lock | SELECT status FROM orders WHERE id = 1; | {migration} |

We named each session with `SET application_name` and show those names in place of process IDs. The migration reported `Time: 5506.593 ms`. The application's single-row read reported `Time: 4994.398 ms`.

## Why the read waits for the migration

The report holds an `ACCESS SHARE` lock until it commits. `ALTER TABLE ... ADD COLUMN` needs `ACCESS EXCLUSIVE`, which conflicts with every other lock mode, so it waits. The application's `SELECT` also needs only `ACCESS SHARE`, which the report's lock would allow. PostgreSQL still queues it behind the waiting `ACCESS EXCLUSIVE` request, because the [lock conflict table](https://www.postgresql.org/docs/16/explicit-locking.html#LOCKING-TABLES) applies to waiting requests too, and letting later readers jump ahead could keep the migration waiting forever. `pg_blocking_pids` shows the result: the read is blocked by the migration, not by the report.

During a deployment, each blocked read holds a connection from the application's pool. Once the pool is used up, requests that never touch `orders` wait for a connection as well.

## Make the migration give up instead

[`lock_timeout`](https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-LOCK-TIMEOUT) cancels a statement that has waited longer than the limit to acquire a lock. Remove the column the first run added, then repeat the experiment with the timeout set in session 2:

```
ALTER TABLE orders DROP COLUMN note;  -- reset, with no other session open

-- session 2, while session 1 holds its transaction open
SET lock_timeout = '500ms';
ALTER TABLE orders ADD COLUMN note text;
```

```
ERROR:  canceling statement due to lock timeout
Time: 500.538 ms
```

The application's read now returned after 290.822 ms, the remainder of the migration's half-second wait. The migration failed with SQLSTATE `55P03`, and nothing changed. Retry it after a short pause, a limited number of times. Then stop and find the transaction that holds the lock.

```
SELECT pid, state, xact_start, left(query, 60)
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 minute'
ORDER BY xact_start;
```

Pick the timeout from what the application tolerates. A lock wait adds to the latency of every query on the table, so a timeout of a few hundred milliseconds keeps a failed attempt short. `lock_timeout` limits only the wait. It does not limit a rewrite that has already acquired its lock, so a statement that copies the table also needs a [rehearsal of its own duration](/learn/postgres-alter-column-type-rewrite/).

## What to rehearse

Check how your migration tool runs statements before you rely on a timeout. Many tools wrap a migration file in one transaction. The `lock_timeout` must apply inside that transaction, for example with `SET LOCAL lock_timeout` as the file's first statement, and the retry must repeat the whole file. In the rehearsal, open a long transaction on the table from another session, as session 1 does here, and confirm that the migration fails within the timeout and that its retry succeeds after the transaction ends.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Which PostgreSQL migration needs a rehearsal before the next deploy](/learn/postgres-migration-rehearsal/)
    
    Breaking migrations The Proofline team November 20, 2026 4 min read
    
-   ### [Which PostgreSQL column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
