PostgreSQL中INSERT INTO ... SELECT与COPY FROM BINARY性能对比

我在日常帮团队处理PostgreSQL数千万到数亿行规模的数据迁移时，经常会对比这两种方案的性能表现，结合官方文档和生产环境的实测数据，给你拆解下核心的差异和影响因素：

核心性能差异 #

INSERT INTO ... SELECT #

• 这是数据库内部闭环操作，数据完全在PostgreSQL进程内流转，不需要经过客户端或网络IO，所以磁盘IO是最核心的瓶颈。如果源表和目标表在同一物理磁盘，读写操作会互相抢占带宽，拖慢整体速度；如果能把源、目标表部署在不同的存储卷（比如独立的SSD盘），性能能提升30%以上。
• 默认会逐行触发约束检查、触发器（如果目标表有定义），并且小批量写入WAL日志。对于超大规模数据，WAL的写入量会非常可观，除非临时调整wal_buffers、checkpoint_timeout等参数，或者暂时禁用目标表的触发器/非必要约束（操作前一定要做好数据校验）。
• 性能上限依赖于数据库的CPU、磁盘IO吞吐量，以及shared_buffers的缓存命中率——如果源表数据能大部分被缓存到内存，读取速度会大幅提升。

COPY FROM ... BINARY #

• 二进制COPY是PostgreSQL里批量写入效率最高的方式之一，它的格式比文本COPY更紧凑，几乎没有解析开销，能把数据写入的CPU占用降到最低。
• 如果用COPY TO BINARY通过管道直接传输到COPY FROM BINARY（比如psql里的管道命令，或者程序里的流处理），数据不需要落地到临时文件，只在客户端和数据库进程间做轻量的进程通信，这个开销相比磁盘IO可以忽略。
• 默认不触发触发器（除非显式指定TRIGGERS选项），并且采用批量WAL写入模式，单批次的WAL写入效率远高于INSERT ... SELECT。另外，还可以加上FREEZE选项，直接把导入的数据标记为“冻结”状态，跳过后续的VACUUM freeze操作，能节省大量后续的维护资源。
• 如果是从本地文件导入，客户端的磁盘读取速度或网络带宽（跨机场景）会成为瓶颈，但同机管道传输时这个问题完全不存在。

容易忽略的关键影响因素 #

• 事务规模控制：INSERT INTO ... SELECT默认会在单个事务中完成全量导入，数据量过大时会导致事务快照占用大量内存，甚至触发OOM。虽然可以用LIMIT+OFFSET分批次执行，但逻辑复杂且性能损耗大；而COPY可以很方便地分批次导入并提交，避免大事务的问题。
• 索引维护成本：如果目标表已经创建了索引，两种操作都会实时更新索引，但COPY的批量更新效率更高。如果能先删除目标表的索引，导入完成后再重建，性能能提升几个数量级——这对两种方案都适用，但COPY在带索引的场景下表现依然优于INSERT ... SELECT。
• 并行处理潜力：PostgreSQL 12+支持INSERT INTO ... SELECT的并行扫描，但并行度受限于max_parallel_workers_per_gather等参数；而COPY本身是单进程的，但可以通过多进程同时导入不同的数据分片（比如分区表的不同分区），充分利用多核CPU的优势，把磁盘IO的利用率拉满。
• WAL日志优化：临时将目标表设置为UNLOGGED（如果不需要强持久性，比如迁移完成后再转为普通表），两种方案都能跳过WAL写入，性能提升非常明显。但COPY本身的WAL写入效率就比INSERT ... SELECT高，即便不使用UNLOGGED表，差距依然存在。

总结建议 #

• 如果源表和目标表在同一个PostgreSQL实例，且能优化磁盘布局、调整WAL参数、临时禁用触发器/索引，INSERT INTO ... SELECT的性能可以满足需求，但上限不如COPY FROM BINARY。
• 如果是跨实例迁移，或者需要最大化导入速度，COPY FROM BINARY（配合管道传输或分片导入）是首选，尤其是加上FREEZE选项和分批次提交，能把性能推到接近磁盘IO的极限。
• 实际落地前，一定要先做小批量测试，对比两种方案的耗时、CPU/磁盘/WAL的使用率，再结合你的存储配置、CPU核心数、数据量等实际情况选择最优方案。

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