Індексування JSONB-даних: використання індексів GIN і BTREE

Індексування в PostgreSQL — це спосіб швидкого пошуку даних у базі. Якщо б дані у твоїй таблиці були книжками, то індексація — це як каталог у бібліотеці, який дозволяє швидко знайти потрібну книжку за назвою чи автором. З JSONB це може бути трохи хитріше, бо дані зберігаються у структурованому форматі, а не у вигляді окремих рядків і стовпців.

Коли JSONB дані починають розростатися до розмірів "книжки про Гаррі Поттера, тільки без ілюстрацій", пошук всередині цієї структури може стати повільним. Наприклад, якщо ти хочеш знайти всі замовлення, де певний ключ "status" має значення "delivered", PostgreSQL має пройтись по всіх записах, щоб виконати пошук. Це звучить як робота, яку ти точно не захочеш робити вручну, правда?

Ну а індекси GIN і BTREE — це наші герої, які приходять на допомогу, рятуючи нас від довгих очікувань!

Типи індексів для JSONB

GIN (Generalized Inverted Index)

Індекс GIN спеціально створений для роботи зі структурованими даними, такими як масиви та об'єкти, що робить його ідеальним для JSONB. Він дозволяє індексувати не сам об'єкт цілком, а окремі ключі та значення всередині нього. Це означає, що з допомогою GIN можна швидко знаходити записи, які містять певні ключі, значення або їх комбінації.

Уяви JSONB колонку з даними:

{"name": "Alice", "age": 25, "city": "Berlin"}

GIN-індекс створює внутрішню структуру, де ключі "name", "age" і "city" пов'язані зі своїми значеннями. Тому, коли ми шукаємо "name": "Alice", PostgreSQL вже знає, де це шукати — він не бігає по всій таблиці.

BTREE

Індекс BTREE більш традиційний. Він створює впорядковану структуру, яка дозволяє швидко знаходити дані за конкретними значеннями. У випадку JSONB BTREE-індекс можна використовувати, якщо ти шукаєш точний збіг даних або якщо у тебе фіксований ключ (наприклад, ти хочеш порівняти значення JSONB цілком).

Якщо твоя колонка містить JSONB-об'єкти, такі як:

{"name": "Bob", "age": 30}

BTREE-індекс може бути корисним, якщо ти шукаєш записи, де весь об'єкт строго рівний.

{"name": "Bob", "age": 30}

Створення індексу для JSONB

Спочатку подивимось, як треба створювати GIN-індекс. Все, що потрібно, це магічна команда CREATE INDEX. Ось як це виглядає:

-- Створюємо індекс GIN для JSONB колонки
CREATE INDEX idx_jsonb_data ON orders USING GIN (data);

Де:

• idx_jsonb_data — ім'я індексу.
• orders — ім'я таблиці.
• data — колонка з даними JSONB.

Після створення цього індексу запити, які шукають ключі чи значення всередині JSONB, будуть працювати швидше.

Припустимо, у нас є таблиця orders з колонкою data, яка містить JSONB:

Запит без індексу:

-- Знайдемо всі замовлення зі статусом "delivered"
SELECT * FROM orders WHERE data @> '{"status": "delivered"}';

Якщо таблиця велика, цей запит може зайняти багато часу. Але з GIN індексом він буде працювати значно швидше.

Як створити BTREE-індекс

Для створення BTREE індексу тобі треба трохи змінити підхід. У більшості випадків, щоб використовувати BTREE з JSONB, треба вказати, що ти хочеш індексувати не весь об'єкт, а його частину. Ось приклад:

-- Створюємо індекс BTREE для конкретного ключа
CREATE INDEX idx_jsonb_total ON orders ((data->>'total'));

Зверни увагу на (data->>'total'). Це витягує значення ключа total з JSONB об'єкта, і саме це значення індексується. Тепер, якщо ти шукаєш замовлення, де total = 100, PostgreSQL буде використовувати цей індекс.

Наводимо приклад використання з тими ж даними:

Запит:

-- Знайти всі замовлення, де total = 100
SELECT * FROM orders WHERE data->>'total' = '100';

З BTREE індексом для data->>'total', цей запит буде виконуватись значно швидше.

Порівняння GIN і BTREE

Якщо у тебе складні JSONB структури і ти часто використовуєш такі оператори, як @> чи ?, обирай GIN. Якщо ти шукаєш конкретні значення або ключі, які фіксовані, BTREE може бути кращим вибором.

Підводні камені та типові помилки при індексуванні JSONB

Робота з JSONB індексацією може бути потужною, але є кілька підводних каменів, про які варто пам'ятати.

1. Відсутність індексу там, де він потрібен. Якщо ти часто використовуєш JSONB дані у фільтрах (WHERE), але не створив індекс, запити будуть повільними.
2. Зайве індексування. Якщо ти створюєш індекси для кожного можливого ключа JSONB, це може сповільнити вставки та оновлення.
3. Неправильний вибір типу індексу. Якщо твої запити складні і використовують оператори типу @> чи ?, але ти створив BTREE індекс, це не дасть приросту продуктивності.
4. Брак знань про шляхи. Якщо ти постійно звертаєшся до вкладених значень, але не створив індекс для конкретного шляху (наприклад, data->>'some_key'), твій запит все одно буде повільним.

Підсумок: коли який індекс використовувати

• Використовуй GIN, якщо у тебе масиви або складні об'єкти, де часто застосовується пошук по ключах і значеннях.
• Використовуй BTREE, якщо ти шукаєш точний збіг або часто звертаєшся до конкретних ключів.