Хранение пустых (NULL) значений в таблицах PostgreSQL

В статье рассматривается, сколько места занимают поля с пустыми значениями и стоит ли их использовать с точки зрения экономии места под хранение строк. В статье приведены расчёты и команды для самостоятельного повторения. Примеры полезны, чтобы понять, как в блоках хранятся строки с пустыми значениями. Для практического применения в конце статьи приведены результаты.

На схеме изображены три строки: первая строка зеленого цвета, вторая — оранжевого, третья — синего.

Если в строке хотя бы одно из полей пусто (имеет значение NULL), то в заголовке этой строки выделяется место под битовую карту. Минимальный размер, который физически занимает заголовок строки в таблице — 24 байт из них первые 21 байт используются. Заголовок выравнивается (aligning) по 8 байт, поэтому занимает 24 байта. Битовая карта располагается сразу после заголовка. Минимальный размер битовой карты — один байт, который сохранит биты для 8 столбцов. Если битовая карта занимает до 3 байт (21+3), то заголовок строки вписывается в 24 байта.

Во второй строке (на схеме оранжевого цвета) все поля пустые и показана битовая карта( t_bits). В битовой карте для одного поля выделяется один бит. Если в таблице тысяча столбцов, то в битовой карте будет тысяча бит даже, если NULL только в одном поле. Размер заголовка увеличится на 1000 бит и будет после этого «выровнен» до 8 байт, то есть размер заголовка в байтах будет увеличен так, чтобы размер делился на 8 без остатка.

Сколько столбцов может быть в таблице, чтобы размер заголовка строки не увеличился с 24 до 32 байт? 

 Создадим таблицы с числом столбцов 8, 9, 24, 25 и вставим в каждую таблицу по две строки. В первой строке все поля непустые, во второй строке хотя бы одно поле пустое (NULL):

Вставим две строки и посмотрим, сколько занимает заголовок каждой строки:

INSERT INTO t1 VALUES (1,2,3,4,5,6,7,8); INSERT INTO t1 VALUES (1,NULL,3,4,5,6,7,8); select lp, lp_off, lp_len, t_ctid, t_hoff, t_bits from heap_page_items(get_raw_page('t1', 'main',0));  lp | lp_off | lp_len | t_ctid | t_hoff |  t_bits   ----+--------+--------+--------+--------+----------   1 |   8136 |     56 | (0,1)  |     24 |    2 |   8080 |     52 | (0,2)  |     24 | 10111111 (2 rows)

У обеих строк размер заголовка (t_hoff, tuple header offset) одинаков: 24 байта. В t_bits  второй бит (1011111) нулевой, это означает, что во втором столбце NULL.

Вставим две строки во вторую таблицу, которая отличается от первой таблицы тем, что в ней 9 столбцов:

INSERT INTO t2 VALUES (1,2,3,4,5,6,7,8,9); INSERT INTO t2 VALUES (1,NULL,3,4,5,6,7,8,9); select lp, lp_off, lp_len, t_ctid, t_hoff, t_bits from heap_page_items(get_raw_page('t2', 'main',0));  lp | lp_off | lp_len | t_ctid | t_hoff |      t_bits       ----+--------+--------+--------+--------+------------------   1 |   8128 |     60 | (0,1)  |     24 |    2 |   8064 |     64 | (0,2)  |     32 | 1011111110000000 (2 rows)

Если в таблице девять столбцов, то у строки с пустыми полями размер заголовка 32 байта, а не 24. Если бы столбцов было 8, то была бы экономия 8 байт. Это значит, что при прочих равных, если в каком-то из столбцов возможно пустое значение, то лучше создать таблицу с 8 столбцами, а не 9. В t_bits  второй бит нулевой, это означает, что во втором столбце NULL. При этом с 10 по 16 бит тоже нули. Это означает, что полей в области данных нет, так как в таблице 9 столбцов. Размерность же битовой карты — это байты (8 бит), то есть битовая карта может хранить 8, 16, 24, 32.. бита.

Проверим, что для таблицы с 24 столбцами размер заголовка такой же, как и с 9 столбцами:

INSERT INTO t3 VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24); INSERT INTO t3 VALUES (1,NULL,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24);  select lp, lp_off, lp_len, t_ctid, t_hoff, t_bits from heap_page_items(get_raw_page('t3', 'main',0));  lp | lp_off | lp_len | t_ctid | t_hoff |          t_bits           ----+--------+--------+--------+--------+--------------------------   1 |   8072 |    120 | (0,1)  |     24 |    2 |   7944 |    124 | (0,2)  |     32 | 101111111111111111111111 (2 rows) 

Для 24 столбцов размер заголовка такой же, как и для 9 столбцов. В поле t_bits в конце нет нулей. Это означает, что если добавить ещё один столбец, то битовая карта увеличится на один байт. Проверим это:

INSERT INTO t4 VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25); INSERT INTO t4 VALUES (1,NULL,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25); select lp, lp_off, lp_len, t_ctid, t_hoff, t_bits from heap_page_items(get_raw_page('t4', 'main',0));  lp | lp_off | lp_len | t_ctid | t_hoff |              t_bits               ----+--------+--------+--------+--------+----------------------------------   1 |   8064 |    124 | (0,1)  |     24 |    2 |   7936 |    128 | (0,2)  |     32 | 10111111111111111111111110000000 (2 rows)

Битовая карта пустых значений (t_bits) увеличилась на байт — в конце добавились биты: 10000000.
При этом размер заголовка второй строки не увеличился и остался 32 байт.

Минимальная единица хранения битовой карты — один байт. Или другими словами битовая карта выравнивается по одному байту. Это означает, что если в таблице до 9 столбцов, то битовая карта занимает 1 байт (8 бит). Если в таблице 9-16 столбца, то битовая карта занимает 2 байта (16 бит). Если 17-24, то 3 байта. Начиная с 25 столбцов — 4 байта и так далее.

Сколько столбцов в таблице должно быть минимально, чтобы заголовок строки из-за битовой карты пустых значений увеличился с 32 байт до 40 байт?

Формула для расчёта: 8*8 (64 столбца это 8 байт в карте)+8 (битовая карта на 8 столбцов помещается в заголовке размером 24 байта)+1 (лишний столбец из-за которого размер заголовка строки увеличится на 8 байт)=73 столбца.

Несколько пустых полей компенсируют увеличение заголовка

Не нужно переоценивать увеличение размера заголовка строки. Дело в том, что хранение NULL не занимает ни байта в области данных, то есть высокоэффективно. Например, если в поле вместо NULL хранить ноль и это поле выравнивается по 8 байт, то использование NULL вместо любого другого значения сэкономит 8 байт. Это видно по столбцу lp_len: 52 меньше, чем 56 в таблице  t1. В других таблицах нужно сделать пустым ещё один столбец, чтобы размер строки стал меньше на 4 байта:

insert into t3 values (1,NULL,3,NULL,5,NULL,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24); select lp, lp_off, lp_len, t_ctid, t_hoff, t_bits from heap_page_items(get_raw_page('t3','main',0)); INSERT 0 1  lp | lp_off | lp_len | t_ctid | t_hoff |          t_bits           ----+--------+--------+--------+--------+--------------------------   1 |   8072 |    120 | (0,1)  |     24 |    2 |   7944 |    124 | (0,2)  |     32 | 101111111111111111111111   3 |   7824 |    116 | (0,3)  |     32 | 101010111111111111111111 (3 rows)

Строка с тремя пустыми полями имеет размер 116 байт, что меньше, чем 120 байт у строки где нет пустых значений. t_hoff входит в lp_len. Реальный размер первой и третьей строки (с учетом выравнивания всей строки, которая выравнивается по 8 байт) одинаков: 120 байт. Это легко проверить по столбцу lp_off: первая строка занимает 8192-8072=120 байт; вторая строка занимает 8072-7944=128 байт, третья строка занимает 7944-7824=120 байт.

lp_len выдает длинну строки без учета выравнивания всей строки по 8 байт и если об этом забыть, то можно ошибиться в расчетах. Итак, из-за выравнивания всей строки следующие строки занимают в блоке одинаковое место (120 байт):

(1,NULL,3,NULL,5,NULL,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)

Результаты

В статье рассмотрены пустые значения с точки зрения эффективности хранения. Пустые значения (NULL) в PostgreSQL экономят место под хранение, что можно учитывать, если в таблицах планируется хранить большое число строк. С учётом того, что PostgreSQL хранит старые версии строк в блоках таблиц, размер строк важен.
Если в таблице до 8 столбцов включительно, то размер заголовка строки 24 байта.
Если в таблице от 9 столбцов до 72 включительно, то размер заголовка строки, в случае если хоть в одном поле присутствует пустое значение (NULL),  увеличивается в размере и становится 32 байта.
В дальнейшем, заголовок строки будет увеличиваться на 8 байт для каждых 64 столбцов.
Если в строке несколько полей имеют пустые значения, это компенсирует увеличение размера заголовка строки из-за хранения в нем битовой карты пустых значений.