如何利用Pgbouncer并通过Drizzle解决TimescaleDB闲置连接过多问题

问题根源 #

你之前的代码每次调用timescaleConnection都会创建一个全新的postgres客户端实例，每个实例都维护独立的连接池，导致连接数无限制增长；同时客户端侧的idle_timeout可能因Pgbouncer的配置或连接池管理逻辑未生效，闲置连接无法被及时回收。

解决方案 #

1. 全局单例连接池（核心） #

必须使用全局单例的postgres连接池配合Drizzle，避免重复创建客户端实例。Drizzle本身不管理连接，依赖底层postgres驱动的连接池能力。

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

// 全局单例连接池，仅初始化一次
const pgClient = postgres(process.env.TIMESCALE_DATABASE_URL ?? '', {
  max: 10, // 连接池最大连接数，需匹配Pgbouncer的default_pool_size
  idle_timeout: 20, // 闲置连接20秒后自动回收
  connection_timeout: 5, // 连接超时5秒
  ssl: process.env.NODE_ENV === 'production' ? true : false, // 根据环境配置SSL
});

// 初始化Drizzle实例
export const db = drizzle(pgClient);

2. 正确执行查询与释放连接 #

• 普通查询：Drizzle的execute()方法会自动从连接池获取连接，执行完成后放回，无需手动处理

  import { yourTable } from './schema';
  
  async function fetchData() {
    const data = await db.select().from(yourTable).limit(10).execute();
    return data;
  }
  

• 手动事务/连接操作：如果需要手动控制连接，必须在finally块中释放连接回池

  async function runCustomTransaction() {
    const conn = await pgClient.connect();
    try {
      await conn.query('BEGIN');
      // 执行自定义SQL或Drizzle操作
      await db.insert(yourTable).values({ ... }).execute({ connection: conn });
      await conn.query('COMMIT');
    } catch (err) {
      await conn.query('ROLLBACK');
      throw err;
    } finally {
      conn.release(); // 必须释放，否则连接会被永久占用
    }
  }
  

3. 配合Pgbouncer配置优化 #

修改pgbouncer.ini，采用事务池模式（最适合OLTP/短查询场景），加速连接回收：

[databases]
timescaledb = host=timescaledb-host port=5432 dbname=your-db user=your-user password=your-pass

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction  # 事务模式，查询结束立即回收连接
max_client_conn = 100    # 允许的最大客户端连接数
default_pool_size = 10   # 每个数据库的连接池大小，需与postgres客户端的max参数匹配
server_idle_timeout = 20 # 后端闲置连接20秒后断开
server_lifetime = 3600   # 后端连接最大存活时间，避免长期占用

4. 排查额外问题 #

• 检查是否有未关闭的长连接（比如订阅、长事务），这类连接不会被idle_timeout回收
• 框架环境下（如Next.js），确保连接池实例是全局单例，避免在请求上下文重复初始化
• 验证postgres客户端的配置是否生效：可以通过pgClient.pool.counts查看连接池状态（闲置、活跃连接数）

内容的提问来源于stack exchange，提问作者PlayMa256