PostgreSQL и JSONB: как расширение jsquery ускоряет точные выборки

Сложности работы с JSONB и как их преодолевает jsquery

JSON – это формат, который используется для хранения полуструктурированных данных. В PostgreSQL есть доступ к двум типам для работы – jsonb и json. Первый вариант использует оптимизированный бинарный формат, в то время как второй сохраняет точную текстовую копию.

Когда информация вставляется в поле jsonb, система занимается разбором текста, удалением дубликатов ключей, проверяет корректность информации, и в итоге – информация будет сохранена в высокоэффективной структуре.

Различие этих двух вариантов состоит, также в скорости чтения и записи. Вставка json осуществляется быстрее, потому как для jsonb задействуются дополнительные вычисления.

Но большая временная затрата происходит во время первой операции, все последующие исполняются намного быстрее. Кроме того, jsonb поддерживает индексацию, которая значительным образом влияет на обработку больших объемов данных.

Jsonb имеет множество бонусов и его можно использовать для простых задач. Но при сложном запросе, выражение становиться очень громоздким и нечитабельным. Потому, разработчики начинают использовать вложенные подзапросы, разворачивать массивы с помощью дополнительных функций, и тому подобные варианты.

Именно для решения этой проблемы с громоздкими запросами и замедленной работой во время поиска было создано расширение jsquery. Таким образом, пользователи могут использовать полноценный язык для работы с jsonb-полями.

Обзор расширения jsquery и его ключевые возможности

Расширение jsquery используется для поиска по данным jsonb. Данное приложение не просто предлагает определенный набор функция, а является полноценным декларативным языком. В нем есть собственный тип данных и оператор сопоставления @@.

Приложение предоставляет доступ к эффективному синтаксису и глубокой интеграции в ядро PostgreSQL. Наличие выделенного оператора, собственный тип данных, и другие особенности архитектуры – значительным образом влияют на скорость функционирования.

Планировщик PostgreSQL ускоряет инструкции @@ с помощью GIN-индекса. Запросы выглядят декларативно, и нет необходимости все пошагово описывать. Таким образом код становиться намного проще и чище.

Вот несколько основных преимуществ jsquery:

• Разнообразие операторов: есть много вариантов для проверки типов (IS OBJECT и т. д.) или сравнения.
• Простой синтаксис: можно легче описывать пути используя точки и другие доступные символы.
• Декларативность: с помощью одного логического и читаемого выражениям можно формулировать сложные условия фильтрации.
• GIN-индексация: полная интеграция с GIN-индексами PostgreSQL повышает производительность запросов.

Оптимизация запросов: роль индексации в производительности jsquery

Кроме декларативного синтаксиса, у jsquery еще одно весомое преимущество – GIN-индексация. Без индекса, процесс поиска может значительно затянутся, потому как любой запрос приведет к полному сканированию и прочтению каждой строки. При наличии большого количества записей процесс может быть значительно замедлен.

Давайте, покажем, как это работает на практике и создадим таблицу:

CREATE TABLE products (

            id serial PRIMARY KEY,

            data jsonb

);

INSERT INTO products (data)

SELECT jsonb_build_object(

            ‘items’, jsonb_build_array(jsonb_build_object(‘price’, (random() * 2000)::int))

)

FROM generate_series(1, 200000);

Таким образом, мы создали таблицу, которая состоит с 200 000 строк со случайными ценами на товары.

Читайте также: Очистка и оптимизация PostgreSQL с помощью команды VACUUM

Выполнение запросов без индекса: ограничения и слабые места

Приступаем к выполнению запроса на поиск товара, где хоть один предмет дороже 490. Вот как это выглядит на практике:

EXPLAIN ANALYZE

SELECT count(*)

FROM products

WHERE jsonb_path_exists(data, ‘$.items[*].price ? (@ > 490)’);

В результате использования запроса без индексации, результат покажет время выполнения в сотни секунд. В конце результата время исполнения может выглядеть подобным образом:

Planning Time: 0.116 ms

Execution Time: 245.924 ms

Как правильно создать и настроить GIN-индекс для jsquery

Создание GIN-индекса может выглядеть подобным образом:

CREATE INDEX idx_products_data_gin

ON products

USING GIN (data jsonb_path_ops);

ANALYZE products;

Альтернативные подходы к выборкам из JSONB и их сравнение с jsquery

Практические кейсы и рекомендации по применению jsquery

Чаще всего jsquery используется для проектов, в которых нужно использовать сложную фильтрацию сведений. Давайте детально рассмотрим некоторые основные сценарии использования jsquery.

В каких сценариях использование jsquery максимально эффективно

• Проекты, где нужно работать с полуструктурированной информацией. Работа с настройками, документами, пользовательскими профилями, где структура может быть очень разнообразной.
• Проекты аналитики по событиям и логам. К примеру, нужно фильтровать логи по большому количеству вложенных параметров.
• Работа на PostgreSQL до 12 версии. В других старших версиях нет альтернативных вариантов для комплексных выборок из jsonb.
• Отбор позиций в большом каталоге. Подходит для проектов с большим каталогом товаров и сложным набором атрибутов.

Итоги: стоит ли внедрять jsquery в проект на PostgreSQL

Jsquery – это отличный вариант, который может значительно увеличить возможности PostgreSQL при работе с jsonb.

Данное приложение имеет отличный декларативный синтаксис структуры, высокую продуктивность из-за интеграции с GIN-индексами.