How to find long-running transactions in PostgreSQL

Why old transactions hurt

A transaction that stays open for a long time causes two separate problems:

• Locks: anything it changed stays locked until it ends, so other sessions queue behind it.
• Bloat: PostgreSQL cannot remove dead row versions that an open transaction might still need to see. Vacuum runs but cleans nothing, tables and indexes grow, and queries slow down.

The second problem is quieter. A single forgotten session can hold back cleanup for the whole database for hours.

1. Find the oldest transactions

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - xact_start  AS transaction_age,
       now() - state_change AS in_this_state_for,
       left(query, 120)    AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 20;

transaction_age is how long the transaction has been open. For most web workloads anything over a minute deserves a look; over an hour is almost always a bug or a forgotten console.

2. Idle in transaction

The usual culprit is a session in state idle in transaction: it started a transaction, ran something and then stopped talking to the database without committing. Common causes are an application error path that skips the commit, a connection pool that hands out connections mid-transaction, or someone in a SQL console.

SELECT pid,
       usename,
       application_name,
       now() - state_change AS idle_for,
       left(query, 120)     AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY state_change;

application_name is the fastest way to find the owner, if your applications set it. It is worth adding to every connection string.

3. What is holding back vacuum

Each session that pins old row versions shows a backend_xmin. The oldest one is what vacuum is waiting for.

SELECT pid,
       usename,
       application_name,
       state,
       age(backend_xmin) AS xmin_age_in_transactions,
       now() - xact_start AS transaction_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

Then check whether dead rows are piling up:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

4. Replication slots and prepared transactions

If no session looks old but cleanup is still stuck, two other things can hold it back:

-- replication slots whose consumer has stopped
SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots;

-- prepared (two-phase) transactions left behind
SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared;

An inactive slot from a decommissioned replica or CDC tool is a common cause. Drop it only after confirming nothing still uses it.

5. End them

SELECT pg_terminate_backend(12345);

6. Timeouts that stop them

-- end sessions that sit idle inside a transaction
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

-- PostgreSQL 17+: cap the total length of any transaction
ALTER ROLE app_user SET transaction_timeout = '10min';

Set them per application role, so reporting jobs and migrations can keep longer limits. New sessions pick up the setting; existing ones keep theirs until they reconnect.

The same steps in Quorum

Quorum's health and performance views list long-running transactions and connection pressure per connection, ranked by impact, beside the waits they cause.

• Long-running transactions and connection pressure ranked by observed impact.
• Change timeline: deployments, writes, plan flips, jobs and incidents on one axis, so you can see what started when.
• Review before you act: the exact statement and target, confirmed once, with an honest outcome.