由 John Doe 九月 22, 2025
摘要：在本文中，我们将了解更新 PostgreSQL 表带来的索引写放大问题，以及相关的解决方案。
目录
表数据更新场景: 用例
下面我们在 PostgreSQL 的数据库中，创建一个表，插入一些数据，并对表数据进行更新操作，查看更新操作修改的页面数目。
CREATE TABLE boat (id integer, name text, reg double precision);
INSERT INTO boat(id, name, reg)
SELECT i, md5(i::text), log(i)
FROM generate_series(1, 500000) as i;
CREATE INDEX boat_idx1 ON boat(id);
CREATE INDEX boat_idx2 ON boat(reg);
VACUUM ANALYZE boat;
CHECKPOINT;
上面我们进行了检查点操作，刷写了缓冲区中的所有脏页面。这样，就可以通过 EXPLAIN ANALYZE 来查看修改的页面情况：
EXPLAIN (analyze, buffers, wal, costs false)
UPDATE boat SET name = 'trade winds'
WHERE mod(id, 1000) = 1;
QUERY PLAN
---------------------------------------------------------------------
Update on boat (actual time=119.992..119.993 rows=0 loops=1)
Buffers: shared hit=8680 read=1012 dirtied=1509 written=4
WAL: records=2000 fpi=1501 bytes=11616847
-> Seq Scan on boat (actual time=0.021..95.015 rows=500 loops=1)
Filter: (mod(id, 1000) = 1)
Rows Removed by Filter: 499500
Buffers: shared hit=4673
Planning:
Buffers: shared hit=73 read=1
Planning Time: 0.625 ms
Execution Time: 120.126 ms
(11 rows)
上面的 UPDATE 操作更新了 500 条记录，不涉及修改索引相关的列。总共修改了 1509 个页面，里面涉及约 500 个堆表页面和 1000 个索引页面。
由于刚刚进行过检查点操作，在 WAL 日志中产生了 1501 个页面镜像记录，WAL 日志总量为 11.08MB。
注意：PostgreSQL 服务器在检查点后对每个磁盘页面进行首次修改期间，会将该页面的全部内容写入 WAL 日志，即全页写（full page writes），目的是防止意外宕机时数据块部分写导致数据库无法恢复。此外，更新记录时若新记录位置（ctid）发生变更，索引记录也要相应变更并记录 WAL 日志，这会进一步导致索引页的全页写，加剧 WAL 写放大。
表数据更新场景: 内部原理
下面是我们在 PostgreSQL 中进行表更新时，单个页面内数据记录发生的变化：
PostgreSQL 采用多版本并发控制（MVCC）机制，当数据被更新或删除时，原始记录不会立即移除，而是标记为不可见，以支持事务回滚和快照读。更新操作在将旧行标记为不可见后，会在堆表中创建新的行版本（新元组）。
当新元组与旧元组不位于同一数据页，或者虽然处于同一数据页，但更新修改了索引相关的列时，就需要创建新的索引条目。例如，在一个订单状态频繁更新的订单表中，每次更新都需要创建新的索引条目，如果该表有 3 个索引，每次更新需要修改 4 个页面。
Redrock Postgres 的解决方案
Redrock Postgres 引入了撤消日志，修改元组时可实现就地更新，无需添加索引记录。由于无需修改索引，可避免冗余的索引插入操作，从而帮助减少索引膨胀。
下面我们在 Redrock Postgres 的数据库中，创建和上面相同的表，并插入同样的数据，对表数据进行更新操作，查看更新操作修改的页面数目。
CREATE TABLE boat (id integer, name text, reg double precision);
INSERT INTO boat(id, name, reg)
SELECT i, md5(i::text), log(i)
FROM generate_series(1, 500000) as i;
CREATE INDEX boat_idx1 ON boat(id);
CREATE INDEX boat_idx2 ON boat(reg);
VACUUM ANALYZE boat;
CHECKPOINT;
上面我们进行了检查点操作，刷写了缓冲区中的所有脏页面。这样，就可以通过 EXPLAIN ANALYZE 来查看修改的页面情况：
EXPLAIN (analyze, buffers, wal, costs false)
UPDATE boat SET name = 'trade winds'
WHERE mod(id, 1000) = 1;
QUERY PLAN
---------------------------------------------------------------------
Update on boat (actual time=36.476..36.478 rows=0 loops=1)
Buffers: shared hit=4757 read=8 dirtied=509
WAL: records=507 fpi=503 bytes=4162628
-> Seq Scan on boat (actual time=0.026..32.624 rows=500 loops=1)
Filter: (mod(id, 1000) = 1)
Rows Removed by Filter: 499500
Buffers: shared hit=3732
Planning:
Buffers: shared hit=5 read=3
Planning Time: 0.450 ms
Execution Time: 36.610 ms
(11 rows)
上面的 UPDATE 操作更新了 500 条记录，不涉及修改索引相关的列。总共修改了 509 个页面，涉及约 500 个堆表页面和少数其他页面。
由于刚刚进行过检查点操作，在 WAL 日志中产生了 503 个页面镜像记录，WAL 日志总量为 3.97MB。
显然，此处的更新操作没有修改任何索引页面。并且，执行性能相对会更加高效。