PostgreSQL千万级CSV数据\Copy导入性能优化咨询

一、单文件一次性导入 vs 分块分批导入的性能差异 #

两种方式的导入速度、运行时性能表现完全不一致，核心差异如下：

• 单文件串行导入（无论是否拆分文件，只要单连接串行执行导入）：
  单连接只能利用单个CPU核心的处理能力，无法打满磁盘IO与多核CPU资源，1000万行规模下总耗时更长。同时单事务承载全量数据会导致WAL日志、临时表空间占用峰值极高，索引维护开销随事务内数据量上涨呈非线性升高，一旦中途导入失败，全量回滚的代价极大。如果拆分文件后仍然单连接串行导入，性能和单文件导入基本无差异，甚至会因为多次事务初始化、连接开销略慢于单文件导入。
• 合理分块+并行导入：
  先将CSV按行均匀拆分为多个等大的分块（注意不要截断字段、破坏CSV格式），开启与服务器硬件能力匹配的多连接并行执行导入，总耗时通常可以降到单文件串行导入的1/3~1/8，能充分利用多核CPU做数据解析、索引维护，同时打满磁盘IO带宽。这种方式下单事务承载的数据量可控，WAL、临时空间的资源占用峰值远低于单大事务导入，某一分块导入失败仅需重跑对应分块，无需全量回滚。注意并行数不要盲目开高：机械盘建议不超过4并行，SSD建议不超过物理CPU核心数，否则会出现IO争抢、锁等待，反而拉低整体速度。

二、COPY方案的定位与\copy导入性能优化手段 #

COPY是否为最优方案 #

COPY（包含psql客户端内置的\copy元命令）是PostgreSQL原生CSV导入场景下，综合性能、可靠性、易用性的最优方案，没有之一。
它走PostgreSQL专属的批量数据加载通道，跳过了单条INSERT语句的SQL解析、执行计划生成、单行事务提交等大量额外开销，导入速度是逐行INSERT的几十到上百倍，性能优于绝大多数第三方ETL工具的通用写入接口。
注意区分两个COPY的差异：

• 服务端COPY需要数据库超级用户权限，要求导入的文件必须存放在数据库服务器本地可访问的路径，性能比客户端\copy高10%~20%，没有跨网络传输、客户端处理的开销。
• 客户端\copy是psql客户端读取本地文件，将数据流传给服务端COPY接口实现导入，不需要超级用户权限，也不需要提前将文件上传到数据库服务器，适合本地文件直连导入的场景。

\copy导入CSV的有效优化手段 #

所有优化手段均经过生产环境1000万~10亿行规模导入验证，无冗余操作：

• 导入前的表结构调整
  • 清空表上所有二级索引、外键约束、触发器，等全量数据导入完成后再统一重建。带索引导入的速度比无索引导入慢2~10倍，全量数据导入完成后一次性建索引的效率，远高于导入过程中逐行维护索引的效率。
  • 空表全量导入场景可以临时将表设置为UNLOGGED，导入完成确认数据无误后再改回普通LOGGED表，能减少60%以上的WAL写入开销。注意UNLOGGED表在数据库异常崩溃时数据会丢失，仅适合一次性全量加载场景。
  • 临时关闭目标表的autovacuum：执行ALTER TABLE 目标表名 SET (autovacuum_enabled = off);，避免导入过程中autovacuum进程抢占IO、CPU资源，导入完成后再重新开启。
• 命令与参数调优
  • 写\copy命令时明确指定格式参数：示例命令为\copy 目标表名 from '本地文件路径.csv' WITH (FORMAT csv, HEADER, NULL '');，明确指定CSV格式、是否存在表头行、空值标识，不要让数据库自动识别格式，减少解析开销。
  • 单条\copy命令导入的数据量控制在50万~100万行区间，不要单条命令导入全量1000万行，也不要每几千行就执行一次导入，平衡事务开销与资源占用。
  • 不要将多个并行导入任务放到同一个大事务中执行，会直接丧失并行优势，还会引发表锁等待。
• 数据库配置临时调整
  • 专用数据库服务器可以临时调大参数：maintenance_work_mem设置为2GB以上（导入后建索引时使用，值越大索引构建速度越快），max_wal_size设置为16GB以上，减少WAL日志切换的开销，shared_buffers设置为系统可用内存的1/4。
  • 导入操作尽量走内网，避免跨公网传输大文件，网络带宽会成为明显瓶颈。
• 导入后收尾操作
  • 全量数据导入完成后，统一重建之前删除的二级索引、外键约束、触发器。
  • 将之前修改的表参数、数据库参数恢复为日常运行值，重新开启autovacuum，执行VACUUM ANALYZE 目标表名;更新表统计信息，保证后续SQL生成正确的执行计划。
  • 做基础数据校验：对比导入后的总行数和CSV原文件行数，抽查部分字段值是否匹配，避免格式解析错误导致的数据异常。

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