Postgres插入操作优化

Question

Postgres插入操作优化

sqlpostgresqlbulkinsert

6

我有一个通过自定义ORM向Postgres数据库生成数万个插入的脚本。如你所想，它非常慢。这只是用于开发目的，以创建虚拟数据。在 Postgres 层面上，是否有简单的优化方法可以使其更快？这是唯一正在按顺序运行且不需要线程安全的脚本。

也许我可以关闭所有锁定、安全检查和触发器等功能？只是寻找一个快速而简单的解决方案，可以大大加快此过程的速度。

谢谢。

- Scott Klarenbach

7个回答

8

如果您在生产环境中不需要这种功能，我建议您从PostgreSQL配置中关闭fsync。这将极大地加快插入速度。

永远不要在生产数据库上关闭fsync。

- jmz

1

我同意：在生产环境中永远不应该关闭fsync（除非你有一个非常可靠的电池备份控制器）。但是，将synchronous_commit = false可能会实际上改善事情，并且不会带来很大的风险。 - user330315

在我的测试环境中，synchronous_commit 没有提高速度到足以产生差异。如果我没记错的话，这将把一个 2 分钟的数据库创建和填充过程缩短了一半，但关闭 fsync 后只需 10 秒即可运行。由于我的测试数据库没有数万条记录，因此不会使用 fsync=off 将数据写入磁盘。 - jmz

3

电池备份高速缓存无法防止由于关闭fsync而造成的数据丢失！如果您在数据写入磁盘之前进行虚假fsync并在操作系统崩溃或断电后，您将会丢失数据。目前还存在问题，即即使在BBU缓存RAID控制器上进行完整页面写入也不是100％安全的。 - Scott Marlowe

6

对于数量在数百到数千的插入操作，请将它们批量处理：

begin;
insert1 ...
insert2 ...
...
insert10k ... 
commit;

如果需要进行百万级别的数据插入，请使用copy命令：

COPY test (ts) FROM stdin;
2010-11-29 22:32:01.383741-07
2010-11-29 22:32:01.737722-07
... 1Million rows
\.

如果在另一张表中作为外键使用的任何列在该表中的大小超过微不足道，请确保对其进行索引。

- Scott Marlowe

3

你可以做的一件事是删除所有索引，进行插入操作，再重新创建索引。

- Tyler Eaves

2

你是发送成千上万个INSERT语句的批处理吗？还是你只是发送了成千上万个INSERT语句？

我知道使用Hibernate，你可以将所有SQL语句进行批处理，在最后一次性发送它们，而不是单独执行成千上万条SQL语句，这样可以减少网络和数据库负担。

- karlgrz

2

如果您只是在初始化常量测试数据，您也可以将测试数据放入一个临时表中，然后使用“复制表内容”命令将表内容复制到目标表中。

INSERT INTO... SELECT...

这应该和使用COPY一样快（虽然我没有进行基准测试），但有一个优点，就是您可以仅使用SQL命令进行复制，而无需像使用COPY那样设置外部文件。

- sleske

2

尽量在一个请求中完成尽可能多的操作！

insert into my_table (col1, col2) 
values (
  unnest(array[row_1_col_value_1, row_2_col_value_1, row3_col_value_1]), 
  unnest(array[row_1_col_value_2, row_2_col_value_2, row_3_col_value_2));

这类似于 @a_horse_with_no_name 的建议。使用 unnest 的优点是：您可以使用包含数组的查询参数！

insert into my_table (col1, col2) 
values (unnest(:col_values_1), unnest(:col_values_2));

将三个insert语句合并为一个，可以节省50%以上的执行时间。使用单个Insert中的2000个查询参数，在我的应用程序中获得了150倍的速度优势。

- hagello

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

插入数据的最快方式是使用COPY命令，但这需要一个扁平文件作为输入。我猜生成扁平文件不是一个选项。

不要经常提交，特别是不要启用自动提交。听起来像是在最后进行单个提交就足够了。

如果您可以说服ORM使用Postgres的多行插入，那么速度也会加快。

这是多行插入的示例：

insert into my_table (col1, col2) 
values 
(row_1_col_value1, row_1_col_value_2), 
(row_2_col_value1, row_2_col_value_2), 
(row_3_col_value1, row_3_col_value_2)

如果您无法生成上述语法，并且正在使用Java，请确保使用批处理语句而不是单个语句插入（可能还有其他DB层允许类似的操作）。

编辑：

jmz的帖子启发了我添加一些内容：

当您将wal_buffers增加到更大的值（例如8MB）和checkpoint_segments（例如16）时，您可能还会看到改进。