Если строка не помещается в блок (страницу), то в PostgreSQL применяется техника выноса полей в отдельную таблицу, называемую TOAST-таблица. Техника выноса и хранения называется TOAST (The Oversized-Attribute Storage Technique, техника хранения атрибутов большого размера). В статье достаточно детально рассматривается алгоритм работы TOAST. Знание алгорима и его граничные значения полезно, чтобы понимать каким образом хранятся данные в таблицах.

Не все встроенные типы данных поддерживают технику TOAST. Не поддерживают типы данных фиксированной длины, так как их длина небольшая и для любых значений одинакова ("фиксирована"), например, 1,2,4,8 байт. В TOAST могут выноситься поля переменной длины.

Размер типа, поддерживающего TOAST ограничен 1 Гигабайт. Это ограничение следует из того, что под длину в начале поля в блоке отводится 30 бит (2^30=1Гб) из 1 или 4 байт (32 бита). Два бита в этих байтах используются для обозначения: 00 - значение короткое, не TOAST, оставшиеся биты задают длину поля вместе с этим байтом; 01 - длина поля хранится в одном байте, оставшиеся биты задают длину поля в байтах и эти 6 бит могут хранить длину от 1 до 126 байт (2^6=64, но это для диапазона от нуля); 10 - значение сжато, оставшиеся биты задают длину поля в сжатом виде. Значения с одним байтом заголовка поля не выравниваются. Значения четырьмя байтами заголовка поля выравниваются по границе pg_type.typealign.

Вынесенные в TOAST поля делятся на части - "чанки" (после сжатия, если оно применялось) размером 1996 байт (значение задано константой TOAST_MAX_CHUNK_SIZE), которые располагаются в строках TOAST-таблицы размером 2032 байта (значение задано константой TOAST_TUPLE_THRESHOLD). Значения выбраны так, чтобы в блок таблицы TOAST поместилось четыре строки. Так как размер поля таблицы не кратен 1996 байт, то последний чанк поля может быть меньшего размера.

Значение TOAST_MAX_CHUNK_SIZE хранится в управляющем файле кластера, его можно посмотреть утилитой командной строки pg_controldata.

В таблице TOAST есть три столбца: chunk_id (тип OID, уникальный для поля вынесенного в TOAST размер 4 байта), chunk_seq (порядковый номер чанка, размер 4 байта), chunk_data (данные поля, тип bytea, размер сырых данных плюс 1 или 4 байта на хранение размера). Для быстрого доступа к чанкам на TOAST-таблицу создается составной уникальный индекс по chunk_id и chunk_seq. В блоке таблицы остаётся указатель на первый чанк поля и другие данные. Общий размер остающейся в таблице части поля всегда 18 байт.

В 32-разрядном PostgreSQL размер чанка на 4 байта больше: 2000 байт.

Поля переменной длины

Строка (запись) таблицы должна поместиться в один блок размером 8Кб и не может находиться в нескольких блоках файлов таблицы. Однако строки могут иметь размер больше 8Кб. Для их хранения применяется TOAST.

Индексная запись индекса btree не может превышать примерно треть блока (после сжатия проиндексированных столбцов, если оно применялось в таблице).

TOAST поддерживают типы данных varlena (pg_type.typlen=-1). Поля фиксированной длины не могут храниться вне блока таблицы, так как для этих типов данных не написан код, реализующий хранение вне блока таблицы (в TOAST-таблице). При этом строка должна поместиться в один блок и фактическое число столбцов в таблице будет меньше, чем лимит в 1600 столбцов (MaxHeapAttributeNumber в htup_details.h).

Чтобы поддерживать TOAST, в поле типа varlena первый байт или первые 4 байта всегда (даже если размер поля небольшой и не вытеснен в TOAST) содержат общую длину поля в байтах (включая эти 4 байта). Причем, эти байты могут (но не всегда) быть сжаты вместе с данными, то есть храниться в сжатом виде. Один байт используется, если длина поля не превышает 126 байт. Поэтому, при хранении данных поля размером до 127 байт "экономится" три байта на каждой версии строки, а также отсутствует выравнивание, на чем можно сэкономить до 3 (typealign='i') или до 7 байт (typealign='d').

Другими словами, проектировщику схем хранения лучше задать char(126) и меньше, чем char(127) и больше.

Поля varlena с одним байтом длины не выравниваются, а поля с 4 байтами длины выравниваются до pg_type.typealign. Для большинства типов переменной длины выравнивание до 4 байт (pg_type.typalign=i). Отсутствие выравнивания даёт выигрыш в объёме хранения, что ощутимо для коротких значений. Но всегда нужно помнить о выравнивании всей строки до 8 байт, которое выполняется всегда.

Сжатие поддерживается только для типов данных переменной длины. Сжатие производится только, если режим хранения столбца установлен в MAIN или EXTENDED. Если поле хранится в TOAST и команда UPDATE не затрагивает это поле, то поле не будет специально сжиматься-разжиматься.

Для большинства типов переменной длины по умолчанию используется режим EXTENDED, кроме типов:

select distinct typname, typalign, typstorage, typcategory, typlen
 from pg_type
 where typtype='b' and typlen<0 and typstorage<>'x'
 order by typname;
  typname   | typalign | typstorage | typcategory | typlen
------------+----------+------------+-------------+--------
 cidr       | i        | m          | I           |     -1
 gtsvector  | i        | p          | U           |     -1
 inet       | i        | m          | I           |     -1
 int2vector | i        | p          | A           |     -1
 numeric    | i        | m          | N           |     -1
 oidvector  | i        | p          | A           |     -1
 tsquery    | i        | p          | U           |     -1
(7 rows)

Для каждого столбца помимо режима можно еще установить алгоритм сжатия (в команде CREATE или ALTER TABLE). Если не устанавливать, то используется алгоритм из параметра default_toast_compression, который по умолчанию установлен в pglz.

Режим (стратегию) хранения можно установить командой ALTER TABLE имя ALTER COLUMN имя SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }.

EXTERNAL похож на EXTENDED, только без сжатия и по умолчанию не установлено на стандартных типах. Если алгоритм pglz не может сжать первый килобайт данных, он прекращает попытку сжатия.

Вытеснение полей в TOAST

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

У одной таблицы может быть только одна связанная с ней таблица TOAST и один TOAST-индекс (уникальный btree индекс по столбцам chunk_id и chunk_seq). OID TOAST-таблицы хранится в поле pg_class.reltoastrelid.

При доступе к каждому вытесненному полю дополнительно читается 2-3 блока TOAST-индекса, что снижает производительность даже, если блоки в буферном кэше. Основное замедление на получение блокировки для чтения каждого лишнего блока. Любые разделяемые ресурсы (то, что не в локальной памяти процесса) требуют получения блокировки даже для чтения ресурса.

Поля после сжатия (если оно есть) делятся на части (chunk) по 1996 байт:

pg_controldata | grep TOAST
Maximum size of a TOAST chunk:        1996

В PostgreSQL cтрока рассматривается на предмет помещения части ее полей в TOAST, если размер строки больше 2032 байт. Поля будут сжиматься и рассматриваться на предмет хранения в TOAST пока строка не поместится в 2032 байта или toast_tuple_target байт, если значение было установлено командой:

ALTER TABLE t SET (toast_tuple_target = 2032);

Оставшаяся часть строки в любом случае должна поместиться в один блок (8Кб).

Для PostgreSQL из AstralInux 1.8:

pg_controldata | grep TOAST
Maximum size of a TOAST chunk: 1988

поэтому будут выноситься поля длиннее, чем 1988+8=1996 байт, а не 2004. При этом поле длиной 1997 байт так же породит 2 чанка, второй чанк размером 9 байт, первый размером 1988 байт.

В 32-битном PostgreSQL 9.6 - 2009 байт (а максимальный размер чанка 2000).

Алгоритм вытеснения полей в TOAST

При вставке строки в таблицу она полностью размещается в памяти серверного процесса в строковом буфере размера 1Гб (или 2Гб для сессий, у которых установлен параметр конфигурации enable_large_allocations=on, отсутствующий в ванильном PostgreSQL, но имеющийся в форках).

Алгоритм вытеснения в четыре прохода:

1) По очереди выбираются поля EXTENDED и EXTERNAL в порядке от наибольшего размера до меньшего. После обработки каждого поля проверяется размер строки и если размер меньше или равен toast_tuple_target (по умолчанию 2032 байт), то вытеснение останавливается и строка сохраняется в блоке таблицы.

Берется поле EXTENDED или EXTERNAL. EXTENDED сжимается. Если размер строки с полем в сжатом виде превышает 2032, поле вытесняется в TOAST. Поле EXTERNAL вытесняется не сжимаясь.

2) Если размер строки всё ещё превышает 2032, во второй проход вытесняются оставшиеся уже сжатые EXTENDED и EXTERNAL по очереди, пока размер строки не станет меньше 2032.

3) Если размер строки не стал меньше 2032, по очереди в порядке размера сжимаются поля MAIN. После сжатия каждого поля проверяется размер строки.

4) Если размер строки не стал меньше 2032, по очереди вытесняются сжатые на 3 проходе MAIN.

5) Если размер строки не помещается в блок, выдаётся ошибка:

row is too big: size ..., maximum size ...

При обновлении строки обработка выполняется по затрагиваемым командой полям в пределах строкового буфера. Поля не затрагиваемые командой, представлены в буфере заголовком 18 байт.

TOAST chunk

Поле вытесняется в TOAST, если размер строки больше, чем 2032 байта, а резаться поле будет на части по 1996 байт. Из-за этого для поля больше 1996 байт появися чанк небольшого размера, который будет вставлен серверным процессом в блок с чанком большого размера. Например, в таблицу вставить 4 строки:

drop table if exists t;
create table t (c text);
alter table t alter column c set storage external;
insert into t VALUES (repeat('a',2005));
insert into t VALUES (repeat('a',2005));
insert into t VALUES (repeat('a',2005));
insert into t VALUES (repeat('a',2005));

в блок TOAST поместится 3 длинных чанка:

SELECT lp,lp_off,lp_len,t_ctid,t_hoff
 FROM heap_page_items(get_raw_page(
   (SELECT reltoastrelid::regclass::text
     FROM pg_class WHERE relname='t'),'main',0));
 lp | lp_off | lp_len | t_ctid | t_hoff
----+--------+--------+--------+-------
  1 |   6152 |   2032 | (0,1)  |     24
  2 |   6104 |     45 | (0,2)  |     24
  3 |   4072 |   2032 | (0,3)  |     24
  4 |   4024 |     45 | (0,4)  |     24
  5 |   1992 |   2032 | (0,5)  |     24
  6 |   1944 |     45 | (0,6)  |     24

Полный размер строки с длинным чанком 2032 байт (6104-4072).

select lower, upper, special, pagesize
 from page_header(get_raw_page(
  (SELECT reltoastrelid::regclass::text
    FROM pg_class WHERE relname='t'),'main',0));
lower| upper | special | pagesize
-----+-------+---------+---------
  48 |  1944 |    8184 |     8192

Пример как рассчитывать место в блоке для 4 строк размера 2032 байт (с 4 чанками):

24 (заголовок)+ 4*4 (заголовок) + 2032*4 + 8 (pagesize-special)=8176. Не используется 16 байт, но они бы и не могли использоваться, так строки выравниваются по 8 байт, а их 4.

Ограничения TOAST

В PostgreSQL служебной области special в конце блоков таблиц нет.

В 32-разрядном PostgreSQL:

Maximum size of a TOAST chunk: 2000

При использовании EXTENDED скорее всего поле будет сжато и маленького чанка не будет.

Каждое поле хранится в TOAST таблице в виде набора строк (chunk) хранится в виде одной строки в TOAST-таблице.

В поле основной таблицы хранится указатель на первый chunk размером 18 байт (независимо от размера поля). В этих 18 байтах хранится структура varatt_external, описанная в varatt.h:

первый байт имеет значение 0x01, это признак того, что поле вынесено в TOAST;

второй байт - длина этой записи (значение 0x12 = 18 байт);
4 байта длина поля с заголовком поля до сжатия;
4 байта длина того, что вынесено в TOAST;
4 байта - указатель на первый чанк в TOAST (столбец chunk_id таблицы TOAST);
4 байта - oid toast-таблицы (pg_class.reltoastrelid)

остаток поля в блоке таблицы, после выноса поля в TOAST

В столбце chunk_id (тип oid 4 байта) может быть 4млрд. (2 в степени 32) значений. Это значит, что в одной таблице в TOAST может быть вытеснено только 4млрд. полей (даже не строк). Это существенно ограничивает количество строк в исходной таблице и, вероятно, желателен мониторинг. Обойти ограничение можно секционированием.

Режим MAIN применяется для хранения внутри блока в сжатом виде, EXTERNAL - для хранения в TOAST в несжатом виде, EXTENDED для хранения в TOAST в сжатом виде. Если значения плохо сжимаются или планируется обрабатывать значения полей (например, текстовые поля функциями substr, upper), то эффективным будет использование режима EXTERNAL. Для типов фиксированной ширины установлен режим PLAIN, который поменять командой ALTER TABLE нельзя, будет выдана ошибка "ERROR: column data type тип can only have storage PLAIN".

Выравнивание строк с вытесненными в TOAST полями

В блоке таблицы для поля, вынесенного в TOAST-таблицу хранится указатель размером всегда 18 байт.

Про выравнивание отдельных полей известно, но о выравнивании всей строки часто забывают. Выравнивается строка с заголовком или область данных? И то и другое. Заголовок строки всегда выравнивается по 8 байт и может иметь размер 24, 32, 40.. байт. Если сказать что выравнивается область данных, то автоматически будет выровнена область данных с заголовком (вся строка). Если сказать, что выравнивается вся строка и заголовок, то из этого автоматически следует, что будет выровнена область данных. Все они выравниваются по 8 байт на 64-разрядных операционных системах.

Параметры toast_tuple_target и default_toast_compression

На вытеснение влияют два макроса, установленные в исходном коде (heaptoast.h):

TOAST_TUPLE_THRESHOLD и TOAST_TUPLE_TARGET, которые имеют одинаковые значения. Если размер строки больше TOAST_TUPLE_THRESHOLD, то начинается сжатие и/или вытеснение полей строки.

Поля будут сжиматься и рассматриваться на предмет хранения в TOAST, пока оставшаяся часть строки (полная: с заголовком строки) не поместится в TOAST_TUPLE_TARGET. Значение можно переопределить на уровне таблицы:

ALTER TABLE t SET (toast_tuple_target = 2032);

TOAST_TUPLE_THRESHOLD не переопределяется.

Также есть параметр, устанавливающий алгоритм сжатия pglz или lz4:

default_toast_compression

Константы определены в исходном коде:

#define MaximumBytesPerTuple(tuplesPerPage) MAXALIGN_DOWN((BLCKSZ - MAXALIGN(SizeOfPageHeaderData + (tuplesPerPage) * sizeof(ItemIdData)))/(tuplesPerPage))
#define TOAST_TUPLES_PER_PAGE 4
#define TOAST_TUPLE_THRESHOLD MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)
#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD

Параметры заголовка блока:

ItemIdData = 4 байта

SizeOfPageHeaderData = 24 байта

Если подставить значения, то получится:

TOAST_TUPLE_TARGET=TOAST_TUPLE_THRESHOLD=MAXALIGN_DOWN((BLCKSZ - MAXALIGN(24 + (4) sizeof(4)))/(4))=MAXALIGN_DOWN((BLCKSZ - MAXALIGN(24 + 44))/4)=MAXALIGN_DOWN((8192 - MAXALIGN(40))/4)=MAXALIGN_DOWN((8192 - 40)/4)=MAXALIGN_DOWN(2038)=2032.

TOAST_TUPLE_TARGET также определяет максимальный размер строк TOAST таблиц. Заголовок строки обычной и TOAST таблицы 24 байта. Размер области данных строки TOAST-таблицы 2032-24=2008 байт. В строке три поля: oid (4 байта), int4 (4 байта), bytea. В bytea первый байт в начале поля переменной ширины хранит длину поля длиной до 127 байт, первые 4 байта в начале поля переменной ширины хранят длину поля для bytea длиннее 126 байт. Выравнивание по 4 байта. 2008-4-4-4=1996.

Выравнивание строк с вытесненными в TOAST полями

В блоке таблицы для поля, вынесенного в TOAST-таблицу хранится указатель размером всегда 18 байт.
Про выравнивание отдельных полей известно, но о выравнивании всей строки часто забывают. Выравнивается строка с заголовком или область данных? И то и другое. Заголовок строки всегда выравнивается по 8 байт и может иметь размер 24, 32, 40.. байт. Если сказать что выравнивается область данных, то автоматически будет выровнена область данных с заголовком (вся строка). Если сказать, что выравнивается вся строка и заголовок, то из этого автоматически следует, что будет выровнена область данных. Все они выравниваются по 8 байт для 64-битных операционных систем (x86-64 и ARM64).

Если создать две таблицы:

create table t3 (c1 text); 
create table t4 (c1 serial , c2 smallint, c3 text);

вставить 200 строк с полем большой длинны, так чтобы значение длинного поля было вытеснено в TOAST:

DO $$ BEGIN FOR i IN 1 .. 200 LOOP
insert into t3 VALUES (repeat('a',1000000));
insert into t4 VALUES (default, 1, repeat('a',1000000));
END LOOP; END; $$ LANGUAGE plpgsql;

то в блок обоих таблиц помещается одинаковое число строк:

select count(*) from heap_page_items(get_raw_page('t3','main',0)) where (t_ctid::text::point)[0]=0 union all select count(*) from heap_page_items(get_raw_page('t4','main',0)) where (t_ctid::text::point)[0]=0;
   156
   156

Во второй таблице можно хранить два столбца в том же месте, которое в первой таблице не могло использоваться (6 байт) из-за padding всей строки.

Размер файлов таблиц также одинаков:

select pg_total_relation_size('t3'), pg_total_relation_size('t4');
          5070848              |          5070848

Если заменить serial и smallint на bigserial, то размер будет разный (156 и 135 строк в каждом блоке). bigserial при выносе в TOAST поля каждой строки бесполезен, так как в таблицах сможет сохраниться не больше 4млрд (2^32) строк с длинным полем.

Был рассмотрен алгоритм работы TOAST, что позволяет лучше понять, как хранятся строки большого размера в PostgreSQL.

Техника TOAST (The Oversized-Attribute Storage Technique) в PostgreSQL