Как мы переносили аналитику из PostgreSQL в ClickHouse

Привет, Хабр! Меня зовут Кирилл, одной из задач, которой я занимаюсь в Just AI, является пользовательская аналитика. В этой статье я хочу рассказать о нашем опыте миграции этой аналитики на новую СУБД ClickHouse. О том, с какими нюансами пришлось столкнуться и как мы их решали. Посмотрим на примерах, как изменилась схема и запросы к БД и удалось ли получить прирост в производительности.

Наша компания занимается разработкой платформ для создания чат-ботов и голосовых ассистентов. Всё общение клиентов сохраняется, и в дальнейшем владелец бота может искать диалоги, клиентов или сообщения по множеству различных фильтров, строить графики, скачивать отчеты и т. д. 

Изначально все эти данные хранились в PostgreSQL, и, пока их было немного, все работало достаточно быстро. Шли года, приходило все больше крупных пользователей, и постепенно наступила ситуация, что для построения какого-нибудь простого графика за две недели перестало хватать таймаута в 10 минут. Тут-то мы и поняли, что настало время что-то менять…

Мы, конечно, пытались оптимизировать запросы, думали о добавлении дополнительных индексов или представлений. В некоторых случаях нам даже удавалось немного улучшить производительность, но время выполнения запросов все равно оставалось слишком велико. 

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

СlickHouse

Стало понятно, что подправить текущую реализацию нам не удастся и нужно искать новый инструмент. В конечном итоге мы остановились на такой СУБД, как ClickHouse.

ClickHouse — столбцовая СУБД для онлайн-обработки аналитических запросов (OLAP) от Яндекс. Подробнее об этой СУБД можно почитать в документации, я приведу только несколько ее особенностей:

1. Данные одного столбца хранятся физически рядом, и при запросах происходит чтение только нужных колонок. Благодаря этому удается значительно уменьшить объем читаемых данных.

2. Не поддерживается привычное обновление данных. Существуют некоторые обходные пути, но они имеют свои ограничения. О том, как мы реализовывали обновление данных в ClickHouse, я расскажу во второй части статьи.

3. При чтении из БД вынимается достаточно большое количество строк. Из-за этого производительность точечного чтения записей может быть ниже по сравнению с реляционной БД.

4. Нет транзакций.

5. Можно создать только один индекс.

6. Множественные join’ы не поддерживаются.

7. В запросе должна участвовать только одна большая таблица, остальные должны помещаться в память. 

8. Синтаксис похож на SQL, при этом добавлено множество удобных конструкций и функций для построения запроса.

Несмотря на некоторые минусы и риски, мы решили, что ClickHouse больше всего подходит для нашей задачи. В следующем разделе хочу на практике рассмотреть, насколько сильно поменялась реализация для новой СУБД, как обходили ее ограничения и удалось ли получить прирост в производительности.

Реализация и тестирование

Для наглядности сильно упростим нашу реальную схему данных и представим, что у нас есть следующие сущности:

• клиент — информация о клиенте, который пишет боту

• сессия — информация о конкретной сессии общения, у каждого клиента может быть несколько различных сессий

• сообщение — сообщение клиента и ответ бота на него  

Схема базы данных

Для реляционной БД у нас получается следующая схема: 

create table client (
   id            bigserial primary key,
   natural_id    text      not null,
   bot_id        bigint    not null,
   start_time    timestamp not null,
   end_time      timestamp not null,
   message_count integer   not null
);

create table session (
   id            bigserial primary key,
   natural_id    text      not null,
   bot_id        bigint    not null,
   client_id     bigint    not null references client,
   start_time    timestamp not null,
   end_time      timestamp not null,
   message_count integer   not null
);

create table message
(
   id              bigserial primary key,
   natural_id      text      not null,
   bot_id          bigint    not null,
   client_id       bigint    not null references client,
   session_id      bigint    not null references session,
   timestamp       timestamp not null,
   question        text,
   answer          text,
   another_columns text
);

CREATE INDEX message_bot_id_timestamp_idx ON message (bot_id, timestamp);
CREATE INDEX message_session_id_idx ON message (session_id);

Теперь нужно создать аналогичную схему в ClickHouse. Так как это столбцовая СУБД, предполагается, что в запросах будет участвовать только одна большая таблица, и итоговая схема сократится до одной таблицы:

create table message
(
   timestamp       UInt64,
   bot_id          String,
   client_id       String,
   session_id      String,
   message_id      String,
   is_new_session  UInt8,
   is_new_client   UInt8,
   question        String,
   answer          String,
   labels          Array(UInt64),
   another_columns String,
   comment         String
)
   engine = MergeTree()
       PARTITION BY toYYYYMM(toDate(timestamp))
       ORDER BY (bot_id, client_id, session_id, timestamp);

Индексы

Из индексов в реляционной БД создадим только два: составной индекс на bot_id и timestamp, и, для того чтобы мы могли быстро получать сообщения конкретной сессии, индекс на поле session_id. 

В ClickHouse мы можем создать только один индекс. В этом порядке данные будут отсортированы физически. Для нашего примера выбираем bot_id, client_id, session_id, timestamp.

Тестовые данные

Для тестирования создадим одинаковый набор тестовых данных в двух БД. Набор содержит 100 млн записей в таблице message. Эти записи распределены между несколькими ботами следующим образом:

• bot_id=1: 50 млн сообщений, 10 млн сессий и клиентов

• bot_id=2: 25 млн сообщений, 5 млн сессий и клиентов

• bot_id=3: 10 млн сообщений, 1 млн сессий и клиентов

• bot_id=10-20: 15 млн сообщений, 100 тыс сессий и клиентов

Для имитации того, что каждая запись содержит множество данных, поле another_columns каждой записи содержит случайную строку из 5000 символов. 

Объем занимаемого места для PostgreSQL получился 582 ГБ, для ClickHouse 476 ГБ (481 ГБ до сжатия). Так как при генерации тестовых записей использовались случайные последовательности строк, ClickHouse сжал данные только на 1%. Для примера, на одном из наших продов размер данных до сжатия равен 1005 GB, после сжатия — 90 GB. 

Для такого объема данных в ClickHouse на машине выделено достаточно мало оперативной памяти. Как указано в рекомендациях, увеличение RAM может значительно улучшить производительность. Исходя из этого, будем допускать, что на более мощном железе итоговое время выполнения может оказаться меньше.

Кроме того, время выполнения может сильно зависеть от того, какие данные читаются, горячие или холодные. Если время будет отличаться, время на холодных данных я буду указывать в скобках.

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

Простые аналитические запросы

Для начала поэкспериментируем на некоторых простых запросах для получения аналитики по сообщениям:

Как мы видим, для сообщений подобные запросы на ClickHouse выполняются значительно быстрее. Но что насчет аналитики по сессиям или клиентам? 

Чтобы выполнить подобные запросы в Postgres, нам достаточно поменять таблицу и, за счет того, что данных в этих таблицах в несколько раз меньше, все должно выполняться быстрее. А вот для ClickHouse нам придется работать все с той же большой таблицей и во многих случаях агрегировать данные по session_id или client_id.

Замерим аналогичные запросы для сессий. В нашем наборе данных количество записей в таблице session получилось около 16 млн.

Для такой задачи производительность запросов в ClickHouse значительно уменьшилась. За счет того, что в Postgres анализировалась таблица намного меньшего размера и каждая запись занимает немного места, время получения количества уникальных сессий в нем оказалось меньше. Для остальных запросов этого оказалось недостаточно, и ClickHouse стал быстрее.

-- PostgreSQL
-- Количество сообщений
select count(*) from message;
-- Распределение количества сообщений по годам
select extract(year from timestamp) "year", count(*)
from message
group by year;
-- Распределение количества сообщений по месяцам
select extract(year from timestamp) "year", extract(month from timestamp) "month", count(*)
from message
group by year, month;
-- Количество сессий
select count(*) from session;
-- Распределение количества новых сессий по годам
select extract(year from start_time) "year", count(*)
from session
group by year;
-- Распределение количества новых сессий по месяцам
select extract(year from start_time) "year", extract(month from start_time) "month", count(*)
from session
group by year, month;

-- ClickHouse
-- Количество сообщений
select count(*) from message;
-- Распределение количества сообщений по годам
select toYear(toDate(timestamp)) year, count()
from message
group by year;
-- Распределение количества сообщений по месяцам
select toYear(toDate(timestamp)) year, toMonth(toDate(timestamp)) month, count()
from message
group by year, month;
-- Количество сессий
select uniq(session_id) from message
-- Распределение количества новых сессий по годам
select toYear(toDate(min_ts)) year, count()
from (
      select min(timestamp) min_ts
      from message
      group by session_id
         )
group by year;
-- Распределение количества новых сессий по месяцам
select toYear(toDate(min_ts)) year, toMonth(toDate(min_ts)) month, count()
from (
      select min(timestamp) min_ts
      from message
      group by session_id
         )
group by year, month;

Фильтрация сообщений

Теперь попробуем построить более сложный запрос. Представим, что перед нами стоит следующая задача:

Получить первые 10 сообщений пользователя для бота с bot_id=1, в которых содержится ‘привет’, при этом они должны находиться в сессиях, где больше 3 сообщений.

Запрос в реляционной БД выглядит достаточно просто:

select *
from message inner join session on message.session_id = session.id
where message.bot_id = 1 and 
			message.question like '%привет%' and 
			session.message_count > 3
order by message.timestamp
limit 10;

Результат будет зависеть от того, насколько быстро по времени попадутся 10 удовлетворяющих условию записей. Если результирующих строк будет меньше 10, то придется пройтись по всем записям бота. Получились следующие результаты: 

• bot_id=1 (50 млн): запрос не выполнился за 30 минут

• bot_id=2 (25 млн): запрос не выполнился за 30 минут

• bot_id=3 (10 млн): запрос выполнился за 3 минуты 50 секунд

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

select *
from message
where bot_id = 'botId-1' and question like '%привет%' and 
      session_id in (
   										select session_id 
        							from message 
        							where bot_id = 'botId-1' 
        							group by session_id 
        							having count() > 3
										)
order by timestamp
limit 10;

Здесь тоже есть большая зависимость от того, сколько строк удовлетворяет условию до ограничения limit. В наших тестовых данных я сделал так, чтобы условию на вхождение `привет` удовлетворяло всего 6 строк. Для bot_id=1 в этом случае запрос выполняется за 1 минуту 20 секунд.

Что будет, если условию будут удовлетворять тысячи записей? Если мы, например, поменяем условие с `%привет%` на `%a%`, то в этом случае запрос не выполнится даже за 5 минут. Несмотря на то, что у нас есть ограничение записей через limit, из-за сортировки происходит чтение всех столбцов каждой строки, которая удовлетворяет условию where. Это сильно сказывается на производительности. 

Чтобы решить эту проблему, мы можем поделить этот запрос на два. Сначала получаем только идентификаторы нужных сообщений, а после, в отдельном запросе, получаем остальную необходимую информацию по простому фильтру.

Также чтобы ускорить второй запрос, помимо идентификаторов, можно получить столбцы из индекса.

Итого у нас получаются следующие два запроса:

-- 1
select client_id, message_id
from message
where bot_id = 'botId-1' and question like '%привет%' and 
			session_id in (
   										select session_id 
        							from message 
        							where bot_id = 'botId-1' 
        							group by session_id 
        							having count() > 3
										)
order by timestamp
limit 10;

-- 2
select *
from message
where bot_id = 'botId-1' and client_id in (...) and message_id in (...)

В результате время первого стало около 53 секунд вне зависимости от того, сколько записей удовлетворяют условию. Время второго 52 мс (2.3 c). Если выполнить первый запрос для bot_id=3 (10 млн), то время будет около 908 мc (1.1 с).

Попробуем еще больше оптимизировать первый запрос. Смущает то, что мы два раза обращаемся к большой таблице message. Постараемся сократить количество вызовов до одного.

В ClickHouse есть множество различных удобных функций и конструкций, позволяющих более комфортно работать с агрегированными данными. Примером такой функции является  groupArrayIf, позволяющая собрать массив значений по условию. Также довольно удобным инструментом является конструкция “array join”, которая может “раскрыть” агрегированные данные обратно во множество отдельных строк.

Используя эти конструкции, перепишем наш первый запрос:

select tupleElement(tuple_arr, 2) message_id
from (
        select session_id, groupArrayIf(tuple(timestamp, message_id), question like '%привет%') as tuple_arr
        from message
        where bot_id = 'botId-1'
        group by session_id
        having count() > 3
		)
        array join tuple_arr
order by tupleElement(tuple_arr, 1)
limit 10;

Итого получились следующие результаты:

• Для bot_id=1 (50 млн): 7.3 с (14.1 с)

• Для bot_id=2 (25 млн): 2.1 с (11.9 с)

• Для bot_id=3 (10 млн): 516 мс (3.8 с)

Заключение

В результате переезда на новую СУБД нам пришлось кардинально поменять существующую реализацию. Изменение схемы базы данных и множество других особенностей, таких как ограничения на обновление полей или выгрузка данных в память, значительно усложнило запросы к БД. 

Несмотря на все ограничения, нам удалось перенести весь функционал на ClickHouse и добиться, чтобы все запросы выполнялись за приемлемое для пользователя время. 

Для большинства случаев ClickHouse оказался на порядок быстрее чем Postgres, что не удивительно, так как он разрабатывался для обработки аналитических запросов. В некоторых ситуациях, например при получении точечных данных, Postgres может оказаться быстрее, но так как ClickHouse в подобных запросах все равно показывает допустимое время, мы готовы с этим жить.

Всем спасибо за внимание. Напомню, что вторую часть статьи, где я рассказываю как мы реализовывали обновление данных в ClickHouse, можно почитать по следующей ссылке.