6 更新就是插入

示例数据库中的 users 表有 9 MB。对它运行一条语句：

update users set name = upper(name);

现在它有 18 MB，索引也从 8 MB 增长到 12 MB。你没有改变任何一行的大小，也没有增加任何行，但表却翻了一倍。运行 vacuum 后，它仍然是 18 MB。

这都不是 bug。这是 Postgres 让许多事务在不让读者等待写者的情况下读写同一份数据的方式。这项技术叫作多版本并发控制（MVCC），它的核心规则只有三个词：更新就是插入。一旦你在页中看到了这一点，膨胀、缓慢的更新、跟不上趟的清理，以及事务 ID 回卷，就都不再神秘了。

读者永远不用等待写者

想象一份汇总每个账户余额的报表，它需要运行十秒。运行到一半时，另一个事务把钱从报表还没读到的账户转移到它已经统计过的账户。如果报表每到一页就读取当时页上的内容，这笔钱就会从总数中消失。

一种解决办法是加锁：报表锁住它读到的内容，写者等待。结果正确，却很难受。Postgres 采取了另一条路：它从不原地覆盖一行。更新会写入这一行的一个新版本，并把旧版本留在原处，标记是谁替换了它。在更新之前开始的读者继续看到旧版本；之后开始的读者看到新版本。谁也不用等待。

代价会晚些时候出现：旧版本会不断堆积，直到再也没有人需要它们，然后由清理把它们收走。那堆东西就是膨胀。

每次写入都会取得一个事务 ID

每一行首部的两个数字，是本书后面膨胀、清理工作和回卷问题的起点。它们背后牵连着后面所有这些问题。

每个改变数据的事务，都会在第一次写入时从服务器级计数器取得一个事务 ID（XID）；只读事务永远不会取得事务 ID。每个行版本的首部都带着两个事务 ID（一行的成本不止它的列）：xmin 是插入它的事务，xmax 是删除、替换或锁住它的事务（没有则为 0）。每一行 events 的 xmin 都是 766，也就是种子数据事务，xmax 都是 0；对任何表运行 select xmin, xmax, * from events 都能看到它们。

（运行本章实验时服务器比较忙，所以下面的 XID 很大，而且会在步骤之间跳跃。你的值会不同。）

更新会写入完整的新行

要观察版本如何产生，我们需要一张小到可以完整读出的页。一项按账户统计使用量的计数器很合适，这正是应用会不断更新的那类行：

create table account_usage (
  account_id    bigint primary key references accounts (id),
  events_today  int not null default 0,
  updated_at    timestamptz not null default now()
);

insert into account_usage (account_id) values (1), (2), (3);

三行位于第 0 页的第 1 至第 3 个槽位，由事务 869572 插入。现在递增账户 1 的计数器。RETURNING 配合 old 和 new（PostgreSQL 18+）可以显示两个版本的地址：

update account_usage
set events_today = events_today + 1
where account_id = 1
returning old.events_today, new.events_today,
          old.ctid as old_ctid, new.ctid as new_ctid;

 events_today | events_today | old_ctid | new_ctid
--------------+--------------+----------+----------
            0 |            1 | (0,1)    | (0,4)

这一行移到了新的槽位。现在这一页为三行保留了四个元组：旧版本仍然逐字节位于槽位 1，被更新事务的 XID 标记，并指向 (0,4)；槽位 4 中则有一份完整的新副本：

整个机制就是这样：更新插入一个新版本，并在旧版本上盖章：“已被 869576 替换，见 (0,4)”。

每次更新都会写入一份完整的新副本，无论实际改变了多少内容。在一行 1 KB 的记录中递增一个 4 字节的计数器，页上也会落下一条新的 1 KB 元组。这就是为什么宽而且频繁更新的行在 Postgres 中代价高昂，也解释了为什么应该像 account_usage 这样，把热点计数器放进单独的窄表。

select lp, t_xmin, t_xmax, t_ctid, raw_flags
from heap_page_items(get_raw_page('account_usage', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2);

 lp | t_xmin | t_xmax | t_ctid |                    raw_flags
----+--------+--------+--------+--------------------------------------------------
  1 | 869572 | 869576 | (0,4)  | {HEAP_XMIN_COMMITTED,HEAP_HOT_UPDATED}
  2 | 869572 |      0 | (0,2)  | {HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}
  3 | 869572 |      0 | (0,3)  | {HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}
  4 | 869576 |      0 | (0,4)  | {HEAP_XMAX_INVALID,HEAP_UPDATED,HEAP_ONLY_TUPLE}

select lab.prove(
  'the old version is still there, stamped with the updater''s xid and pointing at (0,4)',
  (select t_xmax from page0 where lp = 1) = (select t_xmin from page0 where lp = 4)
  and (select t_ctid from page0 where lp = 1) = '(0,4)');

select lab.prove(
  'HEAP_HOT_UPDATED on the old version and HEAP_ONLY_TUPLE on the new: a HOT update',
  'HEAP_HOT_UPDATED' = any((select raw_flags from page0 where lp = 1)::text[])
  and 'HEAP_ONLY_TUPLE' = any((select raw_flags from page0 where lp = 4)::text[]));

select lab.prove(
  'incrementing a 4-byte counter in a 1 KB row writes a whole new 1 KB tuple',
  lp_len > 1000)
from heap_page_items(get_raw_page('wide_counter', (:'wide_ctid'::text::point)[0]::int))
where lp = (:'wide_ctid'::text::point)[1]::int;

select lab.prove(
  'but the WAL record for a same-page update logs only the changed bytes (under 100)',
  bytes < 100) from wide_wal;

只有没有快照能看到的版本才是死版本

再更新账户 1 两次，它的版本链就会依次串起 (0,1)、(0,4)、(0,5)、(0,6)：三行对应六个元组，其中三个已经成了无用的死版本。

任何事务都再也看不到的版本，就是一个死元组。“再也”很重要：一个被替换的版本，只有在所有可能仍然读取它的事务都结束后才算死亡。仍在运行的最老快照（它是一个固定的、记录了哪些事务已经提交的视图，见下文）会设置 xmin 清理界限（xmin horizon）：在它之前死亡的版本可以移除；其余版本必须留下。清理能清掉多少，就由这条界线决定；长事务一节会说明这一点。

死元组有两种清除方式。页内剪枝会在查询读取一张几乎填满的页时顺手发生：成本低，一次处理一页，也不需要处理索引。清理则做完整的工作，包括索引条目（清理不可省）。

运行 vacuum account_usage 后，账户 1 的三个死版本消失了，槽位 1 变成指向槽位 6 的重定向，因为主键索引仍然指向 (0,1)。元组会移动，地址不会。这正是行指针让元组移动而不改变地址所说明的事情。

 lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------
  1 |        1 | 869572 | 869576 | (0,4)
  2 |        1 | 869572 |      0 | (0,2)
  3 |        1 | 869572 |      0 | (0,3)
  4 |        1 | 869576 | 869578 | (0,5)
  5 |        1 | 869578 | 869580 | (0,6)
  6 |        1 | 869580 |      0 | (0,6)

 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_ctid
----+--------+----------+--------+--------+--------+--------
  1 |      6 |        2 |      0 |        |        |
  2 |   8144 |        1 |     48 | 869572 |      0 | (0,2)
  3 |   8096 |        1 |     48 | 869572 |      0 | (0,3)
  4 |      0 |        0 |      0 |        |        |
  5 |      0 |        0 |      0 |        |        |
  6 |   8048 |        1 |     48 | 869580 |      0 | (0,6)

select lab.prove(
  'after vacuum, lp 1 is a redirect (lp_flags 2) to slot 6',
  lp_flags = 2 and lp_off = 6) from page0 where lp = 1;

select lab.prove(
  'lp 4 and 5 are unused (lp_flags 0), free for the next insert or update',
  count(*) = 2 and bool_and(lp_flags = 0)) from page0 where lp in (4, 5);

HOT 更新会跳过索引

索引指向地址，因此一个位于新地址的新版本，通常需要在表的每一个索引中新增条目。我们的账户 1 更新却没有这样做：它们是 HOT（heap-only tuple，堆内元组）更新，留在同一页中，不改动索引。只有同时满足以下条件才行：没有索引列发生变化，并且新版本能放进同一页。

违反第一个条件，更新就会变成普通更新。给 account_usage.updated_at 建索引，然后更新账户 2 的 updated_at；账户 2 的主键索引会新增第二个条目，指向新版本的槽位，而账户 1 在三次 HOT 更新后仍只有一个条目。

页填得太满则会违反第二个条件。我们加载了两份 users 副本，一份使用默认的 100 fillfactor，另一份使用 85（填充因子为更新留下空间），然后分别更新每十行中的一行。在 100 时，10,000 次更新没有一次是 HOT，表从 1,126 页增长到 1,239 页。在 85 时，10,000 次更新全部是 HOT，表一页也没有增长。让更新保持 HOT会继续讲解。

select lab.prove(
  'account 2 now has two primary key entries: the update changed an indexed column, so it was not HOT',
  array_agg(ctid::text order by itemoffset) = '{"(0,2)","(0,4)"}')
from bt_page_items('account_usage_pkey', 1)
where data = '02 00 00 00 00 00 00 00';

select lab.prove(
  'fillfactor 100: 0 of 10,000 updates were HOT, and the table grew from 1,126 to 1,239 pages',
  s.n_tup_upd = 10000 and s.n_tup_hot_upd = 0
  and pg_relation_size('users_ff100') / 8192 between 1200 and 1280)
from pg_stat_user_tables s where relname = 'users_ff100';
select lab.prove(
  'fillfactor 85: all 10,000 updates were HOT, and the table did not grow (1,322 pages)',
  s.n_tup_upd = 10000 and s.n_tup_hot_upd = 10000
  and pg_relation_size('users_ff85') / 8192 = 1322)
from pg_stat_user_tables s where relname = 'users_ff85';

一个快照选择一个版本

当一行在页上有多个版本时，查询如何选出其中一个？它依据的是一个快照：在语句（或事务）开始时拍下的、记录哪些事务已经提交的照片。如果一个版本在照片拍下之前出生，并且在那时还没有死亡，你就能看到它。一个版本链中最多只有一个版本通过这项检查。

我们从两个会话中观察它。会话 A 使用事务 869588 更新账户 3，并让事务保持打开：

-- session A
begin;
update account_usage set events_today = 100 where account_id = 3;
-- (not committed yet)

会话 B 读取同一行：

-- session B
select ctid, xmin, xmax, account_id, events_today
from account_usage where account_id = 3;

 ctid  |  xmin  |  xmax  | account_id | events_today
-------+--------+--------+------------+--------------
 (0,3) | 869572 | 869588 |          3 |            0

B 看到的是旧版本，尽管 A 的 XID 已经出现在它的 xmax 中。B 拍下快照时 A 还没有提交，所以在 B 看来，旧行还没有死亡，新行也还没有出生。B 没有等待，也没有阻塞 A。

A 提交后，B 的下一条语句就会看到 events_today = 100。如果 A 回滚，页上的内容不会改变：它的版本会带着一个已中止的 xmin，对所有人都不可见（原子性：任何事情都不会被撤销）。

select pg_current_snapshot();

                      pg_current_snapshot
----------------------------------------------------------------
 868807:869587:868807,868917,868951,868974,869184,869185,869537

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  3 | 869572 | 869588 | (0,5)
  5 | 869588 |      0 | (0,5)

select lab.prove(
  'session B still sees the old version at (0,3), with A''s xid already in its xmax',
  ctid = '(0,3)' and xmax::text = :'a_xid' and events_today = 0)
from account_usage where account_id = 3;

select lab.prove(
  'pg_xact_status says A is in progress',
  pg_xact_status(:'a_xid'::xid8) = 'in progress');

读取也可能写入

读者如何知道事务 869588 是否提交？它会查询提交日志（pg_xact），每个事务占两个 bit；第一个查到结果的读者，会把答案缓存到元组本身的提示位中。

这意味着读取也可能写入。第一次对刚加载的行执行 select 时，会在它读取的每个元组上设置提示位，把这些页标记为脏页，之后再写回磁盘。批量加载后，运行 vacuum analyze，让这阵写入按你的安排发生，而不是在用户访问时发生（在打开大门前清理并分析）。

create table hint_demo2 (id int, v text);
insert into hint_demo2 select g, md5(g::text) from generate_series(1, 200000) g;

select raw_flags
from heap_page_items(get_raw_page('hint_demo2', 100)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

              raw_flags
--------------------------------------
 {HEAP_HASVARWIDTH,HEAP_XMAX_INVALID}

select lab.prove(
  'a freshly loaded tuple has no HEAP_XMIN_COMMITTED hint yet',
  not ('HEAP_XMIN_COMMITTED' = any(raw_flags)))
from heap_page_items(get_raw_page('hint_demo2', 100)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

select lab.prove(
  'after a plain count(*), the tuple carries HEAP_XMIN_COMMITTED: a read modified the page',
  'HEAP_XMIN_COMMITTED' = any(raw_flags))
from heap_page_items(get_raw_page('hint_demo2', 100)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

删除只给行盖章

删除比更新简单：它只设置 xmax，除此之外什么也不做。删除一行后，它仍以完整大小留在页上，xmax 中带着删除者的 XID。它从所有新快照中消失，却仍然占用空间。删除一百万行不会缩小表；它只会产生一百万个死元组，等待清理将其变成可复用的空间。

truncate 是例外。它什么也不盖章：它换入一个新的空文件，所以快照早于它的事务看到的也是空表，而不是原来的内容（TRUNCATE）。不要截断一张仍可能被长时间运行的读者读取的表，例如报表或逻辑备份正在读取的表。

begin;
select pg_current_xact_id_if_assigned();   -- nothing yet
select count(*) from accounts;
-- still nothing: reads don't need an xid
select pg_current_xact_id_if_assigned();
insert into account_usage (account_id) values (4);
select pg_current_xact_id_if_assigned();   -- 869595, assigned by the first write
commit;

delete from account_usage where account_id = 4 returning ctid, xmin, xmax;

 ctid  |  xmin  |  xmax
-------+--------+--------
 (0,7) | 869595 | 869597

select lab.prove('still no xid after a read: reads don''t need one',
                 pg_current_xact_id_if_assigned() is null);

select lab.prove(
  'the deleted row stays on the page at full size, with an xmax and HEAP_KEYS_UPDATED',
  lp_flags = 1 and lp_len > 0 and t_xmax::text <> '0'
  and t_ctid = :'deleted_ctid'::tid and 'HEAP_KEYS_UPDATED' = any(raw_flags))
from page0 where lp = (:'deleted_ctid'::tid::text::point)[1]::int;

 imports_seen
--------------
            0

 deletes_seen
--------------
          500

select lab.prove(
  'an older REPEATABLE READ snapshot sees the truncated table as empty',
  (select n from dblink('session_a', 'select count(*) from imports') as t(n bigint)) = 0);
select lab.prove(
  'but still sees all 500 deleted rows',
  (select n from dblink('session_a', 'select count(*) from deletes_demo') as t(n bigint)) = 500);

清理释放表内空间，而不是磁盘空间

现在来测量本章开头的实验。pgstattuple 扩展会读取表的每一页，并报告空间的使用情况：

select table_len, tuple_count, dead_tuple_count, free_percent
from pgstattuple('users');

先对刚加载的种子数据运行这项查询，执行 update users set name = upper(name) 后再查一次，执行 vacuum users 后再查一次：

              | table_len | tuple_count | dead_tuple_count | free_percent
--------------+-----------+-------------+------------------+--------------
 fresh        |   9224192 |      100000 |                0 |         0.84
 after update |  18448384 |      100000 |           100000 |         0.84
 after vacuum |  18448384 |      100000 |                0 |        50.25

更新使表翻了一倍。由于没有一次更新是 HOT（页原本已经填满），两个索引也各自新增了 100,000 个条目。清理移除了死元组，留下一个 18 MB、空闲空间占一半的表。清理产生的是页内的空闲空间，而不是磁盘上的空间：它只能把文件末尾的空页交还出去，而这里旧版本填满了前半部分。

这些空间并没有浪费；第二次完整更新复用了它，表仍然是 18 MB，而不是 27 MB。批量更新后的表，会稳定在活跃行加上两次清理之间堆积的死元组所需的大小。多出来的部分就是膨胀。少量膨胀很正常，甚至有用。一张表如果膨胀到活跃数据的 5 倍大小，就有问题：每次扫描都要读取 5 倍的页，缓存中能容纳的有用行也减至原来的五分之一。而它就来自应用代码中看起来无害的一件事：一次触及每一行的回填。

select lab.prove(
  'after updating every row: about 18 MB, 100,000 live and 100,000 dead tuples',
  table_len between 18000000 and 19000000 and tuple_count = 100000 and dead_tuple_count = 100000)
from pgstattuple('users');

select lab.prove(
  'after vacuum: no dead tuples, still about 18 MB, about half of it free space',
  dead_tuple_count = 0 and table_len between 18000000 and 19000000 and free_percent between 45 and 55)
from pgstattuple('users');

select lab.prove(
  'the next full update reuses the space: still about 18 MB, not 27',
  pg_relation_size('users') between 18000000 and 19000000);

长事务会把一切都拖住

清理只有在没有任何快照可能仍然看到一个死元组时，才能移除它。所以数据库中任何地方的一个旧快照，都会拖住每张表的清理。这是失控膨胀最常见的原因之一，而且你可以看着它发生。

会话 A 开启一个可重复读事务（所以它的快照在事务结束前固定不变），读取一些内容，然后停在那里。pg_stat_activity 将它的快照界线显示为 backend_xmin，值为 868917：这是 A 取得快照时仍在运行的最老事务。与此同时，会话 B 删除表的一半数据并执行清理：

-- session B
delete from users_ff100 where id % 2 = 0;
vacuum (verbose) users_ff100;

...
tuples: 0 removed, 110000 remain, 60000 are dead but not yet removable
removable cutoff: 868917, which was 2038 XIDs old when operation ended
...

60,000 个死元组尚不能移除：其中 50,000 个是刚刚删除的，另外 10,000 个来自 fillfactor 实验。截止点恰好是会话 A 的 backend_xmin。A 从未碰过 users_ff100，这也无关紧要：它的快照早于这次删除，因此它仍可能请求这些行。A 一提交，同样的清理就会移除全部 60,000 个元组，并把表从 1,239 页截断到 1,126 页。

select lab.prove(
  'vacuum removes nothing: the 50,000 deleted rows are still there, dead but not yet removable',
  dead_tuple_count = 50000) from pgstattuple('users_ff100');

select lab.prove(
  'once A commits, the same vacuum removes them',
  dead_tuple_count = 0) from pgstattuple('users_ff100');

select lab.prove(
  'and truncates the table from 1,239 to 1,126 pages',
  pg_relation_size('users_ff100') / 8192 = 1126);

在生产环境中，阻塞者可能是因为漏掉提交而处于idle in transaction状态的连接，运行了六个小时的分析查询，或者某人在午饭前于 psql 中启动后就忘记了的迁移。常见嫌疑包括：长事务（包括打开了 hot_standby_feedback 的副本上的事务）、处于 idle-in-transaction 状态的会话（设置 idle_in_transaction_session_timeout）、未消费的复制槽，以及被遗忘的预备事务。当任意事务打开超过几分钟时就发出告警；清理无法移除旧快照可能需要的内容给出了找出这些对象的查询。

旧行必须在 XID 计数器回卷前被冻结

磁盘上的 XID 是 32 位的，Postgres 会在一个圆环上比较它们，因此如果某一行的 xmin 落后超过大约 21 亿个事务，它突然就会看起来像来自未来，然后消失。清理通过冻结旧行来防止这种情况：把它们标记为永远对所有人可见；如果一页上的所有行都已冻结，后续每次清理都会跳过这一页。如果冻结落后了，通常是因为某些东西把 xmin 界线拖住，Postgres 就会停止接受写入；回卷会在任何内容损坏前停止写入会介绍设置、监控和恢复。

vacuum (freeze) account_usage;

select lp, combined_flags,
       'HEAP_XMIN_COMMITTED' = any(raw_flags) as committed,
       'HEAP_XMIN_INVALID' = any(raw_flags) as invalid
from heap_page_items(get_raw_page('account_usage', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp_flags = 1;

 lp |   combined_flags   | committed | invalid
----+--------------------+-----------+---------
  4 | {HEAP_XMIN_FROZEN} | t         | t
  5 | {HEAP_XMIN_FROZEN} | t         | t
  6 | {HEAP_XMIN_FROZEN} | t         | t

select lab.prove(
  'after vacuum (freeze), every live tuple is HEAP_XMIN_FROZEN: committed and invalid together',
  count(*) = 3 and bool_and('HEAP_XMIN_FROZEN' = any(combined_flags))
  and bool_and('HEAP_XMIN_COMMITTED' = any(raw_flags) and 'HEAP_XMIN_INVALID' = any(raw_flags)))
from page0 where lp_flags = 1;

select lab.prove(
  'the visibility map marks the page all-visible and all-frozen',
  all_visible and all_frozen) from pg_visibility_map('account_usage') where blkno = 0;

隔离级别主要决定何时取得快照

快照也解释了隔离级别：一个级别改变的主要是取得快照的时机。读已提交是默认级别，每条语句取得一个快照，所以同一事务中的两次 select 可能看到不同的数据。可重复读和可串行化则每个事务取得一个快照，因此更新一行时，如果它在取得快照之后被别人修改过，更新就会因串行化错误而失败，并且应用必须重试（只有可串行化能排除所有异常和大家都得排队会继续介绍）。无论处于哪个级别，打开的事务都会让其快照拖住清理。让事务尽量短。

本章要点

• Postgres 从不原地更新一行。update 写入一个完整的新版本，并用更新者的 XID 标记旧版本；delete 只做标记。两者都会留下死元组。
• 快照决定查询看到每一行的哪个版本，因此读者和写者不会互相阻塞。
• 当没有索引列发生变化且新版本能放入同一页时，HOT 更新会跳过所有索引。对于每次更新都会变化的列，不要在没有权衡代价的情况下给它们建索引。
• 清理会把死元组变成表内可复用的空间，很少缩小文件。大型更新要分批执行。
• 一个长时间运行或处于 idle-in-transaction 状态的会话，会阻塞整个数据库的清理。要按事务年龄告警。
• 旧行必须在其 XID 落后大约 20 亿个事务之前被冻结；清理一章会介绍如何监视这一点。
• 第一次读取刚加载的行时会写入提示位；批量加载后，在用户访问数据前运行 vacuum analyze。

延伸阅读

• Concurrency control: introduction和transaction isolation，PGDG。官方模型，以及每个隔离级别究竟保证什么。
• Preventing transaction ID wraparound failures，PGDG。冻结、年龄设置，以及接近限制时会发生什么。
• src/backend/access/heap/README.tuplock，PostgreSQL 源码。行锁和 MultiXact 如何存放在 xmax 中。
• Hint Bits，PostgreSQL Wiki。进一步了解为什么读取也可能写入。
• Concurrency control，Hironobu Suzuki，《The Internals of PostgreSQL》。通过图示逐步讲解可见性规则。