Comments 15
Можно еще проще и быстрее
select CounterID, url FROM
(
select CounterID, arraySlice(arrayFilter(x -> (x.2) like '%yandex.ru%', arraySort(x-> x.1, groupArray(tuple(ClientEventTime, StartURL)))),1,10).2 as urls
FROM visits_v1
--where UserID = 5732566399506708088 фильтр по пользователю при желании
GROUP BY CounterID
HAVING count() > 2
)
array join urls as urlПолучаем первые 10 url пользователя в которых есть подстрока yandex.ru для счетчиков у которых у него есть 3 или больше записей. Вроде как у вас условие. Если надо вывести другое поле, то его можно запихнуть в тот же тупл.
Пример тут работает https://play.clickhouse.com
Гипотеза - а если бы в Postgres поменять схему сущностей на ту, что применялась для ClickHouse, но на ClickHouse не переходить - а вдруг это тоже дало бы ускорение, но с меньшим гемором?
PostgreSQL
Количество сообщений в таблице message
Распределение количества сообщений по годам
Распределение количества сообщений по месяцам
Переходить на на порядок более нишевый инструмент, аффилированный с токсичной корпорацией, не попробовав типовые методы постгреса — вью, мат вью, разного рода нормализацию таблиц и индексы — это конечно интересно.
Странно, что уже сразу кубернетес и hadoop стек не стали завозить.
На эту тему есть прекрасная статья — https://blog.bradfieldcs.com/you-are-not-google-84912cf44afb
К сожалению нет. Основная проблема не в том, что в pg множество таблиц, а в том, что долгое чтение всех данных. Например, в секции "Простые аналитические запросы" замеряется время на одной таблице без всяких join'ов, на этом примере схема не важна.
Спасибо за статью, но осталось несолько вопросов.
Аналитику пернесли, а остальное осталось в PG ? Или это изначально чисто аналитическая БД была ? Если чисто аналитическая, то почему выбрали PG изначально ?
Как загружаете данные в Clickhouse ?
Аналитика была частью достаточно большой схемы, вынесли только ее. Почему изначально выбрали PG сказать не могу, было до меня. Думаю на ранних этапах продукта пошли по пути наименьшего сопротивления и добавили функционал в существующую схему.
По поводу загрузки данных в ClickHouse. Данные аналитики у нас отправляют несколько сервисов в небольшой сервис очередь (самописная kafka на минималках). Новый сервис для аналитики периодически вытягивает эти данные батчами и записывает в ClickHouse.
Дополню относительно загрузки данных. Перед тем как загрузить пачку данных, мы делаем select по id и вставляем только те данные, которых еще в CH нет. Такой нехитрый способ позволяет убедиться в отсутствии дублирования данных аналитики.
А почему сравнение производилось с ванильным PostgreSQL, а не с cstore_fdw от Citus Data?
Тоже хотел спросить про это.
В последних версиях Citus Data добавили колоночное хранилище. Ограничения у него плюс-минус такие же как у ClickHouse. Например нельзя обновлять данные. Но за счёт использования партицирования таблицы, можно "горячие" данные держать в part-е с обычным строковым хранилищем, а "холодные" part-ы переводить в колоночное хранилище с получением всех его преимуществ.
Сравнение производилось с обычным PostgreSQL, так как целью статьи было показать как мы переходили с текущей реализации на колоночную, а не сравнить аналитические бд.
Перед реализацией рассматривали разные инструменты, в том числе Citus Data. В конечном итоге решили, что ClickHouse нас полностью устраивает и скорее всего в конечном счете окажется проще по сравнению с другими вариантами, например, за счет таких вещей, как партиционирование из коробки.
Обратил внимание, что почти все запросы идут по timestamp. Отсюда резонный вопрос. А чем TimeScaleDB не угодил? В нем таблицы client и session можно было бы иметь в виде materialized views в реальном времени актуализируемые механизмом сontinuous aggregates. А производительность у гипертаблиц TimeScaleDB выше, чем у обычных таблиц, не менее, чем на порядок.
Как мы переносили аналитику из PostgreSQL в ClickHouse