Ваша база данных работает в 40 раз медленнее, чем могла бы

Ваши аналитические запросы, вероятно, выполняются значительно медленнее, чем могли бы. Рассмотрим агрегирующий запрос по 100 миллионам строк: в Postgres эта операция занимает ошеломляющие 9,7 секунды. DuckDB, column-store база данных, выполняет тот же запрос всего за 0,24 секунды, демонстрируя более чем 40-кратное увеличение скорости. Эта разительная разница выявляет фундаментальное архитектурное «узкое место», присущее многим традиционным базам данных.

Традиционные реляционные базы данных, такие как Postgres, являются row-stores. Они организуют данные на диске построчно, что означает, что из хранилища извлекается вся строка целиком, даже если для запроса требуется значение только одного столбца из этой строки. Для аналитических нагрузок, которые агрегируют данные по многим строкам, но по немногим столбцам, такая архитектура заставляет базу данных считывать огромное количество ненужных данных, расходуя ценную пропускную способность I/O и циклы CPU.

Column-stores, такие как DuckDB и ClickHouse, меняют эту парадигму. Они хранят данные, сгруппированные по столбцам. Когда агрегирующий запрос нацелен на конкретный столбец, база данных считывает с диска только данные этого конкретного столбца, полностью пропуская все остальные столбцы в таблице. Это радикально сокращает объем обрабатываемых данных, что и приводит к наблюдаемому приросту производительности.

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

ClickHouse, например, не индексирует отдельные строки; он полагается на sparse primary key (разреженный первичный ключ), определенный по отсортированному столбцу, например, временной метке. По мере поступления данные сортируются и разбиваются на блоки фиксированного размера примерно по 8 192 строки, известные как granules. ClickHouse хранит только первую временную метку каждого гранулы, создавая около 12 000 небольших заметок в оперативной памяти для набора данных из 100 миллионов строк.

Когда запрос запрашивает данные за определенный период, например, за март, ClickHouse быстро сканирует эти заметки в памяти, идентифицируя и считывая с диска только релевантные гранулы. В нашем тесте это означало чтение всего 1 633 блоков из 12 208, игнорируя более 86% набора данных. DuckDB использует похожий, высокоэффективный трюк: он хранит min/max metadata для каждого чанка столбца. Это позволяет DuckDB доказать, что чанк не имеет отношения к запросу — например, чанк, охватывающий период с января по февраль — и полностью пропустить его содержимое, не считывая его.

Колоночные базы данных превосходны в агрегирующих запросах, но их архитектура вносит значительные компромиссы для других распространенных операций. Точечный запрос (point lookup), извлекающий одну строку по ID, выявляет этот резкий контраст. В Postgres это занимает всего 2 миллисекунды благодаря binary tree index, который эффективно переходит к точной странице данных, содержащей запрашиваемую строку.

ClickHouse, однако, испытывает трудности с такими запросами, показывая результат в 168 миллисекунд. Его разреженный первичный ключ, отсортированный по временной метке, а затем по ID, не предлагает прямого пути к произвольному ID без сканирования всех 12 208 блоков данных. Даже после нахождения строки он должен восстановить полную запись, открывая и объединяя все восемь файлов столбцов, что является значительными накладными расходами для, казалось бы, простой операции получения данных.

Обновление одной строки еще больше обнажает архитектурные различия. В Postgres обновление завершается за 5 миллисекунд, перезаписывая одну строку и обновляя одну запись индекса. ClickHouse, напротив, затрачивает ошеломляющие 5,8 секунды на ту же операцию. Это связано с его неизменяемыми файлами данных (immutable data files); изменение одного значения требует перезаписи всего файла столбца revenue для затронутого фрагмента данных.

Эти характеристики производительности — не недостатки проектирования, а присущие им специализации. Строково-ориентированные базы данных, такие как Postgres, оптимизированы для транзакционных рабочих нагрузок (OLTP), где частые вставки, обновления и выборки отдельных строк имеют первостепенное значение. Колоночные базы данных по своей сути отдают приоритет аналитическим рабочим нагрузкам (OLAP), где преобладают агрегирующие запросы к огромным наборам данных, что делает их сильные и слабые стороны различными в зависимости от варианта использования.

Существуют два различных колоночных варианта для ускорения аналитических запросов: ClickHouse и DuckDB. Хотя оба обеспечивают впечатляющий прирост производительности по сравнению с Postgres при выполнении агрегатных операций, их архитектурные философии существенно различаются, что определяет разные области идеального применения.

ClickHouse работает как полнофункциональная серверная система, специально созданная для производственных хранилищ данных. Она функционирует подобно Postgres, требуя работающего экземпляра сервера и поддерживая одновременный многопользовательский доступ для надежных сред совместной аналитики.

DuckDB, напротив, предоставляет возможности встраиваемой библиотеки (embedded library), подобно SQLite для аналитических нагрузок. Она работает внутри процесса вашего приложения, сохраняя всю базу данных в одном файле на диске, что делает ее идеальной для локальной обработки данных на стороне клиента.

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

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

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

В чем основное различие между колоночной и строковой базами данных?

Строковая база данных (например, Postgres) хранит все данные одной записи вместе. Колоночная база данных (например, ClickHouse) хранит все значения одного столбца вместе, что гораздо эффективнее для аналитических запросов.

Когда следует использовать колоночную базу данных?

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

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

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

Является ли DuckDB заменой Postgres?

Нет, они служат разным основным целям. DuckDB — это встраиваемая аналитическая база данных (как SQLite для аналитики), идеальная для внутрипроцессного анализа данных. Postgres — это транзакционная база данных общего назначения (OLTP), предназначенная для использования в качестве системы учета.