# 14.4\. 向数据库中添加记录 第一次填充数据库时可能需要做大量的表插入。下面是一些建议，可以尽可能高效地处理这些事情。 ## 14.4.1\. 关闭自动提交 当使用多条`INSERT`时，关闭自动提交，并且只在每次(数据拷贝)结束的时候做一次提交。 在纯SQL里，这就意味着在开始的时候发出`BEGIN`并且在结束的时候执行`COMMIT`。 有些客户端的库可能背着你干这些事情， 这种情况下你必须确信只有在你确实要那些库干这些事情的时候它才做。 如果你允许每个插入都独立地提交，那么PostgreSQL会为所增加的每行记录做大量的处理。 在一个事务里完成所有插入的动作的最大的好处就是，如果有一条记录插入失败， 那么，到该点为止的所有已插入记录都将被回滚， 这样你就不会很难受地面对一个只装载了一部分数据的表。 ## 14.4.2\. 使用`COPY` 使用[COPY](#calibre_link-777)在一条命令里装载所有记录， 而不是一连串的`INSERT`命令。`COPY`命令是为装载数量巨大的数据行优化过的； 它没`INSERT`那么灵活，但是在大量装载数据的情况下，导致的荷载也少很多。 因为`COPY`是单条命令，因此填充表的时候就没有必要关闭自动提交了。 如果你不能使用`COPY`，那么使用[PREPARE](#calibre_link-625)来创建一个预备`INSERT`， 然后使用`EXECUTE`多次效率更高。这样就避免了重复分析和规划`INSERT`的开销。 不同的接口提供便利的方式不同；查看接口文档的"已准备语句"。 请注意，在装载大量数据行的时候，`COPY`几乎总是比`INSERT`快， 即使使用了`PREPARE`并且把多个`INSERT`命令绑在一个事务中也是这样。 当在相同事务中作为较早的`CREATE TABLE`或者`TRUNCATE`命令 使用的时候，`COPY`是最快的。在这种情况下，没有WAL 需要写入，因为在错误情况下，这些文件包含新加载的数据将被删除。 然而，这种考虑只适用于当所有命令必须写WAL时，[wal_level](#calibre_link-1039)是`最小的`。 ## 14.4.3\. 删除索引 如果你正在装载一个新创建的表，最快的方法是创建表， 用`COPY`批量装载，然后创建表需要的任何索引。 在已存在数据的表上创建索引要比递增地更新所装载的每一行记录要快。 如果你对现有表增加大量的数据，可能先删除索引，装载表， 然后重新创建索引更快些。当然，在缺少索引的期间，其它数据库用户的数据库性能将有负面的影响。 并且我们在删除唯一索引之前还需要仔细考虑清楚，因为唯一约束提供的错误检查在缺少索引的时候会消失。 ## 14.4.4\. 删除外键约束 和索引一样，"批量地"检查外键约束比一行行检查更高效。因此，也许我们先删除外键约束， 装载数据，然后重建约束会更高效。同样，装载数据和缺少约束而失去错误检查之间也有一个平衡。 更重要的是，当你将数据加载到已有外键约束的表中的时候， 每个新行需要等待触发事件的服务器列表中的项（因为它是触发器的触发，检查行的外键约束）。 装载数以百万计的行可以引起触发事件队列溢出可用内存，导致无法忍受的交换， 甚至命令的彻底失败。因此它可能是_必要的_，不只是理想的，当加载大量数据的时候， 删除并且重新申请外键约束。如果暂时删除约束是不能接受的， 唯一的其他资源可能会将负载操作分裂为更小的事务。 ## 14.4.5\. 增大`maintenance_work_mem` 在装载大量的数据的时候，临时增大[maintenance_work_mem](#calibre_link-1150)配置变量可以改进性能。 这个参数也可以帮助加速 `CREATE INDEX`和`ALTER TABLE ADD FOREIGN KEY`命令。 它不会对`COPY`本身有多大作用，所以这个建议只有在你使用上面的两个技巧时才有效。 ## 14.4.6\. 增大`checkpoint_segments` 临时增大[checkpoint_segments](#calibre_link-1208)配置变量也可以让大量数据装载得更快。 这是因为向PostgreSQL里面装载大量的数据可以导致检查点操作 (由配置变量`checkpoint_timeout`声明) 比平常更加频繁发生。在发生一个检查点的时候，所有脏数据都必须刷新到磁盘上。 通过在大量数据装载的时候临时增加`checkpoint_segments`， 所要求的检查点的数目可以减少。 ## 14.4.7\. 禁用WAL归档和流复制 当加载大量数据到使用WAL归档或流复制的安装过程时， 加载完成之后采取新的基础备份比处理大量增量WAL数据可能会更快。当加载时为防止增量WAL日志，关闭归档和流复制，通过设置 [wal_level](#calibre_link-1039)到`最小`，[archive_mode](#calibre_link-1038)到`off`, [max_wal_senders](#calibre_link-470)为零。 但是请注意，更改这些设置需要重新启动服务器。 除了为归档或者WAL发送处理WAL数据来避免时间， 这样做实际上将使某些命令更快，因为如果`wal_level` 是`最小的`，他们设计不写WAL（他们可以保证碰撞安全性最后通过`fsync`比写WAL更便宜）。 这适用于以下命令： * `CREATE TABLE AS SELECT` * `CREATE INDEX` (正如 `ALTER TABLE ADD PRIMARY KEY`) * `ALTER TABLE SET TABLESPACE` * `CLUSTER` * `COPY FROM`，当目标表在同一事务之前已经被创建或截断。 ## 14.4.8\. 事后运行`ANALYZE` 不管什么时候，如果你在更新了表中的大量数据之后，运行[ANALYZE](#calibre_link-589)都是个好习惯。 这包含大量加载数据到表。运行`ANALYZE` (或者`VACUUM ANALYZE`) 可以保证规划器有表数据的最新统计。 如果没有统计数据或者统计数据太陈旧，那么规划器可能选择很差劲的查询规划， 导致表的错误或者不存在数据的性能恶化。 请注意如果启动autovacuum守护进程，可能自动运行`ANALYZE`；获取详情请 参阅[Section 23.1.3](#calibre_link-446)和[Section 23.1.6](#calibre_link-77)。 ## 14.4.9\. pg_dump的一些注意事项 pg_dump生成的转储脚本自动使用上面的若干个技巧，但不是全部。 要尽可能快地装载pg_dump转储，我们需要手工做几个事情。请注意， 这些要点适用于恢复一个_转储_，而不是_创建_一个转储的时候。 同样的要点也适用于使用psql或者pg_restore从pg_dump 归档文件装载文本复制的时候。 缺省的时候，pg_dump使用`COPY`，在它生成一个完整的模式和数据的转储的时候， 它会很小心地先装载数据，然后创建索引和外键。因此，在这个情况下，头几条技巧是自动处理的。 剩余的是你要做的： * 设置比正常状况大的`maintenance_work_mem`和`checkpoint_segments`值。 * 如果使用WAL归档或流复制，可以考虑在恢复过程中禁用他们。要做到这一点，设置`archive_mode`为`off`， `wal_level`为`最小的`，并且设置 `max_wal_senders`在装载前为零。随后，设置它们返回正确的值，并采取新的基础备份。 * 使用pg_dump和pg_restore并行转储和恢复两种模式的实验，并找到 并行作业使用的最佳数目。平行转储和恢复通过`-j`选项应提供给你串行模式中的更高的性能。 * 考虑整个转储是否应作为单个事务进行恢复。要做到这一点，通过`-1`或者 `-single-transaction`命令行选项的psql或者pg_restore。 当使用此模式时，即使是最小的误差将回滚整个恢复，可能丢弃很多时间的处理。 取决于如何相互关联数据，这可能最好是手动清理或者没有。如果你使用单一事务并且WAL归档关闭，则`COPY`命令将运行速度最快。 * 如果在数据库服务器中有多个CPU，可以考虑使用pg_restore的`-jobs` 选项。 这允许并发数据加载和索引的创建。 * 之后运行`ANALYZE`。 只保存数据的转储仍然会使用`COPY`，但是它不会删除或者重建索引， 并且它不会自动修改外键。 \[1\] 因此当装载只有数据的转储时候，如果你想使用这些技术，删除以及重建索引和外键完全取决于你。 当加载数据时，增大`checkpoint_segments`仍然是有用的， 但是增大`maintenance_work_mem`就没什么必要了； 相反，你只是应该在事后手工创建索引和外键， 最后结束时不要忘记`ANALYZE`命令。参阅[Section 23.1.3](#calibre_link-446) 和[Section 23.1.6](#calibre_link-77)获取更多详情。 ### Notes \[1\] 你可以通过使用`-disable-triggers`选项的方法获取关闭外键的效果。 不过要意识到这么做是消除，而不只是推迟违反外键约束， 因此如果你使用这个选项，将有可能插入坏数据。