Skip to content

连接 Pooling

Manage 数据库连接 efficiently 使用 pooling 到 handle concurrency.

你将学到

  • 为什么数据库连接是 expensive 到创建
  • 如何连接 pool reuses 连接
  • 配置 pool 大小 and timeout 设置
  • 连接 leaks and 如何 prevent 它们
  • Serverless and 外部 pooling 解答 (PgBouncer)

概念讲解

为什么连接开销很大

Opening a database connection is not cheap. It involves a TCP handshake, authentication, session setup, and memory allocation on both the client and server. A typical PostgreSQL connection uses ~10 MB of memory on the server. If every HTTP request opens a new connection, you will quickly exhaust the database's max connections (default 100 in PostgreSQL) and memory.

How a Pool Works

A connection pool maintains a set of open connections and lends them out as needed. When your code needs a query, it borrows a connection from the pool, runs the query, and returns the connection. The connection stays open and is reused for the next request. This amortizes the connection cost across many requests and limits the total number of connections to the pool size.

Configuring the Pool

Key settings: max connections (how many connections the pool holds open — typically 10–50 per application instance), idle timeout (close connections that have been idle for too long), connection timeout (how long to wait for a connection before erroring), and max lifetime (recycle connections periodically to prevent stale connections). The optimal pool size depends on your database's max connections and the number of application instances.

Connection Leaks

A connection leak occurs when code borrows a connection but never returns it — usually because an exception skipped the return/close call. The pool gradually empties and the application hangs. Prevent leaks by always using a try/finally block or the pool's built-in withConnection / 事务 API that auto-releases connections.

Serverless and PgBouncer

In serverless environments (Lambda, Cloudflare Workers), each invocation is a short-lived process that cannot maintain a pool. Use an external pooler like PgBouncer (PostgreSQL) or ProxySQL (MySQL) that sits between your functions and the database, multiplexing many short-lived client connections onto a few persistent server connections. This is essential for serverless + relational database architectures.

示例代码

              -- PostgreSQL: check active connections
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
--  state   | count
--  active  | 12
--  idle    | 38

-- Set max connections (in postgresql.conf)
-- max_connections = 100

-- PgBouncer config (external pooler, transaction mode)
-- [pgbouncer]
-- listen_port = 6432
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 25

-- Application code pattern (Node.js with pg Pool)
-- const pool = new Pool({
--   host: 'localhost',
--   max: 20,              // pool size
--   idleTimeoutMillis: 30000,
--   connectionTimeoutMillis: 2000,
-- });
--
-- // Always use a try/finally to release
-- const client = await pool.connect();
-- try {
--   const res = await client.query('SELECT * FROM users WHERE id = $1', [42]);
-- } finally {
--   client.release();  // return to pool
-- }
            

检查 active 连接, 设置 max_connections, 配置 PgBouncer for serverless, and 正确 application 模式使用 try/最后到 prevent leaks.

动手试一试

常见错误

错误写法

打开新 连接每 请求不使用 pooling

正确做法

总是 use 连接 pool. 打开连接是 expensive and 你将 exhaust 数据库's 最大值连接 under 加载. 配置 pool 一次 at application startup.

错误写法

Forgetting 到发布连接 (连接 leaks)

正确做法

Use try/最后 or pool's 事务 API 到 guarantee 连接是 returned. Monitor pool stats and alert on 连接 in use approaching pool 大小.

练习 连接 Pooling

3 个练习

练习 连接 Pooling

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误