PostgreSQL数据库导入大量数据时如何优化
PostgreSQL数据库导入大量数据时如何优化
来源 | OSCHINA 社区、作者 | PostgreSQLChina
链接：https://my.oschina.net/postgresqlchina/blog/5568852
在使用 PostgreSQL 的时候，我们某些时候会往库里插入大量数据，例如，导入测试数据，导入业务数据等等。本篇文章介绍了在导入大量数据时的一些可供选择的优化手段。可以结合自己的情况进行选择。
一、关闭自动提交
关闭自动提交，并且只在每次 (数据拷贝) 结束的时候做一次提交。
如果允许每个插入都独立地提交，那么 PostgreSQL 会为所增加的每行记录做大量的处理。而且在一个事务里完成所有插入的动作的最大的好处就是，如果有一条记录插入失败， 那么，到该点为止的所有已插入记录都将被回滚，这样就不会面对只有部分数据，数据不完整的问题。
postgres=# \echo :AUTOCOMMITonpostgres=# \set AUTOCOMMIT offpostgres=# \echo :AUTOCOMMIToff二、导入阶段不创建索引，或者导入阶段删除索引
如果你正导入一张表的数据，最快的方法是创建表，用 COPY 批量导入，然后创建表需要的索引。在已存在数据的表上创建索引要比递增地更新表的每一行记录要快。
如果你对现有表增加大量的数据，可以先删除索引，导入表的数据，然后重新创建索引。当然，在缺少索引的期间，其它数据库用户的数据库性能将有负面的影响。并且我们在删除唯一索引之前还需要仔细考虑清楚，因为唯一约束提供的错误检查在缺少索引的时候会消失。（慎重考虑索引带来的影响）
三、删除外键约束
和索引一样，整体地检查外键约束比检查递增的数据行更高效。所以我们也可以删除外键约束，导入表地数据，然后重建约束会更高效。
我们创建了一个高质量的技术交流群，与优秀的人在一起，自己也会优秀起来，赶紧点击加群，享受一起成长的快乐。
四、增大 maintenance_work_mem
在装载大量的数据的时候，临时增大 maintenance_work_mem 可以改进性能。这个参数也可以帮助加速 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY 命令。它不会对 COPY 本身有很大作用，但是它可以加速创建索引和外键约束。
postgres=# show maintenance_work_mem; maintenance_work_mem---------------------- 64MB(1 row)五、单值 insert 改多值 insert
减少 SQL 解析的时间。
六、关闭归档模式并降低 wal 日志级别
当使用 WAL 归档或流复制向一个安装中录入大量数据时，在导入数据结束时，执行一次新的 basebackup 比执行一次增量 WAL 更快。
为了防止录入时的增量 WAL，可以将 wal_level 暂时调整为 minimal， archive_modet 关闭，max_wal_senders 设置为 0 来禁用归档和流复制。但需修改这些设置需要重启服务。
postgres=# show wal_level; wal_level----------- minimal(1 row)
postgres=# show archive_mode; archive_mode-------------- off(1 row)
postgres=# show max_wal_senders; max_wal_senders----------------- 0(1 row)七、增大 max_wal_size
临时增大 max_wal_size 配置变量也可以让大量数据载入更快。这是因为向 PostgreSQL 中载入大量的数据将导致检查点的发生比平常（由 checkpoint_timeout 配置变量指定）更频繁。
发生检查点时，所有脏页都必须被刷写到磁盘上。通过在批量数据载入时临时增加 max_wal_size，减少检查点的数目。
postgres=# show max_wal_size; max_wal_size-------------- 1GB(1 row)八、使用 copy 替代 insert
COPY 针对批量数据加载进行了优化。
COPY 命令是为装载数量巨大的数据行优化过的；它没 INSERT 那么灵活，但是在大量装载数据的情况下，导致的荷载也少很多。因为 COPY 是单条命令，因此填充表的时候就没有必要关闭自动提交了。
如果不能使用 COPY，可以使用 PREPARE 来创建一个预备 INSERT，然后使用 EXECUTE 多次效率更高。这样就避免了重复分析和规划 INSERT 的开销。
九、禁用触发器
导入数据之前先 DISABLE 掉相关表上的触发器，导入完成后重新让他 ENABLE。
ALTER TABLE tab_1 DISABLE TRIGGER ALL;导入数据ALTER TABLE tab_1 ENABLE TRIGGER ALL;十、相关导数工具：pg_bulkload
pg_bulkload 是 PostgreSQL 的一个高速数据加载工具，相对于 copy 命令。最大的优势是速度。在 pg_bulkload 的直接模式下，它将跳过共享缓冲区和 WAL 缓冲区，直接写入文件。它还包括数据恢复功能，可在导入失败时进行恢复。
地址：https://github.com/ossc-db/pg_bulkload
十一、导入数据后，使用 analyze
运行 ANALYZE 或者 VACUUM ANALYZE 可以保证规划器有表数据的最新统计。
如果没有统计数据或者统计数据太陈旧，那么规划器可能选择性能很差的执行计划，导致表的查询性能较差。
我们创建了一个高质量的技术交流群，与优秀的人在一起，自己也会优秀起来，赶紧点击加群，享受一起成长的快乐。另外，如果你最近想跳槽的话，年前我花了2周时间收集了一波大厂面经，节后准备跳槽的可以点击这里领取！
推荐阅读
··································
你好，我是程序猿DD，10年开发老司机、阿里云MVP、腾讯云TVP、出过书创过业、国企4年互联网6年。从普通开发到架构师、再到合伙人。一路过来，给我最深的感受就是一定要不断学习并关注前沿。只要你能坚持下来，多思考、少抱怨、勤动手，就很容易实现弯道超车！所以，不要问我现在干什么是否来得及。如果你看好一个事情，一定是坚持了才能看到希望，而不是看到希望才去坚持。相信我，只要坚持下来，你一定比现在更好！如果你还没什么方向，可以先关注我，这里会经常分享一些前沿资讯，帮你积累弯道超车的资本。
腾讯云开发者
