Почему соединения дороги в PostgreSQL

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:

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.

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.

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.

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:

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.

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.

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.

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.

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.

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.

Test your understanding of per-connection costs in PostgreSQL.

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.