数据库的应用开发者和 DBA，在日常工作中，经常都会要进行数据的批量导入。而导入的过程往往非常耗时，既可能对业务系统的运行产生影响，同时也会影响工作效率。

背景

有时，PostgreSQL 数据库需要通过单个或最少的步骤，来导入大量数据。这通常称为批量数据导入，其中数据源通常是一个或多个大文件。这个过程有时可能会慢得令人无法接受。

导致性能如此糟糕的原因有很多，例如：索引、触发器、外键、GUID 主键，甚至预写式日志（WAL）也可能导致延迟。

在本文中，我们将介绍将数据批量导入 PostgreSQL 数据库的一些最佳实践技巧。但是，在某些情况下，这些方法也可能都不是那么有效。我们建议您在应用任何方法之前，先考虑好它的优缺点。

方法 1: 将目标表更改为 UNLOGGED 模式

对于 PostgreSQL 9.5 及更高版本，可以先将目标表更改为 UNLOGGED，然后在加载完数据后将其更改回 LOGGED：

ALTER TABLE <target table> SET UNLOGGED
<bulk data insert operations…>
ALTER TABLE <target table> LOGGED

UNLOGGED 模式可确保 PostgreSQL 不会将表的写入操作记录到预写式日志（WAL）。这可以使加载过程非常快。但是，由于未记录操作日志，因此，如果在加载期间服务器发生崩溃或不正常的停机，则无法恢复数据。PostgreSQL 将在重新启动后自动截断任何 UNLOGGED 模式的表。

此外，UNLOGGED 模式的表不会同步到备用服务器。在这种情况下，必须在加载之前删除现有的复制，并在加载后重新创建现有复制。根据主节点中的数据量和备用节点的数量，重新创建复制的时间可能相当长，并且无法满足高可用的要求。

我们建议采用以下最佳实践，将数据批量插入到 UNLOGGED 模式的表中：

• ? 在将表和数据更改为 UNLOGGED 模式之前对其进行备份

• ? 数据加载完成后，重新创建到备用服务器的任何复制

• ? 对可以轻松重新填充的表（例如，大型查找表或维度表），才使用 UNLOGGED 模式的批量插入

方法 2: 删除并重新创建索引

现有索引可能会导致批量数据插入期间出现严重延迟。这是因为在添加每一行时，相应的索引记录也必须更新。

我们建议在开始批量插入之前，尽可能删除目标表中的索引，并在加载完成后重新创建索引。同样，在大型表上创建索引可能很耗时，但通常比在加载期间更新索引更快。

DROP INDEX <index_name1>, <index_name2> … <index_name_n>
<bulk data insert operations…>
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)

在创建索引之前，临时调大 maintenance_work_mem 配置参数可能是值得的。增加的工作内存有助于更快地创建索引。

另一个安全的措施是，在同一数据库中创建目标表的副本，其中包含现有数据和索引。然后，可以使用这个新复制的表，对批量插入测试两种情况：删除并重新创建索引，或动态更新索引。然后，就可以将性能验证更好的方法应用到生产表上面。

方法 3: 删除并重新创建外键

与索引一样，外键约束也会影响批量加载性能。这是因为必须检查每个插入行中的每个外键是否存在相应的主键。在后台，PostgreSQL 使用触发器来执行检查。加载大量行时，必须为每行触发此触发器，这会增加开销。

除非受业务规则限制，否则我们建议从目标表中删除所有外键，在单个事务中加载数据，然后在提交事务后重新创建外键。

ALTER TABLE <target_table>
    DROP CONSTRAINT <foreign_key_constraint>

BEGIN TRANSACTION
    <bulk data insert operations…>
COMMIT

ALTER TABLE <target_table>
    ADD CONSTRAINT <foreign key constraint>
    FOREIGN KEY (<foreign_key_field>)
    REFERENCES <parent_table>(<primary key field>)...

同样，调大 maintenance_work_mem 配置参数，可以提高重新创建外键约束的性能。

方法 4: 禁用触发器

INSERT 或 DELETE 触发器（如果加载过程还涉及从目标表中删除记录）可能会导致批量数据加载延迟。这是因为，每个触发器在每行被 INSERT 或 DELETE 后，都有需要检查的逻辑和需要立即完成的操作。

我们建议，在批量加载数据之前禁用目标表中的所有触发器，并在加载完成后启用它们。禁用的所有触发器也包括强制执行外键约束检查的内部触发器。

ALTER TABLE <target table> DISABLE TRIGGER ALL
<bulk data insert operations…>
ALTER TABLE <target table> ENABLE TRIGGER ALL

方法 5: 使用 COPY 命令

我们建议使用 PostgreSQL 的 COPY 命令，从一个或多个文件加载数据。COPY 针对批量数据加载进行了优化。它比运行大量 INSERT 语句或者多行 INSERT 都要更加高效。

COPY <target table> [( column1>, … , <column_n>)]
    FROM  '<file_name_and_path>'
    WITH  (<option1>, <option2>, … , <option_n>)

使用 COPY 的其他好处包括：

• ? 它支持文本和二进制文件导入

• ? 它天然是事务性的

• ? 它允许指定输入文件的结构

• ? 它可以使用 WHERE 子句有条件地加载数据

方法 6: 使用多行 INSERT

对于批量数据加载来说，运行几千或几十万个 INSERT 语句，可能是一个糟糕的选择。这是因为，每个单独的 INSERT 命令都必须由查询优化器解析和准备，完成所有约束检查，作为单独的事务运行，并记录在 WAL 中。使用多行的单个 INSERT 语句可以节省此开销。

INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>) 
VALUES 
    (<value a>, <value b>, …, <value x>),
    (<value 1>, <value 2>, …, <value n>),
    (<value A>, <value B>, …, <value Z>),
    (<value i>, <value ii>, …, <value L>),
    ...

多行 INSERT 的性能受现有索引的影响。我们建议在运行命令之前删除索引，然后在运行之后重新创建索引。

另一个需要注意的方面是，PostgreSQL 可用于运行多行 INSERT 的内存大小。当运行多行 INSERT 时，内存中必须要容纳大量的输入值，除非有足够的可用内存，否则该过程可能会失败。

我们建议将 effective_cache_size 参数设置为机器总内存的 50%，shared_buffer 参数设置为总内存的 25%。此外，为了安全起见，最好运行一系列的多行 INSERT，每条语句都有 1000 行的值。

方法 7: 运行 ANALYZE

这与提高批量数据导入性能无关，但我们强烈建议，在批量导入后立即对目标表运行 ANALYZE 命令。大量新行会显著改变列中的数据分布，并导致表上的任何现有统计信息过时。当查询优化器使用过时的统计信息时，查询性能可能会差得令人无法接受。运行 ANALYZE 命令，可以确保任何现有的统计信息得到更新。

最后的思考

数据库应用程序可能并非每天都要进行批量数据导入，但运行时会对查询的性能产生影响。这就是为什么有必要尽可能减少加载时间的原因。为了最大限度地减少任何意外，DBA 可以做的一件事是，在具有类似服务器规格和 PostgreSQL 配置的开发环境或灰度环境中，测试负载的优化效果。每个数据加载方案都是不同的，最好尝试下每种方法，并找出最有效的方法。