Old Transaction Detection | PG Pilot PostgreSQL Monitoring

What is this issue?

An old transaction is one where xact_start is more than 5 minutes ago,
regardless of the current connection state.

The transaction may be:
- Actively processing a long operation
- Idle in transaction (waiting for more commands)
- Holding a transaction open unnecessarily

Old transactions are problematic because they prevent VACUUM from removing
rows that were deleted after the transaction started.

Why it matters

Vacuum Blocking

Dead tuples visible to this transaction can't be cleaned

Table Bloat

Extended old transactions cause significant bloat accumulation

XID Consumption

Old xmin ages prevent XID freezing

Lock Duration

Any locks held persist for the transaction's duration

How PG Pilot detects it

```sql
SELECT
  pid,
  usename,
  application_name,
  state,
  xact_start,
  age(now(), xact_start) AS transaction_age,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND xact_start < now() - interval '5 minutes'
ORDER BY xact_start;
```

How to fix it

1

Identify the transaction's purpose

Check what the session is doing:

SELECT pid, state, query, xact_start, query_start
FROM pg_stat_activity
WHERE pid = YOUR_PID;

- If state is 'active': It's still running a query
- If state is 'idle in transaction': It's waiting for more commands

2

Check for legitimate long operations

Some operations legitimately take time:
- Large data migrations
- Batch processing jobs
- Complex reports

Verify if this is expected behavior.

3

Terminate if abandoned

If the transaction appears abandoned:

SELECT pg_terminate_backend(YOUR_PID);

4

Set transaction timeouts

Prevent future issues:

-- Timeout idle transactions
SET idle_in_transaction_session_timeout = '5min';
-- Timeout statements
SET statement_timeout = '30min';

Prevention

Set idle_in_transaction_session_timeout
Use autocommit where possible
Keep transactions as short as possible
Monitor old transactions with PG Pilot

Related Features

Connection Management

PostgreSQL Documentation

PostgreSQL Transaction Management