Ещё лет 5 назад на собеседованиях с backend-разработчиками был популярен вопрос: как переименовать колонку в высоконагруженной таблице (возможны вариации: как сделать любой другой alter table)? Ответ мог быть примерно следующий:
Для начала создаём новую колонку (или даже таблицу с нужной структурой). Далее делаем триггеры на старую колонку, которые вносят все поступающие изменения на новую колонку. Параллельно с этим синхронизируем уже добавленные данные из старой колонки в новую. Через какое-то время данные актуализируются, и задача будет решена.
Вопрос всё ещё часто звучит на собесах. И актуальность его выросла, так как за последние 5 лет стало намного больше масштабных проектов с высокой нагрузкой.
В MySQL существуют специальные инструменты для решения подобных задач: pt-online-schema-change и gh-ost. Принцип их действия практически такой же, как описанный выше.
Хочу рассказать, как мы делали alter table в нашем микросервисе с высоконагруженной таблицей (golang + postgres, ~1500rps, ~15 млн. записей).
Постановка задачи
В одном из проектов, над которым мы работаем в Каруне, задача стояла немного сложнее, чем просто "переименовать колонку": необходимо было захешировать данные одной колонки. Мы решили сделать это через создание новой колонки и перенести функционал на неё.
Шаг 1. Добавляем колонку
Для начала мы создали колонку и повесили на неё индекс:
ALTER TABLE my_table ADD COLUMN IF NOT EXISTS field_encrypted TEXT;
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS field_encrypted_unique_idx
ON my_table (field_encrypted);Выполненные операции не создавали никаких блокировок.
Шаг 2. Переливаем старые данные
Перезалив данных из старой колонки в новую мы реализовали через cron библиотеку языка golang. Такое решение приняли из-за удобного логирования возникающих проблем и возможности быстрого реагирования на них. Да и сторонников делать через логику базы данных у нас не нашлось.
Конечно, сразу же возникли трудности следующего вида. В принципе, аффектить базу данных сильно нельзя — это увеличивает время ответа сервиса (>1 sec — это уже плохо) и количество блокировок. Зависимые сервисы могут не обработать ответы, появится неконсистентность. Или может спровоцироваться ещё более сильная нагрузка на базу данных, и сервис может не выдержать.
Поэтому:
Все изменения для новой колонки делались батчами, а не по одной записи (batch insert, batch update).
У нас запущено несколько инстансов этого микросервиса. Поэтому необходимо было не допустить многократный запуск этого скрипта в одно время. Тут нам помогла advisory lock для postgres (каждый инстанс проверял блокировку и только 1 выполнял джобу):
SELECT pg_try_advisory_xact_lock(1);Периодически для базы запускался достаточно тяжелый VACUUM:
Во время его работы также нежелательно запускать скрипты по описанной выше причине. Поэтому мы должны были проверять, не запущен ли он.
SELECT check_vacuum('my_table');Сама функция check_vacuum:
CREATE FUNCTION check_vacuum(name text) RETURNS boolean
LANGUAGE sql SECURITY DEFINER
AS $_$
SELECT count(*)::int > 0
FROM pg_stat_progress_vacuum
WHERE relid::regclass = $1::regclass OR
relid::regclass::text in (
SELECT reltoastrelid::regclass::text FROM pg_class WHERE relname = $1
);
$_$;После того, как мы перегнали все данные, необходимостью стало переключение на работу с новой колонкой.
ШАГ 3. Вешаем constraint
Для новой колонки требовался constraint not null. Но напрямую сделать этого нельзя, иначе можно надолго заблокировать таблицу, что недопустимо. Поэтому в postgres существует следующий механизм:
ALTER TABLE my_table ADD CONSTRAINT field_ecnrypted_not_null
CHECK (field_encrypted IS NOT NULL) NOT VALID;Он позволяет повесить ограничение not null, но не валидировать существующие записи сразу, а сделать это следующим шагом и без блокировок:
ALTER TABLE my_table VALIDATE CONSTRAINT field_ecnrypted_not_null;Ну и в заключение мы сделали то, к чему шли:
ALTER TABLE my_table DROP COLUMN old_field;Заключение
Данные шаги позволили бесшовно, незаметно для пользователей нашего микросервиса переименовать колонку и добавить хеширование к ней. Данный подход мы реализовали примерно за неделю — у нас не было дедлайна. Но если вдруг вы едете на горящем велосипеде, и всё вокруг горит, то можно справиться и за пару дней.
Интересно узнать, как вы решаете похожие задачи на MySQL. Пишите в комменты, с удовольствием почитаю и подключусь к обсуждению.