max_prepared_transactions

Definition PG 18 manual

Sets the maximum number of transactions that can be in the “prepared” state simultaneously (see PREPARE TRANSACTION). Setting this parameter to zero (which is the default) disables the prepared-transaction feature. This parameter can only be set at server start.

If you are not planning to use prepared transactions, this parameter should be set to zero to prevent accidental creation of prepared transactions. If you are using prepared transactions, you will probably want max_prepared_transactions to be at least as large as max_connections, so that every session can have a prepared transaction pending.

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

How it works

max_prepared_transactions reserves capacity for two-phase transactions left in the prepared state by PREPARE TRANSACTION. Zero disables creating prepared transactions.

Prepared transactions retain locks and transaction state across client disconnects and crashes until COMMIT PREPARED or ROLLBACK PREPARED. Capacity requires shared memory and durable state.

A standby must configure at least the primary's value or read queries can be refused. This is unrelated to SQL prepared statements and should remain zero unless a two-phase commit coordinator is operationally managed. Its postmaster context fixes the value at server start; changing it requires a restart.

Operational considerations

Changing max_prepared_transactions without applying its documented unit and configuration context.

Optimizing an isolated benchmark while ignoring concurrent aggregate resource use.

Assuming a configured value guarantees operating-system or storage behavior.

Failing to retest startup, failover, and workload latency after the change.

Workload guidance

OLAP: Analytical workload does not justify max_prepared_transactions. Enable only for an application protocol that requires durable prepared transactions, not for SQL prepared statements.

OLTP: Keep max_prepared_transactions=0 unless a real two-phase-commit coordinator monitors and resolves every prepared transaction. If enabled, size primary and standbys consistently and alert on transaction age.

SMALL: Leave max_prepared_transactions=0 on a small deployment unless two-phase commit is mandatory and operational recovery is documented; stranded prepared transactions can block the cluster.