-- Синтетический набор для трека 5.2 (запускается research/track2.py в отдельной БД research). -- Переменные psql: :seed, :users, :chats, :messages, :zipf_s. -- Размеры чатов неравномерны: доля чата ранга k пропорциональна 1/k^s (закон Ципфа). -- Сообщения вставляются в глобальном хронологическом порядке, чаты перемешаны во времени — -- как в реальном мессенджере, поэтому строки большого чата разбросаны по всей heap. \set ON_ERROR_STOP on SELECT setseed(:seed); INSERT INTO users (id, username, display_name, password_hash, created_at) SELECT gen_random_uuid(), 'r_' || g, 'Research user ' || g, '$scrypt$ln=15,r=8,p=3$synthetic$synthetic', timestamptz '2026-01-01 00:00:00Z' + g * interval '1 second' FROM generate_series(1, :users) g; CREATE TEMP TABLE r_users AS SELECT row_number() OVER (ORDER BY username) AS n, id FROM users; CREATE TEMP TABLE r_chats AS SELECT g AS rank, gen_random_uuid() AS id FROM generate_series(1, :chats) g; -- created_by временно = первый пользователь, ниже заменяется на участника с position = 0. INSERT INTO chats (id, title, created_by, created_at) SELECT c.id, 'research chat #' || c.rank, (SELECT id FROM r_users WHERE n = 1), timestamptz '2026-01-02 00:00:00Z' + c.rank * interval '1 second' FROM r_chats c; -- От 2 до 50 участников; LATERAL коррелирован с c.rank, поэтому random() вычисляется для каждого чата. INSERT INTO chat_members (chat_id, user_id, position) SELECT c.id, u.id, (row_number() OVER (PARTITION BY c.id ORDER BY u.n) - 1)::smallint FROM r_chats c CROSS JOIN LATERAL ( SELECT DISTINCT 1 + floor(random() * :users)::int AS n FROM generate_series(1, 2 + (c.rank * 7919) % 49)) pick JOIN r_users u USING (n); UPDATE chats SET created_by = m.user_id FROM chat_members m WHERE m.chat_id = chats.id AND m.position = 0; CREATE TEMP TABLE r_w AS SELECT rank, id, 1.0 / power(rank, :zipf_s) AS w FROM r_chats; CREATE TEMP TABLE r_cum AS SELECT rank, id, coalesce(sum(w) OVER (ORDER BY rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) / sum(w) OVER () AS lo, sum(w) OVER (ORDER BY rank) / sum(w) OVER () AS hi FROM r_w; CREATE TEMP TABLE r_mem AS SELECT chat_id, array_agg(user_id ORDER BY position) AS members FROM chat_members GROUP BY chat_id; INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), c.id, m.members[1 + floor(random() * cardinality(m.members))::int], 'synthetic message ' || x.g || repeat(' lorem ipsum', (x.g % 23)::int), timestamptz '2026-02-01 00:00:00Z' + x.g * interval '5 seconds' FROM (SELECT g, random() AS r FROM generate_series(1, :messages) g) x JOIN r_cum c ON x.r >= c.lo AND x.r < c.hi JOIN r_mem m ON m.chat_id = c.id ORDER BY x.g; VACUUM (ANALYZE) users; VACUUM (ANALYZE) chats; VACUUM (ANALYZE) chat_members; VACUUM (ANALYZE) messages;