What is this issue?
The idle_in_transaction_session_timeout setting automatically terminates
sessions that have been idle (not executing queries) while in a transaction.
Without this timeout:
- Forgotten transactions stay open forever
- Crashed applications leave transactions dangling
- VACUUM cannot clean dead tuples visible to old transactions
- Locks are held indefinitely
This is one of the most impactful "notice" settings because abandoned
transactions cause cascading problems.
Why it matters
Vacuum Blocking
Dead tuples can't be removed while old transactions exist
Table Bloat
Extended blocking causes significant bloat
Lock Retention
Locks prevent DDL and may block queries
XID Age
Old transactions prevent XID freezing
How PG Pilot detects it
```sql SELECT name, setting, unit FROM pg_settings WHERE name = 'idle_in_transaction_session_timeout'; ```
How to fix it
Set a reasonable timeout
5-15 minutes is common for interactive applications:
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();Consider per-role settings
Different timeouts for different use cases:
-- Strict for web applications
ALTER ROLE web_app SET idle_in_transaction_session_timeout = '1min';
-- More lenient for batch jobs
ALTER ROLE batch_job SET idle_in_transaction_session_timeout = '30min';
Terminate existing idle transactions
Clean up current offenders:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND xact_start < now() - interval '10 minutes';Prevention
- Always set idle_in_transaction_session_timeout
- Monitor idle transactions with PG Pilot
- Fix application code that leaves transactions open
- Use autocommit where possible