Parameter · Resource Usage / Memory
max_prepared_transactions
Sets the maximum number of simultaneously prepared transactions.
- Default:
0 - Type: integer
- Range: 0 … 262143
- Applies: Restart required
In plain words
Two-phase commit lets an external transaction manager first prepare a transaction on several databases and later commit it everywhere. Each prepared transaction needs a reserved slot in shared memory. The default 0 turns the feature off, which is what most applications want.
For DBAs and operators
Each slot reserves shared memory (also lock table space, which scales with max_locks_per_transaction × (max_connections + max_prepared_transactions)). If you use XA / two-phase commit (some Java app servers, distributed transaction managers), set it at least as large as max_connections. On a hot standby it must be ≥ the primary's value.
Errors you will see:
- ERROR: prepared transactions are disabled with the hint to set a nonzero value.
- ERROR: maximum number of prepared transactions reached
The real danger: orphans
A prepared transaction survives disconnects and restarts. Forgotten ones hold locks and, worse, hold back the xmin horizon, so VACUUM cannot clean dead rows and XID age grows until wraparound protection kicks in. Check regularly:
SELECT gid, prepared, owner, database,
age(transaction) AS xid_age
FROM pg_prepared_xacts ORDER BY prepared;
-- resolve: COMMIT PREPARED 'gid'; or ROLLBACK PREPARED 'gid';How to change it
Change in postgresql.conf or ALTER SYSTEM, then restart the server.
See it in context
Locking and deadlocks XID wraparound and freezing MVCC: row versions
Related parameters
max_connections max_locks_per_transaction autovacuum_freeze_max_age