sql - 如何加快PostgreSQL中的插入性能

我正在测试Postgres插入性能。我有一张表，其中一列以数字作为其数据类型。也有一个索引。我使用以下查询填充数据库:

insert into aNumber (id) values (564),(43536),(34560) ...

通过上面的查询，我一次非常快地一次插入了400万行10,000。数据库达到600万行后，性能每15分钟急剧下降到100万行。有什么技巧可以提高插入性能？我需要此项目的最佳插入性能。

在具有5 GB RAM的计算机上使用Windows 7 Pro。

最佳答案

请参见PostgreSQL手册中的populate a database，关于该主题的depesz's excellent-as-usual article和this SO question。

(请注意，此答案是关于将数据批量加载到现有数据库中或创建新数据库的。如果您有兴趣通过pg_restore输出或psql执行pg_dump输出来恢复数据库性能，那么由于pg_dump和pg_restore，其中的大部分内容都不适用已经完成了架构+数据还原后创建了触发器和索引之类的操作)。

有很多事情要做。理想的解决方案是将没有索引的表导入到UNLOGGED表中，然后将其更改为已记录并添加索引。不幸的是，在PostgreSQL 9.4中，不支持将表从UNLOGGED更改为已记录。 9.5添加ALTER TABLE ... SET LOGGED允许您执行此操作。

如果您可以使数据库脱机以进行批量导入，请使用 pg_bulkload 。

除此以外:

禁用表

上的所有触发器

在开始导入之前删除索引，然后重新创建它们。 (一次建立索引所花费的时间要比向其逐步添加相同数据所花费的时间少得多，并且所产生的索引要紧凑得多)。

如果在单个事务中进行导入，则可以安全地删除外键约束，进行导入并在提交之前重新创建约束。如果导入分散在多个事务中，请不要执行此操作，因为这可能会引入无效数据。

如果可能，请使用COPY而不是INSERT的

如果不能使用COPY，请考虑使用多值INSERT，如果可行的话。您似乎已经在这样做了。但是，不要尝试在单个VALUES中列出太多值；这些值必须多次存储在内存中，因此每个语句将其保留为几百个。

将插入内容批处理到显式事务中，每个事务执行数十万或数百万个插入。 AFAIK没有实际限制，但是批处理可通过在输入数据中标记每个批处理的开始来使您从错误中恢复。同样，您似乎已经在执行此操作。

使用synchronous_commit=off和大量的commit_delay可以降低fsync()的成本。但是，如果您将工作分批进行大笔交易，这将无济于事。

几个连接并行的

INSERT或COPY。多少取决于您的硬件的磁盘子系统；根据经验，如果使用直接连接的存储，则每个物理硬盘驱动器需要一个连接。

设置较高的checkpoint_segments值并启用log_checkpoints。查看PostgreSQL日志，并确保它没有抱怨检查点发生得太频繁。

如果且仅当您不介意在导入过程中系统崩溃时，将整个PostgreSQL集群(您的数据库和同一集群上的其他任何数据库)丢失而导致灾难性损坏时，您可以停止Pg，设置fsync=off，启动Pg，然后执行导入，然后(主要)停止Pg并再次设置fsync=on。参见WAL configuration。 如果您在PostgreSQL安装上的任何数据库中已经有任何数据需要关注，请不要执行此操作。 如果设置fsync=off，也可以设置full_page_writes=off；再次，只是记得在导入后将其重新打开，以防止数据库损坏和数据丢失。请参见Pg手册中的non-durable settings。

您还应该考虑调整系统:

尽可能使用高质量的SSD进行存储。具有可靠的，受电源保护的回写式高速缓存的优质SSD可以使提交速度变得异常快。当您按照上面的建议使用时，它们的作用较小-减少了磁盘刷新次数/ fsync()的数量-但仍然可以提供很大帮助。除非您不关心保留数据，否则请不要使用没有适当电源故障保护功能的廉价SSD。

如果您将RAID 5或RAID 6用于直接连接的存储，请立即停止。备份数据，将RAID阵列重组为RAID 10，然后重试。 RAID 5/6对于批量写入性能没有希望-尽管具有良好缓存的良好RAID Controller 可以提供帮助。

如果您可以选择使用具有大型电池后备回写缓存的硬件RAID Controller ，则可以确实提高具有大量提交的工作负载的写入性能。如果您正在使用带有commit_delay的异步提交，或者在批量加载过程中执行的大型事务较少，则没有太大帮助。

如果可能，将WAL(pg_xlog)存储在单独的磁盘/磁盘阵列上。在同一磁盘上使用单独的文件系统毫无意义。人们经常选择对WAL使用RAID1对。同样，这对具有高提交率的系统有更大的影响，并且如果您使用未记录的表作为数据加载目标，则几乎没有影响。

您可能也对Optimise PostgreSQL for fast testing感兴趣。