Vert.x SQL Client Pool and PostgreSQL Idle Connection Timeout: Determining Responsibility for Stale Connection Eviction

Question

The goal is to decide whether the Vert.x SQL client connection pool should proactively evict idle connections after a period of inactivity or rely solely on PostgreSQL's idle-in-transaction session timeout to avoid intermittent pool exhaustion during burst traffic.
Constraints include using Vert.x 4 with its HikariCP‑backed pool (configurable maxSize, idleTimeout, leakDetectionThreshold), observing pool saturation via Micrometer metrics, avoiding thread starvation by not oversizing the pool relative to the Vert.x worker executor, and recognizing that aggressive leak detection thresholds can generate false positives in environments with legitimate long‑running queries. PostgreSQL‑side timeouts may vary across versions, adding another layer of variability.
Uncertainty remains about whether proactive eviction complements or conflicts with the database's own timeout mechanism, which can lead to differing exhaustion symptoms across Vert.x releases and complicate diagnosis when leak‑detection logs are silent.
Should the Vert.x SQL client pool enable idleTimeout‑based eviction as the primary method for clearing stale connections, or should it disable idle eviction and defer entirely to PostgreSQL's idle_in_transaction_session_timeout?How does the interaction between Vert.x leakDetectionThreshold and PostgreSQL statement_timeout influence the visibility of pool exhaustion during intermittent high‑load bursts?

Tasadduq Burney · Answer

The Recommended Eviction Strategy
Enable idleTimeout in the Vert.x SQL client pool as your primary mechanism for clearing stale connections. This should be configured to a moderate value (e.g., 30–60 seconds), while PostgreSQL's idle_in_transaction_session_timeout should be set to a significantly higher value. This ensures that the application layer recovers idle connections before the database is forced to terminate the session, preventing the pool from becoming saturated with "ghost" connections during burst traffic.

Mechanism Interaction and Logic
The primary distinction lies in what these timeouts target. Vert.x idleTimeout manages the lifecycle of the connection object within the pool regardless of transaction state. In contrast, PostgreSQL's idle_in_transaction_session_timeout only triggers when a session is idling inside an open transaction. Relying solely on the database timeout leaves non-transactional idle connections lingering, which can lead to pool exhaustion when a sudden burst of requests arrives and finds the pool filled with stale sockets.

Leak Detection vs. Statement Timeouts
The interaction between leakDetectionThreshold and PostgreSQL's statement_timeout determines how you perceive pool exhaustion:

Aggressive Leak Detection: If leakDetectionThreshold is set shorter than the expected query duration or the statement_timeout, Vert.x will log leak warnings for healthy but slow queries. This creates noise that masks actual pool exhaustion.
  Balanced Configuration: Setting leakDetectionThreshold slightly longer than your maximum acceptable query time allows you to distinguish between a slow query (aborted by PostgreSQL) and a genuine connection leak (where the connection is never returned to the pool).

Implementation and Verification
To stabilize the pool during intermittent high-load bursts, apply the following configuration logic:

Set Vert.x idleTimeout: 30s to 60s.
  Set PostgreSQL idle_in_transaction_session_timeout: 5min to 10min (or higher than the Vert.x timeout).
  Set leakDetectionThreshold: 10s to 15s (ensure this is higher than your 99th percentile query latency).

Verification Steps
Verify the configuration using Micrometer metrics and database logs:

Metric Check: Monitor pool.active and pool.idle. After a traffic burst, pool.idle should gradually decrease as idleTimeout evicts connections.
  Log Check: Ensure PostgreSQL logs do not show frequent idle_in_transaction_session_timeout terminations; if they do, the Vert.x pool is not evicting connections fast enough.

Required Diagnostic Detail: Please provide the current value of your PostgreSQL idle_in_transaction_session_timeout. If this value is currently lower than your Vert.x idleTimeout, you may experience "double eviction" where the database closes the socket before the pool realizes it is idle, leading to SocketException errors during connection acquisition.

Vert.x SQL Client Pool and PostgreSQL Idle Connection Timeout: Determining Responsibility for Stale Connection Eviction

1 answer

The Recommended Eviction Strategy

Mechanism Interaction and Logic

Leak Detection vs. Statement Timeouts

Implementation and Verification

Verification Steps

Your perspective belongs here.

0 question comments