# PostgreSQL connection pooling in practice (PgBouncer / RDS Proxy / Supavisor, the transaction-mode traps, serverless support)

> A practical guide to connection pooling that prevents PostgreSQL connection exhaustion. It explains, with real code: the process-per-connection mechanism, why you must not raise max_connections, the difference between PgBouncer's session/transaction/statement modes, the features that break in transaction mode (prepared statements, LISTEN/NOTIFY, session advisory locks) and how to handle them, the mandatory configuration for serverless (Lambda/Edge), and RDS Proxy and Supabase Supavisor.

- Published: 2026-06-25
- Author: 友田 陽大
- Tags: PostgreSQL, パフォーマンス, アーキテクチャ設計
- URL: https://tomodahinata.com/en/blog/postgresql-connection-pooling-pgbouncer-serverless-guide
- Category: PostgreSQL operations & reliability
- Pillar guide: https://tomodahinata.com/en/blog/postgresql-production-operations-guide

## Key points

- PostgreSQL forks one OS process per connection. Each backend eats memory, and work_mem is also multiplied per connection. So raising max_connections isn't the solution.
- PgBouncer's 3 modes: session (default, maximum compatibility), transaction (maximum reuse, recommended for Web/serverless), statement (most aggressive, autocommit only).
- Transaction mode breaks session state: SET/RESET, LISTEN/NOTIFY, session advisory locks, SQL-level PREPARE, WITH HOLD cursors. The countermeasure is not using session features + pg_advisory_xact_lock + protocol-level prepared support.
- The pool size is 'a small multiple of the CPU core count,' not 'the number of clients.' Make max_client_conn large and squeeze default_pool_size (default 20) small — that's the funnel design.
- Serverless (Lambda/Edge) falls over with a connection storm if connected directly. A transaction-mode pooler (PgBouncer/RDS Proxy/Supavisor) is effectively mandatory. Supavisor's transaction mode is port 6543 and doesn't support prepared statements.

---

"The DB fell over with `too many clients` when concurrent access increased," "connections were exhausted when I ran Lambda in parallel" — these are all caused by connecting directly without knowing PostgreSQL's **connection model.**

PostgreSQL handles connections with **OS processes.** So a connection isn't a "cheap resource" but a "**high fixed cost.**" In production, especially serverless, **connection pooling** becomes mandatory. This article organizes why it's needed, which mode to choose, what breaks, and how to fix it, faithful to the official and vendor materials. It's a deep dive on [§5 of the production-operations guide](/blog/postgresql-production-operations-guide).

> **Rules for this article**: the PostgreSQL server-side spec is based on the **PostgreSQL 18 official documentation**, PgBouncer's behavior on the **PgBouncer official documentation**, and RDS Proxy / Supavisor on the **AWS / Supabase official** docs respectively (sources noted). Community lore like pool-size rules of thumb is annotated as such.

---

## 1. Why a connection is "expensive": process per connection

The official docs state PostgreSQL's connection model clearly.

> PostgreSQL implements a "process per user" client/server model. … Each time it detects a connection request, it **spawns (forks) a new backend process.**

Not threads but **processes.** 1 connection = 1 OS process, with a fixed memory overhead, and furthermore `work_mem` is **allocated per backend, per operation** ([§4.2 overview](/blog/postgresql-performance-tuning-production-guide)). With 500 connections, 500 processes eat memory, and the CPU is worn down by context switching.

The default of `max_connections` is **100** (changeable only at server startup). "Raise it if it's not enough" is a **trap.** What you raise eats up that much memory and CPU, and at some threshold performance falls off a cliff.

> **Conclusion**: raising `max_connections` is not the solution. **Reuse a small number of physical connections across many clients** — that's connection pooling.

Note that some of `max_connections` is reserved: `superuser_reserved_connections` (default **3**, for emergencies), `reserved_connections` (default **0**, PG16+). What general roles can use is roughly `max_connections − these reservations`.

---

## 2. The pooler's role: a funnel

A connection pooler stands between the app and PostgreSQL, **aggregating many client connections into a small number of physical connections.**

```text
[アプリ: 数百〜数千の接続] ──▶ [プーラー: max_client_conn] ──▶ [物理接続: default_pool_size（少数）] ──▶ PostgreSQL
        安い・大量でOK              入口は広く                       出口は狭く絞る
```

With PgBouncer, you design this funnel with `max_client_conn` (the client-side entrance, default 100, OK to make large) and `default_pool_size` (physical connections per DB/user pair, default **20**, squeeze this).

---

## 3. The three pool modes: session / transaction / statement

The core of PgBouncer is **`pool_mode`** — "when to let a server connection be reused by another client." Per the official definition.

| Mode | When the server connection is returned | Use |
| --- | --- | --- |
| **session** (default) | When the client **disconnects** | Maximum compatibility, minimum reuse. Legacy / session features required |
| **transaction** | At each **transaction end** | **Maximum reuse. Recommended for Web/serverless** |
| **statement** | At each **query end** (multi-statement transactions forbidden) | Most aggressive, autocommit only. Niche |

The practical answer is clear. **Web apps and serverless use `transaction` mode.** Since it lends one physical connection "only for the duration of the transaction," a small pool handles thousands of clients.

```ini
# pgbouncer.ini（トランザクションモードの基本形）
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000        # 入口は広く
default_pool_size = 20        # 出口（物理接続）は CPU コアの小さな倍数に絞る
server_reset_query = DISCARD ALL
```

---

## 4. The transaction-mode trap: session state breaks

This is the **most important** part. In transaction mode, "consecutive transactions of the same client **ride on different physical connections**," so **state tied to the session breaks.** The targets PgBouncer's official docs state plainly "break some session-based features":

- **`SET` / `RESET`** (session variables; `SET LOCAL` is within a transaction so it's OK)
- **`LISTEN` / `NOTIFY`** (notifications are missed when the connection changes)
- **Session advisory locks** (`pg_advisory_lock`)
- **SQL-level `PREPARE` / `DEALLOCATE`** (server-side prepared statements)
- **`WITH HOLD` cursors**, **temporary tables**, **`LOAD`**

The official basis (the explanation of `server_reset_query`):

> In transaction pooling, since each transaction rides on a different connection and gets **different session state**, the client must not use session-based features.

Moreover, per the official docs, in transaction mode **`server_reset_query` (default `DISCARD ALL`) is not used** (it isn't reset on each lend-out). So "not depending on session state" becomes the **app's responsibility.**

### Countermeasure 1: change the design to not use session features

| Feature that breaks | Alternative |
| --- | --- |
| `pg_advisory_lock` (session) | **`pg_advisory_xact_lock`** (auto-released at the transaction boundary) |
| `SET search_path = ...` (session) | The connection string's `options=-c search_path=...`, or `SET LOCAL` |
| `LISTEN`/`NOTIFY` | A dedicated connection bypassing the pooler, or separate messaging |
| SQL `PREPARE` | Protocol-level prepared (below) |

```sql
-- ❌ セッションアドバイザリロック（トランザクションモードで壊れる）
-- SELECT pg_advisory_lock(42);
-- ✅ トランザクションスコープ（コミット/ロールバックで自動解放。プーラー安全）
BEGIN;
SELECT pg_advisory_xact_lock(42);
-- ... クリティカルセクション ...
COMMIT;
```

### Countermeasure 2: prepared statements at the protocol level

PgBouncer 1.21+, with **`max_prepared_statements` (default 200)** set non-zero, can track and rewrite **protocol-level named prepared statements** to handle them safely even in transaction/statement mode. However, **SQL-level `PREPARE`/`DEALLOCATE` still break.**

The client-library-side setting is also needed.

```ts
// node-postgres: トランザクションプーラー配下では「クライアント側のステートメントキャッシュ」を避け、
// パラメータ化クエリ（プロトコルレベル）に寄せる。ORM 各種はプーラー用フラグを持つ。
import { Pool } from "pg";

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // 例: PgBouncer/Supavisor のエンドポイント
  max: 10,                  // アプリ内プールは小さく（外側のプーラーが本命）
  // node-postgres はデフォルトでサーバー側プリペアドを多用しない。
  // Prisma なら ?pgbouncer=true、Drizzle/postgres.js なら prepare:false 相当を検討。
});
```

> **The design implication**: transaction mode demands "**the discipline of being stateless**" in exchange for "speed." Thoroughly using `SET LOCAL`, `pg_advisory_xact_lock`, and parameterized queries makes compatibility problems almost vanish.

---

## 5. How to decide the pool size: core count, not client count

Counterintuitively, **the pool size is decided not by the number of clients connecting but by the DB server's CPU core count.** There's an upper bound to the number of queries the CPU can execute simultaneously, and a pool far larger than the core count **only increases contention.**

A widely used rule of thumb (the empirical rule of the PgBouncer/HikariCP family. **Community guidance, not the official spec**):

```text
推奨プールサイズ ≈ (コア数 × 2) + 実効スピンドル数
例：8コア・SSD（実効スピンドル≒1〜2）→ 約 18〜20 物理接続
```

And the **iron rule**: `default_pool_size × (the number of DB/user pairs)` must **not exceed** the server's `max_connections` (minus reservations). Exceed it and the pooler can't open physical connections and stalls.

---

## 6. Mandatory in serverless: prevent the connection storm

Connecting serverless (Lambda/edge functions) directly to PostgreSQL is **an antipattern.** Functions scale horizontally and open **a new direct connection** per concurrent execution. In a spike, hundreds to thousands of connections stand up at once, blow past `max_connections`, and cause a **connection storm.** Functions are short-lived, so the connection-establishment cost can't be amortized either.

The countermeasure is to **always interpose a transaction-mode pooler.** The main options:

| Pooler | Positioning | Source |
| --- | --- | --- |
| **PgBouncer** | The standard. Lightweight, single-threaded. Strong with many idle connections | PgBouncer official |
| **AWS RDS Proxy** | Managed. Pools/shares connections for Lambda. IAM/Secrets integration, failover support | AWS official |
| **Supabase Supavisor** | Managed. **Transaction mode is port 6543.** Recommended for serverless | Supabase official |
| **PgCat / Odyssey** | Multi-threaded, scales to core count. Built-in sharding/LB/failover | Each OSS |

**AWS RDS Proxy** (official): "Applications can **pool and share connections** to improve scalability," "handles **surges in connections** and avoids the memory/CPU overhead of opening a new connection each time by reusing connections from the pool." Connections it can't lend immediately are **queued/throttled.**

**Supabase Supavisor** (official): "Use transaction mode for **transient clients like serverless or edge functions.**" However, "**transaction mode doesn't support prepared statements.** Disable the client library's prepared statements to avoid errors" — the same caution as §4.

```ts
// サーバーレス（Lambda/Edge）の鉄則：
// 1) 直結しない → プーラーのエンドポイント（例 Supavisor:6543 / RDS Proxy）に接続
// 2) ハンドラ外でプールを生成し、ウォームインスタンス間で再利用（毎回 new しない）
// 3) プリペアドを無効化、SET LOCAL / pg_advisory_xact_lock を使う
import { Pool } from "pg";

// モジュールスコープ＝コールド起動時に1度だけ。ウォーム再利用で接続確立を償却
const pool = new Pool({ connectionString: process.env.POOLER_URL, max: 2 });

export async function handler(event: { id: string }) {
  const { rows } = await pool.query(
    "SELECT id, status FROM orders WHERE id = $1", // パラメータ化（インジェクション対策）
    [event.id],
  );
  return rows[0] ?? null;
  // 注意: pool.end() をハンドラ末尾で呼ばない（次の呼び出しで再利用するため）
}
```

---

## 7. Operations: monitor and maintain the pooler

PgBouncer has an admin pseudo-DB and can visualize its operation (PgBouncer official).

```bash
# 管理コンソール（admin_users に登録したユーザーで）
psql -p 6432 -U pgbouncer_admin pgbouncer
```

```text
SHOW POOLS;     -- DB/ユーザー対ごとのプール状態（待ち・使用中・アイドル）
SHOW STATS;     -- トランザクション/クエリの統計
PAUSE appdb;    -- メンテ用にドレイン（新規トランザクションを保留）
RESUME appdb;   -- 再開
RELOAD;         -- 設定の再読み込み（無停止）
```

If `cl_waiting` (waiting clients) in `SHOW POOLS` is consistently high, it's a sign that `default_pool_size` is too small (or the DB is slow). Combining `SHUTDOWN WAIT_FOR_CLIENTS` and `so_reuseport` also enables a zero-downtime rolling restart.

> **Change in PostgreSQL 18**: the connection-model and reserved-slot specs are **unchanged from 16 to 18.** The connection-pooling design holds as stable knowledge.

---

## 8. Conclusion

- PostgreSQL is **1 connection = 1 process.** A connection is a high fixed cost, and raising `max_connections` isn't the solution.
- **Make a funnel with a pooler**: the entrance (`max_client_conn`) wide, the exit (`default_pool_size`) squeezed.
- **Web/serverless is transaction mode.** But **session state breaks** — avoid it with `SET LOCAL`, `pg_advisory_xact_lock`, parameterization, and disabling prepared statements.
- **Pool size is core-count-based** (not client count). `pool_size × number of pairs < max_connections`.
- **Serverless requires a pooler** (PgBouncer / RDS Proxy / Supavisor). A direct connection dies in a connection storm.

Connection design is the layer that decides "**not falling over**" before "speed." Next, on top of that, go to [backup & PITR](/blog/postgresql-backup-pitr-pg-dump-wal-archiving-guide), which guarantees "being able to revert."

---

### References (primary sources)

- [PostgreSQL 18: 19.3. Connection Settings](https://www.postgresql.org/docs/18/runtime-config-connection.html)
- [PostgreSQL 18: 19.3.1. How Connections Are Established](https://www.postgresql.org/docs/18/connect-estab.html)
- [PgBouncer: Features (pool modes)](https://www.pgbouncer.org/features.html)
- [PgBouncer: Configuration](https://www.pgbouncer.org/config.html)
- [AWS: Amazon RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html)
- [Supabase: Connecting to Postgres (Supavisor)](https://supabase.com/docs/guides/database/connecting-to-postgres)