Расширяем SQL для эффективной работы с JSON документами в PostgreSQL
Чем мы сейчас занимаемся ? На фотографии Федя Сигаев у нас в 38 комнате ГАИШ задумавшись смотрит на Сашу Короткова. А мысли у нас о том, что делать с jsquery. Надо сказать, что jsquery мы придумали для возможности играться с нашими индексами. Кстати, сейчас jsquery уже имеет поддержку хинтов, которыми можно управлять использование индексами, а также поддержка схемы (IS BOOLEAN, IS NUMERIC, IS ARRAY, IS STRING). Однако, jsquery имеет проблемы с расширяемостью, и вообще, как-то противоречит общей концепции SQL.
ВВЕДЕНИЕ.
Появление полноценной поддержки json в PostgreSQL (версия 9.4) стало знаковым событием в мире СУБД, ответом реляционных баз данных на потребности современных информационных систем, в частности, на необходимость использования, так называемых, schema-less баз данных для работы с изменяющимися данными, данных с неизвестной схемой, нового паттерна разработки систем (частые релизы). Российская группа разработчиков PostgreSQL давно работает в этой области, в частности, еще в 2003 году мы разработали известное расширение hstore для эффективной работы с данными ключ-значение , которое вошло в дистрибутив PostgreSQL в 2006 году. Однако, с появлением стандарта json и его популярностью, стало понятно, что простая модель ключ-значение уже не удовлетворяет потребности пользователей и в 2013 году мы начали проект по созданию полноценной поддержки документно-ориентированного хранилища в PostgreSQL, который привел к появлению нового типа данных jsonb с индексной поддержкой и производительностью не хуже (а то и лучше) популярной NoSQL базы данных MongoDB (см. наши доклады “One step forward true json data type.Nested hstore with arrays support”, “Binary storage for nested data structures and application to hstore data type”, “CREATE INDEX ... USING VODKA. An efficient indexing of nested structures”, а также недавний доклад компании EnterpriseDB “NoSQL on ACID - Meet Unstructured Postgres”). Учитывая слабую функциональность NoSQL баз данных, богатые возможности и славную историю PostgreSQL, его доступность (либеральная лицензия BSD), перспективы этой новой возможности для рядовых (большинство проектов не нуждается в замечательной возможности MongoDB работать с распределенными данными) пользователей баз данных трудно переоценить. Однако, наша работа не закончена и мы продолжаем работать над новым типом индексного доступа VODKA, ориентированный на индексирование сложных типов данных (в частности, вложенных структур) и богатого и расширяемого языка запросов, на котором можно было бы сформулировать запросы к jsonb. В настоящей работе мы рассматриваем возможность встраивания такого языка запросов (смотри наш прототип jsquery) в существующий язык запросов SQL, что обеспечило бы поддержку языка всей мощью PostgreSQL (планер, оптимизатор, расширяемость типов).
ОПИСАНИЕ ПРОБЛЕМЫ.
Язык SQL был изначально спроектирован так, чтобы поисковое условие формировалось к строке таблице или к фиксированному набору строк разных таблиц. Например, в следующем SQL-запросе условие WHERE сформулировано условие относительно атрибутов таблиц t1 и t2.
SELECT *
FROM t1 JOIN t2 ON t1.id = t2.t1_id
WHERE t1.x < 10 AND t2.y = 30;
При работе с данными в формате JSON, ситуация сильно усложняется, поскльку JSON может содержать массивы и атрибуты разных типов и произвольного уровня вложенности. Оператор -> и аналогичные, позволяют производить доступ к свойствам объектов и элементам массивов. Следующий SQL-запрос выбирает из набора данных delicious те документы, где свойство term первого элемента массива tags равно 'NYC'.
SELECT *
FROM js
WHERE js -> 'tags' -> 0 ->> 'term' = 'NYC';
Однако в реальных задачах поиска, редко требуется искать что-то в конкретном элементе массива. Как правильно, требуется искать во всех элементах массива. Существует несколько путей решения этой задачи. С помощью оператора @> (contains) подобный запрос можно записать следующим образом.
SELECT *
FROM js
WHERE js @> '{"tags":[{"term":"NYC"}]}';
Оператор @> означает, что один JSON-объект содержит внутри себя другой. При этом элементы массива могут быть отождествлены в произвольном порядке. Однако, оператор @> имеет ряд недостатков:
1. Может проверять только равенство свойств JSON-объектов.
2. Даже когда условие состоит только из равенств, формирование сложных логических связок затруднено.
Второе ограничение нужно проиллюстрировать подробнее.
Предположим, что требуется найти документы, для которых в массиве tags содержится объект, у которого свойство scheme равно 'http://delicious.com/mcasas1/', а свойство term равно 'NYC' или 'arquitectos'. Наивный способ записи такого запроса был бы следующим.
SELECT *
FROM js
WHERE
js @> '{"tags":[{"scheme": "http://delicious.com/mcasas1/"}]}'
AND (js @> '{"tags":[{"term":"NYC"}]}' OR
js @> '{"tags":[{"term":"arquitectos"}]}');
Но такой способ записи является неверным. Условия относительно свойств term и scheme могут выполняться в разных объектах массива tags. Чтобы правильно учесть то, что условия должны выполняться в рамках одного объекта, нужно записать запрос следующим образом.
SELECT *
FROM js
WHERE
js @> '{"tags":[{"term": "NYC",
"scheme": "http://delicious.com/mcasas1/"}]}' OR
js @> '{"tags":[{"term": "arquitectos",
"scheme": "http://delicious.com/mcasas1/"}]}';
Однако, такая форма записи не интуитивно понятна для пользователя. Помимо этого, она представляет собой нормальную форму логического условия. Хотя в данном примере она довольно проста, в общем случае длина такой формы записи может расти экспоненциально.
Ещё одним сопсобом формулирования условий к JSON-документам, являются подзапросы. Функция jsonb_array_elements позволяет преобразовывать JSON-массив в таблицу его элементов. С помощью этой функции можно приведенное ранее условие записать следующим образом.
SELECT *
FROM js
WHERE
EXISTS (
SELECT 1
FROM jsonb_array_elements(js -> 'tags') o
WHERE
o ->> 'scheme' = 'http://delicious.com/mcasas1/'
AND (o ->> 'term' = 'NYC' OR
o ->> 'term' = 'arquitectos'));
Несмотря на то, что подзапросы можно назвать “классическим” подходом в SQL к решению таких задач, поскольку именно они были бы использованы, если бы данные хранились в нормализованной реляционной схеме, такой подход также обладает рядом недостатков.
Подзапросы
1) Подзапрос с спользованием специальных функций, наподобие jsonb_array_elements, является довольно громоздкой конструкцией, неудобной для записи.
2) Декомпозиция JSON-массива в таблицу, а также nested loop узел в плане запроса, требуют дополнительных накладных запросов. Из-за этого, подзапрос работает медленнее, чем другие варианты.
3) Реализация индексной поддержки для обработки таких подзапросов крайне затруднена.
Для того, чтобы преодолеть ограничения вышеизложенных подходов было разработано расширение jsquery. В ресширении jsquery запрос к JSON-документом записывается в виде отдельного значения, имеющего тип jsquery. С помощью jsquery можно записать приведенное ранее условие в следующем виде.
SELECT *
FROM js
WHERE js @@ 'tags.#(scheme = "http://delicious.com/mcasas1/" &
(term = "NYC" |
term = "arquitectos"))'::jsquery;
Запись запроса в таком виде довольно удобна. Символ # означает произвольной элемент массива. Кроме этого % означает произвольный ключ объекта, а * - произвольный путь в JSON-документе (произвольная вложенность объектов и массивов).
Однако jsquery оперирует фиксированным наобором поддерживаемых операций, в то время как пользователи хотят при работе с JSON-документами иметь то же богатство возможностей, что они имеют в SQL. Например, делать запросы с использованием полнотекстовых или геометрических условий, использовать собственные функции в запросах и т.д. Дублировать механизмы расширяемости PostgreSQL в jsquery неоправданно сложно.
ПУТИ РЕШЕНИЯ.
Таким образом, стало понятно, что нужно формулировать условие к JSON-документу на уровне SQL, а не “упаковывать” запрос в одно значение. После обсуждений было предложено добавить в SQL конструкции, позволяющие осуществлять поиск в документе без подзапроса:
1. ANYELEMENT - поиск в любом элементе массива,
2. ANYVALUE - поиск в любом значении объекта,
3. ANYTHING - поиск в произвольном месте JSON-документа.
С помощью ANYELEMENT можно было бы записать приведенное ранее условие в следующем виде.
SELECT *
FROM js
WHERE
js -> 'tags' ANYELEMENT AS x (
x ->> 'scheme' = 'http://delicious.com/mcasas1/' AND
(x ->> 'term' = 'NYC' OR
x ->> 'term' = 'arquitectos');
Однако, реализация таких конструкций сопряжена с рядом трудностей.
1. Новые alias’ы в них вводятся в произвольных местах самого вычисляемого выражения. На текущий момент в SQL alias’ы могут вводиться строго в определенных местах запроса. Поэтому, реализация предложенных конструкций потребует серьёзной переработки parser’а, optimizer’а и executor’а.
2. Для индексной поддержки таких выражений требуется серьёзная переработка системы расширяемости индексов в PostgreSQL. На текущий момент индексы принципиально могут поддерживать: фильтрацию по выражению "колонка оператор значение", сортировку по колонке и сортировку по "колонка оператор значение" (knn).
Итак, задача является очень масштабной. Предлагается разбить её на несколько этапов. Первым этапом предлагается релизовать конструкцию ANYELEMENT для массивов, поскольку для массивов эта конструкция тоже будет полезна. Таким образом на первом этапе должна быть решена проблема введения alias’ов в произвольных местах самого вычисляемого выражения.
Для массивов поиск с помощью ANYELEMENT также даёт преимущества. Например, поиск в массиве числа большего 10 и меньшего 20 можно записать без подзапросов.
SELECT *
FROM arr
WHERE arr ANYELEMENT AS x (x > 10 AND x < 20);
Тогда как с подзапросом запрос бы выглядел следующим образом.
SELECT *
FROM arr
WHERE EXISTS (
SELECT 1
FROM unnest(arr) x
WHERE x > 10 AND x < 20);
Также становится возможной индексная поддержка таких запросов, которая до этого была только для операторов && и @> (эти операторы работают с массивом целиком).
Мы рассчитываем сделать эту работу для версии 9.5. Учитывая специфику цикла разработки PostgreSQL, нам необходимо успеть обсудить с комьюнити и выложить патчи в декабре, до этапа feature-freeze. Патч будет затрагивать ядро PostgreSQL, что сильно усложняет разработку и обсуждение. При удачном стечении обстоятельств мы доложим весной (летом) о работе на международной конференции в Оттаве, Канада.
О КОМАНДЕ.
Олег Бартунов и Федор Сигаев работают в Государственном Астрономическом Институте им. П.К. Штернберга, МГУ, являются основными разработчиками постгреса более 10 лет, на их счету такие разработки, как системы расширяемости GiST, GIN, SP-GiST, встроенный полнотекстовый поиск, jsonb, работа с массивами, расширения hstore, intarray, pg_trgm и другие. Александр Коротков работает в МИФИ, защитил диссертацию по PostgreSQL, присоединился к нашей группе несколько лет назад и стал основным разработчиком постгреса благодаря своим работам в области поисковых технологий, индексов и jsonb.
Все наши работы доступны в дистрибутиве PostgreSQL и доложены на большом количестве конференций. Наши работы были поддержаны компаниями “GFG Networks” (Франция), EnterpriseDB (США), “Engine Yard” (США), “Heroku” (США), фондом РФФИ ( Россия).
ВВЕДЕНИЕ.
Появление полноценной поддержки json в PostgreSQL (версия 9.4) стало знаковым событием в мире СУБД, ответом реляционных баз данных на потребности современных информационных систем, в частности, на необходимость использования, так называемых, schema-less баз данных для работы с изменяющимися данными, данных с неизвестной схемой, нового паттерна разработки систем (частые релизы). Российская группа разработчиков PostgreSQL давно работает в этой области, в частности, еще в 2003 году мы разработали известное расширение hstore для эффективной работы с данными ключ-значение , которое вошло в дистрибутив PostgreSQL в 2006 году. Однако, с появлением стандарта json и его популярностью, стало понятно, что простая модель ключ-значение уже не удовлетворяет потребности пользователей и в 2013 году мы начали проект по созданию полноценной поддержки документно-ориентированного хранилища в PostgreSQL, который привел к появлению нового типа данных jsonb с индексной поддержкой и производительностью не хуже (а то и лучше) популярной NoSQL базы данных MongoDB (см. наши доклады “One step forward true json data type.Nested hstore with arrays support”, “Binary storage for nested data structures and application to hstore data type”, “CREATE INDEX ... USING VODKA. An efficient indexing of nested structures”, а также недавний доклад компании EnterpriseDB “NoSQL on ACID - Meet Unstructured Postgres”). Учитывая слабую функциональность NoSQL баз данных, богатые возможности и славную историю PostgreSQL, его доступность (либеральная лицензия BSD), перспективы этой новой возможности для рядовых (большинство проектов не нуждается в замечательной возможности MongoDB работать с распределенными данными) пользователей баз данных трудно переоценить. Однако, наша работа не закончена и мы продолжаем работать над новым типом индексного доступа VODKA, ориентированный на индексирование сложных типов данных (в частности, вложенных структур) и богатого и расширяемого языка запросов, на котором можно было бы сформулировать запросы к jsonb. В настоящей работе мы рассматриваем возможность встраивания такого языка запросов (смотри наш прототип jsquery) в существующий язык запросов SQL, что обеспечило бы поддержку языка всей мощью PostgreSQL (планер, оптимизатор, расширяемость типов).
ОПИСАНИЕ ПРОБЛЕМЫ.
Язык SQL был изначально спроектирован так, чтобы поисковое условие формировалось к строке таблице или к фиксированному набору строк разных таблиц. Например, в следующем SQL-запросе условие WHERE сформулировано условие относительно атрибутов таблиц t1 и t2.
SELECT *
FROM t1 JOIN t2 ON t1.id = t2.t1_id
WHERE t1.x < 10 AND t2.y = 30;
При работе с данными в формате JSON, ситуация сильно усложняется, поскльку JSON может содержать массивы и атрибуты разных типов и произвольного уровня вложенности. Оператор -> и аналогичные, позволяют производить доступ к свойствам объектов и элементам массивов. Следующий SQL-запрос выбирает из набора данных delicious те документы, где свойство term первого элемента массива tags равно 'NYC'.
SELECT *
FROM js
WHERE js -> 'tags' -> 0 ->> 'term' = 'NYC';
Однако в реальных задачах поиска, редко требуется искать что-то в конкретном элементе массива. Как правильно, требуется искать во всех элементах массива. Существует несколько путей решения этой задачи. С помощью оператора @> (contains) подобный запрос можно записать следующим образом.
SELECT *
FROM js
WHERE js @> '{"tags":[{"term":"NYC"}]}';
Оператор @> означает, что один JSON-объект содержит внутри себя другой. При этом элементы массива могут быть отождествлены в произвольном порядке. Однако, оператор @> имеет ряд недостатков:
1. Может проверять только равенство свойств JSON-объектов.
2. Даже когда условие состоит только из равенств, формирование сложных логических связок затруднено.
Второе ограничение нужно проиллюстрировать подробнее.
Предположим, что требуется найти документы, для которых в массиве tags содержится объект, у которого свойство scheme равно 'http://delicious.com/mcasas1/', а свойство term равно 'NYC' или 'arquitectos'. Наивный способ записи такого запроса был бы следующим.
SELECT *
FROM js
WHERE
js @> '{"tags":[{"scheme": "http://delicious.com/mcasas1/"}]}'
AND (js @> '{"tags":[{"term":"NYC"}]}' OR
js @> '{"tags":[{"term":"arquitectos"}]}');
Но такой способ записи является неверным. Условия относительно свойств term и scheme могут выполняться в разных объектах массива tags. Чтобы правильно учесть то, что условия должны выполняться в рамках одного объекта, нужно записать запрос следующим образом.
SELECT *
FROM js
WHERE
js @> '{"tags":[{"term": "NYC",
"scheme": "http://delicious.com/mcasas1/"}]}' OR
js @> '{"tags":[{"term": "arquitectos",
"scheme": "http://delicious.com/mcasas1/"}]}';
Однако, такая форма записи не интуитивно понятна для пользователя. Помимо этого, она представляет собой нормальную форму логического условия. Хотя в данном примере она довольно проста, в общем случае длина такой формы записи может расти экспоненциально.
Ещё одним сопсобом формулирования условий к JSON-документам, являются подзапросы. Функция jsonb_array_elements позволяет преобразовывать JSON-массив в таблицу его элементов. С помощью этой функции можно приведенное ранее условие записать следующим образом.
SELECT *
FROM js
WHERE
EXISTS (
SELECT 1
FROM jsonb_array_elements(js -> 'tags') o
WHERE
o ->> 'scheme' = 'http://delicious.com/mcasas1/'
AND (o ->> 'term' = 'NYC' OR
o ->> 'term' = 'arquitectos'));
Несмотря на то, что подзапросы можно назвать “классическим” подходом в SQL к решению таких задач, поскольку именно они были бы использованы, если бы данные хранились в нормализованной реляционной схеме, такой подход также обладает рядом недостатков.
Подзапросы
1) Подзапрос с спользованием специальных функций, наподобие jsonb_array_elements, является довольно громоздкой конструкцией, неудобной для записи.
2) Декомпозиция JSON-массива в таблицу, а также nested loop узел в плане запроса, требуют дополнительных накладных запросов. Из-за этого, подзапрос работает медленнее, чем другие варианты.
3) Реализация индексной поддержки для обработки таких подзапросов крайне затруднена.
Для того, чтобы преодолеть ограничения вышеизложенных подходов было разработано расширение jsquery. В ресширении jsquery запрос к JSON-документом записывается в виде отдельного значения, имеющего тип jsquery. С помощью jsquery можно записать приведенное ранее условие в следующем виде.
SELECT *
FROM js
WHERE js @@ 'tags.#(scheme = "http://delicious.com/mcasas1/" &
(term = "NYC" |
term = "arquitectos"))'::jsquery;
Запись запроса в таком виде довольно удобна. Символ # означает произвольной элемент массива. Кроме этого % означает произвольный ключ объекта, а * - произвольный путь в JSON-документе (произвольная вложенность объектов и массивов).
Однако jsquery оперирует фиксированным наобором поддерживаемых операций, в то время как пользователи хотят при работе с JSON-документами иметь то же богатство возможностей, что они имеют в SQL. Например, делать запросы с использованием полнотекстовых или геометрических условий, использовать собственные функции в запросах и т.д. Дублировать механизмы расширяемости PostgreSQL в jsquery неоправданно сложно.
ПУТИ РЕШЕНИЯ.
Таким образом, стало понятно, что нужно формулировать условие к JSON-документу на уровне SQL, а не “упаковывать” запрос в одно значение. После обсуждений было предложено добавить в SQL конструкции, позволяющие осуществлять поиск в документе без подзапроса:
1. ANYELEMENT - поиск в любом элементе массива,
2. ANYVALUE - поиск в любом значении объекта,
3. ANYTHING - поиск в произвольном месте JSON-документа.
С помощью ANYELEMENT можно было бы записать приведенное ранее условие в следующем виде.
SELECT *
FROM js
WHERE
js -> 'tags' ANYELEMENT AS x (
x ->> 'scheme' = 'http://delicious.com/mcasas1/' AND
(x ->> 'term' = 'NYC' OR
x ->> 'term' = 'arquitectos');
Однако, реализация таких конструкций сопряжена с рядом трудностей.
1. Новые alias’ы в них вводятся в произвольных местах самого вычисляемого выражения. На текущий момент в SQL alias’ы могут вводиться строго в определенных местах запроса. Поэтому, реализация предложенных конструкций потребует серьёзной переработки parser’а, optimizer’а и executor’а.
2. Для индексной поддержки таких выражений требуется серьёзная переработка системы расширяемости индексов в PostgreSQL. На текущий момент индексы принципиально могут поддерживать: фильтрацию по выражению "колонка оператор значение", сортировку по колонке и сортировку по "колонка оператор значение" (knn).
Итак, задача является очень масштабной. Предлагается разбить её на несколько этапов. Первым этапом предлагается релизовать конструкцию ANYELEMENT для массивов, поскольку для массивов эта конструкция тоже будет полезна. Таким образом на первом этапе должна быть решена проблема введения alias’ов в произвольных местах самого вычисляемого выражения.
Для массивов поиск с помощью ANYELEMENT также даёт преимущества. Например, поиск в массиве числа большего 10 и меньшего 20 можно записать без подзапросов.
SELECT *
FROM arr
WHERE arr ANYELEMENT AS x (x > 10 AND x < 20);
Тогда как с подзапросом запрос бы выглядел следующим образом.
SELECT *
FROM arr
WHERE EXISTS (
SELECT 1
FROM unnest(arr) x
WHERE x > 10 AND x < 20);
Также становится возможной индексная поддержка таких запросов, которая до этого была только для операторов && и @> (эти операторы работают с массивом целиком).
Мы рассчитываем сделать эту работу для версии 9.5. Учитывая специфику цикла разработки PostgreSQL, нам необходимо успеть обсудить с комьюнити и выложить патчи в декабре, до этапа feature-freeze. Патч будет затрагивать ядро PostgreSQL, что сильно усложняет разработку и обсуждение. При удачном стечении обстоятельств мы доложим весной (летом) о работе на международной конференции в Оттаве, Канада.
О КОМАНДЕ.
Олег Бартунов и Федор Сигаев работают в Государственном Астрономическом Институте им. П.К. Штернберга, МГУ, являются основными разработчиками постгреса более 10 лет, на их счету такие разработки, как системы расширяемости GiST, GIN, SP-GiST, встроенный полнотекстовый поиск, jsonb, работа с массивами, расширения hstore, intarray, pg_trgm и другие. Александр Коротков работает в МИФИ, защитил диссертацию по PostgreSQL, присоединился к нашей группе несколько лет назад и стал основным разработчиком постгреса благодаря своим работам в области поисковых технологий, индексов и jsonb.
Все наши работы доступны в дистрибутиве PostgreSQL и доложены на большом количестве конференций. Наши работы были поддержаны компаниями “GFG Networks” (Франция), EnterpriseDB (США), “Engine Yard” (США), “Heroku” (США), фондом РФФИ ( Россия).