Cracking the Hood on PostgreSQL’s max_pred_locks_per_transaction: Anatomy of a Hidden Bottleneck

As high-concurrency database architectures push the limits of data integrity, Serializable Snapshot Isolation (SSI) remains a cornerstone for maintaining absolute ACID compliance in PostgreSQL. However, beneath the hood of PostgreSQL’s robust isolation engine lies a legacy configuration parameter that frequently trips up database administrators: max_pred_locks_per_transaction.

Carrying forward a nomenclature and sizing philosophy from earlier architectural eras, this parameter governs the predicate lock tables essential for SSI. Recent analysis into PostgreSQL 18 and the upcoming PostgreSQL 19 beta 3 reveals that its default settings are fundamentally misaligned with modern workloads. When systems hit this limit, they do not throw standard serialization retry errors—they crash out with catastrophic shared memory allocation failures.

Main Facts

At its core, max_pred_locks_per_transaction dictates the baseline allocation for PostgreSQL’s internal SSI predicate lock tables. Despite its misleading name, the parameter does not impose a limit on a per-transaction basis. Instead, it acts as a multiplier against the total number of process slots capable of holding a serializable transaction—including maximum concurrent connections, background worker slots, autovacuum processes, and replication senders.

The primary technical realities of this parameter include:

• The Sizing Miscalculation: The default value is set to 64, a figure that has remained unchanged since SSI was first introduced in PostgreSQL 9.1. PostgreSQL 19 beta 3 preserves this default while simultaneously doubling max_pred_locks_per_transaction‘s data sibling, max_locks_per_transaction.
• Persistent Lifecycles: Unlike standard locks that evaporate the moment a transaction concludes, SIREAD (Serializable Isolation Read) locks persist until every overlapping serializable transaction has finished. Consequently, the tracking tables hold artifacts of the recent past alongside active operations.
• The Memory Crash Exception: When the predicate lock tables fill up, PostgreSQL raises a fatal ERROR: out of shared memory (SQLSTATE 53200) rather than a standard retryable serialization failure (40001). Application-level retry loops cannot catch this error.
• Minimal Memory Footprint: At a default of 64, the memory allocation is under 2 megabytes. Even scaling this parameter up tenfold (640) generally consumes only a modest additional slice of shared system memory.

Chronology

The structural challenges surrounding predicate locking in PostgreSQL are deeply rooted in the platform’s development history:

• PostgreSQL 9.1 (The SSI Milestone): PostgreSQL introduces Serializable Snapshot Isolation, bringing true serializability without requiring explicit locking overhead. The developers establish max_pred_locks_per_transaction with a default value of 64, calibrated for the connection densities and transaction patterns of the early 2010s.
• The Interim Years: Over subsequent major releases, hardware scaling trends dramatically upward. Core counts multiply, memory sizes expand into hundreds of gigabytes, and applications adopt microservices patterns that dramatically increase the frequency of short, highly concurrent transactional bursts. Despite these massive shifts in infrastructure paradigms, the default predicate lock allocation remains anchored at 64.
• PostgreSQL 18 Lifecycle: As monitoring tools and database diagnostics improve, database engineers begin tracking the behavior of max_pred_locks_per_transaction under heavy primary-key fetching workloads. Benchmarks demonstrate that modest concurrent client pools (such as eight pgbench clients executing indexed queries) can consume 10% to 14% of the default table capacity almost instantly.
• PostgreSQL 19 Beta 3 Era: Current evaluations of PostgreSQL 19 beta 3 show that while modifications are made to related parameters like max_locks_per_transaction, the default ceiling for predicate locks stubbornly stays at 64, leaving modern, high-throughput systems vulnerable to unhandled memory exhaustion unless manually tuned.

Supporting Data

To truly understand why the default configuration falls short, one must examine the mathematics of PostgreSQL process slots and hash table allocation.

Calculating the Target Table

PostgreSQL determines the ultimate capacity of the primary predicate lock table by multiplying the parameter value across all potential transaction-holding slots. On a standard default installation of PostgreSQL 18, these slots comprise:

• max_connections
• autovacuum_worker_slots
• max_worker_processes
• max_wal_senders
• max_prepared_transactions
• Two dedicated, fixed system processes

Combined, these default elements establish roughly 136 concurrent process slots. Multiplied by the default parameter value of 64, the system creates a target table sized for 8,704 locked objects (tuples, pages, or relations).

The Twin Table Architecture

PostgreSQL does not rely on just one table; it maintains two distinct fixed-size hash structures with zero spillover or summarization capabilities:

1. The Target Table: Tracks the individual database objects (tuples, index pages, or relations) being monitored.
2. The Lock Record Table: Sized at twice the capacity of the target table, operating on the assumption that an average of two transactions have read each target.

When workloads feature natural "hot spots"—such as thousands of concurrent transactions contending for the exact same index pages—the lock record table fills up rapidly. In rigorous testing, secondary structures have saturated while the primary table sat at roughly 94% utilization, immediately triggering out-of-memory errors for subsequent operations.

Memory Scaling Costs

Administrators hesitant to alter default configurations often fear exorbitant memory penalties. However, benchmark metrics show that increasing the parameter is remarkably inexpensive in terms of RAM:

• Default (64): Consumes approximately < 2 MB of shared memory.
• Moderate Scale (640): Increases shared memory consumption from roughly 150 MB to 169 MB, providing a tenfold increase in lock capacity and scaling dependent parameters (like max_pred_locks_per_relation) accordingly.
• Aggressive Scale (6,400): Elevates shared memory utilization to roughly 350 MB.
• Enterprise Scale (64,000): Demands approximately 2.3 GB of shared memory.

Administrators can safely verify memory consumption changes prior to production restarts by utilizing the postmaster check command:

postgres -D $PGDATA -C shared_memory_size -c max_pred_locks_per_transaction=640

Official Responses and Engineering Realities

Core PostgreSQL documentation maintains that the default value for max_pred_locks_per_transaction "has historically proven sufficient." In a strictly technical sense, this statement is accurate: the vast majority of database deployments either avoid SERIALIZABLE isolation entirely in favor of READ COMMITTED, or they operate at thresholds low enough that transactions complete and shed their locks before accumulation becomes fatal.

However, database engine experts and core contributors acknowledge a critical nuance: the documentation’s phrasing of "per server process" is frequently misinterpreted by administrators as meaning max_connections alone, masking the true breadth of system-wide process slots that multiply the memory footprint.

Furthermore, engineering analyses highlight a distinct limitation regarding what parameter tuning cannot fix. Adjusting max_pred_locks_per_transaction does not protect a system from poorly managed application architecture, specifically idle serializable transactions.

During stress tests where a single session remained open in a read-write serializable transaction while multiple client threads hammered the database, scaling the predicate lock table merely delayed the inevitable crash—extending the grace period from a few hundred transactions to a few thousand before exhausting system limits. More critically, these idle sessions can completely saturate the independent RWConflictPool (a fixed pool of 50 entries per slot designed to track read/write conflicts), triggering fatal errors completely distinct from the predicate lock tables:

ERROR: not enough elements in RWConflictPool to record a read/write conflict
HINT: You might need to run fewer transactions at a time or increase "max_connections".

Implications and Strategic Recommendations

For database reliability engineers and systems architects running high-concurrency applications under strict serializability constraints, relying on out-of-the-box PostgreSQL defaults is an operational risk.

To maintain system stability, database teams should implement the following monitoring and tuning protocol:

1. Diagnostic Monitoring

Actively query the database catalog during peak traffic windows to audit actual lock consumption against theoretical limits. Use the following diagnostic query to track current active locks versus distinct target objects:

SELECT count(*) AS locks,
       count(DISTINCT (locktype, database, relation, page, tuple)) AS targets
  FROM pg_locks
 WHERE mode = 'SIReadLock';

Compare the resulting targets metric against your parameter value multiplied by your total slot count, and compare locks to twice that figure.

2. Strategic Tuning for Serializable Workloads

• Non-Serializable Environments: If your application stack exclusively utilizes READ COMMITTED or REPEATABLE READ, leave max_pred_locks_per_transaction at its default value of 64. The tables will remain empty, and the parameter will consume negligible resources.
• Active Serializable Environments: For production systems relying heavily on SERIALIZABLE isolation, schedule a restart to elevate max_pred_locks_per_transaction to at least 640 (or higher depending on your concurrency profile). Allow dependent thresholds like max_pred_locks_per_relation to scale proportionally.

3. Addressing Root Architectural Flaws

Expanding memory parameters is merely a palliative measure against application-level bottlenecks. If table exhaustion persists despite upward tuning, the root cause is invariably long-lived, abandoned, or poorly managed client sessions holding locks open indefinitely.

Teams must enforce strict query lifetimes by proactively implementing robust configurations for idle_in_transaction_session_timeout and transaction_timeout. Only through a combination of appropriate memory sizing, vigilant monitoring, and aggressive session management can modern PostgreSQL deployments harness the full power of Serializable Snapshot Isolation without fear of sudden memory collapse.