# 快速加载数据
> 用 COPY、FREEZE 和延后建立的索引，在几秒内加载数百万行；并按自己的计划承担 WAL、检查点、提示位和统计信息的代价。
---
LLMS 索引： [llms.txt](/llms.txt)
---
一个跑一整夜的导入，和一个在咖啡变凉前就完成的导入，可以把同样的一百万行加载进同一张表。区别在于每一行还拖着什么：一次提交、一次往返通信、一次语句解析、每个索引中的一项、一次外键查找。去掉这些工作后，Postgres 加载行的速度几乎和写入页的速度一样快。
然后账单就来了。大批量加载写入的 WAL 比它填满的表还大，这些日志会触发检查点，并落到每个副本上。行还没有完成，因此第一次读取它们的查询会向每一页写入。规划器也不知道表发生了变化。
所以，加载时要让每行做尽可能少的工作，然后在用户发现之前，按照自己的意图主动支付延期的代价。
## 加载速度从每秒 1,200 行到 50 万行 {#load-speed-ranges-from-1200-rows-a-second-to-half-a-million}
我们把结构相同的事件行加载进 `events` 的新副本，测试了十种方式；每种方式都在一台使用默认设置的独立服务器上运行，并统计每秒行数和 **WAL**。WAL 是每次变更在到达数据文件之前都要写入的日志（[先记日志，再写数据](/08-wal/#basics-chapter-8)）。两种单行方式加载 1,000 行，因为一百万行会耗时数小时；其余方式加载 1,000,000 行：
```text
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。
> [!DETAILS]- 深入阅读：十种加载方式是如何测量的
>
> 每台服务器都设置了 `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 行，因此实验只检查比例，从不比较时间。
## 每批提交一次，而不是每行提交一次 {#commit-once-per-batch-not-once-per-row}
第 1 行和第 2 行是相同的 1,000 次插入。唯一的区别是在它们外面加了一次 `begin` 和一次 `commit`。在这里，这让加载速度达到原来的约 15 倍；在更繁忙的运行中，所有刷新都要排在其他写入后面，速度则达到原来的 100 多倍。
自动提交的 `INSERT` 自己就是一个事务，每次提交都要等待它的 WAL 到达磁盘（[提交意味着刷新](/08-wal/#commit-means-flush)）。提交记录很小，等待才是每行的代价。
整个加载只使用一个事务，也有自己的问题：第 999,999 行失败会回滚全部工作，运行期间还会阻碍清理。向线上表加载时，每几千行到几十万行提交一次，就像[回填](/26-migrations/#backfill-in-short-batches-that-walk-the-primary-key)那样。向空表加载时，一个事务就够了，也能解锁下面最快的路径。
> [!DETAILS]- 深入阅读：每行多一条记录，以及等待刷新
>
> 单行插入会写两条 WAL 记录：堆和主键；自动提交还会增加一条提交记录。500 行的结果是：12,884 ms 内写入 1,521 条记录，而另一种方式在 88 ms 内写入 1,021 条。多出的 500 条记录只多出 17 KB WAL，而繁忙笔记本运行 Docker 时，12.9 秒几乎全部耗在刷新上。优秀的 NVMe 磁盘刷新耗时远低于 1 毫秒；这种规律仍然成立。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 用数组批处理，而不是 VALUES 列表 {#batch-with-arrays-not-values-lists}
提交开销去掉后，每行的代价就是语句本身：一次往返通信、一次解析、一次规划。批量发送 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`。两种形式都不能节省磁盘：每行都会写一条堆记录和一条索引记录。
> [!DETAILS]- 深入阅读：为什么 VALUES 列表会到达上限
>
> 预先准备的 `unnest` 形式：
>
> ```sql
> 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 倍。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## COPY 是批量加载路径 {#copy-is-the-bulk-path}
`COPY ... FROM STDIN` 可以用文本、CSV 或二进制这样的简单格式流式发送行，完全不需要每行一条语句。它的写入方式也不同：它会缓冲行，一次填满一页堆，而不是每行写一条堆 WAL 记录，每页只写一条堆 WAL 记录（但每条索引项仍然按行记录）。读取 `wal/row` 列：每种 `INSERT` 形式都是 239 字节，`COPY` 是 193 字节。第 5 行的加载速度则是 `unnest` 的好几倍。
使用客户端一侧的方式：`COPY table FROM '/path'` 会读取数据库服务器上的文件，需要特殊权限；而 psql 的 `\copy` 会读取**你的**机器上的文件，并以 `COPY FROM STDIN` 的形式发送：
```sql
\copy events_import from 'events.csv' with (format csv)
```
所有成熟的驱动程序都支持 copy 协议。在 Go 中，pgx 的 `CopyFrom` 发送二进制 `COPY`；在 Node 中，`pg-copy-streams` 会把任意流接入管道。
> [!DETAILS]- 深入阅读：是什么关闭了 COPY 逐页写入的路径
>
> 在 100,000 行上，`VALUES` 和 `unnest` 各自写入约 203,000 条 WAL 记录（每行 2 条）；`COPY` 写入 105,243 条：每条主键项一条，再加上每个堆页一条；字节数少了 19%。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
>
> 有三件事会让 `COPY` 退回到逐行处理。第一，`BEFORE` 或 `INSTEAD OF` 行触发器，因为每行都必须先通过它，才能处理下一行。第二，易变的默认表达式，因为它可能读取正在加载的表（标识列和 serial 列背后的 `nextval()` 是源码中特别放行的唯一例外）。第三，带有易变函数的 `WHERE` 子句位于 `COPY` 上。`AFTER` 触发器，包括外键背后的触发器，不会停止批处理，但每行仍然会排队一个事件。
## COPY FREEZE 会写入已完成的行 {#copy-freeze-writes-the-rows-finished}
普通 `COPY` 加载的行还没有完成。它的行头记录了加载事务，因此第一次读取时，系统会检查该事务是否已提交，并把答案保存到行的**提示位**中（[读取也可能写入](/06-mvcc/#a-read-can-write)）。这会弄脏页面；而在开启[数据校验和](/43-glossary/#data-checksums)时（PostgreSQL 18 的默认设置），还会记录一张[整页镜像](/43-glossary/#full-page-write)。检查点之后，对刚刚复制的 100,000 行执行第一次 `count(*)`：
```text
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 计数器[回卷](/06-mvcc/#old-rows-must-be-frozen-before-the-xid-counter-wraps)之前，回来**冻结**每一行，将它们永久标记为对所有事务可见。
`COPY ... WITH (FREEZE)` 会写入已经冻结的行；从 PostgreSQL 14 开始，它还会把每一页标记为[全可见](/43-glossary/#visibility-map)。条件是：表必须在同一个事务中创建或截断，这样其他人不可能已经看到它：
```sql
begin;
truncate events_import;
\copy events_import from 'events.csv' with (format csv, freeze)
commit;
```
跳过 `truncate` 后，Postgres 会拒绝执行。对冻结表执行相同的 `count(*)` 什么也不会弄脏，即使是第一次执行也如此；第 6 行的 WAL 代价与第 5 行相同。如果可以使用 `FREEZE` 加载，就使用它。
> [!EXAMPLE]- 动手验证 · lab `26-loading`
>
> ```sql
> 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);
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
> [!DETAILS]- 深入阅读：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 本应把它们隐藏起来。对于在加载完成前没有人读取的表，这无关紧要。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 先使用不记日志表，再 SET LOGGED，只是把 WAL 开销后移 {#unlogged-then-set-logged-moves-the-wal}
一个常见技巧是：创建[`unlogged`](/43-glossary/#unlogged-table)表，不写 WAL 地加载它，然后执行 `alter table ... set logged`。第 8a 和第 8b 行：加载只写入 34 KB，然后 `SET LOGGED` 写入 181 MB，而普通 `COPY` 写入 184 MB。
这条绕路只是把代价推后，并没有消除它。`SET LOGGED` 会重写表并记录每一行，因为副本或恢复操作没有其他数据记录。它执行时会持有 `ACCESS EXCLUSIVE` 锁，而在转换完成之前如果崩溃，表会被清空。
不记日志表适合用作不会转为正式表的**暂存表**：加载原始数据，用 SQL 清洗，把结果插入真正的表，再删除暂存表。只有最后一次插入承担 WAL（[能重新生成的数据可以跳过 WAL](/37-scale/#skip-the-wal-for-data-you-can-regenerate)）。
> [!DETAILS]- 深入阅读：SET LOGGED 会先写表，再写索引
>
> 在 100,000 行上，`SET LOGGED` 写入 18 MB：先为行写入 100,190 条记录，再为重建的索引写日志。这个大小大约等于表加索引的大小，接近普通 `COPY` 写入的 19.3 MB。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## wal_level = minimal 会为新表跳过 WAL {#wal_level--minimal-skips-wal-for-new-tables}
第 7 行是常见加载建议中的陷阱。“在同一个事务中创建表并执行 `COPY`”什么也没有节省：184 MB，与第 5 行相同。
这个技巧只有在 `wal_level = minimal` 下才有效；此时，创建或截断表并加载数据的事务会在提交时对新文件执行 fsync，而不是记录这些行。在这样启动的服务器上，相同的加载只写入 38 KB，而不是 184 MB；在该服务器上，它的速度约为向已有表执行 `COPY` 的三倍。
除此之外，它的代价都很高：开启和关闭都需要重启，没有副本，没有归档，也就没有时间点恢复。它只适合一种工作：在任何对象依赖它之前，从头构建数据库，然后获取一份新的基础备份。托管数据库通常不允许设置它。
> [!DETAILS]- 深入阅读：最小 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 已经让文件持久化，而无需日志。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading.sh`
> >
> > ```bash
> > 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"
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sh)
## 加载之后再建立索引和外键 {#build-indexes-and-foreign-keys-after-the-load}
第 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 {#foreign-keys-not-valid-then-validate}
外键会为每一条加载的行检查父表，并在语句结束前为每行排队一个触发器事件；文档警告，在加载数百万行时，这个队列可能耗尽内存。
像[迁移](/26-migrations/#add-foreign-keys-not-valid-then-validate)那样分两步添加：`NOT VALID` 会在不检查旧行的情况下对新行强制执行；然后 `VALIDATE CONSTRAINT` 通过一次连接运算检查旧行，并在允许写入继续进行的锁下运行。验证是真正的检查：实验中，它发现了约束建立前埋入的一条孤儿行；在删除这条孤儿行之前，约束一直保持未验证状态。
> [!DETAILS]- 深入阅读：为什么逐行增长的索引会更大
>
> 在 100,000 行上，索引已存在时要写入 348,851 条 WAL 记录；之后建立索引时只有 5,194 条。（时间差会在不同运行中变化；早期有一次两种方式之间没有检查点，裸加载反而输了。）
>
> 行按 `created_at` 顺序到达，但 `(project_id, created_at)` 会把它们分散到索引各处，因此叶页会从中间分裂，最后部分为空。构建索引时会先排序，并按填充因子（fillfactor）填充（B-树叶页为 90%）：构建后的大小是 3,104 KB，而逐行增长的是 4,328 KB；增长出来的索引会一直更大，直到 `REINDEX`。[每个索引都是写入税](/17-btree/#every-index-is-a-tax-on-writes)测量了每个索引的代价。
>
> `CREATE INDEX` 会在 `maintenance_work_mem` 中排序（默认 64 MB），并与最多 `max_parallel_maintenance_workers` 个工作进程共享内存（默认 2）；超过内存后会溢写到临时文件。实验中的构建在 1 MB 时发生溢写，在 256 MB 时没有。让全服务器值保持适度；每个 autovacuum 工作进程也可能占用这么多内存，除非另有 `autovacuum_work_mem` 设置。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 大批量加载会触发检查点并淹没副本 {#a-big-load-forces-checkpoints-and-floods-replicas}
写入数 GB WAL 的加载不会孤立发生。
**检查点会提前到来。** 超过 `max_wal_size`（默认 1 GB）的 WAL 会触发检查点（[检查点为恢复设定上限](/08-wal/#checkpoints-put-a-limit-on-recovery)）。上面最重的加载为一百万行写入了 346 MB，因此一千万行会超过这个限额三次。每个提前的检查点都会和加载争用磁盘，并让对每一页的下一次变更再次记录整页镜像（[检查点后的第一次变化会记录整页](/08-wal/#the-first-change-after-a-checkpoint-logs-the-whole-page)）。计划加载之前，提高 `max_wal_size`（只需 reload，不需重启），确保 `pg_wal` 所在磁盘有空间，完成后再改回去。
**每个副本都会收到每个字节**，并由单个进程重放（[重放只有一个进程](/38-replication/#replay-is-one-process)）。减少 WAL（不带索引和约束加载、在不记日志表中暂存、开启[wal_compression](/08-wal/#wal_compression-is-a-cheap-win)），分批加载，并在副本延迟超过预算时暂停。逻辑订阅端逐行应用加载，因此延迟会更久。
> [!DETAILS]- 深入阅读：两个强制检查点让加载付出什么
>
> 将同样的 500,000 行加载进有三个索引的表，分别把 `max_wal_size` 设为 128 MB 和 16 GB：
>
> ```text
> 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。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading.sh`
> >
> > ```bash
> > 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)"
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sh)
## 开放入口前先 vacuum 和 analyze {#vacuum-and-analyze-before-you-open-the-doors}
加载完成后，立刻自己支付延期的代价：
```sql
vacuum (freeze, analyze) events_import;
```
一次处理会设置提示位，冻结每一行，使防回卷清理不必再次访问这些页（清理的[冻结步骤](/10-vacuum/#basics-10-4-freeze-old-tuples)），把页标记为全可见以便仅索引扫描工作，并收集统计信息。autovacuum 最终也会到达那里，但会受到限流，并按自己的计划运行；而此时用户已经在查询了。对于 `COPY FREEZE` 表，没有行需要再冻结，同一条命令几乎不写任何东西。
不要跳过 `analyze`。从未分析过的表没有列统计信息，因此规划器只能猜测。问它刚加载的 100,000 个事件中有多少是告警时，它估计为 554；真实数量是 12,621，约为估计值的 23 倍，这样的猜测会选择嵌套循环，而应该使用哈希连接。执行 `ANALYZE` 后，估算是 12,550。把它作为每次加载的最后一步（[三种变化会绕过 autovacuum 的 analyze](/20-planner/#three-kinds-of-change-slip-past-autovacuums-analyze)）。
> [!DETAILS]- 深入阅读：向已有表加载时，统计信息会以另一种方式过时
>
> 有统计信息的表也不安全：向其中大量加载后，在再次分析之前，统计信息描述的仍然是加载前的表。autovacuum 会在表的 10% 发生变化时分析它（`autovacuum_analyze_scale_factor`，再加 50 行），因此向大表增加 5% 的数据可能永远不会触发它。而你刚加载的行通常是最新的，也是查询最常请求的行，却位于规划器仍持有的直方图末端之外。每次大批量加载后都要分析，不只是向空表加载时才这样做。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 一个坏值会让整个 COPY 失败 {#one-bad-value-fails-the-whole-copy}
默认情况下，任何位置出现一个格式错误的值，都会让整个 `COPY` 失败，并且不会加载任何内容。PostgreSQL 17 增加了 `ON_ERROR ignore`，会跳过值无法转换为列类型的行；PostgreSQL 18 增加了 `REJECT_LIMIT`，因此“跳过几行”不会悄悄变成“跳过一半文件”。只有**数据类型**错误会被跳过：重复键或约束失败仍然会让全部操作停止。
对于更脏的数据，可以用 `COPY` 把数据导入一个由 `text` 列组成的暂存表，它什么都接受；然后用一条 `INSERT ... SELECT` 把通过检查的行写入目标表。
> [!DETAILS]- 深入阅读：ON_ERROR 会跳过什么，以及什么仍会停止加载
>
> 给定四行数据，其中两行有坏整数，`on_error ignore` 会加载另外两行；设置 `log_verbosity verbose` 后，每跳过一行都会打印一条通知。设置 `reject_limit 1` 后，第二个坏行会让命令失败（`skipped more than REJECT_LIMIT (1) rows due to data type incompatibility`），并且不会加载任何内容。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 冻结分区，挂接已构建的分区，再分析父表 {#freeze-the-partition-attach-it-built-analyze-the-parent}
`COPY` 向分区表加载时，会把每行路由到所属分区，并且仍然按分区批处理。这里有三个区别：
- **`FREEZE` 只对在同一事务中创建或截断的分区有效，对父表永远无效。**
- **把下一个分区放到一边构建：** 加载并索引一张普通表，添加一个与边界匹配的 `CHECK`，然后 `attach partition` 会跳过验证扫描（[不停机地改造现有表](/39-partitioning/#convert-a-live-table-without-downtime)）。
- **分析父表。** autovacuum 永远不会分析父表，而规划跨分区查询时会使用父表的统计信息。
> [!DETAILS]- 深入阅读：FREEZE 需要作用于分区本身
>
> 在 `COPY` 中指定分区，而不是父表：
>
> ```sql
> begin;
> truncate events_2026_10;
> \copy events_2026_10 from 'october.csv' with (format csv, freeze)
> commit;
> ```
>
> 直接加载分区仍然会针对边界检查每一行：属于错误月份的行会让命令失败。截断分区会在该分区上取得 `ACCESS EXCLUSIVE` 锁，因此这适合尚未有人读取的分区，例如下个月的分区。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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')));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 批量 upsert：先暂存，再执行一条语句 {#upserts-in-bulk-stage-then-one-statement}
把数据流同步进已有表时，通常会对每个输入行执行一条 `INSERT ... ON CONFLICT`：这是本章开头的单行模式，再加一次索引查找。改为先暂存：
```sql
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`）；因为它是一张表，你可以这样做。
> [!DETAILS]- 深入阅读：MERGE，以及 autovacuum 永远不会分析的表
>
> 以同一张暂存表为来源执行 `MERGE`（PostgreSQL 15+）会产生相同的表；[ON CONFLICT 会等待，MERGE 却会发生竞态](/22-queries/#merge-races-where-on-conflict-waits)解释了为什么在其他会话也在并发写入时，`ON CONFLICT` 更安全。autovacuum 永远不会分析临时表，因此在对它执行连接前，先运行 `analyze stage`。
>
> > [!EXAMPLE]- 动手验证 · lab `26-loading`
> >
> > ```sql
> > 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'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/26-loading.sql)
## 一次有计划的加载分七步 {#a-planned-load-takes-seven-steps}
向新表或空表执行有计划的加载：
1. 提高 `max_wal_size`，并检查 `pg_wal` 有足够的磁盘空间。
2. 创建不带二级索引和外键的表，或者在加载事务中 `truncate` 它。
3. 在该事务中执行 `COPY ... WITH (FREEZE)`。
4. 执行 `set maintenance_work_mem` 将其调高，然后建立索引。
5. 以 `NOT VALID` 添加外键，然后执行 `VALIDATE` 验证它们。
6. 执行 `vacuum (freeze, analyze)`；只需执行 `analyze`，前提是已经使用 `FREEZE`。
7. 把 `max_wal_size` 改回去，并观察副本延迟直到追上。
对于线上表：通过暂存表分批处理，保留索引，并在延迟增加时暂停。
## 本章要点 {#the-short-version}
- 每批提交，而不是每行提交：自动提交的插入每次都会等待磁盘刷新。
- 使用 `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。
## 延伸阅读 {#further-reading}
- [Populating a Database](https://www.postgresql.org/docs/18/populate.html)，PostgreSQL 文档。官方清单：COPY、删除索引和外键、`maintenance_work_mem`、`max_wal_size`、`wal_level = minimal` 以及之后的 analyze。
- [COPY](https://www.postgresql.org/docs/18/sql-copy.html)，PostgreSQL 文档。所有选项，包括 `FREEZE`、`ON_ERROR`、`REJECT_LIMIT` 和 `LOG_VERBOSITY`，以及服务器端 COPY 的文件访问规则。
- [Write Ahead Log settings](https://www.postgresql.org/docs/18/runtime-config-wal.html)，PostgreSQL 文档。`wal_level = minimal` 会移除什么、哪些命令会在其中跳过 WAL，以及 `wal_skip_threshold`。
- [INSERT ... ON CONFLICT](https://www.postgresql.org/docs/18/sql-insert.html#SQL-ON-CONFLICT)，PostgreSQL 文档。upsert 规则，包括为什么一条语句不能两次处理同一行。