导入大量数据时如何优化PostgreSQL数据库？

我们在使用PostgreSQL的时候，有时会导入很多数据到库里中，比如导入测试数据，导入业务数据等。本文介绍导入大量数据时可以使用的优化方法。您可以根据自己的情况进行选择。

1。关闭自动上传

停止自动上传并在时间结束时重新启动一次（复制数据）。
如果允许每次插入独立完成，PostgreSQL 将为添加的每一行做大量处理。在一个事务中完成所有输入操作的主要优点是，如果无法加载某个文件，则将回滚到该点加载的所有文件，从而仅回滚部分数据。数据不完整的问题。

postgres=#  \echo :AUTOCOMMIT
on
postgres=# \set AUTOCOMMIT off
postgres=#  \echo :AUTOCOMMIT
off

2。导入过程中不要创建索引，或者导入过程中删除索引

如果是从表导入数据，最快的方法是创建表，使用COPY批量导入，然后创建需要的索引桌子。在已经有数据的表上创建索引比逐一更新表的每一行要快。
如果向现有表添加大量数据，可以删除索引，导入表数据，然后重新创建索引。当然，在缺少索引期间，数据库的性能将会受到负面影响。而且在删除唯一指针之前我们需要仔细考虑，因为当找不到指针时，唯一约束提供的错误检查就会消失。 （密切关注索引的效果）

3.删除外键约束

与索引一样，外键约束通常控制起来比跟踪附加数据更有效。所以我们也可以删除外键约束，导入表数据，然后重建约束，这样会更加高效。

4。增大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)

5。将单值插入更改为多值插入

减少 SQL 解析时间。

6。关闭存储模式并减少 WAL 日志大小

当使用 WAL 存储或流式复制将大量数据加载到安装中时，在导入数据结束时，执行新的数据库备份比执行新的数据库备份更昂贵。增量 WAL。迅速地。
为了防止登录过程中 WAL 增长，可以暂时将 wal_level 调整为最小值，禁用 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)

7。增加 max_wal_size 的大小

暂时增加 max_wal_size 变量可能会更快地加载更多数据。这是因为向 PostgreSQL 输入大量数据将导致检查点比平时更频繁地出现（由 checkpoint_timeout 变量确定）。
当存在检查点时，所有脏页必须刷新到磁盘。通过在数据库安装过程中临时增加 max_wal_size 来减少检查点的数量。

postgres=# show max_wal_size;
 max_wal_size 
--------------
 1GB
(1 row)

8。使用复制代替插入

COPY 最好用于插入数据。
COPY 命令最适合放置多行；它不像 INSERT 那样灵活，但在插入大量数据时会产生更高的负载。由于COPY是单个命令，因此加载表时无需关闭自动加载。

如果无法使用COPY，可以使用PREPARE预先创建一个INSERT，并重复使用EXECUTE，效率更高。这避免了重复解析和 INSERT 规划的开销。

9。禁用触发器

在导入数据前禁用相关表上的触发器，导入完成后重新启动。

ALTER TABLE tab_1 DISABLE TRIGGER ALL; 
导入数据
ALTER TABLE tab_1 ENABLE TRIGGER ALL;

10。相关衍生工具：pg_bulkload

pg_bulkload是PostgreSQL的高速数据加载工具，与复制命令相关。最大的优点是速度。 在pg_bulkload的直接模式下，它将跳过共享缓冲区和WAL缓冲区，直接写入文件。它还包括导入失败时的数据恢复功能。
地址：https://github.com/ossc-db/pg_bulkload

11。导入数据后，使用analyze

运行ANALYZE或VACUUM ANALYZE，以确保调度程序拥有表数据的最新统计信息。
如果没有统计或者统计已经统计完，发起人可能会选择无效的生产计划，导致查询表困难。