SQL миграции в Postgres. Часть 221.05.2023 00:16

В первой части мы рассмотрели базовые операции, такие как добавление новых атрибутов, создание индексов и ограничений и т.д.

Эта статья посвящена двум более сложным миграциям:

Содержание

Обновление большой таблицы

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

UPDATE my_table SET new_column = 42

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

Основных таких ограничений два:

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

  • раздувание (bloat) таблицы и ее индексов

Таким образом, в этой статье мы рассмотрим подходы, которые позволяют уменьшить эффект одной или обеих этих проблем.

Всего будет разобрано три подхода:

  • замена таблицы

  • использование временной таблицы

  • обновление порциями

Перед тем, как переходить к самим подходам, давайте взглянем на причины возникновения упомянутых проблем. Далее будет немного базовой теории о том, как работают блокировки в postgres, а также об эффекте раздувания таблиц и индексов (bloat) — жмите сюда, если хотите пропустить теорию и перейти сразу к делу. 

Блокировки в postgres

В основе движка postgres находится механизм MVCC (Multi-Version Concurrency Control), который определяет правила работы одновременных транзакций. Идея MVCC, как можно догадаться из названия, основывается на версионности данных — есть некоторые версии, которые видны/доступны только определенным транзакциям и не видны другим. Версионность реализована на основе специальных системных атрибутов, которые есть в каждой таблице по умолчанию не отображаются: ctid, xmin, xmax и др. Их можно посмотреть, явно указав в SQL запросе: 

postgres=# SELECT ctid, xmin, xmax, * FROM my_table;

    ctid    | xmin | xmax |   id    | new_column
------------+------+------+---------+------------
 (0,1)      | 1401 | 1420 |  200061 | 2
 (0,2)      | 1401 | 1420 |  250528 | 4
 (0,3)      | 1405 | 0    |  407753 | 11

  • ctid — это пара значений (страница + позиция на странице), представляющих собой адрес актуальной версии строки

  • xmin — ID транзакции, создавшей версию

  • xmax — ID транзакции, удалившей версию (пустое для неудаленных версий)

Когда транзакции обращаются к таблице, они видят снимок данных, построенный на основе xmin и xmax. В данной статье мы не будет углубляться в условия фильтрации, используемые для построения снимка. Вместо этого рассмотрим пример, чтобы понять, как происходит заполнение этих атрибутов. 

Предположим, что транзакция с ID=435 вставляет запись в пустую таблицу.

  • ctid = (0,1) — 0-я страница, 1-я позиция на странице. ctid указывает на саму себя (адрес справа от таблицы — реальное расположение версий)

  • xmax = 0 означает, что версия не удалена

Затем транзакция 456 вставляет вторую запись. Под капотом происходит все аналогично предыдущей команде.

Далее происходит update. Транзакция 459 обновляет один из атрибутов предыдущей строки. 

Это приводит к трем основным изменениям:

  • создается новая версия, которая размещается по адресу (0,3)

  • указатель ctid предыдущей версии устанавливается на этот адрес

  • xmax предыдущей версии становится равен ID текущей транзакции (459)

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

Важно отметить, что если попытаться посмотреть все версии, используя ранее представленный SELECT, мы увидим только одну версию с ctid=(0,3), потому что результат будет уже отфильтрован в соответствии с правилами видимости для нашей транзакции. Чтобы увидеть все версии, можно использовать расширение pageinspect. Пример запроса: 

SELECT t_xmin, t_xmax, t_ctid, t_data 
FROM heap_page_items(get_raw_page('my_table',0));

 t_xmin | t_xmax | t_ctid |       t_data
--------+--------+--------+--------------------
   1267 |   1268 | (0,2)  | \x0100000002000000
   1268 |      0 | (0,2)  | \x0200000002000000

Продолжим наш пример. Новая транзакция, ID=501, пытается обновить вторую запись.

Postgres необходимо определить какая из двух версий активна и доступна для текущей транзакции. Версия 1 по адресу (0,2) имеет xmax != 0 — это означает, что она была удалена. Однако, чтобы просто проигнорировать эту версию и перейти к версии 2 (по адресу (0,3)), необходимо знать статус транзакции 459 — был ли уже COMMIT (или ROLLBACK)?

Если транзакция 459 все еще активна, транзакция 501 будет ждать ее завершения. Это и есть момент, когда могут начаться проблемы, если транзакция 459 будет длительной.

Кстати, если бы транзакция 501 выполняла только чтение (SELECT), то она бы не ожидала результата транзакции 459. Если та уже зафиксирована, то будет использоваться версия (0,3). Если еще нет или был откат, то версия (0,2).

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

Эффект раздувания (bloat)

Раздувание таблиц, или bloat, напрямую связан с механизмом MVCC. Чтобы понять причины возникновения, давайте взглянем на то, как данные хранятся на физическом уровне. Данные таблицы разделены на страницы (размером 8kb по умолчанию). Страница содержит как актуальные, так и удаленные версии (помечены зеленым и серым цветами соответственно).

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

В нашем примере 4 страницами видно, что примерно половина данных — удаленные версии, которые только занимают место. Когда речь идет о больших размерах таблиц, объем такого «мусора» может быть ощутимым.

Другой важный момент в том, что, страница — это минимальная единица доступа к диску. Если необходимо прочитать версию по адресу (3,5), то страница 3 будет считана целиком и загружена в shared buffers (что тоже не безгранично). Таким образом, чем меньше страниц необходимо считывать, тем меньше нагрузка на дисковую систему и выше производительность БД. Поэтому, упрощенно говоря, наша цель — минимизировать количество серых ячеек.

Пара слов о vacuum

Как известно, в Postgres есть специальный механизм (auto)vacuum, который как раз занимается освобождением места, занятого удаленными версиями, для переиспользования. Проблема в том, что при интенсивной нагрузке на БД и недостаточно агрессивной настройке, vacuum может не успевать выполнять «уборку» и его работа не так эффективна. Более того, в случае с долгой транзакцией, vacuum не может обрабатывать удаленные версии пока транзакция не завершилась. Это приводит к следующей картине:

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

После небольшого теоретического отступления вернемся к практике.

Подход 1: замена таблицы

Идея очень простая и заключается в создании новой таблицы, копировании данных с учетом изменений и удаления исходной таблицы.

Этот подход может быть применим если:

  1. операции чтения значительно превалируют над операциями записи (SELECT >> INSERT + UPDATE + DELETE)

  2. часть операций на запись можно потерять или выполнить с большой задержкой

  3. имеется свободное место на диске

Дополнительное свободное место необходимо только на время миграции, когда обе таблицы будут существовать одновременно. По завершению исходная таблица удаляется и место освобождается. Размер можно оценить как:

size(исходная_таблица) - bloat(исходная_таблица) + size(новые_данные)

  • для оценки bloat можно использовать разнообразные SQL скрипты, например эти.

  • новые_данные— это оценка прироста таблицы в результате миграции.

  • желательно добавить 30–50% процентов сверху, на всякий случай.

Итак, приступим к делу.

BEGIN;
LOCK TABLE my_table IN SHARE MODE; (1)

CREATE TABLE new_my_table (id int, ...); (2)

INSERT INTO new_my_table SELECT ... FROM my_table; (3)

-- create constraints, indices, triggers (4)

DROP TABLE my_table; (5)

ALTER TABLE new_my_table RENAME TO my_table; (6)

COMMIT;
END;

Идем по порядку:

  1. Захватываем блокировку в режиме SHARE MODE — это не мешает операциям чтения, но блокирует все модификации. Блокировка необходима, чтобы во время выполнения миграции состояние таблицы не могло измениться в параллельных транзакциях.

  2. Создаем новую таблицу.

  3. Копируем данные в новую таблицу. На этом шаге проводим модификацию данных.

  4. Создаем ограничения, индексы и пр. Делаем это после заполнения таблицы, чтобы не замедлять вставку данных на предыдущем шаге.

  5. Удаляем предыдущую таблицу. В этот момент запросы могут завершиться с ошибкой (см. детали далее).

  6. Переименовываем новую таблицу.

  7. Осталось закоммитить транзакцию и миграция завершена.

Важные моменты:

  • Шаги 2 и 3 — очень универсальные. Здесь можно провести множество разнообразных модификаций — добавить атрибуты, изменить тип, объединить/разделить атрибуты и т.д. Главное, чтобы приложение было готово к новой модели данных.

  • В зависимости от версии Postgres поведение в 6 и 7 может отличаться. До версии 9.2 при удалении таблицы все запросы, ожидавшие захвата блокировки (речь про модификации — SELECTы не блокировались), завершатся с ошибкой. Это связано с тем, что при пересоздании таблицы меняется ее OID, а именно он используется при захвате блокировок, не имя таблицы напрямую. В версии 9.2 поведение поменялось — транзакции «перерасчитывают» OID и захватят блокировку на новой таблице. Если быть более точным, то встанут в очередь на ее захват, т.к. новая таблица будет находиться в режиме блокировки ACCESS EXCLUSIVE до момента коммита.

  • Если таблица имеет внешние ключи, то миграция сильно усложняется, поскольку это вызывает блокировки на соответствующих таблицах. Если ключей/таблиц немного, то подход по-прежнему можно использовать. В (1) необходимо дополнительно захватить блокировки и на эти таблицы. С этого момента они также будут в режиме только для чтения. Теоретически, этот шаг можно было бы отсрочить до момента создания внешних ключей в (4), но тогда появляется риск получить взаимные блокировки на этом шаге. Предварительная блокировка всех нужных таблиц — наиболее простой и надежный способ.

Итого об этом подходе:

  • (+) самый быстрый способ (среди рассматриваемых в этой статье)

  • (+) нет раздувания таблицы

  • (-) необходимо наличие свободного места

  • (-) блокировка операций на запись на время миграции

  • (-) смена OID таблицы (иногда может быть критично)

Подход 2: использование временной таблицы

Если по какой-либо причине требуется сохранить оригинальную таблицу, то можно несколько модифицировать предыдущий подход, сделав две «переливки» данных вместо одной. Сперва мы копируем данные в новую таблицу, применяя миграцию, а затем, копируем данные обратно в пустую таблицу.

В остальном условия такие же:

  1. операции чтения значительно превалируют над операциями записи (SELECT >> INSERT + UPDATE + DELETE)

  2. часть операций на запись можно потерять или выполнить с большой задержкой

  3. имеется свободное место на диске

  4. исходная таблица сохраняется

BEGIN;
LOCK TABLE my_table IN SHARE MODE; (1)

SET LOCAL temp_buffers = '????MB'; (2)

CREATE TEMP TABLE new_my_table ON COMMIT DROP AS SELECT ... FROM my_table; (3)

-- drop constraints, indexes, triggers (4)

TRUNCATE my_table; (5)

INSERT INTO my_table SELECT * FROM new_my_table; (6)

-- create constraints, indexes, triggers (7)

COMMIT;
END;

Основные отличия от первого подхода:

  • Вместо создания обычной таблицы, создается временная (3). ON COMMIT DROP используется, чтобы таблица автоматически удалилась по завершению транзакции. По умолчанию, это происходит при завершении сессии.

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

  • На 4 м шаге, подготавливаем исходную таблицу к очистке. 

  • Затем очищаем таблицу (5). В этот момент захваченная предварительно блокировка будет сэскалирована до максимальной (ACCESS EXCLUSIVE), что означает, что новые запросы на чтение будут также заблокированы.

  • На 6 шаге возвращаем данные в исходную таблицу. Здесь также можно изменить модель данных, например, добавить или удалить атрибут.

  • В случае наличия внешних ключей, сложности аналогичны предыдущему подходу.

Итого:

  • (+) достаточно быстрый способ

  • (+) сохраняется исходная таблица

  • (+) нет раздувания таблицы

  • (-) необходимо наличие свободного места

  • (-) блокировка операций на запись (на все время выполнения миграции)

  • (-) блокировка операций на чтение (~ ½ времени, с момента TRUNCATE)

Подход 3: обновление порциями

Оба подхода, рассмотренные ранее, хорошо решают проблему раздувания таблицы, однако они имеет общий недостаток — операции на запись блокируются (во втором подходе еще и часть операций на чтение).

Как вы можете помнить из начала статьи, причина длительной блокировки при использовании подхода «в лоб» заключается в том, что миграция проводится в одной транзакции, которая обновляет большой объем данных. Таким образом, идея 3 го подхода напрашивается сама собой — разбить миграцию на много маленьких и выполнять их в отдельных транзакциях.

Этот подход применим если:

  • Модификации составляют ощутимую долю нагрузки (SELECT > INSERT + UPDATE + DELETE)

  • Необходимо поддержать параллельные операции на запись

  • На диске нет много места

Перейдем к делу.

Допустим, что мы будем использовать порции (батчи) размером до 10000 записей. Это будет выглядеть примерно так: 

UPDATE my_table SET new_column = 42
WHERE id BETWEEN 1 AND 10000 AND new_column IS NULL;
COMMIT;

UPDATE my_table SET new_column = 42
WHERE id BETWEEN 10001 AND 20000 AND new_column IS NULL;
COMMIT;

...

Важно:

  • Мы не используем в запросе OFFSET *10000 LIMIT 10000 потому что это значительно неэффективнее — OFFSET будет сканировать лишние записи.

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

Таким образом мы решаем проблему с длительной блокировкой — теперь не более 10000 записей будут заблокированы в один момент. Однако это не решает проблему с раздуванием таблицы — если каждый такой UPDATE будет проводиться быстро друг за другом, то VACUUM по-прежнему не будет успевать. Очевидное решение — вызывать vacuum руками: 

UPDATE my_table SET new_column = 42
WHERE id BETWEEN 1 AND 10000 AND new_column IS NULL;
COMMIT;
VACUUM;

UPDATE my_table SET new_column = 42
WHERE id BETWEEN 10001 AND 20000 AND new_column IS NULL;
COMMIT;
VACUUM;

...

Главный недостаток — это значительно замедлит миграцию. Компромиссным вариантом будет выполнение vacuum через каждые N обновлений, например, 5 или 10.

Чтобы не писать каждый UPDATE по-отдельности напрашивается написание примерно такой процедуры: 

FOR i IN 1..batch_count LOOP
    id_from := ...
    id_to := ...

    UPDATE my_table SET new_column = 42
      WHERE id BETWEEN id_from AND id_to AND new_column IS NULL;

    COMMIT;

    VACUUM my_table; -- ERROR!
END LOOP;

Однако, такой код не сработает, потому что команда VACUUM не может вызываться внутри транзакции. Чтобы обойти ограничение можно использовать расширение pg_background (поддерживает версии Postgres 9.5+). Оно позволяет выполнить команду в другом процессе. Обновленный код будет выглядеть так: 

FOR i IN 1..batch_count LOOP
    id_from := ...
    id_to := ...

    UPDATE my_table SET new_column = 42
      WHERE id BETWEEN id_from AND id_to AND new_column IS NULL;

    COMMIT;

    pid := pg_background_launch('vacuum my_table');
    PERFORM * FROM pg_background_result(pid) as p (r text);
END LOOP;

Выбор размера порции

Количество записей, обновляемых за одну итерацию влияет на несколько вещей. Чем меньше значение, тем:

  • ниже скорость миграции

  • меньше объем итогового раздувания

  • ниже вероятность взаимоблокировок

С первыми двумя пунктами, я думаю, все понятно, давайте подробнее рассмотрим третий.

Когда мы обновляем таблицу целиком, предварительно захватив блокировку, никто кроме нашей миграции не может модифицировать данные — все ждут завершения. В случае с порционным обновлением, параллельная транзакция может также попытаться захватить блокировки на какие-то строки. Если захваты блокировок происходит в разном порядке (например, транзакция 1 захватывает id=1 и id=3, а транзакция 2 — id=3 и id=1), то мы получит типичную ситуацию взаимной блокировки. Postgres в таком случае «отстрелит» одну из транзакций на свое усмотрение. Стоит отметить, что взаимоблокировки могут быть гораздо более сложные — с участием нескольких таблиц и транзакций.

Чтобы полностью избавиться от возможности взаимоблокировок, необходимо гарантировать, что порядок их захвата всегда одинаков. На практике этого, как правило, сложно добиться. Например, когда выполняется запрос UPDATE … WHERE id < 100, обновляемые строки могут быть заблокированы в произвольном порядке. Если Postgres использует индекс, то скорее всего блокировки будут упорядочены в соответствии с индексом. Если же происходит seqscan, то порядок будет определяться тем, как данные хранятся на диске.

Чтобы контролировать порядок блокировок, можно использовать следующую конструкцию: 

UPDATE my_table SET … WHERE id IN (
    SELECT id FROM my_table WHERE  ORDER BY id FOR UPDATE
);

Сперва мы захватываем блокировку по упорядоченному результату SELECT, после чего проводим обновление. Работает прекрасно, но есть нюанс — необходимо, чтобы все UPDATE были написаны таким образом, и в миграции и в «обычном» коде приложения, что, конечно же, никто делать не будет. 

Однако, мы можем поступить иначе. SELECT FOR UPDATE поддерживает опцию SKIP LOCKED, которая позволяет захватывать блокировки на «свободные» строки и пропускать уже заблокированные. Используя ее в миграции, мы избегаем взаимоблокировок. Минус подхода — придется выполнять больше запросов UPDATE.

Давайте соберем все вместе, немного унифицируем и добавим отладочной информации: 

DO $$
DECLARE
    batch_size constant int := 10000;
    vacuum_freq constant int := 10;
    id_max int;
    id_from int;
    id_to int := 0;
    i int := 1;
    updated_cnt_query int;
    updated_cnt_since_last_vacuum int := 0;
    pid int;
BEGIN
    SELECT max(id) into id_max FROM my_table;

    WHILE (id_to < id_max) LOOP
        id_from := (i - 1) * batch_size;
        id_to := id_from + batch_size - 1;
        RAISE INFO 'processing id % - %', id_from, id_to;

        WHILE (SELECT EXISTS(SELECT 1 FROM my_table 
                             WHERE id BETWEEN id_from AND id_to AND new_column IS NULL)) LOOP
            UPDATE my_table SET new_column = 42 WHERE id IN (
                SELECT id FROM my_table 
                WHERE id BETWEEN id_from AND id_to AND new_column IS NULL 
                FOR UPDATE SKIP LOCKED
            );

            GET DIAGNOSTICS updated_cnt_query = ROW_COUNT;
            COMMIT;

            IF (updated_cnt_query = 0) THEN
                RAISE INFO 'sleeping 1 sec';
                PERFORM pg_sleep(1);
            END IF;

            updated_cnt_since_last_vacuum := updated_cnt_since_last_vacuum + updated_cnt_query;
        END LOOP;

        IF (i % vacuum_freq = 0 AND updated_cnt_since_last_vacuum > 0) THEN
            RAISE INFO 'running vacuum';
            pid := pg_background_launch('vacuum my_table');
            PERFORM  FROM pg_background_result(pid) as r (t text);
            updated_cnt_since_last_vacuum := 0;
        END IF;
        i := i + 1;
    END LOOP;
END $$;

Основное:

  • Обновляем по 10000 записей

  • Если ничего не обновили, потому что записи были заблокированы — ждем 1 сек

  • Запускаем vacuum каждые 10 батчей, если были успешные обновления

Запускаем и видим следующее: 

INFO:  processing id 0 - 9999
INFO:  processing id 10000 - 19999
INFO:  processing id 20000 - 29999
INFO:  processing id 30000 - 39999
INFO:  processing id 40000 - 49999
INFO:  processing id 50000 - 59999
INFO:  processing id 60000 - 69999
INFO:  processing id 70000 - 79999
INFO:  processing id 80000 - 89999
INFO:  processing id 90000 - 99999
INFO:  running vacuum
INFO:  processing id 100000 - 109999
INFO:  processing id 110000 - 119999

Если миграция наткнется на заблокированные строки, то вывод будет примерно следующим: 

INFO:  processing id 370000 - 379999
INFO:  processing id 380000 - 389999
INFO:  processing id 390000 - 399999
INFO:  running vacuum
INFO:  processing id 400000 - 409999
INFO:  processing id 410000 - 419999
INFO:  sleeping 1 sec
INFO:  sleeping 1 sec
INFO:  sleeping 1 sec
INFO:  processing id 420000 - 429999
INFO:  processing id 430000 - 439999

Через 3 секунды блокировка снята и миграция продолжается.

Итого о подходе:

  • (+) минимальное время блокировок 

  • (+) небольшое раздувание таблицы

  • (+) наличие внешних ключей не усложняет миграцию

  • (-) медленный способ

  • (-) необходимо устанавливать расширение pg_background

В целом, это довольно универсальное решение, применимое во многих случаях.

Разделение таблицы на две

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

Так или иначе, предположим, что у нас есть таблица, которую мы хотим «располовинить» на две, и одна из новых таблиц будет ссылаться на вторую. Будем называть из «головной» и «хвостовой» соответственно.

Самое важное условие — миграцию нужно провести без остановки приложения.

Идея подхода основывается на интересной особенности Postgres — обновляемых представлениях. Для того, чтобы разделить таблицу, мы временно подменим ее на представление, с которым можно проводить все необходимые операции (кроме TRUNCATE) — SELECT, UPDATE, DELETE. Исходная таблица превратится в головную таблицу, от которой мы затем «отрежем хвост». Визуально процесс будет выглядеть так:

Приступим к делу.
Исходная таблица: 

CREATE TABLE big_table (id int primary key, a int, b int, c int);

Создаем «хвост»: 

CREATE TABLE tail_table
(
   id int primary key,
   b  int,
   c  int,
   CONSTRAINT fk_tail_table FOREIGN KEY (id) 
      REFERENCES big_table (id) ON DELETE CASCADE
)

Первый шаг — подмена исходной таблицы на представление и триггер. Открываем транзакцию и выполняем несколько операций: 

BEGIN
ALTER TABLE big_table RENAME TO head_table;
CREATE VIEW big_table as SELECT * FROM head_table;

CREATE OR REPLACE FUNCTION big_table_trigger_func() RETURNS TRIGGER AS
$$
BEGIN
   IF TG_OP = 'INSERT' THEN
       INSERT INTO head_table VALUES (NEW.);
       INSERT INTO tail_table values (NEW.id, NEW.b, NEW.c);
       RETURN NEW;

   ELSEIF TG_OP = 'UPDATE' THEN
       UPDATE head_table SET a=new.a, b=new.b, c=new.c WHERE id = new.id;
       INSERT INTO tail_table VALUES (NEW.id, NEW.b, NEW.c)
           ON CONFLICT (id) DO UPDATE SET b=new.b, c=new.c;
       RETURN NEW;

   ELSEIF TG_OP = 'DELETE' THEN
       DELETE FROM head_table WHERE id = OLD.id;
       RETURN OLD;
   END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER big_table_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON big_table
FOR EACH ROW EXECUTE PROCEDURE big_table_trigger_func();

END;

Самая интересное в этом коде — возможность Postgres по созданию триггеров для представлений (появилось в Postgres 9.1). Логика обработки модификаций довольно очевидная:

  • при INSERT проводим вставку в обе таблицы

  • при UPDATE обновляем «головную» таблицу, а также пытаемся вставить в «хвост». В случае, если запись уже есть (т.е. вставлена ранее), обновляем значение.

  • DELETE выполняем на «головной» таблице — «хвост» обновится за счет внешнего ключа с каскадным удалением.

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

С данного момента, все новые данные будут разделены по новым таблицам. Нам необходимо смигрировать существующие данные. Переходим ко второму шагу.  Применяем подход из предыдущей миграции: 

INSERT INTO tail_table(id, b, c) SELECT id, b, c FROM head_table
WHERE id BETWEEN 1 AND 10000 ON CONFLICT DO NOTHING;
COMMIT;

INSERT INTO tail_table(id, b, c) SELECT id, b, c FROM head_table
WHERE id BETWEEN 10001 AND 20000 ON CONFLICT DO NOTHING;
COMMIT;

...

В случае если запись уже существует, просто игнорируем — ON CONFLICT DO NOTHING.

Аналогично предыдущему подходу можно реализовать цикл, примерно так: 

BEGIN
    FOR i IN 1..batch_count LOOP
        id_from := ...
        id_to := ...
       
        INSERT INTO tail_table(id, b, c)
        SELECT id, b, c FROM head_table
        WHERE id BETWEEN id_from AND id_to ON CONFLICT DO NOTHING;

        COMMIT;
    END LOOP;
END;

Заметьте, что мы не выполняем vacuum, поскольку INSERT не создает bloat.

Может появиться желание написать код несколько иначе, с использованием курсора – НЕ делайте так: 

-- DON'T DO THIS!
BEGIN
    FOR r in SELECT  FROM head_table LOOP -- holdable cursor
        INSERT INTO tail_table(id, b, c) VALUES (r.id, r.b, r.c) 
          ON CONFLICT DO NOTHING;

        i := i + 1;

        IF i % batch_size = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;

При выполнении такого кода открывается курсор, который будет «смотреть» на снимок данных, созданный Postgres для текущей транзакции. В момент коммита, снимок становится неактуальным. Для того, чтобы продолжить итерирование по курсору, Postgres сконвертирует курсор в так называемый holdable cursor, что фактически означает копирование снимка данных на диск. Очевидно, что для большой таблицы это может занять весьма продолжительное время.

Третий шаг — замена запроса представления.

После того, как все данные находятся в нужных таблицах мы проводим замену запроса представления для подготовки к удалению атрибутов из «головной» таблицы: 

CREATE OR REPLACE VIEW big_table AS
  SELECT h.id, h.a, t.b, t.c FROM head_table h
  LEFT JOIN tail_table t ON h.id = t.id;

Четвертый шаг — переводим приложение на работу с head_table и tail_table.

После предыдущего шага приложение может работать как по-прежнему с представление, так и с новыми таблицами — данные синхронизированы. Мы переключаем код на целевую модель данных и перестаем использовать big_table.

Пятый, последний, шаг — приборка. Удаляем представление и ненужные атрибуты: 

DROP VIEW big_table;
DROP FUNCTION big_table_trigger_func();
ALTER TABLE head_table DROP COLUMN b, DROP COLUMN c;

Если существовали внешние ключи на атрибуты b и c исходной таблицы. То перед приборкой необходимо создать аналогичные ограничения на «хвостовую» таблицу. В предыдущей статье мы рассматривали как сделать это наилучшим способом.

На этом миграция завершена. И наша статья тоже

© Habrahabr.ru