У вас есть таблица с ограничением первичного ключа для одного столбца integer (пусть это будет id), а количество записей близко (или скоро достигнет) ~ 2b. В этом случае значения столбцов скоро переполнятся, и ваша база данных начнет отклонять новые вставки:

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

Чтобы избежать этого сценария, вам необходимо перенести тип столбца на bigint. Если столбец имеет значение по умолчанию, созданное последовательностью (например, последовательность автоматического увеличения по умолчанию), вам также необходимо перенести последовательность.

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

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

Этого можно добиться, используя … IF NOT EXISTS … при создании объектов, удаляя существующие объекты, если они существуют, и т. д. Вы увидите такие блоки в приведенных ниже примерах.

Тестирование

В целях тестирования хорошо следовать тем же условиям, что и в продакшене:

  1. Запустите скрипт для того же (или почти того же) тома таблицы.
  2. Запустите скрипт для таблицы под нагрузкой (т. е. используемой приложением).

Предлагается использовать следующие фрагменты:

1. Создайте примитивную таблицу (мы будем запускать миграцию для нее в будущем):

2. Заполните таблицу достаточным количеством данных (например, 10 млн записей — этого будет достаточно):

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

Простое решение

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

Эта команда должна будет перезаписать всю таблицу, потому что bigint занимает 8 байт памяти, а не 4 байта integer. Пока это происходит, таблица будет заблокирована от параллельного доступа, поэтому с большой таблицей вы должны быть готовы к более длительному простою.

Если вы предполагаете, что это может понадобиться, выполните изменение как можно скорее, пока таблица еще мала.

Примечание о последовательных типах

В документации PostgreSQL ПОСЛЕДОВАТЕЛЬНЫЕ типы упоминаются следующим образом:

Типы данных smallserial, serial и bigserial не являются истинными типами, а являются просто удобными обозначениями для создания столбцов уникальных идентификаторов (аналогично свойству AUTO_INCREMENT, поддерживаемому некоторыми другими базами данных). В текущей реализации указание:

CREATE TABLE tablename (
colname SERIAL
);

эквивалентно указанию:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

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

Миграция на месте

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

Алгоритм состоит из четырех шагов:

  1. Создайте новый столбец нужного типа (bigint). Это можно сделать двумя способами:
    — директивой NOT NULL DEFAULT <CONST> — это работает без простоев для PostgreSQL 11+ (в статье мы продолжим этот подход),
    — сделать столбец обнуляемым, что потребует дополнительного ограничения «не нуль» в будущем для ограничения первичного ключа.
  2. Дублируйте id значений в новый столбец.
    — Для новых вставок мы можем настроить триггер или обработать это в коде приложения.
    — Для существующих значений мы будем делать это в пакетах с промежуточным засыпанием ( зачем вам партии).
  3. Одновременно создайте уникальный индекс для нового столбца. Это потребуется для создания ограничения первичного ключа в будущем.
  4. Продвигайте новый столбец как новый первичный ключ.

Создание нового столбца

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

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

Дублирование значений столбцов

Пакетное обновление довольно просто:

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

Есть несколько способов реализовать это:

  • Первый подход будет полагаться на некоторый параметр конфигурации (например, переменную среды, предоставленную разработчиком, или временно сохраненное значение в базе данных), чтобы узнать последнюю обновленную запись и начать обновление с нее.
  • Второй подход не требует никаких переменных — он пробежит записи таблицы в порядке возрастания первичного ключа и найдет первое значение, допускающее значение null new_column (если столбец допускает значение null) или первое значение, не равное константе по умолчанию, указанной при создании столбца. время (такая константа по умолчанию не должна совпадать с какими-либо существующими записями!). Здесь мы сканируем тот же объем данных, что и обновлялся, но делаем более быструю операцию «чтения» вместо «обновления».
  • Что-нибудь еще, способное обнаруживать обновленные или не обновленные записи.

Создание уникального индекса

Ограничение первичного ключа можно разделить на два ограничения:

  • NOT NULL
  • UNIQUE

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

Создание уникального индекса с нулевым временем простоя выполняется с помощью следующей конструкции:

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

Удалить индекс без блокировки одновременных операций выбора, вставки, обновления и удаления в таблице индекса. Обычный DROP INDEX получает блокировку ACCESS EXCLUSIVE на таблицу, блокируя другие обращения до тех пор, пока удаление индекса не будет завершено. При использовании этого параметра команда ожидает завершения конфликтующих транзакций.

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

Повысить уровень нового первичного ключа

Последняя часть головоломки — продвижение нового столбца в качестве нового первичного ключа. В общем, мы хотели бы получить то же имя (id) в качестве первичного ключа. Для этого мы должны выполнить следующие шаги:

  1. Очистить временные объекты (например, триггер).
  2. Переименуйте новый столбец в id.
  3. Обновите последовательность по умолчанию до типа bigint.
  4. Добавьте ограничение первичного ключа для свежего столбца id, используя уникальный индекс (а не ограничение null, если вы решили создать новый столбец без постоянного значения по умолчанию).

Вот SQL-скрипт для этого (обратите внимание, что он должен выполняться в одной транзакции):

Пост-релиз

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

Чтобы помочь планировщику запросов создавать более эффективные планы для запросов, мы также должны запустить ANALYZE. VERBOSE дополнительно распечатает статистику каждой очищенной таблицы.

И мы можем объединить эти два:

VACUUM (VERBOSE, ANALYZE) test_table;

Неизменяемая миграция

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

Алгоритм состоит из следующих шагов:

  1. Создайте новую таблицу с необходимыми изменениями.
  2. Дублировать новые вставки/обновления/удаления исходной таблицы в новую таблицу с помощью триггеров.
  3. Скопируйте строки исходной таблицы, существовавшие до двух предыдущих шагов, в целевую таблицу.
  4. Поменяйте имена таблиц.

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

Пример переноса таблицы

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

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

Удачной миграции!