PricingLoginStart free
PostgreSQL Performance & Query Optimization · 课时

事务池化与会话池化模式

选择正确的 PgBouncer 模式，并了解事务池化会导致哪些功能失效。

第 2 / 4 课13 个步骤

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

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

Why Pooling Modes Matter

Each PostgreSQL backend process costs memory (work_mem, catalog caches, plan caches) and CPU. A few thousand idle client connections can exhaust a server even when nothing is running.

PgBouncer sits between your app and PostgreSQL, multiplexing many client connections onto a small set of real server connections. The pool_mode setting decides when a server connection is handed back to the pool.

  • session — server held for the client's whole session
  • transaction — server held only for one transaction
  • statement — server released after every statement

Session Pooling Mode

In session pooling, a server connection is assigned to a client when it connects and is only returned to the pool when the client disconnects.

This is the safest mode: the client gets a dedicated backend for its entire lifetime, so every PostgreSQL feature behaves exactly as if it connected directly. The cost is poor reuse — an idle but connected client still pins a server connection.

; PgBouncer config: pgbouncer.ini
[pgbouncer]
pool_mode = session
max_client_conn = 10000
default_pool_size = 20

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

Transaction Pooling Mode

In transaction pooling, the server connection is assigned only for the duration of a single transaction. The instant the transaction commits or rolls back, the backend goes back to the pool and may serve a different client next.

This gives dramatically better reuse: thousands of mostly-idle clients can share a tiny pool, because a server connection is only borrowed during active work. It is the recommended mode for web apps with many short-lived requests.

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20

; 10000 clients multiplexed onto only 20 backends

The Core Tradeoff

The decision is reuse versus feature compatibility:

  • Session: full compatibility, low connection reuse.
  • Transaction: high reuse, but anything that relies on state outside a transaction can break.

The key insight: in transaction mode, consecutive transactions from the same client may land on different backends. Anything that lives on the connection between transactions is unsafe.

What Breaks: Session-Level State

Because a backend is shared across clients between transactions, any session-scoped state set in one transaction can leak to another client or be lost.

These commonly break under transaction pooling:

  • SET / SET SESSION session GUCs (e.g. SET statement_timeout, SET search_path outside a transaction)
  • Session-level advisory locks (pg_advisory_lock)
  • LISTEN / NOTIFY subscriptions
  • Unparameterized session variables and WITH HOLD cursors
-- Unsafe in transaction pooling: runs in its own tx,
-- the GUC is reset before your next query reuses a backend
SET statement_timeout = '5s';

-- Session advisory lock may be acquired on one backend
-- and never matched by the unlock on another
SELECT pg_advisory_lock(42);

What Breaks: Prepared Statements

Named prepared statements are stored on a specific backend. In transaction mode, your next execution may hit a different backend that has never seen that prepared statement, causing errors like prepared statement "sN" does not exist.

Mitigations:

  • Disable client-side prepared statements, or use simple/unnamed protocol.
  • PgBouncer 1.21+ supports max_prepared_statements to track and re-prepare named statements per backend automatically.
; PgBouncer 1.21+ : safely allow named prepared statements
; in transaction mode by tracking them per server connection
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

Keep Settings Inside the Transaction

If you need a GUC like statement_timeout or search_path under transaction pooling, scope it to the transaction with SET LOCAL. It applies only until the transaction ends, so it can never leak to the next client on that backend.

Use this pattern instead of a bare SET.

BEGIN;
  SET LOCAL statement_timeout = '5s';
  SET LOCAL search_path = analytics, public;

  SELECT count(*) FROM orders WHERE created_at >= now() - interval '1 day';
COMMIT;

Long Transactions Pin the Pool

Transaction pooling only reuses backends between transactions. A long-running or idle-in-transaction query holds its backend the entire time, just like session mode would.

If many clients hold open transactions, the small pool drains and new requests queue. Guard against this:

  • Set a low idle_in_transaction_session_timeout on the server.
  • Keep transactions short; never BEGIN then wait on app-side I/O.
-- Server-side safety net (postgresql.conf or ALTER ROLE)
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10s';

-- Now an app that BEGINs and stalls gets its backend
-- reclaimed instead of starving the PgBouncer pool

Sizing default_pool_size

Under transaction pooling, default_pool_size is the number of real backends per (database, user) pair. Because work is interleaved, you need far fewer backends than clients.

A common starting point is roughly the number of CPU cores available for queries, not the number of clients. Oversizing the pool just recreates the connection-storm problem you used PgBouncer to avoid.

[pgbouncer]
pool_mode = transaction
default_pool_size = 20      ; ~ matches Postgres CPU capacity
min_pool_size = 5           ; keep warm backends ready
reserve_pool_size = 5       ; burst headroom
max_client_conn = 10000     ; how many apps can attach

Inspecting Pool Behavior

PgBouncer exposes a virtual admin database. Connect to it and run SHOW POOLS; to see, per pool, how many clients are active/waiting and how many server connections are active/idle.

If cl_waiting is consistently above zero, clients are queuing for a backend — either raise default_pool_size or shorten transactions.

-- psql -p 6432 pgbouncer
SHOW POOLS;
-- columns: database | user | cl_active | cl_waiting
--          sv_active | sv_idle | sv_used | pool_mode

SHOW STATS;   -- query/transaction throughput per database

Per-Database Mode Overrides

You do not have to pick one mode globally. Set a default pool_mode and override it per database. A typical split:

  • Main OLTP app database in transaction mode for maximum reuse.
  • A legacy or admin database that uses LISTEN/NOTIFY, advisory locks, or temp tables in session mode for correctness.
[databases]
; high-concurrency web traffic -> transaction reuse
appdb  = host=127.0.0.1 dbname=appdb pool_mode=transaction

; uses LISTEN/NOTIFY + session advisory locks -> keep session
jobsdb = host=127.0.0.1 dbname=jobsdb pool_mode=session

Quick Check

Choose the correct behavior under PgBouncer transaction pooling.

在交互式课程中回答

Recap

Session vs transaction pooling, distilled:

  • Session mode: backend held until client disconnects. Full feature compatibility, low reuse. Use it for databases needing LISTEN/NOTIFY, session advisory locks, or persistent prepared statements.
  • Transaction mode: backend released per transaction. High reuse for many short requests, but session-level state can leak or vanish.
  • What breaks in transaction mode: bare SET GUCs, named prepared statements, session advisory locks, LISTEN/NOTIFY, WITH HOLD cursors.
  • Fixes: SET LOCAL inside a transaction, max_prepared_statements (1.21+), short transactions, idle_in_transaction_session_timeout, and per-database pool_mode overrides.
免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程
22
课程
88
免费开始学习查看完整课程

常见问题解答

「事务池化与会话池化模式」课时是免费的吗？

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

「事务池化与会话池化模式」这节课中我会学到什么？

选择正确的 PgBouncer 模式，并了解事务池化会导致哪些功能失效。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「事务池化与会话池化模式」课时需要多长时间？

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

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

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

此课程中的所有课时

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