| PostgreSQL 9.0.4 中文文档 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 14. 性能提升技巧 | Fast Forward | Next |
第一次填充数据库时可能需要做大量的表插入。 下面是一些建议，可以尽可能高效地处理这些事情。
关闭自动提交，并且只在每次(数据拷贝)结束的时候做一次提交。 在纯 SQL 里，这就意味着在开始的时候发出BEGIN并且在结束的时候执行COMMIT。 有些客户端的库可能背着你干这些事情，这种情况下你必须确信只有在你确实要那些库干这些事情的时候它才做。 如果你允许每个插入都独立地提交，那么PostgreSQL会为所增加的每行记录做大量的处理。 在一个事务里完成所有插入的动作的最大的好处就是，如果有一条记录插入失败， 那么，到该点为止的所有已插入记录都将被回滚，这样你就不会很难受地面对一个只装载了一部分数据的表。
使用COPY在一条命令里装载所有记录，而不是一连串的INSERT命令。 COPY命令是为装载数量巨大的数据行优化过的； 它没INSERT那么灵活，但是在大量装载数据的情况下，导致的荷载也少很多。 因为COPY是单条命令，因此填充表的时候就没有必要关闭自动提交了。
如果你不能使用COPY，那么使用PREPARE来创建一个预备INSERT， 然后使用EXECUTE多次效率更高。 这样就避免了重复分析和规划INSERT的开销。不同接口以不同的方式提供该功能， 可参阅接口文档中的"prepared statements"章节。
请注意，在装载大量数据行的时候，COPY几乎总是比INSERT快， 即使使用了PREPARE并且把多个INSERT命令绑在一个事务中也是这样。
同样的事务中，COPY命令比CREATE TABLE或TRUNCATE更快。 这这些情况下，不需要写WAL，因为在一个错误的情况下，文件中新增的数据会被删掉。 然而，只有当wal_level设置为minimal（此时所有的命令必须写WAL）才会考虑这种情况。
如果你正在装载一个新创建的表，最快的方法是创建表，用COPY批量装载，然后创建表需要的任何索引。 在已存在数据的表上创建索引要比递增地更新所装载的每一行记录要快
如果你对现有表增加大量的数据，可能先删除索引，装载表，然后重新创建索引更快些。 当然，在缺少索引的期间，其它数据库用户的数据库性能将有负面的影响。 并且我们在删除唯一索引之前还需要仔细考虑清楚，因为唯一约束提供的错误检查在缺少索引的时候会消失。
和索引一样，"批量地"检查外键约束比一行行检查更高效。 因此，也许我们先删除外键约束，装载数据，然后重建约束会更高效。 同样，装载数据和缺少约束而失去错误检查之间也有一个平衡
更重要的是，当用已经存在的外键向表中导入数据时， 每个新行需要一个在服务器的待触发事件列表中的条目。载入中几百万行会导致触发事件队列溢出可用内存， 造成不能接受的交换，甚至是彻底失败的命令。因此在录入大量数据是，可能需要删除并重建外键。 如果不想临时移除约束，那唯一的办法就是讲事务分解成一个个更小的事务。
在装载大量的数据的时候，临时增大 maintenance_work_mem 配置变量可以改进性能。 这个参数也可以帮助加速CREATE INDEX和ALTER TABLE ADD FOREIGN KEY命令。 它不会对COPY本身有多大作用，所以这个建议只有在你使用上面的两个技巧时才有效
临时增大checkpoint_segments配置变量也可以让大量数据装载得更快。 这是因为向PostgreSQL里面装载大量的数据可以导致检查点操作 (由配置变量checkpoint_timeout声明)比平常更加频繁发生。 在发生一个检查点的时候，所有脏数据都必须刷新到磁盘上。 通过在大量数据装载的时候临时增加checkpoint_segments，所要求的检查点的数目可以减少。
当使用WAL归档或流复制向一个安装中录入大量数据时，在录入结束时，执行一次新的基础备份 比执行一次增量WAL更快。为了防止录入时的增量WAL，将wal_leveltominimal， archive_modetooff和max_wal_senders设置为0来禁用归档和流复制。 但需要注意的是，修改这些设置需要重启服务。
除了避免归档或处理WAL数据的WAL发送的时间之外，这样做，实际上使某些命令更快，
因为他们被这是为完全不需要写WAL（如果wal_level为minimal）。
（在最后时，相比较写WAL，运行fsync可以保证故障安全性的开销更低）
命令如下：
CREATE TABLE AS SELECT
CREATE INDEX(and variants such as ALTER TABLE ADD PRIMARY KEY)
ALTER TABLE SET TABLESPACE
CLUSTER
COPY FROM，当目的表已经创建或在这之前已删除（同一个事务中）
不管什么时候，如果你在增加或者更新了大量数据之后，运行 ANALYZE都是个好习惯。 运行ANALYZE 或者VACUUM ANALYZE可以保证规划器有表数据的最新统计。 如果没有统计数据或者统计数据太陈旧，那么规划器可能选择很差劲的查询规划，导致表的查询性能恶化。 需要注意的是，如果启用了autovacuum守护进程，也会自动运行ANALYZE； 参阅 Section 23.1.3和Section 23.1.5。
pg_dump生成的转储脚本自动使用上面的若干个技巧，但不是全部。 要尽可能快地装载pg_dump转储，我们需要手工做几个事情。 请注意，这些要点适用于恢复一个转储，而不是创建一个转储的时候。 同样的要点也适用于使用psql或pg_restore 从pg_dump归档文件装载数据的时候。
缺省的时候，pg_dump使用COPY，在它生成一个完整的模式和数据的转储的时候， 它会很小心地先装载数据，然后创建索引和外键。 因此，在这个情况下，头几条技巧是自动处理的。 你需要做的只是
在装载转储脚本之前设置比正常状况大的maintenance_work_mem和 checkpoint_segments值。
如果使用WAL归档或流复制，在转储时，可以考虑禁用这些。将archive_mode 设置为off，wal_level设置为minimal以及 max_wal_senders设置为0（在录入dump前）来实现。 最后，将它们设回正确的值，并执行一次新的基础备份。
考虑是否在一个事务中转储所有的dump。可以在psql或pg_restore 中使用-1或--single-transaction选项来实现。 当使用这个模式时，即使是一个很小的错误也会回滚所有的转储。根据数据间的相关性， 最好手动清理或不。单一事务模式下，同时关闭WAL归档，COPY命令会更快。
如果多CPU支持数据库服务，可以考虑使用pg_restore's--jobs 选项。允许并发录入数据和创建索引。
最后，运行ANALYZE。
只保存数据的转储仍然会使用COPY，但是它不会删除或者重建索引，并且它不会自动修改外键 [1] 因此，在装载只有数据的转储的时候，是否使用删除以及重建索引和外键等技巧完全取决于你。 装载数据的时候，增大checkpoint_segments仍然是有用的， 但是增大maintenance_work_mem就没什么必要了； 你只是应该在事后手工创建索引和外键的事后增大它。 最后结束时不要忘记ANALYZE 命令。 获得更多信息参考Section 23.1.3和Section 23.1.5
| [1] | 你可以通过使用--disable-triggers选项的方法获取关闭外键的效果。 不过要意识到这么做是消除，而不只是推迟违反外键约束，因此如果你使用这个选项，将有可能插入坏数据。 |