Reading Postgres Locks and Bloat During a Migration

Knowing a pid is waiting tells you little. You want the holder. pg_locks shows every lock request, granted or not, with the lock mode and the object. The PostgreSQL wiki keeps a long-standing query that joins pg_locks to itself on every lock identity column and then to pg_stat_activity twice, so each blocked pid appears next to its blocker and both queries the PostgreSQL wiki on lock monitoring.

That self-join is correct but verbose. If you are on a version that has pg_blocking_pids(), the query is a few lines, as shown on Stack Overflow the pg_blocking_pids answer:

SELECT
  a.pid,
  a.state,
  a.wait_event_type,
  a.wait_event,
  pg_blocking_pids(a.pid) AS blocked_by,
  now() - a.xact_start     AS xact_age,
  left(a.query, 70)        AS waiting_query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY xact_age DESC;

For the migration itself, add the lock mode. This is the detail most monitoring dashboards omit, and it explains why a fast DDL statement can still take an outage: lock requests are queued in order, so the ACCESS EXCLUSIVE request blocks every later reader even before it is granted. The RunxBuild guide on adding columns to a live table describes the mechanism and the fix in one line: SET LOCAL lock_timeout = '3s' inside the migration transaction, so the DDL fails fast instead of parking the table behind itself the RunxBuild column guide.

SELECT
  l.pid,
  l.locktype,
  l.relation::regclass AS relation,
  l.mode,
  l.granted,
  pg_blocking_pids(l.pid) AS blocked_by
FROM pg_locks l
WHERE l.relation = 'users'::regclass
ORDER BY l.granted, l.pid;

A worked example makes the queue behavior concrete. Suppose session A runs an UPDATE users inside an open transaction and then idles, holding a ROW EXCLUSIVE lock on users. Session B runs ALTER TABLE users ADD COLUMN last_login timestamptz, requesting ACCESS EXCLUSIVE, and waits. Session C runs a plain SELECT on users. Because B's request is queued first, C waits behind B even though C's read would not conflict with A at all. The table is unavailable to new readers not because of a long migration, but because of a paused client. Raising lock_timeout on the ALTER converts this into a failed migration you can retry, instead of an application outage.

The mode column in pg_locks only makes sense alongside the operations that take each mode. For migrations, four cases cover most of what you will see.

Adding a column with a constant default is catalogue-only on modern Postgres, so the ACCESS EXCLUSIVE lock lasts milliseconds regardless of table size. A volatile default such as now() or gen_random_uuid() forces a full table rewrite under that same lock, which is the case that turns a one-line statement into minutes of blocked traffic the RunxBuild column guide.

A plain CREATE INDEX takes a SHARE lock, which permits reads and blocks writes; CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead, which permits reads and writes but blocks other DDL on the same table the Bytebase index guide. The Postgres manual documents the concurrent build in detail, including that it cannot run inside a transaction block and that a failed build leaves an invalid index behind the PostgreSQL CREATE INDEX documentation. A common misconception is worth settling: CONCURRENTLY is a build option, not a property of the finished index, so nothing in pg_indexes records it later the Stack Overflow explanation.

ALTER TABLE ... SET NOT NULL is the quiet one. Even when no nulls exist, Postgres must verify by scanning every row while holding ACCESS EXCLUSIVE, which blocks reads as well as writes the Atlas guide to lock-safe NOT NULL. The safe sequence is add a NOT VALID check constraint, validate it under a weak lock, then set NOT NULL, which newer versions can do by trusting the validated constraint the RunxBuild column guide.

Index builds and some maintenance operations also report progress. pg_stat_progress_create_index exposes a phase column with values from initializing through building index to waiting for old snapshots, plus counters for blocks and tuples done the Bytebase index guide. When a concurrent build seems stuck, that view tells you whether it is scanning, waiting for writers, or waiting for an old snapshot to be released by a long transaction.

Bloat is not a lock problem, but it is part of reading what a migration is doing to the database. Updates and deletes leave dead tuples behind, and a table that looks small on disk can carry a large amount of dead space that VACUUM has not yet reclaimed. During a backfill, every updated row creates a new version, so dead tuple counts and table size rise while the migration runs. That is expected; what matters is whether they come back down.

A practical pattern is a snapshot table you fill before, during and after the migration, so you can compare rather than remember:

CREATE TABLE IF NOT EXISTS migration_watch (
  taken_at      timestamptz DEFAULT now(),
  relname       text,
  total_bytes   bigint,
  live_tuples   bigint,
  dead_tuples   bigint,
  seq_scans     bigint
);

INSERT INTO migration_watch (relname, total_bytes, live_tuples, dead_tuples, seq_scans)
SELECT
  c.relname,
  pg_total_relation_size(c.oid),
  s.n_live_tup,
  s.n_dead_tup,
  s.seq_scan
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relname IN ('users', 'orders');

Run that insert at the start, once mid-run, and once after the migration and a VACUUM. If dead tuples are an order of magnitude higher after the backfill and the table size never returns to baseline, the backfill batch size is too large or too fast for autovacuum to keep up, and the next migration on that table will run against a fatter table.

One more decision rule matters for the observation window itself. Static safety linting on the DDL text catches obvious hazards, but the same statement can be a no-op or a full rewrite depending on the column's existing type and the table's size the Hacker News discussion of migration safety. That is the argument for watching the live database rather than trusting the ruleset alone: the catalog tells you what Postgres actually decided to do.

The queries above are most useful when the migration follows a shape that gives you something observable to check. The expand and contract pattern splits one risky change into a sequence of small, backwards-compatible steps: add the new structure, migrate data and client code to it, then remove the old structure once nothing uses it the Prisma expand and contract guide. Each step is separately deployable, so each gets its own observation window instead of one long, opaque cutover.

Jasmin Fluri's write-up of the same pattern names the three phases (expand, migrate, contract) and the reason teams accept the extra steps: safe incremental releases, and the ability to roll forward rather than unwind a large batch of changes the expand and contract overview. Harness frames the same idea as a compatibility rule: every schema change must be safe for the current app version and the next one, because during a rolling deploy both may be running the Harness zero-downtime guide.

For observability, that means you can attach a specific check to each phase. During expand, confirm the ALTER TABLE ADD COLUMN returned in milliseconds and left no waiting backends behind it. During migrate, watch dead tuples and table size as the backfill runs. During contract, confirm no pg_stat_activity row references the old column before you drop it. A migration that is structured this way produces a readable story in the catalogs; a single big-bang change produces one long lock wait and no way to tell which statement caused it.

Put the checks in this order. It moves from "is anything blocked" to "what is the lock holder doing" to "is the database healthy underneath".

1. Run the pg_stat_activity query with query_age twice, a few seconds apart. Note any pid that stays active with the same wait event.
2. Run the pg_locks query for the affected relation. Record the mode and granted values for the migration's own pid.
3. Call pg_blocking_pids() on the migration pid. Follow the chain until you reach a pid with no blocker; that is the root.
4. Read the root's state, xact_start and query. idle in transaction means a client is holding locks; an old xact_start on a busy query means a long transaction.
5. Check pg_stat_progress_create_index if an index build is involved, and pg_stat_progress_vacuum if a vacuum is running alongside.
6. Record size and dead tuple numbers in your watch table so the after-migration comparison is quantitative.
7. Check for invalid indexes in pg_index if any concurrent build failed, since a failed build leaves a broken index that ordinary queries will ignore.

If step 3 lands on a process you do not recognize, the safest move is not pg_terminate_backend but a look at application_name and client_addr first. The wiki's alternate query includes application_name for exactly this reason: it maps a pid back to a code path or service the PostgreSQL wiki on lock monitoring.

Teams that run migrations often end up automating the read side: a small script that samples these queries every few seconds, stores the results, and flags any migration whose wait time exceeds a threshold. That is the natural place where an AI assistant that can query your Postgres and summarize the output earns its keep, and where a specialist can wire the check into your deploy pipeline and your monitoring. If you need a tool built for that, describe it and a Quanta specialist will pick it up and build it with you at Quanta.