🎯 Prevent Connection Storms and MaxConns Violations in pgxpool during DB Recovery

[colmev080]

📝 Description

During recovery from a temporary PostgreSQL outage, pgxpool.Pool can experience a race condition under heavy contention. When the database becomes reachable again, multiple goroutines blocked on Acquire() simultaneously attempt to establish new connections.

Because the pool does not immediately account for "in-flight" or pending connection attempts, multiple goroutines independently observe that the current pool size is below MaxConns. Consequently, they all initiate physical connection establishment (dialing and handshaking) concurrently. This leads to:

• A temporary breach of the configured MaxConns limit.
• Connection storms that can exhaust PostgreSQL's max_connections limit.
• Increased CPU and authentication load on both the client and the database during recovery.

The pool must track pending connection attempts and ensure that len(active_connections) + pending_connections never exceeds MaxConns.

🎯 Acceptance Criteria

• The pool must strictly respect the MaxConns configuration limit, even under high concurrency and during recovery from database outages.
• Connection attempts (in-flight dials/handshakes) must be tracked as part of the pool's capacity.
• If len(established_connections) + pending_connections >= MaxConns, subsequent Acquire() calls must block and wait for an available connection or a slot to open, rather than initiating new connection attempts.
• Pending connection counters must be safely decremented if a connection attempt fails (e.g., due to dial timeout, context cancellation, or authentication failure) to prevent pool starvation.
• The fix must not introduce deadlocks or significantly degrade performance under normal, steady-state operation.

🛠️ Technical Specifications & Context

• Target Package: github.com/jackc/pgx/v5/pgxpool (or the corresponding version in colmev080/pgx).
• Key Files:
  • pgxpool/pool.go
• Implementation Details:
  • Locate the connection creation logic within pgxpool.Pool.Acquire() or the internal helper that handles pool expansion (often involving a resource pool implementation like github.com/jackc/puddle).
  • If puddle is used, ensure that the reservation of a slot happens before the physical connection is dialed. In puddle, this is typically managed by acquiring a placeholder or incrementing a counter before performing the slow I/O operation.
  • If custom tracking is required, introduce a thread-safe counter (e.g., using sync/atomic or guarded by the pool's internal mutex) to track pendingConnections.
  • Before dialing a new connection:
    1. Check if activeConnections + pendingConnections < MaxConns.
    2. If yes, increment pendingConnections and proceed to dial.
    3. If no, block the goroutine until a connection is returned or a pending attempt fails.
  • Ensure a defer block or robust try-catch equivalent handles decrementing pendingConnections if the connection creation returns an error.

🧪 Verification & Testing

• Unit/Integration Test:
  • Create a test case in pgxpool/pool_test.go that mocks or simulates a slow connection establishment (e.g., using a custom BeforeConnect hook or a dialer that sleeps for 100ms).
  • Configure the pool with a small MaxConns (e.g., 5).
  • Concurrently spawn a large number of goroutines (e.g., 50) calling Acquire().
  • Verify that:
    1. The total number of concurrent dial attempts never exceeds 5.
    2. The total number of successfully established connections never exceeds 5.
    3. If some dial attempts are forced to fail, the pool eventually recovers and allows new attempts up to the limit of 5.

---

---

This repo is using Opire - what does it mean? 👇

💵 Everyone can add rewards for this issue commenting /reward 100 (replace 100 with the amount).
🕵️‍♂️ If someone starts working on this issue to earn the rewards, they can comment /try to let everyone know!
🙌 And when they open the PR, they can comment /claim #3 either in the PR description or in a PR's comment.

🪙 Also, everyone can tip any user commenting /tip 20 @colmev080 (replace 20 with the amount, and @colmev080 with the user to tip).

📖 If you want to learn more, check out our documentation.

[colmev080]

/reward 10

[opirebot]

😅 Ups... you cannot create a reward of $10.00. It needs to be at least $20.00

[testdasislyt]

Hola!

I'm interested in tackling this. The description of MaxConns violations due to uncounted 'in-flight' connections during recovery resonates as a classic race condition scenario.

My initial thought revolves around introducing an atomic counter (or guarded by a sync.Mutex) to track actively pending connection attempts. This counter would be incremented before initiating a new connection establishment and decremented upon its completion (success or failure). The MaxConns check would then factor in established_connections + pending_connections to make the decision, ensuring the pool respects its limits proactively. This would require careful synchronization within the Acquire() logic to prevent overshooting while also ensuring liveness.

Is this issue currently being worked on or available to pick up?

[jamboriu]

/try

Check if you're the only one trying 👇

You're the first one to try to solve this issue, go for it! 😉

[0xhermes-28]

/try

Check if you're the only one trying 👇

There are already people trying to solve this issue. You're welcome to tackle it independently, but we encourage you to reach out to the contributors that are trying to solve the issue if you'd like to collaborate with them.

The other contributor is: @jamboriu.