How to fix Idle session timeout in PostgreSQL

Connect to your PostgreSQL database and query the current timeout settings:

SELECT name, setting FROM pg_settings WHERE name LIKE '%timeout%';

Look for idle_session_timeout and idle_in_transaction_session_timeout values in milliseconds (0 means disabled). Also check statement_timeout and lock_timeout if relevant.

Check your application logs for the exact error message:

- "idle-session timeout" → idle_session_timeout is too low
- "idle-in-transaction timeout" → idle_in_transaction_session_timeout is too low
- No message but connection closes → check connection pooling or middleware timeout settings

This tells you which parameter to adjust.

Rather than changing postgresql.conf globally, set the timeout per role or database to minimize side effects.

For a specific user role:

ALTER ROLE your_application_user SET idle_in_transaction_session_timeout = '10min';

For a specific database:

ALTER DATABASE your_database SET idle_in_transaction_session_timeout = '10min';

For the current session only (testing):

SET idle_in_transaction_session_timeout = '10min';

Common timeout values: '1min', '5min', '10min', '30min'. Start conservative and increase based on workload needs.

Adjust your application code to not hold connections idle:

- Commit transactions promptly after queries complete
- Close connections when not actively querying
- Use connection pooling (PgBouncer, pgpool) to recycle connections
- For batch jobs, commit after each batch of rows instead of one large transaction
- Use BEGIN/COMMIT blocks instead of leaving implicit transactions open

Example: Instead of holding a transaction for 30 minutes, process data in smaller chunks with commits between them.

If using connection pooling (PgBouncer, pgpool, etc.), ensure pool timeout settings align with PostgreSQL timeout settings:

# PgBouncer example
idle_in_transaction_session_timeout = 600000  # 10 minutes

The pool's timeout should be greater than PostgreSQL's timeout, otherwise the pool closes connections before PostgreSQL can enforce its timeout.

Reload the PostgreSQL configuration to apply changes:

SELECT pg_reload_conf();

Or restart PostgreSQL for changes to postgresql.conf:

sudo systemctl restart postgresql

Test your application with long idle periods to confirm the new timeout allows your workload to complete without terminating connections unexpectedly.