0Pricing
PostgreSQL Performance & Query Optimization · 강의

코어 수에 맞춘 풀 크기 설정

CPU와 작업량을 기준으로 풀 및 max_connections 한도를 도출하여 과도한 컨텍스트 전환을 피하는 방법을 배웁니다.

코어 수에 맞춘 풀 크기 설정은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Pool Size Is Not Connection Count

A common mistake is treating the connection pool as a buffer you can grow freely. With PgBouncer in front of PostgreSQL, you actually run two limits: how many clients can talk to PgBouncer, and how many server connections PgBouncer keeps open to PostgreSQL.

  • max_client_conn can be large (thousands) — these are cheap proxied sockets.
  • default_pool_size (and PostgreSQL max_connections) is the expensive number — each one is a real backend process.

This lesson is about choosing that expensive number from your CPU core count and workload, so the database does real work instead of thrashing between too many backends.

One Backend = One Process

Each PostgreSQL connection is backed by a dedicated OS process. When you have more active backends than CPU cores, the kernel time-slices them. Past a point, adding connections does not add throughput — it adds context switches, lock contention, and memory pressure.

You can see how many backends exist right now and how many are actually running queries:

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

The Starting Formula

The widely cited baseline for a CPU-bound, mostly-active workload is:

  • connections = (core_count * 2) + effective_spindle_count

The * 2 accounts for backends that briefly stall on I/O or locks while others use the CPU. The effective_spindle_count approximates how many concurrent I/O operations your storage can absorb (think 0 for a fully cached working set, higher for many-disk arrays).

For an 8-core server on SSD with a mostly-cached dataset, this lands around 16-20 server connections — not 200.

Computing It In SQL

You don't have to do the arithmetic by hand. PostgreSQL exposes detected core counts, so you can compute a starting pool size directly. The query below is self-contained and runs anywhere:

WITH params AS (
  SELECT 8::int  AS core_count,
         0::int  AS effective_spindles
)
SELECT core_count,
       effective_spindles,
       (core_count * 2) + effective_spindles AS suggested_connections
FROM params;

Active vs Idle Backends

The formula sizes for active work. Pools fail in practice because of backends parked in idle in transaction — they hold a slot (and often locks) without doing anything. These eat your budget just as much as busy queries.

Audit them before you size up the pool:

SELECT pid,
       state,
       now() - state_change AS idle_for,
       wait_event_type,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC;

Pool Mode Changes Everything

How aggressively PgBouncer reuses server connections depends on pool_mode:

  • session: a server connection is tied to a client for its whole session. You need roughly as many server connections as concurrent clients — pooling buys little.
  • transaction: a server connection is returned after each transaction. A small pool can serve many clients. This is what lets (cores*2)-sized pools handle thousands of clients.
  • statement: returned after each statement; most aggressive, but forbids multi-statement transactions.

Sizing against core count assumes transaction mode for OLTP workloads.

A Realistic PgBouncer Block

Putting the numbers together for an 8-core OLTP database, a typical pgbouncer.ini looks like this. Note how max_client_conn is huge while default_pool_size stays near the formula's output:

-- pgbouncer.ini (excerpt)
-- pool_mode = transaction
-- max_client_conn = 2000
-- default_pool_size = 20
-- reserve_pool_size = 5
-- reserve_pool_timeout = 3
-- For an 8-core box: (8 * 2) + 0 = 16, rounded to 20.

max_connections Must Cover Every Pool

PostgreSQL's max_connections is a hard ceiling across all PgBouncer pools plus reserved superuser slots. If you run several databases/users, each gets its own pool of up to default_pool_size, and they all draw from the same backend budget.

Rule of thumb: max_connections ≥ sum of all pool sizes + reserve_pool_size + superuser_reserved_connections + a margin for maintenance and replication.

SHOW max_connections;

SELECT current_setting('max_connections')::int            AS max_conn,
       current_setting('superuser_reserved_connections')::int AS reserved,
       current_setting('max_connections')::int
         - current_setting('superuser_reserved_connections')::int AS usable;

Memory Is The Other Budget

Cores cap useful concurrency, but RAM caps how high max_connections can safely go. Each backend can allocate up to work_mem per sort/hash node, and a single query may use it several times over.

  • Worst case ≈ max_connections * work_mem * (nodes per query).
  • Set work_mem with the real connection ceiling in mind — a small pool lets you afford a larger work_mem.

This is a strong argument for pooling: fewer backends means more memory per query.

SELECT current_setting('work_mem')                       AS work_mem,
       current_setting('max_connections')::int           AS max_conn,
       pg_size_pretty(
         current_setting('work_mem')::bigint
         * current_setting('max_connections')::int
       ) AS naive_worst_case;

Validate Against Real Saturation

The formula is a starting point, not gospel. After deploying, watch whether backends are CPU-bound (good — cores are the limit) or stuck on LWLock/Lock waits (a sign the pool is too large and contention is rising).

Sample the wait events under load:

SELECT coalesce(wait_event_type, 'Running') AS wait_type,
       coalesce(wait_event, 'on_cpu')       AS wait_event,
       count(*)
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY count(*) DESC;

Tuning Loop In Practice

Use a tight feedback loop instead of guessing:

  • Start at (cores * 2) for default_pool_size.
  • Load test. If throughput is flat and latency rises while CPUs are saturated, the pool is already big enough — shrink it.
  • If CPUs sit idle while clients queue at PgBouncer (rising cl_waiting), the pool may be too small or queries are I/O-bound — raise effective_spindle_count and retest.

Check PgBouncer's own view of pressure with the admin console:

-- Connect to the special 'pgbouncer' admin database, then:
SHOW POOLS;
-- Watch cl_active, cl_waiting, sv_active, sv_idle.
-- Persistent cl_waiting > 0 with idle CPUs => pool too small.

Quick Check

You have a 16-core PostgreSQL server, NVMe storage, and a working set that fits entirely in RAM (effectively zero spindles). The app currently opens 800 direct connections and CPUs are pegged with rising lock waits. Using the standard sizing approach with PgBouncer in transaction mode, what is the best starting default_pool_size?

Recap

Key takeaways for sizing pools against core count:

  • Separate the cheap limit (max_client_conn) from the expensive one (default_pool_size / max_connections).
  • Start from (cores * 2) + effective_spindle_count — usually tens of connections, not hundreds.
  • The formula assumes transaction pool mode; session mode needs far more server connections.
  • Ensure max_connections covers the sum of all pools plus reserved slots, and budget RAM via work_mem * max_connections.
  • Hunt down idle in transaction backends and validate with real wait-event and SHOW POOLS data — shrink when CPU-bound, only grow when CPUs idle and clients queue.

자주 묻는 질문

“코어 수에 맞춘 풀 크기 설정” 강의는 무료인가요?

네 — “코어 수에 맞춘 풀 크기 설정” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“코어 수에 맞춘 풀 크기 설정”에서 뭘 배우나요?

CPU와 작업량을 기준으로 풀 및 max_connections 한도를 도출하여 과도한 컨텍스트 전환을 피하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“코어 수에 맞춘 풀 크기 설정” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. PostgreSQL에서 연결 비용이 큰 이유
  2. 트랜잭션 풀링과 세션 풀링 모드
  3. 코어 수에 맞춘 풀 크기 설정
  4. 풀 포화와 대기열 진단
← PostgreSQL Performance & Query Optimization(으)로 돌아가기