Выравнивание и порядок колонок

Колонки в строке хранятся не вплотную. Процессор быстрее читает 8-байтовое число, когда оно начинается с адреса, кратного 8. PostgreSQL соблюдает это правило выравнивания (alignment) и вставляет между колонками байты-заполнители - padding. Заполнители места стоят, но пользы не несут.

Из-за этого две таблицы с одними и теми же колонками, но в разном порядке, занимают на диске разное место.

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

Возьмём три колонки: два smallint (по 2 байта, выравнивание 2) и один bigint (8 байт, выравнивание 8). Разложим их двумя способами.

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 байт разницы на строку. Откуда они берутся.

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

Заголовок кортежа - 24 байта (см. tuple-header). Дальше идут данные.

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

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

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

bigint обязан начаться с адреса, кратного 8. После первого smallint мы на смещении 2, поэтому PostgreSQL добивает 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.

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

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

Узнать выравнивание типа можно из каталога:

SELECT typname, typalign FROM pg_type

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

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

Стоит ли переставлять колонки

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