Как сделать alter table в высоконагруженной таблице

Ещё лет 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);
Время работы vacum'a
Время работы vacum’a

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

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. Пишите в комменты, с удовольствием почитаю и подключусь к обсуждению.

Читайте так же:

  • Замена камеры или аккумулятора в один клик. У Xiaomi может появиться модульный смартфонЗамена камеры или аккумулятора в один клик. У Xiaomi может появиться модульный смартфон Идея модульного смартфона, предполагающего быструю замену камеры. Экрана или аккумулятора. Известно давно. Но в реальном воплощении встречается не часто. Одной из самых массовых моделей стала Motorola Moto Z. Но и ее тираж был невелик – в отличие от обычных «цельных» смартфонов. Тем не […]
  • Закон о «приземлении» иностранных IT-компаний прошел второе чтениеЗакон о «приземлении» иностранных IT-компаний прошел второе чтение Госдума РФ одобрила во втором чтении законопроект. Обязывающий крупные ИТ-компании открывать представительства в России. Под его действие попадают соцсети. Иностранные издания и компании с ежедневной аудиторией в РФ от 500 тыс. человек. В рамках проекта закона ИТ-компании обязаны […]
  • Наполнение сайта на bitrixНаполнение сайта на bitrix Bitrix. Inc.. Ведущий разработчик систем управления контентом (CMS). Выпускает новую версию Bitrix Site Manager. Которая теперь включает в себя расширенные возможности SEO. Надежные инструменты для анализа производительности веб-сайта и другие необходимые функции для редактирования […]
  • Google добавил короткие названия компаний в их GMB-профилиGoogle добавил короткие названия компаний в их GMB-профили Специалисты отрасли заметили, что Google добавил короткие названия компаний, запущенные ранее в этом году. В их бизнес-профили. Это название теперь отображается на вкладке «О месте» (в англоязычной версии – «About») в разделе «Публикация» («Sharing»): В англоязычной выдаче это […]