В этом посте представлен перевод поста Surya Sankar. К сожалению, исходников этого тестирования нет. Попробуйте вместо обычного Postgres использовать Clickhouse. Так же можно попробовать Greenplum.

Этим постом я хотел обратить внимание сообщества на отсутствие бенчмарков Postgres vs Clickhouse. А также хотел бы обратить внимание этим постом для тех людей, которые делают аналитику на PostgreSQL.

Я уже давно ищу хороший способ анализа наших веб-журналов nginx. Если бы денег на эту процедуру было больше, то и вариантов было бы гораздо больше. Я мог бы настроить кластер AWS Redshift или хранилище данных Google BigQuery. Или я мог бы просто согласиться с разработкой плана, предлагаемых различными инструментами APM на рынке.

Но деньги-это действительно проблема в стартапе, который работает с ограниченным бюджетом. И Redshift, и BigQuery закончили бы с неприемлемыми ежемесячными бюджетами. То же самое и с готовыми продуктами предприятия. Поэтому я искал решение с открытым исходным кодом. Стек ELK действительно подходит для этого случая. Несмотря на то, что мне удалось настроить кластер с одним узлом, поддерживать его в рабочем состоянии было непростой задачей. Он безвозвратно потерпит неудачу при малейшем нарушении. Если какой-либо сценарий очистки не работает и диск заполнится, произойдет сбой. Иногда происходили сбои без видимой причины, и мне приходилось тратить несколько часов, чтобы все восстановить. В конце концов я решил, что хлопоты по поддержанию этого метода в рабочем состоянии не стоят того. Облачный план, предложенный компанией Elastic, все еще не входил в наш бюджет.

Наконец я наткнулся на Clickhouse — это СУБД с открытым исходным кодом, которая утверждает, что ее колоночная архитектура, где данные, принадлежащие столбцу, хранятся вместе, специально подходит для рабочих нагрузок OLAP. Требования были впечатляющими, и я решил попробовать. Но, пытаясь это сделать, я также хотел проверить, сможет ли Postgres удовлетворить мои требования. Я обращаюсь к SQLAlchemy всегда, когда хочу писать запросы, и, учитывая, что она очень хорошо интегрирована с Postgres, я действительно хотел рассмотреть возможность того, что, возможно, сам Postgres будет работать. Возможно, моя нагрузка на данные была недостаточно велика, чтобы гарантировать специализированное решение, такое как Clickhouse. Имея в виду эти вопросы, я приступил к настройке Postgres и Clickhouse для обработки моих логов nginx и сравнил оба.

Загрузка данных nginx в базы данных

Мои журналы nginx хранятся в корзинах AWS S3. Поэтому я получил дамп данных, просто синхронизировав содержимое этого сегмента с локальной папкой. Срок хранения журналов составлял примерно 2 месяца. Общий размер папки с gzipped журналами составил 277 Мбайт. Мне пришлось написать несколько скриптов для чтения этих файлов и загрузки данных в базы данных — clickhouse и postgres. У меня есть общий код, используемый для этого в репозиторий nginx в лог-аналитики. Я могу преобразовать его в готовый к использованию пакет pypi в ближайшее время. Я также напишу отдельный подробный пост об используемых методах моделирования и загрузки. Но поскольку этот пост посвящен сравнению баз данных после завершения загрузки, вы можете просто предположить, что загрузка сработала.

Сравнение размеров хранилища данных после загрузки

Как упоминалось ранее, размер архивированных (gzip) журналов доступа nginx составлял 277 МБ.

Размер базы данных clickhouse после загрузки данных определялся путем просмотра размера папки с именем базы данных в файле /var/lib/clickhouse/data. Я использовал БД с именем test. Таким образом, вывод команды du-sh /var/lib/clickhouse/data/test дал 733 МБ в качестве размера базы данных.

Размер базы данных postgres был определен путем просмотра размеров папок внутри нее /var/lib/postgresql/12/main/base. Там были разные папки с именами в виде цифр. Я смог найти тот, который ссылается на таблицу базы данных, используемую для загрузки данных журнала nginx, проверив выходные данные SELECT pg_relation_filepath('weblog_entries'); в клиенте psql. Размер папки оказался колоссальным 7,5 ГБ

Таким образом, Postgres занимал в 10 раз больше места, чем Clickhouse для тех же данных. И это было почти в 30 раз больше, чем исходный gzipped. Учитывая, что это было только для 2-месячных журналов и только для 1 сервера, загрузка журналов на год с 2-х серверов привела бы к 12-кратному увеличению этого размера, то есть 90 ГБ. Это было больше, чем размер файловой системы, которую я использовал. Это само по себе должно было исключить postgres как допустимый вариант.

Сравнение времени выполнения запроса

Я начал с простого сравнения времени, затраченного на подсчет всех записей

Clickhouse: 0.005 seconds

surya-VirtualBox :) SELECT COUNT() FROM test.weblog_entries;

SELECT COUNT()
FROM test.weblog_entries

--COUNT()-¬
¦ 6258734 ¦
L----------

1 rows in set. Elapsed: 0.005 sec. 

Postgres: 62.96 seconds

test=# SELECT COUNT(*) FROM weblog_entries;
  count  
---------
 6566618
(1 row)

Time: 62960.427 ms (01:02.960)

Clickhouse был в 1260 раз быстрее.

Таким образом, мы видим, что Clickhouse явно выигрывает на несколько порядков. Но все же у него есть недостатки по сравнению с Postgres

1. Clickhouse не поддерживает уникальные ограничения. Поэтому я должен написать команду приложения, чтобы убедиться, что одна и та же запись журнала не загружается более одного раза. Я бы хотел, чтобы это можно было оставить в базе данных
2. SQLAlchemy поддерживает Clickhouse по-прежнему в зачаточном состоянии, используя только некоторые сторонние неофициальные библиотеки. В Clickhouse даже близко нет того, что доступно для Postgres

Postgresql поддерживает концепцию под названием Foreign Data Wrappers. Это механизм, который позволяет пользователю взаимодействовать с внешними источниками данных через интерфейс Postgresql. Percona открыла FDW, который они создали для Clickhouse. Это позволит мне использовать интерфейс postgres для связи с clickhouse. Я должен еще проверить, решает ли это 2 проблемы, упомянутые выше — открывать уникальные ограничения с помощью postgresql и поддерживать SQLALchemy с помощью драйверов Postgres. Если бы это оказалось так, я бы брал лучшее из обоих систем.