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.
-
Кодовая страница - ISO 8859-1.
-
Используемые разделители:
HT (0x09)- horizontal_tab,LF (0x0A)- line_feed.Для полей используется один или несколько разделителей HT, для ролей актера один LF, для актеров два LF.
-
Правила синтаксического разбора поля
Titles:-
первый атрибут
titleили"title"= название роли, переносим в json с одинарными кавычками или без кавычек; -
второй атрибут
(year)= год роли, первые круглые скобки, может быть(????)- дата неизвестна; -
третий атрибут/необязательный (type1) = тип материала, все что после года в круглых скобках:
(V)- видео или клип;(TV)- для телевидения;(VG)- для видео игры;(archive footage)- архивные или ранние кадры;(uncredited)– не зарегистрирован или не указан в титрах;(voice)- озвучка или закадровый голос.
Может иметь несколько атрибутов одновременно, тогда следует именовать ключи как
type1,type2,type3, т.к. в jsonb все ключи должны быть разными; -
четвертый атрибут/необязательный
{series name}= название серии в сериале: в фигурных скобках,#xx.yy= номер_сезона/номер_серии; -
пятый атрибут/необязательный
(as character)= пояснение имени героя: находится в скобках перед именем героя, добавляем в json к ключу"character name"в конце как есть в скобках; -
шестой атрибут/необязательный
[character name]= имя героя: в квадратных скобках; -
седьмой атрибут/необязательный
<credit>= номер в титрах: в треугольных скобках.
-
Реальные данные неидеальны, файл с актерами тоже, т.о. есть строки, которые не описываются диаграммой разбора. Это необходимо учесть.
Примеры исключений:
- Внутри роли разделители пробелы.
- Для поля
nameможет быть только имя - оставляем фамилию пустой. - Имена, фамилии включают скобки и разные странные символы - отставляем как есть.
- Поле
(as character)может повторяться как(also as character)- игнорировать или добавить ещеtype. (2014/III)- замена на(2014).(????)- замена на(0000).(????/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" } ] }
- Составить 4-5 запросов с использованием api postgresql для jsonb:
- используя проверки на вхождение и существование jsonb;
- используя обращение по индексу к элементам jsonb;
- используя язык jsonpath;
- используя функции для типа jsonb.
- Измерить время доступа к полю jsonb для каждой строчки (в виде таблицы или графика). Оценить влияние длины строки на скорость доступа (ожидается ступенька до 2kB, после линейная зависимость). Для этого следует измерить время чтения year первой роли актера (explain analyze). Для точности оценки важно учитывать хранимую длину (jsonb хранится в сжатой форме). Как можно это влияние уменьшить?
- Составить запрос на изменение year у первой роли актера. Сравнить изменение объема БД для актера с малым кол-вом ролей и актера с большим количеством ролей (toasted roles).
- Разбор исходного текста можно производить как с использованием хранимой процедуры plpython3u, так и любым клиентом (например, python, C#, java…). Для хранимой процедуры есть ограничение на объем текста для вставки.
- Не предполагается использование промежуточного формата файла при загрузке (например, csv).
- Для построения графика можно использовать не все данные, но выборка должна быть представительной (например, 50 для начальной ступеньки и 50 для линейной части).
- При построении графика следует фильтровать случайные выбросы (изменение нагрузки на сервер), желательно производить усреднение по значениям графика.
- Денормализация
- Json
PostgreSQL поддерживает два типа для хранения JSON-данных, которые существенно различаются по своей реализации:
-
Формат хранения:
- json хранит точную текстовую копию введенного JSON как есть, включая все пробелы, форматирование и порядок ключей;
- jsonb хранит данные в бинарном декомпозированном формате, что позволяет быстро обращаться к отдельным элементам.
-
Эффективность обработки:
- json требует повторного парсинга при каждом запросе, что снижает производительность;
- jsonb хранится уже в предварительно разобранном виде, что делает все операции поиска и обработки значительно быстрее.
-
Порядок ключей:
- json сохраняет порядок ключей в том виде, в каком они были введены;
- jsonb не сохраняет порядок ключей (они сортируются автоматически для оптимизации доступа).
-
Индексация:
- json не поддерживает индексацию, что делает поиск по содержимому неэффективным;
- jsonb поддерживает специализированные GIN-индексы, позволяющие быстро извлекать данные по ключам и значениям.
-
Дублирующиеся ключи:
- json сохраняет все дублирующиеся ключи как есть;
- jsonb сохраняет только последнее значение для каждого дублирующегося ключа.
Таким образом, json подходит для случаев, когда требуется точное сохранение исходного формата, а jsonb - когда важны производительность запросов и возможность эффективного поиска.
JSONB поддерживает несколько типов индексации, что делает его мощным инструментом для работы со слабоструктурированными данными:
-
GIN (Generalized Inverted Index) - наиболее эффективный тип индексации для JSONB с двумя вариантами операторов:
jsonb_ops(по умолчанию) - индексирует каждый ключ и значение, поддерживает операторы@>,?,?&,?|;jsonb_path_ops- оптимизирован специально для оператора включения (@>), более компактен и быстрее для таких запросов:
CREATE INDEX idx_actors_roles ON actors USING GIN (RolesName jsonb_path_ops);
-
B-tree индексы - могут использоваться для сравнения JSONB-полей целиком:
CREATE INDEX idx_json_equality ON actors (RolesName);
-
Функциональные индексы - для индексации значений конкретных ключей:
CREATE INDEX idx_year ON actors ((RolesName->'roles'->0->>'year'));
-
Частичные индексы - для индексирования только строк, удовлетворяющих определенному условию:
CREATE INDEX idx_actors_with_type ON actors USING GIN (RolesName) WHERE jsonb_path_exists(RolesName, '$.roles[*].type1');
-
Комбинированные индексы - для одновременной индексации нескольких полей:
CREATE INDEX idx_actors_combined ON actors (ActorFirstName, ActorSecondName, (RolesName->'roles'->0->>'year'));
Правильный выбор типа индекса существенно влияет на производительность запросов к JSONB-данным.
JSONPath - это специализированный язык запросов для работы с JSON-данными, добавленный в PostgreSQL 12 и стандартизированный в SQL/JSON:
-
Синтаксис и основные элементы:
$- корневой элемент JSON-документа;.keyили."key with spaces"- доступ к полю объекта;[*]- обращение ко всем элементам массива;?()- фильтр для выборки элементов;@- ссылка на текущий обрабатываемый элемент.
-
Возможности языка:
- Сложная навигация по вложенным структурам;
- Арифметические операции с извлекаемыми числовыми значениями;
- Логические операторы (
&&,||,!); - Операторы сравнения (
==,!=,<,<=,>,>=); - Поиск по шаблону с использованием регулярных выражений (
like_regex).
-
Основные функции для работы с jsonpath:
jsonb_path_exists(json, path)- проверяет, существуют ли элементы, соответствующие пути;jsonb_path_query(json, path)- извлекает все значения, соответствующие пути;jsonb_path_query_array(json, path)- возвращает результаты в виде JSON-массива;jsonb_path_query_first(json, path)- возвращает первый соответствующий элемент.
-
Пример использования:
SELECT ActorFirstName, ActorSecondName FROM actors WHERE jsonb_path_exists( RolesName, '$.roles[*] ? (@.year >= "2010" && @.credit == "1")' );
Этот запрос находит актеров, сыгравших главную роль в фильмах 2010 года и позже.
JSONPath предоставляет более мощный и декларативный способ работы с JSON-данными по сравнению со стандартными операторами доступа к полям (->, ->>) и значительно упрощает сложные запросы к вложенным структурам.
TOAST (The Oversized-Attribute Storage Technique) - это механизм PostgreSQL для эффективного хранения и доступа к большим значениям атрибутов:
-
Принцип работы:
- PostgreSQL хранит строки в страницах фиксированного размера (обычно 8 KB);
- Когда значение атрибута слишком велико (превышает определенный порог), оно "тостируется" - перемещается во внешнюю TOAST-таблицу;
- В основной таблице остается только небольшой указатель на TOAST-значение.
-
Пороговые значения:
- Для JSONB порог TOAST составляет около 2 KB;
- Данные, превышающие этот порог, автоматически перемещаются в TOAST-таблицы.
-
Стратегии хранения TOAST-данных:
В стратегиях используются два ключевых механизма:
- Сжатие - процесс уменьшения размера данных с применением алгоритма LZ, который PostgreSQL использует для TOAST-данных. Сжатие позволяет существенно уменьшить занимаемое пространство, особенно для текстовых данных, но требует дополнительных вычислительных ресурсов при каждой операции чтения и записи.
- Внешнее хранение - механизм перемещения больших значений из основной таблицы во вспомогательную TOAST-таблицу. При этом в основной строке таблицы остается только небольшой указатель (16 байт), который ссылается на соответствующую запись в TOAST-таблице. Это позволяет поддерживать эффективное хранение больших объектов, но добавляет дополнительное обращение к диску при доступе к данным.
Сами стратегии определяют, какие механизмы будут использоваться для TOAST-данных:
PLAIN- предотвращает как сжатие, так и внешнее хранение. Это единственная возможная стратегия для столбцов с типами данных, не поддерживающими TOAST;EXTENDED- позволяет использовать как сжатие, так и внешнее хранение (по умолчанию для большинства TOAST-поддерживаемых типов данных). Сначала предпринимается попытка сжатия, а затем, если строка все еще слишком большая, применяется внешнее хранение;EXTERNAL- разрешает внешнее хранение, но не сжатие. ИспользованиеEXTERNALделает операции с подстроками на широких текстовых и bytea столбцах быстрее (за счет увеличения занимаемого пространства), поскольку эти операции оптимизированы для извлечения только необходимых частей внешнего значения, когда оно не сжато;MAIN- разрешает сжатие, но не внешнее хранение. (Фактически, внешнее хранение всё равно будет выполнено для таких столбцов, но только как последнее средство, когда нет другого способа уменьшить строку до размера, позволяющего разместить её на странице).
-
Оптимизация работы с 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 - для форматированного вывода статистики в консоль.
Скрипт организован по принципу поэтапной обработки данных:
- Подключение к БД - с оптимизированными параметрами PostgreSQL.
- Чтение файла - загрузка файла
actors.list.txtв память. - Разделение данных - разбиение файла на блоки актеров по пустым строкам: для роли актёра используеться один разделитель
LF, для актёров - дваLF. - Параллельная обработка - многопоточный парсинг данных об актерах и их ролях.
- Пакетная вставка - эффективная загрузка обработанных данных в БД.
- Сбор и вывод статистики - анализ производительности и вывод результатов.
- 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() - основная функция, координирующая весь процесс.
- Предкомпиляция регулярных выражений - для более быстрого парсинга.
- Многопоточная обработка - использование
ThreadPoolExecutorс оптимальным количеством потоков, зависящим от числа ядер процессора. - Пакетные операции - группировка операций вставки данных.
- Оптимизация памяти - своевременное освобождение памяти после использования объемных данных.
- Настройка 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 содержит массив объектов, каждый из которых описывает отдельную роль актера со всеми атрибутами, указанными в задании (название, год, персонаж и т.д.).
Скрипт отслеживает время выполнения каждого этапа и в конце выводит детальную статистику:
- Общее время выполнения и распределение по этапам.
- Производительность (актеров и ролей в секунду).
- Соотношение количества ролей к актерам.
В скрипте перед массовой вставкой данных выполняется полная очистка таблицы 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)
Запустим выполнение реализованных запросов из файла ./1-queries/queries.sql выполнив следующую команду в терминале:
psql -U postgres -d imdb -f ./1-queries/queries.sql | catSELECT
ActorFirstName,
ActorSecondName
FROM
actors
WHERE
RolesName @> '{"roles": [{"character name": "Detective"}]}'
LIMIT 10;Описание:
- Оператор
@>(содержит/включает) - проверяет, является ли левый JSONB-документ супермножеством правого. - Запрос ищет записи, где в массиве
rolesсуществует хотя бы один объект с полем"character name"равным"Detective". - Преимущество: использует GIN-индекс по JSONB, что делает поиск эффективным.
LIMIT 10ограничивает вывод первыми 10 совпадениями.
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'- получает название первой роли из списка.
SELECT
COUNT(*)
FROM
actors
WHERE
RolesName -> 'roles' -> 0 ? 'type1';;Описание:
- Оператор
?- проверяет существование ключа в JSONB-объекте (без учета его значения). RolesName->'roles'->0- обращение к первой роли в массиве ролей.- Запрос подсчитывает количество актеров, у которых в первой роли указан тип материала (type1).
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- задает псевдонимы для выходных столбцов.- Выводит имена актеров и информацию об их первой роли (название и год).
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 актеров с наибольшим количеством ролей в базе данных.
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 года и позже.
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".
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-объект.
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)
В директории ./2-timings располагаются два скрипта: 1-timings.sql и 2-timings.py.
Представленные скрипты реализуют комплексное исследование влияния размера JSONB-полей на скорость доступа к ним в PostgreSQL, фокусируясь на важной граничной точке в 2KB (порог TOAST механизма).
psql -U postgres -d imdb -f ./2-timings/1-timings.sql | catCREATE 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'.
Создаётся таблица jsonb_access_metrics для хранения результатов измерений:
id- идентификатор записи.jsonb_size- размер JSONB в байтах.storage_type- тип хранения (inline/toasted).roles_count- количество ролей в массиве.access_time_ns- измеренное время доступа в наносекундах.
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()для микросекундной точности измерений. - Возвращает среднее время доступа в наносекундах.
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);
-- ... код ...- С помощью анонимного блока выполняется цикл по всем тестовым записям.
- Для каждой записи:
- Измеряется время доступа с 10 итерациями для уменьшения случайных колебаний;
- Подсчитывается количество ролей актёра;
- Результаты сохраняются в таблицу метрик;
- Выводится информационное сообщение о прогрессе с помощью
RAISE NOTICE.
-
Выполняется агрегированный запрос, группирующий данные по типу хранения:
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/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-данным от их размера:
Результаты проведённых измерений наглядно демонстрируют влияние механизма TOAST на скорость доступа к JSONB-данным в PostgreSQL:
-
Явная "ступенька" в производительности на границе 2KB:
- Для данных меньше 2KB (inline хранение): среднее время доступа составляет 5844 нс;
- Для данных больше 2KB (TOAST хранение): среднее время доступа составляет 37476 нс;
- Разница составляет примерно 6.4 раза, что подтверждает теоретическое предположение о значительном влиянии механизма TOAST на скорость доступа.
-
Характер зависимости время/размер различается для разных типов хранения:
- Для inline данных зависимость между размером и временем доступа относительно слабая;
- Для TOAST данных наблюдается более выраженная линейная зависимость, особенно на больших объёмах (>10KB).
-
Диапазоны времени доступа:
- Inline: минимум 3500 нс, максимум 39700 нс (выброс для ID=7579);
- TOAST: минимум 11700 нс, максимум 197700 нс (для самого большого объекта 29281 байт).
-
Парадокс эффективности:
- Несмотря на более высокое абсолютное время доступа, TOAST хранение демонстрирует лучшее соотношение время/размер: 7.14 нс/байт против 23.64 нс/байт для inline хранения;
- Это объясняется тем, что накладные расходы на декомпрессию TOAST объектов распределяются на больший объём данных.
-
Корреляция с количеством ролей:
- Средние характеристики inline хранения: 247 байт на 2 роли;
- Средние характеристики TOAST хранения: 5250 байт на 185 ролей;
- Наблюдается закономерность: время доступа растёт с увеличением количества ролей, особенно для TOAST данных.
-
Выбросы в измерениях:
- Для inline данных заметен один значительный выброс (ID=7579, 39700 нс);
- Для TOAST данных несколько выбросов на больших объёмах, что может быть связано с повышенной нагрузкой на систему кеширования.
Эти результаты подтверждают гипотезу о наличии "ступеньки" в производительности при переходе через порог TOAST (2KB), а также демонстрируют последующую линейную зависимость для больших объектов данных.
-
Структурирование данных:
- Нормализация критичных полей: вынесите часто используемые или критичные для производительности поля из 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 -- Остальные данные о ролях );
-
Эффективная индексация:
- Специализированные индексы: создавайте индексы для конкретных путей 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);
-
Оптимизация запросов:
- Используйте JSONPath: предпочитайте функции JSONPath вместо последовательного применения операторов
->и->>для сложных условий; - Материализованные представления: предварительно вычисляйте часто запрашиваемые данные для сложных запросов:
-- Эффективный запрос с использованием JSONPath SELECT * FROM actors WHERE jsonb_path_exists(RolesName, '$.roles[*] ? (@.year > "2010" && @.credit == "1")');
- Используйте JSONPath: предпочитайте функции JSONPath вместо последовательного применения операторов
Скрипт 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 | catCREATE 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 таблицы).
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
-- ... аналогичный код для большого актера ...Скрипт выбирает две записи актеров:
- Маленький актер - с данными менее 1000 байт и не более 5 ролями.
- Большой актер - с данными более 10000 байт и не менее 200 ролями.
Функция pg_column_size() используется для точного определения реального размера JSONB-данных в байтах.
CREATE OR REPLACE FUNCTION update_actor_role_year(p_id integer, p_iterations integer)
RETURNS VOID
AS $$Эта функция:
- Принимает ID актера и количество итераций.
- В цикле меняет год первой роли между '1900' и '2023'.
- Использует
jsonb_set()для изменения конкретного вложенного поля JSONB без полной замены всего документа.
Скрипт выполняет несколько измерений размера таблицы:
- Начальное состояние.
- После 100 обновлений маленького актера.
- После 100 обновлений большого актера.
Все измерения сохраняются в таблице size_measurements для последующего анализа.
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:
- Временное существование: CTE существует только во время выполнения запроса, в котором оно определено.
- Синтаксис: определяется с помощью ключевого слова
WITH, за которым следует имя выражения и опционально список столбцов. - Область видимости: результаты CTE доступны только в пределах запроса, содержащего выражение
WITH.
- Метрики размера: скрипт отслеживает не только общий размер, но и отдельно размеры основной и TOAST-таблиц, что важно для понимания механизма работы с большими данными.
- Многократное обновление: 100 итераций обновления гарантируют, что эффект будет заметен и статистически значимым.
- Целенаправленный выбор записей: скрипт тщательно выбирает актеров с разными характеристиками для демонстрации различий.
После запуска скрипта в консоли можно увидеть результаты измерений и обновлений:
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 |
-
Для актера с малыми данными (inline):
- Отсутствие изменений в размере основной таблицы;
- TOAST-таблица не изменилась, что ожидаемо, ведь данные хранятся непосредственно в строке.
-
Для актера с большими данными (TOAST):
- Значительное увеличение размера TOAST-таблицы из-за особенности механизма TOAST:
- При каждом обновлении создается новая версия TOAST-данных;
- Старая версия помечается как недействительная, но место не освобождается;
- Для освобождения места требуется выполнение операции
VACUUM.
- Значительное увеличение размера TOAST-таблицы из-за особенности механизма TOAST:
-
Общие выводы:
- Частые обновления больших JSONB-полей могут вызвать существенное "разрастание" (bloating) TOAST-таблиц;
- Для баз данных с активными обновлениями больших JSON-данных важно регулярно выполнять
VACUUM; - При проектировании структуры данных стоит выносить часто обновляемые поля в отдельные колонки, если это возможно.
Этот эксперимент наглядно демонстрирует одну из важных особенностей работы с JSONB в PostgreSQL и помогает понять, почему частые обновления больших JSON-документов могут вызывать проблемы с производительностью и дисковым пространством.
В ходе выполнения практического задания были проведены комплексные исследования особенностей работы с JSONB-данными в PostgreSQL и получены следующие ключевые выводы:
-
Производительность доступа к JSONB напрямую зависит от размера данных:
- Для данных до 2KB (inline хранение) среднее время доступа составило около 5844 наносекунд;
- Для данных более 2KB (TOAST хранение) время доступа увеличивается до 37476 наносекунд (~6.4 раз медленнее);
- После порога в 2KB наблюдается линейная зависимость скорости от размера данных.
-
Механизм TOAST значительно влияет на использование дискового пространства при обновлениях:
- При обновлении небольших JSONB-объектов (inline) изменение размера базы незначительно или отсутствует вовсе;
- При обновлении больших JSONB-объектов (TOAST) наблюдается существенное увеличение размера TOAST-таблицы;
- Это происходит из-за стратегии копирования при запросе: старая версия остаётся в TOAST-таблице до выполнения
VACUUM.
-
Различные операторы и функции для работы с JSONB имеют разную производительность:
- Оператор включения
@>с индексомGIN(jsonb_path_ops)работает наиболее эффективно; - Обращение к глубоко вложенным полям через операторы
->и->>может быть медленным для больших документов; - JSONPath обеспечивает более эффективный и декларативный способ работы с вложенными структурами.
- Оператор включения
PostgreSQL с его поддержкой JSONB предоставляет мощный инструментарий для работы со слабоструктурированными данными, однако для достижения оптимальной производительности необходимо понимать внутренние механизмы хранения и обработки таких данных и применять соответствующие методы оптимизации.