Database Connection Pool Sizing Calculator

Database Connection Pool Sizing Calculator

Sizes database connection pools from services, pods and pool settings, checking worst-case connections against the database limit.

Skill files8 files

references/

templates/

examples/

scripts/

SKILL.md62 lines
1
---
2
name: database-connection-pool-sizing-calculator
3
description: Sizes database connection pools for Postgres/MySQL apps from a short JSON of services, pods, workers, threads and pool settings - worst-case connections at autoscale max and during deploy surges vs max_connections, busy connections from rps x hold time, per-process wait odds (Erlang C), CPU and memory limits, PgBouncer client and server limits. Recommends pool sizes or proxy settings. Includes an offline stdlib Python calculator with --self-test.
4
---
5
6
# Database Connection Pool Sizing Calculator
7
8
You work out how many database connections an application really needs and whether its pool settings can take the database down. Most "too many clients already" outages are arithmetic: pods x worker processes x (pool_size + max_overflow), multiplied again by autoscaling and rolling deploys, against a max_connections that also has to cover admin sessions, migrations and monitoring. You do that arithmetic for every service, compare it with how many connections are actually busy at peak, and return pool sizes (or proxy settings) that fit with headroom.
9
10
## Files in this skill
11
12
- `scripts/pool_sizer.py` - reads the JSON description, computes open connections today / at autoscaling maximum / during a deploy surge, busy connections (Little's law), the chance a request waits for a connection and the mean wait per process (Erlang C), CPU sweet spot and memory, PgBouncer limits, and recommends pool sizes; `--set` runs what-ifs (Python 3.8+ standard library only)
13
- `references/sizing-model.md` - the formulas, where each input comes from, and the limits of the model
14
- `references/pool-settings-by-stack.md` - what pool_size, max_overflow, threads and timeouts are called in SQLAlchemy, Django, HikariCP, node-postgres, Prisma, Rails, Go database/sql, Celery/Sidekiq, PgBouncer and RDS Proxy
15
- `templates/pool-sizing-review.md` - the review to hand back
16
- `examples/shop-pools.json` - a shop with an API (6-12 pods x 4 gthread workers, pool 10 + overflow 20), Celery workers, an admin app and a nightly report job on a 200-connection Postgres
17
- `examples/pool-report.md` - the real report on that file
18
- `examples/shop-pools-pgbouncer.json` - the fixed setup: right-sized fixed pools, API and workers through PgBouncer in transaction mode, admin and reports direct
19
20
## Workflow
21
22
### 1. Collect the inputs
23
For the database: engine, `max_connections`, `superuser_reserved_connections` (Postgres default 3), connections used by everything that is not an app service (admin tools, monitoring, replication, migrations, BI), vCPUs and RAM. For each service: instances now and autoscaling maximum, worker processes per instance, threads or concurrency per process (async or not), pool size and overflow, pool timeout, request timeout, and load: peak requests or jobs per second and how long one keeps a connection checked out (`conn_hold_ms`; frameworks with a session per request hold it for the whole request). If there is no hold time, use queries per request x average query time, or the pool's "checked out" gauge as `busy_connections`. Ask for the deploy strategy (maxSurge) and any proxy (PgBouncer, RDS Proxy, ProxySQL, Supavisor). Write it as JSON like `examples/shop-pools.json`.
24
25
### 2. Run the calculator
26
```
27
python3 scripts/pool_sizer.py examples/shop-pools.json
28
python3 scripts/pool_sizer.py pools.json --set api.max_instances=20 --set database.max_connections=400
29
python3 scripts/pool_sizer.py pools.json --json
30
python3 scripts/pool_sizer.py --self-test
31
```
32
Exit 0 = no HIGH finding, 1 = HIGH findings, 2 = the JSON is invalid (the message names the field). Without a shell, do the same sums by hand with `references/sizing-model.md`: open = instances x processes x (pool_size + max_overflow) per service; busy = rps x hold seconds; compare both with max_connections - reserved - other.
33
34
### 3. Read the result
35
- HIGH findings first: limits exceeded today, at autoscaling maximum or during a deploy surge, pools or proxies too small for the load, workers saturated by database time.
36
- Compare "busy at peak" with "open": a large gap means idle connections hold memory and slots; a transaction-mode proxy or smaller fixed pools close it.
37
- "Busy at peak" above the CPU sweet spot (cores x 2 + 1) means the database itself is the bottleneck: more connections make it slower, not faster. Fix hold time (short transactions, no HTTP calls inside them, indexes) or capacity.
38
- Treat the Erlang C numbers as a guide: they assume evenly spread, random arrivals. Check them against the pool's wait-time and checked-out metrics.
39
40
### 4. Recommend
41
- Prefer fixed pools (`max_overflow` 0, min idle = max) sized per process from the recommendation, never more than the threads that can use them.
42
- Keep at least 10% of usable connections free at max scale + deploy surge for admin sessions, failover and migrations.
43
- When direct connections cannot fit, put the busy services behind a transaction-mode proxy with the server pool the report suggests; keep migrations, `LISTEN/NOTIFY`, advisory locks and session settings on a direct connection.
44
- Raise `max_connections` only when RAM allows it, and say what it costs.
45
- Set pool timeouts well below the request timeout so overload fails fast instead of piling up.
46
- Rerun with `--set` for the next traffic peak or the next autoscaling change.
47
48
### 5. Deliver
49
Fill `templates/pool-sizing-review.md`: verdict, the connection budget table, findings with fixes, the new settings per service in that stack's own names, proxy settings, and what to monitor after the change.
50
51
## Example result
52
On `examples/shop-pools.json` the services can open 1,230 connections today, 2,430 at autoscaling maximum and 3,050 during a deploy, against 185 usable, while only about 28 are busy at peak. The API pool (10 + 20 per process) is larger than its 8 threads can ever use, and every Celery process keeps 15 connections for 1 thread. Right-sized fixed pools still need 400 direct connections, so the report suggests a transaction-mode PgBouncer with about 53 server connections. `examples/shop-pools-pgbouncer.json` applies that and fits in 80 of 185 with no HIGH finding; the remaining MEDIUM says the 8-core database is already busy at peak (28 active vs a sweet spot of 17).
53
54
## Rules
55
56
- Never guess max_connections, autoscaling limits or hold times; ask, or label the number as an assumption in the review.
57
- Size for the worst case the platform allows (autoscaling max + deploy surge), not today's pod count.
58
- Count every client of the database, including cron jobs, BI tools, migrations and replicas' replication slots.
59
- Do not recommend raising max_connections as the first fix; idle connections cost memory and CPU on the database.
60
- Give settings in the names of the user's own framework (see `references/pool-settings-by-stack.md`).
61
- Say clearly that the queueing numbers are a model and name the metric that confirms them.
62
@OpenSourceBuffGuy

I find openly licensed prompts and post them here.

0 followers · 2,873 prompts

More by @OpenSourceBuffGuy

See all
Ethereum Developer prompt image

Ethereum Developer

Asks the AI to act as an experienced Ethereum developer who writes a Solidity smart contract for an on-chain messenger with public reads and owner-only writes.

act-as, crypto, coding

1 view
Linux Terminal prompt image

Linux Terminal

Makes the AI act as a Linux terminal that replies to each typed command with only its output inside one code block.

simulator, linux, coding

5 views
English Translator and Improver prompt image

English Translator and Improver

Turns the AI into an English translator and editor that detects the input language and returns only an improved, more literary English version.

act-as, translation, rewriting, proofreading

2 views

Reviews

Loading reviews…

Discussion

Loading comments…