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

Question

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

出于理智的原因，我们创建了自己的Data Mapper类似层，工作得相当好，但它有一个很大的瓶颈，即更新性能。当然，我不希望更新/替换方案会像“插入空表”那样快速，但如果能再接近一​​点就好了。

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

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

1. 创建一个 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
   

2. 创建一个 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
  )
  

所以，真正的问题是:如何才能加快更新/替换操作的速度？ (我认为这些发现可能“足够好”，但我不想在不挖掘 SO 人群的情况下放弃 :)

也欢迎任何人暗示更优雅的 replace_item()，或者我的测试完全被破坏的证据。

测试脚本可用here如果您想尝试复制。不过记得先检查一下……它适用于我，但是……

您需要编辑 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 指出我应该使用 insert-to-temp/update 变体进行测试(上面列为“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

编辑

可能是最终编辑:我更改了我的脚本以更好地匹配我们的负载场景，并且似乎数字即使在放大一点​​并添加一些随机性时也保持不变。如果有人从其他场景中得到非常不同的数字，我会很想知道。

Answer 1

我在 pg 中做这些事情的常用方法是:使用复制、合并(有趣的部分)、利润将原始数据匹配目标表加载到临时表(无约束)。

我专门针对这些情况写了一个merge_by_key函数:

http://mbk.projects.postgresql.org/

文档不是非常友好，但我建议给它一个好的外观。