Постановка задачи

PostgreSQL стала первой реляционной базой данных, поддерживающей слабоструктурированные данные. В PostgreSQL для этого используется JSON (JavaScript Object Notation, Запись объекта JavaScript RFC 7159), который имеет два представления: json и jsonb. Для реализации эффективного механизма запросов к этим типам данных в Postgres также имеется тип jsonpath. Официально JSON появился в PostgreSQL в 2014 году. PostgreSQL с JSONB совмещает гибкость NoSQL, а также надёжность и богатство функциональности реляционных СУБД.

Практическая часть

Создать PostgreSQL базу данных imdb, использующую стандартные атрибуты и атрибут jsonb. Для загрузки используется один файл actors.list.txt из папки DataSet.

Описание файла

1. Кодовая страница - ISO 8859-1.

2. Используемые разделители: HT (0x09) - horizontal_tab, LF (0x0A) - line_feed.

   Для полей используется один или несколько разделителей HT, для ролей актера один LF, для актеров два LF.

3. Правила синтаксического разбора поля Titles:

   1. первый атрибут title или "title" = название роли, переносим в json с одинарными кавычками или без кавычек;

   2. второй атрибут (year) = год роли, первые круглые скобки, может быть (????) - дата неизвестна;

   3. третий атрибут/необязательный (type1) = тип материала, все что после года в круглых скобках:

      • (V) - видео или клип;
      • (TV) - для телевидения;
      • (VG) - для видео игры;
      • (archive footage) - архивные или ранние кадры;
      • (uncredited) – не зарегистрирован или не указан в титрах;
      • (voice) - озвучка или закадровый голос.

      Может иметь несколько атрибутов одновременно, тогда следует именовать ключи как type1, type2, type3, т.к. в jsonb все ключи должны быть разными;

   4. четвертый атрибут/необязательный {series name} = название серии в сериале: в фигурных скобках, #xx.yy = номер_сезона/номер_серии;

   5. пятый атрибут/необязательный (as character) = пояснение имени героя: находится в скобках перед именем героя, добавляем в json к ключу "character name" в конце как есть в скобках;

   6. шестой атрибут/необязательный [character name] = имя героя: в квадратных скобках;

   7. седьмой атрибут/необязательный <credit> = номер в титрах: в треугольных скобках.

Исключения из правил при разборе

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

Примеры исключений:

1. Внутри роли разделители пробелы.
2. Для поля name может быть только имя - оставляем фамилию пустой.
3. Имена, фамилии включают скобки и разные странные символы - отставляем как есть.
4. Поле (as character) может повторяться как (also as character) - игнорировать или добавить еще type.
5. (2014/III) - замена на (2014).
6. (????) - замена на (0000).
7. (????/III) замена на (0000).

Поля СУБД

• id integer (PRIMARY KEY генерируется из последовательности).
• ActorFirstName varchar (из поля Name, исходный формат SecondName, FirstName).
• ActorSecondName varchar (из поля Name, исходный формат SecondName, FirstName).
• RolesName jsonb (из поля Titles по правилу разбора).

Пример записи в файле

Freeman, Morgan (I) 10 Items or Less (2006)  [Him]  <1>
   100 Years at the Movies (1994) (TV)  (archive footage)  [Himself]
   11th Annual Screen Actors Guild Awards (2005) (TV)  [Himself - Winner & Nominee]
   12th Annual Screen Actors Guild Awards (2006) (TV)  [Himself]
   15th Annual Critics' Choice Movie Awards (2010) (TV)  [Himself]
   16th Annual Screen Actors Guild Awards (2010) (TV)  [Himself]
   17th Annual Screen Actors Guild Awards (2011) (TV)  [Himself - Presenter: Life Achievement Award]
   1995 VH1 Honors (1995) (TV)  [Himself]
   1996 Blockbuster Entertainment Awards (1996) (TV)  [Himself]
   19th Annual Screen Actors Guild Awards (2013) (TV)  [Himself - Presenter]  <52>
   2003 World Awards (2003) (TV)  [Himself]
   2014 Primetime Creative Arts Emmy Awards (2014) (TV)  [Himself - Presenter: Outstanding Guest Actress in a Comedy Series]  <178>
   2016 Creative Arts Emmys (2016) (TV)  [Himself - Presenter: Outstanding Special Class Program and Nominated: Outstanding Informational Series or Special]  <148>
   20th Annual Screen Actors Guild Awards (2014) (TV)  (uncredited)  [Himself - Presenter]
   22nd NAACP Image Awards (1990) (TV)  [Himself]
   24 Hours of Reality and Live Earth: The World Is Watching (2015) (TV)  [Himself]
   25th NAACP Image Awards (1993) (TV)  [Himself]
   29th NAACP Image Awards (1998) (TV)  [Himself]
   30th NAACP Image Awards (1999) (TV)  [Himself]
   3rd Annual Screen Actors Guild Awards (1997) (TV)  [Himself]
   41st NAACP Image Awards (2010) (TV)  [Himself]
   5 Flights Up (2014)  [Alex Carver]  <2>
   73rd Golden Globe Awards (2016) (TV)  [Himself - Presenter: Director-Motion Picture]  <134>
   A Cautionary Tale: The Making of 'The Sum of All Fears' (2002) (V)  [Himself]
   A Century of Cinema (1994)  [Himself]

Пример шаблона файла

Две первые строки в файле пропускать при разборе, поскольку они содержат заголовок списка ролей:

Name                    Titles 
----                    ------

Далее формат файла выглядит примерно следующим образом:

SecondName, FirstName HT HT HT title (year)                                [character name] <credit> LF
                      HT HT HT title (year)                (type1)         [character name]          LF
LF
SecondName, FirstName       HT title (year)                                [character name] <credit> LF
                      HT HT HT title (year) {series name}  (type1) (type2) [character name] <credit> LF
                      HT HT HT title (year) (as character)                 [character name]          LF

Пример разбора для таблицы

• id *****

• ActorFirstName "Morgan"

• ActorSecondName "Freeman"

• RolesName

  {
      "roles": [
          {
              "title": "The Shawshank Redemption",
              "year": "1994",
              "character name": "Ellis Boyd 'Red' Redding",
              "credit": "2"
          },
          {
              "title": "The True Story of Glory Continues",
              "year": "1991",
              "type1": "voice",
              "character name": "Narrator",
              "credit": "1"
          },
          {
              "title": "Unforgiven",
              "year": "1992",
              "character name": "Ned Logan",
              "credit": "1"
          },
          {
              "title": "Through the Wormhole",
              "year": "2010",
              "series name": "Are Aliens Inside Us? (#6.5)",
              "character name": "Himself - Narrator",
              "credit": "1"
          }
      ]
  }

Задачи

1. Составить 4-5 запросов с использованием api postgresql для jsonb:
   • используя проверки на вхождение и существование jsonb;
   • используя обращение по индексу к элементам jsonb;
   • используя язык jsonpath;
   • используя функции для типа jsonb.
2. Измерить время доступа к полю jsonb для каждой строчки (в виде таблицы или графика). Оценить влияние длины строки на скорость доступа (ожидается ступенька до 2kB, после линейная зависимость). Для этого следует измерить время чтения year первой роли актера (explain analyze). Для точности оценки важно учитывать хранимую длину (jsonb хранится в сжатой форме). Как можно это влияние уменьшить?
3. Составить запрос на изменение year у первой роли актера. Сравнить изменение объема БД для актера с малым кол-вом ролей и актера с большим количеством ролей (toasted roles).

Особенности работы

1. Разбор исходного текста можно производить как с использованием хранимой процедуры plpython3u, так и любым клиентом (например, python, C#, java…). Для хранимой процедуры есть ограничение на объем текста для вставки.
2. Не предполагается использование промежуточного формата файла при загрузке (например, csv).
3. Для построения графика можно использовать не все данные, но выборка должна быть представительной (например, 50 для начальной ступеньки и 50 для линейной части).
4. При построении графика следует фильтровать случайные выбросы (изменение нагрузки на сервер), желательно производить усреднение по значениям графика.

Темы для проработки

• Денормализация
  • https://habr.com/ru/company/latera/blog/281262
  • https://habr.com/ru/post/64524
• Json
  • https://postgrespro.ru/docs/postgrespro/17/datatype-json
  • https://postgrespro.ru/docs/postgrespro/17/functions-json
  • https://habr.com/ru/company/oleg-bunin/blog/646987/

Ответы на вопросы по PostgreSQL и JSON

В чем отличие типов json и jsonb?

PostgreSQL поддерживает два типа для хранения JSON-данных, которые существенно различаются по своей реализации:

1. Формат хранения:

   • json хранит точную текстовую копию введенного JSON как есть, включая все пробелы, форматирование и порядок ключей;
   • jsonb хранит данные в бинарном декомпозированном формате, что позволяет быстро обращаться к отдельным элементам.

2. Эффективность обработки:

   • json требует повторного парсинга при каждом запросе, что снижает производительность;
   • jsonb хранится уже в предварительно разобранном виде, что делает все операции поиска и обработки значительно быстрее.

3. Порядок ключей:

   • json сохраняет порядок ключей в том виде, в каком они были введены;
   • jsonb не сохраняет порядок ключей (они сортируются автоматически для оптимизации доступа).

4. Индексация:

   • json не поддерживает индексацию, что делает поиск по содержимому неэффективным;
   • jsonb поддерживает специализированные GIN-индексы, позволяющие быстро извлекать данные по ключам и значениям.

5. Дублирующиеся ключи:

   • json сохраняет все дублирующиеся ключи как есть;
   • jsonb сохраняет только последнее значение для каждого дублирующегося ключа.

Таким образом, json подходит для случаев, когда требуется точное сохранение исходного формата, а jsonb - когда важны производительность запросов и возможность эффективного поиска.

Какие типы индексации поддерживает jsonb?

JSONB поддерживает несколько типов индексации, что делает его мощным инструментом для работы со слабоструктурированными данными:

1. GIN (Generalized Inverted Index) - наиболее эффективный тип индексации для JSONB с двумя вариантами операторов:

   • jsonb_ops (по умолчанию) - индексирует каждый ключ и значение, поддерживает операторы @>, ?, ?&, ?|;
   • jsonb_path_ops - оптимизирован специально для оператора включения (@>), более компактен и быстрее для таких запросов:

   CREATE INDEX idx_actors_roles ON actors USING GIN (RolesName jsonb_path_ops);

2. B-tree индексы - могут использоваться для сравнения JSONB-полей целиком:

   CREATE INDEX idx_json_equality ON actors (RolesName);

3. Функциональные индексы - для индексации значений конкретных ключей:

   CREATE INDEX idx_year ON actors ((RolesName->'roles'->0->>'year'));

4. Частичные индексы - для индексирования только строк, удовлетворяющих определенному условию:

   CREATE INDEX idx_actors_with_type ON actors USING GIN (RolesName)
   WHERE jsonb_path_exists(RolesName, '$.roles[*].type1');

5. Комбинированные индексы - для одновременной индексации нескольких полей:

   CREATE INDEX idx_actors_combined ON actors (ActorFirstName, ActorSecondName, (RolesName->'roles'->0->>'year'));

Правильный выбор типа индекса существенно влияет на производительность запросов к JSONB-данным.

Что такое jsonpath?

JSONPath - это специализированный язык запросов для работы с JSON-данными, добавленный в PostgreSQL 12 и стандартизированный в SQL/JSON:

1. Синтаксис и основные элементы:

   • $ - корневой элемент JSON-документа;
   • .key или ."key with spaces" - доступ к полю объекта;
   • [*] - обращение ко всем элементам массива;
   • ?() - фильтр для выборки элементов;
   • @ - ссылка на текущий обрабатываемый элемент.

2. Возможности языка:

   • Сложная навигация по вложенным структурам;
   • Арифметические операции с извлекаемыми числовыми значениями;
   • Логические операторы (&&, ||, !);
   • Операторы сравнения (==, !=, <, <=, >, >=);
   • Поиск по шаблону с использованием регулярных выражений (like_regex).

3. Основные функции для работы с jsonpath:

   • jsonb_path_exists(json, path) - проверяет, существуют ли элементы, соответствующие пути;
   • jsonb_path_query(json, path) - извлекает все значения, соответствующие пути;
   • jsonb_path_query_array(json, path) - возвращает результаты в виде JSON-массива;
   • jsonb_path_query_first(json, path) - возвращает первый соответствующий элемент.

4. Пример использования:

   SELECT ActorFirstName, ActorSecondName
   FROM actors
   WHERE jsonb_path_exists(
       RolesName,
       '$.roles[*] ? (@.year >= "2010" && @.credit == "1")'
   );

   Этот запрос находит актеров, сыгравших главную роль в фильмах 2010 года и позже.

JSONPath предоставляет более мощный и декларативный способ работы с JSON-данными по сравнению со стандартными операторами доступа к полям (->, ->>) и значительно упрощает сложные запросы к вложенным структурам.

Что такое toasted object?

TOAST (The Oversized-Attribute Storage Technique) - это механизм PostgreSQL для эффективного хранения и доступа к большим значениям атрибутов:

1. Принцип работы:

   • PostgreSQL хранит строки в страницах фиксированного размера (обычно 8 KB);
   • Когда значение атрибута слишком велико (превышает определенный порог), оно "тостируется" - перемещается во внешнюю TOAST-таблицу;
   • В основной таблице остается только небольшой указатель на TOAST-значение.

2. Пороговые значения:

   • Для JSONB порог TOAST составляет около 2 KB;
   • Данные, превышающие этот порог, автоматически перемещаются в TOAST-таблицы.

3. Стратегии хранения TOAST-данных:

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

   • Сжатие - процесс уменьшения размера данных с применением алгоритма LZ, который PostgreSQL использует для TOAST-данных. Сжатие позволяет существенно уменьшить занимаемое пространство, особенно для текстовых данных, но требует дополнительных вычислительных ресурсов при каждой операции чтения и записи.
   • Внешнее хранение - механизм перемещения больших значений из основной таблицы во вспомогательную TOAST-таблицу. При этом в основной строке таблицы остается только небольшой указатель (16 байт), который ссылается на соответствующую запись в TOAST-таблице. Это позволяет поддерживать эффективное хранение больших объектов, но добавляет дополнительное обращение к диску при доступе к данным.

   Сами стратегии определяют, какие механизмы будут использоваться для TOAST-данных:

   • PLAIN - предотвращает как сжатие, так и внешнее хранение. Это единственная возможная стратегия для столбцов с типами данных, не поддерживающими TOAST;
   • EXTENDED - позволяет использовать как сжатие, так и внешнее хранение (по умолчанию для большинства TOAST-поддерживаемых типов данных). Сначала предпринимается попытка сжатия, а затем, если строка все еще слишком большая, применяется внешнее хранение;
   • EXTERNAL - разрешает внешнее хранение, но не сжатие. Использование EXTERNAL делает операции с подстроками на широких текстовых и bytea столбцах быстрее (за счет увеличения занимаемого пространства), поскольку эти операции оптимизированы для извлечения только необходимых частей внешнего значения, когда оно не сжато;
   • MAIN - разрешает сжатие, но не внешнее хранение. (Фактически, внешнее хранение всё равно будет выполнено для таких столбцов, но только как последнее средство, когда нет другого способа уменьшить строку до размера, позволяющего разместить её на странице).

4. Оптимизация работы с TOAST-объектами:

   • Выделение часто используемых небольших полей в отдельные столбцы;
   • Использование частичных индексов для оптимизации доступа;
   • Разделение больших JSON-документов на логические части.

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

Ход работы

Создание базы данных

Для начала подключимся к серверу PostgreSQL:

psql -U postgres

Создадим базу данных imdb:

CREATE DATABASE "imdb";

Обработка файла со списком актеров и их ролей

Скрипт parse.py предназначен для обработки файла actors.list.txt из набора данных IMDB и загрузки этих данных в PostgreSQL базу данных, как требуется в техническом задании.

Используемые библиотеки

• psycopg2 - для взаимодействия с PostgreSQL.
• json - для работы с JSON-форматом данных.
• re - для использования регулярных выражений при парсинге файла.
• time - для измерения времени выполнения различных этапов.
• concurrent.futures.ThreadPoolExecutor - для параллельной обработки данных.
• os - для получения системной информации и работы с файлами.
• psycopg2.extras.execute_values - для оптимизированной пакетной вставки данных.
• prettytable - для форматированного вывода статистики в консоль.

Архитектура решения

Скрипт организован по принципу поэтапной обработки данных:

1. Подключение к БД - с оптимизированными параметрами PostgreSQL.
2. Чтение файла - загрузка файла actors.list.txt в память.
3. Разделение данных - разбиение файла на блоки актеров по пустым строкам: для роли актёра используеться один разделитель LF, для актёров - два LF.
4. Параллельная обработка - многопоточный парсинг данных об актерах и их ролях.
5. Пакетная вставка - эффективная загрузка обработанных данных в БД.
6. Сбор и вывод статистики - анализ производительности и вывод результатов.

Ключевые функции

Функции парсинга данных

• parse_name(name_text) - извлекает имя и фамилию актера из строки формата "Фамилия, Имя".
• parse_role(raw_title) - разбирает строку с описанием роли на составляющие:
  • название фильма/сериала;
  • год выпуска;
  • типы материала (TV, V, VG и т.д.);
  • название серии и её номер;
  • имя персонажа;
  • номер в титрах;
  • альтернативные имена персонажа;
• parse_actor_data(actor_block) - обрабатывает весь блок текста об одном актере.

Вспомогательные функции

• clean_string(text) - удаляет двойные кавычки из строк.
• process_chunk_of_actors(actors_chunk) - обработка группы актеров (для параллельного выполнения).
• chunk_list(lst, chunk_size) - разбиение списка на подсписки.
• format_time(seconds) - преобразует секунды в читаемый формат времени.

Функции для работы с БД

• create_schema(cursor) - создает структуру таблицы в БД.
• main() - основная функция, координирующая весь процесс.

Оптимизации производительности

1. Предкомпиляция регулярных выражений - для более быстрого парсинга.
2. Многопоточная обработка - использование ThreadPoolExecutor с оптимальным количеством потоков, зависящим от числа ядер процессора.
3. Пакетные операции - группировка операций вставки данных.
4. Оптимизация памяти - своевременное освобождение памяти после использования объемных данных.
5. Настройка PostgreSQL - оптимизация параметров:
   • work_mem=64MB - увеличенная память для операций сортировки и хеширования;
   • statement_timeout=0 - отключение ограничений на время выполнения запроса.

Структура данных

Скрипт создает таблицу actors со следующими полями:

• id - автоинкрементный первичный ключ.
• ActorFirstName - имя актера.
• ActorSecondName - фамилия актера.
• RolesName - информация о ролях в формате JSONB.

CREATE TABLE IF NOT EXISTS actors (
    id SERIAL PRIMARY KEY,
    ActorFirstName TEXT,
    ActorSecondName TEXT,
    RolesName JSONB
);

Поле RolesName содержит массив объектов, каждый из которых описывает отдельную роль актера со всеми атрибутами, указанными в задании (название, год, персонаж и т.д.).

Мониторинг и статистика

Скрипт отслеживает время выполнения каждого этапа и в конце выводит детальную статистику:

• Общее время выполнения и распределение по этапам.
• Производительность (актеров и ролей в секунду).
• Соотношение количества ролей к актерам.

Очистка таблицы и создание индексов

Очистка таблицы (TRUNCATE)

В скрипте перед массовой вставкой данных выполняется полная очистка таблицы actors для обеспечения "чистых" запусков:

# Очищаем таблицу перед вставкой
truncate_start = time.time()
# Дополнительно испрользуем "RESTART IDENTITY", чтобы сбросить счетчик ID
cursor.execute("TRUNCATE TABLE actors RESTART IDENTITY")
timing["truncate"] = time.time() - truncate_start

Важные аспекты этого этапа:

• Используется команда TRUNCATE TABLE вместо DELETE FROM для более эффективной очистки, так как она не сканирует таблицу и требует меньше блокировок ресурсов.
• Опция RESTART IDENTITY сбрасывает счетчик автоинкрементного первичного ключа id до начального значения, что обеспечивает одинаковые ID при повторных запусках.
• Время выполнения этой операции измеряется и сохраняется для последующего анализа производительности.

Создание индексов

После загрузки всех данных скрипт создает специализированные индексы для оптимизации последующих запросов:

# Время создания индексов
index_start = time.time()
print("Создание индексов...")

# Создаем индекс для ускорения поиска, если его нет
cursor.execute(
    """
    CREATE INDEX IF NOT EXISTS idx_actors_names
    ON actors (ActorFirstName, ActorSecondName)
    """
)

# Создаем GIN-индекс для поиска по JSON, если его нет
cursor.execute(
    """
    CREATE INDEX IF NOT EXISTS idx_actors_roles
    ON actors USING GIN (RolesName jsonb_path_ops)
    """
)

conn.commit()

timing["index"] = time.time() - index_start
print(f"Создание индексов: {format_time(timing['index'])}")

Важные детали этого этапа:

• Создается составной B-tree индекс idx_actors_names по столбцам ActorFirstName и ActorSecondName, что ускоряет поиск и сортировку по именам актеров.
• Создается специализированный GIN (Generalized Inverted Index) индекс idx_actors_roles для работы с JSONB-полем RolesName. Этот тип индекса оптимизирован для полнотекстового поиска и поиска по элементам/ключам в JSON-структурах.
• Опция jsonb_path_ops оптимизирует индекс специально для операторов поиска по пути в JSONB и делает поиск более эффективным для заданий из технического задания.
• После создания индексов выполняется явный commit транзакции для фиксации изменений.
• Время создания индексов измеряется отдельно, так как это ресурсоемкая операция, особенно для больших объемов данных.

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

Установка зависимостей

Для работы скрипта необходимо установить зависимости. Сделать это можно создав виртуальное окружение и установив зависимости из файла requirements.txt:

python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

Файл requirements.txt содержит следующие зависимости:

psycopg2>=2.9.10
prettytable>=3.15.1
pandas>=2.2.3
matplotlib>=3.10.1
numpy>=2.2.4
scipy>=1.15.2

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

Запуск скрипта

Для запуска скрипта необходимо выполнить команду:

python ./0-parse/parse.py

Пример вывода работы скрипта

Полный листинг процесса обработки файла и загрузки данных в БД:

Подключение к БД: 0.14с
Чтение файла...
Файл прочитан, размер: 1235.54 МБ, время: 4.88с
Разделение данных на блоки актеров...
Всего найдено блоков актеров: 2673784, время: 1.08с
Обработка данных...
Потоков для параллельной обработки: 24
Всего обработано актеров: 2673784, ролей: 19993184, время: 2м 9.07с
Вставка данных в базу...
Вставлено 100000 из 2673784 записей (3.7%)... Осталось примерно: 1м 54.50с
Вставлено 200000 из 2673784 записей (7.5%)... Осталось примерно: 1м 38.30с
Вставлено 300000 из 2673784 записей (11.2%)... Осталось примерно: 1м 34.01с
Вставлено 400000 из 2673784 записей (15.0%)... Осталось примерно: 1м 31.12с
Вставлено 500000 из 2673784 записей (18.7%)... Осталось примерно: 1м 26.43с
Вставлено 600000 из 2673784 записей (22.4%)... Осталось примерно: 1м 22.42с
Вставлено 700000 из 2673784 записей (26.2%)... Осталось примерно: 1м 17.85с
Вставлено 800000 из 2673784 записей (29.9%)... Осталось примерно: 1м 13.97с
Вставлено 900000 из 2673784 записей (33.7%)... Осталось примерно: 1м 10.28с
Вставлено 1000000 из 2673784 записей (37.4%)... Осталось примерно: 1м 5.87с
Вставлено 1100000 из 2673784 записей (41.1%)... Осталось примерно: 1м 2.04с
Вставлено 1200000 из 2673784 записей (44.9%)... Осталось примерно: 57.91с
Вставлено 1300000 из 2673784 записей (48.6%)... Осталось примерно: 53.74с
Вставлено 1400000 из 2673784 записей (52.4%)... Осталось примерно: 49.75с
Вставлено 1500000 из 2673784 записей (56.1%)... Осталось примерно: 45.86с
Вставлено 1600000 из 2673784 записей (59.8%)... Осталось примерно: 42.08с
Вставлено 1700000 из 2673784 записей (63.6%)... Осталось примерно: 38.04с
Вставлено 1800000 из 2673784 записей (67.3%)... Осталось примерно: 33.71с
Вставлено 1900000 из 2673784 записей (71.1%)... Осталось примерно: 29.88с
Вставлено 2000000 из 2673784 записей (74.8%)... Осталось примерно: 25.99с
Вставлено 2100000 из 2673784 записей (78.5%)... Осталось примерно: 22.10с
Вставлено 2200000 из 2673784 записей (82.3%)... Осталось примерно: 18.25с
Вставлено 2300000 из 2673784 записей (86.0%)... Осталось примерно: 14.37с
Вставлено 2400000 из 2673784 записей (89.8%)... Осталось примерно: 10.50с
Вставлено 2500000 из 2673784 записей (93.5%)... Осталось примерно: 6.69с
Вставлено 2600000 из 2673784 записей (97.2%)... Осталось примерно: 2.84с
Вставка данных: 1м 42.00с
Создание индексов...
Создание индексов: 1м 10.10с
+--------------------------------------------------+
|      ИТОГОВАЯ СТАТИСТИКА ВРЕМЕНИ ВЫПОЛНЕНИЯ      |
+------------------------+-----------+-------------+
| Этап                   | Время     | % от общего |
+------------------------+-----------+-------------+
| Общее время выполнения | 5м 7.54с  | 100.0%      |
| Подключение к БД       | 0.14с     | 0.0%        |
| Чтение файла           | 4.88с     | 1.6%        |
| Разбиение на блоки     | 1.08с     | 0.3%        |
| Очистка таблицы        | 0.10с     | 0.0%        |
| Подготовка частей      | 0.17с     | 0.1%        |
| Парсинг данных         | 2м 9.07с  | 42.0%       |
| Вставка данных         | 1м 42.00с | 33.2%       |
| Создание индексов      | 1м 10.10с | 22.8%       |
+------------------------+-----------+-------------+
+----------------------------------------------+
|        СТАТИСТИКА ПРОИЗВОДИТЕЛЬНОСТИ         |
+----------------------------------+-----------+
| Метрика                          | Значение  |
+----------------------------------+-----------+
| Скорость обработки (актеров/сек) | 20716.44  |
| Скорость обработки (ролей/сек)   | 154906.93 |
| Скорость вставки (актеров/сек)   | 26212.65  |
| Общая скорость (актеров/сек)     | 8694.00   |
| Соотношение ролей к актерам      | 7.48      |
+----------------------------------+-----------+

Результаты

Для проверки выведем первые 3 записи из таблицы actors:

psql --tuples-only -U postgres -d imdb -c "select * from actors limit 3;"

id	actorfirstname	actorsecondname	rolesname
1	Buffy	#1	{"roles": [{"year": "2015", "title": "Closet Monster", "credit": "31", "character name": "Buffy 4"}]}
2	Claw	$	{"roles": [{"year": "2012", "title": "OnCreativity", "character name": "Himself"}]}
3	Homo	$	{"roles": [{"year": "1986", "title": "Nykytaiteen museo", "credit": "25", "character name": "Himself"}, {"year": "1985", "title": "Suuri illusioni", "credit": "22", "character name": "Guests"}]}

Зафиксируем количество строк в итоговой таблице actors - 2'673'784:

psql -U postgres -d imdb -c "select count(*) from actors;"

  count  
---------
 2673784
(1 row)

Запросы с использованием API PostgreSQL для JSONB

Запуск запросов

Запустим выполнение реализованных запросов из файла ./1-queries/queries.sql выполнив следующую команду в терминале:

psql -U postgres -d imdb -f ./1-queries/queries.sql | cat

1.1. Проверки на вхождение и существование JSONB

Запрос 1: Поиск актёров, сыгравших Детектива

SELECT
    ActorFirstName,
    ActorSecondName
FROM
    actors
WHERE
    RolesName @> '{"roles": [{"character name": "Detective"}]}'
LIMIT 10;

Описание:

• Оператор @> (содержит/включает) - проверяет, является ли левый JSONB-документ супермножеством правого.
• Запрос ищет записи, где в массиве roles существует хотя бы один объект с полем "character name" равным "Detective".
• Преимущество: использует GIN-индекс по JSONB, что делает поиск эффективным.
• LIMIT 10 ограничивает вывод первыми 10 совпадениями.

Запрос 2: Фильмы 2002 года, где актер играл самого себя

SELECT
    ActorFirstName,
    ActorSecondName,
    RolesName -> 'roles' -> 0 ->> 'title'
FROM
    actors
WHERE
    RolesName @> '{"roles": [{"year": "2002"}]}'
    AND RolesName @> '{"roles": [{"character name": "Himself"}]}'
LIMIT 10;

Описание:

• Используются два условия с оператором @>, соединенные через AND.
• Первое условие: ищет роли с годом "2002".
• Второе условие: ищет роли с именем персонажа "Himself".
• Оператор -> - извлекает поле из JSONB как JSONB-объект.
• Оператор ->> - извлекает поле из JSONB как текст (TEXT).
• RolesName->'roles'->0->>'title' - получает название первой роли из списка.

Запрос 3: Проверка существования ключа

SELECT
    COUNT(*)
FROM
    actors
WHERE
    RolesName -> 'roles' -> 0 ? 'type1';;

Описание:

• Оператор ? - проверяет существование ключа в JSONB-объекте (без учета его значения).
• RolesName->'roles'->0 - обращение к первой роли в массиве ролей.
• Запрос подсчитывает количество актеров, у которых в первой роли указан тип материала (type1).

1.2. Обращение по индексу к элементам JSONB

Запрос 4: Получение данных о первой роли

SELECT
    ActorFirstName,
    ActorSecondName,
    RolesName -> 'roles' -> 0 ->> 'title' AS first_role_title,
    RolesName -> 'roles' -> 0 ->> 'year' AS first_role_year
FROM
    actors
LIMIT 15;

Описание:

• Использует операторы доступа к элементам массива по индексу.
• ->0 - обращение к первому элементу массива (в PostgreSQL индексация начинается с 0).
• ->>'title' - извлекает значение ключа 'title' как текст.
• AS - задает псевдонимы для выходных столбцов.
• Выводит имена актеров и информацию об их первой роли (название и год).

Запрос 5: Рейтинг актеров по количеству ролей

SELECT
    ActorFirstName,
    ActorSecondName,
    jsonb_array_length(RolesName -> 'roles') AS roles_count
FROM
    actors
ORDER BY
    roles_count DESC
LIMIT 10;

Описание:

• jsonb_array_length() - встроенная функция, возвращающая количество элементов в JSONB-массиве.
• ORDER BY roles_count DESC - сортировка в порядке убывания количества ролей.
• Выводит ТОП-10 актеров с наибольшим количеством ролей в базе данных.

1.3. Использование языка JSONPath

Запрос 6: Актеры в главных ролях после 2010 года

SELECT
    ActorFirstName,
    ActorSecondName
FROM
    actors
WHERE
    jsonb_path_exists(RolesName, '$.roles[*] ? (@.year >= "2010" && @.credit == "1")')
LIMIT 10;

Описание:

• jsonb_path_exists() - функция, проверяющая существование элементов, соответствующих JSONPath-выражению.
• $ - корневой элемент документа.
• roles[*] - обращение ко всем элементам массива roles.
• ? - оператор фильтра в JSONPath.
• @ - текущий элемент при итерации.
• @.year >= "2010" && @.credit == "1" - условие: год больше или равен 2010 И номер в титрах равен 1.
• Находит актеров, сыгравших главную роль (credit=1) в фильмах 2010 года и позже.

Запрос 7: Поиск ролей с "Doctor" в имени персонажа

SELECT
    ActorFirstName,
    ActorSecondName,
    jsonb_path_query_array(RolesName, '$.roles[*] ? (@."character name" like_regex ".*Doctor.*")') AS doctor_roles
FROM
    actors
WHERE
    jsonb_path_exists(RolesName, '$.roles[*] ? (@."character name" like_regex ".*Doctor.*")')
LIMIT 5;

Описание:

• jsonb_path_query_array() - собирает результаты JSONPath-запроса в JSONB-массив.
• like_regex - оператор поиска по регулярному выражению в JSONPath.
• ".*Doctor.*" - регулярное выражение для поиска строк, содержащих "Doctor".
• В условии WHERE используется jsonb_path_exists() для фильтрации актеров.
• Запрос возвращает информацию об актерах и их ролях, где имя персонажа содержит "Doctor".

1.4. Использование функций для JSONB

Запрос 8: Создание агрегированной информации об актере

SELECT
    ActorFirstName,
    ActorSecondName,
    jsonb_build_object('name', ActorFirstName || ' ' || ActorSecondName, 'roles_count', jsonb_array_length(RolesName -> 'roles'), 'first_role', RolesName -> 'roles' -> 0) AS actor_summary
FROM
    actors
LIMIT 10;

Описание:

• jsonb_build_object() - создает новый JSONB-объект из пар ключ-значение.
• || - оператор конкатенации строк в PostgreSQL.
• Формирует для каждого актера новый JSON-объект с тремя полями:
  • name: полное имя актера (конкатенация имени и фамилии);
  • roles_count: количество ролей (через функцию jsonb_array_length);
  • first_role: полная информация о первой роли как вложенный JSON-объект.

Запрос 9: Развертывание массива ролей в отдельные строки

SELECT
    ActorFirstName,
    ActorSecondName,
    jsonb_array_elements(RolesName -> 'roles') ->> 'title' AS role_title
FROM
    actors
LIMIT 20;

Описание:

• jsonb_array_elements() - функция с возвращаемым набором (set-returning function).
• Преобразует каждый элемент массива roles в отдельную строку результата.
• Для каждого актера будет возвращено столько строк, сколько у него ролей.
• ->>'title' - извлекает только название роли в текстовом формате.
• Генерирует "плоский" список ролей вместо вложенной структуры.

Результаты выполнения запросов

Ниже представлены результаты выполнения всех вышеупомянутых запросов с использованием API PostgreSQL для JSONB:

 actorfirstname |     actorsecondname      
----------------+--------------------------
 Harry          | 'The Carpenter' Mansoori
 Peter          | Aanensen
 Frank          | Aaron
 Allan          | Aarons
 Asa            | Aarons
 Poul           | Aars
 Tony           | Abatemarco
 Serge          | Abattucci
 Marc           | Abbink
 David          | Abbott
(10 rows)

 actorfirstname | actorsecondname |                   ?column?                    
----------------+-----------------+-----------------------------------------------
 Guilford       | Adams           | Boys Don't Cry
 José           | 'El Francés'    | Alma gitana
 Sander         | 't Sas          | De wereld draait door
                | .38 Special     | 38 Special: Live at Sturgis
 Angry          | Anderson        | At Last... Bullamakanka: The Motion Picture
 Todd           | 1               | A Tribe Called Quest: The Video Anthology
 Roberto        | Arce            | Diez años juntos. Gala del décimo aniversario
                | 3 Doors Down    | 2004 Radio Music Awards
                | 3 Steps Ahead   | Thunderdome: A Decade
 Articolo       | 31              | L'erba proibita
(10 rows)

 count  
--------
 535981
(1 row)

 actorfirstname  | actorsecondname |               first_role_title               | first_role_year 
-----------------+-----------------+----------------------------------------------+-----------------
 Kyle            | Aames           | A Rim with a View 2                          | 2006
 Willie          | Aames           | 20 Greatest Celebreality Moments             | 2006
                 | Aamir (IV)      | Madhosh                                      | 1974
                 | Aamir (V)       | Heroine No.1                                 | 2001
 Mian            | Aamir           | The Girl Without a Song                      | 0000
 Sadiq           | Aamir           | Zinda Bhaag                                  | 2013
 Anders          | Aamodt          | The Recording                                | 2013
 Anders Kristian | Aamodt          | From Another Place                           | 2016
 Kitty           | Aamodt          | Alcohol By Volume                            | 2012
 Kjetil André    | Aamodt          | Bud Greenspan Presents: Torino 2006 Olympics | 2007
 Kristian        | Aamodt          | Kristine Valdresdatter                       | 1930
 Michael G.      | Aamodt          | Biography                                    | 1987
 Thor Michael    | Aamodt          | ...av hensyn til rikets sikkerhet            | 1989
 Tommy           | Aamodt          | Mengaloth                                    | 2005
                 | Aamon           | Geared Up 3                                  | 2013
(15 rows)

 actorfirstname | actorsecondname | roles_count 
----------------+-----------------+-------------
 Johnny         | Gilbert         |        7772
 Alex           | Trebek          |        7737
 Bob            | Barker          |        6950
 Pat            | Sajak           |        6668
 David          | Letterman       |        6474
 Paul           | Shaffer         |        6180
 Johnny         | Olson           |        6116
 Jay            | Leno            |        5640
 Ed             | McMahon         |        5108
 Frank          | Welker          |        5046
(10 rows)

 actorfirstname | actorsecondname 
----------------+-----------------
 Willie         | Aames
 Asger          | Aamund
                | Aari
 Kate           | Aarness
 Daniel         | Aarnio
 Marcus Mathias | Aarnseth
 Tony           | Aaron II
 Ben            | Aaron
 Just           | Aaron
 Matthew        | Aaron
(10 rows)

 actorfirstname | actorsecondname |                                                                   doctor_roles                                                                    
----------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------
                | Aanoosh         | [{"year": "1972", "title": "Shahr-e aftab", "credit": "3", "character name": "Doctor"}]
 Joe            | Aaron           | [{"year": "2017", "title": "See Pap Love", "credit": "4", "character name": "Doctor"}]
 Josh           | Aaron           | [{"year": "0000", "title": "Apnea", "character name": "Doctor Wayne"}]
 Lance          | Aaron           | [{"year": "2013", "title": "The Burden", "character name": "Doctor"}]
 Jerome         | Aarts           | [{"year": "2008", "title": "John Adams", "credit": "11", "episode": "1.3", "series name": "Don't Tread on Me", "character name": "Dutch Doctor"}]
(5 rows)

 actorfirstname | actorsecondname |                                                                                          actor_summary                                                                                          
----------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Rupert         | Aarons          | {"name": "Rupert Aarons", "first_role": {"year": "1980", "title": "Mystery!", "series name": "David Suchet on the Orient Express", "character name": "Himself"}, "roles_count": 1}
 Shawn          | Aarons          | {"name": "Shawn Aarons", "first_role": {"year": "2008", "title": "Never Back Down", "type1": "uncredited", "character name": "Wide Receiver"}, "roles_count": 1}
 Todd           | Aarons          | {"name": "Todd Aarons", "first_role": {"year": "2010", "title": "Phoebe", "credit": "3", "character name": "Phone Tech #2"}, "roles_count": 1}
 Doron          | Aaronsohn       | {"name": "Doron Aaronsohn", "first_role": {"year": "2009", "title": "1:0", "character name": "Soldier#1"}, "roles_count": 1}
 Alan           | Aaronson        | {"name": "Alan Aaronson", "first_role": {"year": "1958", "title": "The Party Crashers", "type1": "uncredited", "character name": "Ted Nickerson"}, "roles_count": 7}
 Bob            | Aaronson        | {"name": "Bob Aaronson", "first_role": {"year": "1990", "title": "Metamorphosis: The Alien Factor", "type1": "voice", "credit": "13", "character name": "Additional voices"}, "roles_count": 1}
 Brandon        | Aaronson        | {"name": "Brandon Aaronson", "first_role": {"year": "2014", "title": "Unstoppable Love", "credit": "6", "character name": "Band"}, "roles_count": 1}
 Craig          | Aaronson        | {"name": "Craig Aaronson", "first_role": {"year": "2007", "title": "Punk's Not Dead", "credit": "1", "character name": "Himself - Warner Brothers Records"}, "roles_count": 1}
 David          | Aaronson        | {"name": "David Aaronson", "first_role": {"year": "2001", "title": "The Fear", "credit": "2", "character name": "Tom Robinson"}, "roles_count": 1}
 Irving         | Aaronson        | {"name": "Irving Aaronson", "first_role": {"year": "1929", "title": "Aaronson's Commanders", "character name": "Himself"}, "roles_count": 2}
(10 rows)

 actorfirstname | actorsecondname |             role_title             
----------------+-----------------+------------------------------------
 Rupert         | Aarons          | Mystery!
 Shawn          | Aarons          | Never Back Down
 Todd           | Aarons          | Phoebe
 Doron          | Aaronsohn       | 1:0
 Alan           | Aaronson        | The Party Crashers
 Alan           | Aaronson        | The Pied Piper of Hamelin
 Alan           | Aaronson        | Underworld U.S.A.
 Alan           | Aaronson        | The Donna Reed Show
 Alan           | Aaronson        | The Donna Reed Show
 Alan           | Aaronson        | The O. Henry Playhouse
 Alan           | Aaronson        | Westinghouse Desilu Playhouse
 Bob            | Aaronson        | Metamorphosis: The Alien Factor
 Brandon        | Aaronson        | Unstoppable Love
 Craig          | Aaronson        | Punk's Not Dead
 David          | Aaronson        | The Fear
 Irving         | Aaronson        | Aaronson's Commanders
 Irving         | Aaronson        | Irving Aaronson and His Commanders
 Jack           | Aaronson        | The Big Gay Musical
 James          | Aaronson        | Of Plants & Plans
 James          | Aaronson        | The Making of a Superhero Musical
(20 rows)

Измерение времени доступа к полю JSONB

В директории ./2-timings располагаются два скрипта: 1-timings.sql и 2-timings.py.

Представленные скрипты реализуют комплексное исследование влияния размера JSONB-полей на скорость доступа к ним в PostgreSQL, фокусируясь на важной граничной точке в 2KB (порог TOAST механизма).

SQL-скрипт (1-timings.sql) - сбор данных

Запуск скрипта

psql -U postgres -d imdb -f ./2-timings/1-timings.sql | cat

Шаг 1: Подготовка тестовых данных

CREATE TEMP TABLE jsonb_access_sample AS
WITH small_docs AS (
    -- Выборка актеров с маленьким JSONB (меньше 2kB)
    SELECT
        id,
        RolesName,
        pg_column_size(RolesName) AS jsonb_size
    FROM
        actors
    WHERE
        pg_column_size(RolesName) < 2048
    LIMIT 50
),
large_docs AS (
    -- ... код ...

• Создаётся временная таблица с репрезентативной выборкой:
  • 50 записей размером меньше 2KB (хранятся inline);
  • 50 записей размером больше 2KB (используют TOAST).
• Функция pg_column_size() определяет реальный размер данных JSONB в байтах.
• Каждой записи присваивается метка 'inline' или 'toasted'.

Шаг 2: Подготовка таблицы для результатов

Создаётся таблица jsonb_access_metrics для хранения результатов измерений:

• id - идентификатор записи.
• jsonb_size - размер JSONB в байтах.
• storage_type - тип хранения (inline/toasted).
• roles_count - количество ролей в массиве.
• access_time_ns - измеренное время доступа в наносекундах.

Шаг 3: Функция измерения времени доступа

CREATE OR REPLACE FUNCTION measure_jsonb_access(p_id int, p_iterations int DEFAULT 10)
    RETURNS float
    AS $$

• Создаёт PL/pgSQL функцию для высокоточных измерений.
• Выполняет заданный запрос несколько раз (по умолчанию 10 итераций) для усреднения результатов.
• Измеряет именно время доступа к полю year из первой роли актёра: RolesName->'roles'->0->>'year'.
• Использует clock_timestamp() для микросекундной точности измерений.
• Возвращает среднее время доступа в наносекундах.

Шаг 4: Проведение измерений

DO $$
DECLARE
    rec RECORD;
    access_time float;
    roles_count int;
BEGIN
    -- Измерения для каждой записи
    FOR rec IN
    SELECT
        id,
        jsonb_size,
        storage_type
    FROM
        jsonb_access_sample
    ORDER BY
        jsonb_size LOOP
            -- Измеряем время доступа
            access_time := measure_jsonb_access(rec.id, 10);
            -- ... код ...

• С помощью анонимного блока выполняется цикл по всем тестовым записям.
• Для каждой записи:
  1. Измеряется время доступа с 10 итерациями для уменьшения случайных колебаний;
  2. Подсчитывается количество ролей актёра;
  3. Результаты сохраняются в таблицу метрик;
  4. Выводится информационное сообщение о прогрессе с помощью RAISE NOTICE.

Шаг 5: Анализ результатов и экспорт

• Выполняется агрегированный запрос, группирующий данные по типу хранения:

  SELECT
      storage_type,
      COUNT(*) AS samples,
      ROUND(AVG(jsonb_size)) AS avg_size_bytes,
      -- ... код ...

• Результаты записываются в CSV-файл через команду COPY:

  COPY (
    SELECT
        id,
        jsonb_size,
        storage_type,
        roles_count,
        access_time_ns
    FROM
        jsonb_access_metrics
    ORDER BY
        jsonb_size)
    TO '/tmp/jsonb_access_data.csv' WITH CSV HEADER;

Вывод скрипта

psql:2-timings/1-timings.sql:2: NOTICE:  table "jsonb_access_sample" does not exist, skipping
DROP TABLE
SELECT 100
 total_samples | inline_samples | toasted_samples 
---------------+----------------+-----------------
           100 |             50 |              50
(1 row)

psql:2-timings/1-timings.sql:35: NOTICE:  table "jsonb_access_metrics" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE FUNCTION
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=7579, размер=75, тип=inline, роли=1, время=39700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1651, размер=75, тип=inline, роли=1, время=4600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1642, размер=76, тип=inline, роли=1, время=3600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1640, размер=90, тип=inline, роли=1, время=3600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1612, размер=96, тип=inline, роли=1, время=3600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1647, размер=102, тип=inline, роли=1, время=3500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1632, размер=107, тип=inline, роли=1, время=3800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1653, размер=108, тип=inline, роли=1, время=3800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1619, размер=110, тип=inline, роли=1, время=3700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1660, размер=110, тип=inline, роли=1, время=3600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1630, размер=113, тип=inline, роли=1, время=3700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1635, размер=115, тип=inline, роли=1, время=3800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1641, размер=116, тип=inline, роли=1, время=3900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1611, размер=118, тип=inline, роли=1, время=3800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1617, размер=119, тип=inline, роли=1, время=4000.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1615, размер=119, тип=inline, роли=1, время=3900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1648, размер=119, тип=inline, роли=1, время=3600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1639, размер=123, тип=inline, роли=1, время=4100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1621, размер=125, тип=inline, роли=1, время=4400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1654, размер=125, тип=inline, роли=1, время=4300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1628, размер=125, тип=inline, роли=1, время=4300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1633, размер=126, тип=inline, роли=1, время=4200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1652, размер=127, тип=inline, роли=1, время=4500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1627, размер=131, тип=inline, роли=1, время=4500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1622, размер=131, тип=inline, роли=1, время=5200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1655, размер=132, тип=inline, роли=1, время=4400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1650, размер=133, тип=inline, роли=1, время=5100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1623, размер=134, тип=inline, роли=1, время=5100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1610, размер=138, тип=inline, роли=1, время=4500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1616, размер=150, тип=inline, роли=1, время=6200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1609, размер=155, тип=inline, роли=1, время=7700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1625, размер=155, тип=inline, роли=1, время=7600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1643, размер=163, тип=inline, роли=1, время=7900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1614, размер=169, тип=inline, роли=1, время=7400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1634, размер=173, тип=inline, роли=2, время=7900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1624, размер=175, тип=inline, роли=2, время=7900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1646, размер=182, тип=inline, роли=2, время=8400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1649, размер=202, тип=inline, роли=2, время=4900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1631, размер=203, тип=inline, роли=2, время=5100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1656, размер=205, тип=inline, роли=1, время=6500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1620, размер=205, тип=inline, роли=2, время=5300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1618, размер=231, тип=inline, роли=2, время=5300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1636, размер=326, тип=inline, роли=3, время=5400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1645, размер=381, тип=inline, роли=3, время=6100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1629, размер=681, тип=inline, роли=7, время=6200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1613, размер=892, тип=inline, роли=7, время=6000.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1637, размер=894, тип=inline, роли=8, время=5900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1644, размер=933, тип=inline, роли=7, время=5900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1638, размер=1077, тип=inline, роли=19, время=7700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1626, размер=1489, тип=inline, роли=12, время=6100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2395, размер=2116, тип=toasted, роли=53, время=22500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2362, размер=2132, тип=toasted, роли=39, время=17100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2632, размер=2139, тип=toasted, роли=41, время=11700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4567, размер=2222, тип=toasted, роли=61, время=15300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3200, размер=2246, тип=toasted, роли=55, время=20500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2232, размер=2249, тип=toasted, роли=77, время=11900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3221, размер=2264, тип=toasted, роли=38, время=17200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3680, размер=2270, тип=toasted, роли=55, время=18900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4064, размер=2279, тип=toasted, роли=70, время=21500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4627, размер=2320, тип=toasted, роли=39, время=13100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4928, размер=2339, тип=toasted, роли=70, время=12900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3720, размер=2371, тип=toasted, роли=62, время=14600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3509, размер=2410, тип=toasted, роли=400, время=18300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4441, размер=2413, тип=toasted, роли=45, время=14800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1865, размер=2456, тип=toasted, роли=95, время=19500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2005, размер=2673, тип=toasted, роли=265, время=20400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2941, размер=2802, тип=toasted, роли=71, время=13400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2043, размер=2805, тип=toasted, роли=67, время=13500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3414, размер=2871, тип=toasted, роли=224, время=23500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2572, размер=2951, тип=toasted, роли=102, время=20200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2488, размер=2988, тип=toasted, роли=57, время=25400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4662, размер=2994, тип=toasted, роли=65, время=24300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3311, размер=3015, тип=toasted, роли=60, время=22100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3178, размер=3091, тип=toasted, роли=72, время=27600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=1729, размер=3171, тип=toasted, роли=73, время=58800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4775, размер=3426, тип=toasted, роли=85, время=85600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4611, размер=3479, тип=toasted, роли=76, время=25800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2698, размер=3559, тип=toasted, роли=145, время=35000.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3521, размер=3575, тип=toasted, роли=101, время=26700.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3009, размер=3659, тип=toasted, роли=93, время=26900.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2328, размер=3846, тип=toasted, роли=266, время=29400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2610, размер=4259, тип=toasted, роли=154, время=42600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2671, размер=4313, тип=toasted, роли=102, время=30400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3252, размер=4344, тип=toasted, роли=105, время=23800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3062, размер=4586, тип=toasted, роли=96, время=24200.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2336, размер=4674, тип=toasted, роли=99, время=27000.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4769, размер=4682, тип=toasted, роли=127, время=24400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2447, размер=4970, тип=toasted, роли=130, время=25800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2387, размер=6449, тип=toasted, роли=602, время=67800.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3181, размер=6974, тип=toasted, роли=184, время=31100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4913, размер=7516, тип=toasted, роли=286, время=29100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3124, размер=7934, тип=toasted, роли=319, время=26400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3310, размер=9063, тип=toasted, роли=184, время=42500.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2545, размер=9240, тип=toasted, роли=539, время=86400.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4891, размер=9381, тип=toasted, роли=210, время=51100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=4404, размер=11201, тип=toasted, роли=270, время=38300.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3456, размер=11362, тип=toasted, роли=922, время=125100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3396, размер=15441, тип=toasted, роли=448, время=92600.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=2230, размер=21679, тип=toasted, роли=504, время=159100.00 нс
psql:2-timings/1-timings.sql:99: NOTICE:  Измерен ID=3010, размер=29281, тип=toasted, роли=928, время=197700.00 нс
DO
 storage_type | samples | avg_size_bytes | avg_roles | avg_time_ns | min_time_ns | max_time_ns 
--------------+---------+----------------+-----------+-------------+-------------+-------------
 inline       |      50 |            247 |         2 |        5844 |        3500 |       39700
 toasted      |      50 |           5250 |       185 |       37476 |       11700 |      197700
(2 rows)

  id  | jsonb_size | storage_type | roles_count | access_time_ns 
------+------------+--------------+-------------+----------------
 7579 |         75 | inline       |           1 |          39700
 1651 |         75 | inline       |           1 |           4600
 1642 |         76 | inline       |           1 |           3600
 1640 |         90 | inline       |           1 |           3600
 1612 |         96 | inline       |           1 |           3600
 1647 |        102 | inline       |           1 |           3500
 1632 |        107 | inline       |           1 |           3800
 1653 |        108 | inline       |           1 |           3800
 1619 |        110 | inline       |           1 |           3700
 1660 |        110 | inline       |           1 |           3600
 1630 |        113 | inline       |           1 |           3700
 1635 |        115 | inline       |           1 |           3800
 1641 |        116 | inline       |           1 |           3900
 1611 |        118 | inline       |           1 |           3800
 1617 |        119 | inline       |           1 |           4000
 1615 |        119 | inline       |           1 |           3900
 1648 |        119 | inline       |           1 |           3600
 1639 |        123 | inline       |           1 |           4100
 1621 |        125 | inline       |           1 |           4400
 1654 |        125 | inline       |           1 |           4300
 1628 |        125 | inline       |           1 |           4300
 1633 |        126 | inline       |           1 |           4200
 1652 |        127 | inline       |           1 |           4500
 1627 |        131 | inline       |           1 |           4500
 1622 |        131 | inline       |           1 |           5200
 1655 |        132 | inline       |           1 |           4400
 1650 |        133 | inline       |           1 |           5100
 1623 |        134 | inline       |           1 |           5100
 1610 |        138 | inline       |           1 |           4500
 1616 |        150 | inline       |           1 |           6200
 1609 |        155 | inline       |           1 |           7700
 1625 |        155 | inline       |           1 |           7600
 1643 |        163 | inline       |           1 |           7900
 1614 |        169 | inline       |           1 |           7400
 1634 |        173 | inline       |           2 |           7900
 1624 |        175 | inline       |           2 |           7900
 1646 |        182 | inline       |           2 |           8400
 1649 |        202 | inline       |           2 |           4900
 1631 |        203 | inline       |           2 |           5100
 1656 |        205 | inline       |           1 |           6500
 1620 |        205 | inline       |           2 |           5300
 1618 |        231 | inline       |           2 |           5300
 1636 |        326 | inline       |           3 |           5400
 1645 |        381 | inline       |           3 |           6100
 1629 |        681 | inline       |           7 |           6200
 1613 |        892 | inline       |           7 |           6000
 1637 |        894 | inline       |           8 |           5900
 1644 |        933 | inline       |           7 |           5900
 1638 |       1077 | inline       |          19 |           7700
 1626 |       1489 | inline       |          12 |           6100
 2395 |       2116 | toasted      |          53 |          22500
 2362 |       2132 | toasted      |          39 |          17100
 2632 |       2139 | toasted      |          41 |          11700
 4567 |       2222 | toasted      |          61 |          15300
 3200 |       2246 | toasted      |          55 |          20500
 2232 |       2249 | toasted      |          77 |          11900
 3221 |       2264 | toasted      |          38 |          17200
 3680 |       2270 | toasted      |          55 |          18900
 4064 |       2279 | toasted      |          70 |          21500
 4627 |       2320 | toasted      |          39 |          13100
 4928 |       2339 | toasted      |          70 |          12900
 3720 |       2371 | toasted      |          62 |          14600
 3509 |       2410 | toasted      |         400 |          18300
 4441 |       2413 | toasted      |          45 |          14800
 1865 |       2456 | toasted      |          95 |          19500
 2005 |       2673 | toasted      |         265 |          20400
 2941 |       2802 | toasted      |          71 |          13400
 2043 |       2805 | toasted      |          67 |          13500
 3414 |       2871 | toasted      |         224 |          23500
 2572 |       2951 | toasted      |         102 |          20200
 2488 |       2988 | toasted      |          57 |          25400
 4662 |       2994 | toasted      |          65 |          24300
 3311 |       3015 | toasted      |          60 |          22100
 3178 |       3091 | toasted      |          72 |          27600
 1729 |       3171 | toasted      |          73 |          58800
 4775 |       3426 | toasted      |          85 |          85600
 4611 |       3479 | toasted      |          76 |          25800
 2698 |       3559 | toasted      |         145 |          35000
 3521 |       3575 | toasted      |         101 |          26700
 3009 |       3659 | toasted      |          93 |          26900
 2328 |       3846 | toasted      |         266 |          29400
 2610 |       4259 | toasted      |         154 |          42600
 2671 |       4313 | toasted      |         102 |          30400
 3252 |       4344 | toasted      |         105 |          23800
 3062 |       4586 | toasted      |          96 |          24200
 2336 |       4674 | toasted      |          99 |          27000
 4769 |       4682 | toasted      |         127 |          24400
 2447 |       4970 | toasted      |         130 |          25800
 2387 |       6449 | toasted      |         602 |          67800
 3181 |       6974 | toasted      |         184 |          31100
 4913 |       7516 | toasted      |         286 |          29100
 3124 |       7934 | toasted      |         319 |          26400
 3310 |       9063 | toasted      |         184 |          42500
 2545 |       9240 | toasted      |         539 |          86400
 4891 |       9381 | toasted      |         210 |          51100
 4404 |      11201 | toasted      |         270 |          38300
 3456 |      11362 | toasted      |         922 |         125100
 3396 |      15441 | toasted      |         448 |          92600
 2230 |      21679 | toasted      |         504 |         159100
 3010 |      29281 | toasted      |         928 |         197700
(100 rows)

COPY 100

Python-скрипт (2-timings.py) - визуализация и интерпретация

Запуск скрипта

python ./2-timings/2-timings.py

Загрузка и подготовка данных

# Загружаем данные
df = pd.read_csv(file_path)
print(f"Загружено {len(df)} записей")

# Разделяем данные по типу хранения
inline_data = df[df["storage_type"] == "inline"]
toasted_data = df[df["storage_type"] == "toasted"]

• Скрипт загружает данные измерений из CSV-файла.
• Разделяет данные на две группы: встроенные (<2KB) и TOAST (>2KB).

Построение диаграммы рассеяния

# Строим точки для каждого типа хранения
plt.scatter(
    inline_data["jsonb_size"],
    inline_data["access_time_ns"],
    color="blue",
    alpha=0.7,
    label="Встроенные (<2kB)",
)

• На диаграмму наносятся точки с координатами:
  • X: размер JSONB (в байтах).
  • Y: время доступа (в наносекундах).
• Разные типы хранения выделены разными цветами (синий - inline, красный - TOAST).

Расчёт линий тренда

# Добавляем линии тренда для каждой группы
if len(inline_data) > 1 and inline_data["jsonb_size"].nunique() > 1:
    slope, intercept, r_value, _, _ = linregress(
        inline_data["jsonb_size"], inline_data["access_time_ns"]
    )

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

Визуализация "ступеньки" производительности

# Добавляем линию границы TOAST
plt.axvline(
    x=2048, color="green", linestyle="--", linewidth=2, label="TOAST граница (2kB)"
)

# Добавляем средние линии для визуализации "ступеньки"
if len(inline_data) > 0:
    avg_inline = inline_data["access_time_ns"].mean()
    plt.axhline(y=avg_inline, ...)

• Вертикальная зелёная линия отмечает границу между типами хранения (2KB).
• Горизонтальные линии показывают средние значения времени доступа для каждого типа.

Вывод статистических данных

print("\n=== СТАТИСТИКА ПО ГРУППАМ ===")
for name, group in [("Встроенные (<2kB)", inline_data), ("TOAST (>2kB)", toasted_data)]:
    print(f"\n{name}:")
    print(f"Количество записей: {len(group)}")
    ...

• Скрипт выводит подробную статистику по каждой группе данных:
  • Средний/минимальный/максимальный размеры;
  • Среднее время доступа;
  • Соотношение время/размер (нс/байт).

Вывод скрипта

Загружено 100 записей
Количество записей inline: 50
Количество записей toasted: 50
График сохранен: jsonb_access_time.png

=== СТАТИСТИКА ПО ГРУППАМ ===

Встроенные (<2kB):
Количество записей: 50
Средний размер: 247.2 байт
Минимальный размер: 75 байт
Максимальный размер: 1489 байт
Уникальных размеров: 41
Среднее время доступа: 5844.00 нс
Соотношение время/размер: 23.6427 нс/байт

TOAST (>2kB):
Количество записей: 50
Средний размер: 5249.6 байт
Минимальный размер: 2116 байт
Максимальный размер: 29281 байт
Уникальных размеров: 50
Среднее время доступа: 37476.00 нс
Соотношение время/размер: 7.1388 нс/байт

Получаемые данные и их интерпретация

График времени доступа к JSONB-данным

В результате выполнения скрипта генерируется график, отображающий зависимость времени доступа к JSONB-данным от их размера:

Выводы

Результаты проведённых измерений наглядно демонстрируют влияние механизма TOAST на скорость доступа к JSONB-данным в PostgreSQL:

1. Явная "ступенька" в производительности на границе 2KB:

   • Для данных меньше 2KB (inline хранение): среднее время доступа составляет 5844 нс;
   • Для данных больше 2KB (TOAST хранение): среднее время доступа составляет 37476 нс;
   • Разница составляет примерно 6.4 раза, что подтверждает теоретическое предположение о значительном влиянии механизма TOAST на скорость доступа.

2. Характер зависимости время/размер различается для разных типов хранения:

   • Для inline данных зависимость между размером и временем доступа относительно слабая;
   • Для TOAST данных наблюдается более выраженная линейная зависимость, особенно на больших объёмах (>10KB).

3. Диапазоны времени доступа:

   • Inline: минимум 3500 нс, максимум 39700 нс (выброс для ID=7579);
   • TOAST: минимум 11700 нс, максимум 197700 нс (для самого большого объекта 29281 байт).

4. Парадокс эффективности:

   • Несмотря на более высокое абсолютное время доступа, TOAST хранение демонстрирует лучшее соотношение время/размер: 7.14 нс/байт против 23.64 нс/байт для inline хранения;
   • Это объясняется тем, что накладные расходы на декомпрессию TOAST объектов распределяются на больший объём данных.

5. Корреляция с количеством ролей:

   • Средние характеристики inline хранения: 247 байт на 2 роли;
   • Средние характеристики TOAST хранения: 5250 байт на 185 ролей;
   • Наблюдается закономерность: время доступа растёт с увеличением количества ролей, особенно для TOAST данных.

6. Выбросы в измерениях:

   • Для inline данных заметен один значительный выброс (ID=7579, 39700 нс);
   • Для TOAST данных несколько выбросов на больших объёмах, что может быть связано с повышенной нагрузкой на систему кеширования.

Эти результаты подтверждают гипотезу о наличии "ступеньки" в производительности при переходе через порог TOAST (2KB), а также демонстрируют последующую линейную зависимость для больших объектов данных.

Рекомендации по оптимизации работы с большими JSONB-данными

1. Структурирование данных:

   • Нормализация критичных полей: вынесите часто используемые или критичные для производительности поля из JSONB в отдельные столбцы таблицы;
   • Декомпозиция больших документов: разделяйте большие JSONB-объекты на логические части, особенно если размер превышает 2KB (порог TOAST).

   -- Пример оптимизированной структуры
   CREATE TABLE actors_optimized (
       id integer PRIMARY KEY,
       actor_first_name varchar,
       actor_second_name varchar,
       first_role_year integer,  -- Вынесенное поле для быстрого доступа
       roles_count integer,      -- Денормализованный счетчик
       roles_data jsonb          -- Остальные данные о ролях
   );

2. Эффективная индексация:

   • Специализированные индексы: создавайте индексы для конкретных путей JSON, которые часто используются в условиях поиска;
   • Оптимизированные GIN-индексы: используйте соответствующий тип оператора для индексации (jsonb_ops или jsonb_path_ops);
   • Частичные индексы: для больших таблиц применяйте индексы только к релевантным записям:

   -- Индекс для часто используемого поля
   CREATE INDEX idx_first_role_year ON actors ((RolesName->'roles'->0->>'year'));
   
   -- Оптимизированный GIN-индекс для проверки вхождения (@>)
   CREATE INDEX idx_jsonb_path_ops ON actors USING GIN (RolesName jsonb_path_ops);

3. Оптимизация запросов:

   • Используйте JSONPath: предпочитайте функции JSONPath вместо последовательного применения операторов -> и ->> для сложных условий;
   • Материализованные представления: предварительно вычисляйте часто запрашиваемые данные для сложных запросов:

   -- Эффективный запрос с использованием JSONPath
   SELECT * FROM actors
   WHERE jsonb_path_exists(RolesName, '$.roles[*] ? (@.year > "2010" && @.credit == "1")');

Измерение влияния обновлений на JSONB с разными размерами данных

Общее описание и цель скрипта

Скрипт update-year.sql проводит эксперимент для сравнения влияния обновления JSONB-поля на объем базы данных в зависимости от размера данных. Основная задача - наглядно продемонстрировать разницу в поведении PostgreSQL при работе с данными, которые хранятся непосредственно в строке таблицы (inline) и данными, использующими механизм TOAST (The Oversized-Attribute Storage Technique).

Запуск скрипта

Запустим выполнение реализованных запросов из файла ./3-update-year/update-year.sql выполнив следующую команду в терминале:

psql -U postgres -d imdb -f ./3-update-year/update-year.sql | cat

Подробный разбор компонентов скрипта

1. Функция для измерения размера таблицы

CREATE OR REPLACE FUNCTION get_table_size(p_table text)
    RETURNS TABLE(
        table_size bigint,
        toast_size bigint,
        total_size bigint
    )
    AS $$

Эта функция использует системные функции PostgreSQL для получения трех ключевых метрик:

• table_size - размер основной таблицы без учета TOAST-данных.
• toast_size - размер TOAST-таблицы (если имеется).
• total_size - общий размер (основная + TOAST таблицы).

2. Выбор тестовых записей

CREATE TEMP TABLE test_actors AS
SELECT
    *
FROM (
    -- Актер с малым количеством ролей
    SELECT
        id,
        ActorFirstName,
        ActorSecondName,
        RolesName,
        'small' AS actor_type,
        jsonb_array_length(RolesName -> 'roles') AS roles_count,
        pg_column_size(RolesName) AS data_size
    FROM
        actors
    WHERE
        pg_column_size(RolesName) < 1000 -- Гарантированно inline
        AND jsonb_array_length(RolesName -> 'roles') BETWEEN 1 AND 5
    ORDER BY
        pg_column_size(RolesName)
    LIMIT 1) AS small
-- ... аналогичный код для большого актера ...

Скрипт выбирает две записи актеров:

1. Маленький актер - с данными менее 1000 байт и не более 5 ролями.
2. Большой актер - с данными более 10000 байт и не менее 200 ролями.

Функция pg_column_size() используется для точного определения реального размера JSONB-данных в байтах.

3. Функция для многократного обновления

CREATE OR REPLACE FUNCTION update_actor_role_year(p_id integer, p_iterations integer)
    RETURNS VOID
    AS $$

Эта функция:

• Принимает ID актера и количество итераций.
• В цикле меняет год первой роли между '1900' и '2023'.
• Использует jsonb_set() для изменения конкретного вложенного поля JSONB без полной замены всего документа.

4. Измерения и обновления

Скрипт выполняет несколько измерений размера таблицы:

1. Начальное состояние.
2. После 100 обновлений маленького актера.
3. После 100 обновлений большого актера.

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

5. Анализ изменений размера

WITH size_changes AS (
    SELECT
        -- Для малого актера (измерение до и после его обновления)
        m1.actor_type,
        m1.id,
        m2.table_size - m1.table_size AS table_size_change,
        -- ... код ...
    FROM 
        size_measurements m1,
        size_measurements m2
    WHERE 
        m1.measurement_point = 'before_update'
        AND m2.measurement_point = 'after_small_update'
        AND m1.actor_type = 'small'
        AND m2.actor_type = 'small'
    UNION ALL
    -- Для большого актера (измерение после обновления малого и после обновления большого)
    SELECT
        -- ... код ...
)

Здесь происходит вычисление фактических изменений размера для каждого актера с использованием CTE (Common Table Expression).

Common Table Expression (CTE) или общее табличное выражение - это мощный инструмент SQL, позволяющий создавать временные именованные наборы результатов, к которым можно обращаться в рамках основного SQL-запроса.

Основные характеристики CTE:

1. Временное существование: CTE существует только во время выполнения запроса, в котором оно определено.
2. Синтаксис: определяется с помощью ключевого слова WITH, за которым следует имя выражения и опционально список столбцов.
3. Область видимости: результаты CTE доступны только в пределах запроса, содержащего выражение WITH.

Ключевые особенности работы скрипта

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

Вывод скрипта

После запуска скрипта в консоли можно увидеть результаты измерений и обновлений:

CREATE FUNCTION
psql:3-update-year/update-year.sql:18: NOTICE:  table "test_actors" does not exist, skipping
DROP TABLE
SELECT 2
 actor_type |   id    |     name     | roles_count | jsonb_size_bytes | jsonb_size_kb | first_role_year 
------------+---------+--------------+-------------+------------------+---------------+-----------------
 small      |  139194 | Tibor Balázs |           1 |               63 |          0.06 | 2009
 large      | 2555314 | Frank Welker |        5046 |           212356 |        207.38 | 2023
(2 rows)

CREATE FUNCTION
 table_size | toast_size | total_size 
------------+------------+------------
  903233536 | 1021689856 | 1924923392
(1 row)

SELECT 2
   id   | actorfirstname | actorsecondname | roles_count 
--------+----------------+-----------------+-------------
 139194 | Tibor          | Balázs          |           1
(1 row)

psql:3-update-year/update-year.sql:118: NOTICE:  Updated small actor (ID: 139194) 100 times
DO
INSERT 0 2
   id    | actorfirstname | actorsecondname | roles_count 
---------+----------------+-----------------+-------------
 2555314 | Frank          | Welker          |        5046
(1 row)

psql:3-update-year/update-year.sql:143: NOTICE:  Updated large actor (ID: 2555314) 100 times
DO
INSERT 0 2
 actor_type |   id    | Изменение основной таблицы (байт) | Изменение TOAST-таблицы (байт) | Общее изменение (байт) | Общее изменение (KiB) 
------------+---------+-----------------------------------+--------------------------------+------------------------+-----------------------
 large      | 2555314 |                                 0 |                        4169728 |                4169728 |               4072.00
 small      |  139194 |                                 0 |                              0 |                      0 |                  0.00
(2 rows)

Результаты и выводы

Приведём полученную из вывода скрипта информацию о размерах таблиц и изменениях в более удобном виде:

actor_type	id	Изменение основной таблицы (байт)	Изменение TOAST-таблицы (байт)	Общее изменение (байт)	Общее изменение (КБ)
large	2555314	0	4169728	4169728	4072.00
small	139194	0	0	0	0.00

1. Для актера с малыми данными (inline):

   • Отсутствие изменений в размере основной таблицы;
   • TOAST-таблица не изменилась, что ожидаемо, ведь данные хранятся непосредственно в строке.

2. Для актера с большими данными (TOAST):

   • Значительное увеличение размера TOAST-таблицы из-за особенности механизма TOAST:
     • При каждом обновлении создается новая версия TOAST-данных;
     • Старая версия помечается как недействительная, но место не освобождается;
     • Для освобождения места требуется выполнение операции VACUUM.

3. Общие выводы:

   • Частые обновления больших JSONB-полей могут вызвать существенное "разрастание" (bloating) TOAST-таблиц;
   • Для баз данных с активными обновлениями больших JSON-данных важно регулярно выполнять VACUUM;
   • При проектировании структуры данных стоит выносить часто обновляемые поля в отдельные колонки, если это возможно.

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

Заключение

В ходе выполнения практического задания были проведены комплексные исследования особенностей работы с JSONB-данными в PostgreSQL и получены следующие ключевые выводы:

1. Производительность доступа к JSONB напрямую зависит от размера данных:

   • Для данных до 2KB (inline хранение) среднее время доступа составило около 5844 наносекунд;
   • Для данных более 2KB (TOAST хранение) время доступа увеличивается до 37476 наносекунд (~6.4 раз медленнее);
   • После порога в 2KB наблюдается линейная зависимость скорости от размера данных.

2. Механизм TOAST значительно влияет на использование дискового пространства при обновлениях:

   • При обновлении небольших JSONB-объектов (inline) изменение размера базы незначительно или отсутствует вовсе;
   • При обновлении больших JSONB-объектов (TOAST) наблюдается существенное увеличение размера TOAST-таблицы;
   • Это происходит из-за стратегии копирования при запросе: старая версия остаётся в TOAST-таблице до выполнения VACUUM.

3. Различные операторы и функции для работы с JSONB имеют разную производительность:

   • Оператор включения @> с индексом GIN(jsonb_path_ops) работает наиболее эффективно;
   • Обращение к глубоко вложенным полям через операторы -> и ->> может быть медленным для больших документов;
   • JSONPath обеспечивает более эффективный и декларативный способ работы с вложенными структурами.

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