The Ghost in the Machine: Understanding, Managing, and Surviving PostgreSQL Prepared Transactions

In the intricate architecture of relational database management systems, few features are as powerful—or as perilous—as two-phase commit (2PC) transactions. Within PostgreSQL, these are operationalized via PREPARE TRANSACTION, a mechanism designed to bridge distributed systems by cutting a transaction loose from its original session.

While essential for distributed coordination, these disembodied entities can morph into database phantoms: locking resources, halting maintenance, stalling logical replication, and resisting standard administrative termination commands. For database administrators (DBAs) and enterprise architects, understanding the lifecycle, risks, and strict configuration requirements of prepared transactions is not merely an advanced topic—it is a critical operational safeguard.

Main Facts: What is a Prepared Transaction?

To understand a prepared transaction, one must look at how it breaks the traditional rules of database sessions. Normally, a transaction is bound to the client session that initiated it. When a developer or system executes PREPARE TRANSACTION 'some-gid', that transaction is severed from its session.

The session that started it is left with no active transaction at all. However, the work performed, the locks acquired, and the unique transaction ID (XID) continue to exist independently. They are written to the Write-Ahead Log (WAL), tracked in shared memory, and meticulously restored following a server crash. They wait indefinitely—or until an administrator or transaction manager explicitly issues a COMMIT PREPARED 'some-gid' or ROLLBACK PREPARED 'some-gid'.

The server tracks the maximum number of these disembodied transactions allowed simultaneously via the configuration parameter max_prepared_transactions.

• The Default Setting: 0
• The Range: Up to 262,143
• Context: postmaster (meaning altering the parameter requires a complete server restart both to enable and disable the feature).

When left unmanaged, these transactions become permanent fixtures in the database cluster. They occupy a PGPROC slot just like any running backend process, yet they have no attached operating system process, and they operate with absolute patience.

Chronology: The Evolution of a Default Setting

The default value of max_prepared_transactions has not always been zero.

• The Pre-2009 Era (PostgreSQL 8.3 and Earlier): Up through version 8.3, the default value for max_prepared_transactions was 5. The rationale at the time was to allow basic out-of-the-box feature exploration.
• The 2009 Shift (PostgreSQL 8.4): Core PostgreSQL contributor Tom Lane altered the default to 0 in 8.4. The motivation behind this change was clear and cautionary: the project had encountered multiple production scenarios where abandoned prepared transactions were forgotten by developers or administrators. These lingering transactions caused severe maintenance bottlenecks, eventually threatening critical anti-wraparound shutdowns.
• The Regression Test Exception: Historical records indicate the only reason the default had ever been non-zero was to allow internal regression tests to exercise the 2PC code paths.
• Modern Warnings (PostgreSQL 18): That same 2009 commit introduced cautionary hints to PostgreSQL’s wraparound warnings. To this day, database logs facing transaction ID exhaustion display the persistent hint: You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Supporting Data: The Mechanics of a Phantom Transaction

Investigating a prepared transaction reveals a unique administrative challenge. If an administrator prepares a transaction that updates a single row, disconnects, and examines the system from a fresh session, standard diagnostic tools tell a distinct story:

• pg_stat_activity displays nothing; there is no active backend process associated with the transaction.
• pg_locks reveals a lingering RowExclusiveLock on the target table and its index, alongside an ExclusiveLock on its own XID. Notably, these locks feature a null pid, which serves as the definitive signature of a prepared transaction’s footprint.
• Administrative operations suffer immediately. An ALTER TABLE ... ADD COLUMN command on the locked table will wait indefinitely until canceled by a lock_timeout.
• Maintenance tasks are crippled. Running VACUUM (VERBOSE) will report dead tuples that are present but strictly non-removable. The system’s removable data cutoff becomes pinned to the prepared transaction’s XID.
• Every newly spawned session inherits that same XID as its backend_xmin, effectively freezing the MVCC (Multi-Version Concurrency Control) horizon.

The Exhaustive List of Things That Will Not Help

When an orphaned prepared transaction locks down a database, administrators often attempt standard interventions—all of which fail:

1. idle_in_transaction_session_timeout: Fails because there is no active session to time out.
2. transaction_timeout (Introduced in PostgreSQL 17): Stops counting the moment the PREPARE command is executed. Setting this and idle timeouts to aggressively short intervals yields no cleanup results.
3. pg_terminate_backend(): Fails because there is no backend process to terminate.
4. Server Crashes: Restarting the database—even with immediate recovery modes (-m immediate)—does not clear them. The server log will calmly note: recovering prepared transaction [XID] from shared memory.
5. DROP DATABASE: Refuses execution with the hard error: There is 1 prepared transaction using the database.
6. pg_upgrade --check: Aborts the upgrade process, warning: The source cluster contains prepared transactions.
7. Logical Replication Setup: Attempting to create a logical replication subscription (CREATE SUBSCRIPTION) will hang indefinitely inside CREATE_REPLICATION_SLOT with the message: Waiting for transactions (approximately 1) older than [XID] to end. This occurs because logical slots require a strictly consistent snapshot, which in turn demands that every in-progress transaction conclude—and this one never will on its own.

The sole path to resolution is COMMIT PREPARED or ROLLBACK PREPARED. This command can be issued from any session connected to the exact same database (attempting it from another database returns an error stating the prepared transaction belongs elsewhere), provided the user acts as a superuser or possesses the specific role that originally prepared the transaction.

Official Responses and Documentation Stance

PostgreSQL’s official documentation is uncharacteristically blunt regarding the deployment of this feature. It states plainly: "It is unwise to leave transactions in the prepared state for a long time."

The core design philosophy is rooted in distributed contract obligations. A prepared transaction is a cryptographic and operational promise made by the PostgreSQL server to an external transaction manager. It guarantees that whenever the manager eventually returns to finalize the commit, the operation will succeed. The database honors this promise against all odds—protecting the distributed transaction integrity even if it means freezing its own internal maintenance cycles and blocking the local database administrator.

Who is Authorized to Turn It On?

According to database engineering best practices, exactly three legitimate use cases justify adjusting max_prepared_transactions above zero:

1. Distributed XA Transaction Managers: The original design intent. This includes Java application servers utilizing JTA (Java Transaction API) or external coordinators like Atomikos and Narayana, which coordinate atomic commits across PostgreSQL, message queues, and secondary databases. (Note: The external manager is also fundamentally responsible for resolving leftovers after a crash).
2. Citus / Distributed Extensions: Distributed database architectures like Citus (since version 11) execute a two-phase commit across worker nodes for every multi-shard write operation automatically, requiring this setting to be raised across all workers.
3. Logical Replication with Two-Phase Commits: Subscriptions created with two_phase = on (introduced in PostgreSQL 15 and later) require the subscriber to prepare transactions that were prepared on the publisher. If a subscriber leaves max_prepared_transactions at its default 0, the apply worker will crash repeatedly, logging prepared transactions are disabled, restarting, and incrementing pg_stat_subscription_stats.apply_error_count until the setting is corrected.

Outside of these three scenarios, community consensus is absolute: if you are not writing or deploying a distributed transaction manager, you have no operational business issuing a PREPARE TRANSACTION command. Leaving the setting at 0 is the ultimate structural safeguard against accidental system locking.

Implications and Enterprise Best Practices

For organizations that meet the criteria to enable prepared transactions, careful tuning and monitoring are mandatory to maintain cluster health.

Sizing and Memory Allocation

When configuring max_prepared_transactions, database architects should match the value to max_connections. The reasoning is straightforward: if an external transaction manager stalls, every active session could theoretically have one transaction pending preparation simultaneously. Ensuring the cap matches max_connections prevents failures where a prepare step fails with the error maximum number of prepared transactions reached, forcing an unexpected rollback.

From a memory perspective, the footprint is modest. Each prepared transaction slot consumes shared memory comparable to a standard connection. For example, allocating 1,000 slots adds approximately 47 MB to shared_memory_size (compared to 51 MB for an additional 1,000 connections). Most of this memory allocation is driven by internal lock tables (max_locks_per_transaction and max_pred_locks_per_transaction), which scale in tandem with max_connections + max_prepared_transactions. For a standard deployment utilizing 100 slots, the memory overhead is roughly 4 MB—an inconsequential cost for modern hardware.

The Standby Rule: A Critical Pitfall

One of the most dangerous operational hazards involves standby databases. max_prepared_transactions is one of five vital parameters—alongside max_connections, max_locks_per_transaction, max_wal_senders, and max_worker_processes—where a standby cluster’s configuration must never be smaller than the primary.

The standby requires identical shared memory sizing to successfully replay incoming PREPARE records from the WAL stream.

• On modern PostgreSQL versions, starting a standby with an insufficient parameter value will cause an immediate startup failure: FATAL: recovery aborted because of insufficient parameter settings.
• Raising this parameter on a primary server while a hot standby is actively running will cause the standby to log hot standby is not possible because of insufficient parameter settings, pause recovery indefinitely, and remain frozen until restarted with a matching configuration value.

Operational Rule of Thumb: Always update standby configurations first, restart them, and subsequently update the primary.

Proactive Monitoring

Never enable prepared transactions without first establishing automated alerting. Because a legitimate two-phase commit spends mere milliseconds in the prepared state, any transaction lingering in this state indicates an orphaned process or a stalled transaction manager.

Enterprise monitoring systems should immediately implement a query-based alert targeting the primary node:

SELECT gid, owner, database, now() - prepared AS age
  FROM pg_prepared_xacts
 WHERE prepared < now() - interval '5 minutes';

(Note: This query must be executed directly on the primary database node; a hot standby’s pg_prepared_xacts view will remain empty even while its underlying shared memory tracks the identical transactions).

Final Operational Recommendations

Leave max_prepared_transactions strictly at 0 unless your architecture explicitly demands distributed transaction management. If your stack requires it:

• Match the value to max_connections across every node in the cluster.
• Update standby nodes before updating the primary.
• Implement aggressive age-based alerting on pg_prepared_xacts.
• Establish a clear operational protocol and assign on-call accountability for knowing precisely who on your engineering team is authorized to execute ROLLBACK PREPARED when an orphaned ghost transaction locks the database at 3:00 a.m.