Как добавить поле в большую таблицу 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: странно, но не нашёл такого распространённого вопроса здесь

Ответ

Если вы счастливый пользователь 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 берёт блокировку на таблицу и начинает перебирать все записи в таблице проставлять на уровне данных и только после этого отпустит блокировку. А вот если вставить default null - то PostgreSQL обновит только метаданные таблицы в служебном каталоге, что очень быстро, но это не то что требуется.
Поэтому внести желаемое изменение возможно, но выглядеть оно будет совсем не так, а в много действий.
Начинаем аккуратно вносить изменение
Сначала добавляем новое поле как null по-умолчанию - это быстро, только изменить метаданные таблицы. Затем ставим желаемое значение по-умолчанию - это значение уже будет применяться для новых вставляемых строк.
begin; set local statement_timeout = '1s'; alter table email_stats add column emails_paid_clicks bigint default null; 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 в данных
Длительный процесс, необходимо запросами
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;
Проверка ограничения не заблокирует запись.
Готово
Всё, поле добавлено

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