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

Question

我们有一个比较特殊的应用程序，使用 PostgreSQL 8.3 作为存储后端（用 Python 和 psycopg2）。我们对重要表格进行的操作大多数是插入或更新（很少有删除或查询）。

为了保持系统的稳定性，我们创建了一个类似于数据映射器的层，这个层工作得还不错，但有一个大问题，就是更新性能。虽然我不指望更新或替换的速度能和“向空表插入”一样快，但如果能更接近一点就好了。

需要注意的是，这个系统没有并发更新的问题。

我们在更新时总是设置每一行的所有字段，这就是我在测试中使用“替换”这个词的原因。到目前为止，我尝试了两种解决更新问题的方法：

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

CREATE OR REPLACE FUNCTION replace_item(data item[]) RETURNS VOID AS $$
BEGIN
    FOR i IN COALESCE(array_lower(data,1),0) .. COALESCE(array_upper(data,1),-1) LOOP
       UPDATE item SET a0=data[i].a0,a1=data[i].a1,a2=data[i].a2 WHERE key=data[i].key;
    END LOOP;
END;
$$ LANGUAGE plpgsql

创建一个 insert_or_replace 规则，这样除了偶尔的删除，其他操作都变成了多行插入。

CREATE RULE "insert_or_replace" AS
    ON INSERT TO "item"
    WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)
    DO INSTEAD
        (UPDATE item SET 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

关于测试运行的一些随机笔记：

所有测试都在数据库所在的同一台计算机上运行；连接到本地主机。
插入和更新以每次 500 项的批量应用到数据库，每批都在自己的事务中处理（已更新）。
所有的更新/替换测试都使用了数据库中已经存在的相同值。
所有数据都使用 psycopg2 的 adapt() 函数进行了转义。
所有表在使用前都进行了清空和清理（已添加，之前的运行只进行了清空）。

表格的结构如下：

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

所以，真正的问题是：我该如何进一步加快更新/替换操作的速度？（我觉得这些发现可能“足够好”，但我不想在没有向大家请教之前就放弃 :)

另外，任何关于更优雅的 replace_item() 的提示，或者证明我的测试完全有问题的证据，都是非常欢迎的。

如果你想尝试复现，测试脚本可以在这里找到。不过记得先检查一下...它对我有效，但...

你需要编辑 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 的 merlin83 和下面的 jug/jwp，我有了一个插入到临时表/删除/插入的方法（在上面列为 "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 脚本也包含了所有更新，供想查看的朋友参考。不过它看起来比较复杂，所以别忘了带上你的护目镜 :)

编辑

#postgresql @ freenode 的 andres 提到我应该测试插入到临时表/更新的变体（在上面列为 "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

编辑

可能是最后一次编辑：我修改了我的脚本，以更好地匹配我们的负载场景，似乎即使在稍微增加规模和添加一些随机性时，数据也依然成立。如果有人在其他场景中得到非常不同的结果，我很想知道。

批量插入 postgresql 数据库优化数据库测试临时表事务处理更新性能数据映射器

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

6 个回答

撰写回答