如何加快PostgreSQL中的插入性能

(注意，这个答案是关于将数据批量加载到现有的数据库或创建一个新的数据库。如果你感兴趣的数据库恢复性能与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()的开销。这不会帮助太多，如果你已经批处理你的工作成大交易，虽然。
>从多个连接并行插入或复制。有多少取决于您的硬件的磁盘子系统;作为经验法则，如果使用直接连接存储，您需要每个物理硬盘驱动器一个连接。
>设置一个高的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控制器与大缓存可以帮助。
>如果您可以选择使用带有大电池备份缓存的硬件RAID控制器，这可以真正提高具有大量提交的工作负载的写入性能。如果你使用async commit和commit_delay，或者如果你在批量加载过程中减少了很多大事务，那么它不会帮助太多。
>如果可能，将WAL(pg_xlog)存储在单独的磁盘/磁盘阵列上。在同一个磁盘上使用单独的文件系统没有什么意义。人们经常选择对WAL使用RAID1对。同样，这对具有高提交率的系统有更多的影响，如果您使用未记录的表作为数据加载目标，它几乎没有影响。

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