PostgreSQL克隆含索引、约束及全量数据的表的高效方案咨询

PostgreSQL 大表克隆优化方案 #

你之前的方案执行慢的核心原因是插入数据时需要同步维护索引、检查约束，每插入一行都要更新所有关联索引，300万行数据累积开销非常高。按照以下步骤操作可将执行时间压缩到2~5分钟区间：

1. 仅创建基础表结构 #

先不引入索引、约束等会拖慢插入速度的对象，只保留基础的字段属性和存储配置：

DROP TABLE IF EXISTS "dbPlan"."tb_plan_next";
CREATE TABLE "dbPlan"."tb_plan_next" (LIKE "dbPlan"."tb_plan" INCLUDING DEFAULTS INCLUDING STORAGE);

INCLUDING DEFAULTS保留字段默认值，INCLUDING STORAGE保留TOAST等存储策略，避免后续插入大字段异常

2. （可选）临时关闭目标表WAL日志写入 #

如果你的数据库无强制归档要求、当前操作不在备库执行，可以开启UNLOGGED属性减少日志写入开销：

ALTER TABLE "dbPlan"."tb_plan_next" SET UNLOGGED;

注意：UNLOGGED表在实例崩溃时会丢失数据，插入完成后必须改回正常模式，有强数据安全要求可跳过该步骤

3. 用COPY命令批量插入数据 #

PG内置的COPY命令是批量数据导入性能最高的方式，比常规INSERT SELECT性能高30%以上：

-- PG12及以上版本支持直接从查询结果导入
COPY "dbPlan"."tb_plan_next" FROM (SELECT * FROM "dbPlan"."tb_plan") WITH (FORMAT binary);

-- 低版本PG可以先导出到临时文件再导入
-- COPY "dbPlan"."tb_plan" TO '/tmp/tb_plan.tmp' WITH (FORMAT binary);
-- COPY "dbPlan"."tb_plan_next" FROM '/tmp/tb_plan.tmp' WITH (FORMAT binary);

4. 插入完成后批量创建索引与约束 #

先查询源表的所有索引、约束创建语句：

-- 查询所有索引创建语句
SELECT indexdef FROM pg_indexes WHERE schemaname = 'dbPlan' AND tablename = 'tb_plan';

-- 查询所有约束创建语句
SELECT pg_get_constraintdef(c.oid) 
FROM pg_constraint c 
JOIN pg_class t ON c.conrelid = t.oid 
JOIN pg_namespace n ON t.relnamespace = n.oid 
WHERE n.nspname = 'dbPlan' AND t.relname = 'tb_plan';

调大当前会话的维护内存加速索引创建，再执行上面查询到的创建语句即可：

-- 会话级调整，不影响其他业务，可根据服务器内存调整为1~8GB
SET maintenance_work_mem = '4GB';
-- 这里执行查询到的所有索引、约束创建语句

5. 恢复表属性并更新统计信息 #

如果之前开启了UNLOGGED属性，要改回正常模式：

ALTER TABLE "dbPlan"."tb_plan_next" SET LOGGED;

更新表统计信息，保证后续查询优化器生成正确的执行计划：

ANALYZE "dbPlan"."tb_plan_next";

额外优化参考 #

• 业务低峰期操作时可临时调大wal_buffers参数，减少WAL刷盘次数
• 允许短暂停服的场景可直接使用ALTER TABLE ... RENAME替换表结构，操作耗时为秒级
• 不要使用plpgsql循环逐行插入，性能会比批量导入低几十倍

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