PostgreSQL添加SERIAL自增列磁盘空间异常占满如何解决？

PostgreSQL 中执行ALTER TABLE ... ADD COLUMN如果列带非常量默认值（SERIAL类型本质是自动绑定序列的integer类型，添加时会给所有现有行调用序列函数生成自增值作为默认值），会触发全表重写：

• 重写过程中会同时保留旧表文件和新生成的表文件，仅这部分就需要至少和原表相当的磁盘空间
• 过程中产生的WAL日志也会占用额外磁盘空间，最终总占用会超过原表体积，你只有30G剩余空间远不够支撑57G表的重写操作

方案1：先加无默认值列，再分批更新填充 #

这种方式不会触发全表重写，磁盘占用极低，是剩余空间不足场景的首选：

1. 先添加可为空的整数列，不设置默认值，这步是元数据操作，瞬间完成无额外磁盘占用

ALTER TABLE mytable ADD COLUMN id integer;

2. 创建自增序列并绑定到该列，给后续新插入的行自动生成id

CREATE SEQUENCE mytable_id_seq OWNED BY mytable.id;
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('mytable_id_seq');

3. 分批更新现有行的id值，每次更新小批量数据避免锁表和产生大量瞬时磁盘占用，比如每次更新1万行：

-- 把语句里的「你表的主键列」替换成你实际表的主键/唯一键字段即可
WITH batch AS (
  SELECT 你表的主键列 FROM mytable WHERE id IS NULL LIMIT 10000
)
UPDATE mytable SET id = nextval('mytable_id_seq')
WHERE 你表的主键列 IN (SELECT 你表的主键列 FROM batch);

反复执行上面的更新语句直到所有行的id都被填充，最后可以根据需要把id列设为NOT NULL。

方案2：创建新表分批迁移 #

如果业务允许短时间的读写切换，也可以用这种方式：

1. 创建结构和原表完全一致的新表，提前添加好id自增列
2. 分批把旧表的数据插入到新表，自增列会自动生成值
3. 迁移完成后停写原表，同步最后一批增量数据，再重命名两张表完成替换

方案3：扩容磁盘后直接加列 #

如果可以扩容磁盘到至少120G以上剩余空间，也可以直接执行原来的ALTER TABLE命令，执行完成后执行VACUUM FULL即可释放多余的磁盘占用。

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