如何在不超磁盘空间限制下将text列转换为jsonb类型

问题原因 #

直接执行ALTER TABLE会触发全表重写，PostgreSQL会创建原表的完整副本并转换列类型，这需要至少与原表大小相当的额外磁盘空间（30GB），加上事务日志和临时文件的开销，导致你的可用空间（30GB）不足以完成操作。

方法一：新增列并分批更新 #

这种方法通过逐步转换数据，避免一次性占用大量磁盘空间：

1. 添加新的jsonb列
   先创建一个可空的jsonb列，避免初始时占用过多空间：

   ALTER TABLE pointstable ADD COLUMN aimjoined_new jsonb;
   

2. 分批更新数据
   利用主键或唯一索引列（假设为id）分批次转换并更新，每次提交一小部分数据，控制磁盘占用。示例中每次处理10000行：

   DO $$
   DECLARE
       batch_size INT := 10000;
       max_id INT;
       current_id INT := 0;
   BEGIN
       SELECT MAX(id) INTO max_id FROM pointstable;
       WHILE current_id < max_id LOOP
           UPDATE pointstable
           SET aimjoined_new = to_jsonb(aimjoined::json)
           WHERE id > current_id AND id <= current_id + batch_size;
           COMMIT;
           current_id := current_id + batch_size;
           -- 可选：每处理10批后执行一次vacuum回收空间
           -- IF current_id % (batch_size * 10) = 0 THEN
           --     VACUUM pointstable;
           -- END IF;
       END LOOP;
   END $$;
   

   • 调整batch_size：如果每次更新后磁盘占用仍过高，可减小批次大小（如5000行）。
   • 若表无主键，可使用ctid临时替代（仅适合当前操作，ctid会随表重写变化）：

     DO $$
     DECLARE
         batch_size INT := 10000;
     BEGIN
         LOOP
             UPDATE pointstable
             SET aimjoined_new = to_jsonb(aimjoined::json)
             WHERE aimjoined_new IS NULL
             LIMIT batch_size;
             COMMIT;
             EXIT WHEN NOT FOUND;
         END LOOP;
     END $$;
     

3. 验证转换完整性
   确认所有行都已转换：

   SELECT COUNT(*) FROM pointstable WHERE aimjoined_new IS NULL;
   

4. 替换原列

   • 若原列是NOT NULL，先设置新列为非空：

     ALTER TABLE pointstable ALTER COLUMN aimjoined_new SET NOT NULL;
     

   • 删除原列并将新列重命名：

     ALTER TABLE pointstable DROP COLUMN aimjoined;
     ALTER TABLE pointstable RENAME COLUMN aimjoined_new TO aimjoined;
     

注意事项 #

• 并发写入：如果表有并发写入操作，建议在低峰期执行，或使用NOWAIT/SKIP LOCKED（PostgreSQL 9.5+）避免长时间锁表导致的冲突。
• 索引与触发器：若原列有索引或触发器，可先删除/禁用它们，完成转换后再重建/启用，减少更新时的性能开销。
• 事务日志：分批提交会减小事务日志的压力，避免日志文件过度膨胀。

方法二：使用临时表分批迁移（可选） #

如果分批更新仍有空间压力，可创建新表并分批插入转换后的数据：

1. 创建结构一致的新表，直接定义目标列类型：

   CREATE TABLE pointstable_new (LIKE pointstable INCLUDING ALL);
   ALTER TABLE pointstable_new ALTER COLUMN aimjoined TYPE jsonb USING to_jsonb(aimjoined::json);
   

2. 分批插入数据：

   DO $$
   DECLARE
       batch_size INT := 10000;
       max_id INT;
       current_id INT := 0;
   BEGIN
       SELECT MAX(id) INTO max_id FROM pointstable;
       WHILE current_id < max_id LOOP
           INSERT INTO pointstable_new
           SELECT * FROM pointstable WHERE id > current_id AND id <= current_id + batch_size;
           COMMIT;
           current_id := current_id + batch_size;
       END LOOP;
   END $$;
   

3. 切换表（需短暂锁表）：

   BEGIN;
   DROP TABLE pointstable;
   ALTER TABLE pointstable_new RENAME TO pointstable;
   COMMIT;
   

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