24 数据库中的代码

两个函数返回相同的行：某个账户的所有事件。一个用 SQL 编写，另一个用 PL/pgSQL 编写，内部查询相同。让它们分别查这个账户最近一天的事件，SQL 版本通过索引读取 7 页；PL/pgSQL 版本读取 2,040 页，取出该账户的全部 2,084 个事件，却丢掉其中 2,080 个。

这两个函数都没有问题。差别在于规划器能看见什么。数据库中的代码发挥得最好时，是你能拥有的最快代码：它在数据旁边运行，不需要往返通信；发挥得最差时，它会隐藏一个把写入者串行化的锁、一个过时的计划，或一个让每次批量更新都付出代价的触发器，而这一切都发生在那台无法继续横向扩展的服务器上。

主库是你最昂贵的 CPU

系统的其他层都可以靠增加机器来扩展：Web 服务器、工作进程、缓存、只读副本。主库是唯一无法横向扩展的那一层。每次写入都要经过同一台机器，而把它换成更大的机器，是扩容阶梯上最昂贵的一步（先用数据证明，该换更大的机器了）。函数或触发器在那里占用的每一个 CPU 周期，都在和每个查询竞争：花 1 毫秒格式化报表，就意味着结账插入要多等 1 毫秒才能得到一个核心。

我们的立场是：数据库负责让数据变少、保持数据正确，或节省一次往返通信。其他事情都交给应用层，即使 SQL 能够表达它。尤其如此。

• 留在数据库中： 过滤、连接和聚合（在应用中统计两百万个事件，意味着把整张表传过去，只为了算出一个数字）；基于集合的写入；必须与写入保持原子性的约束；以及任何能节省往返通信的工作。
• 移到应用中： 逐行格式化（按用户地区设置显示日期、格式化货币或 HTML）；经常变化的业务规则，这些规则应该用功能开关管理，并能在几分钟内回滚，而不是靠一次迁移；任何会调用外部世界的事情，因为事务回滚时，函数无法把已经发出的邮件收回来（把工作移出请求路径）；以及任何你希望用单元测试覆盖的事情。

下面看看格式化的代价。一个报表端点用 SQL 的 format() 和 to_char() 把 200,000 个事件变成句子；另一种做法只返回组成句子的六个字段：

服务器时间是四倍，字节数是两倍。应用本来也会花费大致相同的 CPU；区别在于 CPU 花在哪里。应用服务器一分钟就能加机器。每秒调用 10 次时，多出来的 170 毫秒会让主库将近两个核心整天都在格式化日期，其他查询只能排在它们后面等待。

如果逻辑是在保护数据或缩小数据，就把它放在数据旁边。如果逻辑是在服务某项功能或某个人，就把它和功能放在一起。（用 jsonb_agg 返回结果、替代一串查询循环，这是在节省往返通信，而不是做格式化。）

select format('%s: %s by user %s in project %s took %s ms (%s)',
         to_char(created_at at time zone 'America/New_York',
                 'FMDay, FMMonth FMDD YYYY at FMHH12:MI AM'),
         initcap(kind), coalesce(user_id::text, 'system'), project_id,
         to_char((payload ->> 'duration_ms')::int, 'FM999,999'),
         upper(payload ->> 'status')) as line
from events
where id <= 200000;

Sunday, October 5 2025 at 8:00 PM: Build by user 46936 in project 6705
took 3,454 ms (FAILED)

select exec_ms(:'raw_rows') as raw_ms \gset
select exec_ms(:'formatted_rows') as fmt_ms \gset

select lab.prove(
  'formatting 200,000 rows into sentences in SQL costs the server over 2.5 times the time of returning the fields',
  :fmt_ms > 2.5 * :raw_ms);

select lab.prove(
  'and the formatted text is over 1.5 times the bytes of the raw fields',
  :fmt_bytes > 1.5 * :raw_bytes);

SQL 函数会内联；PL/pgSQL 函数不会

下面是开头提到的两个函数，它们使用 (account_id, created_at) 上的索引：

create function account_events(acct bigint) returns setof events
language sql stable
as $$ select * from events where account_id = acct $$;

create function account_events_pl(acct bigint) returns setof events
language plpgsql stable
as $$ begin return query select * from events where account_id = acct; end $$;

调用者要查一天的数据：select * from account_events(42) where created_at > '2026-10-05'；两个孪生函数各自的计划如下：

 Index Scan using events_account_id_created_at_idx on events (actual rows=4.00 ...)
   Buffers: shared hit=7

 Function Scan on account_events_pl  ... rows=333 ... (actual rows=4.00 loops=1)
   Rows Removed by Filter: 2080
   Buffers: shared hit=2040

SQL 版本的计划中没有函数：规划器把查询粘贴进来，合并调用者的过滤条件，然后使用索引。Function Scan 会先把函数运行完，再进行过滤；规划器只能猜测函数内部做了什么（函数会隐藏你的统计信息）。对于最大的租户，它必须读取该租户曾经拥有的每个事件，才能展示其中一天的数据。

select lab.prove(
  'the SQL function is inlined: an index scan on (account_id, created_at), under 20 pages',
  lab.nodes($$ select * from account_events(42) where created_at > '2026-10-05' $$)
    = array['Index Scan']
  and lab.buffers($$ select * from account_events(42)
                    where created_at > '2026-10-05' $$) < 20);

select lab.prove(
  'the PL/pgSQL twin is a Function Scan that reads the whole account, over 1,000 pages',
  lab.nodes($$ select * from account_events_pl(42) where created_at > '2026-10-05' $$)
    = array['Function Scan']
  and lab.buffers($$ select * from account_events_pl(42)
                    where created_at > '2026-10-05' $$) > 1000);

如果函数只包含一个查询，就用 SQL 编写。 需要变量、循环或异常处理时，再使用 PL/pgSQL，并且要预期它表现得像一次调用，而不是一个视图。

STRICT、SECURITY DEFINER 或 SET 子句会阻止内联

只有在以下条件同时满足时，返回集合的 SQL 函数才会内联：函数体是单个 SELECT，易变性是 STABLE 或 IMMUTABLE（默认值 VOLATILE 不行），不是 STRICT，不是 SECURITY DEFINER，并且没有 SET 子句。我们逐项尝试了这些会阻止内联的设置；每种情况都得到一个 Function Scan。因此，带有固定 search_path 的定义者函数（本来就应该如此）是一个黑盒：把这类函数留给范围狭窄的操作。

PL/pgSQL 会按会话缓存计划

PL/pgSQL 会在每个会话中准备每条语句一次，并保留该计划，因此也会继承 预备语句的切换规则：先使用五次自定义计划；如果通用计划的代价看起来不比这些自定义计划的平均代价高，就切换到通用计划。同一个陷阱现在藏进了函数内部。把规划器章中的 latest 查询封装到 latest_ids(k text) 中，针对常见的 kind 调用五次，第六次调用就会锁定通用计划：对于罕见的 incident kind，它要读取 34,536 页，而不是 23 页。

函数本身没有变化；变化的只有会话历史，这就是这个问题如此难以复现的原因。把修复固定到函数上：alter function latest_ids(text) set plan_cache_mode = force_custom_plan，这样每次调用都会根据实际值规划。

create function latest_ids(k text) returns setof bigint
language plpgsql stable
as $$
begin
  return query
    select id from events where kind = k order by created_at desc limit 10;
end
$$;

select lab.prove(
  'after five calls the function switched to a generic plan: the rare kind now reads over 30,000 pages',
  lab.buffers($$ select * from latest_ids('incident') $$) > 30000);

alter function latest_ids(text) set plan_cache_mode = force_custom_plan;

select lab.prove(
  'with SET plan_cache_mode = force_custom_plan on the function, the rare kind is cheap on every call',
  lab.buffers($$ select * from latest_ids('incident') $$) < 50);

易变性是给规划器的承诺

每个函数都属于 IMMUTABLE（参数相同，结果永远相同）、STABLE（在同一条语句内结果相同，不执行写入）或 VOLATILE（不作任何承诺，也是默认值）之一，规划器会照单全收。下面是同一个纯函数：它计算向前 n 天的截止时间，依次使用每种标记，并用于过滤 projects：

   ->  Index Only Scan using projects_created_at_idx on projects  ...
         Index Cond: (created_at > '2026-09-29 00:00:00+00'::timestamp ...)

   ->  Index Only Scan using projects_created_at_idx on projects  ...
         Index Cond: (created_at > days_ago_s(7))

   ->  Seq Scan on projects  ...
         Filter: (created_at > days_ago_v(7))

第一种 IMMUTABLE 在规划时被折叠成了一个字面值（常量会被折叠）。STABLE 保留为一次调用，却驱动了同一个索引。VOLATILE 可能随行变化，所以顺序扫描会调用它 20,000 次。要为每个函数选择事实允许的最严格标记。

虚假的 IMMUTABLE 会返回过时结果

永远不要给函数贴比事实更严格的标签。一个读取表中设置的查找函数被标记为 IMMUTABLE，它供一个统计过期项目的 PL/pgSQL 函数使用：30 天时结果是 19,271。把设置改成 365 天后，同一会话仍然返回 19,271，因为“常量”30 已经被折叠进缓存计划；新会话返回 10,913。只要连接池中的这些连接还在，这个错误就会持续存在。IMMUTABLE 函数绝不能读取表、设置、时区或时钟。

create function days_ago_i(n int) returns timestamptz
language plpgsql immutable
as $$ begin return timestamptz '2026-10-06' - n * interval '1 day'; end $$;
-- days_ago_s: the same body, labeled stable
-- days_ago_v: the same body, labeled volatile

select lab.prove(
  'IMMUTABLE is folded to a constant at plan time: the index condition holds a literal timestamp',
  lab.plan_text($$ select count(*) from projects where created_at > days_ago_i(7) $$)
    ~ 'Index Cond: \(created_at > ''\d{4}-\d\d-\d\d [^'']+''::timestamp with time zone\)');

select lab.prove(
  'VOLATILE cannot be an index condition: a sequential scan with a filter',
  lab.plan_text($$ select count(*) from projects where created_at > days_ago_v(7) $$)
    like '%Seq Scan on projects%Filter: (created_at > days_ago_v(7))%');

select lab.prove(
  'calls per query: IMMUTABLE once, STABLE at most twice, VOLATILE once per row (20,000)',
  (select array_agg(calls order by funcname) from pg_stat_xact_user_functions
   where funcname in ('days_ago_i', 'days_ago_s', 'days_ago_v'))
  in (array[1, 1, 20000]::bigint[], array[1, 2, 20000]::bigint[]));

create function setting(k text) returns int
language sql immutable  -- a lie: it reads a table
as $$ select value from settings where key = k $$;

select lab.prove(
  'a mislabeled IMMUTABLE function goes stale: the session keeps the old answer, a new session sees the new one',
  :same_session = :before_change and :other_session < :before_change);

alter function setting(text) stable;
select lab.prove(
  'relabeling it STABLE invalidates the cached plan and the answer is right again',
  expired_projects() = :other_session);

并行安全需要显式选择

用户函数默认是 PARALLEL UNSAFE，因此，在 where 子句中只要有一个未标注的辅助函数，两百万行的扫描就会在一个进程中运行（一个未标注的函数会让查询保持串行）。把纯辅助函数标记为 PARALLEL SAFE。不要撒谎：执行写入的函数必须保持 UNSAFE（标成安全后，我们的函数因 cannot execute INSERT during a parallel operation 而失败）；接触临时表或后端本地状态的函数则是 RESTRICTED。

规划器还会相信另外三个标记

易变性不是规划器会照单全收的唯一标记。只有超级用户才能设置的 LEAKPROOF，允许函数先于 行级安全过滤器运行，这样策略就可以使用索引（不防泄漏的函数会在策略之后运行）。较高的 COST 会让规划器先运行代价更低的条件。ROWS 会替换返回集合的函数默认 1,000 行估算（FROM 中的函数会被估算为 1,000 行）；更好的做法是把它写成可内联的 SQL。

触发器逐行运行，除非你另有指定

触发器是数据库在表发生变化时调用的函数，而你拥有的每个外键本身已经带着几个触发器。BEFORE ... FOR EACH ROW 会在行处理过程中调整行；AFTER 会对已经发生的变化作出反应。FOR EACH STATEMENT 触发器只运行一次，并通过过渡表读取所有发生变化的行。WHEN 子句让触发器变得再便宜不过：不触发的触发器。

行触发器每行大约花费一微秒

我们在 events 的一份不记录 WAL 的表副本上更新了 100,000 行，取五次中的最佳结果：

每行约一微秒：一次只处理一行时只是噪声；但对每晚处理 5,000 万行的任务来说，会耗掉将近一分钟的 CPU 时间。BEFORE 触发器还会为每行增加一条 WAL 记录，用于记录在触发器看到该行之前取得的锁。在语句中设置该列，可以完成同样的工作，却不必承担触发器的代价。

select lab.prove(
  'a one-line BEFORE row trigger makes a 100,000-row update measurably slower (over 15%)',
  :t_before > :t_none * 1.15);

select lab.prove(
  'a row-level audit trigger makes the same update at least 1.4x slower',
  :t_row > :t_none * 1.4);

select lab.prove(
  'a BEFORE UPDATE row trigger adds one WAL record per row: it locks each row before updating it',
  :recs_before - :recs_none between 19000 and 21000);

select lab.prove(
  'an audit trigger adds one insert per row to the WAL, row-level or statement-level alike',
  :recs_row - :recs_none between 19000 and 21500
  and abs(:recs_row - :recs_stmt) < :recs_row * 0.02);

计数器触发器会让写入者串行化

保持每个项目精确计数的通常做法是（精确计数会读取全部数据）在 events 上设置行触发器，运行 update projects set event_count = event_count + 1。它是正确的，但有两个问题，你在开发环境中看不到。

现在每次插入都会锁住父行。 两个会话向项目 7 插入事件时，过去可以并行进行。现在每个会话都会更新项目 7，而该行会一直锁到提交，因此会话 B 要等待会话 A 的整个事务结束（行锁存在行中，而不在 pg_locks 中）。最繁忙的项目会产生最多竞争。

一次批量插入会把同一行重写数千次，每次更新都要遍历此前的行版本。为一个项目插入 500 个事件大约耗时 16 ms；插入 4,000 个则耗时约 260 ms。行数是八倍，时间却是十六倍。

语句级触发器可以解决批量场景。锁的问题却无从解决。需要时用索引计数，或者把增量追加到一张窄表中，这种追加操作不会阻塞，再稍后汇总这些增量（把热点计数器移到窄表）。

create function bump_event_counts() returns trigger
language plpgsql
as $$
begin
  update projects p
  set event_count = p.event_count + n.added
  from (select project_id, count(*) as added from new_rows group by project_id) n
  where p.id = n.project_id;
  return null;
end
$$;

create trigger events_count after insert on events
  referencing new table as new_rows
  for each statement execute function bump_event_counts();

 wait_event_type |  wait_event   | blocked_by
-----------------+---------------+------------
 Lock            | transactionid | {3798}

select lab.prove(
  'without a counter trigger, a second insert for the same project does not wait',
  dblink_is_busy('b') = 0);

-- after create trigger events_count ... for each row execute function bump_event_count()
select lab.prove(
  'with the counter trigger, the second insert waits on the first transaction (a transactionid lock)',
  dblink_is_busy('b') = 1
  and (select wait_event_type = 'Lock' and wait_event = 'transactionid'
       from pg_stat_activity where pid = :b_pid)
  and cardinality(pg_blocking_pids(:b_pid)) = 1);

select lab.prove(
  'a 1,000-row insert for one project makes 1,000 updates of one counter row, and projects grows',
  :upd = 1000 and :pages_after > :pages_before);

select lab.prove(
  'with a row-level counter trigger, eight times the rows for one project takes over 12 times as long',
  :t_4000 > :t_500 * 12);

select lab.prove(
  'a statement-level trigger over the transition table updates the counter once, HOT',
  :upd2 = 1 and :hot2 = 1 and (select event_count from projects where id = 9) = 1000);

COPY 会触发触发器；复制角色会跳过触发器

COPY FROM 和 INSERT 一样会触发触发器，因此加载一百万行要为触发器付出一百万次代价。session_replication_role = replica 会跳过触发器（设置它需要超级用户权限，或者在 PostgreSQL 15+ 中被授予对它的 SET 权限，而托管服务往往不会授予这种权限）；逻辑复制会设置这个值，这样触发器就不会执行两遍。

set session_replication_role = replica;
insert into events (account_id, project_id, user_id, kind) values (9, 10, 8, 'build');
insert into events (account_id, project_id, user_id, kind) values (9, 999999, 8, 'orphan');
reset session_replication_role;

select lab.prove(
  'with session_replication_role = replica, ordinary triggers do not fire',
  (select event_count from projects where id = 10) = 2);

select lab.prove(
  'and neither do foreign keys: an event for a project that does not exist got in',
  exists (select 1 from events where project_id = 999999));

触发器必须证明自己值得存在

本章中的每个触发器都做了它声称要做的事。但它是否应该存在，是另一个问题。我们的答案是：触发器应该比你想象的少。

它们带来的好处。

• 原子性。 触发器的工作会和更改一起提交或回滚。审计行不会只写了一半。
• 不怕调用者漏做。 每次写入都会触发它：应用、第二个服务、迁移、凌晨两点有人从 psql 运行的修复命令，都一样。
• 覆盖每个写入者的历史（历史触发器捕获每个写入者，每行插入一次）。

它们隐藏的东西。

• 行为。 一条报告插入一行的 insert，可能还增加了计数器、写入了审计行、排队了一个任务。调用位置没有任何说明，搜索应用代码也找不到这些行为。
• 代价与串行化。 每次回填都要支付逐行代价；更新父行的触发器会把并行插入变成排队。
• 顺序。 同一事件上的触发器按名称顺序运行，而不是按创建顺序。我们依次创建了 z_normalize、a_validate 和 m_stamp，但它们的运行顺序是 a、m、z。重命名一个触发器，就会重新排列业务逻辑。
• 部署与调试。 它们以迁移的形式发布，一次改变所有会话的行为；出错时，INSERT 报告的是一行 PL/pgSQL 上下文，而不是堆栈跟踪。

我们的规则。 当工作必须与更改保持原子性，并且必须对每个写入者发生时，使用触发器：审计和历史行、你不能指望调用者设置的派生列、以及任何约束无法表达的跨行不变量。很可能会有批量写入时，使用语句级触发器。

其他事情都在提交之后交给应用处理：通知、邮件、webhook、缓存失效、搜索索引、热点行上的计数器、业务工作流。如果副作用不能丢失，就在事务中写一行 outbox。触发器是隐藏的写入，每行都要永远为它付费，因此每个触发器都应该经得起设计评审。

create trigger z_normalize before insert on trig_order
  for each row execute function append_note('z');
create trigger a_validate before insert on trig_order
  for each row execute function append_note('a');
create trigger m_stamp before insert on trig_order
  for each row execute function append_note('m');
insert into trig_order (id) values (1);

select lab.prove(
  'triggers on the same event fire in name order, not creation order',
  (select note from trig_order where id = 1) = 'amz');

过程可以提交事务

过程（PostgreSQL 11+）通过 CALL 调用，可以边执行边提交；函数不能这样做。因此，对于回填和清理任务，过程是更合适的形式：如果只有一个长事务，它会持有锁、阻止 vacuum 清理，并在任何错误发生时丢掉全部结果。我们的回填分十个已提交批次更新了 100,000 行。要在自动提交模式下运行过程：如果处于 BEGIN 内部（许多驱动程序会替你添加它），第一次 COMMIT 会因 invalid transaction termination 而失败。

create procedure backfill_kind_upper(batch int)
language plpgsql
as $$
declare
  last_id bigint := 0;
  max_id  bigint;
begin
  select max(id) into max_id from backfill_demo;
  while last_id < max_id loop
    update backfill_demo set kind_upper = upper(kind)
    where id > last_id and id <= last_id + batch;
    last_id := last_id + batch;
    commit;
  end loop;
end
$$;

call backfill_kind_upper(10000);

call backfill_kind_upper(10000);

select lab.prove(
  'the procedure filled every row, in ten separately committed transactions',
  (select count(*) filter (where kind_upper is null) = 0
          and count(distinct xmin::text) = 10
   from backfill_demo));

EXCEPTION 块就是子事务

带有 EXCEPTION 子句的 PL/pgSQL 块每次进入时都会设置一个保存点，形成一个子事务，并付出 子事务并非没有代价所描述的成本。进入这样的块 100,000 次大约耗时 18 ms，而普通块只需 3 ms，即使没有抛出错误也是如此。循环插入 1,000 行，每次插入都放在异常保护块中，就会使用 1,000 个子事务 ID，从第 65 行开始就超过了 64 个条目的缓存。使用 on conflict do nothing，让错误根本不会发生；或者用一层异常处理包住整个循环。

create function insert_guarded(n int) returns void
language plpgsql
as $$
begin
  for i in 1..n loop
    begin
      insert into guarded_insert values (i);
    exception when unique_violation then
      null;
    end;
  end loop;
end
$$;

select lab.prove(
  'entering a block with an EXCEPTION clause costs at least 3x a plain block',
  :t_guarded > :t_plain * 3);

select insert_guarded(1000);
select lab.prove(
  'with one per row, each insert gets its own subtransaction ID: 1,000 of them',
  (select count(distinct xmin::text) from guarded_insert) = 1000);

函数不可见，除非你开启跟踪

本章中的每个问题，一旦能看见它就更容易修复；而默认统计信息会让函数不可见。track_functions 在 pg_stat_user_functions 中统计每个函数的调用次数和耗时。pg_stat_statements.track = all 会为函数内的语句建立独立条目，而不是把它们隐藏在一条缓慢的 INSERT 中（从 pg_stat_statements 开始）。auto_explain.log_nested_statements 会记录函数内部的计划，而 EXPLAIN 本身不会显示这些计划。

load 'auto_explain';
set auto_explain.log_min_duration = 10;
set auto_explain.log_nested_statements = on;
set auto_explain.log_level = notice;  -- send plans to this session, not the log
select count(*) from latest_ids('incident');
select count(*) from latest_ids('build');  -- five times
select count(*) from latest_ids('incident');

NOTICE:  duration: 242.283 ms  plan:
Query Text: select id from events where kind = k order by created_at desc limit 10
Limit  (cost=0.43..2.73 rows=10 width=16)
  ->  Index Scan Backward using events_created_at_idx on events  ...
        Filter: (kind = $1)

本章要点

• 主库是唯一不能横向扩展的 CPU。让它承担能缩小数据、保持规则原子性或节省往返通信的工作。
• 如果函数只包含一个查询，就用 SQL 编写，并标记为 STABLE 或 IMMUTABLE。
• PL/pgSQL 五次调用后可能切换到通用计划；force_custom_plan 可以解决参数倾斜带来的计划问题。
• 在事实允许的范围内尽可能严格地标注易变性，绝不超出这个范围。
• 只有当工作必须具有原子性并且必须对每个写入者发生时，才使用触发器。
• 计数器触发器会把所有写入者串行化到父行上。
• session_replication_role = replica 也会禁用外键。
• 不要在逐行循环中使用 EXCEPTION 块。

延伸阅读

• User-Defined Functions，PostgreSQL 文档。SQL 函数、过程，以及 SUPPORT、ROWS 和 COST 钩子。
• Function Volatility Categories，PostgreSQL 文档。每种标记背后的承诺，包括过时计划的风险。
• Inlining of SQL functions，PostgreSQL wiki。内联的每个条件。
• PL/pgSQL: Plan Caching，PostgreSQL 文档。PL/pgSQL 如何准备语句，以及何时切换到通用计划。
• Transaction Management in PL/pgSQL，PostgreSQL 文档。过程内部何时可以使用 COMMIT。
• Triggers 和 CREATE TRIGGER，PostgreSQL 文档。触发顺序、过渡表和约束触发器。
• auto_explain，PostgreSQL 文档。记录嵌套语句的计划。