11 views
Mystery 500s that vanish on retry are often pool checkout waits exceeding the load balancer idle timeout—not application logic bugs. The unfair advantage is aligning pool_timeout, statement timeouts, and ALB/target timeouts so exhaustion is loud, labeled, and actionable.
⚡ TL;DR: Set checkout timeout ≪ ALB timeout; never block forever onpool.connect(); emit metrics for wait time and pool size; fail with503/429+ codepool_exhausted, not generic 500. Pair with asyncio TaskGroups and Postgres lock_timeout Strategies.
Python Pool Timeouts: production guidance
Request arrives → handler needs a DB connection → pool is empty → client blocks → ALB idle timeout fires (502/504) or the framework surfaces a generic exception as 500. Retries often succeed because a connection freed up. Dashboards show “error rate spike” with no application stack that looks like a logic bug. The fix is not “bigger pool forever.” It is making exhaustion a first-class, short-circuiting failure mode.The silent wait
python
# ❌ SQLAlchemy default-ish: wait until client gives up (ALB 504/502 chaos)
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine(
dsn,
pool_size=5,
max_overflow=10,
# pool_timeout missing → long block
)
python
# ✅ Explicit, shorter than ALB (e.g. ALB 60s → pool 2s)
engine = create_async_engine(
dsn,
pool_size=20,
max_overflow=10,
pool_timeout=2.0, # seconds to wait for a conn
pool_pre_ping=True,
pool_recycle=1800,
)
from fastapi import FastAPI, Response
from sqlalchemy.exc import TimeoutError as SATimeout
app = FastAPI()
@app.get("/v1/items")
async def items():
try:
async with engine.connect() as conn:
...
except SATimeout:
# ✅ intentional: surface as overload, not "bug 500"
return Response(
content='{"error":"pool_exhausted"}',
status_code=503,
media_type="application/json",
headers={"Retry-After": "1"},
)
Timeout budget math
| Layer | Example budget | Role |
|---|---|---|
| Client / mobile | 10s | UX |
| ALB idle | 60s | Edge |
| App handler | 3s | Work |
| Pool checkout | 0.5–2s | Fail fast |
DB statement_timeout |
≤ handler | Query |
DB lock_timeout |
≪ statement | Avoid wait queues |
python
❌ Setting # ✅ asyncpg example
import asyncpg
pool = await asyncpg.create_pool(
dsn,
min_size=5,
max_size=20,
timeout=1.5, # acquire timeout
command_timeout=2.0, # per query
)
pool_size=100 on a DB with max_connections=100 shared by 12 services — you create login storms that look like app 500s.
Capacity planning across services
text
Encode the budget in Terraform/CDK outputs and fail CI if a service’s pool config exceeds its allocation. Pair with Lambda Reserved Concurrency Bulkheads thinking: bulkheads belong at every scarce resource, not only compute.
DB max_connections = 200
Reserve for admin/migrations = 20
Usable = 180
Services = 6
→ budget ≈ 30 connections / service (pool_size + max_overflow)
Metrics that catch it
Emit at least:| Metric | Alert idea |
|---|---|
pool_checked_out |
Gauge near pool_size + overflow |
pool_wait_ms p95 |
Approaches pool_timeout |
pool_timeout_total |
Sustained >0 for 2 minutes |
db_connections_used (RDS) |
Cross-check login storms |
pool_timeout_total. If they rise together, stop hunting application NullPointers.
Middleware pattern for consistent error codes
python
Map the same code in gRPC as from starlette.middleware.base import BaseHTTPMiddleware
class PoolTimeoutMiddleware(BaseHTTPMiddleware):
async def dispatch(self, request, call_next):
try:
return await call_next(request)
except SATimeout:
return Response(
'{"error":"pool_exhausted"}',
status_code=503,
media_type="application/json",
headers={"Retry-After": "1"},
)
RESOURCE_EXHAUSTED so clients apply backoff instead of retrying immediately into a tighter spiral.
Load-test saturation, not only happy QPS
Run a soak that holds connections artificially (pg_sleep) while QPS stays modest. You want to see 503s with pool_exhausted before customers do. Include this in the same suite that exercises asyncio TaskGroups cancellation under load.
Client retry guidance
Document for callers: a503 with error=pool_exhausted means back off, not retry immediately with the same concurrency. Prefer jittered exponential backoff capped below the client UX timeout. Mobile and browser clients should surface a soft “system busy” state rather than a generic failure toast that triggers rage-retries and deepens the pool storm.
Distinguishing pool timeouts from statement timeouts
Both can surface as client errors, but the remediations differ. Pool checkout timeouts mean admission control / capacity. Statement timeouts mean query plan / lock / missing index. Tag exceptions distinctly (pool_exhausted vs query_timeout) and route alerts to different runbooks. Mixing them into one “DB errors” panel is how teams keep adding pool size during lock storms.
Closing checklist
✅ Dos – ✅ Setpool_timeout explicitly on every pool
– ✅ Map pool timeouts to 503 with a stable error code
– ✅ Keep sum of pools × services under DB max_connections
– ✅ Pair with DB statement_timeout / lock_timeout
– ✅ Load-test saturation, not only happy QPS
– ✅ Alert on wait p95 and timeout counters
– ✅ Document per-service connection budgets in infra code
❌ Don’ts
– ❌ Don’t leave infinite checkout waits
– ❌ Don’t convert pool timeouts into empty catch-all 500 handlers
– ❌ Don’t size pools from laptop defaults
– ❌ Don’t ignore ALB 502/504 correlation with pool metrics
– ❌ Don’t “fix” exhaustion by silently raising max_overflow every incident
Related reading
- asyncio TaskGroups
- Postgres lock_timeout Strategies
- Lambda Reserved Concurrency Bulkheads
- HTTP/2 in Node Behind ALB
Watch: 100 Days of AI on YouTube
Short videos from CheatCoders, one AI engineering topic per day.
Most viewed
- Python Decorators Explained: From Simple Wrappers to Production Patterns
- Claude Code vs Cursor vs GitHub Copilot: Choose by Workflow, and Set Up One Repo That Works With All Three
- AI Agent Frameworks in 2025: LangGraph vs CrewAI vs AutoGen vs Raw API
- Python String Methods: Every str Method With Real Production Examples
- Css Animation Performance: Performance-Optimized Motion for Modern UIs
Newly added
- AWS Systems Manager Parameter Store: Hierarchical Runtime Config for Coding Agents
- Amazon Cognito Identity Pools: Federate Coding-Agent Tool Callers Without Long-Lived Keys
- Amazon Athena for Coding Agents: Query Tool Audit Logs and Eval Traces on S3 Without a Warehouse
- AWS Batch for Coding Agents: Parallel Overnight PR Reviews and Long-Running Jobs Without Lambda Timeouts
- Amazon Bedrock Prompt Management: Version and A/B Test Coding-Agent System Prompts
Deep-dive PDF
Get the expanded guide for this post — extra diagrams-style checklists, failure modes, and a production walkthrough. Free when you subscribe to CheatCoders.
Already subscribed? or open the subscribe page.
Discover more from CheatCoders
Subscribe to get the latest posts sent to your email.
