Post-Mortem Framework · Capacity: Connection Pool Fragmentation Under Mixed Workloads

Connection Pool Fragmentation Under Mixed Workloads

SEV-3, Limited Impact

Feedback Loop propagation · capacity · Affects 0 scenario(s)

Severity Classification

Classified as PARTIAL based on failure mode severity. The feedback loop propagation pattern increases risk of broad impact beyond the initial failure point.

Propagation Chain

Origin component

Connection Pool Fragmentation Under Mixed Workloads begins at the source component. Trigger: Analytical or reporting queries run against the same connection pool as OLTP queries.

Immediate (T+0) · Signal: Connection Exhaustion

Downstream dependents

Failure propagates to directly dependent components via synchronous calls or shared resources. Latency increases and error rates rise on affected dependencies.

1–3 minutes via connection pool utilization monitoring (alert when pool utilization > 80% for > 30 seconds). Connection wait timeout errors in application logs are an immediate signal but require log monitoring to surface. The diagnostic signature: high connection pool utilization + normal database CPU + OLTP latency spike. · Signal: Latency spike, connection timeout, or error rate increase on dependents

Downstream of dependents (fan-out)

Failure spreads to multiple downstream systems simultaneously. Retry storms may amplify load on the failing component.

Within minutes of initial failure · Signal: Multiple services reporting elevated error rates

Self-amplification loop

Retry behavior and back-pressure cause the failure to amplify. Resource exhaustion accelerates, the system cannot self-recover without intervention.

Minutes after fan-out · Signal: Cascading alert storms; all downstream indicators deteriorating

Blast Radius

All OLTP endpoints that require a database connection are affected, regardless of whether they touch the same tables as the slow queries. Connection pool starvation is a global resource constraint. A slow reporting query can cause user-facing checkout failures on an entirely unrelated code path if both share the same pool.

Contributing Factors

Trigger Condition: Analytical or reporting queries run against the same connectoperational

This operational trigger enables Connection Pool Fragmentation Under Mixed Workloads: Analytical or reporting queries run against the same connection pool as OLTP queries

Trigger Condition: Batch job holds a connection open for multi-minute query exeoperational

This operational trigger enables Connection Pool Fragmentation Under Mixed Workloads: Batch job holds a connection open for multi-minute query execution

Trigger Condition: Query plan regression causes a previously-fast query to becooperational

This operational trigger enables Connection Pool Fragmentation Under Mixed Workloads: Query plan regression causes a previously-fast query to become a slow full-table scan, suddenly consuming connections for much longer

Remediation Plan

ImmediateIdentify connections held by long-running queries via SELECT pid, query, state,

Identify connections held by long-running queries via SELECT pid, query, state, query_start FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start ASC

Effort: Minutes to hours (on-call response)

ImmediateTerminate the longest-running analytical or batch queries holding connections vi

Terminate the longest-running analytical or batch queries holding connections via SELECT pg_terminate_backend(pid)

Effort: Minutes to hours (on-call response)

ImmediateMonitor connection pool utilization: it should drop below 50% within 30 seconds

Monitor connection pool utilization: it should drop below 50% within 30 seconds of terminating the long-running queries

Effort: Minutes to hours (on-call response)

Short-TermSeparate connection pools for OLTP and analytical/batch workloads

Create two distinct database users with separate PgBouncer pools: app_oltp (pool_size=40, pool_mode=transaction) and app_batch (pool_size=5, pool_mode=session). OLTP application code uses the oltp DSN; batch and reporting code uses the batch DSN. The OLTP pool cannot be starved by batch queries because they draw from separate pools. The batch pool cap of 5 prevents batch from consuming more than 5 database connections at any time.

Effort: 1 day to 1 week

Short-TermStatement-level query timeout for OLTP connections

Set statement_timeout=5000 (5 seconds) on the OLTP database user or connection pool. Any OLTP query running longer than 5 seconds is killed by the database with an error. This prevents a single OLTP query regression from consuming a connection indefinitely. Combine with application-level retry for transient timeout errors. Do NOT apply to the batch pool where long queries are expected.

Effort: 1 day to 1 week

Short-TermRead replica for analytical and reporting queries

Route all reporting, dashboard, and analytical queries to a read replica. The replica has its own connection capacity independent of the primary. Even if a long-running analytical query saturates the replica's connection pool, OLTP traffic to the primary is unaffected. This also reduces I/O contention on the primary from full-table analytical scans.

Effort: 1 day to 1 week

Short-TermAdd alerting for documented detection signals

Configure alerts for: connection exhaustion, latency spike, queue depth. Set thresholds to fire at 70% of critical level to allow response before full failure.

Effort: 1–3 days

Long-TermArchitecture review for Connection Pool Fragmentation Under Mixed Workloads resilience

Conduct a structured architecture review focused on preventing recurrence. Review topology for blast radius reduction, mitigation coverage, and observability gaps. Consider whether the current architecture scenario should evolve.

Effort: 1–2 sprints

This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.