Free tools Windows power users keep installed
One-click scans. No signup required.
Yes. In PostgreSQL, a long-running SELECT can hold a lock that makes ALTER TABLE wait. Once that DDL request is queued, later queries that need the same table may wait behind it too. If you’re asking, “Why are all my queries stuck after an ALTER TABLE?”, those later queries may be caught in a lock queue—not running slowly on their own.
How one SELECT can hold up the queue
A plain SELECT takes an ACCESS SHARE lock on the table it reads. Many ALTER TABLE operations request ACCESS EXCLUSIVE, which conflicts with that lock. If the SELECT’s transaction remains open, the DDL must wait for its lock to be released.
As an Amazon Associate I earn from qualifying purchases.
The queue can then affect queries that arrive later. The PostgreSQL Wiki’s operations example explains that later requestors respect earlier waiters rather than overtaking them. So a new SELECT may be waiting behind the queued ALTER TABLE even though its own requested lock would normally coexist with the original SELECT. The symptom is a queueing incident, not proof that every waiting query is independently slow. The exact behavior depends on the operation, transaction state, workload, and PostgreSQL version.
The PostgreSQL Wiki’s lock-monitoring example recommends scheduling DDL for off-peak periods and/or bounding how long it waits for a lock.
#1 Best Overall
Find the waiter and its blockers
Inspect the database while the incident is happening: activity views can show query state and wait events, and pg_blocking_pids(pid) identifies processes blocking a waiting backend. For example:
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;This is an example assembled from PostgreSQL’s documented facilities, not a tested incident-specific script. Adapt the database filter and selected columns to your environment and PostgreSQL version; access to activity details can also depend on permissions.
PostgreSQL’s activity statistics documentation says that an active backend with a non-null wait_event is executing a query but blocked somewhere in the system. A wait event helps establish that a backend is waiting; it does not alone identify the blocker. The activity view is also not a perfectly synchronized snapshot, so related fields can briefly appear inconsistent while state changes.
Follow the blocker PID
For each PID in blocking_pids, find the corresponding row in pg_stat_activity. Check its query and transaction start time, along with its state, to understand whether the blocker is still working or appears to have left a transaction open. A long-running transaction can retain its locks after its last statement has finished.
Use pg_locks for lock details
The pg_locks view shows outstanding locks, including lock types, target relations, and whether a lock is granted. It is useful for examining the lock state, but it is not by itself a complete blocker graph. PostgreSQL cautions that reconstructing blockers by joining pg_locks is difficult because the logic must account for both lock conflicts and queue order. Use pg_blocking_pids(pid) for blocker identification, then consult pg_locks for details.
Check for prepared transactions
If the visible sessions and lock rows do not explain the contention, check whether a prepared transaction is holding locks. Prepared transactions can persist without a corresponding ordinary session in pg_stat_activity, so there may be no session PID to follow. PostgreSQL’s lock-monitoring documentation describes this limitation.
Choose a mitigation that fits the incident
Schedule planned DDL for a quieter period
Running schema changes off-peak can reduce the chance that the migration’s lock request will build a queue during busy traffic. It does not remove the need to understand long transactions or guarantee that the lock will be available; it is a scheduling choice, not a blocker-resolution mechanism.
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 the DDL lock wait
For a migration where waiting indefinitely is worse than retrying later, PostgreSQL’s Wiki gives this example:
Best Value
SET lock_timeout = '5s';Five seconds is the Wiki’s example, not a universal setting. Choose a limit appropriate to the migration and application, and confirm how your migration runner handles statement failure. A lock timeout can make the DDL fail promptly rather than wait indefinitely, but it does not end the original transaction or ensure an immediate retry will succeed.
Do not cancel a session by guesswork
Before cancelling a query or terminating a backend, identify what it is doing and follow your team’s migration and incident procedures. Ending a session can have application and transaction consequences; the lock views identify a relationship, not whether intervention is safe. If you cannot explain the blocker from ordinary sessions, include prepared transactions in the investigation.
Keep future lock queues visible
For recurring incidents, monitoring that tracks long-running transactions, lock waits, and blocked sessions can help surface the queue before it affects more requests. PostgreSQL’s built-in activity and lock views are the starting point; an external monitoring or database-observability service is optional, not required for the diagnostics above.
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.
