Как ускорить операции обновления/замены в PostgreSQL?

У нас есть довольно специфическое приложение, которое использует PostgreSQL 8.3 в качестве хранилища (используя Python и psycopg2). Операции, которые мы выполняем для важных таблиц, в большинстве случаев вставляются или обновляются (редко удаляются или выбираются).

По соображениям здравомыслия мы создали собственный Data Mapper -подобный слой, который работает достаточно хорошо, но имеет одно большое узкое место, обновление представление. Конечно, я не ожидаю, что сценарий обновления/замены будет таким же быстрым, как "вставить в пустую таблицу", но было бы неплохо получить немного ближе.

Обратите внимание, что эта система не содержит одновременных обновлений

Мы всегда устанавливаем все поля каждой строки в обновлении, что можно увидеть в терминологии, где я использую слово "заменить" в своих тестах. Я до сих пор пробовал два подхода к нашей проблеме обновления:

• Создайте процедуру 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().
• Все таблицы усекаются и пылесотся перед использованием ( ADDED, в предыдущих прогонах происходит только усечение)

• Таблица выглядит так:

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

Итак, реальный вопрос: как ускорить операции обновления/замены немного больше? (Я думаю, что эти результаты могут быть "достаточно хорошими", но я не хочу сдаваться, не нажимая толпу SO:)

Также любые предложения намекают на более элегантный replace_item(), или доказательства того, что мои тесты полностью нарушены, будут наиболее желанными.

Тест script доступен здесь, если вы хотите попытаться воспроизвести. Не забудьте сначала проверить это, хотя... это WorksForMe, но...

Вам нужно будет отредактировать строку db.connect() в соответствии с вашими настройками.

ИЗМЕНИТЬ

Благодаря andres в #postgresql @freenode У меня есть еще один тест с обновлением с одним запросом; как многострочная вставка (указанная как 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

ИЗМЕНИТЬ

Благодаря merlin83 в #postgresql @freenode и jug/jwp ниже у меня есть еще один тест с подходом insert-to-temp/delete/insert (указанным выше как "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 script содержатся все обновления для тех из вас, кто хочет проверить это. Это довольно уродливо, так что не забывайте о своих очках:)

ИЗМЕНИТЬ

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

ИЗМЕНИТЬ

Возможно окончательное редактирование: Я изменил свой script, чтобы лучше соответствовать нашему сценарию загрузки, и кажется, что цифры сохраняются даже при небольшом масштабировании и добавлении некоторой случайности. Если кто-то получает очень разные цифры из какого-то другого сценария, мне было бы интересно узнать об этом.