Long-Running Transaction

Overview

Long-running transactions hold row-level locks, prevent VACUUM from reclaiming dead tuples behind the transaction's xmin horizon, and accumulate WAL that cannot be recycled until the transaction closes. A single stalled transaction can cause table bloat across every table touched by that transaction—even tables the transaction never wrote to.

insightral flags any transaction open longer than five minutes that is not in the 'idle' state. This covers both active queries and transactions that are holding locks while the application sleeps between statements. The five-minute threshold is conservative; many teams set the alarm at two minutes.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Transactions open for more than 5 minutes
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start           AS xact_duration,
  now() - query_start          AS query_duration,
  left(query, 100)             AS current_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
  AND state <> 'idle'
ORDER BY xact_start;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Terminate the long-running backend (use with care)
SELECT pg_terminate_backend(<pid>);

-- Or cancel only the current query, leaving the transaction open
SELECT pg_cancel_backend(<pid>);

Worked example

Common false positives

• Intentional long-running maintenance transactions (e.g., large data migrations) — add a pg_stat_activity application_name prefix like 'migration:' and configure insightral's exception list.
• Read-only long transactions (pg_dump, logical replication slots) — these still block VACUUM but are often expected; filter by state = 'idle in transaction'.