max_pred_locks_per_page

Definition PG 18 manual

This controls how many rows on a single page can be predicate-locked before the lock is promoted to covering the whole page. The default is 2. 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 tuples per page. A configuration reload applies a new value; existing work already in flight is not retroactively changed.

Serializable Snapshot Isolation promotes tuple-level predicate locks to a page-level lock after this many tuples on one page. Promotion saves shared memory but increases the chance that unrelated writes appear to conflict and cause serialization failures.

Monitor and change max_pred_locks_per_page together with max_pred_locks_per_transaction, max_pred_locks_per_relation, 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_page 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.