High PostgreSQL latency? How to inspect locks and queries

What happens when PostgreSQL freezes unexpectedly?

When an application suffers from sudden performance drops, the database is usually the first place to investigate. Often, the slowness is not caused by a lack of hardware, but rather by transactions that refuse to finish. In practice, this means a slow or poorly structured SQL query has locked the data, preventing other legitimate requests from reading or writing.

To understand this behavior, imagine a bank teller line where one customer forgets documents on the counter and stops to chat. Nobody else can be served. In PostgreSQL, this behavior happens because of concurrency mechanisms that ensure two operations do not modify the same record at the same time. When these locks cross paths with no exit, we get the famous deadlock, where two processes wait for each other indefinitely.

Before investigating complex infrastructure issues, it is worth crossing this information with detailed slow query metrics using pg_stat_statements, as many bottlenecks stem from queries running without proper indexes on main tables.

How to see what is running right now in the database

PostgreSQL maintains a system table called pg_stat_activity. It works like a real-time control panel, showing exactly what each connection is doing at that millisecond. Each row represents a connected process, displaying the user, accessed database, current SQL command, and transaction state.

To check active queries taking longer than usual, we can run a simple command filtering out idle processes. This gives us an immediate view of who is consuming server resources and holding precious connections that could be serving other system users.

SELECT pid, usename, client_addr, state, query, backend_start ⏎FROM pg_stat_activity ⏎WHERE state <> 'idle' AND pid <> pg_backend_pid();

In this code block, the pid column indicates the unique operating system process identifier, while the query column shows the exact text of the executing SQL command. The filter prevents our own query from appearing in the results, keeping the listing clean and focused only on real application resource hogs.

How to track transactions stuck by locks

Discovering which query is running is useful, but the real challenge arises when a query is stuck waiting for a lock. PostgreSQL manages simultaneous access through a locking mechanism called locks. When a process tries to alter a row that another process has already modified, it enters a waiting state, freezing the application at that specific point.

To map who is locking whom, we need to cross information from pg_stat_activity with the pg_locks table. This combined view reveals which process holds the lock and which processes are waiting in line patiently (or impatiently) for the resource to be released.

SELECT blocked_locks.pid     AS blocked_pid, ⏎       blocked_activity.usename  AS blocked_user, ⏎       blocking_locks.pid    AS blocking_pid, ⏎       blocking_activity.usename AS blocking_user, ⏎       blocked_activity.query    AS blocked_statement ⏎FROM pg_catalog.pg_locks          blocked_locks ⏎JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid ⏎JOIN pg_catalog.pg_locks          blocking_locks ⏎  ON blocking_locks.locktype = blocked_locks.locktype ⏎  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database ⏎  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation ⏎  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page ⏎  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple ⏎  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid ⏎  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid ⏎  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid ⏎  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid ⏎  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid ⏎  AND blocking_locks.pid <> blocked_locks.pid ⏎JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid ⏎WHERE NOT blocked_locks.granted;

This advanced query joins multiple lock identification fields to ensure we are looking precisely at the real dependency between processes. The result lists the blocked process and the culprit process, allowing the administrator to identify the source of the lockup within seconds without guessing in the dark.

How to safely terminate problematic connections

As soon as the villain of the story is discovered through previous queries, action is required. In production, we often need to stop a transaction that has frozen the system. However, shutting down the entire database because of one bad query is out of the question. The correct solution is to cancel only the specific process causing the damage.

PostgreSQL offers two native functions to handle this: pg_cancel_backend and pg_terminate_backend. The first sends a polite request for the process to cancel the current query while keeping the connection open. The second is more forceful, cutting the entire connection immediately and forcing a rollback of any pending transaction.

-- To politely request cancellation of the current query: ⏎SELECT pg_cancel_backend(12345); ⏎⏎-- To drop the connection outright if cancellation fails: ⏎SELECT pg_terminate_backend(12345);

Replace the number 12345 with the actual PID obtained from previous queries. Using these functions requires database administrative privileges. Always prefer gentle cancellation, as hard termination may leave the client application confused until it reconnects and restores the correct logical state. To complement this implementation in production environments, see our related technical analyses on Container crashing in Kubernetes? How to inspect logs with stern and SSL certificate expiring? How to automate renewal in Nginx.

Final thoughts on health and continuous monitoring

Identifying locks and active queries should not be an exclusive task for moments of crisis. When the system experiences recurring slowness, frequent use of PostgreSQL system tables turns engineering work into a predictable and controlled routine. Keeping inspection scripts ready to use speeds up diagnosis in high-criticality environments.

In addition, it is worth setting up alarms based on transaction execution time and periodically reviewing table and index design. If a concurrency issue repeats frequently, the bottleneck usually points to an architectural design that needs refactoring. With preventive monitoring, the database stops being a mysterious black box and becomes a transparent and stable infrastructure component.