PostgreSQL 教程

Appearance

14.4. 填充数据库# ​

14.4.1. 禁用自动提交
14.4.2. 使用 COPY
14.4.3. 删除索引
14.4.4. 删除外键约束
14.4.5. 增加 maintenance_work_mem
14.4.6. 增加 max_wal_size
14.4.7. 禁用 WAL 归档和流复制
14.4.8. 之后运行 ANALYZE
14.4.9. 有关 pg_dump 的一些说明

首次填充数据库时，可能需要插入大量数据。本节包含一些有关如何尽可能高效地完成此过程的建议。

14.4.1. 禁用自动提交# ​

使用多个INSERT时，关闭自动提交，并在最后提交一次即可。（在纯 SQL 中，这意味着在开始时发出BEGIN，在结束时发出COMMIT。一些客户端库可能会在您不知情的情况下执行此操作，在这种情况下，您需要确保库在您希望执行此操作时执行此操作。）如果您允许每个插入单独提交，PostgreSQL会对添加的每一行执行大量工作。一次事务中完成所有插入的另一个好处是，如果插入一行失败，则会回滚到该点之前插入的所有行，因此您不会陷入部分加载数据的困境。

14.4.2. 使用COPY# ​

使用COPY加载一条命令中的所有行，而不是使用一系列INSERT命令。COPY命令针对加载大量行进行了优化；它不如INSERT灵活，但对于大数据加载产生的开销要小得多。由于COPY是一条命令，因此如果您使用此方法填充表，则无需禁用自动提交。

如果您无法使用COPY，则可以使用PREPARE创建准备好的INSERT语句，然后根据需要多次使用EXECUTE。这避免了重复解析和规划INSERT的一些开销。不同的接口以不同的方式提供此功能；在接口文档中查找“prepared statements”。

请注意，即使使用了PREPARE并且将多个插入批处理到单个事务中，使用COPY加载大量行几乎总是比使用INSERT更快。

COPY在与早期的CREATE TABLE或TRUNCATE命令相同的交易中使用时最快。在这种情况下，无需写入 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配置变量也可以使大型数据加载更快。这是因为将大量数据加载到PostgreSQL中会导致检查点比正常检查点频率（由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而不是通过写入 WAL 来更经济地保证崩溃安全性。）

14.4.8. 之后运行ANALYZE# ​

每当您显著更改表内的数据分布时，强烈建议运行ANALYZE。这包括将大量数据批量加载到表中。运行ANALYZE（或VACUUM ANALYZE）可确保规划器拥有有关该表的最新统计信息。如果没有统计信息或统计信息已过时，则规划器在查询规划期间可能会做出错误的决策，从而导致任何具有不准确或不存在的统计信息的表性能不佳。请注意，如果启用了自动清理守护程序，它可能会自动运行ANALYZE；有关更多信息，请参见第 25.1.3 节和第 25.1.6 节。

14.4.9. 有关pg_dump的一些说明# ​

pg_dump生成的转储脚本会自动应用上述准则中的几条，但不是全部。要尽可能快地恢复pg_dump转储，您需要手动执行一些额外操作。（请注意，这些要点适用于在恢复转储时，而不适用于在创建转储时。无论使用psql加载文本转储还是使用pg_restore从pg_dump存档文件加载，这些要点都适用。）

默认情况下，pg_dump使用COPY，并且在生成完整的架构和数据转储时，它会在创建索引和外键之前小心加载数据。因此，在这种情况下，会自动处理几条准则。您需要做的是

  • 为 maintenance_work_mem 和 max_wal_size 设置适当的值（即大于正常值）。

  • 如果使用 WAL 归档或流复制，请考虑在恢复期间禁用它们。为此，在加载转储之前，将 archive_mode 设置为 off，将 wal_level 设置为 minimal，将 max_wal_senders 设置为零。之后，将它们设置回正确的值并进行新的基本备份。

  • 尝试 pg_dump 和 pg_restore 的并行转储和恢复模式，并找到要使用的最佳并发作业数。通过 -j 选项并行转储和恢复应该比串行模式为您提供显著更高的性能。

  • 考虑是否应将整个转储恢复为单个事务。为此，将 -1 或 --single-transaction 命令行选项传递给 psql 或 pg_restore。使用此模式时，即使是最小的错误也会回滚整个恢复，可能丢弃许多小时的处理。根据数据的相互关联程度，这可能比手动清理更可取，也可能不可取。COPY 命令在使用单个事务并关闭 WAL 归档时运行得最快。

  • 如果数据库服务器中有多个 CPU 可用，请考虑使用 pg_restore 的 --jobs 选项。这允许并发数据加载和索引创建。

  • 之后运行 ANALYZE。

仅数据转储仍将使用COPY，但它不会删除或重新创建索引，并且通常不会触及外键。[14]因此，在加载仅数据转储时，如果您希望使用这些技术，则由您删除并重新创建索引和外键。在加载数据时增加max_wal_size仍然很有用，但不要费心增加maintenance_work_mem；相反，您可以在之后手动重新创建索引和外键时执行此操作。并且不要忘记在完成后ANALYZE；请参阅第 25.1.3 节和第 25.1.6 节以获取更多信息。

[14]您可以通过使用--disable-triggers选项来获得禁用外键的效果——但请意识到这会消除外键验证，而不仅仅是推迟，因此如果您使用它，可能会插入错误的数据。