我正在测试Postgres的插入性能。我有一个只有一个数字列的表。它也有一个索引。我使用以下查询填充了数据库：
insert into aNumber (id) values (564),(43536),(34560) ...
我使用上述查询一次性插入了10000行，共快速插入了400万行。然而，当数据库达到600万行时，性能显著下降，每15分钟只能插入100万行。是否有什么技巧可以提高插入性能？我需要在这个项目中实现最佳插入性能。
我使用的是Windows 7 Pro操作系统，5GB内存的机器。
pg_restore或psql执行pg_dump输出进行DB还原性能感兴趣，那么大部分内容都不适用，因为pg_dump和pg_restore已经在完成模式+数据还原后执行了创建触发器和索引等操作。
有很多工作需要完成。理想的解决方案是将数据导入到一个没有索引的UNLOGGED表中，然后将其更改为已记录并添加索引。不幸的是，在PostgreSQL 9.4中不支持将表从UNLOGGED更改为已记录。9.5添加了ALTER TABLE ... SET LOGGED以允许您执行此操作。
如果可以将数据库离线进行批量导入，请使用pg_bulkload。
否则：
禁用表上的任何触发器
在开始导入之前删除索引，之后重新创建它们。（一次性构建索引比逐步添加相同数据要快得多，并且生成的索引更加紧凑）
如果在单个事务中执行导入，则可以安全地删除外键约束，在提交之前重新创建约束。如果将导入拆分到多个事务中，则不要这样做，因为可能会引入无效数据。
如果可能，请使用COPY而不是INSERT
如果无法使用COPY，请考虑使用多值INSERT（如果可行）。您似乎已经在这样做了。但是不要尝试在单个VALUES中列出太多的值；这些值必须多次适合内存，因此将其保留在每个语句中只有几百个。
将插入批处理成显式事务，每个事务执行数十万或数百万个插入操作。据我所知，没有实际限制，但是批处理将允许您通过在输入数据中标记每个批次的开始来从错误中恢复。您似乎已经在这样做了。
使用synchronous_commit=off和巨大的commit_delay来减少fsync()成本。但是，如果您将工作分批到大型事务中，则不会有太大帮助。
从多个连接并行插入或复制。具体取决于硬件磁盘子系统；根据经验法则，如果使用直接附加存储，则每个物理硬盘需要一个连接。
设置高的max_wal_size值（旧版本中的checkpoint_segments）并启用log_checkpoints。查看PostgreSQL日志，并确保它没有抱怨检查点发生得太频繁。
仅当您不介意在导入期间系统崩溃时失去整个PostgreSQL集群（您的数据库和同一集群上的任何其他数据库）时，才可以停止Pg，设置fsync = off，然后（至关重要）停止Pg并再次设置fsync = on。请参见WAL配置。如果在您的PostgreSQL安装中已经存在任何您关心的数据，请勿执行此操作。如果设置了fsync = off，您还可以将full_page_writes = off；同样，只需记住在导入后将其打开以防止数据库损坏和数据丢失。请参见Pg手册中的非持久设置。
尽可能使用质量好的SSD作为存储。可靠且带有电源保护的写回缓存的好SSD可以使提交速度变得非常快。如果您遵循上面的建议（减少磁盘刷新/ fsync()数量），它们的效果会更小，但仍然可以大有帮助。如果您不在意数据保留，请勿使用没有适当电源故障保护的廉价SSD。
如果您正在使用RAID 5或RAID 6进行直接附加存储，请立即停止。备份您的数据，重新构建RAID阵列到RAID 10，然后再试一次。 RAID 5/6对于大量写入性能是无望的 - 尽管具有大缓存的良好RAID控制器可以提供帮助。
如果您有使用大容量电池支持的写回缓存的硬件RAID控制器的选项，则可以真正改善具有大量提交的工作负载的写入性能。如果您正在使用带有commit_delay的异步提交，或者在批量加载期间执行较少的大型事务，则帮助效果不会太大。
如果可能，请将WAL（pg_wal或旧版本中的pg_xlog）存储在单独的磁盘/磁盘阵列上。在同一磁盘上使用单独的文件系统没有什么意义。人们经常选择使用RAID1对WAL进行备份。同样，这对于提交率高的系统有更多影响，在使用未记录的表作为数据加载目标时影响很小。
您可能也对优化PostgreSQL以进行快速测试感兴趣。
UNLOGGED 实现了明显的速度提升吗？快速测试显示大约有10-20%的改善。 - serg今天我花了大约6个小时在同一个问题上。插入速度一直都很快(每10万条记录不到3秒)，直到总共有300万行数据中的前500万行，然后性能急剧下降(每10万条记录需要1分钟)。
这里不罗列所有无效方法，直接进入主题。
我删除了目标表上的一个主键(该主键为GUID)，我的300万条记录顺利地以不到每10万条记录3秒的恒定速度流向它们的目标地点。
COPY table TO ... WITH BINARY，根据文档说明 "比文本和CSV格式略快"。只有在插入数百万行并且您熟悉二进制数据时才这样做。json或jsonb列中的相关性有多大吗？ - p13rr0mProtocol=-1将回滚级别设置为“事务”。“psqlodbc”默认使用“语句”级别，每个语句创建一个保存点而不是整个事务，从而使插入变慢。UseServerSidePrepare=1来使用服务器端准备的语句。没有这个选项，客户端会发送整个插入语句以及每个被插入的行。SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0)禁用每个语句上的自动提交。SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT)提交事务。没有必要明确打开事务。不幸的是，“psqlodbc”通过发出一系列未经准备的插入语句来“实现”SQLBulkOperations，因此为了实现最快的插入，需要手动编写上述步骤。
A8=30000000也应该用于加速插入。 - Andrus如果你碰巧插入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)
对决
=# 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分钟。
如果可以的话，请禁用索引以获得最佳插入性能。除此之外，更好的硬件（磁盘、内存）也会有所帮助。
我也遇到了这个插入性能问题。我的解决方案是生成一些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()
我的项目加载速度快多了。这段代码片段仅提供了它如何工作的想法。读者应该能够轻松地修改它。