0Pricing
PostgreSQL Performance & Query Optimization · 课时

为什么 PostgreSQL 中的连接成本高

了解每个后端进程的内存和调度成本，理解为什么大规模部署必须使用连接池。

为什么 PostgreSQL 中的连接成本高 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

One Connection, One Process

PostgreSQL uses a process-per-connection model. Every client connection is handled by its own dedicated OS process called a backend, forked from the postmaster when the connection is accepted.

This design is robust and simple, but it has a real cost: a process is far heavier than a thread. Unlike databases that multiplex many sessions onto a thread pool, PostgreSQL pays a per-process price for every single open connection, whether it is actively running a query or sitting idle.

You can see one backend per connection directly in the catalog:

SELECT pid, usename, application_name, state
FROM pg_stat_activity
WHERE backend_type = 'client backend';

The Cost of Forking

Opening a connection is not free. Each new backend requires PostgreSQL to:

  • Fork a new OS process from the postmaster
  • Attach to shared memory and set up its local memory context
  • Authenticate the client and validate the database/role
  • Load catalog and relation cache entries on first access

This setup can take several milliseconds before a single query runs. An application that opens and closes a connection for every HTTP request pays this tax thousands of times per minute, turning connection churn into a measurable latency and CPU drain.

Per-Backend Memory Is Not Shared

Beyond the shared buffer pool, every backend allocates its own private memory. The key per-connection knobs are local to each session:

  • work_mem — memory for each sort, hash, or grouping operation
  • temp_buffers — memory for temporary tables
  • Catalog and plan caches that grow as the session touches more objects

Critically, work_mem is allocated per operation, per connection. A single complex query with several sorts and hash joins can use multiples of work_mem at once.

SHOW work_mem;
SHOW temp_buffers;
SHOW shared_buffers;

Why work_mem Multiplies

The danger with work_mem is that it is not a per-connection cap — it is a per-operation grant. A plan with three sorts and two hash joins can request work_mem five times simultaneously.

Estimate the worst case roughly as:

peak_RAM ≈ max_connections × work_mem × avg_operations_per_query

With max_connections = 500, work_mem = 16MB, and a few sorts per query, you can theoretically reach tens of gigabytes of transient memory — long before you account for shared buffers or the OS page cache.

-- Rough back-of-envelope ceiling
SELECT
  current_setting('max_connections')::int AS max_conn,
  current_setting('work_mem') AS work_mem,
  current_setting('max_connections')::int
    * (pg_size_bytes(current_setting('work_mem')) / 1024 / 1024)
    AS naive_worst_case_mb;

Idle Connections Still Cost You

A common misconception is that an idle connection is free. It is not. Even a backend doing nothing:

  • Holds an OS process slot and its private memory caches
  • Occupies a slot counted against max_connections
  • Must be visited by background scans of pg_stat_activity and snapshot logic
  • If idle in transaction, it can pin old row versions and block vacuum

Hunting down long-lived idle and idle-in-transaction sessions is one of the first things to check on a struggling server:

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

Snapshots and the Visibility Tax

PostgreSQL's MVCC model means every query takes a snapshot of which transactions are visible. Building and maintaining that snapshot involves scanning the list of currently active backends.

As the number of connections grows, this bookkeeping becomes more expensive. Historically (pre-PostgreSQL 14), GetSnapshotData() scaled with the total number of connections, so thousands of mostly-idle backends added overhead to every active transaction.

The lesson: more connections do not just use more memory — they make the shared coordination work harder for everyone.

The CPU Scheduling Wall

Backends are real OS processes, so the kernel scheduler must time-slice them across your CPU cores. When runnable backends greatly outnumber cores, you hit a wall:

  • More context switching burns CPU on overhead, not query work
  • Cache locality drops as processes are shuffled on and off cores
  • Lock contention on shared structures rises with concurrency

This is why throughput often peaks then declines as concurrency climbs past the core count. A box with 16 cores rarely benefits from 400 simultaneously active queries.

SELECT count(*) AS active_queries
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend';

Sizing max_connections Realistically

It is tempting to set max_connections very high "to be safe," but that backfires. Each potential connection reserves shared-memory bookkeeping and raises the ceiling on memory and scheduling pressure.

A practical rule of thumb for a CPU-bound workload is something like:

active_connections ≈ cores × 2 to cores × 4

You want max_connections set just high enough to cover real concurrency plus headroom — not to absorb thousands of application threads each grabbing a raw connection.

SHOW max_connections;

SELECT count(*) AS current_connections
FROM pg_stat_activity;

Where the Money Goes: A Tiny Model

Here is a self-contained way to reason about the per-connection ceiling using only constants. It estimates worst-case transient query memory for a fleet of connections — no server or tables required, so you can run it anywhere.

The point is to make the multiplication visible: connections times per-operation memory times operations per query is the number that surprises people.

WITH params AS (
  SELECT 400  AS max_conn,
         16   AS work_mem_mb,
         3    AS avg_ops_per_query,
         8192 AS shared_buffers_mb
)
SELECT
  max_conn,
  work_mem_mb,
  avg_ops_per_query,
  max_conn * work_mem_mb * avg_ops_per_query AS worst_case_query_mb,
  shared_buffers_mb
    + max_conn * work_mem_mb * avg_ops_per_query AS total_ceiling_mb
FROM params;

The Fix: Pool, Don't Multiply

The escape from all of these costs is connection pooling. Instead of giving every application thread its own raw backend, a pooler keeps a small set of warm PostgreSQL connections and multiplexes many clients over them.

  • Backends are reused, so the fork/auth/cache-warm cost is paid once, not per request
  • Active backends stay near the core count, avoiding the scheduling wall
  • Total private memory is bounded by the pool size, not the client count

PgBouncer is the canonical lightweight pooler for exactly this reason — it is the subject of the rest of this course.

Diagnosing Connection Pressure

Before tuning a pool, measure where you stand. A quick health snapshot groups current sessions by state so you can see how many are truly active versus idle.

If you see hundreds of idle connections and only a handful active, you are paying the full per-backend memory and scheduling tax for capacity you never use — a textbook case for pooling.

SELECT state,
       count(*) AS sessions,
       max(now() - state_change) AS oldest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

Quick Check

Test your understanding of per-connection costs in PostgreSQL.

Recap: Why Connections Are Expensive

Key takeaways from this lesson:

  • PostgreSQL uses a process-per-connection model — every connection is a forked OS backend, not a cheap thread.
  • Opening a connection pays a real fork, auth, and cache-warming cost before any query runs.
  • work_mem and temp_buffers are per-backend, per-operation, so memory multiplies with connections and operations per query.
  • Idle connections still hold slots, memory, and add snapshot overhead; idle-in-transaction can block vacuum.
  • Too many active backends cause context-switch and lock contention, so throughput peaks near the core count.
  • The remedy is connection pooling (PgBouncer): reuse a small set of warm backends instead of one per client.

常见问题解答

「为什么 PostgreSQL 中的连接成本高」课时是免费的吗？

是的 — 「为什么 PostgreSQL 中的连接成本高」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「为什么 PostgreSQL 中的连接成本高」这节课中我会学到什么？

了解每个后端进程的内存和调度成本，理解为什么大规模部署必须使用连接池。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。

「为什么 PostgreSQL 中的连接成本高」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 为什么 PostgreSQL 中的连接成本高
  2. 事务池化与会话池化模式
  3. 根据核心数确定连接池大小
  4. 诊断连接池饱和与排队
← 返回 PostgreSQL Performance & Query Optimization