我有一个通过自定义ORM向Postgres数据库生成数万个插入的脚本。如你所想,它非常慢。这只是用于开发目的,以创建虚拟数据。在 Postgres 层面上,是否有简单的优化方法可以使其更快?这是唯一正在按顺序运行且不需要线程安全的脚本。
也许我可以关闭所有锁定、安全检查和触发器等功能?只是寻找一个快速而简单的解决方案,可以大大加快此过程的速度。
谢谢。
也许我可以关闭所有锁定、安全检查和触发器等功能?只是寻找一个快速而简单的解决方案,可以大大加快此过程的速度。
谢谢。
COPY
命令,但这需要一个扁平文件作为输入。我猜生成扁平文件不是一个选项。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层允许类似的操作)。
wal_buffers
增加到更大的值(例如8MB)和checkpoint_segments
(例如16)时,您可能还会看到改进。synchronous_commit
没有提高速度到足以产生差异。如果我没记错的话,这将把一个 2 分钟的数据库创建和填充过程缩短了一半,但关闭 fsync
后只需 10 秒即可运行。由于我的测试数据库没有数万条记录,因此不会使用 fsync=off
将数据写入磁盘。 - jmz对于数量在数百到数千的插入操作,请将它们批量处理:
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
\.
如果在另一张表中作为外键使用的任何列在该表中的大小超过微不足道,请确保对其进行索引。
你可以做的一件事是删除所有索引,进行插入操作,再重新创建索引。
INSERT INTO... SELECT...
这应该和使用COPY一样快(虽然我没有进行基准测试),但有一个优点,就是您可以仅使用SQL命令进行复制,而无需像使用COPY那样设置外部文件。
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倍的速度优势。