Сравнение ClickHouse и PostgreSQL

Сейчас мы сравним возможности ClickHouse и PostgreSQL для аналитической обработки данных на реальном примере - открытом датасете COVID-19 Open Data от Google. Этот датасет объединяет эпидемиологическую, демографическую, экономическую, медицинскую и другую информацию по тысячам регионов мира, предоставляя уникальные возможности для анализа на больших объёмах данных.

Почему важен выбор СУБД для аналитики

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

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

О датасете

COVID-19 Open Data - это глобальный мета-набор, включающий данные по эпидемиологии, демографии, экономике, здравоохранению, мобильноcти, погоде и мерам реагирования правительств. Данные агрегированы по регионам и датам, что позволяет строить сложные аналитические отчёты и визуализации на разных уровнях детализации (страна, регион, город и т.д.). Этот большой датасет поделен на несколько датасетов меншего размера. Мы будем работать с датасетом epidemiology.csv, скачать который можно по этой ссылке. По ссылке находится zip-архив, который необходимо распаковать.

Описание датасета `epidemiology.csv`

Датасет COVID-19 Open Data от Google содержит эпидемиологические данные по тысячам регионов мира, представленные в формате временного ряда.

Назначение

Используется для анализа динамики COVID-19:

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

Структура таблицы

Поле	Описание
`date`	Дата записи (формат YYYY-MM-DD)
`location_key`	Уникальный идентификатор региона (страна/регион/подрегион)
`new_confirmed`	Новые подтвержденные случаи за дату
`new_deceased`	Новые случаи смерти за дату
`new_recovered`	Новые выздоровевшие за дату
`new_tested`	Новые тесты за дату
`cumulative_confirmed`	Общее количество подтвержденных случаев на дату
`cumulative_deceased`	Общее количество смертей на дату
`cumulative_recovered`	Общее количество выздоровевших на дату
`cumulative_tested`	Общее количество тестов на дату

Пример кодов регионов России

Поле key отражает административную иерархию. Примеры:

RU — Россия (вся страна)
RU-MOW — Москва
RU-SPE — Санкт-Петербург
RU-KDA — Краснодарский край
RU-SVE — Свердловская область
RU-NVS — Новосибирская область
RU-KHM — Ханты-Мансийский автономный округ

Формат соответствует стандарту ISO 3166-2 и может включать до 3 уровней (например, US-CA-06037 для округа в США).

Особенности

Некоторые значения могут быть пустыми (NULL).
Удобен для временного анализа, фильтрации по регионам и агрегаций.
Хорошо подходит для OLAP-нагрузки.

Цель сравнения

В ходе этого вопроса мы:

Покажем, как устроены архитектуры ClickHouse и PostgreSQL с точки зрения аналитики.
Сравним их производительность и удобство работы на реальных аналитических сценариях с использованием COVID-19 Open Data.
Продемонстрируем, почему ClickHouse является предпочтительным выбором для задач, связанных с анализом больших объёмов данных, агрегациями и быстрой обработкой запросов.

Импорт данных

PosgreSQL

Создайте таблицу:

sql

CREATE TABLE epidemiology (
    date DATE,
    location_key TEXT,
    new_confirmed BIGINT,
    new_deceased BIGINT,
    new_recovered BIGINT,
    new_tested BIGINT,
    cumulative_confirmed BIGINT,
    cumulative_deceased BIGINT,
    cumulative_recovered BIGINT,
    cumulative_tested BIGINT
);

С помощью команды COPY переместите содержимое csv в таблицу:

sql

COPY epidemiology FROM '/tmp/epidemiology.csv' DELIMITER ',' CSV HEADER;

Обратите внимание, что нужно указать правильный путь к файлу epidemiology.csv.

ClickHouse

Создайте таблицу:

sql

CREATE TABLE epidemiology (
    date Date,
    location_key LowCardinality(String),
    new_confirmed Int32,
    new_deceased Int32,
    new_recovered Int32,
    new_tested Int32,
    cumulative_confirmed Int32,
    cumulative_deceased Int32,
    cumulative_recovered Int32,
    cumulative_tested Int32
)
ENGINE = MergeTree
ORDER BY (location_key, date);

В ClickHouse нет аналога функции COPY. Поэтому загрузим данные с помощью консольного клиента. Для этого в командной строке выполните:

bash

clickhouse-client --password --query="INSERT INTO epidemiology FORMAT CSVWithNames" < /tmp/epidemiology.csv

Загрузка данных произошла значительно быстрее, чем в PostgreSQL.

Обратите внимание, что нужно указать правильный путь к файлу epidemiology.csv.

Подсчет строк

Сравните выполнение запроса на подсчет количества строк в ClickHouse и в PostgreSQL

sql

SELECT count(*) FROM epidemiology;

Подсчет количества уникальных стран

Сравните выполнение запроса c DISTINCT в ClickHouse и в PostgreSQL

sql

SELECT DISTINCT substring(location_key, 1, 2) FROM epidemiology

Сумма количества новых заболеваний по регионам

Сравните выполнение запроса c агрегацией и группировкой в ClickHouse и в PostgreSQL

sql

SELECT location_key, sum(new_confirmed)
FROM epidemiology
GROUP BY location_key
ORDER BY sum(new_confirmed) DESC
LIMIT 10;

Статистика по странам

Сравните выполнение запроса в ClickHouse и в PostgreSQL.

Этот запрос отбирает страны, по которым накоплено не менее 300 дней данных и среднее число новых случаев заболевания COVID-19 превышает 500 в день. Для каждой страны вычисляется среднее количество новых случаев (avg_daily_cases) и присваивается ранг (rank_by_avg) в соответствии со средним значением кумулятивных подтверждённых случаев (avg(cumulative_confirmed)) — чем выше общее число, тем выше ранг. Результаты сортируются по возрастанию ранга, то есть в начале списка будут страны, в которых эпидемия накопила наибольшее количество подтверждённых случаев за всё время.

sql

SELECT
    substring(location_key, 1, 2) AS country_code,
    count(*) AS days_reported,
    avg(new_confirmed) AS avg_daily_cases,
    rank() OVER (ORDER BY avg(cumulative_confirmed) DESC) AS rank_by_avg
FROM epidemiology
GROUP BY country_code
HAVING count(*) >= 300 AND avg(new_confirmed) > 500
ORDER BY rank_by_avg asc

Исследование влияния объёма данных и числа ядер на производительность ClickHouse и PostgreSQL

В этом задании вы проведёте серию экспериментов, чтобы сравнить производительность аналитических запросов в PostgreSQL и ClickHouse при увеличении объёма данных и числе задействованных ядер процессора.

Подготовка данных

Датасет epidemiology.csv уже загружен в обе СУБД.

Чтобы получить увеличенные версии датасета, вставьте его в таблицу повторно следующим образом:

Для PostgreSQL:

sql

COPY epidemiology FROM '/tmp/epidemiology.csv' DELIMITER ',' CSV HEADER;

Для ClickHouse:

bash

clickhouse-client --password --query="INSERT INTO epidemiology FORMAT CSVWithNames" < /tmp/epidemiology.csv

Сколько раз вы скопируете датасет, во столько раз данных в таблице станет больше. Вам необходимы датасеты размером x1 (исходный, примерно 12 млн строк), x2 (в два раза больше, примерно 25 млн строк), x4 (в четыре раза больше, примерно 50 млн строк), x8 (в восемь раз больше, примерно 100 млн строк).

Проведение эксперимента

Для каждого объёма данных (x1, x2, x4, x8) выполните следующие шаги:

Измените число доступных ядер в виртуальной машине:

1 ядро
2 ядра
4 ядра

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

Выводы по эксперименту

По результатам полученных данных написать выводы, в которых отразить:

Какая СУБД показала более стабильное время выполнения при увеличении объёма данных? Почему?
Какой запрос оказался самым тяжёлым для PostgreSQL? Для ClickHouse? Почему?
Какой эффект дало увеличение числа ядер для PostgreSQL и ClickHouse? Где масштабирование оказалось эффективнее?
Есть ли в результатах момент, когда PostgreSQL справлялся лучше, чем ClickHouse? Почему?
Насколько важно предварительное агрегирование или использование специализированных движков (например, в ClickHouse)?
Можно ли считать ClickHouse универсальной заменой PostgreSQL в аналитике? Какие есть ограничения?
Что бы вы предложили для оптимизации работы PostgreSQL на больших объёмах? Какие инструменты или настройки могли бы помочь?

Clickhouse

Работа с документной СУБД MongoDB

Python

JavaScript

Сравнение ClickHouse и PostgreSQL

Почему важен выбор СУБД для аналитики

О датасете

Описание датасета `epidemiology.csv`

Назначение

Структура таблицы

Пример кодов регионов России

Особенности

Цель сравнения

Импорт данных

PosgreSQL

ClickHouse

Подсчет строк

Подсчет количества уникальных стран

Сумма количества новых заболеваний по регионам

Статистика по странам

Исследование влияния объёма данных и числа ядер на производительность ClickHouse и PostgreSQL

Подготовка данных

Проведение эксперимента

Выводы по эксперименту

Сравнение ClickHouse и PostgreSQL ​

Почему важен выбор СУБД для аналитики ​

О датасете ​

Описание датасета epidemiology.csv ​

Назначение ​

Структура таблицы ​

Пример кодов регионов России ​

Особенности ​

Цель сравнения ​

Импорт данных ​

PosgreSQL ​

ClickHouse ​

Подсчет строк ​

Подсчет количества уникальных стран ​

Сумма количества новых заболеваний по регионам ​

Статистика по странам ​

Исследование влияния объёма данных и числа ядер на производительность ClickHouse и PostgreSQL ​

Подготовка данных ​

Проведение эксперимента ​

Выводы по эксперименту ​

Сравнение ClickHouse и PostgreSQL

Почему важен выбор СУБД для аналитики

О датасете

Описание датасета `epidemiology.csv`

Назначение

Структура таблицы

Пример кодов регионов России

Особенности

Цель сравнения

Импорт данных

PosgreSQL

ClickHouse

Подсчет строк

Подсчет количества уникальных стран

Сумма количества новых заболеваний по регионам

Статистика по странам

Исследование влияния объёма данных и числа ядер на производительность ClickHouse и PostgreSQL

Подготовка данных

Проведение эксперимента

Выводы по эксперименту