Глава 7. Выравнивание и порядок колонок · PostgreSQL

7.1 Колонки лежат не вплотную

Процессор читает 8-байтовое число быстрее, когда оно начинается с адреса, кратного 8. PostgreSQL соблюдает это правило выравнивания (alignment): каждый тип хочет начинаться с «круглого» адреса. bigint и timestamp - кратного 8, int - кратного 4, smallint - кратного 2.

Чтобы соблюсти правило, между колонками вставляются пустые байты-заполнители - padding. Они занимают место на диске и в кеше, но никаких данных не несут. Подробный разбор - в column-alignment.

7.2 Один набор, два размера

Покажем на эксперименте. Возьмём три колонки: два smallint (по 2 байта) и один bigint (8 байт). Разложим их двумя способами и измерим длину строки через lp_len (см. tuple-header):

sql

CREATE TABLE align_bad  (a smallint, b bigint, c smallint);

CREATE TABLE align_good (b bigint, a smallint, c smallint);

INSERT INTO align_bad  VALUES (1, 2, 3);

INSERT INTO align_good VALUES (2, 1, 3);

SELECT 'bad'  AS t, lp_len FROM heap_page_items(get_raw_page('align_bad', 0))

UNION ALL

SELECT 'good' AS t, lp_len FROM heap_page_items(get_raw_page('align_good', 0));

--   t   | lp_len

-- ------+--------

--  bad  |     42

--  good |     36

Одни и те же данные - и 6 байт разницы на строку, только из-за порядка колонок. Разберём, откуда они.

7.3 Где прячутся 6 байт

Заголовок кортежа занимает 24 байта, дальше идут данные.

Плохой порядок (smallint, bigint, smallint):

смещение:  0       2                8             16    18

данные:    [a: 2]  [6 байт padding] [b: 8 байт]   [c: 2]

bigint обязан начаться с адреса, кратного 8. После первого smallint мы на смещении 2, поэтому добивается 6 байт заполнителя до 8. Итого данных 18 байт, строка 24 + 18 = 42.

Хороший порядок (bigint, smallint, smallint):

смещение:  0            8       10    12

данные:    [b: 8 байт]  [a: 2]  [c: 2]

bigint уже на нуле - выровнен сразу. Два smallint ложатся следом без зазоров. Данных 12 байт, строка 24 + 12 = 36. Шесть байт заполнителя из плохого варианта здесь просто не возникли.

7.4 Правило: от широких к узким

Отсюда простая эвристика для проектирования таблиц: перечисляй колонки по убыванию выравнивания. Сначала 8-байтовые (bigint, timestamp, double precision), потом 4-байтовые (int, date), потом 2-байтовые (smallint), потом 1-байтовые (bool, "char") и переменной длины в конце.

Логика простая: если самые «требовательные» типы идут первыми, они попадают на круглые адреса естественно, и заполнители не нужны. Когда широкий тип стоит после узкого, перед ним почти всегда приходится добивать байты.

7.5 Узнать выравнивание типа

Выравнивание типа записано в системном каталоге pg_type, в колонке typalign:

sql

SELECT typname, typalign, typlen FROM pg_type

WHERE typname IN ('int2','int4','int8','bool','timestamptz');

-- typalign: c = 1 байт, s = 2, i = 4, d = 8

Буква typalign кодирует требование: c (char) - 1 байт, s (short) - 2, i (int) - 4, d (double) - 8. Зная её, можно заранее прикинуть раскладку строки и где появятся заполнители, не вставляя ни одной строки.

7.6 Подводный камень: когда это вообще важно

Не кидайся переставлять колонки во всех таблицах. На таблице в тысячу строк выигрыш в 6 байт незаметен - его съедает любая другая мелочь. Микрооптимизация порядка колонок окупается только на крупных, активно читаемых таблицах: там лишние 8 байт на строку превращаются в гигабайты диска и кеша, которые греются впустую.

И помни про цену перестановки: поменять порядок колонок в живой таблице нельзя «на месте», нужно её пересоздать. Поэтому порядок продумывают на этапе проектирования больших таблиц, а не правят задним числом ради пары процентов. Для обычных таблиц читаемость порядка колонок важнее экономии байтов.

Уроки в sandbox

lab-7.1. Padding своими руками

Создай две таблицы с одинаковыми колонками в разном порядке и измерь разницу. Перед запросом предскажи lp_len каждой по раскладке байтов: где появится заполнитель, а где нет.

Создай align_bad(a smallint, b bigint, c smallint) и align_good(b bigint, a smallint, c smallint).
Вставь по одной строке с любыми числами в каждую таблицу.
Предскажи длину строки align_bad: 24 (заголовок) + данные с заполнителем. Проверь: SELECT lp_len FROM heap_page_items(get_raw_page('align_bad', 0));.
Предскажи длину строки align_good (заполнителя быть не должно) и проверь так же.
Объясни разницу: где в плохом порядке появились 6 байт заполнителя перед bigint.
Загляни в pg_type: SELECT typname, typalign FROM pg_type WHERE typname IN ('int2','int8'); - сопоставь typalign с раскладкой.

sandbox с автопроверкой - открыть в песочнице

Резюме

Колонки выравниваются: bigint хочет адрес кратный 8, int - кратный 4, smallint - кратный 2.
Между колонками появляются байты-заполнители (padding), которые занимают место, но не несут данных.
Порядок колонок меняет размер строки при тех же данных: (smallint,bigint,smallint) = 42 байта, (bigint,smallint,smallint) = 36.
Эвристика: перечислять колонки по убыванию выравнивания, от широких к узким.
Выравнивание типа видно в pg_type.typalign: c=1, s=2, i=4, d=8 байт.
Перестановка колонок - микрооптимизация: окупается только на крупных таблицах и требует пересоздания.

Контрольные вопросы

Почему две таблицы с одинаковыми колонками в разном порядке занимают разное место?
Показать ответ
Из-за выравнивания и заполнителей. Каждый тип хочет начинаться с кратного своему размеру адреса: bigint - с кратного 8. Если перед bigint стоит узкий тип, оставляющий смещение «некруглым», PostgreSQL добивает пустые байты-заполнители до нужной границы. В порядке (smallint, bigint, smallint) перед bigint возникает 6 байт заполнителя, в порядке (bigint, smallint, smallint) - нет, потому что bigint попадает на смещение 0 сразу.
Как измерить реальный размер строки на диске?
Показать ответ
Через lp_len в выводе heap_page_items для нужной страницы: это фактическая длина кортежа в байтах, включая заголовок (24 байта) и данные с учётом заполнителей. Можно также воспользоваться pg_column_size для набора значений. Логическая длина значений и их реальный размер на диске - не одно и то же именно из-за выравнивания.
Какое правило порядка колонок уменьшает заполнители?
Показать ответ
Перечислять колонки по убыванию требования к выравниванию: сначала 8-байтовые (bigint, timestamp, double precision), потом 4-байтовые (int, date), затем 2-байтовые (smallint), потом 1-байтовые (bool) и переменной длины в конце. Тогда требовательные типы попадают на круглые адреса естественно, и добивать байты перед ними не приходится.
Где посмотреть выравнивание конкретного типа?
Показать ответ
В системном каталоге pg_type, колонка typalign. Её буква кодирует требование: c (char) - 1 байт, s (short) - 2, i (int) - 4, d (double) - 8. Зная typalign всех колонок, можно заранее прикинуть раскладку строки и где появятся заполнители, не вставляя данных.
Стоит ли всегда переставлять колонки ради экономии?
Показать ответ
Нет. На небольших таблицах выигрыш в несколько байт на строку незаметен, а читаемость осмысленного порядка колонок важнее. Оптимизация порядка окупается только на крупных, активно читаемых таблицах, где лишние байты на строку превращаются в гигабайты диска и кеша. К тому же поменять порядок в живой таблице нельзя на месте - её надо пересоздавать, поэтому порядок продумывают при проектировании больших таблиц.

7.1 Колонки лежат не вплотную

7.2 Один набор, два размера

sql

CREATE TABLE align_bad  (a smallint, b bigint, c smallint);

CREATE TABLE align_good (b bigint, a smallint, c smallint);

INSERT INTO align_bad  VALUES (1, 2, 3);

INSERT INTO align_good VALUES (2, 1, 3);

SELECT 'bad'  AS t, lp_len FROM heap_page_items(get_raw_page('align_bad', 0))

UNION ALL

SELECT 'good' AS t, lp_len FROM heap_page_items(get_raw_page('align_good', 0));

--   t   | lp_len

-- ------+--------

--  bad  |     42

--  good |     36

Одни и те же данные - и 6 байт разницы на строку, только из-за порядка колонок. Разберём, откуда они.

7.3 Где прячутся 6 байт

Заголовок кортежа занимает 24 байта, дальше идут данные.

Плохой порядок (smallint, bigint, smallint):

смещение:  0       2                8             16    18

данные:    [a: 2]  [6 байт padding] [b: 8 байт]   [c: 2]

Хороший порядок (bigint, smallint, smallint):

смещение:  0            8       10    12

данные:    [b: 8 байт]  [a: 2]  [c: 2]

7.4 Правило: от широких к узким

7.5 Узнать выравнивание типа

Выравнивание типа записано в системном каталоге pg_type, в колонке typalign:

sql

SELECT typname, typalign, typlen FROM pg_type

WHERE typname IN ('int2','int4','int8','bool','timestamptz');

-- typalign: c = 1 байт, s = 2, i = 4, d = 8

7.6 Подводный камень: когда это вообще важно

Уроки в sandbox

lab-7.1. Padding своими руками

Создай align_bad(a smallint, b bigint, c smallint) и align_good(b bigint, a smallint, c smallint).
Вставь по одной строке с любыми числами в каждую таблицу.
Предскажи длину строки align_bad: 24 (заголовок) + данные с заполнителем. Проверь: SELECT lp_len FROM heap_page_items(get_raw_page('align_bad', 0));.
Предскажи длину строки align_good (заполнителя быть не должно) и проверь так же.
Объясни разницу: где в плохом порядке появились 6 байт заполнителя перед bigint.
Загляни в pg_type: SELECT typname, typalign FROM pg_type WHERE typname IN ('int2','int8'); - сопоставь typalign с раскладкой.

sandbox с автопроверкой - открыть в песочнице

Резюме

Колонки выравниваются: bigint хочет адрес кратный 8, int - кратный 4, smallint - кратный 2.
Между колонками появляются байты-заполнители (padding), которые занимают место, но не несут данных.
Порядок колонок меняет размер строки при тех же данных: (smallint,bigint,smallint) = 42 байта, (bigint,smallint,smallint) = 36.
Эвристика: перечислять колонки по убыванию выравнивания, от широких к узким.
Выравнивание типа видно в pg_type.typalign: c=1, s=2, i=4, d=8 байт.
Перестановка колонок - микрооптимизация: окупается только на крупных таблицах и требует пересоздания.

Контрольные вопросы

Почему две таблицы с одинаковыми колонками в разном порядке занимают разное место?
Показать ответ
Из-за выравнивания и заполнителей. Каждый тип хочет начинаться с кратного своему размеру адреса: bigint - с кратного 8. Если перед bigint стоит узкий тип, оставляющий смещение «некруглым», PostgreSQL добивает пустые байты-заполнители до нужной границы. В порядке (smallint, bigint, smallint) перед bigint возникает 6 байт заполнителя, в порядке (bigint, smallint, smallint) - нет, потому что bigint попадает на смещение 0 сразу.
Как измерить реальный размер строки на диске?
Показать ответ
Через lp_len в выводе heap_page_items для нужной страницы: это фактическая длина кортежа в байтах, включая заголовок (24 байта) и данные с учётом заполнителей. Можно также воспользоваться pg_column_size для набора значений. Логическая длина значений и их реальный размер на диске - не одно и то же именно из-за выравнивания.
Какое правило порядка колонок уменьшает заполнители?
Показать ответ
Перечислять колонки по убыванию требования к выравниванию: сначала 8-байтовые (bigint, timestamp, double precision), потом 4-байтовые (int, date), затем 2-байтовые (smallint), потом 1-байтовые (bool) и переменной длины в конце. Тогда требовательные типы попадают на круглые адреса естественно, и добивать байты перед ними не приходится.
Где посмотреть выравнивание конкретного типа?
Показать ответ
В системном каталоге pg_type, колонка typalign. Её буква кодирует требование: c (char) - 1 байт, s (short) - 2, i (int) - 4, d (double) - 8. Зная typalign всех колонок, можно заранее прикинуть раскладку строки и где появятся заполнители, не вставляя данных.
Стоит ли всегда переставлять колонки ради экономии?
Показать ответ
Нет. На небольших таблицах выигрыш в несколько байт на строку незаметен, а читаемость осмысленного порядка колонок важнее. Оптимизация порядка окупается только на крупных, активно читаемых таблицах, где лишние байты на строку превращаются в гигабайты диска и кеша. К тому же поменять порядок в живой таблице нельзя на месте - её надо пересоздавать, поэтому порядок продумывают при проектировании больших таблиц.