27 快速加载数据

用 COPY、FREEZE 和延后建立的索引，在几秒内加载数百万行；并按自己的计划承担 WAL、检查点、提示位和统计信息的代价。

一个跑一整夜的导入，和一个在咖啡变凉前就完成的导入，可以把同样的一百万行加载进同一张表。区别在于每一行还拖着什么：一次提交、一次往返通信、一次语句解析、每个索引中的一项、一次外键查找。去掉这些工作后，Postgres 加载行的速度几乎和写入页的速度一样快。

然后账单就来了。大批量加载写入的 WAL 比它填满的表还大，这些日志会触发检查点，并落到每个副本上。行还没有完成，因此第一次读取它们的查询会向每一页写入。规划器也不知道表发生了变化。

所以，加载时要让每行做尽可能少的工作，然后在用户发现之前，按照自己的意图主动支付延期的代价。

加载速度从每秒 1,200 行到 50 万行

我们把结构相同的事件行加载进 events 的新副本，测试了十种方式；每种方式都在一台使用默认设置的独立服务器上运行，并统计每秒行数和 WAL。WAL 是每次变更在到达数据文件之前都要写入的日志（先记日志，再写数据）。两种单行方式加载 1,000 行，因为一百万行会耗时数小时；其余方式加载 1,000,000 行：

                  method                   |  rows   | rows_per_s |  wal   | wal/row
-------------------------------------------+---------+------------+--------+---------
 1. single-row INSERT, autocommit          |    1000 |       1187 | 305 kB |     312
 2. single-row INSERT, one transaction     |    1000 |      17224 | 273 kB |     279
 3. multi-row VALUES, 1,000 rows each      | 1000000 |      79735 | 228 MB |     239
 4. INSERT ... SELECT from unnest          | 1000000 |      84872 | 228 MB |     239
 5. COPY                                   | 1000000 |     507966 | 184 MB |     193
 6. COPY FREEZE                            | 1000000 |     493756 | 185 MB |     194
 7. COPY, table created in the transaction | 1000000 |     525823 | 184 MB |     193
 8a. COPY into an unlogged table           | 1000000 |     468208 | 34 kB  |       0
 8b. ... then ALTER TABLE SET LOGGED       | 1000000 |     959712 | 181 MB |     190
 9. COPY with 3 indexes and 3 FKs in place | 1000000 |      64047 | 346 MB |     363
 10. COPY bare, then indexes, then FKs     | 1000000 |     431132 | 174 MB |     182

从每秒约 1,200 行到 50 万行。表格每向下走一步，就去掉一种逐行开销；本章剩下的内容会一次比较两行。（第 8b 行的速率是重写表的速度，不是加载速度。）

把这些时间当作大致值。它们来自一次安静运行；如果有十几个其他工作负载共享这台笔记本，有些方式会慢好几倍。WAL 的数值始终没有变化：每次相差都不到 1 KB。

深入阅读：十种加载方式是如何测量的

每台服务器都设置了 wal_level = replica、max_wal_size = 1GB 和数据校验和；检查点之后，WAL 通过 pg_stat_get_backend_wal()（PostgreSQL 18）按会话统计。每张一百万行的表最终都是 137 到 138 MB。我们的多次运行中，COPY 的速率为每秒 125,000 到 508,000 行，而自动提交为每秒 15 到约 1,200 行，因此实验只检查比例，从不比较时间。

每批提交一次，而不是每行提交一次

第 1 行和第 2 行是相同的 1,000 次插入。唯一的区别是在它们外面加了一次 begin 和一次 commit。在这里，这让加载速度达到原来的约 15 倍；在更繁忙的运行中，所有刷新都要排在其他写入后面，速度则达到原来的 100 多倍。

自动提交的 INSERT 自己就是一个事务，每次提交都要等待它的 WAL 到达磁盘（提交意味着刷新）。提交记录很小，等待才是每行的代价。

整个加载只使用一个事务，也有自己的问题：第 999,999 行失败会回滚全部工作，运行期间还会阻碍清理。向线上表加载时，每几千行到几十万行提交一次，就像回填那样。向空表加载时，一个事务就够了，也能解锁下面最快的路径。

深入阅读：每行多一条记录，以及等待刷新

单行插入会写两条 WAL 记录：堆和主键；自动提交还会增加一条提交记录。500 行的结果是：12,884 ms 内写入 1,521 条记录，而另一种方式在 88 ms 内写入 1,021 条。多出的 500 条记录只多出 17 KB WAL，而繁忙笔记本运行 Docker 时，12.9 秒几乎全部耗在刷新上。优秀的 NVMe 磁盘刷新耗时远低于 1 毫秒；这种规律仍然成立。

动手验证 · lab 26-loading

select lab.prove(
  'autocommit writes one extra WAL record per row: its commit',
  (select records from lab.runs where method = 'single-row, autocommit')
  - (select records from lab.runs where method = 'single-row, one transaction') between 490 and 600);
select lab.prove(
  'the same 500 inserts in one transaction run at least 1.5 times as fast as autocommit',
  lab.rate('single-row, one transaction') > 1.5 * lab.rate('single-row, autocommit'));

完整实验脚本

用数组批处理，而不是 VALUES 列表

提交开销去掉后，每行的代价就是语句本身：一次往返通信、一次解析、一次规划。批量发送 1,000 行，这个代价就由 1,000 行共同承担。但如何批处理仍然重要。

第 3 行和第 4 行都在每条语句中发送 1,000 行，并写入相同的 WAL。在这里，unnest 只快了 6%；在更繁忙的运行中，最多快到 9 倍。

差别在解析。多行字面量 VALUES 列表是 ORM 通常使用的“批量插入”形式；每批 1,000 行就是 6,000 个文本值，Postgres 必须为每条语句从头解析它们，而 CPU 紧张时最先受影响的就是解析。INSERT ... SELECT * FROM unnest($1, $2, ...) 为每列发送一个数组：无论批次大小如何都只有六个参数，而且只需准备一次。

如果驱动程序可以绑定数组，就使用 unnest。两种形式都不能节省磁盘：每行都会写一条堆记录和一条索引记录。

深入阅读：为什么 VALUES 列表会到达上限

预先准备的 unnest 形式：

prepare load_events
  (bigint[], bigint[], bigint[], text[], jsonb[], timestamptz[]) as
  insert into events (account_id, project_id, user_id, kind, payload, created_at)
  select * from unnest($1, $2, $3, $4, $5, $6);

作为参数绑定时，VALUES 列表每 1,000 行需要 6,000 个占位符，而且每种批次大小都需要新的语句文本；协议把一条语句限制在 65,535 个参数以内。作为字面量时，它在我们的运行中从未比 unnest 更快：安静服务器上大致相当，繁忙服务器上则慢到 9 倍。

动手验证 · lab 26-loading

select lab.prove(
  'unnest loads at least as many rows per second as multi-row VALUES of literals (within 10%)',
  lab.rate('INSERT ... SELECT from unnest') > 0.9 * lab.rate('multi-row VALUES, 1,000 per statement'));
select lab.prove(
  'unnest loads at least twice as many rows per second as single-row inserts in one transaction',
  lab.rate('INSERT ... SELECT from unnest') > 2 * lab.rate('single-row, one transaction'));

完整实验脚本

COPY 是批量加载路径

COPY ... FROM STDIN 可以用文本、CSV 或二进制这样的简单格式流式发送行，完全不需要每行一条语句。它的写入方式也不同：它会缓冲行，一次填满一页堆，而不是每行写一条堆 WAL 记录，每页只写一条堆 WAL 记录（但每条索引项仍然按行记录）。读取 wal/row 列：每种 INSERT 形式都是 239 字节，COPY 是 193 字节。第 5 行的加载速度则是 unnest 的好几倍。

使用客户端一侧的方式：COPY table FROM '/path' 会读取数据库服务器上的文件，需要特殊权限；而 psql 的 \copy 会读取你的机器上的文件，并以 COPY FROM STDIN 的形式发送：

\copy events_import from 'events.csv' with (format csv)

所有成熟的驱动程序都支持 copy 协议。在 Go 中，pgx 的 CopyFrom 发送二进制 COPY；在 Node 中，pg-copy-streams 会把任意流接入管道。

深入阅读：是什么关闭了 COPY 逐页写入的路径

在 100,000 行上，VALUES 和 unnest 各自写入约 203,000 条 WAL 记录（每行 2 条）；COPY 写入 105,243 条：每条主键项一条，再加上每个堆页一条；字节数少了 19%。

动手验证 · lab 26-loading

select lab.prove(
  'COPY writes far fewer WAL records than the 2 per row an INSERT writes (heap + primary key)',
  (select records from lab.runs where method = 'COPY FROM STDIN')
  < 0.7 * (select records from lab.runs where method = 'multi-row VALUES, 1,000 per statement'));
select lab.prove(
  'COPY writes less WAL than row-at-a-time inserts: one heap record per page, not per row',
  lab.wal('COPY FROM STDIN') < lab.wal('multi-row VALUES, 1,000 per statement'));

完整实验脚本

有三件事会让 COPY 退回到逐行处理。第一，BEFORE 或 INSTEAD OF 行触发器，因为每行都必须先通过它，才能处理下一行。第二，易变的默认表达式，因为它可能读取正在加载的表（标识列和 serial 列背后的 nextval() 是源码中特别放行的唯一例外）。第三，带有易变函数的 WHERE 子句位于 COPY 上。AFTER 触发器，包括外键背后的触发器，不会停止批处理，但每行仍然会排队一个事件。

COPY FREEZE 会写入已完成的行

普通 COPY 加载的行还没有完成。它的行头记录了加载事务，因此第一次读取时，系统会检查该事务是否已提交，并把答案保存到行的提示位中（读取也可能写入）。这会弄脏页面；而在开启数据校验和时（PostgreSQL 18 的默认设置），还会记录一张整页镜像。检查点之后，对刚刚复制的 100,000 行执行第一次 count(*)：

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1816 dirtied=1759
   WAL: records=1759 fpi=1759 bytes=14404959 buffers full=1253

只读查询弄脏了存放这些行的全部 1,759 页，并写入 14.4 MB WAL，而表只有 14 MB。如果这是用户的查询，他们就得等待。之后，清理还必须在事务 ID 计数器回卷之前，回来冻结每一行，将它们永久标记为对所有事务可见。

COPY ... WITH (FREEZE) 会写入已经冻结的行；从 PostgreSQL 14 开始，它还会把每一页标记为全可见。条件是：表必须在同一个事务中创建或截断，这样其他人不可能已经看到它：

begin;
truncate events_import;
\copy events_import from 'events.csv' with (format csv, freeze)
commit;

跳过 truncate 后，Postgres 会拒绝执行。对冻结表执行相同的 count(*) 什么也不会弄脏，即使是第一次执行也如此；第 6 行的 WAL 代价与第 5 行相同。如果可以使用 FREEZE 加载，就使用它。

动手验证 · lab 26-loading

select count(distinct (ctid::text::point)[0]) as copy_pages from l_copy \gset
select lab.prove(
  'the first scan after a plain COPY dirties every page that holds rows (hint bits)',
  (:'first_scan'::jsonb -> 'Plan' ->> 'Shared Dirtied Blocks')::bigint = :copy_pages);
select lab.prove(
  'after a checkpoint, with checksums on, that read also writes a full-page image of each of those pages',
  (:'first_scan'::jsonb -> 'Plan' ->> 'WAL FPI')::bigint = :copy_pages);
select lab.prove(
  'the first scan after COPY FREEZE dirties nothing: the rows were written frozen',
  coalesce((lab.plan('select count(*) from l_freeze') -> 'Plan' ->> 'Shared Dirtied Blocks')::bigint, 0) = 0);

完整实验脚本

深入阅读：FREEZE 标记了什么，以及唯一需要诚实面对的例外

pg_visibility_map_summary('l_freeze') 报告 1,759 页全可见、1,759 页全冻结：也就是所有存放着行的页。（文件共有 1,816 页；COPY 每次扩展表几页，空页没有东西可以标记。）WAL 为 19.4 MB，而普通 COPY 为 19.3 MB。如果没有 truncate，错误就是 cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction。

文档指出了这个例外：冻结行对每个快照都可见，因此一个在加载前就已开始、并在加载事务提交后读取该表的事务，会看到新行，而正常 MVCC 本应把它们隐藏起来。对于在加载完成前没有人读取的表，这无关紧要。

动手验证 · lab 26-loading

select lab.prove(
  'after COPY FREEZE every page holding rows is all-visible and all-frozen in the visibility map',
  (select all_visible = (select count(distinct (ctid::text::point)[0]) from l_freeze)
      and all_frozen = all_visible
   from pg_visibility_map_summary('l_freeze')));
select lab.prove(
  'COPY FREEZE costs about the same WAL as a plain COPY (within 25%)',
  lab.wal('COPY FREEZE') < 1.25 * lab.wal('COPY FROM STDIN'));

完整实验脚本

先使用不记日志表，再 SET LOGGED，只是把 WAL 开销后移

一个常见技巧是：创建unlogged表，不写 WAL 地加载它，然后执行 alter table ... set logged。第 8a 和第 8b 行：加载只写入 34 KB，然后 SET LOGGED 写入 181 MB，而普通 COPY 写入 184 MB。

这条绕路只是把代价推后，并没有消除它。SET LOGGED 会重写表并记录每一行，因为副本或恢复操作没有其他数据记录。它执行时会持有 ACCESS EXCLUSIVE 锁，而在转换完成之前如果崩溃，表会被清空。

不记日志表适合用作不会转为正式表的暂存表：加载原始数据，用 SQL 清洗，把结果插入真正的表，再删除暂存表。只有最后一次插入承担 WAL（能重新生成的数据可以跳过 WAL）。

深入阅读：SET LOGGED 会先写表，再写索引

在 100,000 行上，SET LOGGED 写入 18 MB：先为行写入 100,190 条记录，再为重建的索引写日志。这个大小大约等于表加索引的大小，接近普通 COPY 写入的 19.3 MB。

动手验证 · lab 26-loading

select lab.prove(
  'COPY into an unlogged table writes almost no WAL (under 1% of a logged COPY)',
  lab.wal('COPY into an unlogged table') < 0.01 * lab.wal('COPY FROM STDIN'));
select lab.prove(
  'SET LOGGED rewrites the table into a new file',
  pg_relation_filenode('l_unlogged') <> :filenode_before);
select lab.prove(
  'SET LOGGED writes WAL about the size of the table plus its index (0.7x to 1.3x)',
  (select wal between 0.7 * (heap + idx) and 1.3 * (heap + idx)
   from lab.runs where method = 'ALTER TABLE ... SET LOGGED'));

完整实验脚本

wal_level = minimal 会为新表跳过 WAL

第 7 行是常见加载建议中的陷阱。“在同一个事务中创建表并执行 COPY”什么也没有节省：184 MB，与第 5 行相同。

这个技巧只有在 wal_level = minimal 下才有效；此时，创建或截断表并加载数据的事务会在提交时对新文件执行 fsync，而不是记录这些行。在这样启动的服务器上，相同的加载只写入 38 KB，而不是 184 MB；在该服务器上，它的速度约为向已有表执行 COPY 的三倍。

除此之外，它的代价都很高：开启和关闭都需要重启，没有副本，没有归档，也就没有时间点恢复。它只适合一种工作：在任何对象依赖它之前，从头构建数据库，然后获取一份新的基础备份。托管数据库通常不允许设置它。

深入阅读：最小 WAL 运行，以及没有丢失数据的崩溃

第二台服务器运行 wal_level=minimal 和 max_wal_senders=0（否则无法启动）。在那里，向事务开始前就已存在的表执行 COPY 仍然写入完整的 184 MB（每秒 109,841 行）；在事务中创建表时写入 38 KB（每秒 352,487 行）；在事务中截断表时写入 2,939 KB。自 PostgreSQL 13 起，跳过范围覆盖向新表或已截断表执行的任何写入，而不只是 COPY；写入量低于 wal_skip_threshold（默认 2 MB）时会在提交时记录 WAL，这比 fsync 更便宜。实验随后用 SIGKILL 杀掉服务器并让崩溃恢复运行：两次加载都完整无缺，因为提交时的 fsync 已经让文件持久化，而无需日志。

动手验证 · lab 26-loading.sh

prove "created in the same transaction: under 1% of that WAL" \
  mholds "select (select wal from runs where method = 'minimal: CREATE TABLE + COPY in one transaction')
                < 0.01 * (select wal from runs where method = 'minimal: COPY into an existing table')"
prove "after SIGKILL and crash recovery, both WAL-skipping loads are intact: 1,000,000 rows each" \
  mholds "select (select count(*) from m_new) = 1000000 and (select count(*) from m_existing) = 1000000"

完整实验脚本

加载之后再建立索引和外键

第 9 行和第 10 行把相同的行加载进同一张表，表上有一个主键、两个二级索引和三个外键。索引与约束已经存在时，速度是每秒 64,047 行，WAL 为 346 MB；全部留到之后添加时，速度是每秒 431,132 行，WAL 为 174 MB。在我们的多次运行中，速度达到原来的 6 到 7 倍，WAL 减半，而且索引小了 10 MB。

加载期间，每行都会为每个索引写一条 WAL 记录，并为每个外键执行一次查找。之后再建立时，索引只需对键排序一次，然后按页写入。在建立索引的会话中给这次排序足够的内存（set maintenance_work_mem = '1GB'；它按操作计算，因此会话级设置是安全的）。pg_restore 自己就是按这个顺序工作的。

这适用于空表和暂存表。线上表要保留索引，并分批加载。

外键：先 NOT VALID，再 VALIDATE

外键会为每一条加载的行检查父表，并在语句结束前为每行排队一个触发器事件；文档警告，在加载数百万行时，这个队列可能耗尽内存。

像迁移那样分两步添加：NOT VALID 会在不检查旧行的情况下对新行强制执行；然后 VALIDATE CONSTRAINT 通过一次连接运算检查旧行，并在允许写入继续进行的锁下运行。验证是真正的检查：实验中，它发现了约束建立前埋入的一条孤儿行；在删除这条孤儿行之前，约束一直保持未验证状态。

深入阅读：为什么逐行增长的索引会更大

在 100,000 行上，索引已存在时要写入 348,851 条 WAL 记录；之后建立索引时只有 5,194 条。（时间差会在不同运行中变化；早期有一次两种方式之间没有检查点，裸加载反而输了。）

行按 created_at 顺序到达，但 (project_id, created_at) 会把它们分散到索引各处，因此叶页会从中间分裂，最后部分为空。构建索引时会先排序，并按填充因子（fillfactor）填充（B-树叶页为 90%）：构建后的大小是 3,104 KB，而逐行增长的是 4,328 KB；增长出来的索引会一直更大，直到 REINDEX。每个索引都是写入税测量了每个索引的代价。

CREATE INDEX 会在 maintenance_work_mem 中排序（默认 64 MB），并与最多 max_parallel_maintenance_workers 个工作进程共享内存（默认 2）；超过内存后会溢写到临时文件。实验中的构建在 1 MB 时发生溢写，在 256 MB 时没有。让全服务器值保持适度；每个 autovacuum 工作进程也可能占用这么多内存，除非另有 autovacuum_work_mem 设置。

动手验证 · lab 26-loading

select lab.prove(
  'with indexes in place, every row writes a WAL record per index; built afterwards, under a tenth as many records',
  (select records from lab.runs where method = 'indexes and FKs present during COPY') > 300000
  and (select records from lab.runs where method = 'COPY, then indexes, then FKs NOT VALID + VALIDATE')
      < 0.1 * (select records from lab.runs where method = 'indexes and FKs present during COPY'));
select lab.prove(
  'the (project_id, created_at) index is at least 15% bigger when it grew row by row than when built after',
  pg_relation_size('l_before_project_created_idx') > 1.15 * pg_relation_size('l_after_project_created_idx'));
select lab.prove(
  'with maintenance_work_mem = 1MB the index build spills its sort to temporary files',
  :tf1 > :tf0);
select lab.prove(
  'with 256MB the same build sorts in memory: no temporary files',
  :tf2 = :tf1);
select lab.prove(
  'VALIDATE finds the old orphan (23503) and the constraint stays not validated',
  (select state = '23503' from lab.errs where test = 'validate finds orphan')
  and (select not convalidated from pg_constraint where conname = 'l_after_project_fk'));

完整实验脚本

大批量加载会触发检查点并淹没副本

写入数 GB WAL 的加载不会孤立发生。

检查点会提前到来。 超过 max_wal_size（默认 1 GB）的 WAL 会触发检查点（检查点为恢复设定上限）。上面最重的加载为一百万行写入了 346 MB，因此一千万行会超过这个限额三次。每个提前的检查点都会和加载争用磁盘，并让对每一页的下一次变更再次记录整页镜像（检查点后的第一次变化会记录整页）。计划加载之前，提高 max_wal_size（只需 reload，不需重启），确保 pg_wal 所在磁盘有空间，完成后再改回去。

每个副本都会收到每个字节，并由单个进程重放（重放只有一个进程）。减少 WAL（不带索引和约束加载、在不记日志表中暂存、开启wal_compression），分批加载，并在副本延迟超过预算时暂停。逻辑订阅端逐行应用加载，因此延迟会更久。

深入阅读：两个强制检查点让加载付出什么

将同样的 500,000 行加载进有三个索引的表，分别把 max_wal_size 设为 128 MB 和 16 GB：

        method        |  ms  |  wal   | full_page_images | requested_checkpoints
----------------------+------+--------+------------------+-----------------------
 max_wal_size = 128MB | 7910 | 196 MB |              999 |                     2
 max_wal_size = 16GB  | 6678 | 190 MB |                3 |                     0

两个强制检查点，产生 999 张整页镜像，而不是 3 张。这里的差额只有 6 MB，是因为加载主要写入新页，无论如何都会完整记录；向带有已有索引的大表加载时，每次检查点后都会为数千张散布的旧页重新生成镜像。开启 log_checkpoints 后（PostgreSQL 15 起默认为开启），日志会注明哪些检查点是因 WAL 量而触发的。

长时间运行的 COPY 会在 pg_stat_progress_copy 中报告进度（PostgreSQL 14+）；跟踪 tuples_processed，因为 bytes_total 对于 STDIN 为 0。

动手验证 · lab 26-loading.sh

prove "with max_wal_size = 128MB the load forced at least one checkpoint" \
  holds "select checkpoints >= 1 from runs where method = 'max_wal_size = 128MB'"
prove "the extra checkpoints cost full-page images: at least 5x as many as the run without them" \
  holds "select (select fpi from runs where method = 'max_wal_size = 128MB')
                > 5 * ((select fpi from runs where method = 'max_wal_size = 16GB') + 100)"

完整实验脚本

开放入口前先 vacuum 和 analyze

加载完成后，立刻自己支付延期的代价：

vacuum (freeze, analyze) events_import;

一次处理会设置提示位，冻结每一行，使防回卷清理不必再次访问这些页（清理的冻结步骤），把页标记为全可见以便仅索引扫描工作，并收集统计信息。autovacuum 最终也会到达那里，但会受到限流，并按自己的计划运行；而此时用户已经在查询了。对于 COPY FREEZE 表，没有行需要再冻结，同一条命令几乎不写任何东西。

不要跳过 analyze。从未分析过的表没有列统计信息，因此规划器只能猜测。问它刚加载的 100,000 个事件中有多少是告警时，它估计为 554；真实数量是 12,621，约为估计值的 23 倍，这样的猜测会选择嵌套循环，而应该使用哈希连接。执行 ANALYZE 后，估算是 12,550。把它作为每次加载的最后一步（三种变化会绕过 autovacuum 的 analyze）。

深入阅读：向已有表加载时，统计信息会以另一种方式过时

有统计信息的表也不安全：向其中大量加载后，在再次分析之前，统计信息描述的仍然是加载前的表。autovacuum 会在表的 10% 发生变化时分析它（autovacuum_analyze_scale_factor，再加 50 行），因此向大表增加 5% 的数据可能永远不会触发它。而你刚加载的行通常是最新的，也是查询最常请求的行，却位于规划器仍持有的直方图末端之外。每次大批量加载后都要分析，不只是向空表加载时才这样做。

动手验证 · lab 26-loading

select lab.prove(
  'the COPY FREEZE table has nothing left to freeze: under a twentieth of that WAL',
  :vac_frozen_wal < :vac_plain_wal / 20);
select lab.prove(
  'never analyzed, the planner guesses alerts at under a fifth of the real count',
  :est_before < :alerts / 5.0);
select lab.prove(
  'after ANALYZE the estimate is within 20% of the real count',
  abs(:est_after - :alerts) < 0.2 * :alerts);

完整实验脚本

一个坏值会让整个 COPY 失败

默认情况下，任何位置出现一个格式错误的值，都会让整个 COPY 失败，并且不会加载任何内容。PostgreSQL 17 增加了 ON_ERROR ignore，会跳过值无法转换为列类型的行；PostgreSQL 18 增加了 REJECT_LIMIT，因此“跳过几行”不会悄悄变成“跳过一半文件”。只有数据类型错误会被跳过：重复键或约束失败仍然会让全部操作停止。

对于更脏的数据，可以用 COPY 把数据导入一个由 text 列组成的暂存表，它什么都接受；然后用一条 INSERT ... SELECT 把通过检查的行写入目标表。

深入阅读：ON_ERROR 会跳过什么，以及什么仍会停止加载

给定四行数据，其中两行有坏整数，on_error ignore 会加载另外两行；设置 log_verbosity verbose 后，每跳过一行都会打印一条通知。设置 reject_limit 1 后，第二个坏行会让命令失败（skipped more than REJECT_LIMIT (1) rows due to data type incompatibility），并且不会加载任何内容。

动手验证 · lab 26-loading

select lab.prove('REJECT_LIMIT 1 with two bad rows fails the COPY, and nothing is loaded',
  (select msg like 'skipped more than REJECT_LIMIT (1) rows%' from lab.errs where test = 'reject_limit 1')
  and (select count(*) from l_strict) = 0);
select lab.prove('ON_ERROR ignore does not skip constraint violations: a duplicate key still fails the COPY (23505)',
  (select state = '23505' from lab.errs where test = 'on_error and a duplicate')
  and (select count(*) from l_strict) = 0);

完整实验脚本

冻结分区，挂接已构建的分区，再分析父表

COPY 向分区表加载时，会把每行路由到所属分区，并且仍然按分区批处理。这里有三个区别：

FREEZE 只对在同一事务中创建或截断的分区有效，对父表永远无效。
把下一个分区放到一边构建： 加载并索引一张普通表，添加一个与边界匹配的 CHECK，然后 attach partition 会跳过验证扫描（不停机地改造现有表）。
分析父表。 autovacuum 永远不会分析父表，而规划跨分区查询时会使用父表的统计信息。

深入阅读：FREEZE 需要作用于分区本身

在 COPY 中指定分区，而不是父表：

begin;
truncate events_2026_10;
\copy events_2026_10 from 'october.csv' with (format csv, freeze)
commit;

直接加载分区仍然会针对边界检查每一行：属于错误月份的行会让命令失败。截断分区会在该分区上取得 ACCESS EXCLUSIVE 锁，因此这适合尚未有人读取的分区，例如下个月的分区。

动手验证 · lab 26-loading

select lab.prove('COPY FREEZE on a partitioned table is refused (0A000)',
  (select state = '0A000' and msg = 'cannot perform COPY FREEZE on a partitioned table'
   from lab.errs where test = 'freeze on parent'));
select lab.prove('COPY FREEZE works on one partition truncated in the same transaction: same rows, all frozen',
  (select count(*) from :"leaf") = :leaf_rows
  and (select all_frozen >= 0.9 * pg_relation_size(:'leaf') / 8192 from pg_visibility_map_summary(:'leaf')));

完整实验脚本

批量 upsert：先暂存，再执行一条语句

把数据流同步进已有表时，通常会对每个输入行执行一条 INSERT ... ON CONFLICT：这是本章开头的单行模式，再加一次索引查找。改为先暂存：

create temp table stage
  (project_id bigint, events bigint, last_event_at timestamptz);
\copy stage from 'feed.csv' with (format csv)

insert into project_stats
select * from stage
on conflict (project_id) do update
  set events        = project_stats.events + excluded.events,
      last_event_at = greatest(project_stats.last_event_at, excluded.last_event_at);

对于 19,874 行的数据流，逐条 upsert 即使在一个事务中也耗时 2,811 ms；先暂存则耗时 605 ms，结果相同。

每次批量 upsert 都会遇到一条规则：一条语句不能两次更新同一行。某个键在数据流中出现两次时，会失败并报 ON CONFLICT DO UPDATE command cannot affect row a second time。先对暂存表去重（group by 或 distinct on）；因为它是一张表，你可以这样做。

深入阅读：MERGE，以及 autovacuum 永远不会分析的表

以同一张暂存表为来源执行 MERGE（PostgreSQL 15+）会产生相同的表；ON CONFLICT 会等待，MERGE 却会发生竞态解释了为什么在其他会话也在并发写入时，ON CONFLICT 更安全。autovacuum 永远不会分析临时表，因此在对它执行连接前，先运行 analyze stage。

动手验证 · lab 26-loading

select lab.prove('row-by-row, staged ON CONFLICT and MERGE produce identical tables',
  not exists (select * from ps_row except select * from ps_bulk)
  and not exists (select * from ps_bulk except select * from ps_row)
  and not exists (select * from ps_merge except select * from ps_row)
  and (select count(*) from ps_row) = (select count(*) from ps_merge));
select lab.prove('staging plus one upsert is at least twice as fast as one upsert per row',
  :bulk_ms * 2 < :row_ms);
select lab.prove('a batch with the same key twice fails ON CONFLICT DO UPDATE (21000)',
  (select state = '21000' and msg like 'ON CONFLICT DO UPDATE command cannot affect row a second time%'
   from lab.errs where test = 'same key twice'));

完整实验脚本

一次有计划的加载分七步

向新表或空表执行有计划的加载：

提高 max_wal_size，并检查 pg_wal 有足够的磁盘空间。
创建不带二级索引和外键的表，或者在加载事务中 truncate 它。
在该事务中执行 COPY ... WITH (FREEZE)。
执行 set maintenance_work_mem 将其调高，然后建立索引。
以 NOT VALID 添加外键，然后执行 VALIDATE 验证它们。
执行 vacuum (freeze, analyze)；只需执行 analyze，前提是已经使用 FREEZE。
把 max_wal_size 改回去，并观察副本延迟直到追上。

对于线上表：通过暂存表分批处理，保留索引，并在延迟增加时暂停。

本章要点

每批提交，而不是每行提交：自动提交的插入每次都会等待磁盘刷新。
使用 COPY（\copy 或驱动程序的 copy API）：它在速度和 WAL 上都胜过任何 INSERT。如果必须使用 INSERT，就发送 unnest 数组。
裸加载，然后建立索引并以 NOT VALID 添加外键，再执行 VALIDATE：速度为原来的六倍，WAL 减半。
COPY ... FREEZE 向在同一事务中创建或截断的表加载时，会写入已完成的行，因此第一次读取不会写入。
先使用不记日志表再 SET LOGGED，只是把 WAL 开销后移，并没有节省 WAL。只有 wal_level = minimal 会跳过它，代价是失去副本和 PITR。
大批量加载前提高 max_wal_size，最后执行 vacuum (freeze, analyze)，或者至少执行 analyze。
从去重后的暂存表批量 upsert。

27 快速加载数据

加载速度从每秒 1,200 行到 50 万行

每批提交一次，而不是每行提交一次

用数组批处理，而不是 VALUES 列表

COPY 是批量加载路径

COPY FREEZE 会写入已完成的行

先使用不记日志表，再 SET LOGGED，只是把 WAL 开销后移

wal_level = minimal 会为新表跳过 WAL

加载之后再建立索引和外键

外键：先 NOT VALID，再 VALIDATE

大批量加载会触发检查点并淹没副本

开放入口前先 vacuum 和 analyze

一个坏值会让整个 COPY 失败

冻结分区，挂接已构建的分区，再分析父表

批量 upsert：先暂存，再执行一条语句

一次有计划的加载分七步

本章要点

延伸阅读