如何加快PostgreSQL中的插入性能

Question

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

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

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

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

Answer 1

请参阅PostgreSQL手册中的填充数据库，有关该主题的depesz出色的常规文章以及此SO问题。

（请注意，此答案是关于将数据批量加载到现有数据库中或创建一个新数据库。如果您对数据库恢复性能pg_restore或psql执行pg_dump输出感兴趣，那么该方法就不适用了，因为pg_dump它pg_restore已经做了诸如创建完成架构+数据还原后触发和索引）。

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

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

否则：

• 禁用表格上的所有触发器

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

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

• 如果可能，使用COPY代替INSERTs

• 如果不能使用，请COPY考虑使用多值INSERTs（如果可行）。您似乎已经在执行此操作。但是，不要试图在一个列表中列出 太多的 值VALUES。这些值必须多次存储在内存中，因此每个语句将其保留为几百个。

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

• 使用synchronous_commit=off和大量commit_delay减少fsync（）成本。但是，如果您将工作分批进行大笔交易，这将无济于事。

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

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

• 如果并且仅当您不介意在导入过程中系统崩溃时，将整个PostgreSQL群集（您的数据库和同一群集上的其他任何数据库）丢失而导致灾难性破坏，则可以停止Pg，set fsync=off，启动Pg，执行导入，然后（必要时）停止Pg并fsync=on再次设置。请参阅WAL配置。 如果您在PostgreSQL安装上的任何数据库中已经关心任何数据，请不要执行此操作。 如果您设置了，fsync=off您也可以设置full_page_writes=off；再次，只是记得在导入后将其重新打开，以防止数据库损坏和数据丢失。请参阅Pg手册中的非耐用设置。

您还应该考虑调整系统：

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

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

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

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