В этой статье продолжим говорить про лучшие практики работы с Greenplum и рассмотрим тонкости проектирования схем данных в этой MPP-СУБД, которая часто применяется для хранения и аналитики больших данных. Почему надо задавать одинаковые типы данных для столбцов, используемых в SQL-запросах c оператором JOIN, чем хранилище кучи отличается от Append Only, а ориентация на столбцы – традиционной построчной таблицы. Также разберем, почему в общем случае Greenplum не нужны индексы, и как с ними работать в некоторых ситуациях.
Типы данных
Greenplum активно используется в качестве OLAP-базы и корпоративного хранилища данных. Поэтому она лучше всего работает с денормализованной схемой звезды или снежинки, с большими централизованными таблицами фактов, связанными с несколькими меньшими таблицами измерений. Рекомендуется использовать те же типы данных для столбцов, которые используются в соединениях между таблицами. В случае разных типов данных Greenplum должна динамически выполнять преобразования типов, чтобы правильно сравнивать значения данных. Это снижает скорость выполнения SQL-запросов. Возможно, для облегчения соединения между таблицами придется изменить тип данных, увеличив размер. При этом рекомендуется выбирать такие типы данных, которые занимают меньше места. Лучше увеличить емкость базы данных и ускорить выполнение запросов, выбрав наиболее эффективные типы для хранения данных.
В частности, рекомендуется использовать TEXT или VARCHAR вместо CHAR. Хотя между символьными типами данных нет разницы в производительности, использование TEXT или VARCHAR может снизить пространство для хранения. Аналогично следует использовать наименьший числовой тип данных, к примеру, BIGINT для данных, которые помещаются в INT или SMALLINT, расходует место впустую.
Для оптимальной производительности рекомендуется располагать столбцы таблицы в следующем порядке, чтобы добиться выравнивания байтов типа данных:
- столбцы распределения и разделения данных (distribution key и partition key);
- фиксированные числовые типы;
- переменные типы данных.
Их следует располагать от большего к меньшему, чтобы BIGINT и TIMESTAMP стояли перед INT и DATE, и все эти типы стояли перед TEXT, VARCHAR или NUMERIC. Например, сперва идут 8-байтовые типы (BIGINT, TIMESTAMP), затем 4-байтовые типы (INT, DATE), далее 2-байтовые типы (SMALLINT), и последними переменные типы данных (VARCHAR).
К примеру вместо последовательности:
Int, Bigint, Timestamp, Bigint, Timestamp, Int (distribution key), Date (partition key), Bigint, Smallint
следует определить столбцы так:
Int (distribution key), Date (partition key), Bigint, Bigint, Timestamp, Bigint, Timestamp, Int, Smallint.
Категории и ориентации таблиц
Greenplum предоставляет множество вариантов хранения при создании таблиц. Важно знать, когда следует использовать хранилище кучи, а когда — хранилище, оптимизированное для добавления (AO, Append Only), а также когда выбирать хранилище, ориентированное на строки, а когда — ориентированное на столбцы. Правильный выбор кучи по сравнению с AO и строки по сравнению со столбцом особенно важен для больших таблиц фактов и имеет значение для таблиц с небольшими измерениями.
Изначально следует проектировать модель данных Append Only, усекая ежедневный раздел перед загрузкой. Для больших партиционированных таблиц фактов рекомендуется выбирать оптимальные варианты хранения для разных разделов, причем некоторые из них могут быть ориентированы на строки, а другие — на столбцы.
При использовании хранилища, ориентированного на столбцы, каждый столбец представляет собой отдельный файл в каждом сегменте Greenplum. Для таблиц с большим количеством столбцов рекомендуется столбцовое хранилище для часто используемых данных (горячее) и построчное хранилище для редко используемых данных (холодное). Параметры хранилища должны быть установлены на уровне раздела. Сжатие больших таблиц повысит производительность ввода-вывода и освободит место в кластере.
Хранилище кучи — это модель по умолчанию, которую PostgreSQL использует для всех таблиц базы данных. В Greenplum это рекомендуется для таблиц и разделов, над которыми будут выполняться повторяющиеся и/или параллельные операции UPDATE, DELETE и одиночные INSERT.
AO-хранилище оптимизировано для добавления, а потому подходит для таблиц и разделов, которые редко обновляются после первоначальной загрузки, а последующие вставки выполняются только в пакетных операциях. В этом случае надо избегать выполнения одноэлементных операций INSERT, UPDATE или DELETE в AO-таблицах. Допускаются параллельные пакетные операции INSERT, но не UPDATE и DELETE.
AO-модель не подходит для часто обновляемых таблиц, поскольку пространство, занимаемое строками, которые обновляются и удаляются в AO-таблицах, не восстанавливается и повторно не используется так же эффективно, как в таблицах кучи. Хранилище, оптимизированное для добавления, предназначено для больших таблиц, которые загружаются один раз, редко обновляются и часто запрашиваются для аналитической обработки запросов.
В большинстве баз данных кортежи хранятся построчно. Столбцы, составляющие строку, хранятся на диске непрерывно, так что вся строка может быть прочитана с диска за один ввод-вывод. Колоночная ориентация сохраняет на диске значения столбцов вместе, создавая для каждого столбца отдельный файл. Если таблица партиционирована, для каждого столбца и раздела создается отдельный файл. Когда запрос обращается только к небольшому количеству столбцов в колоночной таблице с большим количеством столбцов, стоимость ввода-вывода существенно снижается по сравнению с таблицей, ориентированной на строки, поскольку столбцы, на которые нет ссылок, не нужно извлекать с диска.
Хранилище, ориентированное на строки, рекомендуется для рабочих OLTP-нагрузок транзакционного типа с итеративными транзакциями, когда требуются обновления и выполняются частые вставки. Строковое хранилище подходит, когда выборки по таблице широки и в запросе требуется много столбцов одной строки. Например, если в SQL-запросе выбрано много столбцов в списке SELECT или условии WHERE, хранилище, ориентированное на строки, быстро справится с этим запросом. Также построчное хранилище подойдет для общих или смешанных рабочих нагрузок, поскольку оно обеспечивает наилучшее сочетание гибкости и производительности.
Хранилище, ориентированное на столбцы, оптимизировано для операций чтения, что чаще всего встречается в аналитических рабочих нагрузках (OLAP). Для операций записи оно не оптимизировано, т.к. значения столбца записываются в разные места на диске. Таблицы, ориентированные на столбцы, дают оптимальную производительность запросов к большим таблицам с множеством столбцов, лишь малая часто которых запрашивается в SQL-запросе.
Еще одно преимущество ориентации по столбцам заключается в том, что набор значений одного типа данных занимает меньше места, чем набор значений смешанного типа. Поэтому таблицы, ориентированные на столбцы, занимают меньше места на диске и требуют меньше операций ввода-вывода, чем строковые таблицы. Таблицы, ориентированные на столбцы, также лучше сжимаются, чем таблицы, ориентированные на строки.
Таким образом, хранилище, ориентированное на столбцы, отлично подходит для аналитических рабочих нагрузок DWH, где выборка ограничена или агрегирование данных вычисляется по небольшому количеству столбцов. Также колоночная ориентация рекомендуется для таблиц с отдельными столбцами, которые регулярно обновляются без изменения других столбцов в строке. Чтение полной строки в широкой колоночной таблице требует больше времени, чем чтение той же строки из таблицы, ориентированной на строки. Все это обусловлено тем, что каждый столбец представляет собой отдельный физический файл в сегменте базы данных Greenplum.
Индексы в Greenplum
Считается, что индексы не нужны в Greenplum, поскольку большинство аналитических запросов работают с большими объемами данных, а индексы предназначены для поиска отдельных строк или небольшого их количества. В Greenplum последовательное сканирование является эффективным методом чтения данных, если каждый сегмент содержит равную часть данных, и все сегменты работают параллельно при чтении. Поэтому если добавление индекса не дает прироста производительности, его следует удалить и убедиться, что каждый созданный индекс используется оптимизатором.
Однако, для запросов с высокой избирательностью индексы могут повысить их производительность. Можно создать индекс для одного столбца колоночной таблицы, чтобы повысить детализацию столбцов с высокой кардинальностью, которые требуются для высокоселективных запросов.
Не рекомендуется индексировать часто обновляемые столбцы, т.к. индекс увеличивает количество операций записи, необходимых для обновлений. Индексы для выражения следует использовать, только если оно часто используется в запросах. Индекс с предикатом создает частичный индекс, который можно использовать для выбора небольшого количества строк из больших таблиц.
Следует избегать перекрывающихся индексов: индексы с одинаковым ведущим столбцом являются избыточными. Индексы могут повысить производительность сжатых AO-таблиц, к которым обращаются SQL-запросы, возвращающих целевой набор строк. Для сжатых данных метод индексного доступа означает, что несжатыми являются только необходимые страницы.
Рекомендуется создавать выборочные индексы B-дерева, селективность которых близка к 1. Напомним, селективность индекса — это отношение количества уникальных значений в столбце к количеству строк в таблице. Например, если в таблице 1000 строк, а в столбце 800 различных значений, селективность индекса равна 0,8, что считается хорошим показателем.
Чтобы ускорить загрузку данных в таблицу, индексы следует удалить и после загрузки заново создать их, если необходимо. Bitmap-индексы подходят для запросов, а не для обновления и работают лучше всего, когда столбец имеет небольшую мощность — от 100 до 100 000 различных значений. Не следует применять битовые индексы для уникальных столбцов, данных с очень высокой или очень низкой кардинальностью, а также для транзакционных рабочих нагрузок.
Если для партиционированных таблиц Greenplum нужны индексы, индексируемые столбцы должны отличаться от партиционированных. Преимущество индексации многораздельных таблиц обусловлено тем, что производительность сбалансированного дерева падает экспоненциально по мере роста размера сбалансированного дерева. Поэтому создание индексов для многораздельных таблиц создает более мелкие сбалансированные деревья, которые работают лучше, чем неразделенные таблицы. О том, что изменилось в индексации таблиц в 7-ой версии Greenplum, выпущенной в декабре 2022 года, читайте в нашей новой статье.
Узнайте больше про администрирование и эксплуатацию Greenplum и Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
