PG导入大量数据时如何优化

在使用PostgreSQL的时候，我们某些时候会往库里插入大量数据，例如，导入测试数据，导入业务数据等等。本篇文章介绍了在导入大量数据时的一些可供选择的优化手段。可以结合自己的情况进行选择。

方法

一、关闭自动提交

关闭自动提交，并且只在每次(数据拷贝)结束的时候做一次提交。

如果允许每个插入都独立地提交，那么PostgreSQL会为所增加的每行记录做大量的处理。而且在一个事务里完成所有插入的动作的最大的好处就是，如果有一条记录插入失败， 那么，到该点为止的所有已插入记录都将被回滚，这样就不会面对只有部分数据，数据不完整的问题。

二、导入阶段不创建索引，或者导入阶段删除索引

如果你正导入一张表的数据，最快的方法是创建表，用COPY批量导入，然后创建表需要的索引。在已存在数据的表上创建索引要比递增地更新表的每一行记录要快。

如果你对现有表增加大量的数据，可以先删除索引，导入表的数据，然后重新创建索引。当然，在缺少索引的期间，其它数据库用户的数据库性能将有负面的影响。并且我们在删除唯一索引之前还需要仔细考虑清楚，因为唯一约束提供的错误检查在缺少索引的时候会消失。（慎重考虑索引带来的影响）

三、删除外键约束

和索引一样，整体地检查外键约束比检查递增的数据行更高效。所以我们也可以删除外键约束，导入表地数据，然后重建约束会更高效。

四、增大maintenance_work_mem

在装载大量的数据的时候，临时增大 maintenance_work_mem 可以改进性能。这个参数也可以帮助加速CREATE INDEX和ALTER TABLE ADD FOREIGN KEY命令。它不会对COPY本身有很大作用，但是它可以加速创建索引和外键约束。

五、单值insert改多值insert

减少SQL解析的时间。

六、关闭归档模式并降低wal日志级别

当使用WAL归档或流复制向一个安装中录入大量数据时，在导入数据结束时，执行一次新的basebackup比执行一次增量WAL更快。

为了防止录入时的增量WAL，可以将wal_level暂时调整为minimal， archive_modet关闭，max_wal_senders设置为0来禁用归档和流复制。但需修改这些设置需要重启服务。

七、增大max_wal_size

临时增大max_wal_size配置变量也可以让大量数据载入更快。这是因为向PostgreSQL中载入大量的数据将导致检查点的发生比平常（由checkpoint_timeout配置变量指定）更频繁。

发生检查点时，所有脏页都必须被刷写到磁盘上。通过在批量数据载入时临时增加max_wal_size，减少检查点的数目。

八、使用copy替代insert

COPY针对批量数据加载进行了优化。

COPY命令是为装载数量巨大的数据行优化过的；它没INSERT那么灵活，但是在大量装载数据的情况下，导致的荷载也少很多。因为COPY是单条命令，因此填充表的时候就没有必要关闭自动提交了。

如果不能使用COPY，可以使用PREPARE来创建一个预备INSERT，然后使用EXECUTE多次效率更高。这样就避免了重复分析和规划INSERT的开销。

九、禁用触发器

导入数据之前先DISABLE掉相关表上的触发器，导入完成后重新让他ENABLE。

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。