如何加快 PostgreSQL 中的插入性能

Question

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

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

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

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

原文由 Luke101 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

请参阅 PostgreSQL 手册中的 填充数据库、 depesz 关于该主题的优秀文章 以及 这个 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 s

• 如果您不能使用 COPY 如果可行，请考虑使用多值 INSERT s。你似乎已经在这样做了。不要尝试在单个 VALUES 中列出 太多 值；这些值必须在内存中适应几次，所以每条语句保持在几百个。

• 将您的插入批处理到显式事务中，每个事务执行数十万或数百万次插入。 AFAIK 没有实际限制，但是通过在输入数据中标记每个批次的开始，批处理可以让您从错误中恢复。同样，您似乎已经在这样做了。

• 使用 synchronous_commit=off 和一个巨大的 commit_delay 来降低 fsync() 成本。但是，如果您将工作批量处理为大事务，这将无济于事。

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

• 设置一个高 max_wal_size 值（ checkpoint_segments 在旧版本中）并启用 log_checkpoints 。查看 PostgreSQL 日志并确保它没有抱怨检查点发生得太频繁。

• 当且仅当您不介意在导入期间系统崩溃时将整个 PostgreSQL 集群（您的数据库和同一集群上的任何其他集群）丢失为灾难性损坏，您可以停止 Pg，设置 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 ⁵⁄₆ 对批量写入性能毫无希望——尽管具有大缓存的良好 RAID 控制器可以提供帮助。

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

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

您可能还对 优化 PostgreSQL 以进行快速测试 感兴趣。

原文由 Craig Ringer 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

如果您碰巧插入带有 UUID 的列（这不 完全是 您的情况）并添加到@Dennis 答案（我还不能评论），建议不要使用 gen_random_uuid() （需要 PG 9.4 和 pgcrypto 模块）是（a很多）比 uuid_generate_v4() 快

=# explain analyze select uuid_generate_v4(),* from generate_series(1,10000);
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=11.674..10304.959 rows=10000 loops=1)
 Planning time: 0.157 ms
 Execution time: 13353.098 ms
(3 filas)

对比

=# explain analyze select gen_random_uuid(),* from generate_series(1,10000);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.274..418.137 rows=10000 loops=1)
 Planning time: 0.064 ms
 Execution time: 503.818 ms
(3 filas)

此外，这是 建议 的官方方法

笔记

如果您只需要随机生成的（版本 4）UUID，请考虑使用 pgcrypto 模块中的 gen_random_uuid() 函数。

这将 370 万行的插入时间从约 2 小时减少到约 10 分钟。

原文由 Francisco Reynoso 发布，翻译遵循 CC BY-SA 4.0 许可协议