Как добавить поле в большую таблицу PostgreSQL без блокировки?

#sql #postgresql

Есть таблица вот такого вида:

create table email_stats (
    id bigserial primary key,
    mailing_list_id int not null,
    sended_date timestamp not null,
    emails_send bigint not null default 0,
    emails_clicks bigint not null default 0
);


Теперь в неё необходимо добавить новое поле. Так-то задача простая,

alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;


Вот только проблема: табличка уже размером в несколько десятков гигабайт и этот alter
table блокирует всю запись в таблицу на длительное время.

Как можно добавить поле без простоя системы?

PS: странно, но не нашёл такого распространённого вопроса здесь
    

Ответы

Ответ 1

Если вы счастливый пользователь PostgreSQL 11 или новее (но не надо бета-версии в
рабочие проекты ставить) - то все хитрые фокусы теперь спрятаны внутри и достаточно
напрямую сделать alter table:

set statement_timeout to '1s';
alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;


Принятый в postgresql 11 патч позволяет больше не копировать всю таблицу заново при
добавлении нового поля со значением по-умолчанию. И проблему с длительным блокированием
таблицы может представлять только ситуация, когда alter table вынужден ждать получение
эксклюзивной блокировки из-за того что эту блокировку удерживают какие-то другие транзакции.
Потому имеет смысл ставить небольшой statement_timeout и повторять попытки выполнения
alter table.



Если же у вас postgresql версии ниже 11, то всё не так просто. В указанном запросе

alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;


к длительной блокировке таблицы приводит default 0. PostgreSQL берёт блокировку на
таблицу и начинает перебирать все записи в таблице проставлять на уровне данных и только
после этого отпустит блокировку. А вот если вставлять по-умолчанию NULL - то PostgreSQL
обновит только метаданные таблицы в служебном каталоге, что очень быстро, но это не
то что требуется.

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

Начинаем аккуратно вносить изменение

Сначала добавляем новое поле как null по-умолчанию - это быстро, только изменить
метаданные таблицы. Затем ставим желаемое значение по-умолчанию - это значение уже
будет применяться для новых вставляемых строк.

begin;
set local statement_timeout = '1s';
alter table email_stats 
    add column emails_paid_clicks bigint;
alter table email_stats 
    alter column emails_paid_clicks set default 0;
commit;


Отдельного пояснения заслуживает зачем я изменил statement_timeout в транзакции.
Эта настройка ограничивает максимальное время выполнения запроса. Этот alter table
всё равно требует блокировку на таблицу, хоть и на короткое время и здесь есть скрытые
грабли: что если alter table не может взять блокировку из-за какой-то другой выполняемой
транзакции? Например, простой insert в другой транзакции не позволит взять блокировку
для изменения структуры. Но при этом запущенный alter table уже заблокирует все последующие
пишущие запросы к этой таблице. Короткий statement_timeout быстро убьёт alter table
и сервис продолжит работу. А попытку добавить поле можно безболезненно повторить чуть
позже пока это в итоге не удастся.

Есть ещё интересная грабля, что явное указание default null в alter table в каких-то
случаях может считаться значением NULL не эквивалентного типа данных и база начинать
опять же таблицу переписывать. Чтобы не ловить эту редкую граблю лучше не указывать
default null в явном виде, а оставить тот который подразумевается самим add column.
По-умолчанию там NULL и предполагается.

Поле добавили, теперь default в данных

Длительный процесс, необходимо запросами

update email_stats 
    set emails_paid_clicks = default 
    where emails_paid_clicks is null 
        and /* следующий кусочек данных */


проставить желаемое значение в имеющихся данных таблицы. Обновлять данные необходимо
кусочками (для чего в запросе оставил открытое условие), делая паузы между обновлениями,
следить за отставанием репликации (если такая есть) и за процессами autovacuum. Есть
несколько подходов по самому обновлению, более простой будет обновлять по первичному
или любому уникальному ключу. Берём любой язык программирования или сценариев по своему
вкусу и делаем что-то такое:

$current_id = (select min(id) from email_stats)
$maxid = (select max(id) from email_stats)

while ($current_id < $maxid) {
    $batch_to = $current_id + 10000 // максимальный размер пачки для одного обновления
    update email_stats 
        set emails_paid_clicks = default 
        where emails_paid_clicks is null 
            and id >= $current_id and id < $batch_to

    $current_id = $batch_to
    sleep(5) -- задержка между обновлениями чтобы меньше мешать сервису
}


Во время выполнения такого скрипта можно изредка делать vacuum email_stats чтобы
табличка не сильно увеличивалась в размерах. Особенно если autovacuum настроен недостаточно
агрессивно и не успевает прибирать за скриптом.

Размер одного обновления и величину паузы между обновлениями нужно подбирать под
профиль нагрузки конкретного сервиса. Маленькие обновления и большие паузы не будут
никому мешать, но просто выполняться скрипт будет очень долго.

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

Из подводных камней на этом этапе: гораздо проще поймать deadlock, подравшись с приложением
за обновление строк если приложение захочет обновить несколько строк из той же самой
пачки, но в другом порядке. Можно поставить соединению, в котором работает скрипт этого
обновления настройку deadlock_timeout = 100ms, тогда при взаимоблокировке будет как
правило убиваться наш скрипт, а не полезная транзакция приложения.

Ставим not null

Теперь у нас в таблице не должно быть null значений в добавленном поле, можно проставить
not null.

alter table email_stats alter column emails_paid_clicks set not null;


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

С минимальной блокировкой записи придётся отказаться от родного not null свойства,
зато можно добавить check ограничение с аналогичным свойством. Сначала добавляем ограничение
с указанием not valid (аналогично со statement timeout для транзакции)

begin;
set local statement_timeout = '1s';
alter table email_stats 
     add constraint emails_paid_clicks_not_null 
     check (emails_paid_clicks is not null) not valid;
commit;


Затем в другой транзакции уже без statement_timeout

alter table email_stats validate constraint emails_paid_clicks_not_null;


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

Для postgresql 11 и новее вся эта инструкция сильно короче стала, но для красоты
дополню ещё немного, не зря же старался, патч писал. Начиная с postgresql 12 alter
table set not null может пропустить проверку данных в таблице, если имеющихся ограничений
хватает чтобы поверить в отсутствие NULL в этом поле. Да, я как раз говорю про check
constraint который я только что описал как создавать без долгой блокировки. После того,
как ограничение прошло validate constraint можно вызвать set not null и удалить более
ненужный check. Для этого всё равно потребуется блокировка таблицы, но теперь уже короткая.

begin;
set local statement_timeout = '1s';
alter table email_stats alter column emails_paid_clicks set not null;
alter table email_stats 
     drop constraint emails_paid_clicks_not_null;
commit;


Готово

Всё, поле добавлено



Кстати, имеет смысл подумать, не использовать ли по-умолчанию именно NULL. Такое
поле не только сильно проще добавляется, но и существенно компактнее хранится. Значение
NULL - это один бит в битовой маске заголовка строки, Значение 0 типа bigint - уже
занимает 8 байт.