Нормализация, JSONB или materialized view: миллион событий тремя способами, цена записи и чтения

Три способа хранить одно и то же: таблицы по нормальным формам, документ jsonb с копией пользователя в каждом событии и материализованное представление с готовой сводкой. Каждый переносит стоимость в другое место: таблицы берут её на запись и соединения, jsonb на размер и перезапись документа, представление на REFRESH и устаревание. Ниже те же 1 000 000 событий в трёх раскладках, размер, WAL, время и сломанная копия имени пользователя.

DBA / 01koddo

Одни 1 млн событий в трёх раскладках: где лежит цена

Одни данные, три раскладки

Контейнер и исходные данные. Таблица raw без журнала нужна, чтобы каждая раскладка заполнялась из одного и того же источника: события, пользователь с именем и страной, вид события, время, сумма и два тега.

docker run -d --name dba-normalization-vs-jsonb-vs-matviews -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-normalization-vs-jsonb-vs-matviews psql -U postgres

SELECT version();
SELECT setseed(0.42);
CREATE UNLOGGED TABLE raw AS
SELECT g AS id, u AS user_id, 'user' || u AS user_name,
       (ARRAY['DE','FR','US','BR','IN','JP','PL','ES','IT','TR'])[1 + u % 10] AS country,
       (ARRAY['view','click','cart','purchase','refund','login'])[1 + (random()*5)::int] AS kind,
       timestamptz '2026-07-01' + random() * interval '90 days' AS at,
       round((random()*500)::numeric, 2) AS amount,
       't' || (g % 50) AS tag1, 't' || ((g*7+3) % 50) AS tag2
FROM (SELECT g, 1 + (random()*99999)::int AS u FROM generate_series(1,1000000) g) s;

CREATE TABLE usr (id int PRIMARY KEY, name text NOT NULL, country text NOT NULL);
CREATE TABLE kind (id smallint PRIMARY KEY, name text NOT NULL UNIQUE);
CREATE TABLE ev (id int PRIMARY KEY, user_id int NOT NULL REFERENCES usr, kind_id smallint NOT NULL REFERENCES kind, at timestamptz NOT NULL, amount numeric(10,2) NOT NULL);
CREATE TABLE ev_tag (ev_id int NOT NULL REFERENCES ev, tag text NOT NULL, PRIMARY KEY (ev_id, tag));
CREATE TABLE ev_j (id int PRIMARY KEY, doc jsonb NOT NULL);
INSERT INTO usr SELECT DISTINCT user_id, user_name, country FROM raw;
INSERT INTO kind SELECT row_number() OVER (ORDER BY kind), kind FROM (SELECT DISTINCT kind FROM raw) k;
CHECKPOINT;

Версия в этом контейнере: PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2). Нормализованный вариант — четыре таблицы с первичными и внешними ключами, пользователь и вид события хранятся по разу. Второй вариант — ev_j, по документу на событие, где пользователь скопирован целиком. Загрузка с замером времени и объёма WAL:

SELECT pg_current_wal_lsn() AS l0 \gset
\timing on
INSERT INTO ev SELECT r.id, r.user_id, k.id, r.at, r.amount FROM raw r JOIN kind k ON k.name = r.kind;
INSERT INTO ev_tag SELECT id, tag1 FROM raw UNION ALL SELECT id, tag2 FROM raw;
\timing off
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'l0') AS wal_bytes;

SELECT pg_current_wal_lsn() AS l0 \gset
\timing on
INSERT INTO ev_j SELECT id, jsonb_build_object('user', jsonb_build_object('id', user_id, 'name', user_name, 'country', country),
  'kind', kind, 'at', at, 'amount', amount, 'tags', jsonb_build_array(tag1, tag2)) FROM raw;
\timing off
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'l0') AS wal_bytes;

Time: 7111.852 ms (00:07.112)
Time: 8713.030 ms (00:08.713)
499323200
Time: 4130.524 ms (00:04.131)
335665104

Таблицы: 15,8 с и 499 МБ WAL, jsonb: 4,1 с и 336 МБ. В таблицы вставлено три миллиона строк вместо одного миллиона, и у них внешние ключи: целостность оплачена на записи.

Индексы под запросы опыта 2 (на таблицах по tag и at, на документах GIN jsonb_path_ops) и материализованное представление:

CREATE INDEX ev_tag_tag_idx ON ev_tag (tag, ev_id);
CREATE INDEX ev_at_idx ON ev (at);
CREATE INDEX ev_j_doc_gin ON ev_j USING gin (doc jsonb_path_ops);
CREATE MATERIALIZED VIEW mv_daily AS
SELECT e.at::date AS day, k.name AS kind, u.country, count(*) AS n, sum(e.amount) AS total
FROM ev e JOIN usr u ON u.id = e.user_id JOIN kind k ON k.id = e.kind_id GROUP BY 1,2,3;
CREATE UNIQUE INDEX mv_daily_key ON mv_daily (day, kind, country);

Индексы построились за 1,16 с (43 МБ), 0,21 с (21 МБ) и 3,28 с (GIN, 94 МБ), представление за 0,39 с. Итоговые размеры pg_total_relation_size после DROP TABLE raw и VACUUM ANALYZE:

normalized|287 MB
jsonb|361 MB
matview|584 kB

jsonb на 26% больше таблиц: пользователь скопирован в каждое событие (в usr все 100 000 занимают 5 МБ), а ключи повторяются в каждом документе. Документ с id = 1 (SELECT doc, pg_column_size(doc), octet_length(doc::text) FROM ev_j WHERE id = 1):

{"at": "2026-09-23T07:03:59.673699+00:00", "kind": "cart", "tags": ["t1", "t10"], "user": {"id": 74295, "name": "user74295", "country": "JP"}, "amount": 32.16}

Текстом это 159 байт, а pg_column_size вернул 202: бинарный формат устроен иначе.

Как устроен документ

jsonb хранится не текстом, а деревом. У каждого узла массив 4-байтовых заголовков JEntry отдельно от самих данных, src/include/utils/jsonb.h (тег REL_18_6), строки 136–148 дословно:

typedef uint32 JEntry;

#define JENTRY_OFFLENMASK		0x0FFFFFFF
#define JENTRY_TYPEMASK			0x70000000
#define JENTRY_HAS_OFF			0x80000000

/* values stored in the type bits */
#define JENTRY_ISSTRING			0x00000000
#define JENTRY_ISNUMERIC		0x10000000
#define JENTRY_ISBOOL_FALSE		0x20000000
#define JENTRY_ISBOOL_TRUE		0x30000000
#define JENTRY_ISNULL			0x40000000
#define JENTRY_ISCONTAINER		0x50000000	/* array or object */

Старший бит JENTRY_HAS_OFF говорит, хранится ли в младших 28 битах смещение или длина; смещение пишется не у каждого значения, а у каждого JB_OFFSET_STRIDE-го (строка 178: 32), иначе заголовки плохо сжимались бы. Найти ключ можно без разбора всего текста. Менять на месте PostgreSQL не умеет ничего: UPDATE пишет новую версию строки (MVCC), а jsonb_set собирает новое значение целиком. Сколько из этого попадёт в журнал, решают индексы и TOAST — видно ниже.

Опыт 2: запросы

Два запроса, выручка за 30 дней по странам и поиск по вложенным условиям (тег t7 у пользователя из DE), по разу на раскладку; запускались трижды с \timing on:

SELECT u.country, k.name, sum(e.amount) FROM ev e JOIN usr u ON u.id=e.user_id JOIN kind k ON k.id=e.kind_id
WHERE e.at >= timestamptz '2026-08-30' AND k.name='purchase' GROUP BY 1,2 ORDER BY 1 LIMIT 3;

SELECT doc->'user'->>'country', doc->>'kind', sum((doc->>'amount')::numeric) FROM ev_j
WHERE (doc->>'at')::timestamptz >= timestamptz '2026-08-30' AND doc->>'kind'='purchase' GROUP BY 1,2 ORDER BY 1 LIMIT 3;
SELECT country, kind, sum(total) FROM mv_daily WHERE day >= date '2026-08-30' AND kind='purchase' GROUP BY 1,2 ORDER BY 1 LIMIT 3;

SELECT count(*) FROM ev_tag t JOIN ev e ON e.id=t.ev_id JOIN usr u ON u.id=e.user_id WHERE t.tag='t7' AND u.country='DE';
SELECT count(*) FROM ev_j WHERE doc @> '{"tags":["t7"],"user":{"country":"DE"}}';

Все три раскладки вернули одни и те же три строки (BR|purchase|1701000.28, DE|purchase|1674712.30, ES|purchase|1658891.01). Времена по Time::

Агрегат на документах вдвое медленнее: запрос приводит текст к timestamptz и numeric на каждой из миллиона строк, а в таблице эти типы лежат как есть. Поиск по вложенному условию наоборот в семь раз быстрее: GIN отвечает на @> по индексу (как устроен GIN), а нормализованная версия соединяет три таблицы. Первый запуск GIN занял 24,8 мс, повторные 5,7 и 5,2. Matview выигрывает на запросе, под который построено, и ничего не знает о других.

Опыт 3: что стоит правка

Первая беда денормализации: имя пользователя живёт в 13 событиях (SELECT count(*) FROM ev WHERE user_id = 42 вернул 13). Переименовать его в таблицах — одна строка. В документах — обновить все 13 и не промахнуться. Если приложение обновит одну копию, раскладка расходится:

UPDATE usr SET name = 'Anna' WHERE id = 42;
UPDATE ev_j SET doc = jsonb_set(doc, '{user,name}', '"Anna"') WHERE doc @> '{"user":{"id":42}}';
UPDATE ev_j SET doc = jsonb_set(doc, '{user,name}', '"Anya"') WHERE id = (SELECT min(id) FROM ev WHERE user_id = 42);
SELECT doc->'user'->>'name' AS name, count(*) FROM ev_j WHERE doc @> '{"user":{"id":42}}' GROUP BY 1 ORDER BY 1;

Anna|12
Anya|1

У одного пользователя два имени, и ни одно ограничение не заметило. Теперь цена в WAL: переименование и правка суммы одного события, три прохода. Первый дороже: первая правка страницы после контрольной точки пишет её целиком (full page write). Второй и третий показывают установившуюся цену.

CREATE TEMP TABLE wal_log (what text, n int, wal_bytes bigint);
DO $$
DECLARE a pg_lsn; b pg_lsn; i int;
BEGIN
  FOR i IN 1..3 LOOP
    a := pg_current_wal_insert_lsn(); UPDATE usr SET name = 'Bob' || i WHERE id = 42;
    b := pg_current_wal_insert_lsn(); INSERT INTO wal_log VALUES ('rename: usr, 1 строка', i, b - a);
    a := pg_current_wal_insert_lsn();
    UPDATE ev_j SET doc = jsonb_set(doc, '{user,name}', to_jsonb('Bob' || i)) WHERE doc @> '{"user":{"id":42}}';
    b := pg_current_wal_insert_lsn(); INSERT INTO wal_log VALUES ('rename: ev_j, 13 документов', i, b - a);
    a := pg_current_wal_insert_lsn(); UPDATE ev SET amount = amount + 1 WHERE id = 700000;
    b := pg_current_wal_insert_lsn(); INSERT INTO wal_log VALUES ('amount: ev, 1 строка', i, b - a);
    a := pg_current_wal_insert_lsn();
    UPDATE ev_j SET doc = jsonb_set(doc, '{amount}', to_jsonb((doc->>'amount')::numeric + 1)) WHERE id = 700000;
    b := pg_current_wal_insert_lsn(); INSERT INTO wal_log VALUES ('amount: ev_j, 1 документ', i, b - a);
  END LOOP;
END $$;
SELECT * FROM wal_log ORDER BY what, n;

amount: ev, 1 строка|1|15944
amount: ev, 1 строка|2|184
amount: ev, 1 строка|3|72
amount: ev_j, 1 документ|1|16104
amount: ev_j, 1 документ|2|408
amount: ev_j, 1 документ|3|408
rename: ev_j, 13 документов|1|7456
rename: ev_j, 13 документов|2|5568
rename: ev_j, 13 документов|3|8200
rename: usr, 1 строка|1|144
rename: usr, 1 строка|2|72
rename: usr, 1 строка|3|72

Переименование в таблицах — 72 байта, в документах — от 5,6 до 8,2 КБ, в 77–114 раз больше. Правка суммы одного события — 72 байта против 408, и дело не в размере документа. Повтор на отдельной копии из 100 000 таких же документов (198 байт), второй и третий проход:

jsonb_set amount, таблица без GIN:   72 | 72 байта   (n_tup_hot_upd = 2 из 3)
jsonb_set amount, таблица с GIN:    408 | 408 байт   (n_tup_hot_upd = 0 из 3)

Без индекса по doc правка документа стоит те же 72 байта, что и правка строки: обновление идёт HOT, и в журнал попадают только изменённые байты новой версии. Индекс по doc делает обновление не HOT: новая версия строки, новая запись в первичном ключе и ключи GIN. Переименование платит это 13 раз.

Опыт 4: когда документ уходит в TOAST

Документ до 2 КБ лежит в строке. Крупный выносится в toast-таблицу кусками, и любая правка создаёт его заново: механизм подробно в статье про TOAST, здесь он нужен как цена денормализации. Возьмём 200 заказов по 100 позиций, в двух раскладках: ord_n с ord_line и документ ord_j с массивом lines:

CREATE TABLE ord_n (id int PRIMARY KEY, status text NOT NULL);
CREATE TABLE ord_line (order_id int NOT NULL REFERENCES ord_n, n int NOT NULL, sku text NOT NULL, qty int NOT NULL, PRIMARY KEY (order_id, n));
CREATE TABLE ord_j (id int PRIMARY KEY, doc jsonb NOT NULL);
INSERT INTO ord_n SELECT g, 'new' FROM generate_series(1,200) g;
INSERT INTO ord_line SELECT o, n, md5(o::text || '/' || n) || md5(n::text || '/' || o), 1 + (o*n) % 5 FROM generate_series(1,200) o, generate_series(1,100) n;
INSERT INTO ord_j SELECT o.id, jsonb_build_object('status', 'new',
  'lines', (SELECT jsonb_agg(jsonb_build_object('sku', l.sku, 'qty', l.qty) ORDER BY l.n) FROM ord_line l WHERE l.order_id = o.id)) FROM ord_n o;
VACUUM ANALYZE ord_n, ord_line, ord_j;
SELECT pg_size_pretty(pg_total_relation_size('ord_n') + pg_total_relation_size('ord_line')), pg_size_pretty(pg_total_relation_size('ord_j'));
SELECT avg(pg_column_size(doc))::int, avg(octet_length(doc::text))::int, count(*) FILTER (WHERE pg_column_toast_chunk_id(doc) IS NOT NULL) FROM ord_j;
SELECT reltoastrelid::regclass AS toast FROM pg_class WHERE relname = 'ord_j' \gset
SELECT pg_size_pretty(pg_relation_size(:'toast')), (SELECT count(*) FROM :toast);

ord_n + ord_line|2832 kB
ord_j|1736 kB
7207|8728|200
1600 kB|800

Здесь документы меньше: 1736 КБ против 2832 КБ (в таблицах 20 000 строк позиций, в документах 200 значений). Средний документ хранится в 7207 байт (текстом 8728), все 200 вынесены в toast-таблицу, 800 кусков. Теперь после CHECKPOINT меняем один статус четыре раза в каждой раскладке тем же блоком DO с замером WAL:

UPDATE ord_j SET doc = jsonb_set(doc, '{status}', to_jsonb('s' || i)) WHERE id = 1;
UPDATE ord_n SET status = 's' || i WHERE id = 1;

ord_j: jsonb_set status|1|54712
ord_j: jsonb_set status|2|16392
ord_j: jsonb_set status|3|8120
ord_j: jsonb_set status|4|8120
ord_n: SET status|1|22944
ord_n: SET status|2|72
ord_n: SET status|3|72
ord_n: SET status|4|72

Установившаяся цена смены статуса: 8120 байт против 72, почти весь документ заново. В toast-таблице после четырёх правок n_tup_ins = 816 (800 исходных кусков и 16 новых, по 4 на правку), n_tup_del = 16. Чем длиннее документ, тем дороже правка.

Опыт 5: стоимость свежести матвью

Представление не стоит ничего на записи события и ничего не знает о ней. После UPDATE ev SET amount = amount + 1 WHERE id BETWEEN 1 AND 10000:

SELECT (SELECT sum(total) FROM mv_daily) AS in_matview, (SELECT sum(amount) FROM ev) AS live;

249762273.95|249772273.95

Расхождение ровно 10 000, по единице на строку. Обновить можно двумя способами, оба пересчитывают весь миллион строк:

SELECT pg_current_wal_lsn() AS l0 \gset
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily;
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'l0') AS wal_bytes_concurrently;
-- ещё один UPDATE на 10 000 строк (id 10001..20000)
SELECT pg_current_wal_lsn() AS l0 \gset
REFRESH MATERIALIZED VIEW mv_daily;
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'l0') AS wal_bytes_plain;

Time: 486.807 ms
1156496
Time: 447.503 ms
675072

CONCURRENTLY занял 487 мс и 1,16 МБ WAL, обычный — 447 мс и 0,68 МБ. Время почти одинаково: считается весь запрос представления (его первое построение заняло 0,39 с). Разница в блокировке. Код ExecRefreshMatView, src/backend/commands/matview.c, строки 127–128:

	/* Determine strength of lock needed. */
	lockmode = stmt->concurrent ? ExclusiveLock : AccessExclusiveLock;

Обычный REFRESH берёт AccessExclusiveLock и закрывает читателей на время пересчёта, CONCURRENTLY берёт ExclusiveLock, который SELECT не мешает. Платой служит сравнение старого и нового содержимого (FULL JOIN в refresh_by_match_merge того же файла). Для него документация требует уникальный индекс на представлении без WHERE; на mv_daily это mv_daily_key. Без него CONCURRENTLY падает с ошибкой cannot refresh materialized view concurrently.

Контейнер удаляется вместе с томом: docker rm -f -v dba-normalization-vs-jsonb-vs-matviews.

Что из этого следует

Правило «нормализуй до боли, денормализуй до работы» заменяется вопросом, какую цену ты согласен платить и где:

• Факт, который меняется (имя, цена, статус), храни один раз: 72 байта против 5,6–8,2 КБ и второе имя у пользователя.
• Атрибуты, которые читаются целиком или ищутся по вложенным условиям, подходят для jsonb с GIN: 5 мс против 36 мс на поиске, цена — 94 МБ индекса и 3,3 с построения.
• Крупный документ, который правят по частям, держи в таблицах: 8120 байт на смену статуса растут вместе с документом.
• Агрегат, который читают часто, а считают редко, выносится в matview: 0,2 мс против 40 мс, и 0,45 с на каждое обновление всего миллиона строк. Свежесть он не обещает.

Чек-лист:

1. Для каждого поля спроси, меняется ли оно после записи. Меняющееся не копируется в документ.
2. Прежде чем заменить соединение документом, измерь pg_column_size и размер индекса: jsonb в опыте оказался на 26% больше таблиц.
3. Оцени, сколько раз в секунду правят документ: с GIN-индексом (в опыте 94 МБ) каждая правка не HOT и пишет в журнал в 5,7 раза больше, чем без него.
4. Для матвью заведи уникальный индекс заранее и решай, кто и когда зовёт REFRESH; без этого данные устаревают молча.
5. Измеряй pg_wal_lsn_diff до и после своей правки на своих данных: числа выше получены на одной форме событий.