Добавляем столбец в PostgreSQL

В системе управления базами данных PostgreSQL для добавления столбца в уже существующую таблицу, как правило, требуется выполнить короткую блокировку уровня ACCESS EXCLUSIVE. Блокировка носит временный характер -- как раз на то время, которое потребуется для обновления системных таблиц каталогов (catalog tables). Рассмотрим нюансы этого процесса.

Установка дефолтных значений

Если устанавливать значения по умолчанию одновременно с созданием столбца, произойдет блокировка таблицы на время установки этих значений. Этого можно избежать следующим образом:

1. Добавляем новый столбец (без дефолтных значений).
2. Назначаем столбцу значение по умолчанию.
3. Заполняем этим значением по умолчанию уже имеющиеся строки по отдельности.

Важно!

В том же PostgreSQL 11 такие советы будут не актуальны для неволатильных дефолтных значений. Теперь добавление столбца с дефолтным значением потребует лишь обновления таблиц каталогов, тогда как все обращения к строкам без значения станут "магическим" образом возвращать необходимое значение.

Ограничения NOT NULL

Добавить столбец с NOT NULL-ограничением можно лишь в 2-х случаях:

• в таблице отсутствуют строки. Это тривиальный случай и потребуется лишь изменение каталога;
• был указан DEFAULT. В этой ситуации надо будет проделать действия, описанные выше для дефолтных значений.

Внимание!

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

По материалам статьи «PostgreSQL at Scale: Database Schema Changes Without Downtime».