PostgreSQL Lock Contention: The Root Cause and Solutions for High-Concurrency Scalability Bottlenecks

Why Locks Become a Performance Ceiling

PostgreSQL has long been regarded as one of the most reliable and feature-complete open-source relational databases. Yet as businesses scale and concurrent connection counts skyrocket, more and more engineering teams have stumbled into the same pitfall—PostgreSQL's locking mechanism exhibits clear scalability bottlenecks under high-concurrency workloads.

This topic has sparked extensive discussion on Hacker News, with the core argument cutting straight to the heart of the matter: when the system faces a large volume of concurrent transactions, PostgreSQL's locks don't scale linearly with the number of CPU cores or connections—instead, they can become a ceiling on the entire system's throughput. This article takes a deep dive into the root causes of this problem and compiles actionable mitigation strategies.

The Fundamentals of PostgreSQL's Locking Mechanism

A Multi-Layered Lock System

PostgreSQL internally maintains a complex lock system to coordinate concurrent access, roughly divided into three levels:

Table-level locks: Such as ACCESS SHARE, ROW EXCLUSIVE, etc., which coordinate access to an entire table.
Row-level locks: Used when updating or deleting specific rows, with finer granularity.
Lightweight locks (LWLocks): Used to protect internal data structures in shared memory—the core of high-concurrency scalability problems.

Table-level and row-level locks are the concepts developers are most familiar with, but what truly triggers scalability issues under high concurrency is often the user-invisible LWLocks and spinlocks.

The Underlying Mechanics of LWLocks and Spinlocks

LWLocks (lightweight locks) are synchronization primitives in the PostgreSQL kernel designed specifically to protect shared-memory data structures. They have lower overhead than heavyweight locks, but can still become bottlenecks under high concurrency. LWLocks support a shared mode (multiple readers can hold them in parallel) and an exclusive mode (a single writer holds it exclusively), and they proactively suspend a process while waiting to avoid needlessly consuming CPU. Spinlocks are even lower-level—essentially a busy-wait loop where a process continuously burns CPU cycles while waiting for the lock to be released, rather than voluntarily yielding the CPU. This means that on multi-core servers, having a large number of processes simultaneously spin-waiting on the same lock leads to a "pseudo-busy" state where CPU utilization appears inflated but effective compute throughput is extremely low. PostgreSQL is already quite conservative in its use of spinlocks internally, but on certain extremely hot code paths (such as the buffer manager's buffer header locks), spinlock contention remains an observable source of performance loss.

Shared Memory Contention: The Real Starting Point of the Problem

PostgreSQL uses a multi-process architecture, where each connection corresponds to a separate backend process. These processes need to frequently access common data structures in shared memory—the lock manager's hash tables, buffer management information, and so on. To ensure consistency, a process must acquire the corresponding lightweight lock before accessing these structures.

When hundreds or thousands of processes simultaneously contend for the same set of shared-memory structures, lock contention escalates sharply. CPU cores require extensive communication to synchronize cache-line states (a phenomenon also known as "cache line bouncing"), and effective compute time is severely diluted.

The Real-World Manifestations of Scalability Bottlenecks

Throughput Drops Instead of Rising

Ideally, adding CPU cores or increasing concurrency should yield roughly linear throughput growth. But in scenarios with severe lock contention, the reality is often:

After concurrency exceeds a certain threshold, throughput plateaus;
As concurrency continues to increase, throughput actually declines;
A large amount of CPU time is consumed by spin-waiting on locks and context switching, rather than by actual query processing.

In the database world, this phenomenon is called "negative scaling," and it's one of the thorniest problems in large-scale PostgreSQL deployments.

The Theoretical Background of Negative Scaling

The phenomenon of "negative scaling" can be rigorously explained using Amdahl's Law and the USL (Universal Scalability Law). Proposed by computer scientist Neil Gunther in the 1990s, USL builds on Amdahl's Law by introducing a "coherency penalty" term—as concurrent resources (such as the number of processes or CPU cores) increase, the communication overhead required to synchronize shared state between nodes grows super-linearly, eventually causing overall throughput to fall rather than rise. Intuitively: suppose the system has N concurrent processes; the coherency overhead is proportional to N², while the effective workload is only proportional to N. Once N exceeds a certain critical value, the coherency overhead completely dominates the system's behavior. PostgreSQL's shared-memory lock contention is a textbook manifestation of this theory in a real-world system—each additional backend process not only contributes work capacity but also imposes an extra synchronization burden on all existing processes.

Typical High-Risk Scenarios

The following types of workloads are most likely to trigger lock scalability problems:

Extremely high connection counts: Thousands of concurrent connections directly connecting to the database, causing LWLock contention to surge.
Hot-row updates: A large number of transactions concurrently updating the same row or a few rows, causing row-level locks to queue up continuously.
Frequent DDL and metadata operations: Lock contention involving system tables such as pg_class and pg_attribute.
Short-transaction storms: Tens of thousands of small transactions per second, where the overhead of acquiring and releasing locks is greatly amplified as a proportion of total work.

The Deeper Root Causes of the Bottleneck

The Cost of the Multi-Process Model

PostgreSQL's multi-process architecture has natural advantages in stability and process isolation—the crash of a single process won't bring down the entire database. But the cost is a higher synchronization overhead for shared state between processes. Compared to the threading model (such as the approach adopted by MySQL InnoDB), shared-memory access under the process model requires stricter lock protection and is more prone to contention on high-core-count servers. It's worth noting that the PostgreSQL community is actively pursuing a long-term improvement plan to "migrate some internal mechanisms to finer-grained locks," but constrained by architectural legacy, this evolution is incremental.

The Amplifying Effect of Connection Counts

Each PostgreSQL connection not only consumes memory (typically several MB of stack space and a shared-memory slot) but also participates in lock contention on various shared data structures—including the ProcArray (the process array used for visibility determination), the LockManager hash tables, and so on. This is precisely the fundamental reason why connection count management is a core concern of PostgreSQL operations—even a large number of idle connections, simply by existing, impose an extra burden on the lock system along critical paths such as snapshot acquisition and lock checking.

Coping Strategies and Practical Recommendations

Introduce Connection Pooling to Control the Number of Backend Processes

This is the most immediately effective measure. Using PgBouncer or Pgpool-II to consolidate thousands of client connections into dozens to hundreds of actual database connections can significantly reduce the number of backend processes, thereby directly alleviating lock contention.

For transactional workloads, it's recommended to enable PgBouncer's transaction pooling mode—with the same number of database connections, it can serve more concurrent clients.

How PgBouncer Connection Pooling Works

PgBouncer is the most commonly used lightweight connection-pooling middleware in the PostgreSQL ecosystem, known for its single-process, asynchronous-I/O architecture and extremely low resource overhead. It supports three connection modes suited to different scenarios: In session pooling, a client connection maps one-to-one with a backend connection and isn't released until the session ends—suitable for long sessions but nearly incapable of reducing the number of backend connections. Transaction pooling is the most recommended mode: a database connection is occupied only during transaction execution and is returned to the pool immediately after the transaction commits or rolls back, theoretically allowing 100 backend connections to support thousands of concurrent clients. Statement pooling has the finest granularity but imposes strict limitations on transaction semantics, making it suitable only for autocommit scenarios without explicit transactions. Note that in transaction pooling mode, features that depend on session state (such as the SET command, prepared statements, and temporary tables) are restricted, requiring corresponding adaptation at the application layer.

Optimize Transaction Design and Lock Granularity

Shorten transaction duration: Release locks as quickly as possible to reduce queuing waits for other transactions.
Distribute hot-row pressure: Through sharding, counter bucketing, and similar techniques, distribute concurrent updates to a single hot row across multiple rows, then aggregate them at the end—avoiding continuous row-level lock queuing.
Design indexes sensibly: Reduce unnecessary full-table scans and the scope of lock acquisition.
Use SELECT ... FOR UPDATE SKIP LOCKED: In queue-type scenarios, skip already-locked rows to avoid a pileup of lock waits.

Keep Up with Version Upgrades

The PostgreSQL community continually improves lock scalability in every major release. Critical paths such as the LWLock implementation, buffer management, and snapshot acquisition have gone through multiple rounds of optimization. For example, PostgreSQL 14 restructured the ProcArray access pattern, significantly reducing snapshot-acquisition overhead under high concurrency; PostgreSQL 16 further optimized the lock granularity of the WAL write path. Upgrading to a newer stable version can often deliver considerable concurrency performance gains at minimal cost, making it one of the most cost-effective optimization measures available.

Horizontal Scaling at the Architectural Level

When a single-instance lock bottleneck can't be overcome through tuning, you need to seek a way out at the architectural level:

Read-write splitting: Divert read traffic to read-only replicas to reduce pressure on the primary. PostgreSQL's streaming replication supports both synchronous and asynchronous modes, which can be flexibly configured according to consistency requirements.
Horizontal sharding: Achieve distributed deployment with extensions like Citus, distributing lock contention across multiple independent nodes.

How the Citus Distributed Extension Works

Citus is a distributed extension for PostgreSQL (now part of the Microsoft Azure ecosystem while remaining open source). By horizontally partitioning data across multiple worker nodes based on a shard key, each node holds only part of the data and independently manages its own lock state, fundamentally breaking through the ceiling of single-node lock contention. The coordinator node is responsible for parsing queries, routing them to the appropriate shards, and aggregating the returned results—essentially transparent to the application layer, since applications still connect to the coordinator via the standard PostgreSQL protocol without needing to be aware of the underlying sharding details. Citus is especially well suited to multi-tenant SaaS scenarios (using tenant ID as the shard key) and time-series data scenarios (using timestamps as the shard key). In these scenarios, the vast majority of queries need to access only a single shard, so lock contention is naturally isolated at the node level.

Recognize the Boundaries and Respond Rationally

The scalability issues of PostgreSQL's locking mechanism are not a reason to dismiss it, but rather a sobering reminder: every database has its performance boundaries. A deep understanding of lock behavior under high-concurrency scenarios is a required course for building large-scale systems.

For the vast majority of workloads, PostgreSQL is fully capable of handling high-load scenarios through connection pooling, transaction optimization, and version upgrades. And when a business truly approaches the limits of a single machine, architectural-level planning must be done in advance. Rather than reacting passively when the throughput curve "peaks and falls back," it's far better to factor lock contention into the design from the very beginning of the system—preventing problems before they arise.