由 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);
VACUUM boat;
CHECKPOINT;
上面我们进行了检查点操作，刷写了缓冲区中的所有脏页面。这样，就可以通过 EXPLAIN ANALYZE 来查看修改的页面情况：
EXPLAIN (analyze, buffers)
UPDATE boat SET name = 'trade winds'
WHERE mod(id, 1000) = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Update on boat (cost=0.00..12173.00 rows=0 width=0) (actual time=111.126..111.127 rows=0 loops=1)
Buffers: shared hit=7686 read=506 dirtied=1009 written=4
-> Seq Scan on boat (cost=0.00..12173.00 rows=2500 width=38) (actual time=0.027..95.165 rows=500 loops=1)
Filter: (mod(id, 1000) = 1)
Rows Removed by Filter: 499500
Buffers: shared hit=4673
Planning:
Buffers: shared hit=13 read=1
Planning Time: 0.332 ms
Execution Time: 111.328 ms
(10 rows)
上面的 UPDATE 操作更新了 500 条记录，修改了 1009 个页面，里面涉及约 500 个堆表页面和 500 个索引页面。
表数据更新场景: 内部原理
下面是我们在 PostgreSQL 中进行表更新时，单个页面内数据记录发生的变化：
PostgreSQL 的多版本并发控制（MVCC）系统旨在减少锁竞争：对于可能仍被旧事务需要的行，更新操作不会直接覆盖，而是在堆表中创建新的行版本（新元组）。当新元组与旧元组不位于同一数据页，或者虽然处于同一数据页，但更新修改了索引相关的列时，就需要创建新的索引条目。之后，旧元组会在堆表中被标记为 “无效”（dead），等待后续清理。
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);
VACUUM boat;
CHECKPOINT;
上面我们进行了检查点操作，刷写了缓冲区中的所有脏页面。这样，就可以通过 EXPLAIN ANALYZE 来查看修改的页面情况：
EXPLAIN (analyze, buffers)
UPDATE boat SET name = 'trade winds'
WHERE mod(id, 1000) = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Update on boat (cost=0.00..11232.00 rows=0 width=0) (actual time=31.615..31.616 rows=0 loops=1)
Buffers: shared hit=4753 read=7 dirtied=508
-> Seq Scan on boat (cost=0.00..11232.00 rows=2500 width=38) (actual time=0.014..29.597 rows=500 loops=1)
Filter: (mod(id, 1000) = 1)
Rows Removed by Filter: 499500
Buffers: shared hit=3732
Planning:
Buffers: shared hit=2 read=7
Planning Time: 0.260 ms
Execution Time: 31.707 ms
(10 rows)
上面的 UPDATE 操作更新了 500 条记录，修改了 508 个页面，涉及约 500 个堆表页面和少数其他页面。
显然，此处的更新操作没有修改任何索引页面。并且，执行性能相对会更加高效。