7 让更新保持 HOT

复制一份带有六个索引的 users，更新其中 5,000 行，并递增一个没有任何索引覆盖的计数器。Postgres 写入 438 KB 的 WAL。再递增同一个计数器，同时更新 updated_at（它有索引），就像大多数 ORM 每次保存时做的那样；同样的 5,000 行会写入 2.96 MB，几乎是七倍。逐行更新时差距还会扩大：一次更新分别是 127 字节和 15,327 字节，因为其中两张被触及的索引页必须整页记录。区别就在于这次更新是不是 HOT。

每次更新都会把一份完整的新行版本写到新地址（更新会写入完整的新行），而索引指向地址。如果不做任何特殊处理，每次更新都会在每个索引中写入一个新条目。HOT 是 heap-only tuple（堆内元组）的缩写，是一条逃生通道：新版本留在同一页，索引完全不用知道它的存在。

你从来不用打开 HOT。它一直处于启用状态，而模式决定它能多常发挥作用：你给哪些列建索引、页填得有多满，以及事务保持打开的时间。把这三点做好，几乎每次更新都能跳过索引。

冷更新要为每个索引付费

把不是 HOT 的更新称为冷更新。它会写入一个新元组（完整的一行），在每个索引中新增一个条目，即使那些索引覆盖的列没有变化，还要为这一切写入 WAL。HOT 更新只写元组及其 WAL。就这些。

下面是完整的开场实验。我们复制了一份 users，让每一页留下 10% 的空闲空间（下面会讲 fillfactor 90），然后用两种方式更新其中的 5,000 行；表上分别有一个、三个和六个索引，并避开任何检查点。HOT 更新递增 login_count，没有索引覆盖它。冷更新还更新了有索引的 updated_at（只有一个索引时，该索引就是建在 updated_at 上的）：

先看 HOT 那几列：无论索引数量是多少，都是 438 KB 和 11,370 个缓冲区。HOT 更新看不见你的索引。每增加一个索引，冷更新一侧就大约多出一次 HOT 更新量的 WAL；索引数为六时，冷批次触及的缓冲区是 HOT 的 7.7 倍。

账单还会继续。HOT 更新之后的清理，没有索引条目需要移除；冷更新之后的清理则读取了全部六个索引的每一页。每个副本也都要重放同一份 WAL（Uber 遇到了这种不对称，而且双方都有道理）。

select lab.prove(
  'second batch, cold vs HOT: about 6.9x with six indexes',
  m('c6 batch 2', 'bytes') / m('h6 batch 2', 'bytes') between 6.2 and 7.6);
select lab.prove(
  'the HOT batch touches the same buffers in every configuration; cold with six indexes about 7.7 times as many',
  m('h1 batch 2', 'buffers') = m('h6 batch 2', 'buffers')
  and m('c6 batch 2', 'buffers') / m('h6 batch 2', 'buffers') between 7.0 and 8.3);
select lab.prove(
  'after cold updates, vacuum reads every page of all six indexes',
  (select idx_blocks from vacuum_cost where tbl = 'c6')
    >= pg_indexes_size('c6') / 8192 - 5);

 update |   record_type   |         relname          | record_length | fpi_length
--------+-----------------+--------------------------+---------------+------------
 1 HOT  | HOT_UPDATE      | users_hot                |         15694 |      15616
 2 cold | UPDATE          | users_hot                |          7497 |       7424
 2 cold | INSERT_LEAF     | users_hot_updated_at_idx |          7453 |       7400
 ...      (five more index records, each about 7,450 bytes)
 3 HOT  | PRUNE_ON_ACCESS | users_hot                |            56 |          0
 3 HOT  | HOT_UPDATE      | users_hot                |            71 |          0
 4 cold | PRUNE_ON_ACCESS | users_hot                |            58 |          0
 4 cold | UPDATE          | users_hot                |            81 |          0
 4 cold | INSERT_LEAF     | users_hot_updated_at_idx |          7453 |       7400
 4 cold | INSERT_LEAF     | users_hot_pkey           |            64 |          0
 ...

select lab.prove(
  'right after a checkpoint, the HOT update is one record carrying two full-page images (heap and visibility map)',
  m('row 1 hot', 'records') = 1 and m('row 1 hot', 'fpi') = 2);
select lab.prove(
  'every one of those seven records carried an 8 KB page image: about 51 KB',
  m('row 2 cold', 'fpi') = 7 and m('row 2 cold', 'bytes') between 50000 and 55000);
select lab.prove(
  'a moment later a HOT update costs about 127 bytes: the update plus a small prune record, no images',
  m('row 3 hot', 'records') = 2 and m('row 3 hot', 'fpi') = 0
  and m('row 3 hot', 'bytes') between 100 and 160);
select lab.prove(
  'cold batches write one more record per row per index: 10,000, about 20,250 and about 35,250',
  m('c1 batch 1', 'records') = 10000
  and m('c3 batch 1', 'records') between 20000 and 20500
  and m('c6 batch 1', 'records') between 35000 and 35500);

HOT 需要一次未被索引覆盖的变化，以及页上的空间

更新因为两个原因之一而无法成为 HOT，修复方式取决于具体是哪一个。我们先给它们命名，后面整章都会使用这些名称：

• 更新没有改变任何索引列时，我们称它为 HOT-safe（对 HOT 而言是安全的）。（从 PostgreSQL 16 起，只被BRIN索引覆盖的列不计入。）
• 更新同时满足 HOT-safe，且新版本能和旧版本放在同一页上时，它才是 HOT。

索引提到的每一列都算作已索引

“已索引”指的是出现在任何索引定义中的任何位置，而不只是键列。我们给一份 projects 副本建立了五种索引：

create index on projects_hot (account_id) where archived_at is null;  -- partial
create index on projects_hot (account_id) include (name);             -- covering
create index on projects_hot (lower(slug));                           -- expression
create index on projects_hot using brin (created_at);                 -- summarizing
create index on projects_hot (budget);

然后一次只运行一项更新，并观察 n_tup_hot_upd 在 pg_stat_user_tables 中的变化：

把这些“否”当作警告。部分索引的谓词也算，所以在部分索引中使用软删除列，会让每次归档和恢复都变成冷更新。INCLUDE 列也算：覆盖索引会让它所携带的列失去 HOT。表达式索引算的是列本身，不是结果。BRIN 指向页范围而不是行，因此不需要新的指针。

发生变化意味着字节不同

“变化”不是 SET 列表，也不是 SQL 相等性：Postgres 会比较每个索引列的新旧字节。因此 set name = name 满足 HOT-safe，这对每次都把所有列写回去的 ORM 是个好消息。而 1.0 变成 1.00 虽然数值相等，却是冷更新，因为字节不同。

HOT 版本永远不会离开它所在的页

第二个条件是物理条件。HOT 版本永远不会离开它所在的页，所以落到该页的查找就能在那里找到它。如果新版本放不下，它就会去别的地方，并在每个索引中取得一个条目。你可以用下面要讲的 fillfactor 控制这部分空间。

select lab.prove('an invalid, never-finished index still makes updates of its column cold',
  m('invalid indexed', 'hot') = 0 and m('invalid indexed', 'newpage') = 0);
select lab.prove('(control) updating an unindexed column of the same table is HOT',
  m('invalid other', 'hot') = 1);

select lab.prove('INCLUDE columns count: renaming is cold',
  m('include', 'updated') = 1 and m('include', 'hot') = 0);
select lab.prove('a column covered only by BRIN stays HOT (PostgreSQL 16+)',
  m('brin', 'hot') = 1);
select lab.prove('numeric 1.0 to 1.00 is cold: equal as numbers, different bytes',
  m('scale', 'hot') = 0
  and 1.0::numeric = 1.00::numeric
  and numeric_send(1.0) <> numeric_send(1.00));
select lab.prove('set x = x on indexed columns stays HOT: the bytes did not change',
  m('same', 'hot') = 1);

HOT 查找会沿着版本链走，不离开所在页

HOT 更新就像贴在旧公寓门上的转交便签。索引保留着旧地址；旧版本写着“搬到了同一栋楼的槽位 3”，查找顺着这张便签走，不必离开这一页。通过 t_ctid 字段连接起来的各个版本，组成一条 HOT 链。lp 编号就是行指针，即页顶端编号的槽位（行指针让元组移动而不改变地址）：

 1  INSERT              index ──► lp1 [v1]

 2  HOT UPDATE x2       index ──► lp1 [v1] ─► lp2 [v2] ─► lp3 [v3]
                                     t_ctid       t_ctid

 3  prune               index ──► lp1 REDIRECT ──────────► lp3 [v3]
    (v1, v2 dead              lp2 UNUSED (free, reusable)
     to everyone)

 4  DELETE, prune       index ──► lp1 DEAD     lp3 UNUSED

 5  VACUUM              index entry removed; lp1 UNUSED

 cold UPDATE at any step: new version on another page, plus a
 new entry in EVERY index; old entries wait for vacuum.

• **索引永远不会移动。**查找落到 lp1，然后沿着版本链走到快照可见的版本：无论链有多长，都只需一张索引页和一张堆页。
• **页内剪枝会把版本链变成重定向。**一旦旧版本对每个快照都已死亡，下一个访问者就会剪枝（只有没有快照能看到的版本才是死版本），让 lp1 成为指向最新版本的重定向，不触碰索引。这就是 HOT 价值的一半：空间会在正常流量中回来。
• **只有清理才能完成工作。**整条链都死亡后，lp1 仍会作为死存根留下，直到清理移除索引条目。

 lp  | t_xmin  | t_xmax  | t_ctid  |                 raw_flags
-----+---------+---------+---------+---------------------------------------------
   1 | 1534621 | 1534622 | (0,129) | {...,HEAP_XMAX_COMMITTED,HEAP_HOT_UPDATED}
 129 | 1534622 | 1534623 | (0,130) | {...,HEAP_HOT_UPDATED,HEAP_ONLY_TUPLE}
 130 | 1534623 | 1534624 | (0,131) | {...,HEAP_HOT_UPDATED,HEAP_ONLY_TUPLE}
 131 | 1534624 |       0 | (0,131) | {...,HEAP_ONLY_TUPLE}

select lab.prove('four versions of row 1, linked by t_ctid: slot 1 to 129 to 130 to 131',
  (select array_agg(t_ctid::text order by lp) from page0 where lp in (1, 129, 130, 131))
  = array['(0,129)', '(0,130)', '(0,131)', '(0,131)']);
select lab.prove('an index scan follows the chain in two buffers: one index page, one heap page',
  lab.buffers($$ select * from counters where id = 1 $$) = 2);

-- session A commits; the next read prunes
select lab.prove('a read query wrote WAL: one small prune record',
  m('prune read', 'records') = 1 and m('prune read', 'bytes') < 100);
select lab.prove('slot 1 is now a redirect (lp_flags 2) to slot 131',
  (select lp_flags = 2 and lp_off = 131 from page0 where lp = 1));

页接近填满时才进行页内剪枝

同时满足两个条件时，任何触及这张页的查询都会进行剪枝，包括 select。页上带有由更新或删除设置的提示，表示页中可能有死版本。并且页上的空闲空间小于其 fillfactor 预留空间和页大小 10%（819 字节）两者中的较大值。空间充裕的页不会在访问时剪枝；清理最终会处理它。

剪枝会记录 WAL，所以读取会写入。只读负载产生 WAL 不必惊讶（启用校验和后，连读取也会写 WAL讲的是另一个原因）。

select lab.prove('a roomy page is not pruned on access: no WAL, the old versions stay',
  m('roomy read', 'records') = 0
  and (select count(*) from heap_page_items(get_raw_page('roomy', 0)) where lp_flags = 1) = 5);

给页留下空间，否则每次更新都会移动

刚加载的表是挤满的。填充因子是每页在继续使用下一页之前允许插入填满的百分比；剩余空间留给更新。它默认是 100，因此刚加载的页没有多余空间，第一次更新无论是否 HOT-safe，都会变成冷更新。你可以按表设置它：

alter table users set (fillfactor = 90);

我们制作了三份 users 副本，都带有 login_count 列，各有两个索引，分别将 fillfactor 设为 100、90 和 70；然后从四个 pgbench 客户端随机更新用户，合计执行 200,000 次单行更新（pgbench 是随 Postgres 提供的负载生成器），并保持自动清理开启：

填充因子为 100 时，有 15,542 次更新变成冷更新；挤满的表最终比填充因子为 90 的表还大，索引也随之增长。它最终还是付出了预留空间的代价，只不过使用了最糟糕的货币。批处理更严苛：在填充因子为 100 的表上，一条更新 10% 行的 update 中 10,000 次更新没有一次是 HOT，写入的 WAL 是同一语句在 90 时的五倍。

call random_updates('users_ff100', 50000);
call random_updates('users_ff90', 50000);
select from pg_stat_force_next_flush();

select lab.prove('fillfactor 90: every one of 50,000 single-row updates was HOT, none moved',
  (select n_tup_upd = 50000 and n_tup_hot_upd = 50000 and n_tup_newpage_upd = 0
   from pg_stat_user_tables where relname = 'users_ff90'));
select lab.prove('fillfactor 100: thousands of HOT-safe updates moved to new pages and went cold',
  (select n_tup_newpage_upd between 1000 and 5000
       and n_tup_hot_upd + n_tup_newpage_upd = n_tup_upd
   from pg_stat_user_tables where relname = 'users_ff100'));

select lab.prove('the packed table wrote about five times the WAL (3.5 MB vs 703 KB)',
  m('bulk_ff100 0', 'bytes') / m('bulk_ff90 0', 'bytes') between 4.5 and 5.6);
select lab.prove('and touched almost five times the buffers (103,024 vs 22,532)',
  m('bulk_ff100 0', 'buffers') / m('bulk_ff90 0', 'buffers') between 4.2 and 5.0);
select lab.prove('and grew to the size of the fillfactor 90 table, 1,266 pages',
  abs(pg_relation_size('bulk_ff100') / 8192 - pg_relation_size('bulk_ff90') / 8192) <= 5);

预留空间要先付出代价，之后才会回本

第一天这些预留空间是空的：填充因子为 90 让表大了 11%，为 70 让表大了 42%，而膨胀估算器会把这些预留空间也算作膨胀。像 events 这样的只追加表保持 100。更新很多的表从 90 开始；如果行有几百字节，或者 n_tup_newpage_upd 一直上升，可以降到 70 或 80。

之后再设置只会作用于新页。已有页会保持挤满，直到发生重写（不加锁重建膨胀的表）；对于一张正在使用的大表，跳过重写，让新页采用这个设置。

忙碌的表 HOT 比例应高于 90%

Postgres 会替你统计这些数据。pg_stat_user_tables 有 n_tup_upd（所有更新）、n_tup_hot_upd（HOT 更新）和 n_tup_newpage_upd（PostgreSQL 16+：新版本去了另一页，因此这是冷更新）。留在原页的冷更新说明页上有空间，所以改变了索引列；移动了的冷更新则说明没有空间：

select relname,
       n_tup_upd,
       n_tup_hot_upd                                 as hot,
       n_tup_newpage_upd                             as no_room,
       n_tup_upd - n_tup_hot_upd - n_tup_newpage_upd as index_changed,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) as hot_pct
from pg_stat_user_tables
where n_tup_upd > 0
order by n_tup_upd desc
limit 20;

index_changed 高：索引覆盖了更新会改变的列，填充因子帮不上忙。no_room 高：页太满了；降低填充因子，或者查找长事务（下一节）。两者都高：先修索引，因为即使索引列发生了变化，只要发生移动，也会计入 no_room。

按一天的变化来观察，而不是看全生命周期累计值。在有真实更新流量的表上，超过 90% 是健康的；忙碌表上的低比例是最应该先修的事情。

   relname    | n_tup_upd |  hot  | no_room | index_changed | hot_pct
--------------+-----------+-------+---------+---------------+---------
 churn        |    300000 |     0 |  127233 |        172767 |     0.0
 users_ff100  |     50000 | 47623 |    2377 |             0 |    95.2
 projects_hot |         7 |     3 |       0 |             4 |    42.9
...

select lab.prove('projects_hot: four cold updates, all because an indexed column changed',
  (select index_changed = 4 and no_room = 0 and hot = 3 from hot_report where relname = 'projects_hot'));

长事务会关闭 HOT

页内剪枝只能移除对每个快照都已死亡的版本。数据库中任何地方的一个旧快照，都会让比该快照更新的版本无法被剪枝。旧版本堆积，页被填满，原本 HOT-safe 的更新开始移动，并写入索引条目。

我们在一张全新的填充因子为 90 的表上重复了五个批次，同时让另一个会话停留在一个打开的可重复读事务中：

另一个会话从未碰过这张表。它只持有快照几秒钟，HOT 比例就降到了 23%，表和索引却永久增长了三分之一以上。同一个快照也会阻塞清理（清理无法移除旧快照可能需要的内容）。保持事务短小，并设置 idle_in_transaction_session_timeout。

-- session A, over dblink
-- ... five batches of 10,000 updates on held_ff90, then session A commits

select lab.prove('with session A holding a snapshot, the HOT rate fell to about 23 percent',
  (select sum((m ->> 'hot')::int) from measurements where name like 'held_ff90 %')
    between 9000 and 14000);
select lab.prove('the table grew about 39 percent (1,266 to about 1,755 pages)',
  pg_relation_size('held_ff90') / 8192.0
    / (select heap_pages from bulk_before where relname = 'held_ff90') between 1.3 and 1.45);

HOT 是一次性做出的设计决定

本章中的每次冷更新都来自某个索引或某个已填满的页。两者都是模式选择，所以一次修好即可。

不要给 updated_at 建索引

几乎每个 ORM 都会在每次保存时设置 updated_at。给它建索引后，这张表的每次更新都会在每个索引上变成冷更新。在给任何大多数更新都会改变的列（updated_at、last_seen_at、status、计数器、locked_by）建索引前，先说清楚是哪条查询需要它：

• **“自上次同步以来发生了什么变化？”**更适合用只追加的变更日志或 outbox 表来服务。
• updated_at 上的 BRIN 索引是 HOT-safe 的（PostgreSQL 16+），但 HOT 会把行留在原处，因此这些值很快就会偏离物理顺序。先测量。
• 很少运行的管理员查询可以直接扫描。每天十秒钟，总好过让数百万次更新都变成冷更新。

把热点计数器移到窄表

宽行上的计数器每次递增都会复制整行，并为了这 8 个字节把整行拖过缓存。我们把一个 view_count 放到 profiles 表上；这张表包含 416 字节的 bio，共 6,667 页。我们又把同一个计数器放进一张两列的旁表（599 页）。两边的 HOT 比例都保持 100%，但检查点之后立刻递增 10,000 次时，宽表写入 49.3 MB WAL，窄表写入 4.9 MB：页镜像只有十分之一，而且计数器能够留在缓存中。

last_seen_at、login_count、速率限制、心跳都可以这样处理：凡是写入频率远高于所在行的内容都适用。非常频繁的递增要合并成批量更新。也不要在繁忙的表上用触发器维护计数（计数器触发器会让写者串行）。

select lab.prove('right after a checkpoint the side table writes about a tenth of the WAL',
  m('wide 1', 'bytes') / m('narrow 1', 'bytes') between 8 and 12);
select lab.prove('in steady state the gap is small: a same-page update logs only the bytes that changed',
  m('wide 2', 'bytes') / m('narrow 2', 'bytes') < 2);
select lab.prove('a burst of 20,000 updates in one statement costs both tables HOT: about 67 and 54 percent',
  m('wide burst', 'hot') between 12000 and 14400
  and m('narrow burst', 'hot') between 9000 and 13000);

让 ORM 少写一些

无条件更新 updated_at 会产生一个新的行版本；如果 updated_at 有索引，这就是冷更新。一次没有真实变化的保存虽然是 HOT-safe，仍然会写入版本和 WAL，并增加未来的清理工作。使用脏检查；在普通 SQL 中则加上保护条件：

update users
set name = $1
where id = $2
  and name is distinct from $1;

自底向上的索引删除是安全网

从 PostgreSQL 14 开始，如果 B-树的键没有变化，它会在冷更新之后自行清理：分裂叶页之前，删除其中指向死版本的条目。这是一张安全网，不能代替 HOT。每次冷更新仍然写入并记录了这些条目，而键发生变化的索引要一直等到清理才会处理自己的垃圾。

select lab.prove('the updated_at index holds all 400,000 pointers: live and stale',
  heap_pointers('churn_updated_at_idx') = 400000);
select lab.prove('the primary key, whose key never changed, deleted over half its stale entries on the fly',
  heap_pointers('churn_pkey') < 250000);

这就是 Postgres 做出的取舍

Postgres 索引指向物理地址：查找直接，但每次冷更新都会写入每个索引。MySQL 的 InnoDB 做了相反的选择：那里的更新只触及发生变化的列所属的索引，所以给 updated_at 建索引，每次更新只需写一个索引，而在这里可能要写每一个索引（一次更新写一行，或者写每个索引）。HOT 缩小了这道差距，所以代价如此依赖你的模式。几乎总能重新塑造造成问题的表。在责怪数据库之前，先做这个。

本章要点

• 冷更新会在每个索引中写入条目，还要付出 WAL、清理以及每个副本上的同等代价：六个索引时，WAL 是 HOT 更新的 6.9 倍。
• HOT-safe 表示没有索引列发生变化；HOT 还要求页上有空间。要知道自己违反的是哪一半。
• 已索引表示出现在索引的任何位置：键、INCLUDE、表达式或部分谓词（仅被 BRIN 索引覆盖的列例外，PostgreSQL 16+）。
• 不要在没有明确查询需求的情况下给 updated_at 或其他大多数更新都会改变的列建索引。
• 更新很多的表设置 fillfactor = 90（宽行使用更低的值）；只追加表保持 100。
• 用 n_tup_hot_upd 和 n_tup_newpage_upd 对照 n_tup_upd，判断应该修索引还是增加页内空间。
• 一个被持有的快照把我们的 HOT 比例从 100% 降到 23%，并让每个索引都增长。保持事务短小。
• 把不断变化的计数器移到窄表，并且不要写入没有变化的行。

延伸阅读

• src/backend/access/heap/README.HOT，PGDG 源码。设计文档：版本链、重定向、剪枝，以及 CREATE INDEX 如何处理已有版本链。
• Heap-Only Tuples (HOT)，PostgreSQL 文档。两个官方条件，包括汇总索引例外。
• B-tree bottom-up index deletion，PostgreSQL 文档。PostgreSQL 14+ 如何限制冷更新造成的索引膨胀。
• HOT updates in PostgreSQL for better performance，Laurenz Albe，CYBERTEC。简洁的实践者视角，讲解条件和填充因子。
• The Part of PostgreSQL We Hate the Most，Andy Pavlo 和 Bohan Zhang。研究者对追加式版本机制的批评，以及关于更新有多大比例是 HOT 的集群数据。