> Какие индексы использовать для JSON по атрибутам в PostgreSQL (JavaScript, Java, PostgreSQL)

Короткий ответ

Для JSON по атрибутам в PostgreSQL используйте GIN-индексы с операторным классом jsonb_path_ops - они компактнее и быстрее, чем стандартный jsonb_ops, но поддерживают только оператор @>. Если нужны другие операторы (?, ?|, ?&), используйте jsonb_ops. Для точечного поиска по конкретному ключу подойдёт B-tree на выражении (data->>'key'), особенно если нужна сортировка или уникальность.

Подробное объяснение

PostgreSQL предлагает два основных типа индексов для JSONB:

1. GIN-индексы - основной инструмент для поиска внутри JSON. Два операторных класса:

   • jsonb_ops - индексирует каждый ключ и значение. Поддерживает операторы @>, ?, ?|, ?&. Больше размер, медленнее.
   • jsonb_path_ops - индексирует только пути (ключ + значение). Поддерживает только @>. Меньше размер (в 2-3 раза), быстрее для вложенных запросов.

2. B-tree на выражении - для поиска по конкретному атрибуту с точным совпадением, диапазоном или сортировкой. Например CREATE INDEX ON table ((data->>'name')). Полезен при частых запросах вида WHERE data->>'status' = 'active'.

Выбор зависит от паттерна запросов:

• Если запросы содержат WHERE data @> '{"key": "value"}' - GIN с jsonb_path_ops.
• Если нужны проверки наличия ключа (?) или пересечения массивов (?|) - GIN с jsonb_ops.
• Если запросы всегда по одному и тому же ключу - B-tree на выражении.

Также стоит учитывать, что GIN-индексы не поддерживают сортировку и операции сравнения (>, <), а B-tree - не поддерживает поиск внутри вложенных структур.

На практике

Для типичного backend-приложения (JavaScript/Java) с фильтрацией по JSON-атрибутам:

• Начните с jsonb_path_ops, если запросы используют @> - это самый частый случай.
• Если нужен поиск по наличию ключа или работа с массивами - переходите на jsonb_ops.
• Для точного совпадения по одному полю с сортировкой - добавьте B-tree на выражении.
• Комбинируйте индексы: GIN для сложных фильтров, B-tree для горячих полей с сортировкой.

Проверяйте план запроса через EXPLAIN ANALYZE - иногда PostgreSQL выбирает seq scan, если индекс неэффективен для конкретного запроса.

Пример кода

SQL
-- GIN с jsonb_path_ops (рекомендуется для @>)
CREATE INDEX idx_data_path_ops ON my_table USING GIN (data jsonb_path_ops);

-- GIN с jsonb_ops (для ? и ?|)
CREATE INDEX idx_data_ops ON my_table USING GIN (data);

-- B-tree на выражении для конкретного ключа
CREATE INDEX idx_data_name ON my_table ((data->>'name'));

-- Запросы, которые используют эти индексы
SELECT * FROM my_table WHERE data @> '{"status": "active"}';
SELECT * FROM my_table WHERE data ? 'email';
SELECT * FROM my_table WHERE data->>'name' = 'John' ORDER BY data->>'name';

Как отвечать на собеседовании

Начните с GIN-индексов как основного инструмента, упомяните два операторных класса и их различия. Затем добавьте про B-tree на выражении для точечного поиска. Объясните критерий выбора: операторы в запросах, размер индекса, частота запросов. Приведите пример из реального проекта, если есть. Завершите рекомендацией проверять через EXPLAIN ANALYZE.

Что проверяет интервьюер

• Понимание разницы между jsonb_ops и jsonb_path_ops.
• Знание ограничений GIN (нет сортировки, нет сравнений).
• Умение выбирать индекс под конкретный паттерн запросов.
• Понимание trade-off между размером индекса и скоростью.
• Практический опыт с EXPLAIN ANALYZE.

Типичные ошибки

• Использование jsonb_ops по умолчанию, хотя jsonb_path_ops быстрее для @>.
• Создание GIN-индекса для запросов с ->> - он не ускоряет такие запросы.
• Игнорирование B-tree на выражении для горячих полей с сортировкой.
• Отсутствие проверки плана запроса - индекс может не использоваться.
• Создание индексов на все JSON-поля без анализа реальных запросов.