Изменение типа столбца наследованной таблицы PostgreSQL

Question

Есть основная таблица, содержащая финансовые проводки, и есть куча помесячных партиций этой таблицы, добавленных через наследование. Возникла потребность заархивировать часть старых данных следующим образом: создать новую таблицу через like ИМЯ_ПАРТИЦИИ с сохранением зависимостей, заполнить эту таблицу через селект из этой же партиции, сагрегировав финансовые проводки до месяца и контрагента, затем убрать наследование с партиции, добавить наследование на таблицу-агрегат и дропнуть партицию.
Однако при попытке произвести агрегацию обнаружилось, что сумма за месяц не помещается в текущий размер поля с типом numeric(11,4). Альтерить родительскую таблицу и все зависимые следом, чтобы увеличить размер поля numeric, возможности нет: высоконагруженная БД, через которую проходит порядка 7-10 финансовых операций в секунду, самих партиций больше ста, а суммарное количество только живых строк стремится к полутора миллиардам. Создать таблицу без зависимостей с измененным типом также нет возможности, поскольку с родительской таблицы питается довольно много сервисов.
Какие есть варианты для обхода данной проблемы? Спасибо.

Roman Konoval · Answer

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

добавляем в родительскую таблицу новое (уже расширенное) поле без значения по умолчанию (то что без значения по-умолчанию - важно. Это быстрая операция даже на большой таблице)
добавляем триггер, который автоматически копирует значение из старого поля в новое, для всех новых/измененных записей
теперь копируем значение из старого поля в новое. Только делаем это не одной гигантской операцией, а что-то типа WITH BATCH_TO_UPDATE AS (SELECT id FROM T WHERE NEW_VALUE IS NULL LIMIT 1000) UPDATE T SET NEW_VALUE = OLD_VALUE WHERE id in (SELECT id FROM BATCH_TO_UPDATE) в цикле. Это можно делать неспешно, не останавливая систему, вожможно в часы, когда нагрузка поменьше и т.д. Конкретную форму этой операции нужно протестировать, конечно. Тут возможно потребуется создать индексы на new_value или использовать другой способ разбивки на пачки (может по id, если он из sequence генерируется - тут уж зависит от структуры таблицы). Думаю, основная идея понятна.
когда процес в п. 3 закончится, у всех записей в в приложении в новом поле будет правильное значение. Теперь переключаемся на использование нового поля в приложении.
удаляем старое поле
делаем агрегацию как планировали

Изменение типа столбца наследованной таблицы PostgreSQL

One Answer

Add your own answers!

Ask a Question