JSONB, партиционирование и расширения SQL в PostgreSQL

ОБЯЗАТЕЛЬНО

Бэкенду

Раздел 8.11, шаг 4 из 12. Дальше — PL/pgSQL и триггеры.

---

JSONB как «NoSQL внутри Postgres»

PostgreSQL хранит JSON в двух формах:

Тип	Хранение	Когда
json	Текст с сохранением пробелов/порядка ключей	Точная сериализация
jsonb	Бинарное, нормализованное	Фильтры, индексы, операторы

Типовой паттерн — гибридная схема: жёсткие столбцы для ключей и FK, JSONB для редко фильтруемых или эволюционирующих атрибутов.

CREATE TABLE products (
  id          bigserial PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  price       numeric(12,2) NOT NULL,
  attributes  jsonb NOT NULL DEFAULT '{}'
);

Операторы извлечения и фильтрации

Оператор	Действие
->	JSON-объект по ключу (json)
->>	Значение как text
#>	Путь '{a,b,c}'
@>	Левый JSON содержит правый
? / `?	/?&`
||	Слияние объектов

SELECT sku, attributes ->> 'color' AS color
FROM products
WHERE attributes @> '{"brand": "acme"}'
  AND attributes ? 'warranty_months';

Обновление документов

UPDATE products
SET attributes = jsonb_set(attributes, '{stock}', '42', true)
WHERE sku = 'WIDGET-1';

UPDATE products
SET attributes = attributes - 'deprecated_field'
WHERE id = 100;

Индексы

CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);
CREATE INDEX idx_products_color ON products ((attributes ->> 'color'));

Полный практикум с JOIN, агрегатами и EXPLAIN — 3.07/66.

---

Оконные функции и CTE

Подробная теория и примеры уже в энциклопедии:

• Оконные функции — ROW_NUMBER, LAG, PARTITION BY.
• CTE и рекурсия — WITH, MATERIALIZED, модифицирующие CTE.

В production PostgreSQL 12+ планировщик может inline CTE (не материализовать), если это выгодно. Принудительная материализация:

WITH big AS MATERIALIZED (
  SELECT … FROM huge_table WHERE …
)
SELECT … FROM big JOIN …;

Комбинация JSONB + оконные функции — типовой аналитический запрос:

SELECT id,
       payload ->> 'region' AS region,
       (payload ->> 'amount')::numeric AS amount,
       SUM((payload ->> 'amount')::numeric) OVER (
         PARTITION BY payload ->> 'region'
         ORDER BY created_at
       ) AS running_total
FROM events
WHERE created_at >= now() - interval '7 days';

---

Declarative partitioning

Партиционирование делит одну логическую таблицу на физические части (partition). Планировщик pruning отсекает лишние партиции по условию WHERE.

RANGE — по диапазону (даты, ID)

CREATE TABLE measurements (
  id         bigserial,
  device_id  int NOT NULL,
  measured_at timestamptz NOT NULL,
  value      double precision
) PARTITION BY RANGE (measured_at);

CREATE TABLE measurements_2025_01 PARTITION OF measurements
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE measurements_2025_02 PARTITION OF measurements
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

LIST — по списку значений

CREATE TABLE orders (
  id bigint,
  region text,
  amount numeric
) PARTITION BY LIST (region);

CREATE TABLE orders_eu PARTITION OF orders
  FOR VALUES IN ('DE', 'FR', 'IT');

HASH — равномерное распределение

CREATE TABLE sessions (
  id uuid PRIMARY KEY,
  user_id bigint,
  data jsonb
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- … p1, p2, p3

---

Управление жизненным циклом данных

Задача	Подход с партициями
Удаление старых логов	DROP TABLE measurements_2024_01 — мгновенно, без VACUUM всей таблицы
Архив	DETACH PARTITION → перенос на cold storage
Загрузка bulk	COPY в новую партицию, затем ATTACH PARTITION
Разные индексы	Разные индексы на «горячей» и «холодной» партиции

Автоматическое создание партиций — расширение pg_partman или cron + DDL.

---

Ограничения и первичные ключи

В partitioned table PK/UNIQUE должны включать ключ партиционирования (или использовать глобальный индекс в будущих версиях — следите за release notes).

-- PK только на id без ключа партиции — ошибка в PG до обходных схем
PRIMARY KEY (id, measured_at)

---

Когда JSONB, когда отдельная таблица

Критерий	JSONB	Нормализация
Схема стабильна, много JOIN	—	отдельные таблицы
Редкие атрибуты, интеграции	JSONB	—
Жёсткая валидация	CHECK + jsonb	столбцы + constraints
Поиск по вложенным полям	GIN + @>	вынести в столбец

---

Практика

1. Перенесите «широкую» таблицу логов на RANGE по месяцу, сравните время DELETE старых данных vs DROP PARTITION.
2. Добавьте GIN на JSONB и снимите EXPLAIN для @> (шаг 2).
3. Напишите отчёт с оконной функцией по JSONB-полю amount.

---

Связанные материалы

• Индексы GIN и BRIN
• PL/pgSQL — триггеры на изменение JSONB
• NoSQL — о разделе — когда Postgres недостаточен

---