Postgresql 中文操作指南

使用多个 INSERT_s, turn off autocommit and just do one commit at the end. (In plain SQL, this means issuing _BEGIN 和 COMMIT 时。有些客户端库可能在您不知情的情况下进行此操作，此时您需要确保库在您希望其执行此操作时执行此操作。）如果您允许独立提交每次插入操作，PostgreSQL 会为每新增一行做大量工作。在一个事务中完成所有插入操作的另一个好处是，如果一行的插入失败，则会回滚到那一行之前插入的所有行的插入操作，这样您便不会陷入数据加载不完整的情况。

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

如果你无法使用 COPY ，使用 PREPARE 创建准备好的 INSERT 语句，然后根据需要多次使用 EXECUTE 可能会有所帮助。这避免了反复解析和规划 INSERT 的一些开销。不同的界面以不同方式提供此功能；在界面文档中查找“准备好的语句”。

请注意，即使使用了 PREPARE 并且多个插入操作分批成为一个事务，使用 COPY 加载大量行几乎总是比使用 INSERT 快。

当在与以前的 CREATE TABLE 或 TRUNCATE 命令相同的交易中使用时，COPY 最快。在这些情况下，无需写 WAL，因为出错时，将新加载的数据包含在文件中的文件将被删除。但是，这个考虑因素只有在 wal_level 是 minimal 时才适用，因为这时所有命令都必须写 WAL。

如果您要加载新创建的表，则最快的办法是创建表，使用 COPY 对表数据进行批量加载，然后创建该表所需的任何索引。在现有数据上创建索引比随着每行加载而增量更新要快。

如果您要向现有表添加大量数据，则删除索引、加载表，然后重新创建索引可能是一种解决办法。当然，在没有索引的那段时间里，其他用户的数据库性能可能会受到影响。同样，在删除唯一索引之前，您也应三思而后行，因为在索引缺失时，唯一约束所提供的错误检查功能将丢失。

正如索引一样，可以比逐行更有效地“批量”检查外键约束。因此，删除外键约束、加载数据并重新创建约束可能会很有用。同样，在约束缺失时，数据加载速度和错误检查功能的损失之间存在权衡。

此外，当您将数据加载到具有现有外键约束的表中时，每行都需要服务器挂起触发器事件列表中的条目（因为触发器触发时，会检查行的外键约束）。加载数百万行可能会导致触发器事件队列溢出可用内存，从而导致无法容忍的交换甚至导致命令彻底失败。因此，在加载大量数据时，删除并重新应用外键可能不仅仅是可取的，而且 necessary。如果暂时取消该约束不可行，则唯一的其他办法可能是将加载操作拆分成较小的事务。

在加载大量数据时，临时增加 maintenance_work_mem 配置变量可以提高性能。这将有助于加快 CREATE INDEX 命令和 ALTER TABLE ADD FOREIGN KEY 命令。它对 COPY 本身不会有太大影响，因此这个建议只在你使用上述一种或两种技术时才有用。

暂时增加 max_wal_size 配置变量也可以让加载大数据变得更快。这是因为将大量数据加载到 PostgreSQL 中会导致检查点比正常的检查点频率（由 checkpoint_timeout 配置变量指定）更频繁地发生。每当发生检查点时，所有脏页必须刷新到磁盘。通过在批量数据加载期间暂时增加 max_wal_size，可以减少所需的检查点数量。

当将大量数据加载到使用 WAL 归档或流复制的安装环境时，在加载完成后进行新基准备份可能比处理大量增量 WAL 数据的速度快。为了防止在加载过程中进行增量 WAL 记录，通过将 wal_level 设置为 minimal、 archive_mode 设置为 off 和 max_wal_senders 设置为零来禁用归档和流复制。但请注意，更改这些设置需要重新启动服务器，并使以前拍摄的任何基准备份无法用于归档恢复和备用服务器，这可能导致数据丢失。

除了避免归档器或 WAL 发送器处理 WAL 数据的时间之外，这样做实际上会加快某些命令，因为如果 wal_level 为 minimal 并且当前的子事务（或顶级事务）创建或截断了它们更改的表或索引，则它们根本不会写入 WAL。（相比通过写入 WAL，它们可以通过在末尾执行 fsync 更低成本地确保崩溃安全性。）

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

由 pg_dump 生成的转储脚本会自动应用上面几条准则（但不是全部准则）。要尽可能快地恢复 pg_dump 转储，您需要手动再做一些事情。（请注意，这些要点适用于 restoring 转储的情况，而不适用于 creating 转储的情况。无论使用 psql 加载文本转储还是使用 pg_restore 从 pg_dump 归档文件加载文本转储，都适用相同的要点。）

默认情况下，pg_dump 使用 COPY，而且当它生成一个完整的架构和数据转储时，它会在创建索引和外键之前仔细加载数据。因此，在这种情况下，有几条准则会自动得到处理。剩下的要由您做的是：

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

[14 ] يمكنك الحصول على تأثير تعطيل المفاتيح الخارجية باستخدام خيار —​disable-triggers — لكن أدرك أن هذا يلغي التحقق من المفتاح الخارجي بدلاً من تأجيله فقط، وبالتالي من الممكن إدخال بيانات سيئة إذا كنت تستخدمه.