Columnstore индекс

Columnstore индекс — это структура хранения данных в реляционных базах данных, оптимизированная для выполнения аналитических запросов (OLAP) и работы с большими объёмами данных. В отличие от традиционных индексов, основанных на построчном (rowstore) хранении, columnstore индекс организует данные по столбцам, что позволяет значительно ускорять операции агрегации, фильтрации и сканирования больших таблиц за счёт снижения объёма считываемых данных и применения методов сжатия.

История

Технология колоночного хранения данных начала развиваться в 1970-х годах, но широкое практическое применение получила лишь в начале 2000-х. Первые коммерческие реализации columnstore индексов появились в специализированных аналитических СУБД, таких как Sybase IQ (1996) и Vertica (2005). В массовые реляционные СУБД, ориентированные на смешанные нагрузки (HTAP), колоночные индексы стали внедряться в 2010-х годах. В 2012 году компания Microsoft включила поддержку columnstore индексов в SQL Server 2012. В 2013 году аналогичная функциональность появилась в PostgreSQL (через расширение cstore_fdw, а затем — встроенный columnar engine). В 2015 году columnstore индексы были добавлены в MySQL (через движок InnoDB с поддержкой columnar storage). В 2020-х годах технология стала стандартом для всех крупных СУБД, включая Oracle (In-Memory Column Store), SAP HANA и ClickHouse.

Принцип работы

Хранение данных по столбцам

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

Columnstore индекс, напротив, хранит значения каждого столбца отдельно, в непрерывных блоках (сегментах). Например, для таблицы «Продажи» со столбцами «Дата», «Товар», «Количество», «Цена» данные будут организованы в четыре отдельных массива: все даты, все товары, все количества, все цены. При выполнении запроса, требующего только суммы по столбцу «Количество», СУБД считывает лишь массив значений этого столбца, игнорируя остальные.

Сжатие данных

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

• RLE (Run-Length Encoding) — для столбцов с повторяющимися значениями (например, «Город»: Москва, Москва, Москва, Санкт-Петербург).
• Дельта-кодирование — для числовых столбцов с небольшими изменениями (например, «Цена»: 100, 101, 99, 102).
• Словарное копирование — для строковых столбцов с ограниченным набором значений (например, «Пол»: мужской, женский).
• Битовые карты — для столбцов с малым количеством уникальных значений.

Сжатие может уменьшать объём хранимых данных в 5–15 раз по сравнению с построчным хранением, что снижает затраты на дисковое пространство и ускоряет чтение.

Пакетная обработка (Batch Processing)

Columnstore индексы оптимизированы для пакетного чтения. Данные считываются большими блоками (обычно по 1–10 миллионов строк), что позволяет эффективно использовать пропускную способность дисков и кэш-память. Векторные операции (SIMD) современных процессоров дополнительно ускоряют обработку таких блоков.

Типы columnstore индексов

Кластеризованный columnstore индекс (Clustered Columnstore Index)

Физически перестраивает таблицу в колоночный формат. Все строки таблицы хранятся в виде колонок. Такой индекс является основным хранилищем данных для таблицы. Подходит для аналитических нагрузок, где требуется максимальная производительность сканирования. В SQL Server и Azure Synapse Analytics кластеризованный columnstore индекс является основным типом для таблиц фактов.

Некластеризованный columnstore индекс (Nonclustered Columnstore Index)

Создаётся поверх существующей таблицы, которая может храниться в построчном формате (rowstore). Индекс содержит копию выбранных столбцов в колоночном формате. Позволяет ускорить аналитические запросы без изменения основной структуры таблицы. Используется в смешанных нагрузках (HTAP), где требуется быстрая вставка/обновление строк (через rowstore) и быстрая аналитика (через columnstore).

Преимущества и недостатки

Преимущества

• Высокая скорость аналитических запросов — агрегации (SUM, AVG, COUNT), фильтрация и группировка выполняются в 10–100 раз быстрее по сравнению с построчными индексами.
• Эффективное сжатие — снижение объёма хранимых данных в 5–15 раз.
• Оптимизация для сканирования больших таблиц — подходит для таблиц с миллиардами строк.
• Снижение нагрузки на ввод-вывод — чтение только необходимых столбцов.

Недостатки

• Низкая производительность для точечных запросов — поиск одной строки по ключу (например, WHERE id = 12345) выполняется медленнее, чем в построчном индексе (B-tree).
• Сложность обновления данных — вставка, обновление и удаление отдельных строк требуют перестроения сегментов, что может быть затратным. В некоторых реализациях (например, SQL Server) используется механизм дельты (delta store) для буферизации изменений.
• Потребление памяти — для эффективной работы требуется достаточный объём оперативной памяти для кэширования колоночных сегментов.
• Не подходит для OLTP-нагрузок — транзакционные запросы с частыми вставками и обновлениями лучше обрабатываются построчными индексами.

Применение

Columnstore индексы широко используются в системах бизнес-аналитики (BI), хранилищах данных (Data Warehouse), системах поддержки принятия решений (DSS) и в аналитике больших данных. Типичные сценарии:

• Агрегация данных за период — отчёты по продажам, финансовые сводки.
• Анализ временных рядов — мониторинг метрик, логирование событий.
• Машинное обучение — подготовка признаков (feature engineering) на больших таблицах.
• Научные вычисления — обработка результатов экспериментов.

Реализации в популярных СУБД

Microsoft SQL Server

Поддерживает кластеризованные и некластеризованные columnstore индексы начиная с версии 2012. В версии 2016 добавлена поддержка обновляемых кластеризованных columnstore индексов. Используется в Azure SQL Database и Azure Synapse Analytics.

PostgreSQL

Начиная с версии 10, поддерживается расширение columnar (от Citus Data). В версии 14 добавлен встроенный columnar engine для таблиц с использованием USING columnar. Также доступно расширение cstore_fdw.

MySQL

В MySQL 8.0.13 добавлена поддержка columnstore индексов через движок InnoDB. Однако реализация ограничена — columnstore индексы могут быть только некластеризованными и не поддерживают полнотекстовый поиск.

Oracle

Технология Oracle In-Memory Column Store (с 2014 года) позволяет хранить таблицы в колоночном формате в оперативной памяти. Поддерживается как дополнение к традиционному построчному хранению.

ClickHouse

Колоночная СУБД, изначально спроектированная для аналитики. Все таблицы в ClickHouse хранятся в колоночном формате, а columnstore индекс является основным механизмом хранения.

SAP HANA

Гибридная СУБД, поддерживающая как построчное, так и колоночное хранение. Columnstore индексы используются для таблиц фактов в аналитических сценариях.

Интересные факты

• В SQL Server кластеризованный columnstore индекс может содержать до 1024 столбцов.
• Степень сжатия данных в columnstore индексах может достигать 90% для таблиц с повторяющимися значениями.
• В ClickHouse колоночное хранение позволяет выполнять запросы, обрабатывающие миллиарды строк за секунды, на обычном оборудовании.
• Технология columnstore индексов лежит в основе многих облачных аналитических сервисов, таких как Amazon Redshift, Google BigQuery и Snowflake.

Источники

1. Microsoft Docs. «Columnstore indexes — overview». SQL Server documentation.
2. PostgreSQL Documentation. «Columnar storage with cstore_fdw».
3. Oracle Documentation. «Oracle In-Memory Column Store».
4. ClickHouse Documentation. «MergeTree table engine».
5. SAP HANA Administration Guide. «Column Store vs Row Store».
6. Stonebraker, M. et al. «C-Store: A Column-oriented DBMS». VLDB 2005.