Попробовать
демоверсию
Бесплатная демоверсия позволяет ознакомиться со всеми возможностями нашего биллинга
+7 499 940-95-05

Как использовать ограничения JSON при работе с PostgreSQL

Сегодня речь пойдет о работе с 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 также присутствуют функциональные индексы, которые могут существенно повысить операции доступа к данным для частых запросов.

Подписывайтесь на наш блог, чтобы не пропустить ничего интересного!

23.04.2016

Другие публикации

По определению консалтинговой компании Deloitte, текущий период в мировой экономике характеризуется неопределенностью. Поэтому бизнес во всем мире стремится к оптимизации и снижению издержек. Сегодня мы рассмотрим несколько способов, с помощью которых компании из разных стран экономят уже сейчас.
В новой версии Гидры мы по традиции продолжили работу над коммерческими возможностями биллинга. Встречайте контрактные тарифы! Другие мелкие улучшения упрощают интеграцию Гидры с внешними системами.
Компании из самых разных отраслей бизнеса сталкиваются с часто повторяющимися бизнес-процессами, связанными с обработкой заявок. В большинстве отраслей выполнение типовых работ и услуг — это сложный и разветвленный бизнес-процесс. Мы много размышляли об этих проблемах и создали инструмент, который каждая компания могла бы легко адаптировать под себя — новый продукт Гидра OMS
В одном из наших постов мы уже описывали ситуацию, в которой бесконтрольный рост таблиц в базе данных одной компании-пользователя нашей системы привел к настоящему DoS. Сегодня речь пойдет о еще одном интересном случае внезапного сбоя, который сделал «день смеха» 1 апреля этого года совсем не смешным для службы поддержки «Латеры».
В Instagram развертывание backend-кода (основная программно-аппаратная часть, с которой работают клиенты) происходит от 30 до 50 раз в день, каждый раз, когда инженеры подтверждают изменение оригинала. И, по большей части, без участия человека — сложно в это поверить, особенно учитывая масштабы соцсети, но факт остается фактом.
В этой заметке речь пойдет о масштабировании. Разработчики open-source почтового приложения Nylas опубликовали в своем блоге материал о том, как им удалось масштабировать систему в 20 раз за три недели с помощью инструмента ProxySQL. Для этого им пришлось переехать с Amazon RDS на MySQL на EC2.
Успешно завершили интеграцию биллинга Гидра с порталом NEXT TV.
В этой заметке мы рассказываем о плюсах и минусах денормализации баз данных. Разработчик баз данных и финансовый аналитик Эмил Дркушич (Emil Drkušić) написал в блоге компании Vertabelo материал о том, зачем, как и когда использовать этот подход. Мы представляем вашему вниманию главные тезисы этой заметки и делимся своим опытом.
Разработчики из американской компании Gaslight написали интересный материал о том, почему организация, известная своей любовью к Ruby и Ruby on Rails, решила инвестировать в освоение новых технологий — например, Clojure. Мы тоже работаем с этим языком программирования, поэтому решили выделить главные тезисы команды Gaslight в отдельный материал.
Разработчик и сотрудник проекта CouldBoost.io Наваз Дандала (Nawaz Dhandala) написал материал о том, почему в некоторых случаях не стоит использовать MongoDB. Мы в «Латере» уже много лет работаем с этой СУБД, поэтому решили представить и свое мнение по данному вопросу.
Немецкий журналист и хакер Ляйф Риге (Leif Ryge) написал для издания Ars Technica интересный материал о том, что современный подход к организации обновлений программного обеспечениях несет в себе серьезные риски информационной безопасности. Мы представляем вашему вниманию главные мысли этой заметки.
Инженер проекта Haleby.se написал материал, в котором рассказал о причинах выбора в качестве инструмента оркестрации Docker-контейнеров технологии Kubernetes. Мы представляем основные мысли этой заметки.
Адаптация заметки бывшего сотрудника Amazon про то, почему плохие продукты пользуются большим успехом, опубликованная в авторской колонке Дмитрия Копловича на Rusbase.
Поучительная история из жизни нашей техподдержки про то почему операторам нужно мониторить размеры таблиц в своих базах.
Инженер компании Akalak & Neo Technology Горка Садаковски (Gorka Sadakowski) написал интересный материал о том, как использование графовых баз данных может в режиме реального времени предотвращать мошенничество в сфере электронной коммерции. Мы представляем вашему вниманию основные мысли этой заметки.
Платежные протоколы уязвимы — об этом рассказали немецкие исследователи информационной безопасности на конференции Chaos Computing Club. Предлагаем вам адаптированный перевод их выступления.
Наша адаптация заметки разработчика и системного архитектора Михаэля Виттига о наиболее распространенных ошибках в использовании Amazon Web Services.
Наш адаптированный перевод заметки главного разработчика Azure Джеффа Уилкокса, о том, как более двух тысяч членов команды проекта переезжали на GitHub.
Представляем вашему вниманию адаптированный перевод одной из глав книги «Архитектура open-source-приложений», в которой описываются предпосылки появления, архитектура и организация работы популярного веб-сервера nginx.
Наш сегодняшний пост посвящен тому как мы писали софт для контроля работы удаленных сотрудников.
Сегодня мы расскажем про устройство системы подпольного банкинга Хавала, которая возникла еще в VIII веке и до сих пор пользуется большой популярности в странах Среднего Востока, Азии и Африки.
Мы уже рассказывали о том какие проблемы могут возникнуть у компании при саомостоятельной разработке сложных систем. Сегодня мы поговорим о том, как мы работали над повышением отказоустойчивости «Гидры».
Адаптированный перевод заметки главного инженера LinkedIn Джоша Клемма о процессе масштабирования инфраструктуры социальной сети.
Адаптированный перевод заметки инженера финансового стартапа Stripe о том, как его команда мигрировала огромное количество записей в базе данных.
По нашим оценкам, около половины российских операторов связи используют самописный (или переписанный до неузнаваемости простенький «покупной») софт. Сегодня мы поговорим о возможных минусах такого подхода.
Появились маркетинговые инструменты для сегментации абонентской базы, управления скидками и пакетными предложениями.
Подробнее читайте в блоге.
В новой версии мы полностью переработали механизм работы с услугами. Это сразу дало несколько серьезных улучшений, но полностью все заложенные в новой версии возможности будут раскрыты в следующих версиях.
В новой версии была полностью переработана система прав доступа. Новая система уникальна и позволяет реализовать самые смелые замыслы по разграничению доступа сотрудников к биллингу.

Начните знакомство с Гидрой прямо сейчас

Попробовать демоверсию Купить Гидру

Оформление демоверсии

Пожалуйста, укажите реальный email, на него придут данные для доступа в демо.
Все поля являются обязательными для заполнения.
.hydra-billing.com

Для вас будет развернута персональная облачная версия Гидры.
Нажимая кнопку "создать демоверсию" вы соглашаетесь с
Политикой обработки персональных данных.

Мы можем вам перезвонить

Нажимая кнопку "отправить" вы соглашаетесь с
Политикой обработки персональных данных.

Напишите нам!

Ваша компания

Услуги, которые вы предоставляете:

Нажимая кнопку "отправить" вы соглашаетесь с
Политикой обработки персональных данных.

Скачать буклет

Ваш адрес:

Хотите узнать больше?

Рассылка делается примерно раз в месяц. Мы обещаем бережно хранить ваш электронный адрес и никому его не выдавать даже под пытками.