Физическое проектирование: типы данных, хранение и полуструктурированные данные

В темах 7 и 8 схема базы данных строилась на логическом уровне: сущности и связи превращались в отношения, функциональные зависимости подсказывали, как разбить широкую таблицу на несколько узких без потери информации. Логическая схема отвечает на вопрос «какие отношения и с какими ключами нужны», но молчит о том, как эти отношения будут жить на диске. Между тем от решений этого уровня зависят и корректность (сумма платежей, посчитанная в числах с плавающей точкой, может не сойтись с банковской выпиской на копейку), и объём (одна и та же таблица при неудачном порядке столбцов занимает на пятую часть больше места), и скорость (таблица, которую можно отсечь целиком, не нужно читать).

Физическое проектирование (англ. physical design) — этап, на котором логическая схема превращается в объекты конкретной СУБД: выбираются типы данных столбцов, способы генерации ключей, размещение таблиц по схемам и табличным пространствам, разбиение больших таблиц на секции, а также индексы ¹⁴. Индексам посвящена отдельная тема 10, а здесь рассматривается всё остальное. В трёхуровневой архитектуре ANSI/SPARC, о которой шла речь в теме 1, это внутренний уровень, и благодаря физической независимости данных его можно менять, не переписывая запросы приложения.

Изложение построено так. Первый раздел посвящён выбору типов данных: числам, строкам, датам, перечислениям, массивам и диапазонам, доменам. Второй — генерации ключей: последовательностям, столбцам идентичности и UUID. Третий раздел спускается на уровень файлов и страниц и объясняет, как PostgreSQL хранит строку таблицы, почему порядок столбцов влияет на размер и что происходит с длинными значениями. Четвёртый посвящён полуструктурированным данным в форматах json и jsonb. Пятый рассматривает схемы, табличные пространства и секционирование таблиц. Все примеры выполнены в PostgreSQL 16 на учебной базе «Книжный узел» (scale=1, база bookstore); эксперименты, создающие собственные таблицы, выполняются в отдельной схеме lab09.

Выбор типов данных

Целые числа, numeric и числа с плавающей точкой

Тип данных столбца — это одновременно ограничение целостности (в столбец integer нельзя записать строку), способ представления значения в памяти и на диске и набор операций над ним. Стандарт SQL определяет несколько семейств числовых типов, и PostgreSQL реализует их все ¹.

Целые типы smallint, integer и bigint занимают 2, 4 и 8 байт и хранят числа в диапазонах примерно ±32 тысячи, ±2,1 миллиарда и ±9,2·10¹⁸ соответственно. Арифметика над ними точная, а переполнение — ошибка, а не «заворачивание» значения, как в языке C:

SELECT 2147483647::integer + 1;

ERROR:  integer out of range

Тип numeric(p, s) (синоним decimal) хранит десятичные числа с заданной точностью p — общим числом значащих цифр — и масштабом s — числом цифр после запятой. Без указания p и s он хранит числа практически любой длины: до 131 072 цифр до запятой и до 16 383 после ¹. Внутри значение записывается в системе счисления с основанием 10 000 (каждые четыре десятичные цифры — двухбайтовое «слово»), поэтому длина представления переменная: цена 1290.00 из таблицы book занимает на диске 5 байт, а 0.00 — 3 байта. Операции над numeric выполняются программно и медленнее целочисленных, но результат точен в пределах заданного масштаба.

Типы real и double precision — это двоичные числа с плавающей точкой стандарта IEEE 754, 4 и 8 байт, с точностью около 6 и 15 десятичных знаков ¹⁵. Главное их свойство, которое нужно понимать при проектировании, состоит в том, что большинство десятичных дробей в двоичной системе непредставимы точно. Число 0,1 в двоичной записи — бесконечная периодическая дробь, и хранится лишь её приближение ⁶:

SELECT 0.1::float8 + 0.2::float8 AS f,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS f_eq,
       0.1 + 0.2 AS n,
       0.1 + 0.2 = 0.3 AS n_eq;

          f          | f_eq |  n  | n_eq 
---------------------+------+-----+------
 0.30000000000000004 | f    | 0.3 | t

Литерал 0.1 без приведения в PostgreSQL имеет тип numeric, поэтому вторая пара столбцов вычислена точно. Ошибка в одном сложении ничтожна, но она накапливается. Сумма миллиона слагаемых 0,1 должна равняться ровно 100 000:

SELECT sum(0.1::float8) AS f8, sum(0.1::float4) AS f4, sum(0.1::numeric) AS n
FROM generate_series(1, 1000000);

         f8         |    f4     |    n     
--------------------+-----------+----------
 100000.00000133288 | 100958.34 | 100000.0

В double precision ошибка составила тысячные доли копейки, в real — почти тысячу рублей. Отсюда правило физического проектирования: денежные суммы, количества и всё, что должно сходиться при сложении и сравниваться на равенство, хранится в целых числах или в numeric с фиксированным масштабом; именно поэтому столбцы price и amount в «Книжном узле» объявлены как numeric(10, 2). Числа с плавающей точкой уместны для результатов измерений и научных расчётов, где значения изначально приближённые, а скорость и компактность важнее точного равенства. Сравнивать их на равенство не следует вовсе — только с допуском.

Тип money хранит сумму как 8-байтовое целое число минимальных единиц валюты и поэтому точен, но число знаков после запятой и формат вывода зависят от параметра lc_monetary: в учебном кластере с локалью C.UTF-8 запрос SELECT '1290.00'::money выводит $1,290.00, а резервная копия, восстановленная в базу с другой локалью, может быть прочитана неверно ¹. Поэтому для денег обычно предпочитают numeric.

Строки, кодировки и правила сортировки

Стандарт SQL определяет строковые типы character(n) (сокращённо char(n)) — строку фиксированной длины, дополняемую пробелами, и character varying(n) (varchar(n)) — строку переменной длины не длиннее n символов. PostgreSQL добавляет к ним тип text — строку произвольной длины, не входящую в стандарт, но поддерживаемую большинством СУБД под тем или иным именем.

В некоторых СУБД выбор между этими типами влияет на производительность: строка фиксированной длины позволяет вычислять смещение следующего столбца без чтения длины. В PostgreSQL все три типа хранятся одинаково — как значение переменной длины с заголовком, — и документация формулирует вывод однозначно: разницы в производительности между ними нет, кроме лишнего места у дополняемого пробелами char(n) и нескольких тактов процессора на проверку длины у varchar(n); char(n) обычно самый медленный из трёх, и в большинстве случаев следует использовать text или varchar ¹. Ограничение длины в varchar(n) — это ограничение целостности, а не способ сэкономить место. Если правило предметной области звучит сложнее, чем «не длиннее n», его лучше выразить через CHECK, как сделано для ISBN в учебной базе: столбец isbn проверяется регулярным выражением «ровно 13 цифр».

У char(n) есть и семантическая особенность: хвостовые пробелы считаются незначимыми при сравнении. Это легко приводит к неожиданностям:

SELECT 'abc'::char(5) = 'abc  '::char(5)       AS char_eq,
       'abc'::varchar(5) = 'abc  '::varchar(5) AS varchar_eq,
       length('abc'::char(5))                  AS len,
       'abc'::char(5) || '|'                   AS concat;

 char_eq | varchar_eq | len | concat 
---------+------------+-----+--------
 t       | f          |   3 | abc|

Значение типа char(5) хранится с дополняющими пробелами, но функция length их не считает, а при приведении к text для конкатенации они отбрасываются.

Длина строки в символах и в байтах различается. Сервер учебной базы использует кодировку UTF-8 (SHOW server_encoding), в которой латинская буква занимает один байт, а кириллическая — два:

SELECT title, length(title) AS chars, octet_length(title) AS bytes,
       pg_column_size(title) AS stored
FROM bookstore.book WHERE book_id IN (1, 9);

         title         | chars | bytes | stored 
-----------------------+-------+-------+--------
 Война и мир           |    11 |    20 |     21
 PostgreSQL 16 изнутри |    21 |    28 |     29

Функция pg_column_size показывает размер значения в том виде, в каком оно хранится: 20 байт текста плюс однобайтовый заголовок длины (подробнее о заголовках — в разделе о хранении). Кодировка задаётся при создании базы и в дальнейшем не меняется; для новых баз фактически безальтернативна UTF-8.

С кодировкой тесно связано правило сортировки (англ. collation) — функция, определяющая порядок строк и их равенство при сравнении. Порядок «по кодам символов» (правило C) прост и быстр, но для человека неестествен: все прописные буквы идут раньше строчных, а «ё» оказывается после «я». Лингвистически правильный порядок для русского языка дают правила сортировки библиотеки ICU (англ. International Components for Unicode), реализующей алгоритм сортировки Unicode ¹¹:

-- правило сортировки базы: C.UTF-8
SELECT name FROM (VALUES ('ёж'), ('Ель'), ('ель'), ('яблоко'),
                         ('Яма'), ('арбуз'), ('Банан')) AS v(name)
ORDER BY name;
-- то же с лингвистическим правилом ICU для русского языка
SELECT name FROM (VALUES ('ёж'), ('Ель'), ('ель'), ('яблоко'),
                         ('Яма'), ('арбуз'), ('Банан')) AS v(name)
ORDER BY name COLLATE "ru-RU-x-icu";

 по C.UTF-8 | по ru-RU-x-icu
------------+---------------
 Банан      | арбуз
 Ель        | Банан
 Яма        | ёж
 арбуз      | ель
 ель        | Ель
 яблоко     | яблоко
 ёж         | Яма

(Результаты двух запросов сведены в одну таблицу.) Правило сортировки задаётся для базы при создании (в PostgreSQL 15 и новее базу можно создать сразу с правилом ICU), а также для отдельного столбца или выражения ключевым словом COLLATE ¹. Выбор правила — тоже решение физического проектирования: от него зависят результат ORDER BY, работа ограничений уникальности и, как будет показано в теме 10, возможность использовать индекс для поиска по префиксу LIKE 'abc%'. Смена правила сортировки у существующих данных требует перестроения индексов, потому что индекс хранит строки упорядоченными по старому правилу.

Дата и время

Тип date хранит календарную дату (4 байта), time — время суток, timestamp и timestamp with time zone (timestamptz) — момент времени с точностью до микросекунды (8 байт), interval — промежуток времени (16 байт) ¹. Самая частая ошибка проектирования в этом семействе — выбор timestamp (полное имя timestamp without time zone) для хранения моментов событий.

Различие между двумя типами не в том, что один «хранит часовой пояс», а другой нет. Ни один из них пояс не хранит. Значение timestamptz при вводе переводится в UTC с учётом указанного или текущего (параметр сеанса TimeZone) часового пояса и хранится как момент в UTC, а при выводе переводится в пояс текущего сеанса ¹. Значение timestamp хранится как есть — это «показания настенных часов» без привязки к месту. Эксперимент:

CREATE TEMP TABLE tz_demo (ts timestamp, tstz timestamptz);
SET TimeZone = 'Europe/Moscow';
INSERT INTO tz_demo VALUES ('2024-03-01 12:00', '2024-03-01 12:00');
SET TimeZone = 'Asia/Novosibirsk';
SELECT * FROM tz_demo;

         ts          |          tstz          
---------------------+------------------------
 2024-03-01 12:00:00 | 2024-03-01 16:00:00+07

Заказ, оформленный в полдень по Москве, для сеанса в Новосибирске произошёл в 16:00 местного времени, и столбец timestamptz показывает это верно. Столбец timestamp утверждает, что заказ был в 12:00, — но по каким часам, уже не восстановить. Поэтому моменты событий (создание заказа, платёж, вход пользователя) хранятся в timestamptz, как это сделано в учебной базе для orders.created_at и payment.paid_at. Тип timestamp уместен лишь для «плановых» значений, привязанных к местному времени по смыслу: «открытие магазина в 9:00 по местному времени» должно оставаться 9:00 и после перехода на летнее время.

Арифметика с интервалами тоже учитывает часовые пояса. Интервал хранит отдельно месяцы, дни и микросекунды, поэтому interval '1 day' и interval '24 hours' — разные величины. В поясе с переходом на летнее время они дают разный результат:

SET TimeZone = 'Europe/Berlin';
SELECT timestamptz '2024-03-30 12:00' + interval '1 day'    AS plus_day,
       timestamptz '2024-03-30 12:00' + interval '24 hours' AS plus_24h;

        plus_day        |        plus_24h        
------------------------+------------------------
 2024-03-31 12:00:00+02 | 2024-03-31 13:00:00+02

В ночь на 31 марта 2024 года в Германии часы перевели вперёд, сутки длились 23 часа, и «через день в то же время» не совпадает с «через 24 часа». Аналогично date '2024-01-31' + interval '1 month' даёт 29 февраля: при прибавлении месяцев день обрезается до последнего дня месяца.

Перечисления, массивы, диапазоны и домены

Столбец orders.status может принимать пять значений. Ограничить его можно тремя способами, и выбор между ними — классическое решение физического проектирования.

Первый способ, принятый в учебной базе, — text с ограничением CHECK (status IN ('new', 'paid', ...)). Он прост, значения читаемы, а изменить список можно командой ALTER TABLE, заменив ограничение. Второй — тип-перечисление (англ. enumerated type), CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'delivered', 'cancelled'). Значение перечисления занимает 4 байта (строка 'delivered' — 10 байт), сравнение идёт в порядке объявления ('paid' < 'shipped' истинно), а добавлять значения можно командой ALTER TYPE ... ADD VALUE; удалить значение из перечисления нельзя ¹. Третий — таблица-справочник статусов и внешний ключ на неё. Справочник позволяет хранить при каждом значении дополнительные атрибуты (название для интерфейса, признак «заказ завершён»), менять список обычными командами DML и управлять им без изменения схемы, но требует соединения для получения названия. Если список значений — часть логики приложения и меняется вместе с кодом, подходят CHECK или перечисление; если им управляют пользователи — справочник.

PostgreSQL поддерживает массивы любого встроенного или пользовательского типа (по стандарту SQL тип записывается как text ARRAY, integer ARRAY) с операторами включения @> и пересечения && ¹. Массив нарушает первую нормальную форму в её строгом понимании (тема 8), и для сущностей, на которые нужно ссылаться внешними ключами, он не годится: нельзя объявить, что каждый элемент массива авторов ссылается на author. Уместен массив для коротких списков значений, которые читаются и записываются целиком, — например, набора меток книги «хит» и «новинка».

Диапазонные типы (int4range, numrange, daterange, tstzrange и др.) представляют интервал значений с включёнными или исключёнными границами. Они удобны для периодов действия цен и акций: оператор @> проверяет попадание точки в диапазон, && — пересечение двух диапазонов, * вычисляет пересечение. В сочетании с ограничением исключения EXCLUDE (тема 2) диапазон позволяет декларативно запретить пересекающиеся периоды акций для одной книги; для этого нужен индекс GiST, о котором пойдёт речь в теме 10.

Тип bytea хранит двоичные данные (обложки, файлы); альтернатива — хранить файл во внешнем хранилище, а в базе только путь и контрольную сумму.

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

CREATE DOMAIN isbn13 AS char(13) CHECK (VALUE ~ '^[0-9]{13}$');
SELECT '978516'::isbn13;

ERROR:  value for domain isbn13 violates check constraint "isbn13_check"

Сводка размеров основных типов приведена в таблице. Значения получены функцией pg_column_size в PostgreSQL 16 на 64-разрядной платформе.

Генерация ключей

Последовательности и столбцы идентичности

Суррогатный ключ (тема 2) нужно откуда-то брать. Классический источник в PostgreSQL — последовательность (англ. sequence), отдельный объект базы, выдающий возрастающие числа функцией nextval. Стандарт SQL:2003 ввёл столбцы идентичности (англ. identity columns), и в PostgreSQL 10 появился синтаксис GENERATED ALWAYS AS IDENTITY, который используется во всех таблицах учебной базы. За столбцом идентичности стоит та же последовательность, но она принадлежит столбцу, удаляется вместе с ним и не позволяет случайно вставить значение вручную ¹:

CREATE TABLE lab09.seq_demo (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text
);
INSERT INTO lab09.seq_demo (name) VALUES ('первая');
BEGIN;
INSERT INTO lab09.seq_demo (name) VALUES ('откатится');
ROLLBACK;
INSERT INTO lab09.seq_demo (name) VALUES ('третья');
SELECT * FROM lab09.seq_demo;

 id |  name  
----+--------
  1 | первая
  3 | третья

Значение 2 потеряно: последовательность нетранзакционна, и выданное nextval число не возвращается при откате. Это сделано сознательно — иначе параллельные транзакции ждали бы друг друга при каждой вставке. Следствие для проектирования: суррогатный ключ гарантирует уникальность, но не отсутствие пропусков, и строить на нём «сквозную нумерацию счетов без пропусков» нельзя. Попытка вставить явное значение в столбец GENERATED ALWAYS завершается ошибкой с подсказкой OVERRIDING SYSTEM VALUE; вариант GENERATED BY DEFAULT разрешает явные значения, но тогда легко получить конфликт с будущими значениями последовательности.

Предел integer — около 2,1 миллиарда — кажется недостижимым, но таблица событий, получающая тысячу строк в секунду, исчерпает его за 25 дней, а смена типа ключа большой таблицы с внешними ссылками требует перезаписи таблиц и индексов. Для таблиц, рост которых трудно предсказать, разумно сразу выбирать bigint.

UUID: случайные и упорядоченные по времени

Последовательность выдаёт уникальные значения только в пределах одной базы. Когда идентификатор должен создаваться в приложении до обращения к базе, в нескольких независимых базах или на клиенте, используют универсальный уникальный идентификатор (англ. Universally Unique Identifier, UUID) — 128-битное число, вероятность совпадения которого пренебрежимо мала. Современная спецификация UUID — RFC 9562 (2024) — описывает несколько версий ⁷. Версия 4 состоит из 122 случайных бит. Версия 7 начинается с 48-битной метки времени в миллисекундах от начала эпохи Unix, за которой идут случайные биты, поэтому идентификаторы версии 7, созданные позже, больше созданных раньше.

В PostgreSQL 16 тип uuid занимает 16 байт, а встроенная функция gen_random_uuid() (в ядре с версии 13) генерирует UUID версии 4. Функции для версии 7 в PostgreSQL 16 нет:

SELECT uuidv7();

ERROR:  function uuidv7() does not exist

Встроенная функция uuidv7() появилась только в PostgreSQL 18 ³; в более ранних версиях UUID версии 7 генерирует приложение или функция на SQL (в окружении лабораторной работы есть учебная функция uuid_v7_like(), собирающая такое значение из метки времени и gen_random_uuid()).

Почему версия имеет значение? Первичный ключ поддерживается индексом-B-деревом (тема 10), в котором ключи хранятся упорядоченно. Возрастающие ключи (последовательность или UUIDv7) всегда вставляются в самый правый лист дерева, страницы заполняются плотно, а «горячей» остаётся лишь небольшая правая часть индекса. Случайные ключи UUIDv4 попадают в произвольные листья: при каждой вставке нужна новая страница индекса, листья делятся пополам и в среднем оказываются заполнены примерно на 70 %. Эксперимент: в три таблицы с первичным ключом разного вида вставлено по 2 миллиона строк, после чего измерены размер индекса первичного ключа и средняя плотность его листьев (функция pgstatindex из расширения pgstattuple).

Индекс по UUID больше индекса по bigint уже потому, что ключ вдвое длиннее, но случайный порядок добавляет к этому ещё почти треть. Пока индекс помещается в оперативную память, разница сказывается в основном на размере; когда индекс многократно больше буферного кеша, случайная вставка означает чтение случайной страницы с диска на каждую строку, и скорость вставки падает в разы. Время вставки в нашем опыте составило около 5 с для bigint и около 21 с для UUIDv4 (из них около 4 с — сама генерация двух миллионов случайных UUID); на другой машине числа будут другими, но соотношение сохраняется. Отсюда рекомендация: если UUID нужен, предпочтительна версия 7; если не нужен — достаточно bigint с идентичностью. У UUIDv7 есть и обратная сторона: метка времени в ключе раскрывает момент создания записи, что не всегда допустимо для идентификаторов, видимых пользователю.

Как PostgreSQL хранит таблицу

Файлы, слои и страницы

На внутреннем уровне PostgreSQL хранит каждое отношение — таблицу, индекс, последовательность, материализованное представление — в отдельном наборе файлов в каталоге данных кластера ². Путь к основному файлу относительно каталога данных возвращает функция pg_relation_filepath:

SELECT pg_relation_filepath('bookstore.orders');

 pg_relation_filepath 
----------------------
 base/16384/16605

Здесь 16384 — идентификатор (OID) базы данных, 16605 — номер файла таблицы (relfilenode); у вас числа будут другими. Файл делится на сегменты по 1 ГиБ (16605, 16605.1, ...), чтобы не упираться в ограничения файловых систем. Кроме основного слоя (англ. main fork) с данными у таблицы есть служебные слои: карта свободного пространства (англ. free space map, файл с суффиксом _fsm), где для каждой страницы записано, сколько места на ней свободно, и карта видимости (англ. visibility map, _vm) — по два бита на страницу, отмечающие, что все строки страницы видимы всем транзакциям и что все они «заморожены» ². Карта видимости создаётся очисткой (VACUUM); она понадобится в теме 10 для сканирования только индекса, а её смысл подробно раскрывается в теме 12.

Размеры слоёв и отношения в целом показывают функции pg_relation_size(rel, fork), pg_table_size (таблица со всеми слоями и TOAST), pg_indexes_size и pg_total_relation_size (всё вместе):

SELECT pg_relation_size('bookstore.orders')         AS main,
       pg_relation_size('bookstore.orders', 'fsm')  AS fsm,
       pg_size_pretty(pg_table_size('bookstore.orders'))          AS tbl,
       pg_size_pretty(pg_indexes_size('bookstore.orders'))        AS idx,
       pg_size_pretty(pg_total_relation_size('bookstore.orders')) AS total;

  main   |  fsm  |   tbl   |  idx   |  total  
---------+-------+---------+--------+---------
 1212416 | 24576 | 1216 kB | 456 kB | 1672 kB

Основной слой занимает 1 212 416 байт — ровно 148 страниц по 8192 байта (в выводе pg_size_pretty единицы kB и MB двоичные, то есть это КиБ и МиБ).

Файл отношения — последовательность страниц (англ. page, также block) фиксированного размера. В PostgreSQL размер страницы задаётся при компиляции и по умолчанию равен 8 КиБ (SHOW block_size выводит 8192); все операции ввода-вывода и весь буферный кеш работают страницами ². Устройство страницы таблицы (англ. heap page) показано на рисунке ниже.

Страница начинается с заголовка (PageHeaderData) длиной 24 байта: в нём LSN — номер последней записи журнала, изменившей страницу (тема 13), контрольная сумма, флаги и три смещения: pd_lower — конец массива указателей, pd_upper — начало области данных, pd_special — начало специальной области (у таблиц она пуста, у индексов в ней хранятся служебные данные). За заголовком идёт массив указателей строк (англ. line pointers, ItemIdData) по 4 байта: каждый содержит смещение и длину кортежа на странице. Сами кортежи записываются с конца страницы к началу, так что свободное место всегда находится посередине, между pd_lower и pd_upper ².

Адрес строки в таблице — пара «номер страницы, номер указателя», которая называется ctid. Ссылка идёт на указатель, а не на байтовое смещение кортежа, поэтому кортеж можно перемещать внутри страницы при уплотнении, не меняя его адрес; индексы хранят именно такие адреса.

Заглянуть внутрь страницы позволяет расширение pageinspect ⁴. Оно устанавливается командой CREATE EXTENSION pageinspect и доступно суперпользователю. Функция get_raw_page читает страницу как массив байтов, page_header разбирает заголовок, heap_page_items — указатели и заголовки кортежей:

SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('bookstore.orders', 0));

 lower | upper | special | pagesize 
-------+-------+---------+----------
   568 |   576 |    8192 |     8192

SELECT lp, lp_off, lp_len, t_hoff, t_ctid
FROM heap_page_items(get_raw_page('bookstore.orders', 0)) LIMIT 3;

 lp | lp_off | lp_len | t_hoff | t_ctid 
----+--------+--------+--------+--------
  1 |   8136 |     56 |     24 | (0,1)
  2 |   8080 |     56 |     24 | (0,2)
  3 |   8024 |     56 |     24 | (0,3)

Первый кортеж занимает последние 56 байт страницы (смещение 8136), второй — предыдущие 56 и так далее. Всего на странице 136 кортежей: массив указателей заканчивается на байте 24 + 136 · 4 = 568, а данные начинаются с байта 8192 − 136 · 56 = 576. Свободными остались 8 байт — меньше, чем нужно для ещё одного кортежа с указателем.

Заголовок кортежа и выравнивание

Каждая версия строки на странице начинается с заголовка кортежа (HeapTupleHeaderData), занимающего 23 байта ². В нём хранятся номера транзакций t_xmin и t_xmax, создавшей и удалившей версию, номер команды, адрес t_ctid (у последней версии он указывает на саму строку, у устаревшей — на более новую версию), число столбцов и флаги (t_infomask), а также смещение начала данных t_hoff. Назначение этих полей — многоверсионное управление параллельным доступом, которое подробно рассматривается в теме 12; для физического проектирования важно, что каждая строка таблицы несёт около 24 байт служебной информации плюс 4 байта указателя. Для узких таблиц это больше, чем сами данные ¹³.

За заголовком может следовать битовая карта NULL (англ. null bitmap) — по одному биту на столбец, — если в строке есть хотя бы одно неопределённое значение. Данные начинаются со смещения t_hoff, кратного 8 байтам (величине MAXALIGN на 64-разрядных платформах). Поэтому 23-байтовый заголовок фактически занимает 24 байта, и в этот лишний байт помещается битовая карта таблицы, в которой не больше 8 столбцов. Если столбцов 9 и в строке есть NULL, карта занимает 2 байта, и t_hoff становится 32: в эксперименте с таблицей из девяти столбцов integer строка без NULL занимает 60 байт, а строка с одним NULL — 64, хотя данных в ней на 4 байта меньше.

Внутри области данных каждое значение выравнивается по границе, которую требует его тип (столбец attalign каталога pg_attribute): smallint — по 2 байтам, integer — по 4, bigint, timestamptz и double precision — по 8; boolean и короткие строки выравнивания не требуют. Между значениями вставляются байты-заполнители (англ. padding). Разберём строку таблицы orders длиной 56 байт:

Порядок столбцов в таблице влияет на число заполнителей. Сравним две таблицы с одинаковым набором столбцов — трёх boolean, двух bigint, integer, smallint и timestamptz, — объявленных в «неудачном» порядке (чередование однобайтовых и восьмибайтовых) и в «удачном» (от больших к меньшим), по 100 тысяч строк в каждой. Раскладка одной строки показана на рисунке.

CREATE TABLE lab09.bad  (a boolean, b bigint, c boolean, d bigint,
                         e boolean, f integer, g smallint, h timestamptz);
CREATE TABLE lab09.good (b bigint, d bigint, h timestamptz, f integer,
                         g smallint, a boolean, c boolean, e boolean);
-- вставка 100 000 строк в каждую

  t   | row_bytes |  size   | pages 
------+-----------+---------+-------
 bad  |        80 | 8248 kB |  1031
 good |        57 | 6672 kB |   834

Строка «неудачной» таблицы занимает 80 байт, «удачной» — 57, а вся таблица уменьшилась на 19 %. Заметим, что выигрыш в размере таблицы меньше выигрыша в длине строки: следующий кортеж на странице тоже начинается с границы 8 байт, поэтому 57-байтовая строка фактически занимает 64 байта, и на странице помещается 120 строк вместо 97. Правило, вытекающее из эксперимента: в широких таблицах с большим числом строк полезно объявлять сначала столбцы фиксированной длины с выравниванием 8, затем 4 и 2, затем однобайтовые и столбцы переменной длины. Для маленьких справочников экономия несущественна, и порядок лучше выбирать из соображений читаемости. Переставить столбцы существующей таблицы в PostgreSQL можно только пересоздав её.

TOAST: хранение длинных значений

Кортеж должен целиком помещаться на одной странице: PostgreSQL не разрывает строки между страницами. Как же тогда хранится text длиной в мегабайт? Ответ — механизм TOAST (англ. The Oversized-Attribute Storage Technique) ².

Все типы переменной длины («varlena») начинаются с заголовка длины. Значения короче 127 байт хранятся на диске с однобайтовым заголовком и без выравнивания, более длинные — с четырёхбайтовым. Два бита заголовка TOAST резервирует для своих целей, поэтому максимальная длина одного значения — 1 ГиБ ². Когда вставляемая строка таблицы длиннее порога TOAST_TUPLE_THRESHOLD, механизм TOAST начинает её «ужимать»: сжимает длинные значения и, если этого мало, выносит их в отдельную TOAST-таблицу, пока строка не станет короче TOAST_TUPLE_TARGET. Оба значения по документации «обычно 2 кБ» ²; точнее, порог вычисляется так, чтобы на странице помещалось четыре кортежа, и для страницы 8 КиБ составляет 2032 байта. Вынесенное значение режется на фрагменты (англ. chunks) примерно по 2000 байт, каждый хранится отдельной строкой TOAST-таблицы, а в основной строке остаётся указатель длиной 18 байт. Схема показана на рисунке.

Проверим порог экспериментом. В таблицу lab09.toast_demo (id integer, body text) вставлены строки со случайным (несжимаемым) текстом длиной от 1000 до 10 000 символов и строки из повторяющейся буквы a. Для каждой строки выведены длина текста, размер хранимого значения и длина кортежа на странице:

 текст              | length | stored | lp_len 
--------------------+--------+--------+--------
 случайный          |   1000 |   1004 |   1032
 случайный          |   1990 |   1994 |   2022
 случайный          |   2000 |   2004 |   2032
 случайный          |   2010 |   2010 |     46
 случайный          |   3000 |   3000 |     46
 случайный          |  10000 |  10000 |     46
 повтор 'a'         |   3000 |     44 |     72
 повтор 'a'         |  10000 |    125 |    153
 повтор 'a'         | 100000 |   1156 |   1184

Кортеж длиной ровно 2032 байта (24 байта заголовка, 4 байта id, 4 байта заголовка значения и 2000 байт текста) ещё хранится целиком. При 2010 символах кортеж превысил бы порог, текст сжать не удалось, и он вынесен в TOAST: на странице остался кортеж длиной 46 байт = 24 + 4 + 18. Текст из повторяющихся символов, напротив, прекрасно сжимается (алгоритмом pglz, заданным параметром default_toast_compression; с PostgreSQL 14 доступен и lz4, если сервер собран с его поддержкой) и остаётся в строке: сто тысяч букв a заняли 1156 байт. TOAST-таблица — обычная таблица в схеме pg_toast со столбцами chunk_id, chunk_seq и chunk_data; значение длиной 10 000 байт хранится в ней шестью фрагментами, максимальный из которых — 1996 байт.

Для каждого столбца задаётся стратегия хранения (столбец attstorage каталога): plain — без сжатия и выноса (для типов фиксированной длины), extended — сжатие, затем вынос (по умолчанию для text, jsonb, bytea), external — вынос без сжатия (ускоряет substring над длинными значениями), main — сжатие, а вынос только в крайнем случае (по умолчанию у numeric) ². Меняется она командой ALTER TABLE ... ALTER COLUMN ... SET STORAGE.

Благодаря TOAST длинные описания не мешают запросам, которые их не читают: основная таблица остаётся компактной. Обратная сторона — SELECT * по таблице с длинными значениями заметно дороже выборки нужных столбцов, потому что каждое вынесенное значение собирается из фрагментов. Обновление строки без изменения вынесенных значений их не копирует.

Fillfactor и оценка размера таблицы

Параметр хранения fillfactor (от 10 до 100, по умолчанию 100) задаёт, до какой доли страницы заполнять её при вставке; остаток резервируется для будущих обновлений строк этой страницы ¹⁰. Когда обновлённая версия строки помещается на ту же страницу, а изменённые столбцы не входят ни в один индекс, PostgreSQL выполняет HOT-обновление (англ. Heap-Only Tuple), не трогая индексы; об этом — в теме 10. Таблица orders, скопированная с fillfactor = 70, занимает 211 страниц вместо 148: на странице 95 строк вместо 136. Уменьшать fillfactor имеет смысл для таблиц с частыми обновлениями (остатки на складе, счётчики), а не для таблиц, в которые только добавляют строки.

Знание устройства страницы позволяет оценить размер таблицы до её создания — это и есть подробно разобранный пример раздела. Оценим таблицу orders при scale=50, где в ней миллион строк.

1. Длина кортежа: 24 байта заголовка и 32 байта данных (по таблице выше) — 56 байт; с указателем — 60 байт. Длина 56 кратна 8, поэтому дополнительного выравнивания между кортежами нет.
2. Полезное место страницы: 8192 − 24 = 8168 байт. Строк на странице: ⌊8168 / 60⌋ = 136 — ровно столько, сколько показал heap_page_items.
3. Страниц: ⌈1 000 000 / 136⌉ = 7353, размер: 7353 · 8192 байт ≈ 57,4 МиБ.

Проверка на базе bookstore50 (таблица после загрузки и автоматической очистки):

SELECT relpages, pg_size_pretty(pg_relation_size('bookstore.orders'))
FROM pg_class WHERE oid = 'bookstore.orders'::regclass;

 relpages | pg_size_pretty 
----------+----------------
     7353 | 57 MB

Расчёт совпал со страницей. Если бы status был перечислением (4 байта с выравниванием по 4) вместо text, строка заняла бы 24 + 4 + 4 + 8 + 4 + 4 = 48 байт, на страницу поместилось бы 157 строк, и таблица уменьшилась бы примерно на 13 %. Для проектировщика такая оценка — быстрый способ сравнить варианты схемы до загрузки данных. При этом надо помнить, что в реальной таблице есть и «мёртвые» версии строк, оставшиеся после обновлений и удалений (тема 12), поэтому фактический размер со временем превышает расчётный.

Полуструктурированные данные: JSON и JSONB

json и jsonb

Не все атрибуты сущности известны заранее. У бумажной книги есть переплёт, у электронной — формат файла, у детской — возрастная маркировка, у многотомника — число томов. Можно завести по столбцу на каждый возможный атрибут (большинство будут NULL), можно вынести атрибуты в таблицу «сущность — атрибут — значение» (англ. entity-attribute-value, EAV), а можно хранить их в столбце полуструктурированного формата. В «Книжном узле» выбран третий путь: столбец book.attrs типа jsonb.

PostgreSQL предлагает два типа для данных в формате JSON ⁸. Тип json хранит исходный текст документа без изменений и при каждом обращении разбирает его заново. Тип jsonb (появился в PostgreSQL 9.4) хранит документ в разобранном двоичном виде: ключи объектов отсортированы, повторяющиеся ключи схлопнуты (остаётся последний), пробелы не сохраняются. Различие видно сразу:

SELECT '{"b": 1, "a": 2, "a": 3}'::json  AS j,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS jb;

            j             |        jb        
--------------------------+------------------
 {"b": 1, "a": 2, "a": 3} | {"a": 3, "b": 1}

Ввод в jsonb чуть медленнее, но обработка намного быстрее, и только jsonb поддерживает операторы включения и индексы GIN (тема 10). Документация рекомендует jsonb для большинства приложений; json оправдан, если нужно сохранить документ байт в байт, например подписанный ответ внешней системы ⁸. По размеру типы близки: документ {"binding": "soft", "series": "Миры Стругацких"} занимает 66 байт как json и 70 байт как jsonb.

Операторы и язык путей SQL/JSON

Основные операторы над jsonb ⁹:

Различие между -> и ->> существенно: первый возвращает jsonb (строка в кавычках), второй — text, который можно сравнивать с обычной строкой и приводить к числу:

SELECT book_id, attrs -> 'binding' AS j, attrs ->> 'binding' AS t,
       attrs -> 'volumes' AS volumes
FROM bookstore.book WHERE book_id IN (1, 4, 9);

 book_id |   j    |  t   | volumes 
---------+--------+------+---------
       1 | "hard" | hard | 2
       4 | "soft" | soft | 
       9 | "pdf"  | pdf  | 

Отсутствующий ключ даёт NULL. Оператор включения @> проверяет, что документ содержит указанную структуру, и именно его поддерживают индексы GIN:

SELECT book_id, title FROM bookstore.book
WHERE attrs @> '{"series": "Миры Стругацких"}';

 book_id |       title       
---------+-------------------
       4 | Пикник на обочине
       5 | Трудно быть богом

Стандарт SQL:2016 ввёл язык путей SQL/JSON (англ. SQL/JSON path language) для навигации по документам, и PostgreSQL поддерживает его с версии 12 ⁹. Выражение пути начинается с $ (весь документ), спускается по ключам через точку, по массивам — через индексы и *, а фильтр записывается после ? в скобках, где @ обозначает текущий элемент. Функция jsonb_path_query возвращает найденные элементы, jsonb_path_exists и оператор @? проверяют, что что-то найдено:

SELECT count(*) FROM bookstore.book
WHERE attrs @? '$.age ? (@ == "18+" || @ == "16+")';

SELECT jsonb_path_query(
    '{"formats": [{"type": "pdf", "size": 12}, {"type": "epub", "size": 5}]}',
    '$.formats[*] ? (@.size > 10).type');

 count 
-------
   295

 jsonb_path_query 
------------------
 "pdf"

Документ можно изменять функциями jsonb_set (заменить значение по пути), операторами || и -. Изменение даже одного ключа записывает новую версию всего документа (а если он вынесен в TOAST — и всех его фрагментов), поэтому для больших документов с частыми точечными изменениями jsonb — дорогой выбор.

Когда JSONB уместен, а когда это скрытая денормализация

JSONB даёт гибкость, но платой за неё служат гарантии, которые реляционная модель предоставляет бесплатно. У ключа в документе нет типа (в одной книге "volumes": 2, в другой — "volumes": "два"), нет ограничения NOT NULL, нет внешнего ключа, нет статистики распределения отдельных ключей для планировщика (тема 11), а опечатка в имени ключа в запросе не вызывает ошибки — просто ничего не находится. Документ, в который встроены данные других сущностей (например, имена авторов книги), — это денормализация в чистом виде со всеми аномалиями обновления из темы 8: переименование автора потребует обойти все документы.

Разумное правило таково. Атрибуты, по которым есть бизнес-логика, связи, частые фильтры и агрегаты, — столбцы со своими типами и ограничениями. В JSONB уместно хранить действительно разнородные и редко используемые в условиях атрибуты (характеристики товаров разных категорий), данные внешних систем, которые сохраняются как есть, и настройки. Если атрибут из JSONB со временем «повзрослел» и стал использоваться повсеместно, его выносят в столбец; в лабораторной работе это делается командами ALTER TABLE ... ADD COLUMN и UPDATE. Промежуточный вариант — генерируемый столбец, вычисляемый из документа:

ALTER TABLE lab09.book_j
    ADD COLUMN binding text GENERATED ALWAYS AS (attrs ->> 'binding') STORED;

Часть гарантий можно вернуть и для документа — ограничением CHECK над выражениями jsonb:

ALTER TABLE lab09.book_j ADD CONSTRAINT attrs_shape
    CHECK (jsonb_typeof(attrs) = 'object'
           AND attrs ->> 'binding' IN ('soft', 'hard', 'pdf'));
UPDATE lab09.book_j SET attrs = attrs || '{"binding": "spiral"}' WHERE book_id = 1;

ERROR:  new row for relation "book_j" violates check constraint "attrs_shape"
DETAIL:  Failing row contains (1, Война и мир, {"binding": "spiral", "volumes": 2}).

Сравнение документной модели с реляционной на более широком материале — с документными СУБД вроде MongoDB — проводится в теме 16 ¹².

Схемы, табличные пространства и секционирование

Схемы и путь поиска

Схема (англ. schema) — пространство имён внутри базы данных: в разных схемах могут быть таблицы с одинаковыми именами ¹. Все таблицы учебной базы находятся в схеме bookstore, системные — в pg_catalog, временные — в отдельной схеме сеанса, а схема public создаётся в каждой базе по умолчанию. Имя без указания схемы разрешается по пути поиска — параметру search_path:

SHOW search_path;
SELECT count(*) FROM book;
SET search_path = bookstore, public;
SELECT count(*) FROM book;

   search_path   
-----------------
 "$user", public

ERROR:  relation "book" does not exist

 count 
-------
  2010

По умолчанию путь включает схему с именем текущего пользователя и public; каталог pg_catalog неявно просматривается первым. Схемы используются для разделения модулей в одной базе, для экспериментов, не затрагивающих основные данные (lab09), и для разграничения прав (тема 15). Путь поиска — источник известной уязвимости: объект с «чужим» именем в схеме, стоящей раньше в пути, перехватит обращение без указания схемы, поэтому в функциях с повышенными правами путь задают явно (темы 14 и 15).

Табличные пространства

Табличное пространство (англ. tablespace) — каталог файловой системы, в котором PostgreSQL размещает файлы отношений ¹. По умолчанию их два: pg_default (каталог base кластера) и pg_global (общие для кластера каталоги). Команда CREATE TABLESPACE fast LOCATION '/mnt/nvme/pg' позволяет вынести, например, горячие индексы на быстрый накопитель. Табличное пространство — не самостоятельная единица хранения: без каталога кластера и журнала его файлы бесполезны, и резервное копирование должно охватывать их все (тема 13).

Декларативное секционирование

Когда таблица вырастает до сотен миллионов строк, дорогими становятся очистка всей таблицы, удаление старых данных командой DELETE и работа с индексами, не помещающимися в память. Секционирование (англ. partitioning) делит одну логическую таблицу на несколько физических — секций (англ. partitions) — по значению ключа секционирования ¹⁰. Запросы обращаются к родительской таблице, а СУБД направляет строки в нужные секции и пропускает секции, которые заведомо не содержат подходящих строк.

PostgreSQL с версии 10 поддерживает декларативное секционирование трёх видов ¹⁰:

• по диапазону (PARTITION BY RANGE) — секция получает интервал значений ключа, типично по дате: заказы по годам или месяцам;
• по списку (PARTITION BY LIST) — секция получает перечень значений: заказы по регионам;
• по хешу (PARTITION BY HASH, с версии 11) — строка попадает в секцию по остатку от деления хеша ключа на число секций (MODULUS, REMAINDER); это способ равномерно разделить данные без естественного диапазона.

Создадим копию таблицы заказов, секционированную по годам:

CREATE TABLE lab09.orders_p (
    order_id     integer NOT NULL,
    customer_id  integer NOT NULL,
    created_at   timestamptz NOT NULL,
    status       text NOT NULL,
    warehouse_id integer,
    PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE (created_at);
CREATE TABLE lab09.orders_2022 PARTITION OF lab09.orders_p
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE lab09.orders_2023 PARTITION OF lab09.orders_p
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE lab09.orders_2024 PARTITION OF lab09.orders_p
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
INSERT INTO lab09.orders_p SELECT * FROM bookstore.orders;
SELECT tableoid::regclass AS partition, count(*) FROM lab09.orders_p GROUP BY 1 ORDER BY 1;

     partition     | count 
-------------------+-------
 lab09.orders_2022 |  6559
 lab09.orders_2023 |  6766
 lab09.orders_2024 |  6675

Нижняя граница диапазона включается, верхняя — нет, поэтому полночь 1 января 2023 года попадает во вторую секцию. Первичный ключ секционированной таблицы обязан включать ключ секционирования: уникальность проверяется индексами каждой секции по отдельности, и только так СУБД может гарантировать её для всей таблицы. Строка, для которой нет подходящей секции, вызывает ошибку no partition of relation "orders_p" found for row; чтобы её избежать, создают секцию по умолчанию (PARTITION OF ... DEFAULT) или заранее — секции на будущие периоды. Структура секционированной таблицы и отсечение секций показаны на рисунке.

Главная выгода для запросов — отсечение секций (англ. partition pruning): если условие запроса ограничивает ключ секционирования, планировщик исключает из плана секции, которые не могут содержать нужных строк ¹⁰. Как именно читается план выполнения, подробно разбирается в теме 11; здесь достаточно увидеть, какие секции в нём остались:

EXPLAIN SELECT count(*) FROM lab09.orders_p
WHERE created_at >= '2023-03-01' AND created_at < '2023-04-01';

                                QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=152.99..153.00 rows=1 width=8)
   ->  Seq Scan on orders_2023 orders_p  (cost=0.00..151.49 rows=600 width=0)
         Filter: ((created_at >= '2023-03-01 00:00:00+03'::timestamp with time zone)
                   AND (created_at < '2023-04-01 00:00:00+03'::timestamp with time zone))

В плане одна секция — orders_2023 (длинная строка условия перенесена для читаемости). Если же условие не касается ключа секционирования, читаются все секции:

EXPLAIN SELECT count(*) FROM lab09.orders_p WHERE status = 'cancelled';

 Aggregate  (cost=409.69..409.70 rows=1 width=8)
   ->  Append  (cost=0.00..406.12 rows=1425 width=0)
         ->  Seq Scan on orders_2022 orders_p_1  (cost=0.00..130.99 rows=488 width=0)
               Filter: (status = 'cancelled'::text)
         ->  Seq Scan on orders_2023 orders_p_2  (cost=0.00..134.57 rows=463 width=0)
               Filter: (status = 'cancelled'::text)
         ->  Seq Scan on orders_2024 orders_p_3  (cost=0.00..133.44 rows=474 width=0)
               Filter: (status = 'cancelled'::text)

Узел Append объединяет результаты сканирования трёх секций. Отсечение возможно и во время выполнения, если граница известна только тогда — например, задана функцией now() или параметром подготовленного оператора; в плане это видно как строка Subplans Removed: N.

Не менее важна выгода для обслуживания. Удалить данные за 2022 год можно командой DROP TABLE lab09.orders_2022 или отсоединить секцию ALTER TABLE ... DETACH PARTITION — это мгновенная операция над метаданными вместо DELETE миллионов строк. Очистка и построение индексов выполняются по секциям, и старые, неизменяемые секции не нужно обрабатывать повторно.

Секционирование — не бесплатное ускорение: запрос без условия по ключу обходит все секции, при сотнях секций растут затраты на планирование, а уникальность по столбцам вне ключа секционирования обеспечить нельзя. Документация рекомендует выбирать ключ секционирования по самым частым условиям запросов и операциям удаления данных и избегать чрезмерного числа секций ¹⁰. Для таблицы в десятки тысяч строк, как orders при scale=1, секционирование ничего не даёт; оно оправдано, когда таблица измеряется десятками гигабайт или когда данные регулярно удаляются целыми периодами. Распределение секций по разным серверам — шардирование — рассматривается в теме 16.

Итоги темы

Физическое проектирование превращает логическую схему в объекты конкретной СУБД, и почти каждое его решение — компромисс между корректностью, объёмом и скоростью. Тип данных — это одновременно ограничение и способ хранения. Денежные суммы требуют точной арифметики numeric или целых чисел, потому что двоичная плавающая точка не представляет десятичные дроби и накапливает ошибку. Моменты событий хранятся в timestamptz, который переводит время в UTC и обратно, а не в timestamp, теряющем привязку к часовому поясу. В PostgreSQL char(n) и varchar(n) не быстрее text, а ограничение длины — это правило целостности, не оптимизация. Выбор между перечислением, CHECK и справочником определяется тем, кто и как меняет список значений.

Суррогатные ключи из последовательностей уникальны, но допускают пропуски. Случайные UUID версии 4 рассыпают вставки по всему индексу, отчего индекс становится больше и медленнее; UUID версии 7, упорядоченные по времени, ведут себя почти как последовательность. В PostgreSQL 16 встроенной функции для них нет — она появилась в версии 18.

На внутреннем уровне таблица — набор файлов из страниц по 8 КиБ. Каждая строка несёт 23-байтовый заголовок, выровненный до 24 байт, и 4-байтовый указатель; столбцы выравниваются по границам своих типов, поэтому порядок столбцов влияет на размер таблицы. Длинные значения сжимаются и выносятся в TOAST-таблицу, когда строка превышает порог около 2 КиБ. Зная эти числа, можно с точностью до страницы рассчитать размер таблицы до её создания.

Тип jsonb даёт гибкость для разнородных атрибутов и поддерживает операторы включения и язык путей SQL/JSON, но лишает данные типов, ограничений и связей; атрибуты, по которым есть логика и связи, должны быть столбцами. Секционирование делит большую таблицу на части по ключу, позволяя пропускать ненужные секции при запросах и удалять старые данные целыми секциями.

Остался главный инструмент физического проектирования, без которого ни одна большая таблица не обходится, — индекс. Как устроено B-дерево, почему его высота почти не растёт с ростом таблицы, какие ещё типы индексов есть в PostgreSQL и чего стоит каждый индекс при изменении данных, рассматривается в теме 10.

Литература

1. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. Chapter 8. Data Types; 5.9. Schemas; 23.6. Tablespaces; 24.2. Collation Support. — 2024. https://www.postgresql.org/docs/16/datatype.html
2. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. Chapter 73. Database Physical Storage. — 2024. https://www.postgresql.org/docs/16/storage.html
3. PostgreSQL Global Development Group. PostgreSQL 18 Documentation. 9.14. UUID Functions. — 2025. https://www.postgresql.org/docs/18/functions-uuid.html
4. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. F.25. pageinspect — low-level inspection of database pages. — 2024. https://www.postgresql.org/docs/16/pageinspect.html
5. IEEE 754-2019. IEEE Standard for Floating-Point Arithmetic. — IEEE, 2019, DOI: 10.1109/IEEESTD.2019.8766229.
6. Goldberg D. What Every Computer Scientist Should Know About Floating-Point Arithmetic. — ACM Computing Surveys, 1991, Т. 23, № 1, С. 5–48, DOI: 10.1145/103162.103163.
7. Davis K., Peabody B., Leach P. Universally Unique IDentifiers (UUIDs). — RFC 9562, IETF, 2024. https://www.rfc-editor.org/rfc/rfc9562
8. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. 8.14. JSON Types. — 2024. https://www.postgresql.org/docs/16/datatype-json.html
9. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. 9.16. JSON Functions and Operators. — 2024. https://www.postgresql.org/docs/16/functions-json.html
10. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. 5.11. Table Partitioning; CREATE TABLE (Storage Parameters). — 2024. https://www.postgresql.org/docs/16/ddl-partitioning.html
11. Davis M., Whistler K., Scherer M. Unicode Technical Standard #10: Unicode Collation Algorithm. — Unicode Consortium. https://www.unicode.org/reports/tr10/
12. Kleppmann M. Designing Data-Intensive Applications. — O'Reilly Media, 2017. (Рус. пер.: Клеппман М. Высоконагруженные приложения. Программирование, масштабирование, поддержка. — СПб.: Питер, 2018.)
13. Рогов Е. В. PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024. https://postgrespro.ru/education/books/internals
14. Silberschatz A., Korth H. F., Sudarshan S. Database System Concepts. 7th ed. — McGraw-Hill, 2019.

Дополнительные материалы

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

1. Почему число 0,1 нельзя точно записать в типе double precision, а в numeric можно? Приведите пример запроса, результат которого из-за этого будет неверным.
2. Какой тип вы выберете для цены товара, массы посылки в граммах, координат на карте и рейтинга книги от 1 до 5? Обоснуйте каждый выбор.
3. В PostgreSQL char(13) для ISBN, varchar(13) и text с ограничением CHECK — чем они различаются по хранению, производительности и поведению при сравнении?
4. Сеанс с TimeZone = 'UTC' записал в столбец timestamptz значение '2024-05-01 10:00'. Что увидит сеанс с TimeZone = 'Asia/Yekaterinburg'? А если столбец timestamp?
5. Почему interval '1 day' и interval '24 hours' — разные значения? В каких поясах это различие проявляется?
6. Для статуса заказа предложены перечисление, CHECK и справочник. При каких требованиях к системе вы выберете каждый вариант?
7. Почему в таблице с идентичностью могут быть пропуски ключей? Можно ли построить на последовательности сквозную нумерацию документов без пропусков?
8. Чем UUID версии 7 лучше версии 4 в роли первичного ключа и чем хуже? Есть ли встроенная функция для версии 7 в PostgreSQL 16?
9. Что такое ctid и почему индекс хранит ссылку на указатель строки, а не на байтовое смещение кортежа?
10. Рассчитайте длину строки таблицы (id bigint, flag boolean, created timestamptz, qty smallint) и предложите порядок столбцов, при котором она минимальна.
11. В каком случае заголовок кортежа занимает 24 байта, а в каком — 32?
12. Что произойдёт при вставке строки с текстом длиной 50 000 символов в столбец text? Как изменится ответ, если текст состоит из одной повторяющейся буквы?
13. Почему запрос SELECT * к таблице с длинными описаниями может быть заметно медленнее, чем SELECT title, даже если описания не выводятся на экран?
14. Какие гарантии целостности теряются при хранении атрибута в jsonb? Как вернуть часть из них?
15. Почему отсечение секций не срабатывает для условия date_part('year', created_at) = 2023 и как переписать условие?

Глоссарий темы

Шпаргалка по инструментам

Типы и проверка значений:

SELECT pg_typeof(x), pg_column_size(x);         -- тип и хранимый размер значения
SELECT pg_column_size(t.*) FROM tbl t LIMIT 1;  -- размер строки целиком
SHOW server_encoding; SHOW TimeZone; SHOW lc_monetary;
SELECT octet_length(s), length(s);              -- байты и символы
SELECT s COLLATE "ru-RU-x-icu" ...;             -- правило сортировки для выражения
CREATE TYPE status AS ENUM ('new', 'paid');     -- перечисление
ALTER TYPE status ADD VALUE 'cancelled';
CREATE DOMAIN isbn13 AS char(13) CHECK (VALUE ~ '^[0-9]{13}$');
SELECT gen_random_uuid();                       -- UUID v4 (uuidv7() — только с PG 18)

Размеры и файлы:

SELECT pg_relation_filepath('bookstore.orders');            -- путь от data_directory
SELECT pg_relation_size('t'), pg_relation_size('t', 'fsm'), pg_relation_size('t', 'vm');
SELECT pg_table_size('t'), pg_indexes_size('t'), pg_total_relation_size('t');
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relpages, reltuples, reltoastrelid::regclass FROM pg_class WHERE oid = 't'::regclass;
SELECT attname, attlen, attalign, attstorage FROM pg_attribute
WHERE attrelid = 't'::regclass AND attnum > 0;
ALTER TABLE t ALTER COLUMN c SET STORAGE external;           -- стратегия TOAST
ALTER TABLE t SET (fillfactor = 80);                         -- для будущих вставок

Расширение pageinspect (суперпользователь):

CREATE EXTENSION pageinspect;
SELECT * FROM page_header(get_raw_page('t', 0));             -- lower, upper, special
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid, t_hoff, t_bits
FROM heap_page_items(get_raw_page('t', 0));
SELECT * FROM heap_page_item_attrs(get_raw_page('t', 0), 't'::regclass);  -- значения столбцов

JSONB:

attrs -> 'k'          attrs ->> 'k'         attrs #>> '{a,b}'
attrs @> '{"k": "v"}' attrs ? 'k'           attrs ?| array['a','b']
attrs || '{"k": 1}'   attrs - 'k'           jsonb_set(attrs, '{k}', '1')
jsonb_typeof(attrs)   jsonb_object_keys(attrs)   jsonb_each_text(attrs)
attrs @? '$.k ? (@ > 1)'   jsonb_path_query(attrs, '$.items[*].price')

Секционирование:

CREATE TABLE p (...) PARTITION BY RANGE (created_at);       -- или LIST (...), HASH (...)
CREATE TABLE p_2024 PARTITION OF p FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE p_list PARTITION OF q FOR VALUES IN ('Москва', 'Казань');
CREATE TABLE p_h0 PARTITION OF h FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE p_def PARTITION OF p DEFAULT;
ALTER TABLE p DETACH PARTITION p_2022;   ALTER TABLE p ATTACH PARTITION x FOR VALUES ...;
SELECT tableoid::regclass, count(*) FROM p GROUP BY 1;      -- строки по секциям
EXPLAIN SELECT ... ;                                        -- какие секции остались в плане

Метакоманды psql: \d+ t (столбцы, хранение, параметры, секции), \dT+ (типы и перечисления), \dD (домены), \dn (схемы), \db (табличные пространства), \dx (расширения), \timing on.

Что почитать и посмотреть

• Документация PostgreSQL 16, глава 8 «Data Types» — https://www.postgresql.org/docs/16/datatype.html. Первоисточник по всем типам, включая важные замечания о money, char(n) и часовых поясах; разделы 8.5 (дата и время) и 8.14 (JSON) стоит прочитать целиком.
• Документация PostgreSQL 16, глава 73 «Database Physical Storage» — https://www.postgresql.org/docs/16/storage.html. Файлы, слои, TOAST, карта видимости, устройство страницы и кортежа; таблицы полей заголовков, использованные в теме.
• Рогов Е. В. «PostgreSQL 16 изнутри» (ДМК Пресс, 2024; свободно на postgrespro.ru/education/books/internals) — части «Изоляция и многоверсионность» и главы о страницах и TOAST. Подробное изложение с экспериментами pageinspect, логичное продолжение этой темы.
• Goldberg D. «What Every Computer Scientist Should Know About Floating-Point Arithmetic» — https://doi.org/10.1145/103162.103163. Классическая статья о представлении чисел с плавающей точкой и источниках ошибок; для практических выводов достаточно первых разделов.
• RFC 9562 «Universally Unique IDentifiers (UUIDs)» — https://www.rfc-editor.org/rfc/rfc9562. Современная спецификация UUID, в том числе версии 7 и рекомендации по использованию в роли ключей баз данных.
• Документация PostgreSQL 16, раздел 5.11 «Table Partitioning» — https://www.postgresql.org/docs/16/ddl-partitioning.html. Виды секционирования, отсечение секций, ограничения и рекомендации по выбору ключа и числа секций.
• Документация PostgreSQL 16, разделы 8.14 «JSON Types» и 9.16 «JSON Functions and Operators» — https://www.postgresql.org/docs/16/functions-json.html. Полный перечень операторов и функций jsonb, синтаксис языка путей SQL/JSON.
• Блог Cybertec, статьи о порядке и выравнивании столбцов — https://www.cybertec-postgresql.com/en/blog/. Практические примеры экономии места перестановкой столбцов на больших таблицах.
• Unicode Technical Standard #10 «Unicode Collation Algorithm» — https://www.unicode.org/reports/tr10/. Как устроены многоуровневые правила сортировки, которые реализует ICU.
• Kleppmann M. «Designing Data-Intensive Applications», глава 2 «Data Models and Query Languages». Сравнение реляционной и документной моделей, обсуждение «схемы при записи» и «схемы при чтении» — хорошее обоснование правил использования JSONB.
• Лекции CMU 15-445/645 «Database Systems» (Andy Pavlo), лекции о хранении («Database Storage», «Storage Models & Compression») — https://15445.courses.cs.cmu.edu. Устройство страниц и кортежей в общем виде и сравнение подходов разных СУБД.

Углубление (по желанию)

Почему у PostgreSQL нет «кластерного» хранения таблицы

В MySQL (InnoDB) и SQL Server таблица с первичным ключом обычно хранится как B-дерево, листья которого содержат сами строки, упорядоченные по ключу; такое хранение называют кластерным (англ. clustered index, index-organized table). Поиск по первичному ключу в нём выполняется за один проход по дереву, а соседние по ключу строки лежат рядом. PostgreSQL хранит таблицу как «кучу» (англ. heap) — неупорядоченный набор страниц, а все индексы, включая первичный ключ, вторичны и ссылаются на строки по ctid. У кучи свои достоинства: вставка дешевле (не нужно искать место по ключу), вторичные индексы ссылаются на физический адрес, а не на значение ключа, и многоверсионность естественно реализуется размещением новой версии в любом свободном месте. Команда CLUSTER переупорядочивает таблицу по индексу один раз, но порядок не поддерживается при дальнейших изменениях. Как это сказывается на индексах, видно в теме 10 на примере индекса BRIN.

Колоночное хранение

Страница PostgreSQL хранит строки целиком: все столбцы одной строки лежат рядом. Для транзакционной нагрузки это естественно — заказ читается и записывается целиком. Аналитические запросы устроены иначе: они читают миллионы строк, но лишь несколько столбцов. Колоночные СУБД (ClickHouse, DuckDB, Vertica) хранят каждый столбец отдельно, что позволяет читать только нужные столбцы и эффективно сжимать однотипные значения; расплата — дорогие точечные изменения строк. Для PostgreSQL существуют расширения с колоночным хранением, а общий обзор строчного и колоночного подходов дан в теме 16.

Числа в деньгах: банковское округление и валюты

Даже точная арифметика numeric не избавляет от необходимости решать, как округлять. Функция round в PostgreSQL для numeric округляет половину «от нуля» (round(2.5) = 3), а для double precision — к ближайшему чётному (round(2.5::float8) = 2), как требует IEEE 754 по умолчанию. В бухгалтерии разных стран приняты разные правила, а при распределении суммы скидки по позициям заказа сумма округлённых частей может не совпасть с округлённой суммой — копейку приходится явно «доначислять» одной из позиций. Если в системе несколько валют, сумма без кода валюты теряет смысл: правильная модель — пара «сумма, валюта» и запрет складывать суммы разных валют без явного пересчёта по курсу на определённую дату.

Лабораторная работа 9. Типы данных, хранение и JSONB

• Раздел курса: тема 9 «Физическое проектирование: типы данных, хранение и полуструктурированные данные»

Введение

В предыдущих работах схема учебной базы принималась как данность: таблицы «Книжного узла» уже были созданы, типы столбцов выбраны. В этой работе мы смотрим на ту же базу глазами проектировщика физического уровня. Почему цена хранится в numeric(10, 2), а не в real? Почему момент заказа — timestamptz? Сколько места занимает строка таблицы orders и почему именно столько? Где на диске лежит таблица и что происходит с длинным описанием книги? Как устроен столбец attrs и когда его содержимое пора превратить в обычные столбцы?

Работа опирается на тему 9 и на навыки SQL из тем 3–6. Основной инструмент наблюдения — функции размеров (pg_column_size, pg_relation_size) и расширение pageinspect, позволяющее увидеть страницу таблицы байт за байтом. Индексы в этой работе не рассматриваются (они — предмет лабораторной работы 10), а планы выполнения используются только в части 6 и только для того, чтобы увидеть, какие секции таблицы читаются; подробно планы разбираются в теме 11.

Работа состоит из основной части (части 1–5) и продвинутой (части 6–7), которую можно выполнить по желанию.

Цель работы

Научиться обоснованно выбирать типы данных и структуру хранения таблицы в PostgreSQL, измеряя последствия выбора на учебной базе.

После выполнения работы можно будет:

• продемонстрировать накопление ошибки округления в числах с плавающей точкой и найти связанную с ней ошибку в существующей схеме;
• объяснить различие timestamp и timestamptz и предсказать результат запроса при смене часового пояса сеанса;
• найти файлы таблицы на диске, прочитать заголовок страницы и указатели строк с помощью pageinspect и рассчитать размер таблицы до её создания;
• измерить влияние порядка столбцов на размер таблицы и объяснить его выравниванием;
• наблюдать сжатие и вынос длинных значений в TOAST и объяснить порог, при котором это происходит;
• писать запросы к документам jsonb с операторами ->, ->>, @>, ? и языком путей SQL/JSON, переносить атрибут из документа в столбец и ограничивать содержимое документа CHECK;
• создать секционированную таблицу и проверить по плану выполнения, что запрос читает только нужные секции.

Теоретический минимум

Теория изложена в теме 9: разделы «Выбор типов данных», «Генерация ключей», «Как PostgreSQL хранит таблицу», «Полуструктурированные данные: JSON и JSONB», «Схемы, табличные пространства и секционирование». Ниже — сжатая справка.

Фрагмент схемы учебной базы, используемый в работе:

book (book_id integer PK, isbn char(13), title text, publisher_id integer,
      category_id integer, pub_year smallint, pages integer,
      price numeric(10,2), attrs jsonb, description text)
orders (order_id integer PK, customer_id integer, created_at timestamptz,
        status text CHECK (...), warehouse_id integer)
payment (payment_id integer PK, order_id integer → orders,
         paid_at timestamptz, amount numeric(10,2), method text)

Ключи документа book.attrs: binding (переплёт: soft, hard, pdf) есть у всех книг; age (маркировка 6+, 12+, 16+, 18+) — примерно у трети; у нескольких первых книг есть series, volumes, lang, free.

Функции и представления, нужные в работе:

Операторы jsonb: -> (элемент как jsonb), ->> (как text), @> (включение), ? (наличие ключа), || (слияние), - (удаление ключа), @? (есть ли элементы по пути SQL/JSON); функции jsonb_path_query, jsonb_object_keys, jsonb_typeof, jsonb_set.

Полезные метакоманды psql: \d+ таблица (столбцы, стратегия хранения, параметры), \dT+ (типы), \dx (расширения), \timing on, \x auto.

Перечень оснащения

• ОС Ubuntu 24.04 (физическая машина, виртуальная машина или WSL2).
• PostgreSQL 16 с модулями contrib (sudo apt install postgresql postgresql-contrib); ваша роль — суперпользователь (создана в ЛР 1), поскольку pageinspect доступен только суперпользователю.
• Архив окружения lr-env: schema.sql и data.sql (учебная база, без изменений), setup.sql (схема lab09, расширения, вспомогательные функции и таблицы), check.sql (самопроверка ответов), README.md.
• Учебная база bookstore с scale=1; для части 3 желательна и база bookstore50 (scale=50), если она уже создана для лабораторной работы 10, — но работа выполнима и без неё.
• Ресурсы: около 300 МБ на диске, генерация scale=1 — несколько секунд.

Порядок выполнения работы

Распакуйте архив и подготовьте базу. Если база bookstore уже есть и не изменялась, первые три команды можно пропустить.

unzip lr-env.zip -d ~/lab09 && cd ~/lab09/lr-env
dropdb --if-exists bookstore && createdb bookstore
psql -d bookstore -q -f schema.sql && psql -d bookstore -q -v scale=1 -f data.sql
psql -d bookstore -f setup.sql

Скрипт setup.sql пересоздаёт схему lab09; повторный запуск возвращает её в исходное состояние, не затрагивая данные bookstore. Все собственные таблицы создавайте в схеме lab09. В начале каждого сеанса удобно выполнить SET search_path = lab09, bookstore, public;.

Ответы на задания, отмеченные «Ответ N», оформляйте представлениями lab09.answer_N со столбцами в указанном порядке и проверяйте командой psql -d bookstore -f check.sql. Скрипт сравнивает контрольную сумму результата с эталоном и сообщает «верно» или «неверно».

В журнал работы по каждой части заносятся предсказания (до выполнения), запросы, существенные фрагменты результатов и объяснения. Числа размеров и времени на разных машинах могут немного отличаться (времена — значительно); объяснение должно опираться на ваши собственные измерения.

Часть 1. Числа: точность и деньги

Задание

1. Предсказание. Запишите, что вернут выражения 0.1::real + 0.2::real = 0.3::real, 0.1::float8 * 3 = 0.3::float8 и 0.1 * 3 = 0.3. Проверьте.
2. Вычислите сумму числа 0,1, взятого миллион раз, в типах real, double precision и numeric (функция generate_series(1, 1000000)). Измерьте время каждого запроса (\timing on). Сведите результаты в таблицу «тип — сумма — абсолютная ошибка — время».
3. Сравните размеры значений: pg_column_size для 1::smallint, 1::integer, 1::bigint, 1::numeric, 1290.00::numeric(10,2), 123456789.12::numeric, 1.5::real, 1.5::float8. Почему размер numeric зависит от значения?
4. Найти ошибку. В схеме lab09 есть таблица payment_legacy — копия платежей «из старой системы», в которой сумма хранится в real, а момент платежа — в timestamp. Бухгалтерия жалуется, что отчёты не сходятся.
   • Сравните sum(amount) по lab09.payment_legacy и по bookstore.payment. Каков тип результата в каждом случае? Каково расхождение в рублях?
   • Найдите в bookstore.payment платёж на сумму 10739.98 и попробуйте найти его же в payment_legacy условием WHERE amount = 10739.98. Объясните результат.
   • Выясните, сколько платежей «изменились» при переносе: сравните l.amount::numeric с p.amount, соединив таблицы по payment_id.
   • Предложите исправление схемы командой ALTER TABLE ... ALTER COLUMN amount TYPE numeric(10, 2) USING .... Внутри транзакции (BEGIN ... ROLLBACK) сравните два варианта выражения USING: round(amount::numeric, 2) и round(amount::float8::numeric, 2). После каждого посчитайте, сколько сумм всё ещё не совпадает с bookstore.payment. Объясните результат, вычислив 10739.98::real::numeric и 10739.98::real::float8::numeric. Как бы вы исправили столбец paid_at?
5. Ответ 1. Сумма платежей по способам оплаты: представление lab09.answer_1 (method, total, payments) по таблице bookstore.payment, где total — точная сумма, payments — число платежей.

Что должно получиться

Таблица из пункта 2 с объяснением, почему ошибка в real больше, чем в double precision; размеры из пункта 3; разбор ошибки из пункта 4 (два отчёта, найденные и ненайденные строки, предложенный DDL); результат самопроверки ответа 1.

Часть 2. Дата и время

Задание

1. Выполните SHOW TimeZone;. Создайте временную таблицу tz_demo (ts timestamp, tstz timestamptz) и вставьте в оба столбца строку '2024-03-01 12:00'.
2. Предсказание. Что покажет SELECT * FROM tz_demo после SET TimeZone = 'Asia/Novosibirsk'? После SET TimeZone = 'UTC'? После SET TimeZone = 'America/New_York'? Запишите предсказания, затем проверьте.
3. Вставьте строку '2024-03-01 12:00+05' в оба столбца. Что сохранилось в ts? Почему смещение +05 не вызвало ошибки?
4. Для заказа с order_id = 1 выведите created_at в трёх видах: как хранится в сеансе, created_at AT TIME ZONE 'Asia/Vladivostok' (какого типа результат?) и to_char(created_at, 'YYYY-MM-DD HH24:MI TZ').
5. В сеансе с TimeZone = 'Europe/Berlin' вычислите timestamptz '2024-10-26 12:00' + interval '1 day' и + interval '24 hours'. Объясните разницу. Повторите в Europe/Moscow.
6. Вычислите date '2024-01-31' + interval '1 month', date '2023-01-31' + interval '1 month' и date '2024-03-01' - date '2024-02-01'. Какого типа каждый результат?
7. Объясните, почему переход таблицы payment_legacy на timestamp опасен: что произойдёт с моментами платежей, если сервер переедет в другой часовой пояс, а приложение начнёт подключаться с другим значением TimeZone?

Что должно получиться

Предсказания и фактические результаты пунктов 2–6; объяснение, что именно хранится в столбце timestamptz; ответ на вопрос пункта 7.

Часть 3. Страницы, кортежи и выравнивание

Задание

1. Найдите файл таблицы bookstore.orders: pg_relation_filepath, SHOW data_directory. Посмотрите на файлы командой ls -l (от имени пользователя postgres: sudo -u postgres ls -l <каталог>/<путь>*). Какие слои есть у таблицы? Сравните размер основного файла с pg_relation_size('bookstore.orders').
2. Вычислите число страниц таблицы orders двумя способами: pg_relation_size / 8192 и relpages из pg_class. Совпадают ли они? Когда могут не совпадать?
3. Прочитайте заголовок страницы 0 таблицы orders (page_header(get_raw_page('bookstore.orders', 0))) и первые 5 указателей (heap_page_items). Запишите lower, upper, lp_off и lp_len и проверьте арифметику: сколько кортежей на странице и сколько байт свободно?
4. По данным каталога (attlen, attalign в pg_attribute) составьте раскладку строки orders по байтам: смещение и длина каждого столбца, заполнители. Сравните сумму с lp_len. Объясните, почему status = 'delivered' занимает 10 байт.
5. Предсказание и расчёт. Рассчитайте вручную число строк на странице и число страниц таблицы orders для миллиона строк (scale=50). Если у вас есть база bookstore50, сравните с relpages её таблицы orders.
6. Создайте в схеме lab09 две таблицы с одинаковым набором столбцов — boolean, bigint, boolean, bigint, boolean, integer, smallint, timestamptz — в порядке «как перечислено» (lab09.bad) и в порядке, который вы считаете оптимальным (lab09.good). До вставки рассчитайте длину строки каждой таблицы и число строк на странице. Вставьте в каждую по 100 000 строк и проверьте расчёт (pg_column_size(t.*), pg_relation_size, heap_page_items по странице 0).
7. Создайте таблицу из девяти столбцов integer и вставьте две строки: без NULL и с NULL во втором столбце. Сравните lp_len, t_hoff и t_bits двух кортежей. Объясните, почему строка с NULL длиннее.
8. Скопируйте orders в lab09.orders_ff70 с параметром WITH (fillfactor = 70) (создайте таблицу (LIKE bookstore.orders) WITH (fillfactor = 70) и вставьте строки). Сколько стало страниц и строк на странице? Для каких таблиц учебной базы вы бы уменьшили fillfactor и почему?

Замечание. При массовой вставке PostgreSQL 16 может заранее расширять файл сразу на несколько страниц, поэтому сразу после CREATE TABLE ... AS SELECT размер файла иногда больше расчётного; команда VACUUM отрезает пустые страницы в конце файла. Если результат не совпал с расчётом, выполните VACUUM и повторите измерение.

Что должно получиться

Путь к файлу и список слоёв; заголовок страницы и указатели с проверкой арифметики; таблица раскладки строки orders; расчёт размера orders при scale=50 и сравнение с фактом; таблица «вариант — длина строки (расчёт/факт) — строк на странице — размер таблицы» для bad и good; объяснение опыта с NULL; результаты для fillfactor.

Часть 4. TOAST и длинные значения

Задание

1. Найдите TOAST-таблицу bookstore.book (reltoastrelid в pg_class) и её размер. Есть ли в ней данные? Какова максимальная длина description у книг? Выведите attstorage столбцов book: какие из них могут попасть в TOAST?
2. Создайте lab09.toast_demo (id integer, body text) и вставьте строки со случайным несжимаемым текстом (lab09.rnd_text(n)) длиной 1000, 1990, 2000, 2010, 2020, 3000 и 10 000 символов. Предсказание: какие из них останутся в строке таблицы, а какие будут вынесены? Проверьте, соединив таблицу с heap_page_items(get_raw_page('lab09.toast_demo', 0)) по ctid = t_ctid и выведя length(body), pg_column_size(body) и lp_len. Найдите порог в байтах и сопоставьте его с документацией.
3. Вставьте строки repeat('a', n) для n = 3000, 10 000 и 100 000. Сколько байт они заняли и где хранятся? Каким алгоритмом сжаты (SHOW default_toast_compression, функция pg_column_compression(body))?
4. Посмотрите содержимое TOAST-таблицы toast_demo: число фрагментов на каждое значение (chunk_id, count(*), max(length(chunk_data))). Объясните, откуда взялся размер фрагмента.
5. Создайте копию lab09.book_long таблицы book и сделайте описания длинными: у книг с book_id % 10 = 0 — случайный текст из 4000 символов, у книг с book_id % 10 = 5 — описание, повторённое 60 раз. Выполните VACUUM lab09.book_long. Сравните размеры основного слоя, TOAST-таблицы и pg_table_size до и после. Для трёх групп книг (остаток 0, 5 и прочие) выведите средние length(description) и pg_column_size(description).
6. Сравните время запросов SELECT sum(length(title)) FROM lab09.book_long и SELECT sum(length(description)) FROM lab09.book_long (по 3 раза, \timing on). Объясните разницу и сделайте вывод о запросах SELECT *.

Что должно получиться

Таблица «длина текста — хранимый размер — lp_len — где хранится» для пунктов 2–3 с найденным порогом; число и размер фрагментов; таблица размеров book_long до и после; времена запросов и вывод.

Часть 5. Полуструктурированные данные в JSONB

Задание

1. Сравните '{"b": 1, "a": 2, "a": 3}'::json и тот же литерал как jsonb. Сравните pg_column_size одного и того же документа в двух типах. Какие свойства исходного текста jsonb не сохраняет?

2. Выведите для книг 1, 4, 9 значения attrs -> 'binding', attrs ->> 'binding' и pg_typeof каждого. Перечислите все ключи, встречающиеся в attrs (jsonb_object_keys), с числом книг для каждого ключа.

3. Ответ 2. Книги в твёрдом переплёте с возрастной маркировкой 16+ или 18+: lab09.answer_2 (book_id, title).

4. Ответ 3. Сколько книг каждого вида переплёта среди книг с маркировкой 18+: lab09.answer_3 (binding, books). Напишите запрос двумя способами — с оператором @> и с языком путей (@? или jsonb_path_exists) — и убедитесь, что результаты совпадают.

5. Ответ 4. Книги, в документе которых есть ключи, кроме binding и age: lab09.answer_4 (book_id, extra_keys), где extra_keys — эти ключи через запятую в алфавитном порядке.

6. Найти ошибку. В таблице lab09.book_j (копия книг с несколькими многотомниками) коллега ищет книги более чем в 5 томах запросом

   SELECT book_id, title FROM lab09.book_j WHERE attrs ->> 'volumes' > '5';
   

   и получает одну книгу, а также пытается найти книги в твёрдом переплёте запросом WHERE attrs -> 'binding' = 'hard' и получает ошибку. Объясните обе проблемы и исправьте запросы. Исправленный первый запрос оформите как Ответ 5: lab09.answer_5 (book_id, title, volumes), где volumes — целое число.

7. Перенос атрибута в столбец. Добавьте в lab09.book_j столбец binding text, заполните его из документа, удалите ключ binding из документов (attrs - 'binding') и добавьте ограничение CHECK (binding IN ('soft', 'hard', 'pdf')) и NOT NULL. Затем выполните обратное преобразование (вернуть ключ в документ из столбца) в одной транзакции и убедитесь, что lab09.book_j совпадает с исходными данными bookstore.book по attrs для всех книг, кроме изменённых setup.sql (запрос с EXCEPT).

8. Альтернатива переносу — генерируемый столбец. Добавьте в lab09.book_j столбец age text GENERATED ALWAYS AS (attrs ->> 'age') STORED. Что произойдёт при попытке записать в него значение напрямую? Что произойдёт со столбцом при изменении attrs?

9. Добавьте в lab09.book_j ограничение CHECK, требующее, чтобы attrs был объектом, а ключ volumes, если он есть, был положительным числом (jsonb_typeof). Проверьте его попытками записать '{"volumes": "два"}', '{"volumes": -1}' и документами, которые не являются объектом: строкой '"книга"' и массивом из двух чисел.

Что должно получиться

Ответы 2–5 (самопроверка check.sql), два варианта запроса ответа 3; объяснение ошибок пункта 6; команды переноса атрибута туда и обратно и проверка EXCEPT; наблюдения за генерируемым столбцом; ограничение пункта 9 и результаты проверок. Короткое (5–7 предложений) рассуждение: какие ключи attrs в «Книжном узле» вы бы оставили в документе, а какие сделали бы столбцами, и почему.

Часть 6. Секционирование и отсечение секций (продвинутая)

Задание

1. Создайте lab09.orders_p — копию структуры orders, секционированную по диапазону created_at с секциями по годам 2022, 2023, 2024. Попробуйте объявить первичный ключ только по order_id и объясните сообщение об ошибке; объявите правильный ключ.

2. Перенесите данные из bookstore.orders (INSERT ... SELECT). Сколько строк попало в каждую секцию (tableoid::regclass)? Попробуйте вставить заказ от 1 февраля 2025 года. Создайте секцию по умолчанию и повторите вставку.

3. Предсказание. Для каждого запроса заранее запишите, какие секции будут прочитаны, затем проверьте EXPLAIN (без ANALYZE; смотрите только на имена секций):

   • заказы за март 2023 года;
   • заказы со статусом cancelled;
   • заказы с created_at > '2024-06-01';
   • заказы, у которых date_part('year', created_at) = 2023;
   • заказы с created_at >= now() - interval '3 years'.

   Объясните, почему в четвёртом запросе отсечение не сработало, и перепишите условие так, чтобы сработало. Что означает строка Subplans Removed в пятом запросе и почему этот результат зависит от даты выполнения работы?

4. Удалите данные за 2022 год двумя способами. Сначала командой DELETE FROM lab09.orders_p WHERE created_at < '2023-01-01': после неё сравните размер файла секции orders_2022 (pg_relation_size) с исходным и через несколько секунд посмотрите n_dead_tup этой секции в pg_stat_user_tables. Затем пересоздайте секцию с данными и удалите её командами ALTER TABLE lab09.orders_p DETACH PARTITION lab09.orders_2022 и DROP TABLE lab09.orders_2022. Что происходит с файлом секции в каждом случае? Какой способ вы выбрали бы для ежемесячного удаления данных из таблицы в сотни миллионов строк и почему?

5. Создайте копию таблицы покупателей, секционированную по хешу customer_id на 4 секции. Как распределились строки? Для каких запросов (по customer_id = 42, по customer_id < 42, по city) происходит отсечение?

Что должно получиться

DDL секционированной таблицы и объяснение требования к первичному ключу; распределение строк; таблица «запрос — предсказанные секции — фактические секции» с объяснениями; сравнение двух способов удаления старых данных; распределение по хеш-секциям.

Часть 7. Ключи: последовательность, UUIDv4 и UUIDv7 (продвинутая)

Задание

1. Проверьте, что в PostgreSQL 16 нет функции uuidv7(). Сгенерируйте по 5 значений gen_random_uuid() и lab09.uuid_v7_like() и сравните: какая цифра в каждом UUID обозначает версию? Упорядочены ли значения версии 7 по времени создания?
2. Создайте три таблицы с первичным ключом bigint GENERATED ALWAYS AS IDENTITY, uuid DEFAULT gen_random_uuid() и uuid DEFAULT lab09.uuid_v7_like() и вставьте в каждую по 500 000 строк. Предсказание: какой индекс первичного ключа окажется самым большим и почему?
3. Сравните размер индексов первичного ключа и среднюю плотность листьев ((pgstatindex('lab09.<индекс>')).avg_leaf_density). Объясните, почему плотность индекса по UUIDv4 около 70 %, а по последовательности и UUIDv7 — около 90 %.
4. Сравните время вставки. Почему по времени нельзя напрямую сравнивать UUIDv7 из учебной функции с остальными вариантами? Измерьте отдельно время генерации 500 000 значений каждой функцией (SELECT count(f()) FROM generate_series(1, 500000)) и учтите его.
5. Вставьте в таблицу с ключом из последовательности строку в транзакции и откатите её. Какой ключ получит следующая строка? Почему последовательность не «возвращает» значение при откате?

Что должно получиться

Таблица «вид ключа — время вставки — время генерации ключей — размер индекса — плотность листьев»; объяснения пунктов 3–5; рекомендация (3–5 предложений), какой ключ вы выбрали бы для таблицы заказов магазина, принимающего заказы из мобильного приложения без связи с сервером.

Журнал работы

Журнал работы — записи для себя в формате «предсказание — команда или запрос — фактический результат — объяснение». Для каждой части полезно записать предсказание до выполнения, текст запроса или команды, существенный фрагмент результата и объяснение расхождений. Таблицы измерений и вывод check.sql удобно хранить рядом; в выводах отметьте, какие решения физического уровня в схеме «Книжного узла» вы считаете удачными, а какие изменили бы, с опорой на свои измерения. В конце стоит сформулировать несколько выводов: какие предсказания не оправдались и почему.

Контрольные вопросы

1. Почему сумма миллиона чисел 0,1 в типе real отличается от 100 000 на сотни, а в double precision — на тысячные доли? Почему в numeric ошибки нет?
2. Чем timestamptz отличается от timestamp с точки зрения того, что хранится на диске? В каком случае timestamp всё же уместен?
3. Почему в PostgreSQL char(n) не быстрее text, а varchar(n) не экономит место? Зачем тогда вообще указывать длину?
4. Из чего складываются 56 байт строки таблицы orders? Сколько из них приходится на служебную информацию и сколько — на данные?
5. Почему 23-байтовый заголовок кортежа фактически занимает 24 байта и при каком условии он занимает 32?
6. Как изменение порядка столбцов уменьшает таблицу? Почему выигрыш в длине строки и выигрыш в размере таблицы различаются?
7. Почему кортеж длиной 2032 байта хранится в строке целиком, а 2042 байта — уже нет? Откуда берётся число 2032?
8. Почему повторяющийся текст длиной 100 000 символов остался в основной таблице, а случайный длиной 2010 символов был вынесен?
9. Чем -> отличается от ->> и почему условие attrs ->> 'volumes' > '5' даёт неверный результат?
10. Какие гарантии теряются, когда атрибут хранится в jsonb, а не в столбце? Какие из них можно вернуть и как?
11. Почему первичный ключ секционированной таблицы должен включать ключ секционирования?
12. Почему вставка случайных UUID увеличивает индекс, а UUID версии 7 — нет? Какой недостаток есть у UUID версии 7?