Глава 3. JSON в таблицах

Главы

1. Введение в документы
2. Базовые возможности JSON
3. JSON в таблицах
4. Индексирование JSON
5. Ссылки и ограничения в документах
6. Язык путей JSONPath
7. Отчеты и функции
8. Функции на языке Python
9. Версионирование и архивация документов
10. Релевантный поиск

Содержание

• Главы
• Определение сущности
• Дизайн таблицы
• Тосты, сжатие и хранение
• Множественность и статистика
• Генерация и вставка
• Псевдослучайные значения
• Чтение
• Вставка
• Обновление
• Вставка с обновлением (UPSERT)
• Удаление

До сих пор мы работали с документами, которые не превышали нескольких строк. На практике документы объемны и занимают сотни килобайт. В этой главе мы обсудим, как хранить JSON в таблицах, читать его, изменять и удалять – полностью и частично.

Для начала обсудим задачи, которые выполним ниже по тексту. План таков:

1. мы определим сущность, с которой будем работать до конца книги. Ее структура повторяет аналог из проекта, в котором работал автор. Сущность содержит разные типы данных и вложенные поля. На ней мы опробуем техники, которые уже рассмотрели, и многое другое.

2. Подготовим таблицу для документов. Здесь же мы рассмотрим сжатие типов (compression) и понятие множественности (cardinality).

3. Запишем в базу миллион документов. Это будут не клоны одной и той же записи, а псевдослучайные данные. Пороги случайности мы определим сами.

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

Цель – получить миллион разнообразных документов и опробовать на них все, что мы изучили.

Определение сущности

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

{
    "id": "3a80e2c2-b720-4a08-97c0-7b4c9839853c",
    "status": "active",
    "created_at": "2025-12-24T07:19:47.496191Z",
    "created_by": {
        "id": "cb1aeac0-247f-44ff-94bf-bc006ea401ad",
        "email": "ivanova.m@acme.com",
        "name": "Maria Ivanova"
    },
    "application_id": 623135,
    "organization": {
        "id": "a61c10ec-c504-4f91-95ce-b880dcaf31b3",
        "code": 156234,
        "short_name": "Super Corp"
    },
    "comment": "A brief note about this application",
    "amounts": [
        {
            "amount": 110000000,
            "currency": "USD",
            "period": {"y": 10, "m": 3, "w": 0, "d": 0}
        },
        {
            "amount": 100000000,
            "currency": "EUR",
            "period": {"y": 9, "m": 6, "w": 0, "d": 0}
        }
    ],
    "departments": [
        {
            "id": "224899ae-fac7-4555-8a94-f41f3c9ed634",
            "code": "risk",
            "name": "Risk Department",
            "users": [
                {
                    "id": "3f798e66-43a3-4d78-b5f9-3c3740b6676b",
                    "email": "mironov.p@acme.com",
                    "name": "Petr Mironov"
                },
                {
                    "id": "9ecef728-a3bb-4a1d-85d7-51dff2401c0b",
                    "email": "smith.k@acme.com",
                    "name": "Karol Smith"
                }
            ]
        },
        {
            "id": "7513991b-9951-4bce-ad4a-470ff2d3febf",
            "code": "analytics",
            "name": "Analytics Department",
            "users": [
                {
                    "id": "361a08a5-3991-4b2a-9d49-8e4cf77b876b",
                    "email": "volkov.l@acme.com",
                    "name": "Leonid Volkov"
                },
                {
                    "id": "11da81b9-b125-4a7e-9965-958cc5593a3e",
                    "email": "jackson.j@acme.com",
                    "name": "John Jackson"
                }
            ]
        }
    ],
    "journal": [
        {
            "event": "created",
            "datetime": "2025-12-20T09:31:46",
            "user_id": "9ecef728-a3bb-4a1d-85d7-51dff2401c0b"
        },
        {
            "event": "reviewed",
            "datetime": "2025-12-21T11:33:55Z",
            "user_id": "11da81b9-b125-4a7e-9965-958cc5593a3e"
        },
        {
            "event": "approved",
            "datetime": "2025-12-22T12:43:00Z",
            "user_id": "3f798e66-43a3-4d78-b5f9-3c3740b6676b"
        }
    ]
}

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

Поле amounts описывает суммы и сроки заявки. Это список, потому что заявка подается не на один, а множество критериев. Банк может удовлетворить их все, ни один или выборочно. Каждый критерий – это словарь с валютой, суммой в центах и сроком. Срок задан числом лет, месяцев, недель и дней. Позже мы рассмотрим, как привести эту структуру к дате.

Поле departments содержит пользователей, которые работали над заявкой. Оно состоит из двух уровней: сперва отделы, а в них – сотрудники. Вложенность влечет трудности, но мы выбрали ее специально. Во-первых, не всегда дизайн документа зависит от нас. При попытке изменить его окажется, что слишком много потребителей опираются на такую структуру. Во-вторых, на примере вложенных данных мы рассмотрим полезные техники.

Кроме уникального идентификатора (поле id) документ имеет внутренний числовой номер: application_id. В бухгалтерии и других отделах принята своя система нумерации, например уникальность в рамках года. Этот номер пригодится нам для экспериментов.

Поле journal хранит историю заявки. Это список с датой события, статусом и ссылкой на пользователя.

Заявка повторяет аналог, с которым работал автор — разве что боевой документ гораздо больше. Кроме указанные полей, он хранил порядок гашения кредита, ссылку на третью сторону, которая берет на себя риски, структуру этих рисков, цели кредита и так далее. В режиме pretty print заявка занимала в среднем двадцать экранов. Для экономии места мы взяли ее подмножество.

Можно ли хранить заявки в реляционной таблице? Да, технически это возможно. Простые поля (номер, статус и другие) станут колонками. Поля amounts, departments и journal – коллекции, поэтому вынесем их в связанные таблицы. Данные amounts хранились бы так:

create table application_amounts(
    application_id uuid references applications(id),
    amount integer,
    currency text,
    year integer,
    month integer,
    week integer,
    day integer
);

А это – таблица application_journal для истории документа:

create table application_journal(
    application_id uuid references applications(id),
    event text,
    datetime timestamptz,
    user_id uuid references users(id)
);

На практике таблиц будет больше, а некоторые требуют двух уровней и таблиц-мостов. Осложняет ситуацию то, что заявка считается монолитной: сервисы принимают и отдают их целиком, а не частями. Попытка “нарезать” заявку на таблицы застопорится по разным причинам, в том числе не только техническим. Если каждый участник смотрит на документ как на что-то неделимое, проще следовать этому принципу на уровне базы данных.

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

Дизайн таблицы

Для хранения заявок понадобится таблица. Ее основные поля – первичный ключ, дата создания и последнего обновления, возможно, какие-то метаданные (автор, версия). Колонка doc содержит сам документ. Вот как выглядит таблица applications на языке SQL:

create extension if not exists "uuid-ossp";

create table applications (
    id uuid primary key default uuid_generate_v4(),
    doc jsonb compression lz4 not null,
    created_at timestamptz not null default current_timestamp,
    updated_at timestamptz
);

Расширение uuid-ossp необходимо для функции uuid_generate_v4. Оно относится к доверенным и не требует привилегий администратора. Имя “uuid-ossp” взято в кавычки, потому что содержит дефис.

У ключа задано свойство default. Если мы не указали ключ в приложении, его произведет база. Мы используем алгоритм UUID версии 4, который полностью случаен, то есть не зависит от настроек и окружения.

То, что идентификатор полностью случаен, влияет доступ к данным. Порядок ключей в индексе не совпадет с порядком записей в таблице. Записи A и B, созданные одна за другой, могут оказаться в разных страницах индекса. Если бы ключом было нарастающее число, записи были бы рядом.

Более удачный вариант – использовать UUID версии 7. Он основан на времени: первые 48 бит содержат время Unix в миллисекундах. Такие идентификаторы упорядочены, из-за чего упрощается поиск в индексе. Из них легко получить временную метку и наоборот – получить идентификатор на заданное время. Если вы работаете с Postgres 18 и старше, замените функцию uuid_generate_v4 на uuidv7 в свойствах ключа.

Чтобы не отклоняться от темы, мы не будем рассматривать UUID 7 во всех деталях. Предлагаем читателю статью с подробным анализом и замерами быстродействия.

При работе с UUID – неважно какой версии – придерживайтесь правила. Должно быть соглашение о том, кто производит ключи: база или приложение. Не допускайте ситуации, когда приложение использует UUID 4, а база – UUID 7 или наоборот. Это вызовет путаницу, а смена ключей – дорогая операция.

Полю created_at задано умолчание current_timestamp. Это так называемая псевдопеременная: читая ее раз за разом, мы получим разные значения. Поле updated_at по умолчанию пусто. Оно обновляется при изменении данных: в запросах UPDATE и INSERT ON CONFLICT.

Поле doc с типом jsonb содержит документ. Напомним, что тип jsonb производительней json, поэтому мы предпочли первый вариант. Поле не пустое (not null), потому что пустой документ не имеет смысла. Также обратите внимание на директиву compression – сжатие. В следующем разделе мы обсудим сжатие документов и их хранение на диске.

Тосты, сжатие и хранение

Типы Postgres условно делятся на две категории: фиксированной длины и переменной. Например, int4 – фиксированный тип: в нем строго четыре байта, неважно единица это или миллион. То же самое относится к другим числовым типам (кроме numeric), датам и времени – их размеры постоянны.

Другое дело типы text, байтовый массив (bytea) или json(b) – какая у них длина? Документ может занимать один символ: например, ‘1’ – это корректный JSON. Возможна и другая крайность: JSON на сотню экранов. Определить длину заранее нельзя, потому что либо кому-то ее не хватит, либо мы будем хранить пустоту.

Типы с переменной длиной усложняют хранение данных. Предположим, некоторая таблица содержит поля int4, uuid и timestamptz. В этом случае длина строки постоянна. Она вычисляется как сумма длин каждого типа (4 + 16 + 8) плюс некоторые служебные байты (заголовок и выравнивание). Если же добавить к полям jsonb, ситуация станет неоднозначной.

Postgres применяет две техники, чтобы справиться с типами переменной длины. Первая называется TOAST: The Oversized-Attribute Storage Technique, метод хранения больших атрибутов. Аббревиатура TOAST отсылает нас к тостам – кусочкам хлеба одинаковой толщины, на которые их режет машина.

Принцип Postgres аналогичен: если данные превышают определенный порог, они режутся на равные части. Далее они помещаются в служебную TOAST-таблицу, скрытую от обывателя. Таблица хранит уникальный ключ документа (chunk_id), номер куска (chunk_seq) и байтовый массив (chunk_data). В исходной таблице вместо значения записан ключ документа. Схематично это выглядит так:

   ┌─────────┬───────────┬──────────────────────────────────────────────┐
   │   id    │created_at │                     doc                      │
   ├─────────┼───────────┼──────────────────────────────────────────────┤
   │ <uuid>  │2025-10-31 │                  chunk_id:1                  │──┐
   └─────────┴───────────┴──────────────────────────────────────────────┘  │
                                                                           │
┌──────────────────────────────────────────────────────────────────────────┘
│  ┌─────────┬───────────┬──────────────────────────────────────────────┐
│  │chunk_id │ chunk_seq │                  chunk_data                  │
│  ├─────────┼───────────┼──────────────────────────────────────────────┤
└─▶│        1│          1│{"users": [{"id": 1, "name": "Ivan", "age":   │
   ├─────────┼───────────┼──────────────────────────────────────────────┤
   │        1│          2│42, "items": [1001, 1002, 1003]}, {"id": 1,   │
   ├─────────┼───────────┼──────────────────────────────────────────────┤
   │        1│          3│"name": "Juan", "age": 33, "items": [2001,    │
   ├─────────┼───────────┼──────────────────────────────────────────────┤
   │        1│          4│ 3005, 5999]}]}                               │
   └─────────┴───────────┴──────────────────────────────────────────────┘

Документ с кодом 1 имеет четыре фрагмента. Другие документы могут умещаться в один фрагмент, сотни или тысячи. Таким образом данные растут по вертикали, а не горизонтали. Как мы помним из первой главы, вертикальный рост удобней в плане контроля.

Когда мы читаем основную таблицу, происходит обратное: “нарезанный” документ с кодом 1 собирается из фрагментов в порядке нарастания chunk_seq.

Кроме TOAST, Postgres применяет сжатие. Данные переменной длины, если содержат текст, хорошо сжимаются: выигрыш иной раз достигает порядка. Символы JSON попадают в узкий диапазон: это заглавные и строчные буквы, цифры и немного спецсимволов – в сумме не более сотни. Поэтому JSON – хороший кандидат на сжатие в отличие, скажем, от изображений или архивов, которые уже сжаты.

Чтобы избежать нарезки тостов, Postgres предварительно сжимает данные. Если результат не превышает определенного порога, он хранится в основной таблице. При чтении не происходит скрытого обращения к TOAST-таблице, что ускоряет доступ. То же самое относится к записи данных: она занимает меньше времени, если документ ужался до порога, выше которого он отправляется в TOAST.

По умолчанию Postgres использует сжатие pglz; в поздних версиях появился улучшенный алгоритм lz4. Он сжимает данные не так плотно как pglz, но работает гораздо быстрее. Скорость оказывается решающим фактором: при чтении и записи нужно как можно быстрее сжать или разжать документ.

В поле doc мы задали алгоритм lz4 явно. Если этого не сделать, будет использоваться pglz. Алгоритм по умолчанию можно задать в настройках сервера. Опция называется default_toast_compression и выглядит так:

SET default_toast_compression=lz4;

Алгоритм сжатия можно сменить после создания таблицы командой:

alter table applications alter column doc set compression lz4;

Ее выполнение, однако, не приведет к смене значений на диске. Изменения вступят в силу постепенно по мере обновления документов. Вот как принудительно обновить все записи:

vacuum full table;

Смена алгоритма сжатия – дорогая операция, особенно для больших документов. Vacuum full блокирует таблицу, поэтому лучше определиться со сжатием заранее.

В совокупности тосты и сжатие определяют то, что называется типом хранения – storage type. Всего их четыре:

1. PLAIN: не допускает ни тостов, ни сжатия. Назначен по умолчанию числам, датам и другим фиксированным типам.

2. EXTENDED: разрешает то и другое. Сперва данные сжимаются, и если результат не уместился в порог, то уходит в TOAST-хранилище. Большинство типов переменной длины, включая json и jsonb, используют именно это подход.

3. EXTERNAL: допускает тосты, но не сжатие. Такое хранение задают столбцам, данные в которых практически не сжимаются. Например, если поле bytea хранит изображение jpeg, байты уже максимально сжаты, и повторное сжатие станет лишь тратой времени.

4. MAIN: противоположность EXTERNAL. Разрешено сжатие, но не тосты. Служит затем, чтобы удержать документ в главной таблице любой ценой. Лишь когда это физически невозможно, данные сбрасываются в тосты.

По умолчанию типам json и jsonb назначено хранение EXTENDED, то есть со сжатием и тостами. При записи документа Postgres сначала сожмет его указанным алгоритмом. Если сжатый результат меньше порога, он окажется в основной таблице (applications). В противном случае Postgres отправит его в TOAST-хранилище в виде фрагментов.

Какой именно размер этого порога? По умолчанию он равен одной четверти от размера страницы (блока). Этот размер задан во время компиляции и не может быть изменен позже. По умолчанию он равен восьми килобайтам; исключение возможно, только если вы собрали Postgres из исходного кода с параметром --with-blocksize=N, например 32.

Из этого вывод: значение json(b) хранится в главной таблице, пока его сжатая версия не превысит два килобайта. Если предположить, что lz4 сжимает JSON в 10-15 раз (это легко проверить утилитой lz4), то исходный документ не должен превышать 20-30 килобайт.

Порог в одну четверть можно сменить командой alter table:

alter table applications set (toast_tuple_target = 8000);

Значение toast_tuple_target подбирается вручную. Очевидно, оно не может превышать размер страницы (8192 байта). Мы задали ему 8000 байтов с тем расчетом, чтобы места хватило на фиксированные столбцы (id, created_at и другие), а также служебные данные (заголовок страницы и выравнивание).

С новым порогом документ отправится в TOAST только если его сжатая версия превысит 8000 байт. Это значит, что исходный объем лежит в диапазоне от 80 до 120 килобайт – около 50 страниц художественного текста.

Колонке doc можно задать иной тип хранения:

alter table applications alter column doc set storage main;

Напомним, хранение main запрещает TOAST и позволяет только сжатие. Postgres воспользуется TOAST лишь том в случае, если сжатый документ физически не умещается в страницу. После смены хранилища дайте команду vacuum full, чтобы изменения вступили в силу.

Меняйте хранение лишь в том случае, если понимаете, чем вас не устраивает текущий. Выше мы упоминали один из вариантов: колонка bytea содержит картинку или pdf, которые уже сжаты. По умолчанию bytea хранится как EXTENDED, то есть разрешает сжатие и тосты. Имеет смысл выставить ей тип EXTERNAL, чтобы исключить сжатие. При этом даже если вы уверены в том, что делаете, проведите замеры до и после.

Теперь когда вы знакомы с техническими деталями, подумаем, какую пользу они несут.

Механизм TOAST решает проблему роста полей с неограниченной длиной. К ним относятся обычный text, json(b), битовые строки (bit), числовой тип numeric. Последний, если не указать точность, хранит до тысячи знаков после запятой, что сказывается на хранении. TOAST выносит длинные значения в отдельную таблицу, где они хранятся кусками и не ограничены в росте.

TOAST следует принципу “живи и дай жить другим”. Он важен в случаях, когда в таблице несколько полей переменной длины, что случается часто. Например, условные поля full_name, nick_name, email, site_url, bio чаще всего представлены текстом (тип text). TOAST следит за тем, чтобы ни одно из них не вышло за адекватный предел. Проверяйте поля перед вставкой, чтобы какой-нибудь шутник не передал в bio роман Льва Толстого. Однако TOAST гарантирует, что даже такой случай не вызовет сбоя.

Если документ находится в TOAST-хранилище, то основная таблица хранит только ссылку на него. Ссылка ничтожно мала: всего несколько байтов. Из-за этого в каждом блоке умещается больше строк. А это значит, что выше шанс их повторного использования. Например, мы прочитали страницу ради товара А, и на той же странице были товары B и C. Если понадобится один их них, они будут взяты из оперативной памяти, а не с диска

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

1. Бесплатная книга Егора Рогова “PostgreSQL 17 изнутри”. Одна из глав рассматривает TOAST во всех подробностях.

2. Статья “PostgreSQL JSONB Size Limits to Prevent TOAST Slicing” в блоге dev.to об особенностях TOAST и JSON. В числе прочего рассматривается сборка Postgres с повышенным размером страницы.

3. “Внутренности PostgreSQL: механизм TOAST” – статья Александра Алексеева, известного под ником afiskon.

4. “Optimizing PostgreSQL Performance & Compression: pglz vs. LZ4” – сравнение алгоритмов сжатия pglz и lz4 с графиками.

На этом мы закончим тему сжатия и TOAST. Возвращаемся к таблице applications, которую спроектировали – пора наполнить ее данными.

Множественность и статистика

Теперь когда таблица готова, подумаем, откуда взять данные. Напомним, мы бы хотели миллион заявок, чтобы опробовать на них все техники. Как этот миллион получить и какими свойствами он должен обладать – об этом мы и поговорим.

Первое свойство, которым должны обладать данные – это разнообразие. Если вставить одну и ту же запись много раз, для них не сработают индексы. Для каждого индекса Postgres ведет гистограмму значений и определяет те, что встречаются часто. Когда мы фильтруем по частому значению, индекс не используется, потому что требует больше усилий, чем линейный обход.

Пример – распределение пользователей по городам. Если это всероссийский сайт, то распределение совпадает с населением городов: с большим отрывом идут Москва и Петербург, а остаток делят между собой малые города. При этом обе столицы попадут в список частых значений в системной таблице Postgres. Когда мы ищем в базе москвичей (where city = 'Moscow'), индекс не сработает (их доля 50%). А для читинцев (where city = 'Chita') индекс будет взят (их доля 2%).

То же самое касается статусов задач, заказов и так далее. Если указать status = 'active' всем сущностям без исключения, мы не сможем искать завершенные иля отмененные. Индексы и попадание в них – ключевой вопрос при работе с базой, поэтому бездумное копирование не подойдет.

Второе свойство – данные должны быть разнообразны в разумных пределах. Это защита от другой крайности: делать каждое поле уникальным при помощи счетчика. Скажем, записывать в поле status строки status_100, status_101 и так далее. Эти данные тоже не отражают реальность: логично, что набор статусов не превышает десяти значений. Поиск по условию

where status = 'status_12345'

сработает молниеносно: в таблице одна такая запись. Однако это не гарантирует такое же быстродействие для status = 'active' в боевом запуске.

Кроме технических моментов есть и эстетический: сгенерированные записи не должны портить интерфейс. Имена людей должны хотя бы отдаленно напоминать имена, а не быть строками вида JFgP_U(#@5$. Даты – быть в адекватном диапазоне в несколько лет, а не столетий. Если выпадающий список расчитан на десять элементов, в нем не должно быть их тысяча.

Характеристика, которая отвечает за разнообразие значений, называется cardinality, что в переводе – множественность. Низкая множественность означает, что при добавлении записей число уникальных значений растет медленно. У логического флага этот показатель равен двум: истина и ложь. Сколько бы записей мы ни добавили, мы не получим третьего значения, отличного от истины и лжи. У статуса задачи множественность обычно не превышает десяти. Людские имена более разнообразны, но тоже до определенного порога. Адреса электронной почты настолько разнообразны, что порой служат уникальным признаком пользователя.

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

Уделите время сбору статистистики. Выпишите поля, по которым требуется поиск и сортировка. Запросите информацию о том, сколько уникальных значений у этого поля, в идеале – распределение: значение A – столько-то процентов, значение B – столько-то и так далее.

Сбор этих сведений зависит от того, где хранятся данные: в реляционной базе, файлах или очереди сообщений. Полагаем, вы получили данные в файле CSV и вставили их в таблицу Postgres. Все готово к тому, чтобы собрать статистику.

Предположим, мы исследуем поле status таблицы tasks. В ней следующие данные:

create table tasks (
    id integer primary key,
    status text
);

insert into tasks values
    (1, 'active'),
    (2, 'active'),
    (3, 'pending'),
    (4, 'approved'),
    (5, 'pending'),
    (6, 'deleted'),
    (7, 'approved'),
    (8, 'approved'),
    (9, 'active');

Чтобы узнать общее число записей в таблице, выполним запрос с функцией count:

select count(*) from tasks;
-- 9

Следующий запрос покажет, сколько всего уникальных статусов:

select count(distinct status) from tasks;
-- 4

Сами статусы:

select distinct status from tasks;

┌──────────┐
│  status  │
├──────────┤
│ active   │
│ approved │
│ pending  │
│ deleted  │
└──────────┘

Чтобы найти распределение статусов (долю каждого относительно общего числа строк), разделим выражение count(distinct id) на общее число строк. Это значение мы получили выше, и оно равно 9. Деление должно быть в рациональных числах (не целых), поэтому девятка записана с нулем в дробной части (тип float4):

select
  status,
  count(distinct id) / 9.0
from tasks
group by status

┌──────────┬────────┐
│  status  │ ratio  │
├──────────┼────────┤
│ active   │  00.33 │
│ approved │  00.33 │
│ pending  │  00.22 │
│ deleted  │  00.11 │
└──────────┴────────┘

Таблица показывает, что 33 процента задач имеют статус active, столько же – approved. В ожидании находятся 22 процента задач, еще 11 удалены. Сумма долей должна быть равна единице, что в нашем случае верно.

Даже если мы имеем дело не с плоской таблицей, а документами, статистика полей вычисляется так же. Предположим, задача – это документ, и статус находится по пути doc #>> '{meta,status}'. В этом случае измените выражение статуса:

select distinct doc #>> '{meta,status}' as status from tasks;

При помощи SQL легко вычислить статистику каждого поля. Если данные лежат в хранилище, отличном от реляционной базы, перенесите их Postgres. В нем собрать статистику проще, чем в других системах. Текст ниже предполагает, что вы прошлись по ключевым полям и выяснили, сколько у каждого уникальных значений.

Генерация и вставка

Чтобы наполнить базу, обычно программисты пишут код на языках вроде Python или Java. Код подключается к серверу и вставляет документы в цикле. Каждый документ производится случайно по его абстрактному описанию – модели или схеме. Для каждого языка найдется библиотека, которая по описанию выдает случайный документ. Подобные библиотеки ищутся по словам “mock factory” или “model generator”.

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

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

При генерации силами Python мы передаем данные по сети. Миллион документов пакуются в бинарные сообщения и идут по сети от клиента к серверу. Документы весьма объемны, и вставка замедлится из-за сетевых расходов.

Одно из решений в том, чтобы записать документы в файл CSV и импортировать командой COPY. Файл можно сжать при помощи gzip и выложить на внутреннем ресурсе. Если коллегам потребуется множество документов, они импортируют файл вместо того, чтобы запускать скрипт. Однако CSV-файл не поможет, если требуется больше данных, а также другая логика их генерации.

Рассмотрим способ, лишенный недостатков выше. Это выражение INSERT INTO ... SELECT, которое вставляет записи в таблицу из некой выборки. При этом выборка не обращается к таблице: данные производятся на лету.

Для начала простой пример. Пусть имеется таблица задач под названием tasks:

create table tasks (
    id integer primary key,
    status text
);

Вот как вставить в нее девять пар (id, status). Выражение SELECT обращается к функции generate_series, которая производит набор записей. Каждая из них содержит число x от 1 до 9. Форма SELECT возвращает поля x и 'status_<x>', и позже они вставляются в tasks. Выполним запрос:

insert into tasks(id, status)
select
    x,
    format('status_%s', x)
from
    generate_series(1, 9) as seq(x);

Результат:

select * from tasks;

┌────┬──────────┐
│ id │  status  │
├────┼──────────┤
│  1 │ status_1 │
│  2 │ status_2 │
│  3 │ status_3 │
│  4 │ status_4 │
│  5 │ status_5 │
│  6 │ status_6 │
│  7 │ status_7 │
│  8 │ status_8 │
│  9 │ status_9 │
└────┴──────────┘

Плюсы этого способа очевидны: не нужен язык программирования и библиотеки к нему. Достаточно минимального клиента для Postgres, в том числе psql. Записи, что помещаются в таблицу, генерируются на сервере – мы не передаем их по сети. Вставка исключает сеть, и это занимает меньше времени, порой на порядок.

Запрос на SQL удобней, чем файл CSV. SQL хранится в репозитории, его снабжают комментариями: какой параметр за что отвечает. Любой желающий доработает его под свои требования. Например, кому-то требуется больше полей или другая логика их заполнения. Коллега, который занимается нагрузочным тестированием, увеличит вставку до пятидесяти миллионов, чтобы проверить, как база справляется с объемами. В случае с SQL это делается минимальными усилиями.

Подводя итог: SQL для генерации данных крайне полезен. Пишите такие запросы и делитесь с коллегами.

Подумаем, как составить запрос, чтобы вместо плоских полей он вставлял документы. Это сложнее, потому что у документа много полей, и каждое зависит от x. Наивное решение в том, чтобы получить документ форматированием строки со множеством параметров %s. В примере ниже SELECT возвращает тройку: случайный идентификатор, результат форматирования с типом jsonb и текущую дату. Опробуем его на небольшом документе:

insert into applications (id, doc, created_at)
select
    uuid_generate_v4(),
    format($${
        "id": "%1$s",
        "status": "active",
        "application_id": %2$s,
        "created_by": {
            "id": "%3$s",
            "email": "user_%3$s@acme.com",
            "name": "Test User %1$s"
        }
    }$$, uuid_generate_v4(), x, uuid_generate_v4())::jsonb,
    now()
from
    generate_series(1, 9) as seq(x);

Результат:

select id, jsonb_pretty(doc), created_at
from applications limit 2;

┌─[ RECORD 1 ]─┬───────────────────────────────────────────────────────────────────────┐
│ id           │ 207971c8-9072-4ad1-a6e8-50dbafe30a5d                                  │
│ jsonb_pretty │ {                                                                    ↵│
│              │     "id": "b77c179c-7f2b-4706-a18d-612061c3b30b",                    ↵│
│              │     "status": "active",                                              ↵│
│              │     "created_by": {                                                  ↵│
│              │         "id": "831c894e-b702-4cbe-b583-8c577ec0f9be",                ↵│
│              │         "name": "Test User b77c179c-7f2b-4706-a18d-612061c3b30b",    ↵│
│              │         "email": "user_831c894e-b702-4cbe-b583-8c577ec0f9be@acme.com"↵│
│              │     },                                                               ↵│
│              │     "application_id": 1                                              ↵│
│              │ }                                                                     │
│ created_at   │ 2025-12-25 12:27:36.158705+03                                         │
├─[ RECORD 2 ]─┼───────────────────────────────────────────────────────────────────────┤
│ id           │ ab825d3c-4ef6-4fad-91db-76c278432597                                  │
│ jsonb_pretty │ {                                                                    ↵│
│              │     "id": "6f24b2df-6e6e-49a8-9464-b59fc4d7b8c4",                    ↵│
│              │     "status": "active",                                              ↵│
│              │     "created_by": {                                                  ↵│
│              │         "id": "bfd65cea-ecc0-4106-9552-b4187ef93037",                ↵│
│              │         "name": "Test User 6f24b2df-6e6e-49a8-9464-b59fc4d7b8c4",    ↵│
│              │         "email": "user_bfd65cea-ecc0-4106-9552-b4187ef93037@acme.com"↵│
│              │     },                                                               ↵│
│              │     "application_id": 2                                              ↵│
│              │ }                                                                     │
│ created_at   │ 2025-12-25 12:27:36.158705+03                                         │
└──────────────┴───────────────────────────────────────────────────────────────────────┘

Способ рабочий, но c недостатком. Форматирование выглядит шумно, и после двух-трех параметров выходит из-под контроля. Представьте шаблон с сотней параметров %s! Поддерживать такой код невозможно.

Нам помогут функции jsonb_build_array и jsonb_build_object, которые мы изучили в прошлой главе. Любой JSON-документ можно выразить их комбинацией. Перепишем запрос так:

delete from applications;

insert into applications (id, doc, created_at)
select
    uuid_generate_v4(),
    jsonb_build_object(
        'id', uuid_generate_v4(),
        'status', 'active',
        'application_id', x,
        'created_by', jsonb_build_object(
            'id', uuid_generate_v4(),
            'email', format('user_%s@acme.com', x),
            'name', format('Test User %s', x)
        )
    ),
    now()
from
    generate_series(1, 9) as seq(x);

Без огромного шаблона код читается лучше. Отсюда решение: документ должен быть каскадом функций jsonb_..., где значения рассчитываются на основе x.

Читатель возразит: наша заявка велика и занимает несколько экранов. Неужели придется вручную переписывать ее с JSON на SQL? К счастью, можно немного схитрить – сгенерировать SQL автозаменой текста.

Предположим, в файле application.json находится JSON-документ, неважно насколько большой. Примените к нему следующие преобразования sed:

cat application.json | sed \
  -e "s/\"/'/g" -e 's/: /, /g' \
  -e 's/{/jsonb_build_object(/g' \
  -e 's/}/\)/g' \
  -e 's/\[/jsonb_build_array(/g' \
  -e 's/\]/)/g'

Они довольно просты: открывающая фигурная скобка { меняется на вызов jsonb_build_object, открывающая квадратная – на jsonb_build_object. Их закрывающие пары становятся круглыми. Двойные кавычки меняются на одинарные, двоеточия – на запятые. В результате получим код на SQL, которой вернет исходный JSON.

Предположим, в файле следующий документ:

{
  "id": "123",
  "status": "active",
  "tags": ["risk", "acme", "sample"],
  "created_by": {
    "id": "789",
    "email": "test@acme.com",
    "name": "Test User 1"
  }
}

Результат автозамены:

jsonb_build_object(
  'id', '123',
  'status', 'active',
  'tags', jsonb_build_array(
    'risk',
    'acme',
    'sample'
  ),
  'created_by', jsonb_build_object(
    'id', '789',
    'email', 'test@acme.com',
    'name', 'Test User 1'
  )
)

Предварите этот код оператором SELECT и выполните в psql – база вернет исходный документ из файла.

Запомнить параметры sed трудно, поэтому поместите их в шелл-скрипт или Makefile, например такой:

# Makefile
file ?= $(error Please specify the file=... argument)

generate-sql:
    @cat ${file} | jq | sed \
        -e "s/\"/'/g" -e 's/: /, /g' \
        -e 's/{/jsonb_build_object(/g' \
        -e 's/}/\)/g' \
        -e 's/\[/jsonb_build_array(/g' \
        -e 's/\]/)/g'

Пример вызова:

make generate-sql file=application.json

Автозамена sed сохраняет отступы и переносы строк. Желательно, чтобы исходный JSON был форматирован, потому что иначе с результатом трудно работать. Впрочем, и эту работу можно поручить утилите: перед тем как передать файл в sed, пропустите его через jq – без параметров она возвращает форматированный документ.

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

Итак, вот что мы получим, когда применим команды cat, jq и sed к файлу application.json:

jsonb_build_object(
    'id', '3a80e2c2-b720-4a08-97c0-7b4c9839853c',
    'status', 'active',
    'created_at', '2025-12-24T07:19:47.496191Z',
    'created_by', jsonb_build_object(
        'id', 'cb1aeac0-247f-44ff-94bf-bc006ea401ad',
        'email', 'ivanova.m@acme.com',
        'name', 'Maria Ivanova'
    ),
    'application_id', 623135,
    'organization', jsonb_build_object(
        'id', 'a61c10ec-c504-4f91-95ce-b880dcaf31b3',
        'code', 156234,
        'short_name', 'Super Corp'
    ),
    'comment', 'A brief note about this application',
    'amounts', jsonb_build_array(
        jsonb_build_object(
            'amount', 110000000,
            'currency', 'USD',
            'period', jsonb_build_object('y', 10, 'm', 3, 'w', 0, 'd', 0)
        ),
        jsonb_build_object(
            'amount', 100000000,
            'currency', 'EUR',
            'period', jsonb_build_object('y', 9, 'm', 6, 'w', 0, 'd', 0)
        )
    ),
    'departments', jsonb_build_array(
        jsonb_build_object(
            'id', '224899ae-fac7-4555-8a94-f41f3c9ed634',
            'code', 'risk',
            'name', 'Risk Department',
            'users', jsonb_build_array(
                jsonb_build_object(
                    'id', '3f798e66-43a3-4d78-b5f9-3c3740b6676b',
                    'email', 'mironov.p@acme.com',
                    'name', 'Petr Mironov'
                ),
                jsonb_build_object(
                    'id', '9ecef728-a3bb-4a1d-85d7-51dff2401c0b',
                    'email', 'smith.k@acme.com',
                    'name', 'Karol Smith'
                )
            )
        ),
        jsonb_build_object(
            'id', '7513991b-9951-4bce-ad4a-470ff2d3febf',
            'code', 'analytics',
            'name', 'Analytics Department',
            'users', jsonb_build_array(
                jsonb_build_object(
                    'id', '361a08a5-3991-4b2a-9d49-8e4cf77b876b',
                    'email', 'volkov.l@acme.com',
                    'name', 'Leonid Volkov'
                ),
                jsonb_build_object(
                    'id', '11da81b9-b125-4a7e-9965-958cc5593a3e',
                    'email', 'jackson.j@acme.com',
                    'name', 'John Jackson'
                )
            )
        )
    ),
    'journal', jsonb_build_array(
        jsonb_build_object(
            'event', 'created',
            'datetime', '2025-12-20T09:31:46',
            'user_id', '9ecef728-a3bb-4a1d-85d7-51dff2401c0b'
        ),
        jsonb_build_object(
            'event', 'reviewed',
            'datetime', '2025-12-21T11:33:55Z',
            'user_id', '11da81b9-b125-4a7e-9965-958cc5593a3e'
        ),
        jsonb_build_object(
            'event', 'approved',
            'datetime', '2025-12-22T12:43:00Z',
            'user_id', '3f798e66-43a3-4d78-b5f9-3c3740b6676b'
        )
    )
)

Скопируйте вывод, поместите его в выражение INSERT и выполните:

insert into applications (id, doc, created_at)
select
    gen_uuid(x),
    jsonb_build_object(...),
    now()
from
    generate_series(1, 9) as seq(x);

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

Псевдослучайные значения

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

Случайная дата в прошлом

(now() - interval '1 day' * random() * 365)

Вычитает случайное число дней из текущей даты. Число дней не превышает 365 (одного года), чтобы результаты были не слишком разрозненны.

Случайный элемент массива

((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)])

Выбирает случайный элемент массива. Число 4 выше означает его длину.

Остаток от деления

(format('user_%s', (x % 1000) + 1))

Чтобы число не превышало порога, используют остаток от деления на сам порог. Каким бы ни был x, остаток от деление будет в диапазоне от 0 до 999. Чтобы не допустить нуля, к результату прибавляют единицу. Выражение выше варьируется от user_1 до user_1000, неважно какое значение содержит x.

Случайное целое от 1 до N

(ceil(random() * 10))

Это выражение вернет число от 1 до 10 включительно.

Уникальный идентификатор для числа

(to_char(123567, 'FM00000000-0000-0000-0000-000000000000')::uuid)

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

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

select (to_char(123567, 'FM00000000-0000-0000-0000-000000000000')::uuid) as uuid;

┌──────────────────────────────────────┐
│                 uuid                 │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000123567 │
└──────────────────────────────────────┘

Символы FM нужны, чтобы отбросить лидирующий пробел. По умолчанию к неотрицательным числам добавляется пробел, чтобы выровнять их с отрицательными как в примере ниже:

select * from (values
    (to_char(1, '00.00')),
    (to_char(-1, '00.00')))
as vals(x);

┌─────────┐
│    x    │
├─────────┤
│    1.00 │
│   -1.00 │
└─────────┘

Первое значение x содержит пробел перед “1”, чтобы уравновесить минус. Тег FM отключает его, потому что иначе приведение типа ::uuid не сработает.

Генерация идентификатора по числу нужна часто, поэтому вынесем ее в функцию:

create or replace function gen_uuid(x integer)
returns uuid
language sql immutable strict parallel safe
return to_char(x, 'FM00000000-0000-0000-0000-000000000000')::uuid;

Не волнуйтесь, если функции вам непонятны: мы не будем ими злоупотреблять и подробно рассмотрим в будущей главе.

Обратите внимание на еще одну деталь. Выражения выше взяты в скобки, например:

(format('user_%s', x % 1000))

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

В особых случаях понадобится взвешенный генератор. Вспомним пример с городами: распределение пользователей по городам совпадает с долей населения. Если брать город случайно или остатком отделения на x, москвичей будет столько же, сколько жителей маленького городка.

Каждому городу можно задать вес: Москве – 65%, Петербургу – 20%, Воронежу – 10% и так далее. Функция ниже получает случайное число от нуля до единицы и пробегает набор условий when/then. Если порог проходит проверку, возвращается определенный город:

create or replace function generate_city() returns text
language plpgsql strict parallel safe
as $func$
declare
    r float4;
begin
    r := random();
    case
        when r < 0.65 then return 'Moscow';
        when r < 0.85 then return 'Saint Petersburg';
        when r < 0.95 then return 'Voronezh';
        when r < 0.99 then return 'Chita';
        else return 'Kopeysk';
    end case;
end;
$func$;

Запустим код ниже и убедимся, что для миллиона пользователей города распределились согласно весам:

create table users (id integer primary key, city text not null);

insert into users
select x, generate_city()
from generate_series(1, 1000000) as seq(x);

select to_char(count(id) / 1000000.0, '0.9999') as ratio, city
from users
group by city
order by 1 desc;

┌─────────┬──────────────────┐
│  ratio  │       city       │
├─────────┼──────────────────┤
│  0.6495 │ Moscow           │
│  0.2006 │ Saint Petersburg │
│  0.1000 │ Voronezh         │
│  0.0399 │ Chita            │
│  0.0100 │ Kopeysk          │
└─────────┴──────────────────┘

По аналогии пишут функции для статуса задачи (60% – активные, 30% – в работе, 10% – удаленные), категории товаров, возраста и других величин, распределение которых на что-то влияет.

Изучив техники выше, вернемся к нашему документу. Задача в том, чтобы пройтись по каскаду функций jsonb_... и заменить каждое поле на выражение: случайный элемент массива, случайная дата, форматирование с остатком и так далее. Это скучная работа, поэтому запаситесь терпением. Вот что получилось у автора:

insert into applications (id, doc, created_at)
select
    gen_uuid(x),
    jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    ),
    (now() - interval '1 day' * random() * 365)
from
    generate_series(1, 1000000) as seq(x);

Запустите скрипт, и в базе окажется миллион документов. Скорость зависит от многих факторов: оборудования, операционной системы, нагрузки других программ и так далее. На ноутбуке MacBook Pro M4 вставка заняла 20 секунд. Цифра растет линейно: для 50 миллионов понадобилось 1100 секунд, что примерно 18 минут.

Проверим число документов функцией count:

select count(*) from applications;

┌─[ RECORD 1 ]────┐
│ count │ 1000000 │
└───────┴─────────┘

Поздравляем! Сложный шаг позади, и мы переходим к следующему – работе с данными.

Чтение

Имея такой объем данных, опробуем на нем различные операции. Выберем случайный документ из середины:

select id, jsonb_pretty(doc) as doc
from applications
limit 1
offset 500000;

Частичный результат:

┌──────────────────────────────────────┬───────────────────────────────────────────────────────────────────┐
│                  id                  │                                doc                                │
├──────────────────────────────────────┼───────────────────────────────────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000500001 │ {                                                                ↵│
│                                      │     "id": "00000000-0000-0000-0000-000000500001",                ↵│
│                                      │     "status": "deleted",                                         ↵│
│                                      │     "amounts": [                                                 ↵│
│                                      │         {                                                        ↵│
│                                      │             "amount": 23223688,                                  ↵│
│                                      │             "period": {                                          ↵│
│                                      │                 "d": 7,                                          ↵│
│                                      │                 "m": 3,                                          ↵│
│                                      │                 "w": 3,                                          ↵│
│                                      │                 "y": 6                                           ↵│
│                                      │             },                                                   ↵│
│                                      │             "currency": "USD"                                    ↵│
│                                      │         },                                                       ↵│
│                                      │         {                                                        ↵│
│                                      │             "amount": 24467700,                                  ↵│
│                                      │             "period": {                                          ↵│
│                                      │                 "d": 6,                                          ↵│
│                                      │                 "m": 8,                                          ↵│
│                                      │                 "w": 7,                                          ↵│
│                                      │                 "y": 9                                           ↵│
│                                      │             },                                                   ↵│
│                                      │             "currency": "RUB"                                    ↵│
│                                      │         }                                                        ↵│
│                                      │     ],                                                           ↵│
│                                      │     "comment": "Comment number #500001",                         ↵│
│                                      │     "journal": [                                                 ↵│
│                                      │         {                                                        ↵│
│                                      │             "event": "active",                                   ↵│
│                                      │             "user_id": "a9922c57-3da1-4177-b03c-263dec405d93",   ↵│
│                                      │             "datetime": "2025-09-08T17:38:42.415013+03:00"       ↵│
│                                      │         },                                                       ↵│

Мы обернули документ в функцию jsonb_pretty, чтобы увидеть его с отступами. Если вы работаете в PGAdmin, jsonb_pretty не понадобится: дважды щелкните по документу, и он откроется в новом окне с форматированием. В заголовке окна находится переключатель, чтобы вывести документ в виде дерева с выпадающими узлами. Здесь же находится поле поиска, чтобы искать в рамках текущего документа.

При работе с документами самая частая операция – поиск по первичном ключу:

select * from applications
where id = '00000000-0000-0000-0000-000000500001';

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

Чтобы искать документы по их полям, используйте стрелочные операторы, что мы изучили во второй главе. Вот как найти документ по бухгалтерскому номеру:

select * from applications
where doc->>'application_id' = '500001';

А так – заявки, поданные одной организацией:

select id from applications
where doc #>> '{organization,id}' = '00000000-0000-0000-0000-000000000001';

┌──────────────────────────────────────┐
│                  id                  │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000500001 │
│ 00000000-0000-0000-0000-000000501001 │
│ 00000000-0000-0000-0000-000000502001 │
│ 00000000-0000-0000-0000-000000503001 │
│ 00000000-0000-0000-0000-000000504001 │

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

Вставка

Вставка документа работает просто – это INSERT, где главная роль отводится документу. При создании документа важно, кто отвечает за идентификатор: клиент или база. В первом случае приложение создает идентификатор и передает в запрос. Выразим это на псевдокоде, близком к Python:

uuid = generate_v7_uuid()

conn.execute(
  "insert into applications(id, doc) values (?, ?)", (
    uuid,
    {"some": "application"}
  )
)

Во втором случае идентификатор не передается, и сработает выражение default, указанное в определении поля. Иногда приложению нужно знать, какой идентификатор произвела база, чтобы передать его в другой документ. Запрос INSERT дополняют выражением RETURNING, где перечисляют поля, которые следует вернуть в качестве ответа. В нашем случае это только id:

result = conn.execute(
  "insert into applications(doc) values (?) returning id",
  ({"some": "application"}, )
)

app_uuid = result[0]["id"]

conn.execute(
  "insert into some_entity(app_id, doc) values (?, ?)", (
    app_uuid,
    {"some": "entity"}
  )
)

С точки зрения клиента запрос INSERT...RETURNING не отличается от SELECT. Из ответа берут первую строку, а из нее – идентификатор нового документа. Строк в ответе столько же, сколько документов мы вставили.

Обновление

Следующее действие, что мы рассмотрим – обновление документа. В простом случае документ читают из базы, обновляют в приложении и перезаписывают старый документ. Вот как это выглядит на псевдокоде:

result = conn.execute(
  "select * from applications where id = ?",
  (app_uuid, )
)

row    = result[0]
app_id = row["id"]
app    = row["doc"]

app["status"] = "pending"

conn.execute(
  "update applications set doc = ? where id = ?",
  (app, app_id, )
)

Код на ORM короче, но под капотом делает то же самое: вызывает SELECT и UPDATE.

app = Application.get_by_id(some_uuid)
app.status = "pending"
app.save()

Минус подхода в том, что он небезопасен в параллельном режиме. Если в промежутке между SELECT и UPDATE кто-то обновил документ, последний UPDATE затрет эти изменения. Позже к вам обратиться сотрудник: я обновил документ, но изменения потерялись.

Поскольку у нас реляционная база, воспользуйтесь транзакцией с блокировкой записи. Это гарантирует, что мы не затрем изменения другого пользователя и наоборот – он не запишет свою версию документа поверх нашей:

conn.with_transaction() as tx:
  result = tx.execute("select ... for update", (app_id, ))
  app = ...
  ...
  tx.execute("update ... ", (app, app_id))

Предположим, один и тот же документ редактируют из двух потоков. Как только первый поток захватил блокировку, во втором потоке запрос select ... for update повиснет до тех пор, пока первый не завершит транзакцию.

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

Простое решение в том, чтобы заносить документы, которые сейчас редактируются, в особую таблицу. Она хранит ключ документа, время и ссылку на сотрудника. Обратите внимание, что ссылка на документ – первичный ключ. Это физически не допустит ситуации, когда документ редактируют несколько человек.

create table docs_editing(
    doc_id uuid primary key,
    user_id uuid not null,
    created_at timestamptz not null default current_timestamp
);

Перед началом редактирования в таблицу docs_editing добавляется запись, а при сохранении – удаляется. Теперь если Петров редактирует документ, а Иванов намерен сделать то же самое, он увидит сообщение, что документ заблокирован Петровым. Иванов получит версию для чтения и не сможет помешать коллеге.

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

Перед записью документа проверяйте его схемой. Если документ не ложится на схему, бросайте исключение. Менеджеры транзакций устроены так, что если произошло исключение, то сперва оно будет удержано. Транзакция отменится (выполнится ROLLBACK), а затем исключение возбуждается снова. Далее оно поднимается по стеку вызовов, пока не встретится оператор catch.

Опасайтесь случаев, когда документ обновляется без проверок. Даже в языках со строгой типизацией возможно, что вместо документа передали null (nil, None и другие пустые значения). Он приводится к JSON-строке “null” и заменяет прежний документ. Восстановить его можно только из бекапа или ранее сохраненной версии, о чем мы поговорим в будующей главе.

У перезаписи документа еще один недостаток: мы передаем его целиком. Однако в среднем бизнес-операция меняет в документе два-три поля. Очевидно, передача всего документа избыточна. Ниже мы рассмотрим, как этого избежать.

Оператор UPDATE предлагает особый синтаксис для JSON, чтобы обновить отдельные поля. Он напоминает Python, где доступ к словарям и массам работает через квадратные скобки. Ниже мы атомарно обновляем три поля. Выражение справа от равенства должно быть JSON-строкой или документом:

update applications set
    doc['status'] = '"deleted"',
    doc['updated_at'] = '"2026-01-02T09:46:12Z"',
    doc['comment'] = '"Deleting this application"'
where
    id = '00000000-0000-0000-0000-000000500001';

Для массивов используют числовые индексы. Вот как обновить первого пользователя заявки:

update applications set
    doc['departments'][0]['users'][0]['name'] = '"Peter Dow"'
where id = '00000000-0000-0000-0000-000000500001';

Индекс -1 ссылается на последний элемент массива:

update applications set
    doc['departments'][-1]['users'][-1]['name'] = '"Last User"'
where id = '00000000-0000-0000-0000-000000500001';

Когда полей много, записывать пути и значения утомительно. Вместо этого передают объект, и документ объединяется с ним. Для этого служит оператор || (палочки). Подготовим выражение, которое принимает ключ документа и его подмножество:

prepare app_update as
update applications set
    doc = doc || $2
where id = $1;

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

execute app_update('00000000-0000-0000-0000-000000500001', $${
    "status": "deleted",
    "updated_at": "2026-01-02T09:46:12Z",
    "comment": "Deleting this application"
}$$::jsonb);

Напомним, что оператор || неглубокий (shallow): он обходит ключи только верхнего уровня. Вложенные объекты и массивы не учитываются, а просто заменяют друг друга. Если слияние должно быть глубоким, воспользуйтесь функцией jsonb_merge. Ее нет в поставке Postgres, поэтому либо скопируйте код по ссылке (gist), либо подключите расширение pg_jsonb_merge с реализацией на Си. Новый запрос выглядит так:

update applications set
    doc = jsonb_merge(doc, $2)
where id = $1;

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

Вставка с обновлением (UPSERT)

Рассмотрев вставку и обновление, подумаем: можно ли их объединить? Сделать так, чтобы за одну операцию либо обновить, либо создать документ, если его нет. Неофициально такое действие называют UPSERT (от слов UPDATE и INSERT). Прежде чем мы перейдем к деталям, рассмотрим, почему UPSERT важен.

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

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

Некоторые документные базы не различают вставку и обновление – для них это одна операция на уровне дизайна. Часто ее называют add, put, assert и другими словами, чтобы подчеркнуть разницу с INSERT и UPDATE в SQL.

Начинающие программисты делают UPSERT следующим образом. Сперва проверяют, есть ли документ, запрашивая его по ключу. В зависимости от результата либо вставляют документ (INSERT), либо обновляют (UPDATE). Псевдокод:

app = Application.get_by_id("<UUID>")

if app is None:
    app = Application(...)
    app.save() # insert into ...
else:
    app.status = "pending"
    app.save() # update ... where id = ?

У этого подхода несколько минусов. Во-первых, возможна ситуация, что между get_by_id и вставкой кто-то добавил документ с таким ключом. Это может быть второй поток, который разбирает очередь задач. В результате INSERT из первого потока окончится неудачей из-за конфликта ключей. Во-вторых, когда документов много, частые get_by_id дают нагрузку на базу.

Оператор INSERT в Postgres предлагает выражение ON CONFLICT. Оно описывает тип конфликта и реакцию на него. Конфликтом может быть имя поля, уникальный индекс или ограничение, заданное формой CONSTRAINT. Реакций может быть две: DO NOTHING (ничего не делать) и DO UPDATE с указанием, какие поля обновить. При этом доступна переменная EXCLUDED, которая ссылается на конфликтную запись.

Подготовим выражение, которое либо создает заявку, либо обновляет ее по ключу:

prepare app_upsert as
insert into applications(id, doc) values ($1, $2)
on conflict (id) do update set
    doc = excluded.doc,
    updated_at = now()
returning id, doc, updated_at;

Вызовем его с ключом и документом:

execute app_upsert('00000000-0000-0000-0000-000000500001', $${
  "some": "field"
}$$::jsonb);

┌──────────────────────────────────────┬───────────────────┬───────────────────────────────┐
│                  id                  │        doc        │          updated_at           │
├──────────────────────────────────────┼───────────────────┼───────────────────────────────┤
│ 00000000-0000-0000-0000-000000500001 │ {"some": "field"} │ 2026-01-02 13:11:20.270694+03 │
└──────────────────────────────────────┴───────────────────┴───────────────────────────────┘

Если документ с таким ключом есть, он перезаписывается. Если требуется не перезапись, а слияние, воспользуйтесь оператором || или jsonb_merge (см. выше):

doc = jsonb_merge(doc, excluded.doc)

Кроме doc, мы обновляем поле updated_at, чтобы отслеживать дату события. Если документа нет, приложение выполняет обычный INSERT.

Иногда конфликт вызван не id, от другим полем, например бухгалтерским номером. В главе про индексы мы рассмотрим такой случай.

Перейдем к тому, как выполнить UPSERT для многих документов. Разница тем, что мы написали, невелика. Сперва поместите документы во временную таблицу. Назовем ее app_temp:

create temp table app_temp (
    id uuid primary key,
    doc jsonb not null
);

insert into app_temp values
    ('00000000-0000-0000-0000-000000500001', '{"field1": "test1"}'::jsonb),
    ('00000000-0000-0000-0000-000000500002', '{"field2": "test2"}'::jsonb),
    ('00000000-0000-0000-0000-000000500003', '{"field3": "test3"}'::jsonb);

Теперь выполните комбинацию insert, select и on conflict:

insert into applications(id, doc)
select * from app_temp
on conflict (id) do update set
    doc = excluded.doc,
    updated_at = now()
returning id, doc, updated_at;

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

С версии 15 Postgres предлагает оператор MERGE – улучшенную версию UPSERT. MERGE принимает две таблицы и задает логику совпадения записей. Каждому исходу (только слева, в обоих таблицах, только справа) можно задать реакцию: удалить, пропустить, вставить в таблицу и так далее. Перепишем вставку документов на MERGE:

merge into applications as a
using app_temp as t
on a.id = t.id
when matched then
    update set
        doc = t.doc,
        updated_at = now()
when not matched then
    insert (id, doc)
    values (t.id, t.doc);

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

Удаление

Рассмотрим два способа удалить документ – полностью и частично. Команда DELETE физически удаляет запись из таблицы (если точнее, сперва запись помечается на удаление, а затем удаляется процессом VACUUM). Обычно документы удаляют по ключу, что делается запросом:

delete from applications
where id = '00000000-0000-0000-0000-000000500005'
returning doc;

Перед удалением документа желательно сохранить его копию. Проще всего это сделать переносом в таблицу с такой же структурой:

insert into applications_backup
select * from applications
where id = '00000000-0000-0000-0000-000000500005'

Заключите оба запроса в транзакцию, чтобы сбой одного не давал побочных эффектов.

Переносить документы может и приложение. Запрос DELETE выше возвращает поле doc – удаленный документ, которого больше нет в базе. Однако он есть в приложении, и можно отправить его в файловое хранилище, в другой сервис и так далее.

Перенос может быть автоматическим при помощи триггера на событие DELETE. Еще один вариант – объединить запросы DELETE и INSERT в CTE-выражении. Мы рассмотрим все эти способы в главе о версионировании документов. Пока что считаем, что обычное удаление нас устраивает.

Можно удалять документы по определенному полю, например со статусом deleted:

delete from applications
where doc->>'status' = 'deleted';
-- DELETE 249656

Другой пример – удалить заявки конкретной организации:

delete from applications
where doc #>> '{organization,id}' = '00000000-0000-0000-0000-000000000001';

Иногда нужно удалить не весь документ, а его подмножество. Скажем, по требованию регулятора мы не удаляем заявки, но обязаны очистить их историю (поле journal). Достаточно присвоить полю пустой массив или null:

update applications
set doc['journal'] = 'null'
where doc->>'status' = 'deleted';
-- UPDATE 249656

Другой способ – удалить поле оператором - (минус):

update applications
set doc = doc - 'journal'
where doc->>'status' = 'deleted';
-- UPDATE 249656

Последняя операция, что мы рассмотрим, называется TRUNCATE. Команда очищает таблицу без каких-либо условий. Удаление происходит быстрее, чем DELETE, потому что TRUNCATE игнорирует триггеры и не сканирует таблицу. Место на диске, занятое таблицей, немедленно освобождается, в то время как DELETE лишь помечает записи на удаление, и позже они очищаются процессом VACUUM. Вот как очистить таблицу заявок:

truncate applications;

TRUNCATE очистит несколько таблиц, если указать их через запятую. Слово CASCADE означает, что будут рекурсивно очищены в том числе те таблицы, на которые ссылаются перечисленные.

truncate applications, users, orders cascade;

В чем польза такой радикальной команды? Маловероятно, что она понадобится в промышленном запуске – напротив, там ее избегают любой ценой. Однако TRUNCATE крайне полезна в тестах. Каждый тест создает документы, и в конце нужно удалить их, чтобы не повлиять на следующий тест. Для этого пишут фикстуру, которая в конце теста выполняет TRUNCATE ... CASCADE для всех таблиц.

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