JSONB в PostgreSQL: документ внутри реляционной модели
Как сочетать обычные столбцы и JSONB в PostgreSQL и подобрать индекс под поиск по свойствам.
- Столбцы таблицыИдентификатор, связи и обязательные поля
- Документ JSONBХарактеристики с переменным набором ключей
- Фильтр и индексИндекс под оператор, проверка через EXPLAIN
У оборудования есть общие данные — серийный номер и модель, — а характеристики зависят от производителя. Сотня почти пустых столбцов усложняет таблицу, но переносить весь объект в JSON тоже неудобно. JSONB позволяет оставить постоянные поля обычными столбцами, а меняющийся набор свойств хранить рядом. Посмотрим, как это влияет на запросы и индексы.
Отделяем постоянное от переменного
CREATE TABLE devices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
serial_no text NOT NULL UNIQUE,
properties jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO devices(serial_no, properties)
VALUES ('LAB-001', '{"vendor":"Acme","channels":4}');
SELECT serial_no FROM devices
WHERE properties @> '{"vendor":"Acme"}'::jsonb;Идентификатор и уникальность серийного номера остаются ограничениями таблицы. Это сохраняет понятную модель данных: JSONB дополняет схему, а не отменяет её. Перед добавлением произвольного поля определите, кто проверяет его тип и допустимые значения.
Индекс следует за запросом
CREATE INDEX devices_properties_gin ON devices USING gin(properties);
SELECT properties ->> 'vendor' AS vendor FROM devices;GIN полезен для поддерживаемых им операторов, в том числе поиска по содержимому. Выражение с извлечением текста и сравнением — другая форма запроса; наличие GIN не означает, что любой фильтр по JSON автоматически ускорится. Для часто используемого выражения рассмотрите отдельный индекс и проверьте план на своих данных.
Когда лучше отдельный столбец
Если свойство участвует во внешних ключах, постоянно сортируется или имеет строгий бизнес-смысл, отдельный столбец обычно проще сопровождать. Сравните несколько типичных операций: запись целого документа, фильтрацию, отчёт и исправление ошибочного значения. Начните с небольшой модели и измерений, а не с переноса всех данных в JSON.
Добавьте устройства с разными свойствами и выполните поиск по производителю. Проверьте, что происходит, если поля нет. На маленькой таблице PostgreSQL может выбрать последовательное чтение вместо индекса — это нормальное решение планировщика, а не ошибка JSONB.