Slow database? How to audit locks and deadlocks in PostgreSQL

Why does PostgreSQL suddenly freeze in production environments?

When an application suffers a sudden performance drop or stops responding entirely, the database is usually the primary suspect. Most of the time, the issue is not a lack of processing power, but rather an invisible queue of transactions competing for the exact same data. In practice, this means a simple command got stuck waiting indefinitely because another process held the key to a table without releasing it.

To understand this behavior, we need to look at the concept of locks, which work just like a deadbolt on a door. When a transaction modifies a row or a table, PostgreSQL applies a lock to ensure no one else touches that data until the work finishes. If another process tries to access the same record, it must wait patiently for release. The trouble begins when this waiting period becomes endless, creating a cascade effect that paralyzes the entire system.

In high-load environments, identifying these bottlenecks quickly is the difference between keeping the business online and facing a major operational outage. To mitigate risks and better understand overall performance, many engineers combine this check with the routine of evaluating slow queries, as we detail in our guide on how to audit queries in PostgreSQL. Knowing exactly where the queue jams prevents connections from exceeding acceptable server limits.

How the pg_stat_activity table reveals active database states

PostgreSQL has fantastic native tools for inspecting its own internal state without requiring complex external packages. The primary diagnostic tool for this scenario is a system table called pg_stat_activity. In practice, it works like a real-time dashboard showing exactly what each connection is executing at that precise microsecond.

Every row in this table represents an active or waiting process in the database. We can see the connected user, the source IP address, the client application, the query start timestamp, and, most importantly, the current transaction state. If a process is marked with a lock-wait condition, we immediately know it encountered an impediment created by another connection in the cluster.

Before investigating deep lock contentions, it is worth remembering that many availability issues stem from poorly configured limits, a topic we cover extensively when discussing how to tune your connection pool. An overcrowded connection pool generates unnecessary contention and leaves room for locks to persist much longer than they should in the infrastructure.

Step-by-step guide to querying stuck transactions and locks

When the application starts rejecting new requests due to timeouts, we need to act with surgical speed. The procedure below demonstrates a safe and highly efficient SQL query to list processes holding locks and those stuck waiting in line.

1. Open your preferred database client connected as a superuser or with monitoring privileges on the PostgreSQL instance.
2. Copy the lock inspection query to identify the process identifier of both the blocker and the blocked session.
3. Execute the SQL command below in your database terminal to list active locking traces.

SELECT bl.pid AS blocked_pid, us.usename AS blocked_user, k.query AS blocked_query, ex.pid AS blocker_pid, us2.usename AS blocker_user, ex.query AS blocker_query FROM pg_catalog.pg_locks bl JOIN pg_stat_activity k ON k.pid = bl.pid JOIN pg_catalog.pg_locks b ON b.locktype = bl.locktype AND b.database IS NOT DISTINCT FROM bl.database AND b.relation IS NOT DISTINCT FROM bl.relation AND b.page IS NOT DISTINCT FROM bl.page AND b.tuple IS NOT DISTINCT FROM bl.tuple AND b.virtualxid IS NOT DISTINCT FROM bl.virtualxid AND b.transactionid IS NOT DISTINCT FROM bl.transactionid AND b.classid IS NOT DISTINCT FROM bl.classid AND b.objid IS NOT DISTINCT FROM bl.objid AND b.objsubid IS NOT DISTINCT FROM bl.objsubid AND b.pid != bl.pid JOIN pg_stat_activity ex ON ex.pid = b.pid JOIN pg_user us ON us.usesysid = k.usesysid JOIN pg_user us2 ON us2.usesysid = ex.usesysid WHERE NOT bl.granted;

This command crosses active lock tables with system activity logs to reveal precisely who is causing the traffic jam. The output provides the identifier code, known as PID, for both the blocking process and the blocked process, enabling an informed decision on whether to interrupt the culprit.

How to safely resolve or terminate problematic transactions

Once you identify which process is choking the database, the inevitable question arises about what to do with it. In some cases, the stuck transaction is just a heavy report that will finish in a few more minutes, but in other scenarios, it is a code bug that will never finish on its own. In practice, deciding between waiting and dropping the connection requires weighing the impact on end users.

If the decision is to terminate the transaction to restore system health, PostgreSQL provides specific commands to cancel or kill the process without corrupting data files. The pg_cancel_backend command sends a polite signal for the query to cancel its current execution and rollback the pending transaction. If the process ignores the warning, the pg_terminate_backend command shuts down the connection immediately and decisively.

It is crucial to use these tools with surgical caution on production servers. Dropping a transaction mid-way through an important write operation can trigger considerable cleanup work, known as a rollback, which also consumes disk and CPU resources. The golden rule is always to check if the transaction is stuck due to application logic flaws before applying any forced interruption. To explore these architectural concepts further in production, see our related technical analysis on How to test motherboard power on circuit with multimeter.

Final considerations on preventing availability outages

Auditing locks and deadlocks proactively is an essential habit to ensure the stability of large-scale production systems. Keeping monitoring scripts ready for use in pg_stat_activity drastically reduces the mean time to response during critical incidents. The secret to a healthy operation lies in combining clean code, proper exception handling, and strict timeout limits for orphaned transactions.

Investing time in educating the engineering team on how transactional locks work prevents recurring bottlenecks from taking down the service again. With a clear strategy for inspection and rapid response, the database ceases to be an unpredictable black box and becomes a predictable, transparent, and resilient component for business growth.