中文
devexamples
← Workflows

阅读中文版本

Database Connection Pool Audit Workflow

Compare the application pool size, database connection limit, and instance count, then size the pool so total connections never exceed the database capacity.

intermediate~75 mindatabasesperformancereliability

Objective

Eliminate exhaustion or over-subscription of database connections across application instances.

Environment

  • Access to application pool configuration
  • Database connection and wait statistics

Steps

1. Measure the maximum connections the database

Measure the maximum connections the database allows and the current peak usage.

Expected state: You know the headroom.

2. Compute the total pool size across

Compute the total pool size across all instances.

Expected state: The multiplication risk is visible.

3. Set the pool size so

Set the pool size so the worst case stays below the limit, accounting for admin connections.

Expected state: Total connections cannot exceed capacity.

4. Monitor connection wait time and pool

Monitor connection wait time and pool saturation after the change.

Expected state: The pool is right-sized, not guessed.

Decision points

Bigger pool or a pooler in front?

  • Set a right-sized pool
  • Add a pooler such as PgBouncer

Add a pooler when many instances each need connections; a single right-sized pool otherwise.

Reject when full or queue?

  • Queue with a timeout
  • Reject fast

Queue with a bounded timeout for interactive traffic; fail fast for latency-critical paths.

Expected outcome

The combined connection demand from all instances stays within the database limit, with observable wait time.

Verification

Confirm that peak connections stay under the limit with margin and that pool wait time is low under normal load.

Cleanup

Document the new pool size and the formula used to derive it.

Overview

Eliminate exhaustion or over-subscription of database connections across application instances. Confirm that peak connections stay under the limit with margin and that pool wait time is low under normal load.

Steps

  1. Measure the maximum connections the database allows and the current peak usage. Expected state: You know the headroom.
  2. Compute the total pool size across all instances. Expected state: The multiplication risk is visible.
  3. Set the pool size so the worst case stays below the limit, accounting for admin connections. Expected state: Total connections cannot exceed capacity.
  4. Monitor connection wait time and pool saturation after the change. Expected state: The pool is right-sized, not guessed.
SHOW max_connections;
SELECT count(*) AS active, state FROM pg_stat_activity GROUP BY state;
-- pool_size * instances + admin_conns < max_connections

Verification

  • The combined connection demand from all instances stays within the database limit, with observable wait time.

Decision points

  • Bigger pool or a pooler in front? Options: Set a right-sized pool; Add a pooler such as PgBouncer. Recommended: Add a pooler when many instances each need connections; a single right-sized pool otherwise.
  • Reject when full or queue? Options: Queue with a timeout; Reject fast. Recommended: Queue with a bounded timeout for interactive traffic; fail fast for latency-critical paths.