给含数百万条记录的PostgreSQL表加列耗时久，如何提升性能？

针对PostgreSQL 12.11版本，处理400万行表的加列操作，可从以下几个方向优化：

1. 排查全表重写触发原因 #

PostgreSQL 11+版本中，添加可为空且无默认值的列是O(1)的即时操作，无需重写表。如果你的ALTER TABLE my_table ADD COLUMN new_column INT;执行缓慢，先排查以下可能触发全表重写的因素：

• 表是否存在行级触发器（重写时会逐行触发，增加耗时）
• 表是否包含TOAST存储的大字段（重写时需同步处理TOAST数据）
• 是否开启同步复制（操作需等待备库确认，拉长执行时间）
• 表上是否挂载大量非主键/唯一索引（全表重写时会同步重建索引）

2. 分批处理规避长时锁表 #

如果必须进行全表重写（比如需要给列设置非空默认值），用分批更新的方式拆分操作，减少锁表时长：

• 先快速添加可为空列：ALTER TABLE my_table ADD COLUMN new_column INT;
• 分批更新列值，每次处理小批量数据（批量大小根据服务器性能调整，建议1000-10000行）：

  WITH batch AS (
    SELECT id FROM my_table WHERE new_column IS NULL LIMIT 1000
  )
  UPDATE my_table SET new_column = 0 WHERE id IN (SELECT id FROM batch);
  

  重复执行直到所有行更新完成
• 最后设置非空约束：ALTER TABLE my_table ALTER COLUMN new_column SET NOT NULL;（此操作仅扫描全表，不会重写表，耗时远低于直接添加带默认值的列）

3. 临时禁用非必要触发器与索引 #

临时关闭触发器和非核心索引，可大幅提升操作速度：

• 禁用所有触发器：ALTER TABLE my_table DISABLE TRIGGER ALL;
• 禁用非主键/唯一索引：ALTER INDEX idx_my_table_target_col DISABLE;
• 操作完成后恢复：

  ALTER TABLE my_table ENABLE TRIGGER ALL;
  ALTER INDEX idx_my_table_target_col ENABLE;
  

  注意：需在业务低峰期操作，操作后要验证数据一致性。

4. 临时调整数据库配置参数 #

针对当前会话调整以下参数，提升维护操作效率：

• 增大维护内存：SET maintenance_work_mem = '1GB';（根据服务器内存调整，默认通常为64MB）
• 关闭同步提交：SET synchronous_commit = off;（避免等待WAL写入磁盘，操作完成后自动恢复默认）
• 增大WAL缓冲区：SET wal_buffers = '64MB';（提升WAL写入效率）

5. 在线表重建或逻辑复制（零停机场景） #

如果对停机时间要求极高，可采用以下方案：

• 使用pg_repack工具：无需锁表即可在线重建表并添加列，执行命令：

  pg_repack -d your_database -t my_table --add-column "new_column INT"
  

• 逻辑复制迁移：
  1. 创建含新列的空表：CREATE TABLE my_table_new (LIKE my_table INCLUDING ALL) ADD COLUMN new_column INT;
  2. 开启逻辑复制，将原表数据同步到新表
  3. 低峰期切换表名：ALTER TABLE my_table RENAME TO my_table_old; ALTER TABLE my_table_new RENAME TO my_table;
  4. 验证数据后删除旧表

6. 选择业务低峰期执行 #

最直接的方式是在业务流量最低的时间段操作，即使耗时几分钟，对整体性能的影响也会降至最低。

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