Your Database Isn't Slow, It's Waiting: 5 Counter-Intuitive Truths About PostgreSQL Lock Contention

When database latency spikes suddenly, backend engineers routinely jump to standard performance troubleshooting: analyzing execution plans, looking for missing indexes, or trying to refactor seemingly expensive queries. However, this reflex often misdiagnoses the true underlying bottleneck.

The core reality of database performance is that a struggling database often isn't performing complex, CPU-intensive calculations at all—it is simply waiting on a locked resource. Relational databases rely on locks to maintain data consistency during concurrent updates. When these locks are held longer than expected, incoming queries stall, creating a perception of poor execution performance.

Understanding the mechanics of PostgreSQL lock contention, distinguishing waiting states from active execution, and knowing how to diagnose lock dependencies are essential skills for maintaining scalable systems. This article explores five counter-intuitive truths about PostgreSQL lock contention, common diagnostic traps, and operational strategies for prevention.

Takeaway 1: Your "Slow Query" Might Not Be Doing Any Work At All

When a transaction updates a record in PostgreSQL, it acquires a row-level lock to prevent concurrent modifications from corrupting data. As long as that initial transaction remains open, the lock stays active.

There is a fundamental difference between slow query execution and lock contention:

  • Slow Query Execution: The database engine is actively using system resources (CPU, I/O) to process work, such as scanning large tables or performing complex joins.
  • Lock Contention: The query has been sent to the database, but execution cannot proceed because it is waiting for a conflicting lock held by another transaction to be released.

When Transaction A updates a customer record and holds the row lock, subsequent requests—such as Transaction B and Transaction C attempting to update or execute conflicting operations on that same record—are forced to queue up. This creates a cascading backlog of stalled requests.

Crucially, lock contention does not mean that every query touching the table—or even the specific row—will block. PostgreSQL's Multi-Version Concurrency Control (MVCC) architecture ensures that standard read operations (SELECT) do not block on row update locks, and write operations do not block non-conflicting reads. Contention arises strictly when concurrent operations request incompatible lock modes on the same database target—such as multiple concurrent UPDATE or DELETE statements targeting the same tuple, or explicit row-locking clauses like SELECT ... FOR UPDATE.

"A slow query is not always doing expensive work. Sometimes it is simply waiting for someone else's transaction to finish."

Developers frequently misdiagnose lock contention because traditional latency metrics measure elapsed execution time from request start to completion. Because a waiting query remains open while stalled, execution time metrics register it as "slow," masking the reality that the query spent its entire lifespan dormant, waiting for an upstream lock release rather than executing database operations.

Takeaway 2: The Silent Killer Called "Idle in Transaction"

One of the most dangerous states for a PostgreSQL session to enter is state = 'idle in transaction'.

In PostgreSQL, idle in transaction means a backend process has executed a BEGIN statement and potentially run one or more queries, but it is currently waiting for the application client to send the next command. No active SQL queries are being processed on the database server during this pause.

Consider a common production incident sequence:

  1. Transaction A opens a database transaction (BEGIN) and executes an UPDATE on a row, successfully acquiring a row-level lock.
  2. Before sending a COMMIT or ROLLBACK, the application logic performs non-database work—such as executing an external API call, running complex application code, or hitting an unhandled error/pause.
  3. While the application is paused or stalled, the database connection sits in an idle in transaction state. Even though the database session is executing zero queries and consuming virtually no CPU, it continues to hold all acquired row-level locks. Subsequent transactions (Transaction B, Transaction C) attempting to modify the same resource immediately stall, building up latency across the application stack.

Takeaway 3: Increasing Connection Pools Can Make the Incident Worse

When latency rises and requests begin timing out, a common operational impulse is to scale up the application's database connection pool to handle the backlog. During a lock contention incident, this reaction is a trap that often exacerbates the failure.

It is vital to distinguish between these two failure modes:

  • Connection Pool Exhaustion: The application runs out of available database client connections because all pool connections are actively serving legitimate traffic.
  • Lock Contention: Database connections are stalled behind a single blocking transaction that holds a required lock.

When lock contention is the root cause, adding more connections to the pool does not speed up processing. Instead, it simply opens more pathways for incoming application requests to enter the database, attempt the conflicting operation, and join the waiting queue behind the primary blocking process.

Rather than resolving the issue, expanding the pool causes:

  • Rapid occupation of newly added connections.
  • Compounded latency as more application worker threads stall.
  • Cascading application-level timeouts across dependent services.

Takeaway 4: PostgreSQL Has Built-In Detectives for Unmasking Blockers

PostgreSQL provides system views and diagnostic functions to inspect active session states and expose lock contention: pg_stat_activity and pg_locks.

High-Level Inspection with pg_stat_activity

The pg_stat_activity system view offers real-time insight into process states across backend engines. Key fields include:

  • pid: The process ID of the backend session.
  • state: The current status of the session (e.g., active, idle in transaction).
  • query_start: The timestamp when the currently executing query began.
  • xact_start: The timestamp when the current transaction started.
  • wait_event_type & wait_event: Indicates if and why a backend is waiting (e.g., wait_event_type = 'Lock').
  • query: The text of the most recent or currently active query.

Deep Lock Inspection with pg_locks

While pg_stat_activity shows session status, pg_locks exposes the exact lock hierarchy and resource targets. pg_locks details every lock held or awaited by active processes, categorized by locktype (such as relation, tuple, or transactionid), lock modes (e.g., RowExclusiveLock acquired during updates versus AccessExclusiveLock acquired during schema migrations), and the Boolean status granted.

When diagnosing complex lock trees, joining pg_locks with pg_stat_activity allows database architects to map explicit target dependencies. By filtering for records where granted = false, engineers can identify blocked processes, trace their required target resource (relation or tuple), and match it to the session where granted = true for that exact resource and conflicting lock mode.

Query 1: Identifying Blocking and Blocked Sessions 

SELECT pid, pg_blocking_pids(pid) AS blocking_pids, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

Query 2: Tracking Idle Transactions Holding Locks 

SELECT pid, state, xact_start, now() - xact_start AS transaction_duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;

Takeaway 5: Session Termination Is a Last Resort, Not a Solution

When a blocking process brings down database throughput, forcefully terminating the backend session (e.g., via pg_terminate_backend) is often attempted. However, session termination carries distinct architectural risks and must not be treated as a default operational fix.

Risks of Terminating Sessions

  • Transaction Rollbacks: Terminating a session forces an immediate rollback of the active transaction, which can leave application workflows in inconsistent or uncoordinated states.
  • Application-Side Consequences: The client application communicating over that connection receives sudden connection errors, potentially leading to unhandled retries or broken state machines.

Prevention Strategies and Operational Safeguards

Rather than relying on manual intervention during outages, production environments should enforce operational safeguards:

  • Keep Transactions Short: Scope transactions strictly to essential database operations to minimize lock hold duration.
  • Remove External Logic from Transactions: Avoid making external API calls or executing non-database application logic inside open database transactions.
  • Explicit Transaction Termination: Ensure client application code paths guaranteed execution of explicit COMMIT or ROLLBACK commands under all conditions, including error states.
  • Configure Transaction Timeouts: Use built-in database timeouts to automatically mitigate abandoned transactions. Specifically, configure idle_in_transaction_session_timeout to automatically terminate sessions that remain idle within an open transaction beyond a specified threshold.
  • Proactive Monitoring: Instrument continuous monitoring for transaction age, lock wait counts, and blocking sessions before they escalate into outages.
  • Establish Operational Protocols: Create strict, documented procedures before manually terminating any database backend process.

Conclusion: The Mental Shift Every Engineer Needs

Managing relational database performance effectively requires a fundamental distinction between query execution efficiency and concurrency management. Adding indexes and rewriting SQL queries will not resolve system slowdowns if the database spends its time waiting on concurrent lock holders.

Before writing a new index or refactoring query logic during a latency incident, engineers should adopt a simple diagnostic step:

Before optimizing a slow query, check whether it's actually waiting for a lock.

Originally published on gokairo.dev.