Decoding PostgreSQL’s Most Misunderstood Parameter: The Hidden Realities of max_locks_per_transaction
For years, database administrators operating the world’s most popular open-source relational database have chased ghosts in their configuration files. Among the most misunderstood directives in the postgresql.conf ecosystem is a setting that has tripped up production systems since version 7.2: max_locks_per_transaction.
Despite its name implying a hard ceiling per individual transaction, this parameter does no such thing. Instead, it acts as a foundational sizing metric for a shared, cluster-wide lock table. Understanding its true mechanics is no longer just a esoteric optimization trick; it has become an urgent operational imperative following architectural shifts introduced in PostgreSQL 19.
Main Facts: What max_locks_per_transaction Actually Does
To properly manage a high-performance database cluster, DBAs must unlearn the intuitive assumptions tied to the name max_locks_per_transaction.
The setting does not limit how many locks a single transaction can hold. In practice, a lone transaction can consume a vast majority of the global lock table—provided that other concurrent processes are not actively claiming their shares. The official documentation’s historical description—calling it "the average number of object locks used by each transaction"—is closer to reality, but it fails to capture the mathematical reality of how shared memory is allocated.
What the parameter actually dictates is the sizing of the shared hash table responsible for heavyweight locks. The postmaster calculates the table’s capacity by multiplying max_locks_per_transaction by the total number of process slots it could conceivably hand out. Under standard configurations in PostgreSQL 18, this includes:
max_connectionsautovacuum_worker_slotsmax_worker_processesmax_wal_sendersmax_prepared_transactions- Two additional slots for the autovacuum launcher and slot-sync worker.
At default settings on PostgreSQL 18, this amounts to 136 slots. Multiplied by the default max_locks_per_transaction value of 64, the cluster reserves 8,704 lock entries. A secondary hash table, designated for per-holder records, is automatically sized at twice that capacity on the assumption of two holders per lock.
It is vital to note that these entries govern objects—such as relations, pages, transaction IDs, and advisory lock keys—rather than individual rows. Row-level locks are maintained directly within the tuples themselves and bypass this parameter entirely. Similarly, weak relation locks that fit comfortably within a backend’s fast-path array never touch the main shared lock table.
When the shared lock table inevitably fills up, the database engine throws a misleading error: out of shared memory, accompanied by the hint, You might need to increase "max_locks_per_transaction". While the hint is accurate, the primary error message misleads engineers into believing they have exhausted standard shared_buffers, when they have simply run out of lock entries.
Chronology: The Evolution of a Misleading Architecture
The confusion surrounding max_locks_per_transaction has deep historical roots, tracing back through more than two decades of database engineering.
The Legacy Eras (PostgreSQL 7.2 to 18)
Since the early days of the modern database engine, the two heavyweight lock hash tables drew their memory allocations from a general, elastic pool of shared memory. This pool held a baseline space reserved for locks, plus whatever dynamic buffer space happened to be left over from other unallocated structures in the shared memory segment.
Consequently, the "real" capacity of a cluster’s lock table was rarely just the nominal mathematical product. It included an undocumented, fluctuating bonus capacity dictated by the rest of the server’s configuration load. This opaque design allowed poorly configured systems to limp along without crashing, masking underlying capacity shortfalls until a massive query, backup routine, or schema migration exposed the deficit.
The PostgreSQL 19 Shift
During the development cycle of PostgreSQL 19, core contributor Heikki Linnakangas systematically dismantled this legacy padding. Linnakangas removed the historical safety margins, permanently settling the memory split between the two lock tables at startup.
To compensate for the elimination of the dynamic safety net, the PostgreSQL development team doubled the default value of max_locks_per_transaction from 64 to 128, starting with version 19. This architectural pivot transformed the parameter from a soft, cushioned guideline into a rigid, explicit allocation.
Supporting Data: Benchmarks and Memory Footprints
The structural changes in PostgreSQL 19 alter how administrators must audit their systems. Empirical testing utilizing AccessExclusiveLock stress tests on individual tables until failure illustrates the divergence between nominal configurations and real-world capacity:
| Configuration Parameter | Nominal Calculation | PostgreSQL 18.6 Real Capacity | PostgreSQL 19 Beta 3 Real Capacity |
|---|---|---|---|
max_locks_per_transaction = 64 |
8,704 entries | 14,872 entries | 8,701 entries |
max_locks_per_transaction = 128 |
17,408 entries | 24,000 entries (test ceiling) | 17,405 entries |
The data reveals that PostgreSQL 18’s dynamic memory pooling allowed systems to absorb significantly more locks than their nominal math suggested. In contrast, PostgreSQL 19 strips away this padding, making the configured number exact.
Furthermore, memory usage is now transparently exposed. Administrators running PostgreSQL 19 can query pg_shmem_allocations to view the LOCK hash and PROCLOCK hash structures at their true operating sizes—consuming approximately 370 bytes per entry combined. In version 18, this same memory footprint was obscured within anonymous shared memory allocations (<anonymous>).
To put this into perspective, configuring max_locks_per_transaction = 1024 alongside max_connections = 300 results in an allocation of roughly 120MB of RAM. In modern enterprise environments, this is a minor price to pay for robust insurance against catastrophic failure during peak operational loads.
Official Responses and Operational Scenarios
Production systems typically encounter lock table exhaustion under three distinct operational scenarios:
1. Massive Single Transactions
When a single transaction touches an extraordinarily large number of relations, it threatens the lock table. A prime example is pg_dump, which acquires an AccessShareLock on every table it encounters within a single overarching transaction. A schema housing more tables than the lock table has entries will fail to back up under default configurations. Similarly, bulk data definition language (DDL) operations—such as custom scripts creating or dropping thousands of tables via DO blocks, DROP SCHEMA ... CASCADE, or DROP OWNED BY—routinely overwhelm the shared lock table.
2. Heavy Partitioning
Modern enterprise databases rely heavily on table partitioning to manage massive datasets. However, the query planner must lock every single partition it considers, alongside every index on every partition.
A query executing against a 3,000-partition table equipped with a single index per partition will instantly consume 6,002 relation locks (comprising the partitions, their respective indexes, the parent table, and the parent’s index)—regardless of whether query pruning ultimately discards most of those partitions during execution.
Crucially, because the lock table is shared cluster-wide, when two such queries run concurrently, they collide. The transaction that receives the fatal error is simply whichever process requested its locks last—not necessarily the transaction holding the lion’s share of the entries.
3. Distributed Concurrency
The final common failure vector is a high volume of concurrent backend processes each holding a moderate number of locks. This represents scenarios one and two distributed thinly across the entire cluster, reflecting the reality behind the historical "average per transaction" documentation.
Implications: Best Practices for Database Administrators
As organizations prepare to migrate production infrastructure to PostgreSQL 19 and beyond, database reliability engineers must adopt a proactive stance toward lock management.
Address Standbys First
Replication standby servers present a unique hazard. A standby’s lock table must be capable of holding every AccessExclusiveLock that the primary cluster generated, because the WAL replay process must mirror those locks faithfully.
If a standby boots up with a lower parameter value than the primary, it will immediately refuse to start, logging:
FATAL: recovery aborted because of insufficient parameter settings.
Even more perilous is the scenario where a running standby’s primary server is restarted with an increased parameter value. In this case, the standby logs hot standby is not possible because of insufficient parameter settings and pauses recovery. It continues serving read queries against a static, frozen snapshot while replication lags behind. Attempting to unpause it directly via administrative commands will force an emergency shutdown.
The Golden Rule: Always upgrade standby parameters and restart them before modifying and restarting the primary cluster.
Monitoring and Tuning Strategies
While native systems lack a dedicated view that directly reports real-time lock-table occupancy percentage, administrators can construct effective monitoring queries. By comparing the output of:
SELECT count(*) FROM pg_locks WHERE NOT fastpath;against the calculated product of max_locks_per_transaction and total process slots during peak operational windows (such as nightly backups or partition-maintenance routines), teams can establish a safety baseline.
In PostgreSQL 19, the new pg_stat_lock.fastpath_exceeded metric explicitly tracks every lock that spills out of the fast-path optimization array and into the shared heavyweight table. A rising counter on a heavily partitioned workload serves as a definitive warning indicator to scale up the parameter.
When calculating the optimal setting, administrators must evaluate two distinct constraints and adopt the larger requirement:
- The Shared Table Constraint: The configured value multiplied by total process slots must comfortably clear the cumulative lock count of the cluster’s worst concurrent operational moment (e.g., a massive backup running alongside routine maintenance tasks), complete with a healthy safety margin.
- The Fast-Path Constraint (v18 and later): The parameter value itself must exceed the total number of relations and indexes touched by the cluster’s hottest, most complex queries.
For any environment leveraging extensive table partitioning, both constraints point naturally toward values of 1,024 or higher. Given modern hardware capacities, the memory overhead is negligible.
Finally, engineers carrying forward configurations from PostgreSQL 18 must heed the official release notes: double any explicit settings when upgrading to PostgreSQL 19. Neglecting this step risks an accidental 40% reduction in practical lock capacity, turning routine maintenance windows into unexpected production outages.
