如何提高PostgreSQL中的插入性能

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

#1 楼

请参阅PostgreSQL手册中的“填充数据库”，有关该主题的depesz优秀文章以及有关SO的问题。创建一个新的。如果您对使用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。查看PostgreSQL日志，并确保它没有抱怨检查点过于频繁。导入期间系统崩溃，您可以停止Pg，设置checkpoint_segments，启动Pg，进行导入，然后（一定要）停止Pg并再次设置log_checkpoints。请参阅WAL配置。如果您在PostgreSQL安装上的任何数据库中已经关心任何数据，请不要执行此操作。如果设置fsync=off，也可以设置fsync=on；再次，只是记得在导入后将其重新打开，以防止数据库损坏和数据丢失。请参阅Pg手册中的非持久性设置。

还应注意调整系统：


尽可能使用高质量的SSD进行存储。具有可靠的，受电源保护的回写式高速缓存的优质SSD可以使提交速度变得异常快。当您按照上面的建议使用时，它们的好处较小-减少了磁盘刷新次数/ fsync=off的数量-但仍然可以提供很大帮助。除非您不关心保存数据，否则不要使用没有适当电源故障保护功能的廉价SSD。
如果您将RAID 5或RAID 6用于直接连接的存储，请立即停止。备份数据，将RAID阵列重组为RAID 10，然后重试。 RAID 5/6对于大容量写入性能没有希望-尽管一个好的具有大缓存的RAID控制器可以提供帮助。确实提高了具有大量提交的工作负载的写入性能。如果您正在使用带有commit_delay的异步提交，或者在批量加载过程中执行的大型事务较少，则没有太大帮助。
如果可能，将WAL（full_page_writes=off）存储在单独的磁盘/磁盘阵列上。在同一磁盘上使用单独的文件系统毫无意义。人们经常选择对WAL使用RAID1对。同样，这对高提交率的系统有更大的影响，如果您使用未记录的表作为数据加载目标，则几乎没有影响。测试。

#2 楼

根据文档使用COPY table TO ... WITH BINARY，它“比文本和CSV格式快一些”。仅当您要插入数百万行并且对二进制数据比较满意时，才执行此操作。

#3 楼

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

我不会列出所有无效的内容，并直接切成肉。

我将主键放在了目标表（这是一个GUID）上，我的30MI或行以每100K小于3秒的恒定速度愉快地流到了目的地。

#4 楼

除了出色的Craig Ringer的帖子和depesz的博客文章之外，如果您想通过在事务中使用准备好的语句插入来通过ODBC（psqlodbc）接口加快插入操作，还需要做一些额外的事情快速工作：


通过在连接字符串中指定Protocol=-1，将错误回滚级别设置为“事务”。默认情况下，psqlodbc使用“语句”级别，该级别为每个语句而不是整个事务创建一个SAVEPOINT，从而使插入速度变慢。如果没有此选项，客户端将发送整个插入语句以及要插入的每一行。
使用UseServerSidePrepare=1禁用每个语句的自动提交

一旦所有行都插入，请使用SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);提交事务。无需显式打开事务。

不幸的是，psqlodbc通过发出一系列未准备好的插入语句来“实现” SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);，因此要实现最快的插入，需要手动编写上述步骤。

#5 楼

如果您碰巧要插入带有UUID的列（这并非您的情况），并添加到@Dennis答案（我尚无法评论），那么建议不要使用gen_random_uuid（）（需要PG 9.4和pgcrypto模块）很多）比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)

vs

=# 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（）函数。

对于3.7M行，这将插入时间从〜2小时缩短为〜10分钟。

#6 楼

为了获得最佳的插入性能，请禁用索引。除此之外，更好的硬件（磁盘，内存）也很有帮助

#7 楼

我也遇到了这种插入性能问题。我的解决方案是生成一些go例程以完成插入工作。同时，应给SetMaxOpenConns一个适当的编号，否则将警告过多的打开连接错误。

db, _ := sql.open() 
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
var wg sync.WaitGroup
for _, query := range queries {
    wg.Add(1)
    go func(msg string) {
        defer wg.Done()
        _, err := db.Exec(msg)
        if err != nil {
            fmt.Println(err)
        }
    }(query)
}
wg.Wait()

我的项目的加载速度要快得多。该代码段只是说明了它是如何工作的。读者应该能够轻松地对其进行修改。