Дисклеймер

На скорость написания этого текста повлияли такие непреодолимые обстоятельства как: лето, шашлычное настроение, солнце и лень обилие работы. Возможно, за несколько месяцев эту информацию в разных вариациях уже публиковали, но я честно не видел.

Основная часть

В этом тексте хочется подробнее рассмотреть хранение данных в PostgreSQL на физическом уровне.

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

Цитаты из обсуждения O_DIRECT между кучей умных дядек (что это такое гуглите сами).
(https://yarchive.net/comp/linux/o_direct.html)

Я загрузил все данные в базу данных и теперь планирую найти физическое местоположение этого шедевра и параллельно понять, что да как лежит в этом вашем PostgreSQL.

CREATE TABLE public.test_table (
	id int4 NULL,
	author varchar NULL,
	phrase varchar NULL
);

INSERT INTO test_table
VALUES 
(1
, 'Linus Torvalds'
, 'The thing that has always disturbed
me about O_DIRECT is that the whote interface is just stupid,
and was probabty designed by а deranged monkey on some serious mind—controlling substances'),
(2
, 'Linus Torvalds'
, 'Sadly, database реор1е don''t seem to have аnу understanding of good taste,
and various 0S реорlе end uр usually just saying “Yes, Мr Oracle“,
I''ll open up any orifice I have for уоur pleasure'); -- наши записи

INSERT INTO test_table 
SELECT ROW_NUMBER () OVER()+2
, gen_random_uuid()
FROM pg_catalog.generate_series(0,1500) --немного синтетики

Начнем с общего обзора базы данных и постепенно перейдем к уровню отдельных строк.
Сначала необходимо найти файлы данных, используемые кластером базы данных, обычно они хранятся в каталоге данных кластера, который по классике называют PGDATA /var/lib/pgsql/data на Linux,
\Program Files\PostgreSQL\[version]\data на Windows).

В этом каталоге лежит куча подкаталогов и несколько файлов конфигурации, относящихся к кластеру (https://postgrespro.ru/docs/postgresql/15/storage-file-layout). Мы не будем разбирать все подкаталоги, ибо их довольно много, а остановимся на тех, которые помогут найти строку данных.

pg_tblspc

Прежде всего, все данные находятся на одном из физических носителей.

Табличные пространства определяют физическое расположение данных. Фактически табличное пространство — это каталог файловой системы. Например, табличные пространства можно использовать, чтобы разместить архивные данные на медленных носителях, а данные, с которыми идет активная работа, — на быстрых.

https://postgrespro.ru/education/books/internals - PostgreSQL16 Internals
всем советую

При инициализации кластера создается два пространства:

• pg_default, который "лежит" в PGDATA/base и используется как дефолтное пространство, если не указать иное

• pg_global, который "лежит" в PGDATA/global и хранит общие объекты системного каталога

В подкаталоге pg_tblspc лежат символьные ссылки на каталоги с пользовательскими табличными пространствами. Перейдя по ним, найдем каталоги типа PGDATA/base, в которых также лежат подкаталоги с oid баз данных. Если база данных имеет объекты, расположенные на разных физических носителях, то для каждого пространства создается отдельный подкаталог базы данных (с одинаковым oid), и в каждый из этих каталогов добавляются файлы объектов, лежащих в этом пространстве.

Скрытый текст

абличные SELECT * FROM pg_catalog.pg_tablespace;

base

Любая БД представляет собой каталог, который хранится в PGDATA/base (название чаще всего соответствует oid объекта). В каждом из этих каталогов хранятся файлы, которые предназначены для хранения и доступа к данным. Для каждого объекта БД есть один или несколько файлов. Например, для обычной таблицы без индексов существует три файла (четыре, если таблица UNLOGGED). Каждый из файлов соответствует одному из слоев.

Скрытый текст

SELECT relfilenode , oid, relname
FROM pg_class 
WHERE relname = 'test_table';

Oid и relfilenode таблицы, которую мы создали вы 20508

Остальные файлы - это другие таблицы, TOAST- файлы, индексы, сиквенсы, материализованные вьюхи

Основной слой
Файлы без постфиксов (в нашем примере /PostgreSQL/15.7/data/base/20508). Место, где собственно находятся данные. Максимальный размер файла 1 Гбайт(можно увеличить при сборке параметромwith-segsize), при его достижении создается следующий файл этого же слоя(сегмент). Порядковый номер добавляется в конец через точку (если таблица test_table разрастется более 1 Гб, то создастся новый файл 20508.1)

Карта свободного пространства (free space map)
Постфикс _fsm. Это слой отношения, состоящий из FSM страниц, который помогает быстро найти страницу из основного слоя для записи новой версии строки. Максимальный размер файла 1 Гбайт(можно увеличить при сборке параметромwith-segsize). Чтобы обеспечить быстрый поиск свободного пространства, карта хранится в виде дерева (вообще в виде множества деревьев, но сегодня не об этом).

--pg_freespace(rel regclass IN, blkno OUT bigint, avail OUT int2)

Выдаёт объём свободного пространства на каждой странице отношения
, согласно FSM. Возвращается набор кортежей (blkno bigint, avail int2)
, по одному кортежу для каждой страницы в отношении.

SELECT * FROM pg_freespace('test_table');

blkno|avail|
-----+-----+
    0|   32|
    1|   32|
    2|   32|
    3|   32|
    4|   32|
    5|   32|
    6|   32|
    7|   32|
    8|   32|
    9|   32|
   10|   32|
   11|   32|
   12|   32|
   13|   32|
   14|   32|
   15|  256|

Карта видимости (visibility map)

Постфикс _vm. Этот слой позволяет определить надо ли почистить или заморозить страничку. Также помогает с index only scan (когда транзакция пытается прочитать строку из такой страницы, можно не проверять ее видимость, а это позволяет использовать сканирование только индекса).Файлы этого слоя обычно довольно маленькие. На каждую страницу выделяется 2 бита (1бит - все версии строк актуальны, 2 бит - все версии строк заморожены). Ссылка на GitHub
Карта видимости не создается для индексов.

Страницы

Каждый файл логически поделен на блоки(страницы)- это минимальный объем данных, который считывается или записывается. Это сделано для оптимизации операций ввода-вывода. Размер страницы задается переменной BLKSZ, по дефолту это 8Кбайт, по максимуму 32Кбайт (можно настроить при сборке, но делается это никогда редко).
Каждое отношение(таблица, индекс...) хранится как массив таких страниц. Они наполняют файл пока тот не достигнет максимального размера(SEGSIZE), после чего создается новый сегмент, и цикл продолжается.

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

Внутри страницы

Страница также поделена на части. Ссылочка на код с более подробным описанием

Заголовок

Сначала идет заголовок, который занимает 24 байта, и хранит общую информацию.

• Первые 2 блока: последняя запись в WAL, связанная с этой страницей(8 байт) и контрольная сумма страниц (2 байта)

• Далее 2 байта флагов:
  

  • PD_HAS_FREE_LINES 0x0001. Если этот флаг установлен, это означает, что на странице может быть свободное место, которое можно использовать для хранения новых кортежей без необходимости деления страницы.

  • PD_PAGE_FULL 0x0002 .Устанавливается, когда страница полностью заполнена данными.

  • PD_ALL_VISIBLE 0x0004 .Устанавливается, когда все записи на странице видимы для всех транзакций.

  • PD_VALID_FLAG_BITS 0x0007 .Маска, определяющая допустимые значения для битового поля.

• Далее идут 3 блока по 2 байта, указывающие на смещение:
  

  • pd_lower. Смещение до начала свободного пространства

  • pd_upper.Смещение до конца свободного пространства

  • pd_special .Смещение до начала специального пространства (до конца страницы)

• Далее информация о размере страницы и номере версии компоновки (2 байта)

• И в конце находится самый старый неочищенный идентификатор xmax на странице или ноль при отсутствии такового (необходимо для оптимизации VACUUM)

SELECT * 
FROM page_header(get_raw_page('test_table',0)); 

lsn        |checksum|flags|lower|upper|special|pagesize|version|prune_xid|
-----------+--------+-----+-----+-----+-------+--------+-------+---------+
40/CED9E4C8|    -905|    4|  432|  480|   8192|    8192|      4|0        |

Указатели

После заголовка следует массив указателей на версии строк (line pointers). Каждый указатель занимает 4 байта и содержит:

• lp_off . Смещение строки относительно начала страницы

• lp_flags. Набор флагов состояния указателя
  

  • LP_UNUSED |0 |/* unused (should always have lp_len=0)*/

  • LP_NORMAL |1 |/* used (should always have lp_len>0) */

  • LP_REDIRECT|2 |/* HOT redirect (should have lp_len=0) */

  • LP_DEAD |3 |/* dead, may or may not have storage */

• lp_len . Длина строки

Ссылочка на исходный код, для дальнейшего погружения

SELECT lp, lp_off, lp_flags, lp_len 
FROM heap_page_items(get_raw_page('test_table',0)) sub

lp |lp_off|lp_flags|lp_len|
---+------+--------+------+
  1|  7944|       1|   241|--тут есть фраза 
  2|  7680|       1|   261|--тут есть фраза
  3|  7608|       1|    65|--тут только имя 
  .
102|   480|       1|    65|
Оффсет уменьшается так как записи добавляются с конца к началу

Версии строк

После указателей до фактических данных (версий строк) идет блок свободного пространства. Собственно, тут ничего особо интересного, никакой фрагментации, единый блок пустого места. Наличие свободного места, как раз, и отмечается в карте свободного пространства.

В конец свободного пространства размещаются версии строк, а в начало указатели.

Слотированная страница
*
 * +--------------------+---------------------------------------+
 * | Заголовок страницы | Указатель1 Указатель2 Указатель3...   |
 * +--------------------+---------------------------------------+
 * | ... УказательN |		            						|
 * +------------------------------------------------------------+
 * |		   		^ Смещение до начала свободного пространства|
 * |												 		    |
 * |			 v Смещение до конца свободного пространства    |
 * +------------------------------------------------------------+
 * |			 | кортежN ...                        		    |
 * +------------------------------------------------------------+
 * |	   ... кортеж3 кортеж2 кортеж1 | "Специальная область"  |
 * +--------------------+---------------------------------------+
 *						^ pd_special			
 *

Далее, собственно, идет самое интересное - версии строк (tuples,rows,кортежи).

В случае таблиц мы говорим не просто о строках, а о версиях строк (row versions, tuples), поскольку многоверсионность предполагает существование нескольких версий одной и той же строки. На индексы многоверсионность не распространяется; вместо этого индексы ссылаются на все возможные табличные версии строк, среди которых по правилам видимости выбираются подходящие.

Версия строки состоит из заголовка и непосредственно данных. Заголовок записи (tuple_header) достаточно тяжелый (минимум 23 байта), он содержит служебную информацию о записи.

• t_xmin Поле, которое наравне с xmax играет ключевую роль в обеспечении многоверсионной конкурентной работы (MVCC). xmin (minimum transaction ID) — это идентификатор транзакции, которая создала запись.

• t_xmax (maximum transaction ID) — это идентификатор транзакции, которая удалила или обновила запись.

• t_field.Поле разделенное между тремя виртуальными полями Cmin,Cmax,Xvac

  We store five "virtual" fields Xmin, Cmin, Xmax, Cmax, and Xvac in three physical fields. Xmin and Xmax are always really stored, but Cmin, Cmax and Xvac share a field. This works because we know that Cmin and Cmax are only interesting for the lifetime of the inserting and deleting transaction respectively. If a tuple is inserted and deleted in the same transaction, we store a "combo" command id that can be mapped to the real cmin and cmax, but only by use of local state within the originating backend. See combocid.c for more details. Meanwhile, Xvac is only set by old-style VACUUM FULL, which does not have any command sub-structure and so does not need either Cmin or Cmax.
  
  ссылочка

• t_ctid. Физическое расположение записи (номер блока, смещение)

• t_infomask. Различные флаги

  /*
   * information stored in t_infomask:
   */
  #define HEAP_HASNULL			0x0001	/* Наличие NULL */
  #define HEAP_HASVARWIDTH		0x0002	/* Наличие атрибутов переменной длины */
  #define HEAP_HASEXTERNAL		0x0004	/* Хранится ли что-то в TOAST */
  #define HEAP_HASOID_OLD			0x0008	/* Есть ли поле OID */
  #define HEAP_XMAX_KEYSHR_LOCK	0x0010	/* Наличие key_share блокировки */
  #define HEAP_COMBOCID			0x0020	/* t_cid является combo CID*/
  #define HEAP_XMAX_EXCL_LOCK		0x0040	/* Эксклюзивная блокировка */
  #define HEAP_XMAX_LOCK_ONLY		0x0080	/* Транзакция, установившая xmax, является единственным владельцем записи. */
  
   /* xmax is a shared locker */
  #define HEAP_XMAX_SHR_LOCK	(HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)
  
  #define HEAP_LOCK_MASK	(HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \
  						 HEAP_XMAX_KEYSHR_LOCK)
  #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 */
  #define HEAP_XMAX_IS_MULTI		0x1000	/* t_xmax это MultiXactId */
  #define HEAP_UPDATED			0x2000	/* Это обновленная версия строки*/
  #define HEAP_MOVED_OFF			0x4000	/* Запись перемещена в другое место вакуумом*/
  #define HEAP_MOVED_IN			0x8000	/* Запись перемещена из другого место вакуумом */
  #define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)
  
  #define HEAP_XACT_MASK			0xFFF0	/* Маска видимости */

  Еще одна ссылка на исходный код

• t_infomask2 Еще немного флагов

  /*
   * Информация сохраняемая в t_infomask2:
   */
  #define HEAP_NATTS_MASK			0x07FF	/* 11 бит для количества аттрибутов */
  /* bits 0x1800 are available */
  #define HEAP_KEYS_UPDATED		0x2000	/* строка была обновлена или удалена */
  #define HEAP_HOT_UPDATED		0x4000	/* строка была HOT-updated */
  #define HEAP_ONLY_TUPLE			0x8000	/* heap-only строка */
  
  #define HEAP2_XACT_MASK			0xE000	/* биты видимости аттрибутов */
  

  Линк

• t_hoff (tuple header offset).Поле, которое указывает на смещение, необходимое для доступа к первым данным кортежа. Иными словами, это своего рода "указатель" на начало полезной нагрузки данных внутри кортежа.

• t_bits Битмап NULL значений. Может отсутствовать, если NULL нет.

SELECT
	t_xmin,
	t_xmax,
	t_field3,
	t_ctid,
	t_infomask2,
	t_infomask,
	t_hoff,
	t_bits
FROM
	heap_page_items(get_raw_page('test_table',0)) sub

t_xmin|t_xmax|t_field3|t_ctid |t_infomask2|t_infomask|t_hoff|t_bits  |
------+------+--------+-------+-----------+----------+------+--------+
110286|0     |       0|(0,1)  |          3|      2306|    24|        |
110286|0     |       0|(0,2)  |          3|      2306|    24|        |--нет NULL
113319|0     |       0|(0,3)  |          3|      2307|    24|11000000|--третий атрибут NULL 
113319|0     |       0|(0,4)  |          3|      2307|    24|11000000|
113319|0     |       0|(0,5)  |          3|      2307|    24|11000000|
113319|0     |       0|(0,6)  |          3|      2307|    24|11000000|
.
113319|0     |       0|(0,102)|          3|      2307|    24|11000000|

Далее идут непосредственно данные, которые хранятся в шестнадцатеричной системе и выглядят угрожающе

SELECT tt.author, tt.phrase
FROM heap_page_items(get_raw_page('test_table',0)) sub
JOIN test_table tt 
	ON tt.ctid = sub.t_ctid; -- не надо так делать, ctid может не соответствовать

Заключение

Надеюсь, материал был интересен, и вы с пользой провели эти 15 минут. Закончить хочется словами с книги Postgres Internals, которые, как мне кажется, актуальны не только для Postgres:

"Обдумывайте, экспериментируйте, проверяйте все сведения самостоятельно."