问如何加快PostgreSQL中的更新/替换操作？

EN

我们有一个相当具体的应用程序，它使用Python8.3作为存储后端(使用PostgreSQL和psycopg2)。在大多数情况下，我们对重要的表执行的操作是插入或更新(很少是删除或选择)。

出于理智的原因，我们已经创建了我们自己的Data Mapper-like层，该层工作得相当好，但它有一个很大的瓶颈，即更新性能。当然，我并不期望更新/替换方案能像“插入到空表”的方案那样快，但如果能更近一点就更好了。

请注意，此系统没有并发更新

我们总是在更新时设置每一行的所有字段，这可以从我在测试中使用单词“替换”的术语中看出。到目前为止，我已经尝试了两种方法来解决更新问题：

1. 创建一个接受要更新的行数组的replace()过程：

CREATE OR REPLACE函数replace_item(data item[])返回VOID作为$$ BEGIN FOR I IN COALESCE(array_lower(data,1),0) ..COALESCE(array_upper(data,1)，-1) LOOP UPDATE item SET a0=datai.a0，a1=datai.a1，a2=datai.a2 WHERE key=datai.key；END LOOP；END；$$ LANGUAGE plpgsql

• 创建一个insert_or_replace规则，以便除偶尔删除之外的所有内容都成为多行插入

创建规则"insert_or_replace“AS ON INSERT TO " item”WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)代替(更新项目集a0=NEW.a0，a1=NEW.a1，a2=NEW.a2 WHERE key=NEW.key);

这两种方法都会大大加快更新速度，尽管后者会稍微减慢插入速度：

Multi-row insert           : 50000 items inserted in  1.32 seconds averaging 37807.84 items/s
executemany() update       : 50000 items updated  in 26.67 seconds averaging  1874.57 items/s
update_andres              : 50000 items updated  in  3.84 seconds averaging 13028.51 items/s
update_merlin83 (i/d/i)    : 50000 items updated  in  1.29 seconds averaging 38780.46 items/s
update_merlin83 (i/u)      : 50000 items updated  in  1.24 seconds averaging 40313.28 items/s
replace_item() procedure   : 50000 items replaced in  3.10 seconds averaging 16151.42 items/s
Multi-row insert_or_replace: 50000 items inserted in  2.73 seconds averaging 18296.30 items/s
Multi-row insert_or_replace: 50000 items replaced in  2.02 seconds averaging 24729.94 items/s

关于测试运行的随机注释：

更新所有测试都运行在数据库所在的同一台计算机上；连接到localhost.

• Inserts并将更新分批应用于数据库，每批500个项目，每个项目在其自己的事务中发送(UPDATED).

• All /
• 测试使用与(UPDATED).
• All中已有的值相同的值使用适配器适配器() function.
• All表在使用之前被截断和清理(添加了，在以前的运行中，只有截断适配器表看起来像这样：

CREATE TABLE item ( KEY MACADDR PRIMARY key、a0 VARCHAR、a1 VARCHAR、a2 VARCHAR )

因此，真正的问题是:如何才能将更新/替换操作的速度提高一点？(我认为这些调查结果可能“足够好”，但我不想在没有利用SO人群的情况下放弃:)

另外，如果有人建议使用更优雅的replace_item()，或者有证据表明我的测试完全失败了，我们将非常欢迎。

如果您想尝试重现，可以在here上找到测试脚本。记得先检查一下，though...it WorksForMe，但是...

您需要编辑db.connect()行以适合您的设置。

编辑

多亏了#postgresql @ freenode中的andres，我有了另一个单查询更新的测试；非常类似于多行插入(如上面的update_andres所示)。

UPDATE item
SET a0=i.a0, a1=i.a1, a2=i.a2 
FROM (VALUES ('00:00:00:00:00:01', 'v0', 'v1', 'v2'), 
             ('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
             ...
      ) AS i(key, a0, a1, a2)
WHERE item.key=i.key::macaddr

编辑

多亏了下面的#postgresql @ freenode和jug/jwp中的merlin83，我有了另一个使用插入到临时/删除/插入方法的测试(上面列出的是"update_merlin83 (i/d/i)“)。

INSERT INTO temp_item (key, a0, a1, a2)
    VALUES (
        ('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
        ('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
        ...);

DELETE FROM item
USING temp_item
WHERE item.key=temp_item.key;

INSERT INTO item (key, a0, a1, a2)
    SELECT key, a0, a1, a2
    FROM temp_item;

我的直觉是，这些测试对现实世界场景中的性能并不是很有代表性，但我认为差异足够大，可以为进一步研究提供最有希望的方法。perftest.py脚本还包含所有更新，供那些想要签出它的人使用。它相当难看，所以不要忘了你的护目镜:)

编辑

andres在#postgresql @ freenode中指出，我应该使用插入到临时/更新的变体(上面列出的是"update_merlin83 (i/u)“)进行测试。

INSERT INTO temp_item (key, a0, a1, a2)
    VALUES (
        ('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
        ('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
        ...);

UPDATE item
SET a0=temp_item.a0, a1=temp_item.a1, a2=temp_item.a2
FROM temp_item
WHERE item.key=temp_item.key

编辑

可能是最后的编辑:我修改了我的脚本，以更好地匹配我们的加载场景，似乎即使在将事情放大一点并添加一些随机性时，这些数字仍然有效。如果有人从其他场景中得到了非常不同的数字，我很有兴趣了解它。