如何加快PostgreSQL中的插入性能

Question

我正在测试博士后的插入性能。我有一个表，其中一列的数据类型是数字。上面还有一个索引。我使用以下查询填充了数据库：

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

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

在内存为5 GB的计算机上使用Windows 7 Pro。

Answer 1

使用COPY表TO...与二进制，这是根据留档是有点比文本和CSV格式快。只有当您有数百万行要插入，并且您对二进制数据感到满意时，才会这样做。

下面是Python中的一个示例配方，使用带二进制输入的psycopg2。

Answer 2

今天我在同一个问题上花了大约6个小时。插入以“常规”速度（每100K小于3秒）移动，直到最多5行（总共30行），然后性能急剧下降（一直下降到每100K 1分钟）。

我不会列出所有不起作用的事情，直接切入肉。

我在目标表（它是一个GUID）上丢弃了一个主键，我的30MI或多行以每100K不到3秒的恒定速度愉快地流向了它们的目标。

Answer 3

请参阅PostgreSQL手册中的填充数据库，depesz关于该主题的优秀文章，以及这个问题。

（请注意，这个答案是关于将数据批量加载到现有数据库或创建新数据库。如果您感兴趣的DB恢复性能与pg_restore或psql执行pg_dump输出，这在很大程度上不适用，因为pg_dump和pg_restore已经做的事情，如创建触发器和索引它完成模式数据还原）。

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

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

否则：

>

禁用表上的任何触发器

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

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

如果可能，使用COPY代替INSERTs

如果不能使用COPY，请考虑使用多值的INSERTs。你似乎已经在这么做了。不过，不要试图在一个VALUES中列出太多的值；这些值必须在内存中放几次，所以每个语句保持几百个。

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

使用synchronous_commit=off和一个巨大的commit_delay来降低fsync（）成本。不过，如果你已经把你的工作批处理成大交易，这不会有太大帮助。

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

设置一个较高的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手册中的非耐用设置。

您还应该考虑调整您的系统：

>

如果使用RAID 5或RAID 6进行直连存储，请立即停止。备份数据，将RAID阵列重组为RAID 10，然后重试。RAID 5/6对于大容量写入性能来说是没有希望的——尽管一个具有大缓存的好RAID控制器可以有所帮助。

如果您可以选择使用硬件RAID控制器和一个大的电池备份写回缓存，这可以真正提高具有大量提交的工作负载的写性能。如果您使用的是带有提交延迟的异步提交，或者在大容量加载期间执行的大型事务较少，那么这并没有多大帮助。

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

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