PostgreSQL高效复制亿级行数据：如何优化批量重复插入操作？

针对数亿行规模的PostgreSQL表，单事务INSERT的效率瓶颈主要来自事务日志开销、磁盘IO压力以及单事务资源占用限制，以下是几个更高效的替代方案：

1. 分批次拆分插入 #

将全量INSERT拆分为多个小事务执行，避免单事务占用过多内存与IO资源，同时让数据库能及时释放中间资源：

-- 假设id为自增主键，每次处理100万行，可根据服务器性能调整批次大小
WITH batch AS (
    SELECT id, is_deleted FROM costs WHERE id BETWEEN 1 AND 1000000
)
INSERT INTO costs(parent_record, is_deleted)
SELECT id, is_deleted FROM batch;

-- 循环执行后续批次，比如id BETWEEN 1000001 AND 2000000，以此类推

可以用Shell或Python脚本自动生成并执行批次SQL，全程无需人工干预。

2. 用COPY替代INSERT #

COPY是PostgreSQL原生的批量数据操作工具，写入效率远高于普通INSERT，步骤如下：

1. 导出需要复制的数据到临时文件：

COPY (SELECT id, is_deleted FROM costs) TO '/tmp/costs_copy.csv' WITH (FORMAT csv, HEADER off);

2. 将临时文件数据导入目标表：

COPY costs(parent_record, is_deleted) FROM '/tmp/costs_copy.csv' WITH (FORMAT csv, HEADER off);

注意要确保PostgreSQL进程拥有该临时文件的读写权限，也可以通过管道方式在脚本中直接传递数据，避免落地文件。

3. 创建新表后替换原表（适合允许短暂离线的场景） #

如果业务可以接受短暂的表不可用，这个方法效率最高：

1. 直接创建包含原数据+复制行的新表：

CREATE UNLOGGED TABLE new_costs AS
SELECT * FROM costs
UNION ALL
SELECT id AS parent_record, is_deleted, [其他字段默认值] FROM costs;
-- 注意：需列出原表所有字段，确保新表结构与原表完全一致，非目标字段按需求设置默认值或对应值

2. 给新表重建原表的索引、约束：

CREATE INDEX idx_new_costs_parent ON new_costs(parent_record);
-- 原表的其他索引、外键、约束需同步重建

3. 原子替换原表：

BEGIN;
ALTER TABLE costs RENAME TO costs_old;
ALTER TABLE new_costs RENAME TO costs;
COMMIT;

CREATE TABLE AS直接写入数据文件，跳过了逐行INSERT的事务逻辑，效率提升非常明显。

额外优化建议 #

• 插入前删除非必要索引，插入完成后再批量重建：逐行插入时维护索引会产生大量额外开销，批量重建索引的效率远高于实时维护。
• 临时调整PostgreSQL配置：调高maintenance_work_mem、work_mem让数据库能使用更多内存处理数据；调大checkpoint_timeout避免频繁触发检查点拖慢写入。
• 确保磁盘IO性能：机械硬盘建议用RAID提升吞吐量，SSD则要避免IO队列阻塞。

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