PostgreSQL中批量导入数据的优化方法

我们常常需要向数据库中导入数据&＃xff0c;如果一次性导入大量的数据必然会对性能产生影响&＃xff0c;而且这个时间可能还会特别长。这个可能受多方面的影响&＃xff0c;例如&＃xff1a;表上存在索引、触发器、磁盘IO等等因素。

那么在PostgreSQL中有什么好的方法来处理批量导入数据这类问题呢&＃xff0c;又或者说批量导入数据时我们有哪些优化方法呢&＃xff1f;

方法1&＃xff1a;修改表为unlogged

pg9.5之后开始支持unlogged表&＃xff0c;这个和Oracle中设置表属性为nologging是一样的&＃xff1a;让指定的表上进行操作时不产生wal日志。

ALTER TABLE SET UNLOGGED

ALTER TABLE LOGGED


但是由于操作不会产生日志&＃xff0c;那么需要注意一旦数据库出现异常导致重启&＃xff0c;那么unlogged表中数据将会被清空。
这里说明下&＃xff1a;如果数据库是正常关闭的&＃xff0c;unlogged表数据是不会丢失的。

除此之外&＃xff0c;unlogged表也不会传输到备库&＃xff0c;那么就需要在数据导入前停止主备复制&＃xff0c;导入完成后再重新同步。

因此在使用unlogged表之前&＃xff0c;我们建议&＃xff1a;

• 修改表为unlogged前先进行数据备份&＃xff1b;
• 数据导入后&＃xff0c;立刻重新进行数据同步&＃xff1b;

方法2&＃xff1a;删除并重建索引

索引在批量数据插入过程中可能会造成严重延迟。 这是因为在添加每一行时&＃xff0c;相应的索引条目也必须更新。

因此我们建议在开始批量插入之前尽可能地删除目标表中的索引&＃xff0c;并在导入完成后重新创建索引。 同样&＃xff0c;在大型表上创建索引可能很耗时&＃xff0c;但通常比在导入数据期间更新索引要快。

DROP INDEX , …

CREATE INDEX ON (column1, …,column n)


在创建索引之前临时增加maintenance_work_mem配置参数。增加的工作内存可以帮助更快地创建索引。

方法3&＃xff1a;删除并重建外键

与索引一样&＃xff0c;外键约束也会影响批量负载性能。 这是因为每个插入行中的每个外键都必须检查是否存在相应的主键。 PostgreSQL中内部是使用触发器来执行检查。 所以当加载大量行时&＃xff0c;必须为每一行触发此触发器&＃xff0c;从而增加开销。

因此一般我们建议将目标表中所有外键删除&＃xff0c;在单个事务中加载数据&＃xff0c;然后在提交事务后重新创建外键。

ALTER TABLE
DROP CONSTRAINT
BEGIN TRANSACTION

COMMIT
ALTER TABLE
ADD CONSTRAINT
FOREIGN KEY ()
REFERENCES ()...


同样的&＃xff0c;增加maintenance_work_mem配置参数可以提高重新创建外键约束的性能。

方法4&＃xff1a;禁用触发器

插入或删除触发器&＃xff08;如果加载过程也涉及从目标表中删除记录&＃xff09;可能会导致批量数据加载的延迟。 这是因为每个触发器都有需要检查的逻辑和在插入或删除每一行之后需要完成的操作。

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

ALTER TABLE DISABLE TRIGGER ALL

ALTER TABLE ENABLE TRIGGER ALL


方法5&＃xff1a;使用COPY方法

使用COPY命令从一个或多个文件加载数据。COPY针对批量数据加载进行了优化。它比运行大量的插入语句&＃xff0c;甚至是多值插入&＃xff0c;更有效。pg12开始支持在copy命令中增加where过滤&＃xff0c;使用起来也更加灵活。

COPY [( column1>, … , )]
FROM &＃39;&＃39;
WITH (, , … , )


方法6&＃xff1a;使用批量插入

执行多个insert语句进行插入的性能远远不如批量插入。这是因为每个单独的insert命令都必须由查询优化器解析和准备&＃xff0c;通过所有约束检查&＃xff0c;作为一个单独的事务运行&＃xff0c;并产生WAL日志。 使用批量INSERT语句可以节省此开销。

INSERT INTO (, , …, )
VALUES
(, , …, ),
(, , …, ),
(, , …, ),
(, , …, ),
...


与之类似的是&＃xff0c;我们可以关闭pg中的自动提交&＃xff0c;来手动进行批量的提交。而不是每执行一条语句就进行一次提交&＃xff0c;而是将多条语句放在一个事务中。

这里我们可以设置commit_delay来实现&＃xff0c;指定提交状态的事务达到commit_siblings时进行一次性提交&＃xff0c;这样只需要flush一次wal日志&＃xff0c;从而减少wal io。

方法7&＃xff1a;其它注意事项

除此之外&＃xff0c;我们可以对一些参数进行优化&＃xff0c;例如&＃xff1a;设置effective_cache_size 为内存的50%&＃xff0c;设置shared_buffer为内存的25%&＃xff0c;还可以适当增加max_wal_size。

还有最后一点需要注意的是&＃xff1a;
批量导入大量数据后记得使用analyze命令来重新收集统计信息。