PostgreSQL中复制表时如何重排数组内自定义类型字段

用户定义了两个复合类型与对应数据表：

CREATE TYPE "MyTypeOld" AS (
  a SMALLINT, 
  b BIGINT,
  c SMALLINT
);

CREATE TYPE "MyType" AS (
  a SMALLINT, 
  c SMALLINT,
  b BIGINT
);

CREATE TABLE old (
  items MyTypeOld[]
);

CREATE TABLE new (
  items MyType[]
);

-- 测试数据示例
INSERT INTO old (items) VALUES ('{"(1,9999999, 2)", "(1,999999988, 2)"}');

old表存储有约500GB数据，new表数据量极少，需要将old表的所有数据迁移至new表（目的是通过调整复合类型字段顺序优化内存布局、节省存储空间），之后需彻底删除旧表。用户尝试了以下语句，但需要更正确的实现方案：

INSERT INTO new (items) (SELECT items[:](a, c, b) FROM old);

1. 基础转换语句 #

PostgreSQL无法直接批量转换数组内的复合类型元素，需先展开数组、逐个调整元素字段顺序，再重新聚合为目标类型数组：

INSERT INTO new (items)
SELECT array_agg((elem.a, elem.c, elem.b)::MyType)
FROM old
CROSS JOIN unnest(old.items) AS elem
GROUP BY old.ctid; -- 以ctid为分组依据，确保每条原记录对应一条新记录

2. 大数据量优化措施 #

针对500GB的超大数据量，直接全量插入可能引发内存溢出或性能瓶颈，建议采取以下优化：

• 分批插入：按主键或ctid范围分批处理，避免一次性加载全表数据：

-- 示例：每次处理10000条记录，可调整范围控制批次
WITH batch AS (
  SELECT old.ctid, array_agg((elem.a, elem.c, elem.b)::MyType) AS converted_items
  FROM old
  CROSS JOIN unnest(old.items) AS elem
  WHERE old.ctid >= '(0,1)' AND old.ctid < '(10000,1)'
  GROUP BY old.ctid
)
INSERT INTO new (items)
SELECT converted_items FROM batch;

• 关闭自动提交：执行SET autocommit = off;，减少事务日志的频繁写入开销。
• 临时禁用约束/索引：先删除new表的索引与非必要约束，待数据插入完成后重新创建，大幅提升插入速度。
• 启用并行查询：确保数据库配置支持并行查询，加速数据转换与聚合过程。

3. 数据校验与收尾 #

插入完成后，务必验证数据一致性：

-- 校验记录总数
SELECT COUNT(*) FROM old;
SELECT COUNT(*) FROM new;

-- 校验数组元素总数
SELECT SUM(array_length(items, 1)) FROM old;
SELECT SUM(array_length(items, 1)) FROM new;

-- 随机抽查数据匹配度
SELECT items FROM old ORDER BY random() LIMIT 5;
SELECT items FROM new ORDER BY random() LIMIT 5;

确认数据完全一致后，即可删除旧表：

DROP TABLE old;

内容的提问来源于stack exchange，提问作者S.R