JSONB: хранение и доступ

Прошлый урок закрыл кластер про индексы. Начинается новый: типы, которых нет в большинстве других СУБД. Первым возвращаемся к JSONB. В уроке про JSONB мы положили книгам магазина записку details и научились доставать из неё значения стрелками. Теперь посмотрим, как записка лежит на диске, как править её по кусочку и как разворачивать её в обычные строки.

Примеры работают на учебной базе магазина. Записку получили пять книг:

ALTER TABLE books ADD COLUMN IF NOT EXISTS details jsonb;

UPDATE books SET details = '{"publisher": "АСТ", "tags": ["классика", "мистика"], "cover": {"type": "твёрдая", "color": "чёрная"}}' WHERE id = 1;
UPDATE books SET details = '{"publisher": "АСТ", "tags": ["фантастика", "классика"], "cover": {"type": "мягкая"}}' WHERE id = 3;
UPDATE books SET details = '{"publisher": "АСТ", "tags": ["фантастика"], "cover": {"type": "мягкая"}}' WHERE id = 4;
UPDATE books SET details = '{"publisher": "Питер", "translator": "Е. Матвеев", "tags": ["алгоритмы", "python"], "cover": {"type": "мягкая"}}' WHERE id = 7;
UPDATE books SET details = '{"publisher": "Русская редакция", "translator": "В. Вдовиченко", "tags": ["программирование", "классика"], "cover": {"type": "твёрдая"}}' WHERE id = 8;

Письмо и картотека

Друг прислал тебе письмо с рецептом. Чтобы найти, сколько класть соли, ты перечитываешь письмо с начала, каждый раз. Это тип json: PostgreSQL хранит текст как пришёл и разбирает его заново при каждом обращении.

А теперь ты переписал рецепт в картотеку: карточка «соль», карточка «сахар», карточки по алфавиту. Переписывать долго, зато нужное находится сразу. Это jsonb: документ разбирается один раз, при записи, и ложится в двоичном виде с оглавлением ключей.

Отсюда все особенности jsonb, которые видны снаружи.

Ключи выстраиваются в свой порядок. Не в том, в каком ты их написал, и даже не по алфавиту:

SELECT '{"publisher": "АСТ", "tags": ["классика"], "cover": {"type": "твёрдая"}, "id": 1}'::jsonb AS doc;

                                        doc
-----------------------------------------------------------------------------------
 {"id": 1, "tags": ["классика"], "cover": {"type": "твёрдая"}, "publisher": "АСТ"}
(1 row)

Сначала короткие ключи, потом длинные: так серверу быстрее искать ключ в оглавлении. Повторный ключ схлопывается до последнего значения, это мы видели в 21-м уроке. Вывод: порядок ключей в jsonb не значит ничего, и код, который на него полагается, сломается.

Пробелы пропадают, а числа остаются точными. Числа в jsonb хранятся как numeric из урока про числа, поэтому нули после запятой сохраняются, а экспонента превращается в обычное число:

SELECT '{"price":   690.50, "rating": 4.70, "big": 1e3}'::jsonb AS doc;

                      doc
------------------------------------------------
 {"big": 1000, "price": 690.50, "rating": 4.70}
(1 row)

Двоичный не значит маленький. Функция pg_column_size показывает, сколько байт занимает значение:

SELECT pg_column_size('{"publisher": "АСТ", "tags": ["классика", "мистика"]}'::json)  AS json_bytes,
       pg_column_size('{"publisher": "АСТ", "tags": ["классика", "мистика"]}'::jsonb) AS jsonb_bytes;

 json_bytes | jsonb_bytes
------------+-------------
         75 |          88
(1 row)

Оглавление ключей и смещений стоит места, поэтому маленький документ в jsonb даже толще. Ты платишь байтами за скорость чтения.

Большой документ и TOAST

Строка таблицы живёт на странице в 8 КБ, про страницы рассказано в уроке теории. Документ на десятки килобайт туда не влезет. Для таких значений у PostgreSQL есть TOAST: длинное значение сжимается, а если и после этого не помещается, уезжает в отдельную служебную таблицу. В строке остаётся ссылка.

CREATE TABLE notes (id integer PRIMARY KEY, doc jsonb);
INSERT INTO notes VALUES (1, jsonb_build_object('text', repeat('Рукописи не горят. ', 2000)));

SELECT octet_length(doc::text) AS as_text, pg_column_size(doc) AS on_disk FROM notes;

 as_text | on_disk
---------+---------
   68012 |     843
(1 row)

68 КБ текста легли в 843 байта, потому что одна фраза повторяется две тысячи раз и отлично сжимается. Обычный документ сожмётся скромнее, но правило то же. И у этого правила есть цена: документ целиком читается, распаковывается и записывается заново, даже если ты поменял в нём один ключ.

Проверим на «Мастере и Маргарите». Адрес строки ctid из урока про индексы и VACUUM меняется после правки одного ключа:

SELECT ctid FROM books WHERE id = 1;
UPDATE books SET details = jsonb_set(details, '{cover,color}', '"красная"') WHERE id = 1;
SELECT ctid, details -> 'cover' AS cover FROM books WHERE id = 1;

  ctid
--------
 (0,10)
(1 row)

  ctid  |                  cover
--------+-----------------------------------------
 (0,15) | {"type": "твёрдая", "color": "красная"}
(1 row)

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