Продвинутые типы данных: JSONB, Array, UUID

Продвинутые типы данных: JSONB, Array, UUID

Главная идея

Как это выглядит на практике

1. Создай таблицу products с JSONB-атрибутами: CREATE TABLE products (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, category TEXT, price NUMERIC(10,2), attributes JSONB DEFAULT '{}');. JSONB DEFAULT '{}' гарантирует, что атрибуты никогда не NULL.
2. Вставь товары с разными схемами атрибутов: INSERT INTO products (name, category, price, attributes) VALUES ('iPhone 15 Pro', 'phones', 89990, '{"color": "titanium", "storage_gb": 256, "display_inches": 6.1}'), ('Футболка Uniqlo', 'clothing', 1490, '{"color": "white", "size": "M", "material": "cotton"}');. Оба товара в одной таблице с разными атрибутами.
3. Создай GIN-индекс для быстрого поиска по JSONB: CREATE INDEX idx_products_attributes ON products USING GIN (attributes);. После этого поиск WHERE attributes @> '{"color": "white"}' использует индекс вместо sequential scan.
4. Используй операторы JSONB: SELECT name, attributes->>'color' AS color, (attributes->>'storage_gb')::int AS storage FROM products WHERE attributes @> '{"color": "titanium"}';. Оператор -> возвращает JSONB, ->> возвращает TEXT. Для числовых операций обязательно приведение типа.
5. Обнови конкретный ключ в JSONB: UPDATE products SET attributes = jsonb_set(attributes, '{price_usd}', '999') WHERE id = 1;. jsonb_set не перезаписывает весь объект — только обновляет указанный путь. Это безопасно для конкурентных обновлений разных ключей.
6. Используй массивы для тегов: ALTER TABLE products ADD COLUMN tags TEXT[] DEFAULT '{}'; UPDATE products SET tags = ARRAY['новинка', 'хит продаж'] WHERE id = 1; SELECT * FROM products WHERE 'хит продаж' = ANY(tags);. Оператор ANY проверяет вхождение элемента.
7. Создай таблицу с UUID первичным ключом: CREATE TABLE events (id UUID DEFAULT gen_random_uuid() PRIMARY KEY, user_id BIGINT, event_type TEXT, payload JSONB, created_at TIMESTAMPTZ DEFAULT now());. gen_random_uuid() генерирует UUID v4. В PostgreSQL 17+ доступна функция uuidv7() для time-ordered UUID.
8. Используй диапазонные типы для временных слотов: CREATE TABLE booking_slots (id BIGINT PRIMARY KEY, resource_id BIGINT, during TSTZRANGE NOT NULL); INSERT INTO booking_slots VALUES (1, 42, '[2024-06-01 10:00, 2024-06-01 11:00)');. TSTZRANGE хранит временной интервал с timezone-aware timestamps.
9. Проверь пересечение диапазонов для предотвращения двойного бронирования: SELECT * FROM booking_slots WHERE resource_id = 42 AND during && '[2024-06-01 10:30, 2024-06-01 11:30)'::tstzrange;. Оператор && проверяет пересечение — возвращает все слоты, конфликтующие с запрошенным.
10. Индексируй диапазонный тип через GiST: CREATE INDEX idx_booking_slots_during ON booking_slots USING GiST (during); CREATE INDEX idx_booking_slots_resource_during ON booking_slots USING GiST (resource_id, during);. GiST — правильный индекс для диапазонных операций, GIN здесь не подойдёт.
11. Разверни массив в строки для аналитики: SELECT unnest(tags) AS tag, count(*) FROM products GROUP BY tag ORDER BY count DESC LIMIT 10;. unnest() разворачивает массив в набор строк — каждый тег становится отдельной строкой для агрегации.
12. Используй jsonb_array_elements для JSONB-массивов: SELECT p.name, feature->>'name' AS feature FROM products p, jsonb_array_elements(attributes->'features') AS feature WHERE feature->>'available' = 'true';. jsonb_array_elements разворачивает JSONB-массив в строки.

Примеры кода

JSONB: поиск, фильтрация и извлечение данных

-- Найти все телефоны с хранилищем >= 128GB
SELECT
  id,
  name,
  price,
  (attributes->>'storage_gb')::int  AS storage_gb,
  attributes->>'color'              AS color
FROM products
WHERE
  category = 'phones'
  AND (attributes->>'storage_gb')::int >= 128
ORDER BY price DESC;

-- Containment: найти все красные товары любой категории
SELECT id, name, category, price
FROM products
WHERE attributes @> '{"color": "red"}'::jsonb;

-- Проверка существования ключа
SELECT id, name
FROM products
WHERE attributes ? 'storage_gb';

Обновление JSONB без перезаписи всего объекта

-- jsonb_set: обновить конкретный ключ
UPDATE products
SET attributes = jsonb_set(
  attributes,
  '{discount_pct}',
  '15'::jsonb
)
WHERE id = 42;

-- Удалить ключ из JSONB
UPDATE products
SET attributes = attributes - 'old_field'
WHERE attributes ? 'old_field';

-- Слить два JSONB объекта (правый побеждает при конфликте ключей)
UPDATE products
SET attributes = attributes || '{"in_stock": true, "warehouse": "Moscow"}'::jsonb
WHERE id = 42;

Массивы: поиск, фильтрация и агрегация

-- Создать GIN-индекс на массив тегов
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- Найти продукты с тегом 'новинка'
SELECT id, name, tags
FROM products
WHERE tags @> ARRAY['новинка'];

-- Найти продукты с ЛЮБЫМ из нескольких тегов (пересечение)
SELECT id, name, tags
FROM products
WHERE tags && ARRAY['хит продаж', 'скидка'];

-- Статистика по тегам через unnest
SELECT
  unnest(tags) AS tag,
  count(*)     AS product_count
FROM products
GROUP BY tag
ORDER BY product_count DESC
LIMIT 10;

UUID как первичный ключ

-- UUID v4 (случайный) — для таблиц без высокой нагрузки на вставку
CREATE TABLE notifications (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  message    TEXT,
  read_at    TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Индекс на user_id для быстрой выборки уведомлений пользователя
CREATE INDEX idx_notifications_user_id ON notifications (user_id, created_at DESC);

-- Вставка
INSERT INTO notifications (user_id, message)
VALUES (1001, 'Ваш заказ отправлен')
RETURNING id;

Диапазонные типы для бронирований

CREATE TABLE room_bookings (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id     BIGINT NOT NULL,
  guest_name  TEXT NOT NULL,
  stay        DATERANGE NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, stay WITH &&)
);

CREATE INDEX idx_room_bookings_room_stay
  ON room_bookings USING GIST (room_id, stay);

-- Проверить доступность комнаты на период
SELECT room_id
FROM room_bookings
WHERE room_id = 5
  AND stay && '[2024-07-01, 2024-07-07)'::daterange;

-- Найти все бронирования, пересекающиеся с июлем 2024
SELECT *
FROM room_bookings
WHERE stay && '[2024-07-01, 2024-08-01)'::daterange;

Что происходит под капотом

• JSON vs JSONB на уровне хранения: JSON хранит оригинальную строку as-is, включая пробелы и порядок ключей. JSONB парсит JSON при вставке и хранит в бинарном формате: ключи отсортированы, дублирующиеся ключи удалены, числа хранятся как native types. JSONB занимает чуть больше места при хранении, но значительно быстрее при чтении и поддерживает индексирование.
• GIN-индекс на JSONB: создаёт инвертированный индекс — mapping от каждого ключа и значения к списку row ID. Это позволяет WHERE attributes @> '{"color": "red"}' выполняться за O(log N + K) вместо O(N). GIN медленнее обновляется (при каждом UPDATE JSONB все изменившиеся ключи нужно переиндексировать), но быстрее для поиска.
• Оператор @> (containment): проверяет, является ли правый JSONB подграфом левого. '{"a":1, "b":2}' @> '{"a":1}' — true. Работает рекурсивно: '{"a":{"b":1}}' @> '{"a":{"b":1}}' — true. Для массивов: '[1,2,3]' @> '[1,3]' — true (порядок не важен). Это основной оператор для GIN-поиска.
• Массивы как first-class type: PostgreSQL хранит массив как отдельный тип с собственным OID. Размерность не фиксирована при объявлении (integer[] и integer[3] — один тип). Массивы могут быть многомерными (integer[][]). Функции: array_length(arr, 1), array_dims(arr), array_append(arr, elem), array_remove(arr, elem).
• UUID v4 vs v7 для производительности: UUID v4 — 128 случайных бит. При INSERT в B-tree индекс случайный UUID вставляется в случайную позицию, что вызывает page splits и фрагментацию. При большом объёме вставок (> 10K/сек) фрагментация приводит к росту индекса и замедлению. UUID v7 — первые 48 бит это timestamp с миллисекундной точностью, остальные биты случайны. Вставки монотонно возрастающие → нет page splits → компактный индекс как у BIGSERIAL.
• Оператор ->> vs ->: -> возвращает JSONB ('{"a":1}'->'a' = '1'::jsonb), ->> возвращает TEXT ('{"a":1}'->>''a' = '1'::text). Для числовых операций всегда нужно явное приведение: (attributes->>'price')::numeric. Нет автоматического преобразования — это защита от типовых ошибок.
• jsonb_path_query (SQL/JSON path, PostgreSQL 12+): более мощный механизм запросов к JSONB. SELECT * FROM products WHERE jsonb_path_exists(attributes, '$.colors[*] ? (@ == "red")') — поиск в массивах внутри JSONB. Поддерживает фильтры, методы, arithmetic.
• Диапазонные типы (range types): встроенные int4range, int8range, numrange, daterange, tsrange, tstzrange. Операторы: && (перекрытие), @> (содержит значение/диапазон), <@ (содержится в), -|- (смежные). EXCLUDE USING GIST — ограничение исключения, которое через GiST-индекс гарантирует отсутствие пересечений без дополнительных CHECK-запросов.
• Частичное обновление JSONB: || оператор создаёт новый JSONB-объект (shallow merge). jsonb_set для точечных обновлений. jsonb_insert для вставки в массив. Нет оператора для deep merge — для вложенных структур нужно использовать jsonb_set с путём.
• Производительность GIN vs GiST для JSONB: GIN быстрее для поиска (lookup), медленнее для вставки. GiST быстрее для вставки, медленнее для поиска. Для JSONB и массивов в OLTP-нагрузке (много reads, некоторые writes) GIN — правильный выбор. GiST используй для диапазонных типов и геометрических данных.

Термины урока

Мини-разбор реальной ситуации

E-commerce платформа с каталогом из 500K товаров в 200 категориях. Изначальная схема: таблица product_attributes с полями (product_id, attribute_name, attribute_value TEXT) — классический EAV. Проблемы: запрос 'найди красные телефоны дороже 50000₽' требует двух JOIN к product_attributes с разными условиями — 8 секунд на 500K товаров. Поддержка новых атрибутов требует изменений в коде, но не в схеме БД. Невозможно задать типизацию (все значения TEXT). Переход на JSONB: ALTER TABLE products ADD COLUMN attributes JSONB DEFAULT '{}'. Скрипт миграции переносит данные из EAV в JSONB за 20 минут. CREATE INDEX idx_products_attrs ON products USING GIN (attributes). Дополнительный индекс: CREATE INDEX idx_products_price ON products ((attributes->>'price')::numeric). После миграции тот же запрос (красные телефоны > 50000₽) выполняется за 12мс против 8 секунд. Добавление нового атрибута — просто вставка в JSONB, никаких изменений схемы. Специализированные индексы на часто запрашиваемые атрибуты позволяют точечно ускорить конкретные фильтры.