如何为超大型表（1亿+行）添加存储生成列？

PostgreSQL目前确实没有官方支持的异步添加存储生成列的机制（类似CONCURRENTLY创建索引），不过针对1亿+行的超大型表，有几种比你当前空函数方案更可靠的优化思路：

方案1：先加普通列分批回填，再转为生成列 #

这是最常用的低锁表风险方案，核心是把大操作拆分为小步骤，避免一次性全表计算：

1. 添加普通空列：这一步是元数据操作，几乎瞬间完成，不会长时间锁表
2. 分批回填数据：按主键或其他有序字段拆分批次，逐批更新并提交，每批仅锁定少量行
3. 转换为存储生成列：因为数据已经符合生成规则，这一步仅修改元数据，耗时极短

示例代码：

-- 1. 添加普通空列（元数据操作，快速完成）
ALTER TABLE my_table ADD COLUMN column_c TEXT;

-- 2. 分批回填数据（假设主键为bigint类型的id，每批处理10000行）
DO $$
DECLARE
    batch_size INT := 10000;
    max_id BIGINT;
    current_id BIGINT := 0;
BEGIN
    SELECT MAX(id) INTO max_id FROM my_table;
    WHILE current_id < max_id LOOP
        UPDATE my_table
        SET column_c = concat(column_a, '_', column_b) -- 替换为你的实际计算逻辑
        WHERE id > current_id AND id <= current_id + batch_size;
        COMMIT; -- 每批提交，释放锁
        current_id := current_id + batch_size;
        -- 可选：添加短暂延迟，避免打满CPU/IO
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

-- 3. 转为存储生成列（数据已匹配，仅修改元数据）
ALTER TABLE my_table ALTER COLUMN column_c SET GENERATED ALWAYS AS (concat(column_a, '_', column_b)) STORED;

方案2：基于分区表的增量处理 #

如果你的表已经是分区表，或者可以临时拆分为分区：

• 逐个对分区添加生成列：单个分区数据量远小于全表，每个分区的ALTER TABLE操作耗时短，锁表范围仅限该分区
• 所有分区处理完成后，在主表层面统一添加生成列的定义，确保后续新分区自动继承该规则

方案3：逻辑复制离线处理（高可用性场景） #

如果业务完全不能接受锁表或性能波动，可以借助逻辑复制：

1. 搭建一个逻辑副本，将主表数据同步到副本
2. 在副本上离线添加存储生成列（此时不影响主业务）
3. 验证副本数据与主表一致后，切换业务流量到副本，原表可作为备用或后续清理

结合你2024-01-21的测试结果：空函数方案耗时7分钟，直接添加耗时14分钟，若业务能接受14分钟的维护窗口，直接添加确实是最简洁的方案。但如果维护窗口不足，上述分批处理的方案可以将锁表时间分散到数十个小事务中，避免长时间阻塞业务。

内容的提问来源于stack exchange，提问作者Alexi Theodore