Таблица из шести столбцов и миллиона строк занимает 76,6 МБ, а с теми же данными в другом порядке столбцов — 60,2 МБ. Разница не в данных, а в служебных байтах: у каждой строки есть заголовок из 23 байт, а перед каждым значением ставятся пустые байты выравнивания. Страницу целиком мы разобрали в статье про раскладку страницы 8 КБ; здесь — одна строка внутри неё.
Из чего состоят 23 байта
Заголовок строки описан структурой HeapTupleHeaderData в src/include/access/htup_details.h. Вот её хвост, строки 161–175 дословно:
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a
* speculative insertion token) */
/* Fields below here must match MinimalTupleData! */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2
uint16 t_infomask2; /* number of attributes + various flags */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3
uint16 t_infomask; /* various flag bits, see below */
#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */Комментарий 23 bytes стоит сразу после t_hoff. Выше, в объединении t_choice (строки 155–159), лежат t_xmin, t_xmax и t_cid, по 4 байта каждое. Сумма: 4 + 4 + 4 + 6 (t_ctid) + 2 (t_infomask2) + 2 (t_infomask) + 1 (t_hoff) = 23. Следом идёт t_bits — битовая карта NULL, но только если в t_infomask взведён HEAP_HASNULL. Данные столбцов начинаются со смещения t_hoff, а оно всегда кратно 8 (на этой машине MAXALIGN равен 8): поэтому у строки без NULL между заголовком и данными остаётся один пустой байт.
Опыт 1: заголовок в pageinspect
Подними контейнер и создай таблицу с двумя строками, у второй note равен NULL:
docker run -d --name dba-heap-tuple-header -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-heap-tuple-header psql -U postgresSELECT version();
CREATE EXTENSION pageinspect;
CREATE TABLE t (id int4, note text);
INSERT INTO t VALUES (1,'abc'),(2,NULL);
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask2,
to_hex(t_infomask) AS infomask, t_hoff, t_bits, lp_len
FROM heap_page_items(get_raw_page('t',0)); lp | t_xmin | t_xmax | t_ctid | t_infomask2 | infomask | t_hoff | t_bits | lp_len
----+--------+--------+--------+-------------+----------+--------+----------+--------
1 | 755 | 0 | (0,1) | 2 | 802 | 24 | | 32
2 | 755 | 0 | (0,2) | 2 | 801 | 24 | 10000000 | 28
(2 rows)version() вернул PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu. Что видно в выводе:
t_infomask2 = 2. Младшие 11 бит (HEAP_NATTS_MASK, 0x07FF) хранят число столбцов в строке: два. Старшие биты этого поля — флаги, ниже мы увидим их взведёнными.
t_hoff = 24. Заголовок 23 байта плюс байт выравнивания у обеих строк. У второй строки в этом байте лежит битовая карта: t_bits = 10000000 — первый столбец не NULL (единица), второй NULL (ноль). Одного байта на 8 столбцов хватает, поэтому t_hoff не вырос.
infomask = 802 и 801. Расшифровка по определениям из заголовочного файла:
#define HEAP_HASNULL 0x0001 /* has null attribute(s) */
#define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */0x0002у первой строки: есть значение переменной длины (text).0x0001у второй строки: есть NULL, и потому естьt_bits.0x0800у обеих —HEAP_XMAX_INVALID(t_xmaxневалиден): строку никто не удалял, вt_xmaxнули.
lp_len совпадает с тем, что считает pg_column_size для строки целиком:
SELECT pg_column_size(ROW(1::int4,'abc'::text)) AS r1,
pg_column_size(ROW(2::int4,NULL::text)) AS r2,
pg_column_size(ROW()) AS r0,
pg_column_size(ROW(1::int4)) AS r_int4; r1 | r2 | r0 | r_int4
----+----+----+--------
32 | 28 | 24 | 28
(1 row)Пустая строка без столбцов весит 24: это заголовок с выравниванием. int4 прибавляет 4, строка abc — ещё 4 (один байт заголовка значения text и три байта текста). NULL не прибавляет ничего: ради него лишь выставлен бит в t_bits.
Хинт-биты: infomask меняется от чтения
Первая строка вставлена и закоммичена, но бита «t_xmin закоммитлен» в ней нет. Прочитай таблицу и посмотри заново:
SELECT * FROM t;
SELECT lp, to_hex(t_infomask) AS infomask, to_hex(t_infomask2) AS infomask2
FROM heap_page_items(get_raw_page('t',0)); lp | infomask | infomask2
----+----------+-----------
1 | 902 | 2
2 | 901 | 2
(2 rows)Появился 0x0100: HEAP_XMIN_COMMITTED.
#define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */
#define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED 0x0400 /* t_xmax committed */
#define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */Так читатель кэширует ответ на вопрос «закоммитлена ли транзакция 755»: узнав его один раз в журнале коммитов, он записывает результат в саму строку, и следующим читателям не надо туда ходить. Эти биты называют хинт-битами. В этом опыте бит поставил SELECT; после массовой загрузки его обычно ставит первый SELECT по таблице или VACUUM. Отсюда практическое следствие: первый SELECT после загрузки большой таблицы пишет в неё, и это нормально.
UPDATE: HOT_UPDATED и ctid
Обнови первую строку и посмотри все версии:
BEGIN; UPDATE t SET note='xyz' WHERE id=1; COMMIT;
SELECT * FROM t;
SELECT lp, t_xmin, t_xmax, t_ctid, to_hex(t_infomask2) AS infomask2,
to_hex(t_infomask) AS infomask
FROM heap_page_items(get_raw_page('t',0)); lp | t_xmin | t_xmax | t_ctid | infomask2 | infomask
----+--------+--------+--------+-----------+----------
1 | 755 | 756 | (0,3) | 4002 | 502
2 | 755 | 0 | (0,2) | 2 | 901
3 | 756 | 0 | (0,3) | 8002 | 2902
(3 rows)Здесь три строки, а не две: UPDATE не меняет версию на месте, а пишет новую (lp = 3) и помечает старую.
- У старой версии
t_xmax= 756 (транзакцияUPDATE) иt_ctid=(0,3): теперьctidуказывает не на себя, а на новую версию. infomask2=4002: младшее2— два столбца,0x4000—HEAP_HOT_UPDATED. Столбецnoteне входит ни в один индекс, поэтому обновление «горячее» (HOT): новая версия осталась на той же странице.- У новой версии
infomask2=8002:0x8000—HEAP_ONLY_TUPLE, на неё нет ссылок из индексов. infomask=502у старой:0x0400(HEAP_XMAX_COMMITTED) +0x0100+0x0002. У новой2902:0x2000(HEAP_UPDATED, это обновлённая версия) +0x0800+0x0100+0x0002.
#define HEAP_KEYS_UPDATED 0x2000 /* tuple was updated and key cols
* modified, or tuple deleted */
#define HEAP_HOT_UPDATED 0x4000 /* tuple was HOT-updated */
#define HEAP_ONLY_TUPLE 0x8000 /* this is heap-only tuple */Осторожнее с числами: 0x2000 и 0x4000 значат разное в t_infomask (HEAP_UPDATED, HEAP_MOVED_OFF) и в t_infomask2 (HEAP_KEYS_UPDATED, HEAP_HOT_UPDATED). Бит без названия поля прочитать нельзя. Как эти поля решают, видна ли версия строки транзакции, разобрано в статье про MVCC; как старые версии убирает очистка — в VACUUM и autovacuum.
NULL и битовая карта: 9-й столбец
Битовая карта занимает по биту на столбец и округляется до байта. Заголовок 23 байта + 1 байт карты = 24, и t_hoff не меняется до 8 столбцов. Девятый столбец ломает это:
CREATE TABLE n9 (a int, b int, c int, d int, e int, f int, g int, h int, i int);
INSERT INTO n9 VALUES (1,2,3,4,5,6,7,8,NULL);
INSERT INTO n9 VALUES (1,2,3,4,5,6,7,8,9);
SELECT lp, t_hoff, t_bits, lp_len, to_hex(t_infomask)
FROM heap_page_items(get_raw_page('n9',0)); lp | t_hoff | t_bits | lp_len | to_hex
----+--------+------------------+--------+--------
1 | 32 | 1111111100000000 | 64 | 801
2 | 24 | | 60 | 800
(2 rows)Строка с NULL в девятом столбце больше, чем строка со всеми значениями: 64 байта против 60. Данные сократились на 4 байта, но 23 + 2 байта карты = 25 округлились до t_hoff = 32. «NULL экономит место» верно, пока карта умещается в тот же кратный восьми блок заголовка.
Опыт 2: порядок столбцов меняет размер таблицы
Выравнивание значения задаёт тип. Посмотри на шесть типов:
SELECT typname, typlen, typalign FROM pg_type
WHERE typname IN ('bool','int2','int4','int8','timestamptz','text')
ORDER BY typlen DESC, typname; typname | typlen | typalign
-------------+--------+----------
int8 | 8 | d
timestamptz | 8 | d
int4 | 4 | i
int2 | 2 | s
bool | 1 | c
text | -1 | i
(6 rows)typalign: d — граница 8 байт, i — 4, s — 2, c — без выравнивания. Значение каждого столбца начинается на кратном своему выравниванию смещении от начала данных, пропущенные байты заполняются нулями. Создай две таблицы с одним набором столбцов и разным порядком: в bad столбцы идут «как придумали», в good — по убыванию выравнивания:
CREATE TABLE bad (a int4, b int8, c int2, d int8, e bool, f timestamptz);
CREATE TABLE good (b int8, d int8, f timestamptz, a int4, c int2, e bool);
INSERT INTO bad SELECT g, g, 1, g, true, now() FROM generate_series(1,1000000) g;
INSERT INTO good SELECT g, g, now(), g::int4, 1, true FROM generate_series(1,1000000) g;
VACUUM bad; VACUUM good;
SELECT 'bad' t, pg_relation_size('bad') rel, pg_relation_size('bad')/8192 pages,
pg_total_relation_size('bad') total
UNION ALL SELECT 'good', pg_relation_size('good'), pg_relation_size('good')/8192,
pg_total_relation_size('good'); t | rel | pages | total
------+----------+-------+----------
bad | 76562432 | 9346 | 76611584
good | 60235776 | 7353 | 60276736
(2 rows)SELECT round(100.0*(pg_relation_size('bad')-pg_relation_size('good'))/pg_relation_size('bad'),1) AS saved_pct_rel,
round(100.0*(pg_total_relation_size('bad')-pg_total_relation_size('good'))/pg_total_relation_size('bad'),1) AS saved_pct_total; saved_pct_rel | saved_pct_total
---------------+-----------------
21.3 | 21.3
(1 row)Тот же миллион строк, те же значения: на 21,3 % меньше. pg_total_relation_size больше pg_relation_size на 49 152 байта у bad и на 40 960 у good: индексов нет, это карта свободного места (fsm, 40 960 и 32 768 байт) и карта видимости (vm, по 8 192). Теперь посмотри на первую строку каждой таблицы:
SELECT 'bad' t, t_hoff, lp_len, t_data FROM heap_page_items(get_raw_page('bad',0)) WHERE lp=1
UNION ALL SELECT 'good', t_hoff, lp_len, t_data FROM heap_page_items(get_raw_page('good',0)) WHERE lp=1; t | t_hoff | lp_len | t_data
------+--------+--------+----------------------------------------------------------------------------------------------------
bad | 24 | 72 | \x0100000000000000010000000000000001000000000000000100000000000000010000000000000069f4833cd0010300
good | 24 | 55 | \x010000000000000001000000000000000b728d3cd001030001000000010001
(2 rows)Разбери t_data строки bad по смещениям от начала данных (смещение в строке — это 24 + эта величина):
a(int4) — 4 байта01000000, затем 4 нуля:b(int8) должен начинаться на границе 8.b—0100000000000000, 8 байт.c(int2) —0100, затем 6 нулей до границы 8 дляd.d— 8 байт;e(bool) —01, затем 7 нулей до границы 8 дляf.f(timestamptz) — последние 8 байт.
Полезных байт 31, строка 72 − 24 = 48 байт данных: 4 + 6 + 7 = 17 байт ушли на дыры. В good дыр нет: три int8 подряд, потом int4, int2, bool, всего 55 байт. Страница это подтверждает:
SELECT 'bad' t, count(*) FROM heap_page_items(get_raw_page('bad',0))
UNION ALL SELECT 'good', count(*) FROM heap_page_items(get_raw_page('good',0));
SELECT pg_column_size(ROW(1::int4,1::int8,1::int2,1::int8,true,now())) AS bad_row,
pg_column_size(ROW(1::int8,1::int8,now(),1::int4,1::int2,true)) AS good_row; t | count
------+-------
bad | 107
good | 136
(2 rows)
bad_row | good_row
---------+----------
72 | 55
(1 row)На страницу влезает 107 строк против 136. Почему именно столько: после заголовка страницы (24 байта) остаётся 8168 байт; строка занимает кратное 8 число байт и ещё 4 байта на указатель. bad: 72 + 4 = 76, и 8168 / 76 = 107,5. good: 55 округляется до 56, 56 + 4 = 60, и 8168 / 60 = 136,1. Без округления до 56 в страницу влезло бы 138 строк, а их 136.
Битовая карта заполняет тот самый свободный байт 23. Строка bad с NULL в f:
CREATE TABLE bad_null (LIKE bad);
INSERT INTO bad_null VALUES (1,1,1,1,true,NULL);
SELECT t_hoff, t_bits, lp_len, to_hex(t_infomask) AS infomask
FROM heap_page_items(get_raw_page('bad_null',0)); t_hoff | t_bits | lp_len | infomask
--------+----------+--------+----------
24 | 11111000 | 57 | 801
(1 row)Карта 11111000 села в байт, который у строки без NULL был пустым; t_hoff остался 24. Строка стала короче на столбец f и его дыру: 57 байт против 72.
Строка из 72 байт: 17 уходят на дыры выравнивания
Что из этого следует
Порядок столбцов — решение при CREATE TABLE. ALTER TABLE не умеет переставлять столбцы, поэтому пересборка таблицы с другим порядком — это новая таблица, перенос данных и переключение. Для широких таблиц с миллионами строк заложи порядок сразу.
Правило сортировки простое. Сначала столбцы с выравниванием d (int8, float8, timestamptz), затем i (int4, date), затем s (int2), затем c (bool, "char"). Типы переменной длины (text, numeric, jsonb, у всех i) ставь в конец: их размер меняется от строки к строке, и всё после них стояло бы на разном смещении.
Выигрыш зависит от таблицы. Мы получили 21,3 % на шести столбцах фиксированной ширины, где дыр было больше всего. В таблице из bigint и text дыр почти нет, и пересборка не окупится. Меряй на своей таблице: pg_column_size(t.*) по строкам и pg_relation_size, как выше, прежде чем что-то переделывать.
Заголовок не отключить. 24 байта на строку — цена MVCC. Для таблиц из двух-трёх узких столбцов заголовок весит больше данных: строка ROW(1::int4) занимает 28 байт, из них полезных 4.
Чек-лист
- Заголовок строки — 23 байта, в строке без NULL с выравниванием 24; карта NULL растёт по биту на столбец и сдвигает
t_hoffкратно 8. - Читай
t_infomaskиt_infomask2только вместе с названием поля:0x2000и0x4000значат в них разное. - Новую широкую таблицу проектируй по убыванию
typalign:d,i,s,c, переменная длина в конце. - Прежде чем пересобирать существующую таблицу, измерь
pg_column_size(t.*)и число строк на странице черезheap_page_items. - Если смотришь страницу через
pageinspect, помни: первыйSELECTилиVACUUMпосле вставки меняетinfomask(хинт-биты).
Контейнер удаляется командой docker rm -f dba-heap-tuple-header.