COPY 与批量数据加载优化

把一个 CSV 的 1000 万行灌进 PostgreSQL，用逐行 INSERT 可能要跑几十分钟甚至几小时，换成 COPY 往往只需几十秒。这不是魔法，而是因为 COPY 走了一条专用通道：它跳过 SQL 解析与规划、绕开逐行的事务与 WAL 开销、以流式方式直接写堆表。数据迁移、ETL、日志归档、初始化测试数据——所有「大批量写入」的场景，COPY 都应该是首选。

但 COPY 只是起点。真正决定批量加载速度的是写放大：索引维护、WAL 记录、触发器、约束校验、synchronous_commit 的刷盘等待。把加载拆成「先关掉一切能关的、灌完再一次性重建」，速度差距可以再拉开一个数量级。本文从 COPY 的原理讲到参数调优与加载后维护，覆盖文件、管道、应用侧三种入口。

一、COPY 的三种形态

1.1 服务端文件

最基础的用法是从服务端文件加载（文件必须在数据库服务器上，且需要超级用户权限）：

COPY orders (id, tenant_id, amount, created_at)
FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"', NULL '');

COPY 的常用选项：

1.2 STDIN（管道）

客户端侧通过 psql 的 \copy（注意是反斜杠命令）加载本地文件：

psql -h db -U app -d shop \
  -c "\copy orders (id, tenant_id, amount) FROM '/local/orders.csv' CSV HEADER"

\copy 与服务端 COPY 的区别是数据流经客户端：\copy 在客户端读文件、通过协议发送，因此文件在客户端机器上，也不要求超级用户。代价是多一跳网络传输，但胜在权限与部署灵活，是生产中最常用的形式。

1.3 PROGRAM（外部命令）

COPY ... FROM PROGRAM 直接执行一个命令并把其标准输出当作输入流：

COPY orders FROM PROGRAM 'gzip -dc /data/orders.csv.gz'
WITH (FORMAT csv, HEADER true);

这省去了「先解压再加载」的中间步骤，对压缩归档文件很实用。等价地，客户端侧可以：

gzip -dc orders.csv.gz | psql -c "\copy orders FROM STDIN CSV HEADER"

导出方向的对称用法：

psql -c "\copy (SELECT * FROM orders WHERE created_at > '2026-01-01') TO STDOUT CSV HEADER" \
  | gzip > orders_2026.csv.gz

COPY (query) TO 支持任意 SELECT，是导出数据最灵活的方式。

1.4 二进制格式

FORMAT binary 跳过文本解析与类型转换，理论上最快，但它是平台相关的（依赖字节序与类型内部表示），且不能被其它工具直接读取：

COPY orders TO '/data/orders.bin' WITH (FORMAT binary);
COPY orders FROM '/data/orders.bin' WITH (FORMAT binary);

实践中，文本 csv 格式的加载速度已经足够快，瓶颈往往不在解析而在 WAL 与索引维护。只有在「同版本 PostgreSQL 之间搬数据」且解析确实成为瓶颈时，才值得用二进制格式。跨版本、跨平台搬运时不要用二进制格式，内部表示可能不兼容。

1.5 错误处理

默认情况下，COPY 遇到一行格式错误就整体失败、回滚整个加载。这在大文件导入时很痛苦——一条坏行就让几十分钟白跑。PG 16 起支持跳过坏行：

COPY orders FROM '/data/orders.csv' CSV HEADER
    ON_ERROR ignore
    LOG_VERBOSITY verbose;

被跳过的坏行会记录到服务器日志（LOG_VERBOSITY verbose 会带上行号与原始内容），加载完成后可以据此定位并修复。注意 ON_ERROR ignore 只跳过格式错误，唯一约束等完整性冲突仍会导致失败。

对于更复杂的情况，可以先把文件加载到一张 UNLOGGED 的暂存表（所有列都是 text），再用 SQL 做校验与转换：

CREATE UNLOGGED TABLE staging (line text);
COPY staging FROM '/data/orders.csv' CSV;
-- 逐列解析、过滤坏行、转换类型后插入正式表
INSERT INTO orders (id, tenant_id, amount)
SELECT (regexp_match(line, '^([^,]+),([^,]+),([^,]+)'))[1]::bigint, ...
FROM staging
WHERE line ~ '^\d+,\d+,[\d.]+$';

这个「先落地再清洗」的模式虽然慢一些，但容错性最好，适合来源不可控的外部数据。

二、COPY 为什么快

2.1 绕过 SQL 解析与规划

逐行 INSERT INTO t VALUES (...) 每一条都要经历「解析 → 重写 → 规划 → 执行」四阶段。规划阶段尤其昂贵——即使是简单的单行插入，规划器也要查系统表、评估索引。1000 万次 INSERT 就是 1000 万次规划。

COPY 只在开始时解析一次目标表结构，之后按二进制/文本流逐行写入，没有逐行规划开销。这一项就能带来数倍差距。

2.2 单事务、少往返

逐行 INSERT 若是自动提交模式，每一行都是一个独立事务：一次提交 = 一次 WAL 刷盘（fsync）。1000 万行 = 1000 万次 fsync，磁盘 IOPS 直接成为瓶颈。

COPY 默认在单个事务里完成（除非用 COPY 的 FREEZE 或分段），只有一次提交、一次刷盘。这一项通常带来数十倍差距。

2.3 简化的 WAL 记录

COPY 对 WAL 的优化体现在两方面：一是它知道自己在做纯插入，不需要记录「可能的更新」所需的额外信息；二是配合 wal_level = minimal + COPY 到「刚创建或 TRUNCATE 过的表」时，PostgreSQL 可以几乎不写 WAL，因为崩溃后该表会被直接丢弃重建。这一点是很多「批量加载加速技巧」的核心，WAL 的完整机制见 PostgreSQL WAL 与崩溃恢复原理 。

2.4 触发器的代价

如果目标表上有 BEFORE INSERT / AFTER INSERT 触发器（包括外键的约束触发器），COPY 也会逐行触发它们。一个写入审计日志的 AFTER 触发器，能把 COPY 的速度拉回 INSERT 的水平。批量加载前先禁用触发器是常规操作：

ALTER TABLE orders DISABLE TRIGGER ALL;
COPY orders FROM '/data/orders.csv' CSV HEADER;
ALTER TABLE orders ENABLE TRIGGER ALL;

DISABLE TRIGGER ALL 会连外键的约束触发器一起禁用，因此加载的数据必须自己保证引用完整性，加载后再启用并验证。

三、参数调优

3.1 事务与 WAL 参数

批量加载是「可重试、可重建」的操作，可以牺牲部分持久性换取速度：

BEGIN;
SET LOCAL synchronous_commit = off;
SET LOCAL maintenance_work_mem = '2GB';
SET LOCAL max_wal_size = '8GB';
COPY orders FROM '/data/orders.csv' CSV HEADER;
COMMIT;

synchronous_commit = off 只影响「崩溃后可能丢失最近一小段时间的事务」，不会造成数据损坏。对可重跑的数据导入，这是最划算的一步。

3.2 无日志表（UNLOGGED）

若数据是「可重建的中间结果」，把表建成 UNLOGGED 可以完全不写 WAL：

CREATE UNLOGGED TABLE orders_staging (LIKE orders INCLUDING ALL);
COPY orders_staging FROM '/data/orders.csv' CSV HEADER;
-- 校验后转为正式表
ALTER TABLE orders_staging SET LOGGED;

UNLOGGED 表不写 WAL，写入速度可提升数倍，但崩溃后表会被自动清空（truncate）。因此它只适合「导入暂存表」，加载完校验后再 SET LOGGED 转正——SET LOGGED 本身要写全表 WAL，会慢，但一次性完成。

3.3 索引与约束的处理顺序

这是批量加载最大的优化点。索引维护是逐行插入的主要成本：每插一行，所有索引都要更新。1000 万行 × 5 个索引 = 5000 万次索引更新。

正确做法是先删索引，灌完再重建：

-- 1. 记录现有索引定义
-- 2. 删除非主键索引
DROP INDEX idx_orders_tenant_created;
-- 3. 加载
COPY orders FROM '/data/orders.csv' CSV HEADER;
-- 4. 重建（可用并行 + 大 maintenance_work_mem）
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at);

外键与 CHECK 约束同理：可以先 ALTER TABLE ... DROP CONSTRAINT，加载后再 ADD CONSTRAINT，让约束一次性校验而不是逐行。NOT VALID 约束可以进一步把校验推迟：

ALTER TABLE orders ADD CONSTRAINT fk_tenant
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) NOT VALID;
-- 之后在低峰期一次性校验
ALTER TABLE orders VALIDATE CONSTRAINT fk_tenant;

四、COPY FREEZE

4.1 原理

正常插入的元组带有「事务 ID（xmin）」，需要靠 VACUUM 冻结（freeze）才能被后续的可见性判断安全处理。COPY FREEZE 在加载时直接把元组标记为「已冻结」，跳过未来的冻结开销：

COPY orders FROM '/data/orders.csv' CSV FREEZE;

它的前提条件很严格：

• 表必须是当前事务中创建或 TRUNCATE 过的。
• 必须是同一事务里的 COPY。
• 目标表上不能有索引（较新版本放宽了部分限制）。

BEGIN;
TRUNCATE orders;
COPY orders FROM '/data/orders.csv' CSV FREEZE;
COMMIT;

满足条件时，COPY FREEZE 能显著减少后续 VACUUM 的负担——因为这些行永远不需要冻结扫描。对「每次全量重灌」的场景（如每日快照表），这是很有效的优化。

4.2 加载后的维护

无论是否 FREEZE，批量加载后都要做三件事：

1. ANALYZE：更新统计信息，否则后续查询可能选错计划。

ANALYZE orders;

统计信息对计划质量的影响极大：一个刚灌完 1000 万行却未 ANALYZE 的表，规划器仍按旧的行数估算，可能给关键查询选错索引甚至走全表扫描。

2. VACUUM：加载本身不产生死元组，但如果加载过程中有并发更新，或使用了 ON CONFLICT，就需要清理。定期 VACUUM (ANALYZE) 是稳妥的。

3. 检查膨胀：如果加载方式是「先 DELETE 再 COPY」，会留下大量死元组，必须 VACUUM 回收，否则表体积膨胀、顺序扫描变慢。膨胀的判定与治理见 PostgreSQL VACUUM 与膨胀治理 。更彻底的替代是 TRUNCATE 而非 DELETE——TRUNCATE 直接重置文件，无死元组。

4.3 加载后校验

大批量加载必须校验完整性，不能假设文件没问题。三个基本校验：

-- 1. 行数是否与源文件一致
SELECT count(*) FROM orders;

-- 2. 是否有 NULL 落到 NOT NULL 列（若曾禁用约束）
SELECT count(*) FROM orders WHERE tenant_id IS NULL;

-- 3. 主键/唯一键是否重复
SELECT id, count(*) FROM orders GROUP BY id HAVING count(*) > 1;

对账的黄金标准是校验和：加载前后对同一列做 sum() 或 md5 聚合，与源系统比对。

SELECT count(*), sum(amount), md5(string_agg(id::text, ',' ORDER BY id))
FROM orders WHERE created_at >= '2026-01-01';

如果加载前 DISABLE TRIGGER ALL 禁用了外键，加载后务必单独校验引用完整性，或重新 ADD CONSTRAINT 让数据库替你校验一遍。

4.4 加速效果的量化

把各项优化叠加起来，效果通常是这样的（以 1000 万行、5 个索引的表为参考）：

这张表是数量级参考，实际倍率取决于硬件与数据形态，但优化项的排序在大多数场景下稳定：先删索引、再关 WAL、最后才考虑并行与二进制格式。

五、进阶方案

5.1 并行加载

单个 COPY 是单线程的。要突破单核写入瓶颈，把数据按分区键切成多份，并行 COPY 到不同分区：

# 按 tenant_id 范围切分文件后并行加载
for i in 0 1 2 3; do
  psql -c "\copy orders_tenant_$i FROM '/data/orders_$i.csv' CSV HEADER" &
done
wait

前提是表已按 tenant_id 分区，每个 COPY 只写自己的分区，互不冲突。这能把加载吞吐提升到接近磁盘或网络的极限。分区表的加载与维护手法见本专题的分区相关文章。

5.2 pg_bulkload

pg_bulkload 是一个第三方工具，绕开 PostgreSQL 的缓冲区与 WAL，直接以「加载器」模式写入数据文件，速度远超 COPY。代价是：

• 加载期间表必须离线（无法并发读写）。
• 不走 WAL，崩溃后需要重建。
• 安装与版本兼容性需要额外维护。

它适合「一次性全量初始化」的场景，不适合在线增量导入。

5.3 外部表与 file_fdw

对「反复加载同一批文件」的场景，file_fdw 可以把 CSV 当作外部表直接查询：

CREATE EXTENSION file_fdw;
CREATE SERVER csv_srv FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE orders_csv (
    id bigint, tenant_id int, amount numeric, created_at timestamptz
) SERVER csv_srv
OPTIONS (filename '/data/orders.csv', format 'csv', header 'true');

-- 直接插入正式表
INSERT INTO orders SELECT * FROM orders_csv;

它省去了 COPY 的显式调用，但仍会逐行走 SQL 执行器，速度不如 COPY。优势在于可以用 SQL 做预处理（过滤、转换、JOIN）后再入库。

六、应用侧批量写入

并非所有加载都能走 COPY——应用运行时往往要「批量插入一批对象」。这时有三种选择。

6.1 多值 INSERT

把多行合并成一条 INSERT：

INSERT INTO orders (id, tenant_id, amount) VALUES
    (1, 42, 100.00),
    (2, 42, 200.00),
    (3, 43, 300.00);

这减少了解析与规划次数，但单条 SQL 的参数量有上限（65535），且 VALUES 列表越长，规划时间越长（尤其带 ON CONFLICT 时）。经验值是每批 500~1000 行。

6.2 使用 COPY 协议

主流驱动都暴露了 COPY 接口。以 Node.js 的 pg 为例，pg-copy-streams 可以把可读流直接 COPY 进表：

import { pipeline } from 'node:stream/promises';
import { from as copyFrom } from 'pg-copy-streams';

const stream = client.query(
  copyFrom('COPY orders (id, tenant_id, amount) FROM STDIN CSV')
);
await pipeline(Readable.from(csvChunks), stream);

这是应用侧最快的路径，速度可达多值 INSERT 的数倍。ORM 层的批量写入封装可参考 Prisma 与 PostgreSQL 集成 ，它在底层同样会把批量操作转换为高效的多值语句或 COPY。

6.3 何时不该批量

批量写入不是无条件更好。以下情况应退回逐行：

• 需要每行的 RETURNING 结果（如返回自增 ID 并立刻使用）。
• 需要行级触发器的副作用（如发消息、写审计）。
• 单批数据里存在唯一键冲突且要精细处理冲突（ON CONFLICT 在超大批次上规划成本高）。
• 事务必须尽快可见（长事务会阻塞 VACUUM、放大膨胀）。

批量的本质是「用更长的单个事务换吞吐」，而长事务有代价。选择批次大小时，要在吞吐与事务长度之间取平衡，通常 500~5000 行是合理区间。

6.4 加载吞吐与整体调优

批量加载的性能是「磁盘 IO + WAL + 索引维护 + CPU」共同作用的结果。加载前后对照 pg_stat_io（PG 16+）可以看清瓶颈在哪：

SELECT backend_type, object, context,
       reads, writes, write_bytes
FROM pg_stat_io
ORDER BY write_bytes DESC;

若 writes 集中在 relation 而非 wal，说明瓶颈是表数据本身（可考虑 UNLOGGED）；若集中在 wal，则应优化 WAL 参数（wal_level、synchronous_commit、max_wal_size）。这类系统性调优的思路与 PostgreSQL 性能调优 中「先定位瓶颈再调参」的方法论一致。

小结

批量加载的核心是「减少每一行带来的固定开销」。COPY 相对逐行 INSERT 的优势来自跳过解析规划、单事务少刷盘、简化的 WAL。在此之上，把加载拆成「关索引/约束/触发器 → 高速灌入 → 重建索引并 ANALYZE」三段，能再拉开一个数量级。可重灌的数据用 UNLOGGED 暂存表、配合 COPY FREEZE 与调大的 maintenance_work_mem，是生产上的标准组合拳。应用侧的批量写入优先用驱动的 COPY 协议，其次是多值 INSERT，并注意长事务对 VACUUM 的副作用。最后别忘了加载后 ANALYZE——否则再快的加载也会因为统计信息陈旧而让查询变慢。