February 3, 2026
Summary: In this tutorial, you will learn how to check abandoned prepared transactions in PostgreSQL.
Table of Contents
Uncommitted prepared transactions
This can occur when applications or distributed database middleware use two-phase commit (2PC). Uncommitted prepared transactions persist in the database even after the session is disconnected, preventing VACUUM from reclaiming dead tuples.
To use prepared transactions, change configuration postgresql.conf, set max_prepared_transactions to 1 or higher and restart PostgreSQL.
max_prepared_transactions = 10
Start a transaction and prepare it for 2PC.
BEGIN;
PREPARE TRANSACTION 'foobar';
Delete data and run VACUUM.
DELETE FROM t;
VACUUM (VERBOSE) t;
INFO: 00000: vacuuming "postgres.public.t"
INFO: 00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 796, which was 2 XIDs old when operation ended
new relfrozenxid: 793, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 14.974 MB/s, avg write rate: 14.974 MB/s
buffer usage: 13 hits, 4 reads, 4 dirtied
WAL usage: 4 records, 4 full page images, 25678 bytes, 25412 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
The message tuples: 0 removed, 100 remain, 100 are dead but not yet removable indicates that dead tuples were not reclaimed. The message removable cutoff: 796, which was 2 XIDs old when operation ended suggests that transaction ID 796 is the culprit.
Abandoned prepared transactions and VACUUM
During two-phase commit, a distributed transaction is first prepared with the PREPARE statement and then committed with the COMMIT PREPARED statement.
Once PostgreSQL prepares a transaction, the transaction is kept “hanging around” until the PostgreSQL commits it or aborts it. It even has to survive a server restart! Normally, transactions don’t remain in the prepared state for long, but sometimes things go wrong and the administrator has to remove a prepared transaction manually.
VACUUM is potentially blocked by the xmin horizon, as we discussed in Check replication slots blocking VACUUM tutorial.
A transaction prepared for a two-phase commit will prevent VACUUM cleanup until it is either committed or rolled back.
List abandoned prepared transactions
You can find all prepared transactions and their xmin value with the following query:
SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
Once identified, you can either use the ROLLBACK PREPARED SQL statement to remove the prepared transactions, or use the COMMIT PREPARED SQL statement to commit the prepared transactions, just as following:
COMMIT PREPARED <gid_from_above>;
ROLLBACK PREPARED <gid_from_above>;