COPY可以与功能一起使用吗? [英] Can COPY be used with a function?
问题描述
我的任务是分析一个PostgreSQL数据库。第一个要求是查看如何快速添加记录,并消除所有可能的外部瓶颈,以便找到我们的理论极限。
I've been tasked with profiling a postgresql database. The first requirement is to see how fast records can be added, with all possible external bottlenecks removed, in order to find our theoretical limit.
首先,我创建了一个csv文件带有样本数据,并通过COPY功能读取。现在,所有记录都通过函数 update_or_add()
添加。是否可以将COPY与 update_or_add()
一起使用,还是有我没有考虑过的更好的解决方案?
At first I created a csv file with sample data and read it in with the COPY function. Now, all records are added via a function update_or_add()
. Is it possible to use COPY along with update_or_add()
or is there a better solution that I haven't considered?
推荐答案
您最好的方法应该是概括化 update_or_add以使其适用于集合中的所有行,而不是对该行的每个行调用update_or_add()。
Rather than "for each row call update_or_add() on the row", your preferred approach should be to generalize "update_or_add" to work on all rows in a set.
COPY
将来自外部源的数据转换为 TEMPORARY
或 UNLOGGED
表。然后编写一个查询,该查询将复制的表中的数据合并到您的主表中,就像 update_or_add()
那样,但同时适用于所有行。如果可以同时修改主表,则可能需要使用 LOCK TABLE main_table IN EXCLUSIVE MODE
锁定主表,以便其他事务只能 SELECT
进行合并。
COPY
the data from the external source into a TEMPORARY
or UNLOGGED
table. Then write a query that merges the data from the copied table into your main table, like update_or_add()
does but for all rows at once. If concurrent modification of the main table is possible this may require locking the main table with LOCK TABLE main_table IN EXCLUSIVE MODE
so that other transactions can only SELECT
from it while the merge is going on.
没有您的架构或不知道什么 update_or_add
很难说更多吗。大概是一个upsert / merge函数,在这种情况下,您可以通过锁定主表并进行整套合并来更有效地完成其工作。
Without your schema or knowing what update_or_add
does it's hard to say more. At a guess it's an upsert/merge function, in which case you can do its job massively more efficiently by locking the main table and doing a whole-set merge.
这篇关于COPY可以与功能一起使用吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!