Поиск по JSONB
В предыдущем уроке документ properties получил ясный смысл. Теперь редактор хочет найти начинающие курсы и материалы с меткой CSS. Поиск по JSONB зависит от выбранного оператора: сравнение извлечённой строки, проверка ключа и включение фрагмента документа являются разными запросами. Индекс выбирается под эту форму, а не просто под наличие JSON в колонке.
Используем PostgreSQL 17.11 и снимок catalog-db-advanced/lesson-24 из архива продолжения. Его начальные документы совпадают с предыдущей главой, но папка самостоятельная. SQL и планы не запускались. На шести курсах мы объясняем область индекса, не заявляем измеренное ускорение.
Включение документа
Сначала выберем свойства с уровнем beginner:
SELECT id, slug
FROM catalog.courses
WHERE properties @> '{"level":"beginner"}'::jsonb
ORDER BY id;Ожидаются HTML, Markdown и SEO. Оператор @> проверяет, содержит ли левый документ соответствующий фрагмент. Он не требует полного равенства объектов: наличие дополнительных меток и caption не мешает совпадению. Это позволяет искать по части структуры, сохраняя остальные сведения внутри properties.
Для метки CSS используем фрагмент массива:
SELECT id, slug
FROM catalog.courses
WHERE properties @> '{"tags":["css"]}'::jsonb
ORDER BY id;Ожидается только HTML. Здесь условие выражено включением JSON-фрагмента, а не совпадением текстового представления всего массива. Поэтому добавление другой метки не должно разрушить смысл поиска CSS. Семантика включения разобрана в руководстве JSONB PostgreSQL.
Если вместо этого выполнить точное сравнение properties->>'level' = 'beginner', предметный результат в нашем наборе будет тот же, однако форма оператора и возможный индекс отличаются. Совпадение ответов на нескольких строках не доказывает взаимозаменяемость любых двух выражений для планировщика. Нужно сохранять точный SQL при обсуждении доступа.
GIN над документом
Для разных проверок JSONB доступен GIN. Первый кандидат использует обычный класс операторов колонки:
BEGIN;
CREATE INDEX courses_properties_gin
ON catalog.courses USING gin (properties);
EXPLAIN (COSTS OFF)
SELECT id FROM catalog.courses
WHERE properties @> '{"level":"beginner"}'::jsonb;
ROLLBACK;Это полный временный опыт создания, просмотра плана и отката. После завершения индекс не остаётся в лабораторной базе. Реальная форма EXPLAIN появится только при будущем ручном запуске; в статье она не выдумывается. Последовательное чтение маленькой таблицы остаётся возможным разумным решением.
Обычный класс поддерживает, среди прочего, включение и проверки существования ключа. Например, properties ? 'caption' выбирает HTML, где ключ есть даже при JSON null. Это отличается от условия «подпись содержит непустой текст». Индекс обслуживает оператор, а предметный смысл наличия значения определяется моделью и дальнейшими условиями.
GIN содержит дополнительные сведения для поиска внутри значения, но не заменяет всю таблицу. Приложению всё ещё нужны соответствующие строки и остальные колонки карточки. Поэтому нельзя объявить один JSON-индекс достаточной оптимизацией всей страницы, не рассматривая фильтр статуса, тему, сортировку и размер ответа.
Более узкий класс операторов
Для набора запросов включения можно рассмотреть jsonb_path_ops:
BEGIN;
CREATE INDEX courses_properties_path_gin
ON catalog.courses USING gin (properties jsonb_path_ops);
EXPLAIN (COSTS OFF)
SELECT id FROM catalog.courses
WHERE properties @> '{"tags":["css"]}'::jsonb;
ROLLBACK;Это самостоятельный второй кандидат после отката первого. Он имеет другой набор поддерживаемых операций и особенности представления. В PostgreSQL 17 jsonb_path_ops подходит включению и определённым JSON-path операциям, но не обслуживает обычную проверку существования ключа ? так же, как основной класс. Выбор описан в официальной документации JSONB-индексации.
Нельзя назвать его «улучшенным GIN для любого JSON». Более узкая поддержка может быть полезна конкретной нагрузке, но административный запрос наличия caption потребует другого доступа. Также есть особенности фрагментов без значений, например пустых объектов, которые не следует переносить из удобного простого совпадения в универсальное обещание.
Для нашего миниатюрного набора не приводим измеренный размер файлов и процент ускорения. Эти характеристики определяются документами и нагрузкой. Честный результат главы — способность объяснить, какую операцию кандидат поддерживает и какая операция выходит за эту область.
Индекс выражения
Рассмотрим другую форму поиска метки:
SELECT id, slug
FROM catalog.courses
WHERE (properties->'tags') ? 'css'
ORDER BY id;Правый оператор теперь применяется к извлечённому массиву, а не к исходной колонке properties. Для такого выражения можно выбрать отдельный кандидат:
BEGIN;
CREATE INDEX courses_tags_gin
ON catalog.courses USING gin ((properties->'tags'));
EXPLAIN (COSTS OFF)
SELECT id FROM catalog.courses WHERE (properties->'tags') ? 'css';
ROLLBACK;Ожидаемый предметный ответ по-прежнему HTML. Однако описание индекса теперь соответствует извлечению конкретного ключа. Это полезное напоминание: нельзя обещать использование обычного индекса колонки для любого произвольного преобразования над ней. Правила выражений и структуры CREATE INDEX приведены в справочнике PostgreSQL.
Аналогично для частого равенства текстового уровня рассматривают B-tree по выражению properties->>'level'. Тогда нужно понять допустимые типы и отсутствие ключа, чтобы фильтр не зависел от случайной структуры. Наиболее устойчивый обязательный уровень сложности может со временем получить обычную колонку — это будет задачей миграции.
Индекс начинается с вопроса клиента
Перед созданием следующего индекса сформулируйте обычное чтение каталога словами. Например: «показать опубликованные курсы для начинающих» проверяет скалярный уровень и статус. «Найти курсы с тегом css» работает с коллекцией внутри документа. «Выбрать документы с ключом caption» проверяет наличие свойства независимо от значения. Один JSON-объект участвует в трёх разных вопросах, и выбор операторов определяет применимость кандидата.
Если разработчик меняет оператор в запросе, прежний индекс может перестать подходить этому выражению. Само слово JSONB в типе колонки не соединяет автоматически любые операции с любым GIN. Поэтому миграция запроса должна сопровождаться рассмотрением нужного пути доступа, а не только синтаксической успешностью SELECT.
Наши индексные варианты создаются по одному и откатываются, чтобы читатель сравнивал их назначение отдельно. Если сохранить все три без причины, операция изменения properties будет обслуживать несколько структур сразу. Дополнительное место и работа записи реальны, даже когда публичный SELECT остаётся очень маленьким. Оценить их нужно на настоящем профиле чтения и редактирования, а не по числу значков индекса в административном интерфейсе.
Важно также не сделать вывод о полноте набора из выбранного пути. Индексный доступ не заменяет WHERE published и не устраняет проверку остальных условий. Если запрос ищет только уровень beginner, он обязан по своему договору объяснить, учитывает ли черновики. Добавление GIN не исправляет ошибочное предметное условие и не делает результат публично допустимым.
При будущем ручном чтении плана сравните исходное выражение с определением соответствующего индекса. Затем отдельно посмотрите выбранный путь и оставшиеся фильтры. Такой разбор полезнее утверждения, что JSONB-поиск «ускорен вообще», и сохраняет смысл примера при дальнейшем росте каталога.
Изменение и состав запроса
Когда редактируются метки или уровень, соответствующие структуры должны отражать новое значение. Индекс не является бесплатной копией документа для каждого возможного условия. Создание всех трёх кандидатов навсегда увеличило бы число обслуживаемых структур, хотя приложение может использовать лишь один сценарий.
Публичная выборка также содержит status = 'published'. JSON-поиск не отменяет этот фильтр: черновик с подходящей меткой не должен появляться только потому, что найден документ. Если добавить тему и сортировку свежести, план будет решать комбинацию условий. Предметный ответ и способ доступа читаются совместно.
В будущей ручной работе сравните три SQL-формы и определения индексов, а не только одинаковую строку HTML в ответе. После каждого блока прочитайте, что кандидат откатился, прежде чем переходить к следующему. Теперь поиск по гибким свойствам имеет точный оператор, понятный ожидаемый результат и обоснованный выбор области индекса, сохраняя границу между рассуждением и ещё не проведённым измерением.