一个查询接口上线两天后开始间歇性 500。日志里是 remaining connection slots are reserved for non-replication superuser connections,数据库的 max_connections 是 100,应用只有两台机器,怎么看都不该用满。翻代码才发现,每个请求处理函数里都写了 new Pool(),用完也没有 end()。池对象随请求创建,连接只增不减,数据库连接被一点点吃光。

第二个事故更安静。一个「下单」事务里,先锁库存,再调用外部支付接口,最后写订单。支付接口偶尔要等三秒,这三秒里数据库连接一直开着,事务也一直开着。并发一高,连接池被占满,其他接口排队等连接,监控上表现为整站变慢,而慢的根源只是一个不该放在事务里的网络调用。

第三个问题出现在发布日。开发在测试库手动加了一列,代码里也用了新列,测试一直正常。生产迁移脚本没包含这一列,发布后所有查询都报 column does not exist。数据库结构没有版本化,代码和库各走各的,迟早会在某次发布对上。这篇从连接池的容量算术讲到迁移和事务,所有数据库示例都在本机临时 PostgreSQL 实例上真实执行过。生产规划按 PostgreSQL 16,实测实例是 18.6,本文涉及的 SQL 与驱动行为两者一致。

连接池:先算容量,再谈参数

连接池是一组预先建立、可重复使用的数据库连接。请求需要查询时从池里借一个,用完还回去,避免每次请求都走 TCP 握手和认证。PostgreSQL 的每个连接都是一个后端进程,占用内存,也有上限,所以池不是越大越好。

pg 的 Pool 有四个参数最常被调:max 是池里最多同时存在多少个连接;idleTimeoutMillis 是空闲连接多久后被回收;connectionTimeoutMillis 是借连接最多等多久;allowExitOnIdle 决定空闲时是否允许进程退出,脚本里常用。

容量要按「所有会连数据库的进程」一起算,而不是只看一个服务。假设数据库 max_connections 是 100,其中 3 个留给超级用户,应用有 4 个副本,每个副本的池 max 设为 20,另外还有 2 个后台任务进程各 5 个连接。总数是 80 加 10 再加 1 个迁移连接,共 91,剩下 9 个余量。这个配置是安全的;如果把每个池设成 50,4 个副本就是 200,数据库会直接拒绝新连接。

数据库上限: max_connections 是硬上限,包含所有库、所有用户、复制连接和运维连接,不是给单个应用专用的额度。
进程池上限: 每个 Node 进程一个 Pool,max 是这个进程能同时持有的连接数,所有副本相加才是应用总占用。
请求排队: 池满时请求在应用内存里等,等超过 connectionTimeoutMillis 就抛错。这个等待不占数据库连接,但会占住 HTTP 请求。

连接池耗尽时,pg 抛出的错误很直接。把 max 设为 2,先借走两个连接,再借第三个,实测输出是:

POOL_TIMEOUT=timeout exceeded when trying to connect

这条错误通常意味着两件事之一:池太小,或者连接被借走太久没还。先看 pool.totalCount、idleCount、waitingCount 三个数字,再决定是调大 max 还是修代码。真实跑完一组查询和事务后,池的状态是 {"total":2,"idle":2,"waiting":0},说明连接已经还回去了。

不要在请求处理函数里 new Pool()。池必须是进程级单例,在模块加载时创建一次,进程退出时 end()。每个请求建池等于每个请求新建连接,既慢又会把数据库连接数打满。

参数化查询:挡住注入,也挡住脏类型

SQL 注入的本质是把用户输入当成 SQL 语法的一部分执行。拼接字符串时,输入里的引号、分号、or 1=1 都能改变语句结构。参数化查询把 SQL 文本和参数分开传输,数据库只把参数当值,不参与语法解析。

const { rows } = await pool.query(
  'select id, status from orders where id = $1',
  [req.params.id]
);

用 '1 or 1=1' 作为参数实测,数据库没有返回全表,而是直接报类型错误:

INJECT=22P02 invalid input syntax for type bigint: "1 or 1=1"

这个错误说明参数被当成了一个值,而不是语句片段。它同时也暴露了一个工程问题:参数类型不匹配时,数据库会报错,应用不能把原始错误直接返回给用户。应该在进入查询前用 zod 或手工校验把 id 转成合法的正整数,失败就返回 400,而不是让 22P02 变成 500。

pg 默认会把 bigint 和 numeric 转成字符串,因为 JavaScript 的 Number 无法安全表示所有 64 位整数,浮点数也会丢精度。这是驱动的保守选择,不是 bug。一个真实查询的响应因此长这样:

{"id":"1","user_id":"2","status":"pending","amount":"2.50"}

如果前端直接把 amount 当数字用,就会遇到字符串相加。解决方式有两种:在 SQL 里显式转换,比如 amount::float8;或者在 Repository 层用映射函数统一转换。不要全局修改 pg 的类型解析器,除非团队清楚每个字段的取值范围,尤其是金额和 ID。

Repository 层:把 SQL 收在一处

路由处理函数直接写 SQL,短时间最快,接口一多就失控:同一个表在不同文件里有五种查法,索引改了不知道哪些查询受影响,测试也没法替换数据源。Repository 层的作用不是「多写一个类」,而是把「这张表怎么查」收在一个文件里,路由只调用语义化的方法。

export async function findOrderById(id) {
  const { rows } = await pool.query(
    'select id, user_id, status, amount from orders where id = $1',
    [id]
  );
  const row = rows[0];
  if (!row) return null;
  return {
    id: Number(row.id),
    userId: Number(row.user_id),
    status: row.status,
    amount: Number(row.amount),
  };
}

这一层负责三件事:SQL 文本、参数绑定、行到对象的映射。路由只判断「有没有」和「怎么响应」。事务不放在 Repository 的单个方法里,而是由服务层传入同一个 client,否则一次业务操作里的多条 SQL 会各自从池里借连接,根本不在一个事务中。

示例依赖和启动命令如下,迁移工具与运行依赖分开列,方便生产只装前者之外的运行包:

{
  "name": "data-lab",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node src/server.js",
    "migrate": "node-pg-migrate up"
  },
  "dependencies": {
    "express": "^5.3.0",
    "pg": "^8.23.1"
  },
  "devDependencies": {
    "node-pg-migrate": "^9.0.0"
  }
}

npm install express@5 pg@8
npm install --save-dev node-pg-migrate@9
npm start

真实接口 GET /orders/1 返回 200 和映射后的对象,查不到时返回 404:

curl -i 127.0.0.1:3203/orders/1

HTTP/1.1 200 OK

{"id":"1","user_id":"2","status":"pending","amount":"2.50"}

curl -i 127.0.0.1:3203/orders/999999

HTTP/1.1 404 Not Found

{"error":"order_not_found"}

注意第二条响应没有泄露「查询了哪张表」。错误码是稳定的,日志里才有完整 SQL 和参数。把数据库错误原样返回,既是安全问题,也是维护问题:一旦 SQL 改了,前端依赖的错误文案也跟着变。

pg 与 Prisma 的取舍

pg 是 PostgreSQL 的驱动,负责连接、查询、结果集,SQL 由你写。Prisma 是 ORM,负责 schema 定义、类型生成、查询构造和迁移,SQL 由它生成。两者不是同一层的东西,但都能完成「查一张表」。

选 pg 的理由

SQL 完全可控,执行计划和索引优化看得见;复杂联表、窗口函数、FOR UPDATE 都能直接写;运行时依赖少,行为透明。代价是手写映射和迁移,容易在不同文件里写出不一致的查询。

选 Prisma 的理由

schema 即类型,编辑器里能补全字段;迁移和客户端生成一体化;常规增删改查的开发速度快。代价是复杂查询要绕回原生 SQL,生成客户端有构建步骤,出问题时要在 ORM 和数据库之间多排查一层。

判断标准是查询复杂度与团队习惯。以简单 CRUD 为主、TypeScript 全覆盖、希望减少手写 SQL 的项目,Prisma 的收益明显。以报表、批量更新、行锁、复杂事务为主的项目,pg 加手写 SQL 更稳。两者也可以共存:核心交易走 pg,后台管理走 Prisma,但要接受两套连接池和两套迁移工具带来的运维成本。

无论选哪个,都要守住一条:事务边界由业务决定,不能由 ORM 的默认行为决定。Prisma 的交互式事务需要显式传入 tx 客户端,pg 需要显式 connect 和 release,本质相同。

NestJS 的数据层对照

NestJS 11 把数据访问也放进依赖注入容器:Repository 写成 @Injectable() 的 Provider,通过构造函数注入 Service,连接池由 TypeOrmModule 或 PrismaModule 在模块初始化时创建,模块销毁时关闭。Express 里我们用模块级 Pool 加导出的函数,装配靠 import,关闭靠退出流程里手动调用 pool.end()。

差别不在 SQL,而在生命周期由谁管。NestJS 由容器统一创建和释放,Express 要自己保证单例、自己保证释放。事务边界仍然在 Service,不能因为注入了 Repository 就以为一次业务调用会自动共享事务;NestJS 里同样要显式传 tx 或同一个 client。

迁移:让库结构和代码一起版本化

迁移是把数据库结构变化写成有顺序、可重复执行的脚本,并记录哪些已经执行过。手改测试库、再把 SQL 发给运维,短期能跑,长期一定会漂移:测试库多了列,生产没有;生产多了索引,测试没有;新人本地更是什么都没有。

node-pg-migrate 用时间戳命名迁移文件,执行后在 pgmigrations 表里记一条。下面这个文件创建 app_users 表,文件名前缀是时间戳,后缀用 .cjs,因为项目是 ESM,迁移工具仍按 CommonJS 加载:

exports.up = (pgm) => {
  pgm.createTable('app_users', {
    id: 'id',
    email: { type: 'text', notNull: true, unique: true },
    role: { type: 'text', notNull: true, default: 'member' },
    created_at: { type: 'timestamptz', notNull: true, default: pgm.func('now()') },
  });
  pgm.createIndex('app_users', 'email');
};

exports.down = (pgm) => {
  pgm.dropTable('app_users');
};

执行命令与真实输出:

npx node-pg-migrate up

> Migrating files:
> - 1720000000000_create-app-users
### MIGRATION 1720000000000_create-app-users (UP) ###
CREATE TABLE "app_users" (
  "id" serial PRIMARY KEY,
  "email" text UNIQUE NOT NULL,
  "role" text DEFAULT $pga$member$pga$ NOT NULL,
  "created_at" timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX "app_users_email_index" ON "app_users" ("email");

Migrations complete!

生产环境的原则是「只进不退」:迁移一旦执行,就不要指望 down 能安全回滚,尤其是已经写入数据的列。正确做法是把每次变化拆成向前兼容的小步:先加可空列,双写一段时间,再回填,最后加非空约束。删除列也分两步,先停止读写,再在下一次发布删除。这样任何一次发布都可以只回退代码,不必回退数据库。

迁移脚本不要和应用的启动强绑定成「每次启动自动跑」。多个副本同时启动会并发执行同一个迁移,轻则报重复对象,重则把表改到一半。把迁移作为发布流程里的独立步骤,只由一个任务执行,成功后再滚动更新应用。

事务:同一个连接,明确的边界

事务要求一组 SQL 要么全成功,要么全失败。在 pg 里,这意味着必须从池里借同一个 client,在这个 client 上依次执行 BEGIN、业务 SQL、COMMIT;任何一步失败就 ROLLBACK,最后无论成败都要 release。

const client = await pool.connect();
try {
  await client.query('begin');
  const { rows } = await client.query(
    'insert into orders (user_id, amount) values ($1, $2) returning id, status, amount',
    [userId, amount]
  );
  await client.query(
    'insert into order_items (order_id, sku, qty) values ($1, $2, $3)',
    [rows[0].id, 'SKU-NEW', 1]
  );
  await client.query('commit');
  return rows[0];
} catch (err) {
  await client.query('rollback');
  throw err;
} finally {
  client.release();
}

真实执行一次「改状态后回滚」,输出是:

TX={"before":{"status":"pending"},"inside":{"status":"cancelled"},"after":{"status":"pending"}}

事务内看得到未提交的修改,回滚后数据恢复原状。这个实验能验证三件事:BEGIN 生效、UPDATE 在事务内可见、ROLLBACK 真的撤销了写入。如果 before 和 after 都是 cancelled,说明 COMMIT 被提前执行,或者根本没用同一个 client。

最危险的写法是在事务里 await 外部 HTTP 调用。数据库连接在等待网络期间一直被你占着,事务也一直开着,其他请求只能排队。更糟的是,如果外部调用超时后你又抛错回滚,这段时间的锁和连接全白占。正确顺序是:先在事务外完成外部调用,拿到结果后再开一个短事务写库;或者用 outbox 模式,把「待调用」写进本地表,由后台任务异步执行。

另一个细节是错误处理。ROLLBACK 本身也可能失败,比如连接已经断开,此时再抛原始错误会掩盖根因。比较稳的写法是:回滚失败只记录日志,仍然抛出原始错误;finally 里的 release 必须执行,但可以传入 true 标记连接已损坏,让池丢弃它而不是放回去。

行锁、死锁与重试

两个请求同时改同一条订单,后写的会覆盖先写的。要避免这种情况,用 SELECT ... FOR UPDATE 在事务里锁住这一行,第二个事务必须等第一个提交或回滚后才能继续。等待是正常的,但等太久会拖垮连接池,所以常配 NOWAIT 或 SKIP LOCKED。

实测两个事务争同一行,第二个用 FOR UPDATE NOWAIT,立即拿到错误:

LOCK=55P03 could not obtain lock on row in relation "orders"

55P03 表示「现在拿不到锁」,不是数据库坏了。接口可以把它映射成 409,提示「该订单正在处理中,请稍后重试」;也可以退化成等待,但要设置 lock_timeout,别让请求无限挂着。

死锁发生在两个事务以相反顺序锁两行:A 锁第一行等第二行,B 锁第二行等第一行。PostgreSQL 会检测到环,选择一个事务回滚,报 40P01 deadlock detected。这不是配置错误,而是访问顺序不一致。解决办法是统一加锁顺序,比如永远先锁订单再锁库存;无法统一时,捕获 40P01 后做有限次重试,每次带随机退避。

async function withDeadlockRetry(fn, attempts = 3) {
  for (let i = 0; i < attempts; i += 1) {
    try {
      return await fn();
    } catch (err) {
      if (err.code !== '40P01' || i === attempts - 1) throw err;
      await new Promise((r) => setTimeout(r, 50 * (i + 1) + Math.random() * 50));
    }
  }
}

重试的前提是函数可重复执行:事务必须整体重开,不能复用已经回滚的 client;业务里不能有「已经发了消息」这种不可撤销的副作用。做不到幂等,就不要自动重试,宁可把错误暴露出来。

N+1 与分页:慢查询往往不在 SQL 本身

列表页先查 20 个订单,再对每个订单查一次明细,就是 21 次数据库往返。数据量小的时候感觉不到,订单涨到几百条、并发几十个请求时,连接池会被这些短查询迅速占满。解决办法不是把池调大,而是把查询合并:用一次 JOIN 或一次 IN 查询把明细取回来,再在应用层组装。

select o.id, o.status, i.sku, i.qty
from orders o
join order_items i on i.order_id = o.id
where o.id = any($1::bigint[])

分页也有两种常见做法。LIMIT/OFFSET 写起来简单,但页码越深,数据库要跳过的行越多,越翻越慢;键集分页用上一页最后一条的 ID 作为游标,where id > $1 order by id limit 20,每页成本稳定,适合无限滚动和后台列表。代价是不能直接跳到第 N 页,产品上要接受这个限制。

分页还要防止「传入 limit=100000」。服务端必须给上限,比如最大 100,并在响应里告诉调用方是否还有下一页。否则一次请求就能把连接和内存占满,这类事故和连接池配置无关,是接口契约缺失。

现象、真实原因与第一步

现象真实原因第一步查什么
timeout exceeded when trying to connect池满,连接被借走未还,或并发超过 max打印 totalCount、idleCount、waitingCount,查未释放的 client
数据库报连接槽不足所有副本的池上限相加超过 max_connections用 pg_stat_activity 按应用用户统计连接数,重算容量
整站变慢但 CPU 不高事务里等外部接口,连接和锁被长时间占用查 pg_stat_activity 里 state 为 idle in transaction 的会话
发布后大量 column does not exist迁移未执行,或测试库被手改而生产没有对比 pgmigrations 记录与代码里用到的列,检查迁移文件是否进版本库
金额出现字符串拼接pg 把 numeric 和 bigint 返回成字符串在 Repository 里显式转换,或用 SQL 的 ::float8
并发更新互相覆盖读后写没有行锁,两个事务都基于旧值计算用 FOR UPDATE 锁行,并确认事务用的是同一个 client
偶发 40P01 deadlock detected两个事务以相反顺序锁多行梳理加锁顺序,统一后仍冲突则加有限重试
列表页越翻越慢OFFSET 很大,或存在 N+1 查询看执行计划,改成键集分页并合并明细查询

边界与代价

连接池能复用连接,但不能提高数据库的并发上限。池调大只是把更多请求同时压给数据库,超过数据库能承受的范围后,所有查询一起变慢。容量的正确算法是「数据库可用连接数」除以「所有进程的池上限之和」,留出余量。

ORM 能省掉手写 SQL,但会把复杂度转移到别处:生成的 SQL 可能不是你想要的执行计划,复杂报表要绕回原生查询,迁移工具的模型和数据库真实结构之间可能有一层抽象。选 ORM 不是免掉数据库知识,而是换了一种理解 SQL 的方式。

迁移的代价在纪律。文件一旦合并进主分支就不能随便改,因为别人的库可能已经执行过;生产不能依赖 down 回滚,必须设计向前兼容的步骤;多副本部署不能自动跑迁移,必须有一个单独的执行点。这些规矩比写一条 CREATE TABLE 难。

事务的代价是锁和连接。事务越长,别人等得越久;隔离级别越高,冲突越多。原则是「事务里只做数据库操作,能短则短」,外部调用、文件读写、消息发送都挪到事务外,或者用 outbox 保证最终一致。不要为了「看起来安全」把所有逻辑都塞进一个长事务。

当天能自己验证的练习

  1. 创建一个 max 为 2 的池,借走两个 client 后再借第三个,把 connectionTimeoutMillis 设为 300,记录真实错误并观察 waitingCount。
  2. 用 '1 or 1=1' 作为参数查询 bigint 主键,确认返回 22P02 而不是全表数据;再把参数改成合法数字,对比两次结果。
  3. 在一个事务里更新一行,查询事务内和回滚后的值,确认 before 与 after 一致;故意把 rollback 去掉,观察数据被提交。
  4. 开两个 client,第一个 FOR UPDATE 锁行,第二个用 NOWAIT,记录 55P03;再让两个事务以相反顺序锁两行,尝试复现 40P01。
  5. 新建一张表,写一个 node-pg-migrate 迁移并执行 up,查 pgmigrations 表确认记录;再改一次迁移文件后重新执行,观察工具是否重复执行。

小结

三句话:连接池是进程级单例,容量要按所有副本和后台进程一起算;SQL 用参数化,事务用同一个 client,外部调用不要放在事务里;结构变化写成版本化迁移,生产只向前兼容,不指望回滚。

下一篇讲鉴权。数据层能保证「谁能查到哪一行」,但「这个请求是谁发的」还没有答案。JWT 放在哪、退出登录后令牌为什么还能用、角色判断为什么不能只放前端,这些问题会在真实接口上产生 401 和 403,而两者的排查方向完全不同。下一篇会从一个被 XSS 偷走令牌的案例开始,把会话、Cookie 属性和权限边界串起来。