Postgres 大表更新性能优化：CTAS-and-swap 方案

说到数据工程，规模至关重要。在只有十几行到上千行的小型 SQL 表上更新一行，通常不会引起任何担忧。但当数据量达到数千万行，并且需要更新所有行时，简单的 SQL UPDATE 命令就会变得低效，原本几分钟的操作可能拖成数小时。

进行大规模优化的第一步，是理解瓶颈所在。在这个案例中，瓶颈就是时间。为什么更新一张拥有数千万行数据的表时，耗时会呈指数级增长？根源在于 Postgres 的 MVCC 设计。

MVCC 机制下，UPDATE 实际做了什么

Postgres 从不会直接在原位置修改一行数据。每次 UPDATE 都会写入一个新版本的行，并将旧版本标记为已死亡。这就是 MVCC 的代价：读操作可以获得一致性快照，而不会阻塞写操作，但旧版本会一直保留下来，直到 VACUUM 将其清理。

对于单次 UPDATE，这种影响几乎不可见。但如果在同一张表上执行数百万次 UPDATE，影响就会不断叠加。每次 UPDATE 都会在表的数据页中增加一个死元组；与此同时，表上的每个索引都会新增一个指向新版数据行的条目，而旧条目则会一直保留到 VACUUM 执行为止。

随着死元组不断积累：

每个数据页中，单位页面能够容纳的有效数据越来越少。为了访问同等数量的有效数据，Postgres 不得不读写更多页面。

索引会因为死亡条目而膨胀。对于同一次查询，B-tree 遍历需要经过更多页面。

后续每一次 UPDATE 都比上一次需要完成更多工作。这并不是因为查询发生了变化，而是因为查询面对的表越来越臃肿。

当你决定使用 UPDATE 重写整张表时，性能下降就已经注定了。

如果这种转换任务还要定期运行，问题会进一步恶化。每一轮执行所承担的膨胀成本都高于上一轮，因此，这个月能在 3 小时内完成的 pipeline，下个月可能需要 4 小时，而原因与你编写的代码毫无关系。VACUUM 也不是出路：它需要扫描整张表，以寻找死空间并将其标记出来，因此它自身的成本也会随表大小线性增长。在一张拥有 2000 万行数据的表上，运行 VACUUM 往往需要几分钟到几小时；而对于正在被大量更新的表，autovacuum 通常还会跟不上更新速度。

解决方案：不要 UPDATE，而是 INSERT 到一张新表

这种模式叫作 CTAS-and-swap，即“Create Table As Select and swap”。不要直接更新原表中的数据行，而是创建一张新的空表，将转换后的数据行 INSERT 到新表中，然后用新表替换旧表的位置。

-- 1. Create the new table with the same shape
CREATE TABLE records_new (LIKE records INCLUDING ALL);

-- 2. Insert the transformed rows (in parallel, batched, however you like)
INSERT INTO records_new
SELECT record_id, col_a, transform(col_b), col_c, ...
FROM records;

-- 3. Atomically swap
BEGIN;
ALTER TABLE records RENAME TO records_old;
ALTER TABLE records_new RENAME TO records;
COMMIT;

-- 4. Drop the old
DROP TABLE records_old;

每次 INSERT 面对的都是一张全新、空白的表。没有死元组，没有数据页膨胀，也没有索引膨胀，更不会出现任务越往后执行，写入成本越高的问题。从第 1 行到第 2000 万行，写入速度始终保持稳定。

交换表只是一次元数据操作，并不涉及数据搬迁。它需要短暂获取表级锁，但无论表有多大，都能在几毫秒内完成。

CTAS-and-swap 假设在转换期间，源表不会被并发修改。在此前提下，还有两个值得明确说明的约束：

磁盘余量。在一个短暂的时间窗口内——新表正在构建，旧表尚未删除——两张表会同时占用磁盘空间。如果磁盘容量已经接近上限，就必须提前为此做好规划。

恢复执行需要额外记录。如果 INSERT 阶段是单个事务中的 INSERT ... SELECT，一旦发生崩溃，所有操作都会回滚，只能从头重新开始。如果该阶段由脚本驱动，并在处理过程中分批提交——例如第 2 部分介绍的并行 worker 方案——那么已经提交的批次可以在崩溃后保留下来。但新表本身没有天然的标记，无法告诉你“已经处理到了哪里”，因此，要实现断点恢复，就需要记录崩溃前已经完成了哪些数据范围。

促使我们采用这一方案的 pipeline，需要使用 Python transform 重新处理一张拥有 2000 万行数据的表。它使用 8 个并行 worker，每个 worker 负责一段主键范围。在基于 UPDATE 的方案中，每轮执行的实际耗时超过 3 小时；而每两周执行一次的频率，又意味着不同轮次之间的表膨胀会持续叠加，进一步放大单轮执行过程中本就存在的性能下降。

在相同硬件、相同 8 个 worker、相同 2000 万行数据的条件下，切换到 CTAS-and-swap 后，耗时降至 12 分钟，性能提升约 14 倍。这一提升完全来自写入方式的改变；读取侧和 enrichment cache 并没有变化，它们分别在本系列的第 2 部分和第 1 部分中介绍。

如果你需要对 1000 万行以上的数据进行全表转换，那么这种模式理应成为默认工具箱中的一员。UPDATE 自有其用武之地，但不适合处理这种规模的批量重新计算任务。

若要采取进一步措施，你可以考虑屏蔽此人和/或举报滥用行为。