Пишу, по большей части, про историю, свою жизнь и немного про программирование.

Коммит миграций кусками с помощью psql

Я ещё не так давно работаю с «Постгресом» и раз в месяц натыкаюсь на что-то неожиданное.

Недавняя ситуация — дано: миграция, которая обновляет более половины полей таблицы с более чем полумиллиардом записей и содержит в себе довольно затратный по процессорному времени запрос.

В «Оракле» такую миграцию наши ребята, насколько я знаю, разбивали хранимой процедурой на части и коммитили внутри кусками по миллиону, в «Постгресе» так нельзя — блок на языке PL/pgSQL выполняется в собственной транзакции и она прервётся при попытке сделать COMMIT. Автономных транзакций нет.

Испытания на тестовом стенде, приближенном к промышленному, показали, что единым куском обновить не получится — на мастере и слейве первой очереди кончилось место и стенд развалился. Надо было найти какой-то эквивалент сбросу миграции в цикле, по типу того как это делается в «Оракле».

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

DO $$
  DECLARE
    max_id table.id % TYPE;
    query TEXT;
BEGIN
  -- делим таблицу на куски по миллиону, теперь в max_id у нас номер последнего куска
  SELECT CEIL(MAX(id)::float/1000000) INTO max_id FROM mytable;

  -- очищаем файл /tmp/sqlquery
  COPY (SELECT '') TO '/tmp/sqlquery';

  -- айдишники в моём конкретном случае идут с единицы, но более правильно идти с минимума до максимума с шагом
  FOR i IN 0..max_id LOOP
        query = FORMAT(
            'UPDATE mytable '
            'SET something=value '
            'WHERE id BETWEEN ' || i*1000000+1 || ' AND ' || (i+1)*1000000 || ';'
        );

        -- запуск /bin/cat нужен, чтобы дописывать в файл
        EXECUTE  'COPY (SELECT ' || quote_literal(query) || ') TO PROGRAM ''/bin/cat >> /tmp/sqlquery''';
  END LOOP;
END$$;

-- запуск запроса
\i /tmp/sqlquery

Я воспользовался тем, что в накатке миграции у нас участвует утилита psql — официальный консольный клиент к «Постгресу», помимо команд СУБД она умеет выполнять свои собственные команды, чем я и пользуюсь — собираю нужные запросы в файл и запускаю его на выполнение в последней строке.

Обратите внимание на вызов /bin/cat — это важно, так как команда COPY сама не умеет добавлять что-то в файл, она его перезаписывает.

2 комментария
111222333 2017

Автономных транзакций нет.

Есть savepoint-ы.

А перелить данные в новую таблицу было реально?
Вообще в MVCC-базах лучше держать на сервере минимум 2x места от реального занимаемого объёма.
В таком случае и при обслуживании хватило бы места в виде:

VACUUM FULL;
CREATE ... SELECT что нужно;
DROP источник;
ALTER переименование;

Конечно, если есть возможность останавливать сервис.

Евгений Степанищев (bolknote.ru) 2017

Комментарий для 111222333:

Есть savepoint-ы.

А что они дадут в данном случае?

А перелить данные в новую таблицу было реально?

Вряд ли — так же кончится место на разделе с журналами и литься скорее всего будет ещё дольше — там 60% таблицы обновляется, а тут 100. Не говоря уже о внешних ключах, которые надо разорвать, а потом вернуть назад — тот ещё геморрой и затраты по времени.