Alert runbooks /

PostgresqlLongRunningTransaction

A transaction in PostgreSQL has been open for a long time, either still working or sitting idle while holding locks and old snapshots.

What it means

The exporter reports the age of the oldest open transaction per database and state. The alert fires when a transaction that is active or idle in transaction has been open for a long time (tens of minutes) and stays that way.

Long transactions hurt in ways that are not obvious: they hold row and table locks that block other writers and DDL, and their snapshot prevents VACUUM from removing dead rows anywhere in the cluster, so tables and indexes bloat while it stays open.

Common causes

• Application bug: a transaction opened, then the code waited on a network call or crashed without rolling back (idle in transaction).
• A person left a BEGIN open in psql or a GUI client.
• Reporting or analytics queries running on the primary.
• Migrations or bulk backfills done in a single transaction.
• pg_dump of a large database (holds one snapshot for the whole dump).

First checks

1. List the oldest transactions:

   SELECT pid, usename, application_name, client_addr, state,
          now() - xact_start AS xact_age, now() - state_change AS in_state_for,
          wait_event_type, left(query, 100) AS last_query
   FROM pg_stat_activity
   WHERE xact_start IS NOT NULL
   ORDER BY xact_start
   LIMIT 10;
   

2. Check whether it is blocking anyone:

   SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 80)
   FROM pg_stat_activity
   WHERE cardinality(pg_blocking_pids(pid)) > 0;
   

3. See how long it has been growing:

   max by (instance, datname, state) (pg_stat_activity_max_tx_duration)
   

4. Also check forgotten prepared transactions, which do not appear as sessions:

   SELECT gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY prepared;
   

Fixing it

Ask the owner if it is a legitimate job. If it is abandoned or blocking production, cancel the query with SELECT pg_cancel_backend(<pid>); or end the session with SELECT pg_terminate_backend(<pid>);. Prevent repeats with idle_in_transaction_session_timeout, per-role statement_timeout for ad-hoc users, and by running reports on a replica.

Related alerts

• PostgresqlHighDeadTuples: old snapshots stop VACUUM from cleaning up.
• PostgresqlDeadlocks: long lock holders increase conflicts.
• PostgresqlTooManyConnections: blocked sessions pile up behind the transaction.