Управление таблицами в Greenplum: хранилище данных, ориентированное на строки vs столбцы
Колоночные базы данных становятся все более и более популярными в области OLAP. Традиционные же базы данных, ориентированные на строки, понемногу отходят на второй план.
Многие базы данных являются исключительно столбцовыми. Greenplum не подвержен влиянию изменчивых модных тенденций и предоставляет своим пользователям возможность самостоятельно выбирать модели хранилища: строка, столбец или их комбинация. В этой статье мы поговорим о столбцовых и строковых БД, а также о том, как сделать выбор в пользу той модели хранения данных, которая подходит именно Вам.
Колоночная база данных — это такой тип БД, в котором данные группируются (хранятся и извлекаются) не по строкам, а по столбцам.
Как выбрать тип хранилища данных в Greenplum?
Для большинства рабочих нагрузок общего назначения хранилище, ориентированное на строки, предлагает отличное сочетание гибкости и производительности. Однако модель хранения, ориентированная на столбцы, обеспечивает более эффективное выполнение операций I/O и более надежное хранение данных.
При выборе типа ориентации хранения для таблицы в Greenplum руководствуйтесь следующими соображениями:
Строковое хранение данных
- Обновление данных: если Вам необходимо часто обновлять таблицу, выберите строковое хранение;
- LOAD/частые операции INSERT: выберите модель хранения, ориентированную на строки, в том случае, если Вам важна эффективная работ LOAD или если Вам необходимо часто выполнять операцию добавления новых строк.
Столбцовое хранение данных
- Сжатие данных: столбцовое хранение данных наиболее эффективно для сжатия данных (так как в столбцах зачастую хранятся повторяющиеся данные;
- Количество столбов в запросе: БД, ориентированные на столбцы, лучше всего подходят для запросов, в которых агрегируется множество значений одного столбца, а WHERE или HAVING также относится к агрегированному столбцу.
Например:
SELECT SUM(salary)…
или
SELECT AVG(salary)… WHERE salary > 10000
или когда предикат WHERE относится к одному столбцу и возвращает относительно небольшое количество строк. Например:
SELECT salary, dept … WHERE state=’CA’
- Количество столбцов в таблице: таблицы, ориентированные на столбцы, могут обеспечить лучшую производительность запросов для таблиц с большим количеством столбцов, особенно в том случае, если Вы обращаетесь к небольшому подмножеству столбцов. Хранилище, ориентированное на строки, более эффективно, когда требуется обработать сразу много столбцов или когда размер строки в таблице относительно невелик.
Пример из реальной жизни:
На 4-узловой системе Greenplum (v6.6.0) на AWS мы протестировали две модели хранения данных:
- Таблица, ориентированная на строки (без сжатия) (amzn-reviews-ro),
- Таблица, ориентированная на столбцы (сжатие ZLib-Level 3) (amzn-reviews-co).
Модели хранения данных сравнивались по эффективности выполнения 6 задач:
Массовая загрузка данных (BULK LOADING) - ~151M строк
- Загрузка amzn-reviews-ro заняла примерно 544,98 секунды (или примерно 9 минут и 5 секунд),
- Загрузка amzn-reviews-co заняла 552,29 секунды (примерно 9 минут и 12 секунд).
Загрузка данных из другой таблицы - ~151M строк
- Загрузка amzn-reviews-ro заняла ~216 секунд (или 3 минуты и 36 секунд), а
- Загрузка amzn-reviews-co заняла ~98 секунд (или 1 минуту и 38 секунд).
Размер таблицы и использование дискового пространства
- amzn-reviews-ro - ~80 ГБ (без сжатия),
- amzn-reviews-co - ~74 ГБ/32 ГБ (до/после сжатия), или ~56% сжатия в целом.
Компактный SELECT — 1x “Короткое” поле данных
SELECT product_id
FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;
- для amzn-reviews-ro выполнено за 5845,564 мс, а
- для amzn-reviews-co выполнено за 3350.621 мс.
Компактный SELECT — 1x “Длинное” поле данных
SELECT review_body
FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;- для amzn-reviews-ro выполнено за 9244.800 мс, а
- для amzn-reviews-co выполнено за 23396.014 мс.
Компактный SELECT — Мало столбцов
SELECT product_id,
marketplace, product_category, star_rating FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;- для amzn-reviews-ro выполнено за 5717.630 мс, а
- для amzn-reviews-co выполнено за 4548.015 мс
Расширенный SELECT — Преобладающее большинство/Множество столбцов
SELECT marketplace
, customer_id , product_id , product_title , product_parent , product_category , star_rating , helpful_votes , total_votes , vine , verified_purchase , review_headline, review_body FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;
- для amzn-reviews-ro выполнено за 10359.897 мс, а
- для amzn-reviews-co выполнено за 29237.993 мс.
Агрегатная/оконная функция по ограниченному количеству столбцов
SELECT COUNT(*) , product_category , star_rating FROM demo.amzn_reviews_heap GROUP BY 2, 3, 4;
- для amzn-reviews-ro выполнено за 3989.040 мс, а
- для amzn-reviews-co - за 1835.179 мс
Агрегатная/оконная функция по большому количеству столбцов
SELECT COUNT(*) , ROUND(AVG(helpful_votes)) AS helpful_votes_avg , ROUND(AVG(total_votes)) AS total_votes_avg , marketplace , product_category , star_rating , verified_purchase , CASE WHEN LENGTH(review_headline)<= 20 THEN 'Short Headline' ELSE 'Long Headline' END AS review_headline_length_type , CASE WHEN LENGTH(review_body)<= 400 THEN 'Short Body' ELSE 'Long Body' END AS review_body_length_type FROM demo.amzn_reviews_ro GROUP BY 4, 5, 6, 7, 8, 9;
- для amzn-reviews-ro выполнено за 2780.567 мс, а
- для amzn-reviews-co - за 1890.663 мс.
Заключение
Если Вам нужен не просто универсальный инструмент, тогда обратите свое внимание на Greenplum – СУБД, отличающуюся гибридным подходом в области хранения. Greenplum предусматривает сразу несколько моделей хранения таблиц, оптимизированных для разных сценариев использования.
Модель хранения, ориентированная на столбцы, обеспечивает наиболее эффективное выполнение операций ввод-вывод и хороша для компактных запросов SELECT (однако в случае с расширенными запросами эффективность падает). БД, ориентированные на строки, обычно называют «старомодными», но это не совсем оправданно, ведь эта модель по-прежнему актуальна для рабочих нагрузок общего назначения или смешанных нагрузок и предлагает отличное сочетание гибкости и производительности.
