PostgreSQL基于row_number()的千万级数据跨表迁移性能优化方案对比

嘿，针对你要把1000万条满足creation_date >= to_date('2000/01/01', 'YYYY/MM/DD')的customers数据快速并行插入clients表的需求，我来拆解两种基于row_number()的并行方案，以及对应的性能优化要点：

方案一：按row_number()取模拆分并行任务 #

这是最直观的拆分方式，给符合条件的所有行分配一个全局行号，然后按并行任务数取模，每个任务处理模值相同的行。

每个并行进程执行的SQL示例（假设开4个并行任务）：

-- 第1个并行任务
INSERT INTO clients (customer_id, creation_date, ...) -- 替换为实际需要的字段
SELECT customer_id, creation_date, ...
FROM (
    SELECT *, row_number() OVER () AS rn
    FROM customers
    WHERE creation_date >= to_date('2000/01/01', 'YYYY/MM/DD')
) t
WHERE rn % 4 = 0;

-- 第2个并行任务把WHERE条件改成 rn %4 =1，以此类推

方案一的优缺点 #

• 优点：实现简单，不需要依赖表的现有结构，直接用row_number()全局编号拆分
• 缺点：row_number() OVER ()会触发全表扫描后再执行全局排序，1000万行的排序会占用大量CPU和内存，甚至可能写入临时文件，严重拖慢整体速度

方案二：基于主键有序性的row_number()范围拆分（推荐） #

因为customer_id是主键，本身是自增/有序的，我们可以利用这个特性避免全局排序，让row_number()直接按主键顺序生成行号，大幅降低开销。

每个并行进程执行的SQL示例（假设拆成4个任务，每个任务处理250万行）：

-- 第1个任务：处理前250万行
INSERT INTO clients (customer_id, creation_date, ...)
SELECT customer_id, creation_date, ...
FROM (
    SELECT *, row_number() OVER (ORDER BY customer_id) AS rn
    FROM customers
    WHERE creation_date >= to_date('2000/01/01', 'YYYY/MM/DD')
) t
WHERE rn BETWEEN 1 AND 2500000;

-- 第2个任务：WHERE rn BETWEEN 2500001 AND 5000000，以此类推

方案二的核心优势 #

PostgreSQL会利用customer_id的主键索引，直接按有序顺序遍历符合条件的行，不需要额外的全局排序，row_number()可以在遍历过程中直接生成，CPU和内存开销会比方案一小很多，是千万级数据场景下的更优选择。

通用性能优化建议 #

不管用哪种方案，这些优化点都能帮你进一步缩短复制时间：

• 批量事务提交：每个并行任务用BEGIN; ... COMMIT;包裹，避免频繁的小事务开销
• 临时禁用索引/约束：插入前先禁用clients表的非主键索引、外键约束等，插入完成后再重建，因为每次插入维护索引会带来巨大的性能损耗
• 调整数据库配置：临时增大work_mem（给排序/哈希操作更多内存）、maintenance_work_mem（重建索引时用），适当调高max_parallel_workers_per_gather参数
• **避免SELECT ***：只插入需要的字段，减少数据传输和IO开销

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