Fix PostgreSQL Idle-in-Transaction Timeout: Configuration and Best Practices

Quick Answer

• What to do: Set idle_in_transaction_session_timeout to automatically terminate sessions stuck in idle transactions, preventing connection pool exhaustion and lock contention.
• First check: Run SHOW idle_in_transaction_session_timeout; to see current value (default is 0, meaning disabled). Query SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; to identify problematic sessions.
• Minimal fix: Execute ALTER SYSTEM SET idle_in_transaction_session_timeout = '900000'; then SELECT pg_reload_conf(); to set a 15-minute timeout without restarting PostgreSQL.
• Applicable versions: PostgreSQL 9.6 and later. Works with all connection poolers (PgBouncer, pgpool-II) and application frameworks (Python, Java, Node.js, Ruby, Go).
• Critical boundary: This parameter only affects sessions idle in a transaction—it does not terminate sessions idle outside a transaction or sessions executing long-running queries.

What Problem It Solves

Applications that open transactions but fail to commit or rollback them create a class of resource leaks that degrade database performance over time. Each idle-in-transaction session holds locks, prevents autovacuum from cleaning dead tuples, and consumes a connection slot from the pool. In high-concurrency environments, even a few leaked transactions can cascade into:

• Connection pool exhaustion, causing new requests to hang or fail
• Deadlock chains as held locks block other transactions
• Bloating tables because autovacuum cannot remove tuples visible only to the idle transaction
• Application-level timeouts and degraded user experience

The idle_in_transaction_session_timeout parameter solves this at the database kernel level, requiring zero application code changes. When a session remains idle in a transaction beyond the configured threshold, PostgreSQL terminates the session with a clear error message, allowing the application's connection pool to detect the failure and reconnect.

Root Cause Analysis

An idle-in-transaction session occurs when a client:

1. Issues BEGIN (or any statement that implicitly starts a transaction)
2. Executes one or more queries
3. Stops sending queries without committing or rolling back

Common root causes include:

• Application bugs: Missing commit() or rollback() in error-handling paths
• Network interruptions: Client disconnects mid-transaction without cleanup
• Connection pool misconfiguration: Pool returns connections that still have open transactions
• Long-running user workflows: Web applications that keep database transactions open while waiting for user input
• ORM issues: Object-relational mappers that lazily manage transactions and fail to close them

The timeout acts as a circuit breaker. When triggered, PostgreSQL logs the termination and releases all resources held by that session.

Minimal Working Configuration

Global Setting (Recommended for Production)

SQL
-- Set timeout to 15 minutes (900000 milliseconds)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '900000';

-- Apply without restart
SELECT pg_reload_conf();

-- Verify the setting
SHOW idle_in_transaction_session_timeout;

Session-Level Setting (For Testing or Specific Users)

SQL
-- Must be set before BEGIN
SET idle_in_transaction_session_timeout = '600000';  -- 10 minutes

-- Now start your transaction
BEGIN;
-- ... queries ...
COMMIT;

Configuration File (postgresql.conf)

INI
# Add to postgresql.conf for persistent configuration
idle_in_transaction_session_timeout = 15min

After editing the file, reload with pg_ctl reload or SELECT pg_reload_conf();.

Parameters and Environment Variables

Recommended values by environment:

Important notes:

• The value is in milliseconds. Use single-quoted strings with time units for readability: '15min', '600s', '900000ms'.
• Setting this parameter does not require a server restart—only a reload.
• The parameter cannot be set inside a transaction block. Use ALTER SYSTEM or set it before BEGIN.
• In replication environments, configure this on each standby independently—it does not replicate from the primary.

Common Errors and Fixes

Production Notes and Security Checks

Deployment Checklist

1. Start conservatively: Begin with 30 minutes (1800000) in production, then reduce to 15 minutes after monitoring for false positives.
2. Monitor terminated sessions: Query pg_stat_activity for sessions with wait_event = 'timeout' and check PostgreSQL logs for idle-in-transaction session timeout entries.
3. Log analysis: Use grep 'idle-in-transaction session timeout' /var/log/postgresql/postgresql-*.log to track frequency and affected PIDs.
4. Application retry: Ensure your application catches the FATAL error and retries the transaction. Most ORMs and connection pools (HikariCP, psycopg2 pool, node-postgres pool) handle this automatically.
5. Avoid configuration drift: Use postgresql.conf or infrastructure-as-code tools (Ansible, Terraform) rather than ALTER SYSTEM to maintain consistent configuration across environments.

Security Considerations

• Use a database user with ALTER SYSTEM privilege (typically superuser) to change this parameter
• Enable log_connections = on and log_disconnections = on to audit terminated sessions
• Consider setting log_min_error_statement = error to capture the SQL that was running when the timeout fired

Cloud Database Configuration

FAQ

Q: How do I monitor sessions terminated by this timeout?

A: Query pg_stat_activity for sessions with state = 'idle in transaction' and wait_event = 'timeout'. For historical tracking, search PostgreSQL logs for idle-in-transaction session timeout. Tools like pgBadger can parse log files and generate reports on terminated sessions. You can also track the pg_stat_database counters xact_commit and xact_rollback to see if rollbacks spike after enabling the timeout.

Q: What is the difference between idle_in_transaction_session_timeout and statement_timeout? Can I use both?

A: Yes, use both—they address different problems. statement_timeout limits how long a single SQL statement can execute (e.g., a slow query). idle_in_transaction_session_timeout limits how long a session can sit idle inside a transaction without executing any statement. For example, if a transaction runs a complex JOIN that takes 2 minutes, statement_timeout of 30 seconds would kill it. If the application then pauses for 20 minutes before committing, idle_in_transaction_session_timeout of 15 minutes would kill it. Recommended: set statement_timeout = '30s' to '5min' and idle_in_transaction_session_timeout = '15min' to '30min'.

Q: How do I clean up existing idle-in-transaction sessions before enabling the timeout?

A: The timeout only affects sessions started after it is enabled. To clean up existing sessions, query pg_stat_activity and terminate them manually:

SQL
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '15 minutes';

Run this before enabling the timeout to ensure a clean baseline.