Техпрогон
ВойтиНачать бесплатно
Выбор ответа Senior Проектирование и нормализация обновлено

NOT NULL на огромной таблице без простоя

Вопрос

Нужно сделать колонку orders.customer_id NOT NULL на таблице в 500 млн строк. ALTER COLUMN ... SET NOT NULL проверяет всю таблицу под ACCESS EXCLUSIVE. Как сделать без долгой блокировки в PostgreSQL 16?

Попробуйте сами

Выберите вариант, и откроется разбор.

Показать ответ и разбор

Коротко

Правильный ответ: CHECK (... IS NOT NULL) NOT VALID, VALIDATE, затем SET NOT NULL. NOT VALID добавляет ограничение мгновенно и проверяет только новые строки.

Варианты ответа

  1. Выполнить ALTER COLUMN ... SET NOT NULL CONCURRENTLY вне транзакции
  2. CHECK (... IS NOT NULL) NOT VALID, VALIDATE, затем SET NOT NULLправильный ответ
  3. Создать уникальный индекс по customer_id: он запрещает NULL
  4. Обновить все NULL на 0 и сделать SET NOT NULL в той же транзакции

Разбор

NOT VALID добавляет ограничение мгновенно и проверяет только новые строки. VALIDATE CONSTRAINT проверяет старые под блокировкой SHARE UPDATE EXCLUSIVE, которая не мешает чтению и записи. С PostgreSQL 12 SET NOT NULL не сканирует таблицу, если уже есть валидный CHECK, доказывающий отсутствие NULL, так что ACCESS EXCLUSIVE берётся на мгновение; после этого CHECK можно удалить. У SET NOT NULL нет опции CONCURRENTLY. Уникальный индекс NULL не запрещает (NULL не равны друг другу). Массовый UPDATE в одной транзакции с DDL держит блокировки долго и раздувает таблицу.

Ответьте сами, а не прочитайте

В тренажёре вопрос попадёт в повторения и вернётся, когда начнёт забываться. Плюс билеты с зачётом: 20 вопросов, не больше 2 ошибок.

Потренироваться бесплатно

Предыдущий вопрос

ALTER TABLE повесил прод на пустом месте

Следующий вопрос

Удаление старых данных: DELETE или партиции

Урок по теме

Проектирование и нормализация за 6 минут

Термины по теме

ДенормализацияНормализацияСуррогатный ключ

Ещё по теме «Проектирование и нормализация»

Все вопросы модуля