PostgreSQL百万行级表新增带默认值0的列的最优方案咨询

直接新增带默认值列的合理性与耗时分析 #

这个操作的合理性完全取决于你使用的PostgreSQL版本：

• PostgreSQL 11及以上版本：直接操作是合理的。从该版本开始，新增带常量默认值的列属于纯元数据变更，不需要重写全表、也不需要逐行写入默认值，百万级表的执行耗时通常在毫秒级，只会持有极短时间的排他锁，对业务影响极小。
• PostgreSQL 10及更低版本：绝对不建议直接操作。低版本新增带默认值的列会触发全表重写，过程中会长时间持有表级排他锁，阻塞所有对该表的读写请求，百万行表的执行耗时可能达到数分钟甚至更久，完全无法在业务无感知的情况下完成。

最优实现方案 #

方案1：PostgreSQL 11及以上版本 #

直接执行DDL即可，建议加锁超时参数避免意外阻塞：

-- 设置2秒锁超时，拿不到锁自动放弃，不会长时间阻塞业务
SET lock_timeout = '2s';
-- 新增列，根据业务需要调整字段类型，这里以INT为例
ALTER TABLE your_table_name ADD COLUMN new_column_name INT NOT NULL DEFAULT 0;

方案2：PostgreSQL 10及更低版本/要求最大程度降低业务影响的场景 #

分三步执行，全程锁表时间都控制在毫秒级：

1. 先新增无默认值的可空列，仅修改元数据，秒级完成

SET lock_timeout = '2s';
ALTER TABLE your_table_name ADD COLUMN new_column_name INT;

2. 分批批量更新历史数据，每次更新1000~10000行，避免长时间锁表和过高IO压力，循环执行以下语句直到所有历史行更新完成：

UPDATE your_table_name 
SET new_column_name = 0 
WHERE id IN (
    SELECT id FROM your_table_name 
    WHERE new_column_name IS NULL 
    LIMIT 1000
);

3. 所有历史数据更新完成后，给列设置默认值和非空约束：

SET lock_timeout = '2s';
ALTER TABLE your_table_name 
ALTER COLUMN new_column_name SET DEFAULT 0,
ALTER COLUMN new_column_name SET NOT NULL;

注意事项 #

• 所有DDL操作尽量选择业务低峰期执行
• 批量更新历史数据时可根据数据库负载调整单次更新的行数，避免影响正常业务请求

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