max_pred_locks_per_relation

Definition PG 18 manual

This controls how many pages or tuples of a single relation can be predicate-locked before the lock is promoted to covering the whole relation. Values greater than or equal to zero mean an absolute limit, while negative values mean max_pred_locks_per_transaction divided by the absolute value of this setting. The default is -2, which keeps the behavior from previous versions of PostgreSQL. This parameter can only be set in the postgresql.conf file or on the server command line.

How it works

Sets the maximum number of predicate-locked pages and tuples per relation. A configuration reload applies a new value; existing work already in flight is not retroactively changed.

SSI promotes page/tuple predicate locks to one relation-level lock at this threshold. A negative value means max_pred_locks_per_transaction divided by its absolute value; promotion changes granularity, not SQL lock strength.

Monitor and change max_pred_locks_per_relation together with max_pred_locks_per_transaction, max_pred_locks_per_page, default_transaction_isolation. Validate on the relevant server role and real workload, then use its sighup context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Reading an average shared-memory sizing value as a hard per-transaction limit.

Raising it without the multiplier from connections and prepared transactions.

Expanding lock memory instead of fixing long transactions, access order, or partition explosion.

Ignoring compatible startup lock capacity on standbys.

Workload guidance

OLAP: Partitioned queries, bulk DDL, and SERIALIZABLE reports may touch many objects; test the worst plan, not only an average transaction.

OLTP: Calibrate max_pred_locks_per_relation from lock-wait logs, objects touched, and the concurrency bound. Fix long transactions and access ordering before adding shared memory or changing detection granularity.

SMALL: Defaults are usually sufficient. If raising a startup lock-table setting, account for max_connections, prepared transactions, and standby consistency together.