Configuring PostgreSQL Session Timeouts Without Breaking Connection Pools

This guide is part of PostgreSQL Server-Side Connection Diagnostics. PostgreSQL has four timeouts that limit how long a session may hold resources, and each one is the standard advice for a particular incident: a runaway query, a transaction left open by leaked code, a connection nobody uses. Applied without regard to the connection pool in front, two of them terminate the session rather than the statement, and a pool that does not expect it hands the dead connection to the next request:

2026-09-16 10:12:40 UTC [48213] FATAL:  terminating connection due to idle-session timeout
2026-09-16 10:12:41 UTC app ERROR org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
2026-09-16 10:12:41 UTC app ERROR HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@6f1a2b marked as broken because of SQLSTATE(08006)

The objective is a set of timeouts that each target one failure mode, ordered so the pool always retires connections before the server does, and rolled out per role so that batch jobs and migrations are not caught by limits meant for request traffic.

Rapid incident diagnosis

Read the effective settings for the application role. Timeouts can be set in postgresql.conf, per database, per role, per role-and-database, and per session. The effective values for a role are best read by connecting as that role:

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('statement_timeout', 'lock_timeout', 'idle_in_transaction_session_timeout',
               'idle_session_timeout', 'transaction_timeout');

-- Role and database overrides, for every role
SELECT coalesce(r.rolname, '*') AS role, coalesce(d.datname, '*') AS database, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

transaction_timeout exists from PostgreSQL 17, idle_session_timeout from 14.

Match the server error to the timeout.

Check whether terminations hit healthy pooled connections. On PostgreSQL 14 and later, pg_stat_database counts sessions ended by fatal errors. A steady climb that matches the pool’s idle connection count over time means idle-session termination is killing warm connections:

SELECT datname, sessions, sessions_fatal, sessions_abandoned, sessions_killed
FROM pg_stat_database WHERE datname = 'orders';

On the application side, look for broken-connection errors on the first query of a request after a quiet period — the signature of a pool handing out a connection the server already closed.

Mathematical sizing or parameter formula

Each timeout has a lower bound set by legitimate work and an upper bound set by what it protects against. The pool’s own timers must sit inside the server’s.

Statement timeout. Longer than the slowest legitimate request query, shorter than the request deadline so the database stops work nobody is waiting for:

p99.9_query_time < statement_timeout < request_deadline − pool_acquisition_timeout

With a 5-second request deadline, a 1-second acquisition timeout and p99.9 query time of 800 ms: statement_timeout = 3s.

Idle in transaction. Longer than the longest legitimate gap between statements inside one transaction — an ORM that loads, computes and writes — and shorter than the time a held lock can be tolerated:

max_legit_in_transaction_gap < idle_in_transaction_session_timeout < tolerable_lock_hold

A gap of up to 2 seconds in normal code and tolerance of 30 seconds for blocked writers gives idle_in_transaction_session_timeout = 15s.

Idle session. Must exceed every pool’s idle retirement, plus a validation or keepalive interval, or the server closes connections the pool believes are healthy:

idle_session_timeout > max over pools ( idleTimeout, keepaliveTime, maxLifetime for idle connections ) + margin

For HikariCP with idleTimeout = 10 min, keepaliveTime = 5 min and maxLifetime = 30 min: a connection kept above minimumIdle is idle at most 10 minutes; a connection at the minimum is touched every 5 minutes by keepalive. idle_session_timeout must be above 10 minutes; 20min leaves margin. If any client pool has no idle retirement at all — some Node.js and Go configurations — idle_session_timeout will break it, and it should stay off for that role.

Transaction timeout. Longer than the longest legitimate request transaction; shorter than the time after which autovacuum and replication are harmed by an open snapshot:

longest_request_transaction < transaction_timeout < snapshot_hold_tolerance

Exact remediation & configuration

Set request-traffic timeouts on the application role, not globally. Global settings also apply to migrations, backups, replication tools and operators:

ALTER ROLE orders_app SET statement_timeout = '3s';
ALTER ROLE orders_app SET lock_timeout = '1s';
ALTER ROLE orders_app SET idle_in_transaction_session_timeout = '15s';
ALTER ROLE orders_app SET idle_session_timeout = '20min';
ALTER ROLE orders_app SET transaction_timeout = '30s';        -- PostgreSQL 17 and later

Role settings apply to new sessions only. Existing pooled connections keep their old values until they are recycled, so the rollout completes after one maxLifetime.

Give batch and migration roles their own limits.

ALTER ROLE orders_batch    SET statement_timeout = '15min';
ALTER ROLE orders_batch    SET idle_in_transaction_session_timeout = '2min';
ALTER ROLE orders_migrator SET statement_timeout = 0;
ALTER ROLE orders_migrator SET lock_timeout = '5s';           -- fail fast rather than queue behind traffic

Align the pool with the server. For HikariCP:

spring:
  datasource:
    hikari:
      idle-timeout: 600000        # 10 min, below idle_session_timeout (20 min)
      keepalive-time: 300000      # 5 min
      max-lifetime: 1800000       # 30 min, recycles connections so new role settings apply
      connection-timeout: 1000

For node-postgres, where idle connections otherwise live indefinitely:

const pool = new pg.Pool({
  max: 10,
  idleTimeoutMillis: 600_000,        // close idle clients after 10 min
  statement_timeout: 3_000,          // also settable client-side per connection
  idle_in_transaction_session_timeout: 15_000,
  keepAlive: true,
});
pool.on("error", (err) => logger.warn({ err }, "idle client error; removed from pool"));

The error handler matters: when the server terminates an idle client, pg.Pool emits error on the pool, and an unhandled error event crashes the Node.js process.

Behind PgBouncer in transaction mode, role settings are applied when the server connection is established, and ALTER ROLE ... SET values follow the server login role. Prefer PgBouncer’s own idle_transaction_timeout and query_timeout for client-facing limits, and keep idle_session_timeout off for the role PgBouncer logs in as — PgBouncer’s server_idle_timeout already retires idle server connections, and a server-side idle termination only causes reconnection churn.

Handle terminations in the application. A connection error with SQLSTATE 25P03, 57P05 or 25P04 is not retryable at the statement level — the transaction is gone — but the request can be retried if it is idempotent. The pool will have discarded the connection; the retry acquires a fresh one.

Validation & verification

Each timeout does what it should, and only that. In staging, connect as orders_app through the pool and trigger each case:

SELECT pg_sleep(5);                                  -- expect 57014 after 3 s; connection still usable
BEGIN; SELECT 1; SELECT pg_sleep(20);                -- expect 57014 from statement_timeout first
BEGIN; SELECT 1;  -- then wait 16 s client-side      -- expect 25P03; connection closed

After the statement timeout, SELECT 1 on the same connection must succeed. After the idle-in-transaction termination, the pool must discard the connection and the next borrow must succeed.

Pooled idle connections survive. Leave the service idle for 30 minutes, then send a request. It must succeed on the first attempt, with no broken-connection errors in the application log and no increase in sessions_fatal attributable to idle-session termination:

SELECT sessions_fatal FROM pg_stat_database WHERE datname = 'orders';   -- sample before and after

Rollout reached every connection. One maxLifetime after applying role settings, no session from the role should still carry the old values. Verify from a fresh pooled connection with SHOW statement_timeout;, and confirm the oldest backend of the role is younger than the change:

SELECT min(backend_start) FROM pg_stat_activity WHERE usename = 'orders_app';

Batch jobs unaffected. Run the longest nightly job as orders_batch and confirm it completes without 57014.

Observing timeouts after rollout

Timeouts that fire silently are almost as bad as none: work is cancelled, users see errors, and nobody connects the errors to the setting. Make each timeout visible on both sides.

Log them on the server. Cancellations and terminations are logged as ERROR and FATAL messages by default. Make sure log_min_error_statement includes the failing statement for statement timeouts, and add the application name and role to log_line_prefix so a log search can attribute them:

ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d app=%a ';
ALTER SYSTEM SET log_min_error_statement = 'error';
SELECT pg_reload_conf();

Count them. With a log-based metric or the PostgreSQL exporter’s log parsing, graph timeouts per role and type. A steady trickle of statement timeouts from one endpoint is a slow query to fix; a burst of idle-in-transaction terminations after a deploy is a code change that holds transactions open.

# Example log-derived counters
pg_timeouts_total{role="orders_app", type="statement_timeout"}
pg_timeouts_total{role="orders_app", type="idle_in_transaction_session_timeout"}

Surface them in the application. Map the SQLSTATEs to specific error types or metrics in the data access layer, so dashboards show “statement timeout on /reports” rather than a generic database error:

Review the counts a week after rollout. Any timeout type that fires regularly for request traffic is either a real problem the timeout exposed — good — or a limit set too tight for legitimate work, which should be adjusted per role rather than removed.

Frequently Asked Questions

Related

• PostgreSQL Server-Side Connection Diagnostics — the parent guide to reading sessions from the server.
• Interpreting pg_stat_activity Wait Events for Pool Issues — finding the sessions these timeouts would reap.
• Tuning PostgreSQL max_connections and superuser_reserved_connections — the server ceiling that reaped sessions give back.
• Tuning HikariCP maxLifetime and idleTimeout — the pool timers that must sit inside the server’s.