批量数据加载的最佳实践技巧

有时，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 将自动截断任何未记录的表。

此外，未记录的表不会复制到备用服务器。在这种情况下，必须在加载之前删除现有复制，并在加载之后重新创建。根据主节点的数据量和备用节点的数量，重新创建复制的时间可能会很长，并且不符合高可用性要求。

对于将数据批量插入未记录的表，我们建议采用以下最佳做法：

• 在将表和数据更改为未记录模式之前备份表和数据
• 数据加载完成后重新创建到备用服务器的任何复制
• 对可以轻松重新填充的表（例如大型查找表或维度表）使用未记录的批量插入

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

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

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

5 使用 COPY 命令

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

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 时，RAM 必须容纳大量输入值，除非有足够的可用内存，否则该过程可能会失败。

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

7 运行ANALYZE

这与提高批量数据导入性能无关，但我们强烈建议在批量导入后立即对目标表运行ANALYZE命令。大量新行将显着扭曲列中的数据分布，并导致表中任何现有统计信息都已过时。当查询优化器使用过时的统计信息时，查询性能可能会非常差。运行 ANALYZE 命令将确保更新任何现有统计信息。

最后的想法

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