Каждые 10 минут в русскоязычном эфире Twitch и VK Видео Live в среднем 7 200 каналов. Писать их как есть - больше миллиона строк в сутки. Я храню агрегаты, и за четыре недели вышло 2 млн строк. Расскажу, как это устроено и где я три недели искал проблему не там.

Я Максим, разработчик, вечерами пишу LanolStream - инструменты для стримеров, которые мы делаем вместе со стримершей lanolkid. Стримеры часто спрашивают «во сколько лучше стримить» и «много ли у меня зрителей для моей игры». Чтобы отвечать цифрами, а не ощущениями, нужен весь рынок, а не только наши каналы.

Откуда данные

Twitch: Helix GET /streams?language=ru по 100 эфиров на страницу, с курсором. В вечерний пик в эфире ~12 700 русскоязычных каналов, это ~130 страниц. На всякий случай стоит предел в 150 страниц: если упёрлись, проход помечается capped.

VK Видео Live: каталог онлайн-каналов через API приложения, по 200 на страницу (мы там зарегистрированы как приложение для входа и чат-бота).

Пока листаешь страницы, эфиры двигаются, и канал может попасться дважды. Оставляем замер с большим онлайном:

export function aggregatePass(streams: readonly MarketStream[]): PassRows {
  const byChannel = new Map<string, ChannelSample>();
  for (const s of streams) {
    if (!s.channelId) continue;
    const viewers = Math.max(0, Math.round(Number(s.viewers) || 0));
    const prev = byChannel.get(s.channelId);
    if (prev && prev.viewers >= viewers) continue;
    byChannel.set(s.channelId, { channelId: s.channelId, login: s.login, category: s.category.trim(), viewers });
  }
  // дальше раскладываем каналы по корзинам «размер × категория»
}

Почему не храним сырые замеры

Каждые 10 минут - в среднем ~7 200 эфиров, то есть больше миллиона строк в сутки, а за полгода под 190 миллионов. Для вопросов «когда стримить» и «сколько у похожих каналов» столько не нужно. Храним два агрегата:

? market_slots: день × час × площадка × категория × размер канала. Размер - корзина онлайна (0-4, 5-14, 15-49, 50-149, 150-499, 500+). Внутри - сумма каналов и зрителей по проходам. ? market_channels: неделя × канал. Внутри - число замеров (1 замер = 10 минут эфира), сумма онлайна и пик.

Средние считаются делением на число проходов из market_passes, поэтому пропущенный проход не портит цифры. Слоты живут 42 дня, каналы - 8 недель, чистка раз в сутки в 4 утра. За четыре недели: 1,38 млн строк слотов (460 МБ) и 641 тысяча строк «канал × неделя» (185 МБ).

Два прохода не должны идти вместе

Cron дёргает POST /api/cron/market раз в 10 минут. Если проход затянулся, второй не должен начать писать те же суммы. Вместо локов - аренда в одной строке Postgres:

update market_poll set lease_id = $1, lease_until = now() + interval '9 minutes'
 where id = 1 and (lease_until is null or lease_until < now())
returning id;

Не вернулась строка - кто-то уже работает, выходим. Упал процесс посреди прохода - аренда истечёт сама через 9 минут.

Вставка - пачками по 1000 строк через unnest и on conflict … do update: каждый проход прибавляется к строке своего часа, а у канала за неделю растёт число замеров и обновляется пик.

Грабли

? Категория. Сначала в market_channels при каждом замере писалось category = excluded.category, то есть у канала за неделю оставалась последняя игра. Стримил шесть часов Dota 2 и полчаса Just Chatting под конец - и он «стример Just Chatting». Теперь рядом копится счётчик замеров по каждой категории в jsonb, прямо в том же on conflict:

categories = case when excluded.category = '' then market_channels.categories
  else market_channels.categories || jsonb_build_object(excluded.category,
    coalesce((market_channels.categories ->> excluded.category)::int, 0) + 1) end

Главная игра недели - ключ с наибольшим значением. У старых недель счётчика нет, там честно остаётся «последняя игра».

? VK днём. В 15:00 сборщик видел ~140 каналов VK при 100+ зрителях на канал, а в 22:00 - ~1 500 при 18. У Twitch каналов между 15:00 и 22:00 становится в 1,6 раза больше, у VK - в 11. Три недели я думал, что днём каталог VK отдаёт не всех, и даже написал это в черновике статьи ?

Оказалось, грабли мои. VK выкидывает из страницы скрытые эфиры, поэтому страница посреди выдачи бывает короче 200, а я считал короткую страницу последней. Днём такая попадалась уже на первой, и проход на ней заканчивался. Теперь следующее смещение беру из extra.offset, конец - только is_last или пустая страница, а в журнал на каждый проход пишу размеры страниц, чтобы такое было видно сразу. Данные VK до 19 октября днём недосчитаны, поэтому VK по часам считаю только с этой даты.

После починки в 15:00 сборщик видит ~1 300 каналов VK, в 22:00 ~2 100. В 1,6 раза, ровно как у Twitch. Так что теперь VK по часам тоже считаю, а за неделю каналов VK вышло 12,3 тысячи вместо прежних 9 с небольшим.

Что получилось увидеть

? у 80 % русскоязычных каналов Twitch в среднем меньше пяти зрителей, а топ-1 % собирает 73 % часов просмотра; ? «днём конкуренция меньше» - миф: в 6 утра на канал в эфире ~32 зрителя, в 20 часов ~40; ? на VK Видео Live пик зрителей на канал не вечером, а в 17-18 часов: ~18 против ~11 в 21 час; ? сверка с TwitchTracker сошлась: у них в начале октября ~242,5 тысячи зрителей и ~7 тысяч каналов одновременно, у нас за 5-11 октября 242 тысячи и 6,2 тысячи.

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

Страница на каждый канал с 3+ неделями замеров: lanolstream.ru/c/ник. На ней ничего, чего не видно на самой площадке, а свою страницу можно скрыть, войдя своим каналом.

Что бы вы сделали иначе? Особенно интересно, как вы храните временные ряды такого размера без TimescaleDB.

PS: Всё это крутится на одном VPS в Москве рядом с основным приложением ?