PostgreSQL. Добавляем not null constraints в большие таблицы

Проекты развиваются, клиентская база увеличивается, базы данных разрастаются, и наступает момент, когда мы начинаем замечать, что некогда простые манипуляции над базами данных требуют более сложных действий, а цена ошибки сильно повышается. Уже нельзя за раз промигрировать данные с одного столбца в другой, индексы лучше накатывать асинхронно, добавлять столбцы с default значениями теперь нельзя.

Одной из команд, с которой надо быть осторожным на таблицах с большим количеством записей, является добавление not null constraint на столбец. При добавлении данного constraint PostgreSQL приобретает access exclusive lock на таблицу, в результате чего другие сессии не могут временно даже читать таблицу; затем БД проверяет, что в столбце действительно ни одного null нет, и только после этого вносятся изменения. Под катом я рассмотрю различные варианты, как можно добавить not null constraint, лоча таблицу на минимально возможное время или даже не лоча ее совсем.

TL;DR:

1. В PostgreSQL 12+ можно добавить check constraint на таблицу, а затем "преобразовать" его в not null constraint для конкретного столбца.
2. Чтобы полностью избежать блокировки таблицы, можно напрямую внести изменения в системную таблицу pg_attribute (этот пункт подробно разбирается в статье).

PostgreSQL постепенно добавляет новые возможности для работы с огромными таблицами. В 8.2.0 добавилась возможность создавать индексы асинхронно через index concurrently, в 9.1.0 была добавлена возможность создавать foreign constraints с флагом not valid и позже вызывать validate для этих constraints, чтобы не блокировать таблицу надолго. Но пока что ничего такого для not null constraint добавлено не было. Было бы удобно сделать что-то вроде

... set not null not valid;

, чтобы завалидировать constraint позже без блокировки таблицы. Или можно было бы дать возможность добавить constraint без валидации, если я уверен, что в столбце нет nulls:

... set not_null_but_dont_check_anything_because_i_checked_myself_i_swear;

К сожалению, первый вариант еще не был никем закоммичен в PostgreSQL, но я уверен, что это будет сделано в будущем. К счастью, второй вариант пока тоже не реализован, и будем надеяться, что это так и останется в будущем. Хотя, может быть, дать больше возможностей разработчику и доверять ему, когда он просит не проводить дополнительную валидацию, — это не такая плохая идея?

Итак, какие же варианты у нас присутствует, если мы решили сделать какой-то столбец not null.

1. Just do it!

Не все БД — высоконагруженные, не во всех таблицах миллионы записей, не все базы данных должны быть доступны 24/7. В общем, если вы можете просто добавить constraint и время блокировки таблицы для вас приемлемо, то вам так и следует сделать.

2. Just… don't do it?

Это больше вредный совет, чем директива к действию, но можно просто не добавлять not null constraint и делать все проверки на уровне бизнес-логики в коде приложения. Но, конечно, наличие constraints на уровне БД — это вещь полезная и то, от чего не стоит отказываться. А уж если БД разрослась настолько, что просто так добавить not null, не затронув пользователей, нельзя, то тем более стоит по максимуму использовать инструменты валидации информации, предоставляемые базой данных: unique constraints, not null constraints, foreign key constraints, etc.

3. Проиндексируйте столбец

Если столбец позже будет использован для фильтрации данных или является foreign key, то наличие индекса будет логичным. В этом случае добавление not null constraint будет осуществляться несравнимо быстрее, поскольку базе будет достаточно просто проверить, присутствуют nulls в столбце или нет.

Однако, если индекс на столбце не нужен, то добавление индекса, просто чтобы быстрее добавить not null constraint, выглядит, как лишняя работа и явный workaround. Но, безусловно, это вариант решения проблемы.

Update: Melkij, контрибьютер в PostgreSQL, указал на то, что индексы при добавлении constraints не используются. Т.е. добавление индекса не сделает добавление constraint более быстрым.

4. Добавьте check constraint и ограничьтесь этим

PostgreSQL пока не умеет добавлять not valid not null constraints, но он уже умеет добавлять not valid check constraints. Выглядит это так:

alter table MY_AWESOME_TABLE add constraint CHECK_MY_AWESOME_TABLE_ON_MY_SPLENDID_COLUMN check (MY_SPLENDID_COLUMN is not null) not valid;

После этого можно вызвать

alter table MY_AWESOME_TABLE validate constraint CHECK_MY_AWESOME_TABLE_ON_MY_SPLENDID_COLUMN;

и вуаля: not null constraint добавлен! Команда validate приобретает мягкий share update exclusive lock, который не мешает остальным сессиям читать и менять данные в таблице.

Какие могут быть проблемы с этим решением? Во-первых, растет сложность поддержки и последующих изменений в БД. Собрать информацию, какие столбцы nullable, а какие — нет, сложнее, и следовательно, больше риск совершить ошибку. Если проект разрастется, то неконсистентность в решениях может представить проблему. С другой стороны, если проект развился до такой степени, значит, скорее всего, дела идут неплохо, и есть средства, которые можно выделить на то, чтобы привести базу в порядок.

Во-вторых, существуют инструменты, которые могут сравнивать схемы базы данных и код приложения для обнаружения потенциальных ошибок в настройке ORM. Например, в проекте, ради которого я и занялся изучением вопроса быстрого добавления not null constraints, используется CUBA Studio, которая умеет при старте приложения на машине разработчика проверять, полностью ли совпадает конфигурации базы данных с Java классами сущностей. Пока что Studio не умеет сравнивать not null constraints и check constraints, и поэтому если поле в классе помечено JPA аннотацией @NotNull, а в БД столбец является nullable, будет выведено предупреждение и предложен SQL update скрипт, который приведет таблицу к консистентности с кодом. Это, конечно, все можно заигнорить и работать дальше, тем не менее это полезный инструмент, который помогает находить ошибки, и я лично не хотел бы от него отказываться.

Но если вы не используете никакое ПО для анализа структуры БД и вас не пугает не консистентный подход к проставлению not null constraints, то использование check constraints является адекватным способом решения проблемы. Тем более, что в будущем всегда можно будет заменить check constraints на обычные not null constraints, а в PostgreSQL 12 это еще и не вызовет долгого лока на таблице. Об этом рассказывает следующий пункт.

5. Добавьте check constraint, но не ограничивайтесь этим

В PostgreSQL версии 12 было представлено интересное улучшение: БД не полезет проверять все записи на отсутствие nulls, если другой constraint уже гарантирует, что в столбце nulls нет. Если у вас включены логи уровня DEBUG1, то там вы увидите сообщение вида: "existing constraints on column MY_AWESOME_TABLE.MY_SPLENDID_COLUMN are sufficient to prove that it does not contain nulls".

Release notes содержат краткое описание этого нововведения, но release notes настолько объемны, что пропустить такие приятные маленькие улучшения достаточно легко.

Что касается более ранних версий PostgreSQL, где этой фичи еще нет, то not null constraint можно добавить напрямую в системную таблицу. Наличие check constraint гарантирует, что в столбце нет nulls, поэтому опасности что-то сломать — нет.

6. Измените системную таблицу напрямую

Как вообще хранится not null constraint в PostgreSQL? На самом деле, очень просто. Есть таблица pg_attribute. В этой таблице для каждого столбца в БД содержится отдельная запись, в которой есть такие данные, как: имя столбца, тип данных, порядковый номер (изменение этого атрибута гарантированно зафакапит вам таблицу), есть ли у столбца дефолтное значение, и т.д. В том числе здесь есть boolean столбец attnotnull, который как раз и определяет, могут ли храниться в определенном столбце nulls или нет.

На этом пункте давайте остановимся поподробней.

Разумеется, при прочих равных лучше использовать рекомендуемые в документации способы работы с БД и не лезть в системные таблицы. Через системные таблицы можно сделать много чего интересного (добавить дефолтное значение, добавить foreign constraints, etc.), но, конечно, безопасней все-таки это делать привычными способами: через "alter table MY_AWESOME_TABLE add constraint …". Это минимизирует вероятность выстрелить себе в ногу. Да и коллегам не надо ничего лишнего объяснять, добавляя миграционные SQL скрипты в репозиторий. Кроме того, то, что сейчас можно поменять not null constraint напрямую через pg_attribute, не значит, что в будущих версиях PostgreSQL это не вызывает неожиданный side-effect.

Но конкретно с not null constraint я считаю, что игра стоит свеч. По сути, нет нормального способа сделать столбец not null без долгой блокировки таблицы. Добавление check constraint с его опять же последующим удалением — это суть workarounds, которые базируются на знании того, как работает PostgreSQL. И при использовании этих workarounds коллегам все равно придется объяснить, как это все работает.

В итоге, я пришел к такому скрипту:

-- alter table MY_AWESOME_TABLE alter column MY_SPLENDID_COLUMN set not null ;
do $$
    begin
        if (select exists (select 1 from MY_AWESOME_TABLE where MY_SPLENDID_COLUMN is null limit 1)) then
            raise exception 'MY_AWESOME_TABLE.MY_SPLENDID_COLUMN contains null values';
        else
            update PG_ATTRIBUTE set ATTNOTNULL = true
                where ATTRELID = (select OID  from PG_CLASS where RELNAME = lower('MY_AWESOME_TABLE'))
                    and ATTNAME = lower('MY_SPLENDID_COLUMN');
        end if;
    end $$;

Проверка select exists здесь на всякий случай. Если я добавляю not null constraint, то я уверен на 100%, что все приложения, имеющие доступ к БД, null в этот столбец не проставляют.

Дальше идут технические подробности. Если они вам не интересны, можете смело прыгать к заключению.

Для начала, хочу заметить, что в прошлом такой workaround был даже прописан в документации PostgreSQL: "It is possible to change this column to enable or disable the constraint." В 10.12 этот комментарий был удален. Самое досадное в коммите — это то, что в сообщении коммита не содержится информации, почему документация изменена. Сообщение выглядит так: "docs: remove mention that attnotnull should be changed". Если бы было добавлено что-то вроде "because it's a bad practice", или "because I was asked by Tom to do it", или "because recent changes in the attribute cache made it unsafe to work with the table directly", или даже "just because I decided so", это было бы гораздо информативней. Я полагаю, что изменение в документации связано с тем, чтобы не поощрять разработчиков лезть туда, куда их не просят, дабы они не наломали там дров. Тем не менее, я решил проверить, что теоретически может пойти не так, чтобы не было сюрприза позже.

Update: в комментариях дали ссылку на обсуждение, которое привело к тому, что из документации был убран совет вручную менять attnotnull: https://www.postgresql.org/message-id/flat/20140724122945.GD16857%40alap3.anarazel.de.

Итак, что же теоретически может пойти не так? Очевидно два проблемных сценария: constraint был добавлен на столбец, в котором все-таки были nulls; в PostgreSQL возможен кэш для системных таблиц, который может быть не обновлен при изменении таблиц напрямую.

Проверить оба сценария — вопрос пары минуты. Можно успешно добавить not null constraint на столбец, где есть nulls. Select в этом случае продолжает работать корректно, без ошибок. Выглядит так, будто constraint нужен только для проверки входных значений при update/insert, и не проверяется в остальных случаях. Также все успешно работает при добавлении constraint в одном коннекшене и проверке, что все ок, — в другом. Т.е., если кэш и есть, то он корректно обновляется. Таким образом, базовые тест-кейсы были прогнаны успешно.

Разумеется, эти проверки — поверхностны, и я мог что-то не учесть, что потом вызвало бы проблему. Поэтому я поступил так, как показалось мне наиболее разумным: перекрестился и накатил скрипт на проде пошел в исходники. Цели было три: пробежаться по всему, что происходит при добавлении not null constraint; найти все места, где используется attnotnull, чтобы осознавать, какова цена ошибки; понять, как кэшируется/шарится pg_attribute между сессиями (ведь должна же информация, которая используются буквально при каждом запросе к БД, как-то кэшироваться?).

С первым пунктом все достаточно просто. Логика сконцентрирована в ATController. Вкратце и упрощенно: перед вызовом данной функции PostgreSQL лочит таблицу; затем проверяет, есть ли у меня permissions на таблицу, не является ли таблица системной; обновляет таблицу pg_attribute; если нет check constraint, который гарантировал бы, что в таблице нет nulls, то проходится по всей таблице, чтобы удостоверится, что nulls действительно отсутствует (собственно, вот этот full table scan и послужил причиной написания статьи). Ничего такого, что заставляет сомневаться, не бросить ли идею с прямым редактирование системной таблицы.

Далее, проверяю всю логику в проекте, завязанную на attnotnull, чтобы понимать, чем чревато, если в not null столбце есть nulls. Как ожидалось, проверки происходят при update и insert, но это далеко не все. Проверки также осуществляются при логической репликации (logical replication), которая впрочем тоже не должна создать проблемы. Проверки осуществляются при восстановлении БД из SQL бэкапа (через pg_dump, например). В этом случае восстановление из бэкапа не упадет, но ошибочные записи в таблицу вставлены не будут. Но это все тоже можно условно отнести к “проверкам при update и insert”. Затем идет самое интересное: attnotnull проверяется при slot_compile_deform. Суть такова: для ускорения загрузки записей с жесткого диска PostgreSQL может использовать JIT компиляцию. Для каждой таблицы может быть сгенерирован нативный код для анализа записей (преобразования байтового представления в логическое). При генерации данного кода проверяется атрибут attnotnull. Если он равен true, то компилятор считает, что в столбце точно не может быть null, и использует эту информацию позже, чтобы избежать лишних проверок. JIT компиляция появилась в PostgreSQL 11 и там она еще по умолчанию выключена. В PostgreSQL 12 JIT компиляция уже включена по умолчанию.

Таким образом, мое изначальное предположение о том, что флаг attnotnull используется только при записи данных и при чтении данных точно ничего не поломает, было ошибочным. Проставление attnotnull = true для столбца, в котором есть null, может и не вызвать никаких проблем в момент осуществления этой операции, но при миграции на будущие версии может оказаться неожиданный сюрприз.

По кэшу системных таблиц. Здесь все хорошо. При внесении изменений вызывается функция CacheInvalidateHeapTuple, которая при необходимости сбрасывает кэши для pg_class, pg_attribute, pg_index. Следовательно, можно не беспокоиться, что изменения в одной сессии будут проигнорированы в другой сессии.

Итак, анализ кода показал, что проставление not null constraint — это простое изменение attnotnull в таблице pg_attribute. Никаких дополнительных действий (помимо множества проверок и обновления кэшей) — не происходит. Но ошибаться ни в коем случае нельзя, поскольку PostgreSQL полагается на этот параметр в большом количестве мест, в том числе, и при операциях чтения (при включенной JIT компиляции).

Хочу заметить, что я проверял только вариант, когда в БД не используются наследование таблиц и секционирование (partitioning). Там могут быть свои нюансы, поэтому перед внесением изменений в attnotnull, лучше ознакомиться с функцией ATController в PostgreSQL.

Итог

Если у в продакшене крутится PostgreSQL 12+, то можно сначала добавить check constraint на всю таблицу, а потом уже добавить not null constraint к столбцу. В этом случае БД не будет делать лишнюю проверку на наличие nulls в столбце.

Тем, кто хочет добавить not null constraint очень быстро и не видит ничего зазорного в работе с системными таблицами, можно напрямую внести изменения в таблицу pg_attribute.

Автор: Станислав Суров

Источник