A prepared transaction is a transaction that has been cut loose from its session. After PREPARE TRANSACTION 'some-gid', the session that started it has no transaction at all; the work it did, the locks it took, and its transaction ID go on existing by themselves, written to WAL, tracked in shared memory, restored after a crash, waiting for somebody to say COMMIT PREPARED 'some-gid' or ROLLBACK PREPARED 'some-gid'. max_prepared_transactions is how many of those disembodied transactions the server will hold at once. The default is 0, the range runs to 262143, and the context is postmaster, so turning the feature on is a restart, and so is turning it back off.
The default was not always zero. Through 8.3 it was 5. Tom Lane changed it in 8.4 (2009), and the commit message is the whole argument for this post: the project had by then seen several cases of a prepared transaction being forgotten and eventually causing severe maintenance trouble, up to an anti-wraparound shutdown, and the only reason the default had ever been nonzero was so the regression tests could exercise the feature. The same commit added prepared transactions to the wraparound warning’s HINT, where they still are on 18: You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
Nothing can end it but you
On 18.6 I prepared a transaction that had updated one row, disconnected, and looked around from a fresh session. pg_stat_activity showed nothing; there is no backend. pg_locks still had its RowExclusiveLock on the table and its index and the ExclusiveLock on its own XID, all with a null pid, which is how you recognize a prepared transaction’s locks. An ALTER TABLE ... ADD COLUMN on that table waited until lock_timeout cancelled it. VACUUM (VERBOSE) reported 500 tuples dead but not yet removable, with a removable cutoff equal to the prepared transaction’s XID, and every new session’s backend_xmin was that XID too. It occupies a PGPROC slot like any running backend; it just has no process attached, and it is in no hurry.
Then the list of things that will not help. idle_in_transaction_session_timeout has no session to time out. transaction_timeout (17 and later) stops counting at PREPARE; I set both to one second, prepared, and the transaction was still there three seconds later, which is to say it would still be there in March. pg_terminate_backend() has nothing to terminate. A crash doesn’t clear it: I restarted with -m immediate and the log said recovering prepared transaction 754 from shared memory. DROP DATABASE refuses (There is 1 prepared transaction using the database). pg_upgrade --check refuses (The source cluster contains prepared transactions). And, which I did not know until it happened to me while setting up the next test, creating a logical replication slot waits for it: a CREATE SUBSCRIPTION against this server hung inside CREATE_REPLICATION_SLOT with Waiting for transactions (approximately 1) older than 773 to end, because a logical slot needs a consistent snapshot, a consistent snapshot needs every in-progress transaction to finish, and this one was never going to.
The only exit is COMMIT PREPARED or ROLLBACK PREPARED, from any session as long as it is connected to the same database (from another database you get prepared transaction belongs to another database), as a superuser or the role that prepared it. That is the design, not a gap in it. A prepared transaction is a promise the server made to an external transaction manager that the commit will succeed whenever the manager gets around to asking, and the server keeps that promise against everything, including you. The documentation, which does not editorialize often, puts it this way: “It is unwise to leave transactions in the prepared state for a long time.”
Who gets to turn it on
Three things legitimately need this parameter. An XA transaction manager, which is what the feature was built for: a Java application server doing JTA, or something like Atomikos or Narayana, coordinating one commit across PostgreSQL and a message queue or a second database. The transaction manager is also the thing that is supposed to resolve the leftovers after a crash, so prepared transactions without a transaction manager are half of a protocol. Citus, which since Citus 11 has done a two-phase commit across the workers for every multi-shard write, with no option to do otherwise, and which tells you to raise this on every worker. And logical replication subscriptions created with two_phase = on (15 and later), where the subscriber has to prepare what the publisher prepared. I pointed such a subscription at a 19 beta 3 subscriber left at the default, prepared a transaction on the publisher, and the apply worker died with prepared transactions are disabled, restarted, and died again, with pg_stat_subscription_stats.apply_error_count climbing until I raised the setting, restarted the subscriber, and watched the transaction arrive as pg_gid_16397_773.
Beyond those three, the documentation is blunt: the command exists for transaction managers, and if you are not writing one you have no business issuing it. 0 is the only setting that stops someone from doing it by accident. I have never seen an application prepare transactions on purpose without a transaction manager. I have seen the wraparound HINT go unread.
If one of the three applies, set it to max_connections, on the reasoning that every session could have exactly one transaction pending at the moment the manager stalls, and that maximum number of prepared transactions reached at the prepare step turns into a rollback, which is the failure the manager exists to prevent. Shared memory bills a prepared-transaction slot at almost the price of a connection: on 18.6 a thousand slots added 47 MB to shared_memory_size, against 51 MB for a thousand more connections, most of it the max_locks_per_transaction and max_pred_locks_per_transaction tables, which are sized by max_connections + max_prepared_transactions. At a hundred slots that is 4 MB. Nobody will notice.
The part that bites is the standby rule. This is one of the five parameters (with max_connections, max_locks_per_transaction, max_wal_senders, and max_worker_processes) that a standby must have no smaller than the primary, because the standby needs the same shared memory to replay a PREPARE record; the documentation’s own example of what goes wrong otherwise is this parameter. On 18.6 a standby started with a smaller value doesn’t start: FATAL: recovery aborted because of insufficient parameter settings. Raise it on the primary underneath a running hot standby and the standby logs hot standby is not possible because of insufficient parameter settings and pauses recovery, and stays paused until you restart it with the larger value (14 and later; before 14 it shut down on the spot). So: standbys first, then the primary, and the same order applies to all five.
Then put this in monitoring before you turn the feature on, not after:
1 SELECT gid, owner, database, now() - prepared AS age 2 FROM pg_prepared_xacts 3 WHERE prepared < now() - interval '5 minutes';
A real two-phase commit spends milliseconds prepared. Anything older than the transaction manager’s recovery interval is orphaned, and it is holding locks and the vacuum horizon while you read this. Run the query on the primary: a hot standby’s pg_prepared_xacts is empty even while its shared memory carries the same transactions.
Leave it at zero unless one of those three things asks for it by name. If one does, max_connections on every node, standbys first, the age query in the alerting, and a decision about who on the team is allowed to type ROLLBACK PREPARED at 3 a.m., because that is the only thing that will ever end one.