Наполняем до краев: влияние порядка столбцов в таблицах на размеры баз данных PostgresQL

[kirik]

Спасибо за перевод. Один из немногочисленных технарских постов на хабре за последнее время.

[NikitchenkoSergey]

~10% места + повышение производительности: странно, что этого еще нет из коробки. Цифра пугающая, и вроде как минусов отдавать ответственность за порядок столбцов на диске на откуп СУБД нет. Ждем в 17 версии :)

[kirik]

Я знаю что в MySQL (innodb) добавление колонок в конец таблицы в разы дешевле вставки в середину/начало. С учетом схожести схемы хранения данных, я полагаю что отдав сортировку колонок на откуп БД мы значительно проиграем в альтерах. Но соглашусь, это можно было бы вынести как настройку в конфиг.

[KivApple]

В большинстве задач проиграть по производительности альтеров в пользу быстрых SELECT/INSERT/UPDATE/DELETE - норм, потому что альтеры происходят в большинстве приложений очень редко (только при обновлении схемы БД).

[kirik]

Всё так, но я больше про то, почему такое поведение не годится по умолчанию.

[igor_suhorukov]

Утилита postgres_dba

прячет подобные запросы за своим интерфейсом, анализирует все таблицы в PostgreSQL и выдает рекомендации по порядку столбцов и оценку экономии занимаемого места в %. Я успешно использовал этот подход в проекте openstreetmap_h3, где объем БД уже пол терабайта и будет только увеличиваться со временем.

[tba]

Спасибо. Стоило написать статью ради этого комментария.

[igor_suhorukov]

На здоровье! Мне про эту проблему с выравниванием и postgres_dba рассказал мой бывший коллега-DBA Павел, когда оптимизировали с ним БД на несколько терабайт. Сейчас так просто всех утилит и не найти, за таким обилием информации.

[plumqqz]

"Postgres тоже использует размер выравнивания в восемь байтов. "
Нет, см. pg_types, колонка typalign
Все, разумеется, описано в документации.
Дальше читать не стал и другим не советую.

[komii]

"Например, стандартное 32-битное целое число, которое может хранить значение немного более четырёх миллиардов, всегда считывается как четыре байта. То есть, даже если значение числа равно нулю, под него выделяется четыре байта памяти. Это называется выравнивание (alignment)."
Это называется не выравнивание, а тип с фиксированным размером, если мне память не изменяет.
А вот булы, например, занимающие 8 бит - уже выравнивание.

[m0tral]

Вот тоже прочитал, либо перевод корявый, либо автор совсем не в теме)

Булы, кстати, не корректный пример) не встречал чтобы где то они паковались в биты)

Байт который хранится по смещению в 4 байта, и занимает 4байта, вот это выравнивание, архитектурно понять это просто, когда нужно читать большие объемы данных, проще одной инструкцией процессора читать один тип 32бита, плюс это быстрее по скорости.

Плюс инструкции бывают оперируют именно с адресацией по dword

[komii]

Базово выравнивание происходит как правило по 1 байту (как раз упомянутые 8 бит), так уж повелось. Меньшими единицами процессор не оперирует (не считая специфичных вариантов). Конкретно булы может и пакуются в 4 байта какими-то компиляторами, но это уже зависит от реализации и настроек. Но можно запаковать и в 1 байт, так или иначе.

К тому же подменено понятие полезной информации по сути в статье. Для инта все нули, которые казалось бы не значащие, ибо "на листочке" можно записать одной цифрой, на самом деле являются значимыми - обозначают, что там именно нету единицы, а значит 2^i не участвует в формировании числа. То есть это не мусорная информация. При выравнивании же булов - все (будь это 1 * 8 бит или 4 * 8 бит) кроме одного бита - мусор.

Ну и привязываться к одной архитектуре - 32 битной - странновато (:

[SGordon123]

Для 1с это актуально, она сама столбцы не тасует ?

[MihaTeam]

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

[SGordon123]

Там ведь не только пользовательские поля, и таблицы разные бывают ... Мне интересно разрабы об этом задумывались?

[zuek]

Сильно сомневаюсь, что в 1С предусмотрена оптимизация под pgsql - она же и с MS и IBM, да и с собственным форматом общается, да и числа хранит чуть ли не в текстовом формате...

[onegreyonewhite]

Прям очень интересно как Postgres реагирует на alter в этом случае. Неужели только пересоздание таблицы может спасти ситуацию?

[MihaTeam]

Действительно интересно, но у меня есть подозрения, что добавляет колонки в конец, только если точечно переставлять в конец колонки. Но тут вопрос, что быстрее.

[c0ntr0ller]

А как будет выглядеть процесс оптимизации порядка столбцов при размерах таблицы в несколько Тб? Не завезли еще функцию перемещения столбцов, жаль...