Чуть больше подробностей про внутренности PostgreSQL можно найти в других статьях.
Мне показалось это полезным:
- JSON Types
- JSON Functions and Operators
- JSON[b] в Postgres: Пора великого объединения (Олег Бартунов, Postgres Professional)
- SQL/JSON в PostgreSQL: настоящее и будущее / Олег Бартунов (Postgres Professional)
- hstore
- Документация к Postgres Pro Enterprise 15.2.1
- Функции и операторы JSON
- about JSON in PostgreSQL
"JSON" типы данных
Так исторически сложилось, что в PostgreSQL есть несколько типов данных для JSON.
hstore
- Это не JSON. Позволяет хранить набор пар key:value
внутри одного поля базы данных. Поддерживает индексирование. Нет массивов и вложенности.
json
- Хранит JSON в виде текста. Не оптимально для часто меняющихся данных или если выполняется много запросов к полям JSON. Не поддерживает индексирование.
jsonb
- (binary json) Наследник hstore
- "вложенный" hstore. Бинарное хранение. Поддерживает индексирование. В отличии от hstore поддерживает вложенные объекты и массивы. Не сохраняет форматирование (удаляет пробелы) и поддерживает только уникальные ключи в JSON (сохранен будет последний). Ключи отсортированы.
number в jsonb реализован как numeric. Это позволяет обойти ограничения 8 байт.
jsonpath
- введен для поддержки jsonpath. Это двоичное представление разобранного jsonpath выражения.
Главная проблема JSON в PostgreSQL
json и jsonb реализации хранят данные в поле таблицы опираясь на стандартные механизм TOAST в случае большого значения ( больше 2kb ).
-
TOAST перезаписывает и читает данные целиком
-
TOAST был сделан для атомарных типов данных. Нет информации про внутреннюю структуру
Как результат:
-
Любое обновление JSON приведет в полной перезаписи поля целиком.
-
Повышенная нагрузка на VACUUM и WAL при частых изменен иях в JSON.
-
Линейная зависимость времени доступа к любому ключу от размера json. Из-за того что нужно читать и разжимать все TOAST блоки.
Алгоритм применения обновлений если JSON не попал в TOAST:
- в WAL будут записаны операции обновления для таблицы
- будет создана новая строка с обновленными данными в таблице
- старая строка будет помечена как "dead tuples" для VACUUM
Алгоритм применения обновлений если JSON находится в TOAST:
- в WAL будут записаны операции обновления для таблицы и для TOAST, включая весь JSON
- В TOAST будет создана полная копия всего JSON с обновлениями
- Старые версии JSON в TOAST будут помечены как "dead tuples"
- будет создана новая строка с обновленными данными в таблице
- старая строка будет помечена как "dead tuples"
Маленькое обновление в больших JSON приводит к потере производительности, распуханию как таблиц так и WAL, а также к большой нагрузке на VACUUM.