Как использовать ограничения JSON при работе с PostgreSQL
Ранее в блоге на Хабре мы рассказывали о развитии нашего продукта — биллинга для операторов связи «Гидра», а также рассматривали вопросы работы с инфраструктурой и использования новых технологий. К примеру, мы рассмотрели плюсы Clojure и ситуации, когда стоит и не стоит использовать MongoDB.
Сегодня речь пойдет о работе с JSON, и в частности, о применении ограничений. Интересный материал на эту тему опубликовал в своем блоге разработчик Магнус Хагандер (Magnus Hagander) — мы представляем вашему вниманию главные мысли этого материала.
Хагандер пишет, что в ходе общения в кулуарах одной из конференций задумался о том, можно ли одновременно использовать плюсы SQL и NoSQL баз данных. В частности, собеседники спрашивали разработчика о возможности применения расширенных ограничений СУБД PostgreSQL. «Если думаешь, что это может сработать в конкретном случае, скорее всего так и будет», — убежден Хагандер.
Однако если в случае уникальных ключей UNIQUE или проверяющих ограничений все довольно просто, то при работе с исключающими ограничениями все далеко не так просто.
Кроме технической стороны безусловно стоит вопрос, стоит ли вообще этим заниматься? Чем больше ограничений добавлено в JSON-данные, тем более структурированными они становятся. С другой стороны есть базы данных, в которых главным преимуществом является наличие динамических схем, однако в них все еще требуются ключевые индексы и ограничения (в отличии от PostgreSQL, где бинарный JSONB является неструктурированным даже после индексации).
В PostgreSQL ключи и ограничения можно определять как для колонок, так и непосредственно для любых выражений, при условии что они неизменяемы — результат зависит только от входных значений, но не от внешних по отношению к выражению признаков. Это так же применимо к JSONB.
Рассмотрим стандартную таблицу, содержащую JSON:
postgres=# CREATE TABLE jsontable (j jsonb NOT NULL);
CREATE TABLE
postgres=# CREATE INDEX j_idx ON jsontable USING gin(j jsonb_path_ops);
CREATE INDEX
Конечно такое объявление таблицы только с JSONB-полем на практике очень редко оказывается хорошей идей. Чаще всего в реальности существует больше информации, и нужно не одно поле JSONB —, но для примера используем именно этот вариант.
Создавая стандартные обратные индексы, используя jsonb_path_ops, можно максимально эффективно получить полностью неструктурированное индексирование в JSONB. Этот индекс не будет использоваться в текущем примере, однако, в реальной разработке — это одна из главных причин применения JSONB. Добавим некоторую информацию в виде регистрационных записей для демонстрации ограничений. Для примера воспользуемся полуфиксированной структурой (semi-fixed schema). Кроме того, в качестве ключа сортировки здесь используется идентификатор пользователя UUID — обычно так и поступают:
postgres=# INSERT INTO jsontable (j) VALUES ($${
"uuid": "4e9cf085-09a5-4b4f-bc99-bde2d2d51f41",
"start": "2015-03-08 10:00",
"end": "2015-03-08 11:00",
"title": "test"
}$$);
INSERT 0 1
Первое, на что следует взглянуть, — есть ли возможность проверить уникальность поля uuid. Этот идентификатор должен быть уникальным во всей таблице. Однако, как показывает практика, это требование не всегда соблюдается, а значит, чтобы убедиться в отсутствии дубликатов, необходимо использовать ограничение. Все довольно просто:
postgres=# CREATE UNIQUE INDEX j_uuid_idx ON jsontable(((j->>'uuid')::uuid));
CREATE INDEX
Здесь по извлеченному значению поля UUID создаётся индекс (с помощью уникального индекса, основанного на B-дереве). Этот индекс можно использовать как для поиска по ключу, так и для устранения дублирования ключей. С помощью команды j->>'uuid' извлекается текстовое значение поля uuid, затем с помощью команды ::uuid осуществляется преобразование во встроенный тип уникальных идентификаторов.
В принципе, можно напрямую создать ограничение на текстовое поле, но гораздо эффективнее использовать для этого UUID, поскольку он обрабатывается как 128-битное целое число.
postgres=# INSERT INTO jsontable (j) VALUES ($${"uuid": "4e9cf085-09a5-4b4f-bc99-bde2d2d51f41", "start": "2015-03-08 11:00", "end": "2015-03-08 12:00", "title": "test2"}$$);
ERROR: duplicate key value violates unique constraint "j_uuid_idx"
DETAIL: Key (((j ->> 'uuid'::text)::uuid))=(4e9cf085-09a5-4b4f-bc99-bde2d2d51f41) already exists.
Остается еще одна проблема — отсутствует проверка существования данного поля. В таблицу можно вставить записи, в которых просто не будет поля UUID. Это происходит из-за того что оператор →> по умолчанию возвращает NULL, что не вызывает нарушение уникальности (поскольку один NULL не равен другому NULL). Если требуется устранить этот недостаток, то можно реализовать проверочное ограничение CHECK:
postgres=# ALTER TABLE jsontable ADD CONSTRAINT uuid_must_exist CHECK (j ? 'uuid');
ALTER TABLE
С этим ограничением больше нельзя будет вставить в таблицу записи без поля UUID, а создание уникального индекса на предыдущем этапе обеспечивает отсутствие дубликатов. Преобразование к типу UUID позволяет обеспечивать корректность формата данных. Этот набор индексов и ограничений повторяет функциональность классической колонки определённой как uuid NOT NULL UNIQUE.
Однако существуют и более сложные исключающие ограничения — именно поэтому в качестве примера была выбрана регистрационная запись.
Основная идея здесь заключается в невозможности существования перекрывающих записей в таблице при реализованных ограничениях. Для обычных реляционных баз данных реализовать такой механизм просто — нужно лишь создать обычное исключающее ограничение.
Для JSON-данных можно применить похожий на изложенный выше метод реализации ограничений на основе функции извлечения. Однако, существует одна проблема — можно создавать ограничения для выражений, только если они неизменны, а преобразование текста к временным меткам изменяемо.
Причиной изменяемости преобразования текста к временным меткам является преобразование величин, которые зависят от внешних значений, например:
postgres=# SELECT 'today'::timestamp;
timestamp
---------------------
2016-03-08 00:00:00
(1 row)
Это хороший пример изменяемости такого преобразования, поскольку значение будет изменяться каждый день. А значение, которое используется в качестве индекса, не должно быть изменено (до явного обновления значения конечно же).
Если точно известно, что в базе не содержится таких типов данных, то использование функции скорее всего будет неизменным. Если это точно известно, то можно создать небольшую интерфейсную функцию, которая будет преобразовывать выше указанное выражение в неизменное:
postgres=# CREATE FUNCTION immutable_tstamp (t text) RETURNS timestamp LANGUAGE sql IMMUTABLE AS $$SELECT t: timestamptz AT TIME ZONE 'UTC'$$;
CREATE FUNCTION
Эта функция реализует неизменяемое преобразование к временной метке, которая жестко записана во временной зоне UTC. Кроме того, имеет смысл реализовать еще и ограничение проверки, которое позволит убедиться в том, что в данных содержатся только неизменяемые значения,
На следующем шаге функция объединяется с функцией tsrange () — это позволяет создать основанное на выражении исключающее ограничение, которое обеспечивает отсутствие перекрывающих записей. Данная задача решается путем пошагового извлечения записей времени начала и окончания с помощью функции неизменного преобразования. Затем для создания действительных временных меток и передачи их в исключающее ограничение с помощью оператора перекрытия (&&) вызывается функция tsrange.
postgres=# ALTER TABLE jsontable
ADD CONSTRAINT overlapping_times
EXCLUDE USING gist(
tsrange(
immutable_tstamp(j->>'start'),
immutable_tstamp(j->>'end')
) WITH &&
);
ALTER TABLE
В результате мы запретили добавление записей с перекрывающимися полями времени:
postgres=# insert into jsontable (j) values ($${
"uuid": "4e9cf085-09a5-4b4f-bc99-bde2d2d51f43",
"start": "2015-03-08 10:30",
"end": "2015-03-08 11:30",
"title": "test"
}$$);
ERROR: conflicting key value violates exclusion constraint "overlapping_times"
DETAIL: Key (tsrange(immutable_tstamp(j ->> 'start'::text), immutable_tstamp(j ->> 'end'::text)))=(["2015-03-08 10:30:00","2015-03-08 11:30:00")) conflicts with existing key (tsrange(immutable_tstamp(j ->> 'start'::text), immutable_tstamp(j ->> 'end'::text)))=(["2015-03-08 11:00:00","2015-03-08 12:00:00")).
С помощью функции и ограничения мы реализовали функциональность обычных исключающих ограничений, определяемых как EXCLUDE USING gist(r WITH &&) в случае, если в колонке r доступен соответствующий диапазон.
Так что ответ на изначальный вопрос «можно ли использовать преимущества одновременно SQL и NoSQL СУБД?» — да. По крайней мере пока используется СУБД, обладающая возможностями обоих типов, а именно PostgreSQL.
Важный момент — если, как в нашем примере, схема известна, система будет работать быстрее и эффективнее благодаря хранению полей в реляционной форме. Безусловно, для работы необходимо использовать правильный инструмент, поэтому применять JSON необходимо только, если схема является, по крайней мере полудинамической. При этом возможность объявления ограничений для части схемы очень полезна даже в том случае, если данные не реляционные, и все работает не так быстро. В конце концов вся суть динамической схемы — это её гибкость.
Наш опыт
Мы используем JSON в PostgreSQL в нескольких проектах. В частности, в проекте для управления бизнес-процессами мы храним в таких полях значения переменных процесса, структура которого определяется в момент внедрения продукта, а не во время его разработки.
Работа с полями таблицы производится через адаптер фреймворка Ruby On Rails для PostgreSQL. Чтение и запись работают в нативном для Ruby режиме — через хэши и списки. Таким образом, вы можете работать с данными из поля без дополнительных преобразований.
Статья описывает важный аспект целостности хранимых данных, но зачастую помимо простого хранения требуется осуществлять поиск по ним, для чего в PostgreSQL также присутствуют функциональные индексы, которые могут существенно повысить операции доступа к данным для частых запросов.
Другие технические статьи в нашем блоге:
