PostgreSQL批量数据加载的7个最佳实践技巧（翻译）

PostgreSQL运维技术 2021-04-26

1738

原文：

https://www.2ndquadrant.com/en/blog/7-best-practice-tips-for-postgresql-bulk-data-loading/

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

造成这种性能低下的原因有很多:索引、触发器、外键、GUID主键，甚至是写提前日志(Write Ahead Log, WAL)都可能导致延迟。

在本文中，我们将介绍一些将数据批量导入PostgreSQL数据库的最佳实践技巧。然而，在某些情况下，这些技巧可能都不是有效的解决方案。我们建议读者在使用任何方法之前考虑其利弊。

技巧1:将目标表更改为unlogged Mode

对于PostgreSQL 9.5及以上版本，可以先将目标表修改为UNLOGGED，然后在数据加载后将其修改为LOGGED:

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

Unlogger模式确保PostgreSQL不会讲表写操作发送给WAL(write ahead log)，这使得加载过程显著加快。但是由于操作没有被记录，所以如果在加载期间出现崩溃，则无法恢复数据。一旦重新启动，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)

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

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

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

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

技巧7:运行ANALYZE

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

写在最后：

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

postgresql

文章转载自PostgreSQL运维技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL批量数据加载的7个最佳实践技巧（翻译）

评论