Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
When a long-running SELECT holds a table lock, an ALTER TABLE can wait for it—and later queries on that table can queue behind the waiting DDL. If you are asking, “Why are all my queries stuck after an ALTER TABLE?”, the answer may be lock-queue contention rather than a sudden slowdown in every query. The exact cause and safest response depend on your PostgreSQL version, transaction state, workload, and incident procedures.
How one SELECT can hold up an ALTER TABLE
A SELECT normally takes an Access Share lock on the table it reads. Many common forms of ALTER TABLE request an Access Exclusive lock, which conflicts with that read lock. If the SELECT keeps its transaction open, the DDL must wait until the conflicting lock is released.
The queue can then make the incident look broader than it is. A later SELECT might ordinarily be compatible with the first SELECT, but it can wait behind the earlier queued DDL request instead of overtaking it. The PostgreSQL Wiki describes this behavior: “Later requestors respect earlier waiters and do not overtake them.” That means a growing set of waiting queries does not, by itself, show that each query is intrinsically slow.
The exact lock mode and duration depend on the particular statement and transaction behavior. Treat this as a possible lock-wait pattern to verify against live database state, not as proof that every ALTER TABLE or every waiting query behaves identically.
#1 Best Overall
How to identify the waiter and its blockers
Inspect the database while the wait is happening. This query combines activity information with PostgreSQL’s blocker-identification function:
SELECT pid,
usename,
state,
wait_event_type,
wait_event,
query_start,
xact_start,
pg_blocking_pids(pid) AS blocking_pids,
query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start;Use blocking_pids to find the processes blocking a waiting backend, then look up those PIDs in pg_stat_activity to see their state, query, and transaction timing. Adapt the database filter and columns to your permissions and PostgreSQL version. Activity and lock snapshots can change during inspection, and reporting fields may briefly be inconsistent because the statistics view is not fully synchronized.
Rank #2
Read wait state in context
In pg_stat_activity, a backend with state = 'active' and a non-null wait_event is executing a query but blocked somewhere in the system. PostgreSQL 19’s development documentation states this explicitly; check the documentation for the version you run before relying on version-specific details. A wait event is evidence of waiting, but it does not alone tell you which transaction to intervene on.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use pg_locks for lock details
The pg_locks view shows outstanding locks, including whether a request has been granted. Use it to examine lock modes and target relations, and correlate that information with the activity and blocker PIDs. It is useful for seeing lock state, but it is not by itself a complete blocker graph.
A hand-built self-join of pg_locks is easy to get wrong: a correct blocker analysis must account for lock-mode conflicts and queue order. PostgreSQL provides pg_blocking_pids(pid) to identify processes blocking a waiting process, so use that function as the starting point for blocker attribution rather than inferring the entire graph from matching lock rows.
Check for prepared transactions when sessions do not explain the wait
A prepared transaction can retain locks without a corresponding backend session in pg_stat_activity. If the visible sessions and blocker PIDs do not explain the lock, inspect prepared transactions as part of the investigation. Their lack of a normal session PID is an important distinction from an ordinary active or idle-in-transaction backend.
Choose a mitigation that fits the incident
Schedule disruptive DDL for a quieter period
The PostgreSQL Wiki’s operations guidance recommends running DDL during off-peak hours even when it is expected to be fast. This reduces the chance that normal workload and a lock wait will combine into a broad queue. It does not guarantee that a particular migration will acquire its lock immediately.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesBound how long the DDL waits
You can set a lock timeout so a migration fails promptly instead of waiting indefinitely. The Wiki gives SET lock_timeout = '5s'; as an example and recommends retrying if the DDL times out. Five seconds is an example, not a universal setting; choose a limit that fits the operation, workload, and migration policy.
A timeout bounds the DDL’s wait; it does not end the transaction holding the conflicting lock or ensure that an immediate retry will succeed. Before cancelling a session or terminating a transaction, identify the blocker and follow your team’s migration and incident procedures. Ending the wrong transaction can interrupt useful work or have application consequences.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What to monitor during a recurring queue
For repeated incidents, monitoring that surfaces long-running transactions, wait events, blocker relationships, and lock state can make the queue easier to diagnose while it is active. PostgreSQL’s built-in pg_stat_activity, pg_blocking_pids, and pg_locks provide the core signals; any monitoring approach should complement, not replace, verification of the live transaction and lock state.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
