Postgres migration hangs: idle in transaction session blocks ALTER TABLE

On a Tuesday afternoon deploy, our pipeline ran Django migrations for an order management app. The migration added one nullable column to orders. It normally takes milliseconds. This time the migrate step sat silent, and within a minute every page that touched orders started timing out. The load balancer reported a wall of 504s, and the web workers logged:

django.db.utils.OperationalError: canceling statement due to statement timeout

after waiting the full 30 seconds of our statement_timeout on simple SELECTs. The site was effectively down for eleven minutes, until someone cancelled the deploy job.

Reproducing it

Three sessions in psql reproduce it every time:

-- session 1: a transaction that read from orders and then went idle
BEGIN;
SELECT id FROM orders WHERE id = 42;
-- ...no COMMIT

-- session 2: the migration
ALTER TABLE orders ADD COLUMN fulfillment_note text;   -- waits

-- session 3: any ordinary read
SELECT count(*) FROM orders WHERE status = 'paid';      -- also waits

Session 3 does not conflict with session 1 at all. It waits because of session 2.

What we ruled out

• A slow migration. Adding a nullable column without a default is a catalog-only change in PostgreSQL; there was no table rewrite. The ALTER was waiting, not working.
• Database CPU or I/O saturation. CPU sat at 8% and IOPS were normal during the outage. Waiting on locks costs nothing.
• Connection pool exhaustion as the cause. PgBouncer did run out of server connections after a few minutes, but only because every connection was stuck waiting.

Root cause

pg_stat_activity showed a connection from a Celery worker in state idle in transaction, with a transaction that had started 14 minutes earlier:

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age, wait_event_type, left(query, 60) AS last_query
FROM   pg_stat_activity
WHERE  datname = current_database() AND state <> 'idle'
ORDER  BY xact_start;

  pid  | usename | application_name |        state        | xact_age  | wait_event_type |               last_query
-------+---------+------------------+---------------------+-----------+-----------------+------------------------------------------
 48211 | app     | celery           | idle in transaction | 00:14:03  | Client          | SELECT "orders"."id", "orders"."status" ...
 51907 | deploy  | migrate          | active              | 00:00:52  | Lock            | ALTER TABLE "orders" ADD COLUMN "fulfillm
 51944 | app     | gunicorn         | active              | 00:00:29  | Lock            | SELECT COUNT(*) AS "__count" FROM "order

The Celery task opened a transaction with transaction.atomic(), read the order, and then called a payment provider's refund API, which was hanging. The read left an ACCESS SHARE lock on orders for as long as the transaction stayed open. The ALTER TABLE needs an ACCESS EXCLUSIVE lock, so it queued behind the Celery transaction, and every new query queued behind the ALTER.

What is happening under the hood

PostgreSQL table locks are granted in queue order. ACCESS SHARE, taken by every SELECT, only conflicts with ACCESS EXCLUSIVE. Normally that means reads never block each other. But once an ACCESS EXCLUSIVE request is waiting in the queue, later requests that conflict with it, including plain ACCESS SHARE from new SELECTs, queue behind it instead of jumping ahead. This avoids starving the ALTER, and it is why a single waiting DDL statement can block all traffic to a table.

Locks taken in a transaction are held until the transaction ends. An idle in transaction session is one that has run statements inside a BEGIN and is now waiting for the client to send more. It holds all its locks and also prevents vacuum from removing dead rows newer than its snapshot.

So the chain was: an idle transaction holding a harmless lock, a DDL statement waiting for it, and the whole application waiting for the DDL.

Environment

Minimal reproduction

The three-session example above is the minimal reproduction. To see the queue, run this in a fourth session while the others are waiting:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 50)
FROM   pg_stat_activity
WHERE  cardinality(pg_blocking_pids(pid)) > 0;

The SELECT is blocked by the ALTER, and the ALTER is blocked by the idle transaction.

Who is affected

Every PostgreSQL version. Any DDL that needs ACCESS EXCLUSIVE can trigger it: ALTER TABLE in most forms, DROP TABLE, TRUNCATE, CREATE INDEX without CONCURRENTLY (which takes a SHARE lock that blocks writes), and REINDEX. It happens with any migration tool: Django, Rails, Flyway, Liquibase, Alembic, Prisma.

The fix

We changed three things: migrations give up quickly instead of waiting forever, idle transactions are cut off, and the Celery task no longer holds a transaction across an HTTP call.

1. lock_timeout for migrations. The deploy now runs migrations with a short lock timeout. If the lock cannot be acquired in 3 seconds, the statement fails, the queue behind it drains immediately, and the deploy retries:

for attempt in 1 2 3 4 5; do
  PGOPTIONS='-c lock_timeout=3s -c statement_timeout=15min' \
    python manage.py migrate --noinput && break
  echo "migrate attempt $attempt hit lock_timeout, retrying in 10s"
  sleep 10
done

With psycopg, libpq reads PGOPTIONS at connect time, so every statement in the migration gets the timeout. The migrate step connects directly to the primary rather than through PgBouncer, so the settings apply to a real, dedicated server session. A failed attempt looks like this, and nothing else is affected:

django.db.utils.OperationalError: canceling statement due to lock timeout

Django runs each migration in a transaction on PostgreSQL, so a timed-out migration rolls back cleanly.

2. idle_in_transaction_session_timeout for application roles. We set it per role so that it covers every client, including ad-hoc scripts:

ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app SET lock_timeout = '10s';

A session that sits idle inside a transaction for more than 60 seconds is now terminated with FATAL: terminating connection due to idle-in-transaction timeout, releasing its locks.

3. No external calls inside a transaction. The refund task now reads what it needs, commits, calls the provider with a timeout, and writes the result in a second short transaction:

-with transaction.atomic():
-    order = Order.objects.get(pk=order_id)
-    result = provider.refund(order.payment_ref, order.total)
-    order.mark_refunded(result.id)
+order = Order.objects.only("id", "payment_ref", "total").get(pk=order_id)
+result = provider.refund(order.payment_ref, order.total, timeout=10)
+with transaction.atomic():
+    order = Order.objects.select_for_update().get(pk=order_id)
+    order.mark_refunded(result.id)

The refund call carries an idempotency key, so a retry after a crash between the two steps is safe.

Alternatives and trade-offs

1. transaction_timeout (PostgreSQL 17). Caps the total duration of any transaction, idle or not. We are on 16, so it is on the upgrade list rather than in use.
2. Terminating blockers before migrating. A pre-migration step that runs pg_terminate_backend on old idle-in-transaction sessions works, but it is a blunt tool and can kill legitimate work. We only use it manually during incidents.
3. Online schema change patterns. For heavy changes, such as adding a NOT NULL constraint to a large table, we split work into steps: add a NOT VALID constraint, then VALIDATE CONSTRAINT, which takes a weaker lock. This reduces lock duration but still needs a short exclusive lock, so lock_timeout remains essential.

How to test the fix

We added the three-session scenario as an integration test in CI against a real PostgreSQL 16 container. The test opens an idle transaction, runs migrate with the deploy wrapper, and asserts that a concurrent SELECT finishes within one second and that the migration eventually succeeds once the idle transaction is closed:

docker compose -f ci/compose.pg.yml up -d postgres
pytest tests/deploy/test_migration_lock_timeout.py -q

Confirming in production

Over the next six weeks we ran 23 deploys with migrations. Two of them hit lock_timeout once and succeeded on the second attempt; neither caused a measurable latency change on the site. The idle-in-transaction timeout terminated 140 sessions in the first week, almost all from an internal reporting script that kept a transaction open between queries; we fixed that script. A daily check now shows no transaction older than 60 seconds:

SELECT count(*) FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%' AND now() - state_change > interval '60 seconds';

 count
-------
     0

Regression guard

• The deploy wrapper refuses to run migrate unless lock_timeout is set in PGOPTIONS.
• An alert fires when any transaction is older than 5 minutes or any session waits on a lock for more than 10 seconds.
• A small AST-based lint script in CI flags HTTP client calls inside transaction.atomic() blocks in our Celery task modules.

Security implications

The outage was an availability incident, and the pattern is a denial-of-service lever: any client able to open a transaction and leave it idle can, combined with a DDL statement, block a table. Role-level idle_in_transaction_session_timeout limits that. Long-lived transactions also block vacuum, which leads to table bloat and, in the extreme, transaction ID wraparound protection, so the timeout helps database health beyond this incident. We also gave the reporting role read-only access with its own stricter timeouts.

Prevention checklist

• Run every migration with lock_timeout and a retry loop.
• Set idle_in_transaction_session_timeout for application and reporting roles.
• Never call external services inside a database transaction.
• Monitor transaction age and lock waits, not just query duration.
• Check pg_stat_activity for long transactions before running DDL manually.

References

• PostgreSQL docs, Explicit Locking (table-level lock modes) — https://www.postgresql.org/docs/16/explicit-locking.html
• PostgreSQL docs, Client Connection Defaults (lock_timeout, idle_in_transaction_session_timeout) — https://www.postgresql.org/docs/16/runtime-config-client.html
• PostgreSQL docs, The Cumulative Statistics System, pg_stat_activity — https://www.postgresql.org/docs/16/monitoring-stats.html
• PostgreSQL docs, System Information Functions, pg_blocking_pids — https://www.postgresql.org/docs/16/functions-info.html
• PostgreSQL 17 release notes (transaction_timeout) — https://www.postgresql.org/docs/17/release-17.html