max_locks_per_transaction

Definition PG 18 manual

The shared lock table has space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time. This parameter limits the average number of object locks used by each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited. The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent table with many children. This parameter can only be set at server start.

When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, queries will not be allowed in the standby server.

How it works

Sets the maximum number of locks per transaction. The value is fixed when the server starts, so changing it requires a restart.

The shared lock table is sized for this average number of distinct lockable objects per backend or prepared transaction. A single transaction may exceed the number if space remains; it does not limit row locks, and standbys need a value at least as large as the primary.

Monitor and change max_locks_per_transaction together with deadlock_timeout, log_lock_waits, lock_timeout. Validate on the relevant server role and real workload, then use its postmaster context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Reading it as a row-lock limit or a hard limit for one transaction.

Raising it without accounting for max_connections and prepared transactions.

Setting a standby below its primary and preventing standby queries.

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

Raising it without the multiplier from connections and prepared transactions.

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_locks_per_transaction 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.