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

一、关闭自动提交

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

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

postgres=#  echo :AUTOCOMMITon

postgres=# 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 可以保证规划器有表数据的最新统计。

如果没有统计数据或者统计数据太陈旧，那么规划器可能选择性能很差的执行计划，导致表的查询性能较差。