Why do you need connection pooling (PgBouncer)?

Question

Accepted Answer

**Connection pooling** reuses a small set of database connections across many client requests, instead of opening a new connection per request. PostgreSQL connections are **expensive** (each is a separate process using significant memory), so pooling is essential for performance and scalability under load. **PgBouncer** is the standard pooler.

## The problem: PostgreSQL connections are expensive

```text
Each PostgreSQL connection = a separate OS PROCESS, using ~5-10MB of memory.
  ✗ Opening a connection has overhead (process creation, auth, setup)
  ✗ Many connections (e.g. 1000s from a busy app) → huge memory use, contention
  ✗ Postgres has a max_connections limit (often ~100); exceeding it = errors
→ Opening/closing a connection per request, or holding thousands open, doesn't scale.
```

## The solution: a connection pool

```text
A POOL maintains a small set of REUSABLE connections to Postgres.
Application requests BORROW a connection from the pool, use it, and RETURN it.
  ✓ Connections are reused (no per-request open/close overhead)
  ✓ Many app requests share a few DB connections (e.g. 10000 clients → 20 connections)
  ✓ Stays within max_connections, controls resource use → scales
```

## PgBouncer — the standard external pooler

```text
PgBouncer sits BETWEEN the application and PostgreSQL — lightweight, efficient.
Pooling modes:
  Session   → a connection is assigned for the client's whole session
  Transaction → a connection is held only for the duration of a TRANSACTION (most
               efficient — connections returned between transactions; common choice)
  Statement → per-statement (most aggressive; limited)
→ Transaction pooling maximizes connection reuse for typical web apps.
```

## Where pooling happens

```text
Application-level → many ORM/driver libraries have built-in pools (per app instance)
External pooler (PgBouncer) → a shared pool in front of Postgres (best for many app
  instances / serverless, where per-instance pools would still overwhelm the DB)
→ Serverless especially needs an external pooler (many short-lived instances).
```

## Why it matters

Connection pooling is essential knowledge for running PostgreSQL **at scale**, because PostgreSQL connections are uniquely expensive (each is a separate OS process using significant memory), so understanding why pooling is needed and how it works is important senior-level knowledge for performant, scalable deployments.

The core problem is that the naive approach — opening a connection per request, or holding thousands of connections open from a busy application — doesn't scale with Postgres: connections are costly (memory per process, creation overhead), and Postgres has a `max_connections` limit (often ~100) that's easily exceeded, causing errors and resource exhaustion. **Connection pooling** solves this by maintaining a small set of reusable connections that many application requests share (borrowing and returning them) — so thousands of clients can be served by a few dozen database connections, eliminating per-request overhead and staying within limits.

Understanding **PgBouncer** (the standard external pooler) and its **pooling modes** — especially **transaction pooling** (holding a connection only for a transaction's duration, maximizing reuse, the common choice for web apps) — is important practical knowledge.

Knowing **where pooling happens** (application-level pools per instance, versus an external pooler like PgBouncer for many instances or serverless) is a key architectural point — **serverless especially needs an external pooler** because many short-lived instances would otherwise overwhelm Postgres with connections (a common, real problem).

Since connection management directly affects scalability and stability under load (and Postgres's process-per-connection model makes this acute), and since pooling (especially PgBouncer with transaction pooling) is the standard, essential solution, understanding connection pooling — why Postgres needs it, how it works, PgBouncer and its modes, and the serverless consideration — is important senior-level knowledge for operating PostgreSQL at scale, a frequently-critical concern for production systems that distinguishes those who can scale Postgres from those who hit connection-limit failures.