PostgreSQL で更新/置換操作を高速化するにはどうすればよいですか? 質問する

私たちは、PostgreSQL 8.3 をストレージ バックエンドとして使用する特殊なアプリケーションを持っています (Python と psycopg2 を使用)。重要なテーブルに対して実行する操作は、ほとんどの場合、挿入または更新です (削除や選択はほとんどありません)。

正気を保つために、私たちは独自のデータマッパーのようなレイヤーは、かなりうまく機能しますが、更新のパフォーマンスという大きなボトルネックがあります。もちろん、更新/置換シナリオが「空のテーブルへの挿入」シナリオほど高速になるとは期待していませんが、もう少し近づくと良いでしょう。

このシステムは同時更新が不要であることに注意してください

更新時には常に各行のすべてのフィールドを設定します。これは、テストで「置換」という用語を使用していることからもわかります。これまで、更新の問題に対して 2 つのアプローチを試しました。

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

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

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;

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