- Introduction — I Raised the Pool Size, So Why Did It Get Slower
- Why Is a Connection an Expensive Resource
- Why It Gets Slower as the Pool Grows — Put the Queue Outside the Database
- The Basis and the Limits of the Frequently Quoted Formula
- Instance Count Times Pool Size — The Classic Microservices Accident
- The Three PgBouncer Modes and What You Cannot Use in Transaction Mode
- Diagnosing Connection Leaks and What Is Special About Serverless
- Closing — Measurement Rather Than Formulas, and the Location of the Queue
Introduction — I Raised the Pool Size, So Why Did It Get Slower
Response times spike in a load test and connection acquisition timeouts appear in the log. The natural response is to raise the pool size. From 20 to 50, and when that does not work, to 100.
Yet throughput stays flat or even drops, and p99 latency gets worse. Database CPU utilization is pinned at 100 percent, but requests per second do not increase.
This phenomenon is not a tuning failure but a predetermined outcome. That is because a connection pool is not a device for increasing performance but a device for limiting concurrency. Enlarging the pool means loosening the limit, and loosening the limit moves the queue from the pool into the database. Only the location of the queue changes and the total amount of work stays the same, but a queue inside the database is far more expensive.
This article explains why, and lays out the actual procedure for choosing a size, the limits of the frequently quoted formula, and what changes with PgBouncer and in serverless environments.
Why Is a Connection an Expensive Resource
PostgreSQL creates one operating system process per connection. Not a thread, a process. This design has advantages in terms of stability, but the costs are equally clear.
-- Confirm that one connection really is one process
SELECT pid, backend_type, application_name, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
LIMIT 5;
# The same pid is right there in the OS process list
ps -o pid,rss,command -p 24188
# PID RSS COMMAND
# 24188 11284 postgres: api shop 10.0.3.21(52144) idle
The cost splits into three layers.
First, creation cost. Every new connection involves a fork, authentication, and catalog cache initialization. That is several milliseconds even locally, and tens of milliseconds once you include the network and the TLS handshake. This is the first reason to use a pool.
Second, memory. Even an idle backend occupies several megabytes for its catalog cache and plan cache, and a backend that has lived a long time serving a variety of queries grows much larger. On top of that, work_mem multiplies. work_mem is allocated not per connection but per sort or hash operation inside a query, so with 200 connections, a work_mem of 64MB, and two sorts per query, you could in theory reach 25GB.
Third, internal costs proportional to the connection count. Building a snapshot requires walking the list of running transactions, and the lock management data structures grow too. The snapshot acquisition path was improved substantially in PostgreSQL 14 so the burden of idle connections dropped a lot, but the cost that scales with the number of active connections still remains. On top of this comes operating system context switching. Once the number of runnable processes greatly exceeds the core count, the CPU spends its time switching instead of working.
# Watch whether context switching is spiking
vmstat 1 5
# procs -----------memory---------- ---system-- ------cpu-----
# r b swpd free buff cache in cs us sy id wa st
# 68 0 0 2104928 88420 9821004 42118 318442 71 26 3 0 0
If the r column greatly exceeds the core count and cs is in the hundreds of thousands, the database is doing scheduling rather than work.
Why It Gets Slower as the Pool Grows — Put the Queue Outside the Database
The principle is explained by a single piece of queueing theory. By the law of Little, the average number of items in the system is throughput times average response time.
concurrent items (L) = throughput (X) × response time (W)
The physical processing capacity of a database is fixed by core count and disk bandwidth. Once you reach that limit and keep increasing concurrent requests, throughput X no longer grows and response time W grows proportionally instead. In other words, the same work gets done while the latency of every request goes up.
On top of that, as concurrency rises, throughput actually begins to decrease.
- Context switching eats into effective CPU time.
- Lock contention over the same rows and index pages increases. Contention grows close to the square of concurrency.
- Competing over the buffer cache lowers the cache hit ratio.
- Deadlocks and serialization failures increase, adding retry load.
So a queue necessarily forms somewhere, and the question is where. Waiting in the pool consumes no resources, is processed in order, and exposes wait time as a metric. Waiting inside the database means waiting while holding processes, memory, and locks, and making each other slower.
Summed up in one sentence: pool size must be the number of items the database can handle well concurrently, not the number the application wants to send.
The actual way to observe this is simple. Fix the pool size, raise the load, and watch two metrics together.
-- What is the database actually doing concurrently
SELECT state, wait_event_type, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC;
state | wait_event_type | count
--------+-----------------+-------
active | LWLock | 41
active | | 8
active | Lock | 22
idle | Client | 64
If backends that are active but waiting on LWLock or Lock far outnumber the backends actually doing work, the pool is already too large. Enlarging the pool further only makes this ratio worse.
The Basis and the Limits of the Frequently Quoted Formula
The most widely quoted expression is this one.
pool size = (core count × 2) + effective number of disk spindles
The basis is clear. The core count is what actually performs computation, and doubling it gives headroom so that another request can fill in when a core idles waiting on disk or network. The last term reflects parallel disk I/O capability. For an 8-core server this lands somewhere around 20.
The message this expression carries is not the number itself but the order of magnitude. The answer is in the tens, not the hundreds. If you have a pool of 200 attached to an 8-core database, that setting is almost certainly wrong.
That said, there are a few conditions under which you cannot use the formula as-is.
First, the round-trip latency between the application and the database. Within the same availability zone it is around 0.2ms, but across zones it exceeds 1ms. With ten statements in one transaction the round trips alone become 10ms, and during that time the connection is occupied while the database sits idle. This kind of workload needs a bigger pool than the formula suggests. More precisely, rather than enlarging the pool, you should reduce the number of round trips.
Second, what the application does inside the transaction. If an external API call or file processing lands in the middle of a transaction, connection occupancy time explodes. In that case the required pool size becomes unrelated to the formula, and the remedy is not pool sizing either.
Third, mixed workloads. If OLTP and analytic queries share one pool, a handful of heavy queries block the entire pool. Here the answer is not resizing but splitting the pool.
# Split pools by purpose
pools:
web: { size: 12, timeout: 3s } # user response path
batch: { size: 4, timeout: 60s } # nightly batch
readonly: { size: 8, timeout: 10s } # replica queries
So the real procedure has to start with the formula and end with measurement.
- Take an initial value from the formula. For 8 cores that is around 20.
- Apply the target load and record the p99 of pool wait time and overall throughput.
- Try halving the pool size. If throughput holds, the original value was too large.
- Increase it until you find the point where throughput stops rising. Slightly below the value where throughput starts to flatten is the right size.
- If pool wait time is still large at that value, you have to fix the queries, not the pool.
Step 5 is especially important. Long connection waits mean long connection occupancy, and that usually means a slow query or a long transaction. Pool size only hides that symptom for a while.
Instance Count Times Pool Size — The Classic Microservices Accident
A setting that was tuned well for a single application quietly collapses as the number of services grows.
Service A: 12 instances × pool 20 = 240
Service B: 8 instances × pool 15 = 120
Service C: 6 instances × pool 10 = 60
Batch workers: 4 × pool 5 = 20
total = 440
max_connections = 200
Every service setting looks reasonable on its own. Yet the sum exceeds twice the maximum connections. In normal times not every pool is full so the problem stays hidden, and then it blows up the moment traffic surges or a rolling deployment temporarily doubles the instance count.
FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved for non-replication superuser connections
Worse still, this error also blocks health checks and monitoring agents. Your means of observation disappears together with the service during an incident.
You have to manage this as a budget. There are items that are easy to leave out of the calculation.
SHOW max_connections; -- 200
SHOW superuser_reserved_connections; -- 3
SHOW max_wal_senders; -- 10 (replicas, separate from max_connections)
-- Who is using how many right now
SELECT application_name,
count(*) AS total,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1
ORDER BY 2 DESC;
application_name | total | active | idle | idle_in_tx
------------------+-------+--------+------+------------
order-service | 88 | 6 | 79 | 3
user-service | 42 | 3 | 39 | 0
datadog-agent | 6 | 1 | 5 | 0
flyway | 2 | 0 | 2 | 0
This output is the typical state. It holds 88 connections, but only 6 are actually working. The remaining 79 occupy nothing but memory and process slots.
The principles for allocating the budget are these.
- Allocate to user-path services first, and give batch and admin tools the minimum.
- Leave headroom for instances temporarily increasing during a rolling deployment. The calculation must be based on the maximum instance count.
- Reserve a few for migration tools, monitoring agents, and administrator access.
- Separate database users per service and cap them per user.
-- Per-user connection cap. Structurally prevents one service from consuming everything.
ALTER ROLE batch_worker CONNECTION LIMIT 10;
ALTER ROLE order_service CONNECTION LIMIT 60;
If the number of services keeps growing even after you shrink pool sizes, that is the point to introduce a connection pooler.
The Three PgBouncer Modes and What You Cannot Use in Transaction Mode
PgBouncer stands between the application and PostgreSQL and multiplexes many client connections onto a few server connections. There are three modes, and the difference is when a server connection is reclaimed.
| Mode | When the server connection returns | Multiplexing efficiency | Features you cannot use |
|---|---|---|---|
| session | Client connection closes | Low | None. Only connection creation cost is saved |
| transaction | Transaction ends | High | Session variables, session-level advisory locks, LISTEN and NOTIFY, WITH HOLD cursors, temporary tables |
| statement | Statement ends | Highest | Multi-statement transactions themselves |
In practice the only meaningful option is transaction mode. It lets you take 1000 clients on 20 server connections.
[databases]
shop = host=10.0.1.10 port=5432 dbname=shop
[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60
max_prepared_statements = 200
What you give up in exchange is clear. The moment a transaction ends another client uses that server connection, so all session state that persists across transaction boundaries breaks.
First, session variables. Run SET search_path or SET timezone outside a transaction and it will not survive into the next request. If you implemented multi-tenancy with search_path, transaction mode can quietly read the schema of a different tenant.
-- The safe way in transaction mode
BEGIN;
SET LOCAL search_path = tenant_42, public;
SELECT ...;
COMMIT;
SET LOCAL reverts automatically at the end of the transaction, so it is safe. The same goes for patterns like SET LOCAL app.current_user_id used with row-level security.
Second, advisory locks. pg_advisory_lock is session-scoped, so it is not released when the transaction ends, and if that connection is handed to another client it never gets released at all.
-- Dangerous: session-scoped lock
SELECT pg_advisory_lock(12345);
-- Safe: released automatically at the end of the transaction
SELECT pg_advisory_xact_lock(12345);
Third, prepared statements. This was the biggest limitation of transaction mode for a long time, and drivers that use prepared statements by default, such as JDBC or asyncpg, would error out. From PgBouncer 1.21 the max_prepared_statements setting supports protocol-level prepared statements, so on a recent version this limitation is gone. If you are on an older version, you have to disable it on the driver side.
# JDBC
prepareThreshold=0
# asyncpg
statement_cache_size=0
Fourth, LISTEN and NOTIFY. A subscription is bound to the session, so it does not work in transaction mode. To use notifications you need a separate session mode pool just for that purpose, or you have to bypass PgBouncer.
One more thing. Adding PgBouncer is not a reason to remove the application-side pool. The usual configuration is to use both layers, keeping the application pool small and letting PgBouncer handle the multiplexing.
Diagnosing Connection Leaks and What Is Special About Serverless
Connection leaks appear in two forms.
First, connections that are never returned. The cause is code that misses a close on an exception path, and it shows up as a graph where pool usage rises monotonically over time.
Second, the more dangerous idle in transaction. This is the state where the application leaves a transaction open while doing something else. It does not stop at occupying a connection: because of the snapshot of that transaction, VACUUM cannot clean up dead tuples. Tables bloat and overall performance slowly degrades.
-- Find old idle in transaction sessions
SELECT pid,
application_name,
state,
now() - xact_start AS tx_age,
now() - state_change AS idle_age,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - state_change > interval '30 seconds'
ORDER BY xact_start;
pid | application_name | state | tx_age | idle_age | last_query
-------+------------------+---------------------+--------------+--------------+-------------------------------
24193 | order-service | idle in transaction | 00:18:44.221 | 00:18:42.008 | SELECT * FROM orders WHERE ...
It has been open for 18 minutes. Since last_query points at the offending code, you can trace it right from here. And then you put a safety net in place.
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET idle_session_timeout = '10min'; -- PostgreSQL 14 and later
SELECT pg_reload_conf();
idle_session_timeout has to be used carefully behind a connection pooler. If the server cuts off idle connections the pool is trying to keep, the pool runs into unexpected disconnections, so it must be set longer than the idle validation interval of the pool.
You should also watch how much the oldest transaction is blocking VACUUM.
SELECT max(age(backend_xmin)) AS oldest_xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL;
Serverless environments have one more structural problem on top of this. Function instances are created and destroyed per request, so the model where each instance maintains its own pool does not hold. With 500 concurrently executing instances you get 500 connection requests. On top of that, there is no guarantee that connections get cleaned up when an instance terminates, so ghost connections pile up on the server side.
There are three responses.
- Make an external pooler mandatory. A managed proxy such as PgBouncer or RDS Proxy takes over multiplexing. In serverless this is not optional.
- Set the pool size inside the function to 1. If one instance handles one request at a time, one connection is enough.
- Create the connection outside the cold start and keep the client in the scope outside the handler so it survives instance reuse.
There is also the option of using an HTTP-based driver. Rather than maintaining a TCP connection and a session, it sends queries over HTTP per request, eliminating the connection management problem itself. In exchange you get constraints on transactions and session features.
Closing — Measurement Rather Than Formulas, and the Location of the Queue
There are three things to remember.
First, the purpose of a connection pool is to limit concurrency, not to increase it. Send more than the number of items the database can handle well concurrently and throughput does not rise while latency does. A queue forms either way, so it is always better to put it on the pool side where waiting holds no resources.
Second, the core-count formula is a starting point, not the answer. The real information it conveys is that the order of magnitude of the answer is tens, not hundreds. The actual value must be decided by measuring throughput and wait time while varying the pool size at your target load. And if wait times are long, it is usually not that the pool is small but that the transactions are long.
Third, always compute instance count times pool size. Even if every service setting is reasonable, once the total exceeds max_connections you get an outage during a deployment. Cap it with a per-user CONNECTION LIMIT, and if services keep multiplying, introduce the transaction mode of PgBouncer — but clean up the code that depends on session state first.
현재 단락 (1/172)
Response times spike in a load test and connection acquisition timeouts appear in the log. The natur...