Как реализовать и оптимизировать UPSERT в Greenplum 6

Привет! Меня зовут Антон Васильев, я работаю инженером технической поддержки компании Arenadata и нередко сталкиваюсь с довольно каверзными задачами и багами. Одной из них была проблема оптимизации механизма UPSERT в Greenplum 6. В этой статье я хочу рассказать, как эта задача может быть решена.

В Greenplum до появления седьмой версии отсутствовала поддержка функционала, известного как UPSERT (UPdate or inSERT). В Greenplum 7 эта возможность появилась в виде дополнения ON CONFLICT DO UPDATE к команде INSERT, которая поддерживает только heap-таблицы. Но что делать, если у нас, допустим, Greenplum 6 или мы хотим использовать этот функционал для append-optimized - таблиц?

Реализовать UPSERT в более ранней версии и при этом не ограничиваясь только heap-таблицами можно, воспользовавшись одним из двух способов:

1. Использовать систему правил с помощью команды CREATE RULE.

2. С помощью UDF-функции.

Система правил СУБД Greenplum (Postgres) позволяет при вызове одной DML-команды (event) для таблицы определить для неё альтернативное действие. Само правило в данном случае описывает условия наступления события (event) и дополнительные команды, которые должны будут выполняться.

Для решения нашей задачи мы создадим правило, по которому при наличии в целевой таблице такого же значения в поле id операцию добавления (INSERT) будет заменять операция изменения (UPDATE). Таким образом, мы реализуем функционал UPSERT.

Пример кода:

-- создаём целевую таблицу:
CREATE TABLE IF NOT EXISTS test_table_rule (
    id    integer PRIMARY KEY,
    name  text,
    state text,
    age   integer
  )
  DISTRIBUTED BY (id);

-- заполняем таблицу первичными значениями:
INSERT INTO test_table_rule VALUES (1, 'Anton', 'inserted', 1);
INSERT INTO test_table_rule VALUES (2, 'Boris', 'inserted', 22);
-- проверка результата:
SELECT * FROM test_table_rule ORDER BY id;

-- создаём правило upsert_rule:
CREATE OR REPLACE RULE upsert_rule AS ON INSERT TO test_table_rule
 WHERE EXISTS (
       SELECT 1
         FROM test_table_rule
        WHERE test_table_rule.id = NEW.id
       )
DO INSTEAD UPDATE test_table_rule
              SET  name = NEW.name,
                  state = NEW.state,
                    age = NEW.age
            WHERE test_table_rule.id = NEW.id;

-- делаем обновление UPDATE:
INSERT INTO test_table_rule VALUES (1, 'Anton',  'updated',  11);
-- делаем вставку INSERT:
INSERT INTO test_table_rule VALUES (3, 'Victor', 'inserted', 33);
-- проверка результата:
SELECT * FROM test_table_rule ORDER BY id;

Другой вариант реализации UPSERT — использование пользовательской (UDF) функции.
Пример кода:

CREATE TABLE IF NOT EXISTS test_table_func (
    id         integer PRIMARY KEY,
    data       text,
    updated_at timestamp default clock_timestamp()
  ) 
  DISTRIBUTED BY (id);

-- Создадим функцию на PL/pgSQL для реализации UPSERT:
CREATE OR REPLACE FUNCTION upsert_func(f_id int, f_data text)
RETURNS void AS $$
  BEGIN
    -- Попытка обновить запись:
    UPDATE test_table_func
       SET data = f_data,
           updated_at = clock_timestamp()
     WHERE id = f_id;

    /* Если обновление не затронуло ни одной строки,
       то вставить новую запись */
    IF NOT FOUND THEN
      INSERT INTO test_table_func (id, data, updated_at)
        VALUES (f_id, f_data, clock_timestamp());
    END IF;
  END;
$$ LANGUAGE plpgsql SET optimizer = off;

-- Попытка вставки записи с id = 1
SELECT upsert_func(1, 'new data 1 inserted') AS "INSERT id = 1";
-- Попытка вставки записи с id = 2
SELECT upsert_func(2, 'new data 2 inserted') AS "INSERT id = 2";
-- Попытка обновления записи с id = 2
SELECT upsert_func(2, 'new data 2 updated')  AS "UPDATE id = 2";

-- Проверка результата выполнения функции:
SELECT * FROM test_table_func ORDER BY id;

Наша функция upsert_func() пытается обновить запись в таблице test_table_func с заданным id. Если обновление не затронуло ни одной строки, то происходит вставка новой строки. Таким образом, функция upsert_func() обеспечивает логику UPSERT, то есть обновляет запись, если существует, или вставляет новую, если не существует.

Исключение "unique_violation"

При использовании функции upsert_func() двумя параллельными процессами может возникнуть ситуация, когда они оба одновременно попытаются вставить данные. В результате второй процесс уже не сможет выполнить INSERT и получит сообщение (EXCEPTION) unique_violation.
В этом случае для второго процесса команда INSERT будет уже не актуальна. И возможно, ему уже имеет смысл по свежесозданной записи сделать UPDATE.
Пример такой логики, с обработкой исключения unique_violation внутри функции рассмотрен в руководстве по PostgreSQL в разделе "Control Structures".

Что тут необходимо заметить по поводу обработки unique_violation внутри функции?

• Функция upsert_func() без обработки EXCEPTION работает гораздо быстрее. Если в вашем случае появление ошибки — явление редкое или маловероятное, то будет проще реализовать обработку unique_violation на стороне бэкенда (в виде повторения вызова upsert_func).

• Такой способ не подойдёт в случае, когда используется другая логика исполнения. Например, когда из двух параллельных вставок вторая не должна сразу же затереть первую, а необходимо будет вставить обе. В этом случае не попасть под unique_violation поможет использование для поля id типа serial.

• Необходимо иметь в виду, что если по таблице создано несколько уникальных индексов, то цикл будет повторяться вне зависимости от того, нарушение какого индекса вызвало ошибку.

Пойдём далее. В нашем примере при определении функции upsert_func() мы устанавливаем GUC-параметр SET optimizer = off. Эта строка связана с особенностью работы оптимизаторов Greenplum. Разберём этот пункт подробнее, поскольку он существенно влияет на скорость выполнения запроса.

Различия в скорости выполнения SQL-запроса разными оптимизаторами

В Greenplum вместе сосуществуют два оптимизатора запросов: GPORCA и Postgres Planner. По умолчанию используется улучшенный оптимизатор GPORCA. Его улучшения связаны с оптимизацией работы планировщика для OLAP-запросов в распределённых системах, коей является Greenplum. Если для конкретного запроса GPORCA использован быть не может, то запрос переключается на использование Postgres Planner.

Именно поэтому в нашем примере кода функции upsert_func установлен параметр SET optimizer = off. Он как раз и определяет, что будет работать: оптимизатор GPORCA (on) или планировщик Postgres (off).

Дело в том, что оптимизатор GPORCA с нашей функцией не заработает и переключится на Postgres query optimizer. Процесс определения этого факта и переключения на планировщик Postgres занимает некоторое (и весьма существенное) время. В рассматриваемом случае это и будет большая часть времени выполнения нашего кратковременного запроса.

Иллюстрация со схемой переключения с GPORCA на Postgres Planner

Подробней с ограничениями оптимизатора GPORCA можно ознакомиться здесь.

Также может возникнуть понижение производительности GPORCA и при использовании системы правил (RULE). Причины, применимые к нашему случаю, упоминаются такие:

1. Выполняется кратковременный запрос. В связи с этим существенную долю у GPORCA займут затраты на выявление оптимального плана (Planning time).

2. Выполняются DML-операции: для GPORCA это также требует дополнительных затрат.

Таким образом, если для вашего запроса важна скорость выполнения, то вы также можете отключить оптимизатор GPORCA, например, в сессии:

SET optimizer = off;
-- Выполнение UPSERT SQL-запроса:
INSERT INTO test_table_rule VALUES (1, 'Anton',  'updated',  11);
SET optimizer = on;

Проблема эксклюзивной блокировки в Greenplum для UPDATE

Ввиду распределённой архитектуры в Greenplum, в отличие от Postgres, по умолчанию для процедуры UPDATE используется более строгая блокировка — EXCLUSIVE. В этом случае операции UPDATE по таблице ходят последовательно, параллельное обновление недоступно.
Решить данную проблему позволяет алгоритм "Global Deadlock Detector".

Включить его можно с помощью GUC-параметра gp_enable_global_deadlock_detector = on. Тогда на мастер-сервере будет запущена фоновая служба (background worker) "global deadlock detector process". Она будет собирать с сегментов информацию о блокировках и отслеживать наличие локальных и глобальных взаимоблокировок.

Этот алгоритм позволяет Greenplum для операций UPDATE и DELETE с heap-таблицами ослабить режим блокировки до уровня ROW EXCLUSIVE, что позволяет осуществлять одновременный UPDATE (и DELETE) в heap-таблицах.

Что же по поводу Append Optimized - таблиц?

AO-таблицы не предназначены для частых построчных обновлений (UPDATE).
Между тем и для таких таблиц тоже может потребоваться UPSERT, пусть и относительно редкий.

Поэтому необходимо помнить о нескольких особенностях AO-таблиц:

• в AO-таблицах для UPDATE используется та же строгая блокировка — EXCLUSIVE;

• параллельная работа с AO-таблицей ограничена 127 одновременными процессами;

• в Greenplum 6 для AO-таблиц построить уникальные индексы и первичный ключ нельзя.

Соответственно, для AO-таблиц обеспечивать уникальность полей необходимо будет самодельными костылями.

В Greenplum 7 появилась возможность построить уникальные индексы. Поэтому в новой версии уже можно реализовывать UPSERT упомянутыми выше способами без проблем с уникальностью. Более подробно ознакомиться с устройством UPSERT в Greenplum 7 вы можете в статье моего коллеги Василия Иванова.