Что происходит в момент добавления нового столбца в БД?

Question

[beduin01]

Документацию почитал. Вопросы остались.
Вот есть таблица с 1000 записей. В ней 9 колонок. На 3 из них есть индексы.
Я добавляю новую колонку и...

1. Возможно ли дозапись новой колонки в существующую структуру? Или под капотом происходит полное копирование всех данных в новую таблицу (табличное пространство?)
2. Происходит ли пересоздание индексов?
3. Происходят ли какие-то еще важные для понимания сути работы в БД процессы?

Comments

[mayton2019]

Тут разные dbms ведут себя по разному. Вообще добавление столбца формализовано только на уровне SQL.
Реализация может быть любой. К примеру для Oracle (при отсутствии активных транзакций) добавление
столбца идет мгновенно. Это модификация словаря метаданных. Но учитывая DDL класс операций
- ее нельзя параллельно "подпихнуть" к действующим транзакциям.

Для графовых и всяких NoSQL систем где schema-less модель хранения данных - не требуется вообще ничего.
Столбец добавляется прозрачно как будто и всегда был. Вернее сказать таблице безразлично число столбцов
и их имена. Просто начинаешь вливать данные с новой колонкой и все ОК. Но там другая беда - нельзя сделать
desc. Впрочем - надо смотреть по каждой. NoSQL - это сильно широкая метафора.

Answer 1

[Melkij]

https://ru.stackoverflow.com/q/721985/203622

Вот есть таблица с 1000 записей. В ней 9 колонок. На 3 из них есть индексы.

На такой мелочи без разницы. Перезапись такой крохи штука быстрая.

1. Возможно ли дозапись новой колонки в существующую структуру? Или под капотом происходит полное копирование всех данных в новую таблицу (табличное пространство?)

В зависимости от конкретного alter table add column.
Если вы добавляете add column score float default random() - то будем переписывать всю таблицу и перестраивать все существующие индексы.
Если default с константой и у вас pg11 или новее - то обновим в системном каталоге описание таблицы, а саму таблицу трогать не будем.
Если нет default (т.е. null) - то без оглядки на версию базы просто обновим описание таблицы и сами файлы таблицы трогать не будем.

2. Происходит ли пересоздание индексов?

да, если выполняется table rewrite

3. Происходят ли какие-то еще важные для понимания сути работы в БД процессы?

классическая история частых даунтаймов, что для операции нужна эксклюзивная блокировка. То есть для взятие блокировки не должно быть совсем никого, кто эту таблицу трогал в транзакции, начавшейся до alter table. Если у вас в это время работает какая-то аналитика на 10 минут или pg_dump - будут приключения, нужен statement_timeout

Comments

[beduin01]

Спасибо за развернутый ответ. У меня на самом деле 300 млн строк и добавляю я тип данных series. Это к какому случаю относится? "Если вы добавляете add column score float default random() - то будем переписывать всю таблицу и перестраивать все существующие индексы." Это? Или другое?

> да, если выполняется table rewrite

А вот тут не совсем понял. В каких случаях это произойдет?

[Melkij]

Смотря как этот тип series объявлен. Среди штатных сотен типов данных типа с таким именем нет, но ведь любой новый тип может быть произвольным образом создан, postgresql штука очень расширяемая. Ваш собственный ли это тип данных или это из какого-то расширения, может быть domain, композитный, или полностью базовый тип.

В общем случае можно создать табличку аналогичной структуры на аналогичной версии базы и посмотреть в

set client_min_messages = 'DEBUG';
alter table ... add ...

Если от alter table будет сразу строка
DEBUG: rewriting table "..."
то проблема, этому add column нужно переписать всю таблицу целиком.

Answer 2

[vreitech]

я не понял, что имеется ввиду под дозаписью колонки в существующую структуру. если колонка создаётся без указания default-значения, то перезаписи строк в таблице не происходит, меняется лишь заголовочная часть (в которую добавляется информация о новой колонке) и битовая карта null'ов (относительно дешёвая операция). если колонка создаётся с указанием default-значения, то происходит пересоздание (удаление и новая вставка) всех строк в таблице с добавлением нового поля с default-значением (со всеми вытекающими в виде сбросов грязных страниц на диск, частичным вымыванием кеша, генерацией WAL, запусками автовакуума). индексы при этом также перезаписываются в моменты проходов автовакуума (при вычищении мёртвых версий строк указатели в индексах меняются на актуальные версии строк).

Comments

[beduin01]

А если потом ей добавить дефолтное значение? То что произойдет?

[Melkij]

если колонка создаётся без указания default-значения, то перезаписи строк в таблице не происходит, меняется лишь заголовочная часть (в которую добавляется информация о новой колонке) и битовая карта null'ов (относительно дешёвая операция)

bitmap не обновляется, т.к. это опять же перезапись таблицы делать придётся. Новую bitmap и дефолтные значения потом проставляет update или другие table rewrite

происходит пересоздание (удаление и новая вставка) всех строк

строки не удаляются, пересоздаются напрямую файлы relfilenode. Старые файлы потом удаляем.

индексы при этом также перезаписываются в моменты проходов автовакуума (при вычищении мёртвых версий строк указатели в индексах меняются на актуальные версии строк).

автовакуум таким не занимается. В индексы при записи данных вставляются ссылки на все версии строк. вакуум только удаляет ссылки на те строки, которые затем удаляет.
При перезаписи таблицы в alter table, делается вполне обычный аналог create index на новом файле данных.