PostgreSQL 大表回填：批次顺序如何让 WAL 相差三倍

给一千万张工单补上 AI 分类，模型费用可能只有几十美元，真正昂贵的部分却可能发生在数据库里。来源实验在 PostgreSQL 18.6 上更新一百万行：同样写入 category 和 confidence，不同批次顺序让 HOT 更新率从 11.5% 变成 96.1%；加入逐批 checkpoint 后，原本更省 WAL 的方案反而写出了约三倍 WAL。

这说明大表回填不能只讨论“每批更新多少行”。行在物理页面上的分布、页面剩余空间、索引依赖和 checkpoint 频率，往往更能决定最终成本。

UPDATE 为什么会让表和索引一起膨胀

PostgreSQL 的普通 UPDATE 不会直接覆盖旧行。它会写入一个新的 tuple 版本，旧版本则要等 VACUUM 或页面剪枝确认没有事务需要后才能回收。

如果新版本不能留在原来的 heap page，PostgreSQL 通常还要给每个索引写入新条目，即使被更新的列本身没有建索引。来源实验中的表有三个索引：主键、created_at 和 customer_id。更新一百万行时，一次非 HOT 更新可能意味着：

• heap 中写入一个完整的新行版本；
• 三个索引分别增加一个指向新版本的条目；
• 相关 heap 和索引修改进入 WAL；
• checkpoint 后首次修改页面时，还可能产生 full-page image。

HOT（Heap-Only Tuple）更新可以避开新索引条目，但必须同时满足两个关键条件：

1. 新 tuple 能放进旧 tuple 所在的页面；
2. 本次修改没有触及任何索引依赖的列，包括部分索引谓词引用的列。

因此，category 和 confidence 没有普通索引，并不自动代表更新一定是 HOT。页面是否有空间才是第一道门槛。

来源实验里，按 fillfactor=100 顺序装载的表几乎没有页内余量。十个连续 ID 范围批次在不执行 VACUUM 时，把 heap 从 269.3 MB 推到 548.3 MB，索引从 35.6 MB 增长到 70.1 MB。把一个大 UPDATE 拆成十条语句，并没有自动减少 heap 膨胀，因为批次之间没有回收旧版本。

同样是十批，行的选择方式决定 HOT 比例

连续范围批次通常写成：

UPDATE tickets
SET category = 'billing', confidence = 0.91
WHERE id BETWEEN 1 AND 100000;

由于数据按 ID 顺序装载，一个范围会集中命中一组相邻页面，并尝试一次更新这些页面上的大部分行。即使表使用 fillfactor=90，为页面预留了空间，这些空间也不足以同时容纳页面内所有行的新版本。于是许多 tuple 被写到其他页面，HOT 条件随之失效。

交错批次则可以这样选择行：

UPDATE tickets
SET category = 'billing', confidence = 0.91
WHERE id % 10 = 0;

每一轮只更新各页面中大约十分之一的行。配合批次后的 VACUUM，后续轮次可以复用页面空间。来源实验显示，在 fillfactor=90 下：

交错更新几乎没有扩大索引，WAL 也接近减半。这里的收益不是来自 % 运算本身，而是来自更稀疏的页面内更新：每一轮只消耗一部分预留空间，VACUUM 后再处理下一部分。

但 checkpoint 会改变结论。来源实验在每批后强制执行 checkpoint 时，连续范围批次写入 1,025.3 MB WAL，而 id % 10 方案暴涨到 3,022.3 MB。

原因在于 checkpoint 之后，页面第一次被修改通常需要把 full-page image 写入 WAL。交错批次每一轮都会触碰遍布全表的大量页面；如果每轮之前或之后都有 checkpoint，同一批页面会在不同轮次反复产生 full-page image。连续范围虽然 HOT 比例低，却把每一轮的写入限制在较小的页面集合中。

实验中的折中方案是“范围内交错”：先选一个 ID 范围，在该范围内完成十轮 id % 10 更新，再进入下一个范围。它保留了 96.1% 的 HOT 比例，在每个范围后 checkpoint 的条件下只写入 544.0 MB WAL。

可以直接改造的回填脚本

下面的实验脚本会创建测试表。默认一百万行可能占用较多磁盘和 WAL；本地验证时可把 ROWS 改成 100000。请将 DATABASE_URL 指向测试数据库，不要直接在生产库执行。

export DATABASE_URL='postgresql://postgres:postgres@localhost:5432/postgres'
export ROWS=1000000

psql "$DATABASE_URL" -v rows="$ROWS" <<'SQL'
DROP TABLE IF EXISTS tickets;

CREATE TABLE tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL,
    customer_id int NOT NULL,
    subject text NOT NULL,
    body text NOT NULL
) WITH (fillfactor = 90);

INSERT INTO tickets (created_at, customer_id, subject, body)
SELECT
    now() - (g % 700) * interval '1 hour',
    ((g::bigint * 7919) % 50000)::int,
    'Ticket ' || g || ' about ' ||
        (ARRAY['billing','login','export','api','invoice','sso'])[1 + g % 6],
    repeat(md5(g::text), 6)
FROM generate_series(1, :rows) AS g;

CREATE INDEX tickets_created_at_idx ON tickets (created_at);
CREATE INDEX tickets_customer_id_idx ON tickets (customer_id);

ALTER TABLE tickets
    ADD COLUMN category text,
    ADD COLUMN confidence real;

VACUUM (ANALYZE) tickets;
SELECT pg_stat_reset();
CHECKPOINT;
SQL

如果数据库账号没有执行 CHECKPOINT 或 pg_stat_reset() 的权限，可以删掉这两条命令；它们用于控制实验条件，不是正常回填的必要步骤。

接着执行“范围内交错”的混合方案。调整 RANGE_SIZE 以控制每个工作集的大小：

export RANGE_SIZE=100000

for ((lo=1; lo<=ROWS; lo+=RANGE_SIZE)); do
  hi=$((lo + RANGE_SIZE - 1))

  for r in $(seq 0 9); do
    psql "$DATABASE_URL" \
      -v lo="$lo" -v hi="$hi" -v remainder="$r" <<'SQL'
UPDATE tickets
SET
    category = (ARRAY[
        'billing', 'login', 'export', 'api',
        'invoice', 'sso', 'security', 'account',
        'delivery', 'refund', 'bug', 'other'
    ])[1 + (id % 12)::int],
    confidence = (0.5 + ((id % 500)::real / 1000.0))::real
WHERE id BETWEEN :lo AND :hi
  AND id % 10 = :remainder
  AND category IS NULL;

VACUUM (ANALYZE) tickets;
SQL
  done

  # 基准测试时可以启用；生产环境通常不应为每个范围强制 checkpoint。
  # psql "$DATABASE_URL" -c 'CHECKPOINT;'
done

运行后可以检查 HOT 比例和对象大小：

SELECT
    n_tup_upd,
    n_tup_hot_upd,
    round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'tickets';

SELECT
    pg_size_pretty(pg_relation_size('tickets')) AS heap,
    pg_size_pretty(pg_indexes_size('tickets')) AS indexes,
    pg_size_pretty(pg_total_relation_size('tickets')) AS total;

如果要比较 WAL，可在回填前后记录 LSN：

start_lsn=$(psql "$DATABASE_URL" -Atc 'SELECT pg_current_wal_lsn()')

# 在这里执行回填脚本

psql "$DATABASE_URL" -c \
  "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '$start_lsn')) AS wal_written;"

上线前不要漏掉这些边界条件

fillfactor 很重要，但修改参数不等于立即重写已有页面：

ALTER TABLE tickets SET (fillfactor = 90);

来源实验中，即使表最初按 fillfactor=100 装载，仅设置新 fillfactor 也达到了 86.2% HOT；通过 VACUUM FULL 或在线重组真正重写表后，HOT 比例进一步达到 96.1%。不过 VACUUM FULL 会获取强锁并需要额外磁盘空间，不能把它当作无成本准备步骤。

索引设计同样需要复查。下面这个部分索引虽然没有把 confidence 放进索引键，但谓词依赖了它：

CREATE INDEX tickets_low_confidence_idx
ON tickets (customer_id)
WHERE confidence < 0.8;

此时更新 confidence 会影响某行是否属于索引，来源实验中相关更新不再是 HOT。回填前应检查表达式索引、部分索引谓词，以及生成列背后的依赖，而不只是查看“新列有没有普通索引”。

数据类型也会改变页面压力。实验发现，把概率映射与标签一起存在行内时，jsonb 比 real[] 损失了更多 HOT 更新。对于大规模回填，应先用真实行宽和真实值分布测试，而不是只比较类型的表达能力。

生产采用时可以按以下顺序决策：

• 在副本或同规模测试表上测量 HOT 比例、heap、索引和 WAL，而不只看执行时间；
• 确认待更新列没有被索引键、表达式或部分索引谓词依赖；
• 根据物理装载顺序选择范围大小，再在范围内部交错更新；
• 给 VACUUM 留出执行窗口，同时观察长事务是否阻碍旧版本回收；
• 不要机械地逐批强制 checkpoint，尤其不要让全表交错扫描与高频 checkpoint 组合；
• 评估复制槽、只读副本、归档带宽和磁盘峰值，因为 WAL 成本会传导到整个复制链路；
• 为任务增加可恢复条件，例如 category IS NULL，并记录每个范围的完成状态。

批处理并不是天然的优化。真正有效的回填方案，需要同时约束“每批有多少行”“这些行分布在哪些页面上”以及“页面在两个 checkpoint 之间会被触碰多少次”。