14.4 向数据库批量装载数据

首次填充数据库时，可能需要插入大量数据。本节介绍一些建议，帮助尽可能高效地完成这一过程。

14.4.1 关闭自动提交

执行多条 INSERT 时，关闭自动提交，只在最后提交一次。在普通 SQL 中，这意味着开始时执行 BEGIN，结束时执行 COMMIT。有些客户端库可能在后台代为处理，因此要确认它们确实按你的要求执行。如果每次插入都单独提交，PostgreSQL 就会为每一行新增数据做大量工作。

把所有插入放在一个事务中的另一个好处是：一行插入失败时，此前已插入的所有行都会回滚，避免留下只装载了一部分的数据。

14.4.2 使用 COPY

使用 COPY 在一条命令中装载所有行，而不是执行一系列 INSERT。COPY 针对大量行的装载进行了优化；它的灵活性不如 INSERT，但大批量装载的开销显著更小。由于 COPY 是单条命令，用它填充表时无需关闭自动提交。

如果不能使用 COPY，可考虑用 PREPARE 创建预备的 INSERT 语句，再按需要多次执行 EXECUTE，从而避免反复解析和规划 INSERT 的部分开销。各接口的方式不同，可查阅接口文档中的“预备语句”。

即使使用 PREPARE 并把多次插入批量放在一个事务中，使用 COPY 装载大量行也几乎总是更快。

在此前执行了 CREATE TABLE 或 TRUNCATE 的同一事务中使用 COPY，速度最快。这时无需写入 WAL，因为发生错误后，新装载数据所在的文件无论如何都会被删除。不过，此项只在 wal_level 为 minimal 时适用；其他设置下，所有命令仍须写入 WAL。

14.4.3 移除索引

装载新建表时，最快的方式是先创建表，用 COPY 批量装载数据，再创建所需索引。在已有数据上创建索引，比每装载一行就增量更新索引更快。

向已有表增加大量数据时，删除索引、装载数据、再重建索引可能有利。但索引缺失期间，其他用户的数据库性能可能下降。删除唯一索引前也应慎重，因为索引缺失时，将失去唯一约束提供的错误检查。

14.4.4 移除外键约束

与索引类似，批量检查外键约束比逐行检查更高效。因此，可以考虑删除外键约束、装载数据、再重新创建约束。同样，需要权衡装载速度与约束缺失期间失去的错误检查。

此外，向已有外键约束的表装载数据时，每个新行都需要在服务器的待处理触发器事件列表中增加一项，因为行的外键约束由触发器检查。装载数百万行可能让触发器事件队列耗尽可用内存，导致难以接受的交换，甚至命令直接失败。

因此，大量装载时删除并重新应用外键可能是必要的，而不只是可取的。如果不能临时移除约束，另一个办法可能只能是把装载操作拆分为较小的事务。

14.4.5 增大 maintenance_work_mem

装载大量数据时，临时增大 maintenance_work_mem 配置变量可能改善性能。它能加速 CREATE INDEX 与 ALTER TABLE ADD FOREIGN KEY，但对 COPY 本身帮助不大，因此仅在使用上述一种或两种技巧时才有用。

14.4.6 增大 max_wal_size

临时增大 max_wal_size 也可能加速大量数据装载。大量装载会让检查点比正常的检查点频率更频繁；正常频率由 checkpoint_timeout 指定。每次检查点都须把所有脏页刷新到磁盘。装载期间临时增大 max_wal_size，可以减少所需检查点的数量。

14.4.7 关闭 WAL 归档与流复制

在使用 WAL 归档或流复制的实例中，大量装载完成后重新做一次基础备份，可能比处理大量增量 WAL 数据更快。要在装载时阻止增量 WAL 日志记录，可关闭归档和流复制，把 wal_level 设为 minimal、archive_mode 设为 off、max_wal_senders 设为零。

注意：这些设置的修改需要重启服务器；此前取得的基础备份将无法用于归档恢复和备用服务器，可能导致数据丢失。

除了省去归档器或 WAL 发送器处理 WAL 数据的时间，这也会真正加速某些命令：当 wal_level 为 minimal，且当前子事务或顶层事务创建或截断了这些命令所修改的表或索引时，它们完全不必写 WAL。通过在结束时执行 fsync，它们可以用更低的成本保证崩溃安全性。

14.4.8 随后执行 ANALYZE

只要显著改变了表内的数据分布，就强烈建议执行 ANALYZE；这包括向表批量装载大量数据。ANALYZE 或 VACUUM ANALYZE 能确保规划器拥有最新的表统计信息。缺少统计信息或信息过时，可能让规划器在查询规划中做出不良选择，导致相关表的性能下降。

如果启用了 autovacuum 守护进程，它可能自动执行 ANALYZE；详见 24.1.3 节与 24.1.6 节。

14.4.9 关于 pg_dump 的几点说明

pg_dump 生成的转储脚本会自动采用上述部分建议，但并非全部。要尽快恢复转储，还需要手工做一些额外处理。这里讨论的是恢复转储时的操作，而不是创建转储；无论用 psql 装载文本转储，还是用 pg_restore 装载归档文件，都适用。

默认情况下，pg_dump 使用 COPY；生成完整的结构和数据转储时，会先装载数据，再创建索引和外键，因此部分建议已自动落实。剩余需要处理的是：

• 为 maintenance_work_mem 和 max_wal_size 设置合适的、比平时更大的值。
• 使用 WAL 归档或流复制时，考虑在恢复期间关闭它们：装载前把 archive_mode 设为 off、wal_level 设为 minimal、max_wal_senders 设为零。随后恢复正确设置，并重新做一次基础备份。
• 尝试 pg_dump 和 pg_restore 的并行转储、并行恢复模式，找出最佳并发作业数。通过 -j 选项并行转储和恢复，应能比串行模式取得显著更高的性能。
• 考虑是否把整个转储作为单个事务恢复。向 psql 或 pg_restore 传入 -1 或 --single-transaction 即可。在此模式下，即使最小的错误也会回滚整个恢复，可能丢弃数小时的处理结果。根据数据之间的关联程度，这可能比手工清理更合适，也可能不合适。采用单个事务并关闭 WAL 归档时，COPY 命令速度最快。
• 数据库服务器有多个 CPU 时，考虑 pg_restore 的 --jobs 选项，以并发装载数据和创建索引。
• 结束后执行 ANALYZE。

仅数据转储仍会使用 COPY，但不会删除或重建索引，通常也不会处理外键。因此，要在仅数据转储装载时采用这些技巧，就需要自行删除并重建索引和外键。装载时增大 max_wal_size 仍有用，但无需增大 maintenance_work_mem；应在随后手工重建索引和外键时再增大它。

完成后别忘记执行 ANALYZE；详见 24.1.3 和 24.1.6 节。

注 [14]：使用 --disable-triggers 可以获得禁用外键的效果，但它会彻底消除外键验证，而不只是延后验证，因此可能插入不合法的数据。