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

"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.

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). 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.

[アプリ: 数百〜数千の接続] ──▶ [プーラー: 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.

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.

# 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

-- ❌ セッションアドバイザリロック（トランザクションモードで壊れる）
-- 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.

// 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):

推奨プールサイズ ≈ (コア数 × 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:

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.

// サーバーレス（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).

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

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, which guarantees "being able to revert."

References (primary sources)

• PostgreSQL 18: 19.3. Connection Settings
• PostgreSQL 18: 19.3.1. How Connections Are Established
• PgBouncer: Features (pool modes)
• PgBouncer: Configuration
• AWS: Amazon RDS Proxy
• Supabase: Connecting to Postgres (Supavisor)