Хэштеги для быстрого поиска по разделам:

#дайджест

#clickhouse
#data_governance
#databasedesign
#dwh
#etl
#greenplum
#null
#snowflake
#soft_skills
#sql
#документация
#оконные_функции
#проектирование
#системный_анализ

Коротко о Greenplum:

— MPP-СУБД на основе PostgreSQL
— Быстро обрабатывает тяжелые аналитические запросы на больших данных
— Параллельная обработка данных
— Концепция Shared-nothing (каждый узел является независимым, самодостаточным и не существует единой точки отказа)
— Линейная масштабируемость
— ACID
— Отказоустойчивость
— Полиморфное хранение данных
— Open source
— Не для OLTP нагрузки

#greenplum

Хранение данных в таблицах в Greenplum

— heap storage (без кластеризованных индексов) – обеспечивает только строковое хранение данных. Подходит для малого объёма данных (например, для справочников) и частого обновления операциями INSERT, UPDATE и DELETE. Тип хранения по умолчанию.

— append-optimized storage (AOT, оптимизированное для добавления) – обеспечивают строковое и колоночное хранение. Подходит для аналитической обработки больших массивов данных, когда данные загружаются большими пакетами и над ними производятся в основном операции чтения. Колоночное хранение значительно снижает затраты на чтение и запись, а также колоночные таблицы лучше поддаются сжатию. Для больших данных рекомендуется использовать колоночное хранение с сжатием 1-3 уровня.

Для AOT таблиц доступна строковая (row) и колоночная (column) ориентация данных.

Пример создания AOT-таблицы с колоночной ориентацией:

create table [schema_name].<table_name> 
             (<columns_list>)
with (appendoptimized = true,
      orientation = column);

#greenplum

Распределение (distribution) в Greenplum

В GP данные физически хранятся на разных сегментах, поэтому указывать распределение при создании таблицы обязательно.

Правила выбора хорошего ключа дистрибьюции
— Поле не должно иметь null-значений.
— Тип поля – integer.
— Не использовать в качестве ключа поля с типами: date, timestamp, boolean, decimal, большие строки.
— Значения поля должны быть уникальными.
— Поле чаще всего используется для соединения с большими таблицами.
— Максимум 2 поля, но лучше использовать 1.
— Поле не должно использоваться в качестве поля для партиционирования.
— Не нужно использовать поля, которые используются при фильтрации запросов в where, так как нагрузка при выполнении запроса будет распределена неравномерно.

Можно использовать случайное распределение, если не получается подобрать подходящие поля, но необходимо учитывать, что такое распределение хорошо работает только при вставке данных большими пакетами, так как GP распределяет данные по циклическому алгоритму, который запускается заново для каждой операции вставки, начиная с первого сегмента. Мелкие вставки приведут к неравномерному распределению данных по сегментам (перекосу).

#greenplum

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

Опции хранения данных определяются на этапе создания таблиц.

Уровень сжатия данных:
— на уровне таблицы (table-level) — применяется ко всей таблице. Доступно для AOT-таблиц как со строковой (row-oriented), так и с колоночной (column-oriented) ориентацией данных.
— на уровне столбца (column-level) — применяется к отдельному столбцу. Позволяет использовать различные алгоритмы сжатия для разных столбцов одной таблицы. Этот тип сжатия доступен только для AOT-таблиц с колоночной ориентацией данных.

Независимо от уровня, на котором применяется сжатие данных, для его настройки можно использовать следующие параметры:
— compresstype – тип сжатия данных. Возможные значения: ZLIB, ZSTD и RLE_TYPE. По умолчанию используется значение none, при котором сжатие не применяется.
— compresslevel  – уровень сжатия данных. Уровни с наименьшими номерами соответствуют самой быстрой, но при этом наименьшей компрессии данных.

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

Пример создания AOT-таблицы с колоночной ориентацией и zstd-хранением:

create table [schema_name].<table_name>
             (<columns_list>)
with (appendoptimized = true,
      orientation = column,
      compresstype = zstd,
      compresslevel = 3
);

#greenplum

Партиционирование (partitioning) в Greenplum

Партиционирование (или секционирование) помогает повысить производительность запросов за счет разбиения больших таблиц на небольшие части, называемые партициями (partitions). Это позволяет оптимизаторам запросов сканировать ограниченное число строк в таблице (на основе условий) вместо чтения всего содержимого таблицы.

Партицировать маленькие таблицы не имеет смысла!

Партиционирование может быть указано только при создании таблицы, однако удалять/добавлять/изменять партиции в дальнейшем можно. Чтобы сделать добавить партиционирование в таблицу, нужно сделать новую таблицу с партициями и перенести данные из непартиционированной.

Пример создания партиционированной таблицы:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by <partition_spec>;

Важно! Загрузка данных в партиционированные таблицы крайне неэффективна. Поэтому рекомендуется загружать данные в промежуточную (staging) таблицу и затем применять к партиционированной таблице команду EXCHANGE PARTITION.

#greenplum

Виды партиций в Greenplum

— partition by range – осуществляет разделение данных на основе числовых или временных (date/timestamp) диапазонов. Интервалы для партиций указываются используя ключевые слова START и END. Выражения INCLUSIVE и EXCLUSIVE используются в связке с START и END для указания того, должны ли попадать в соответствующий диапазон граничные значения. По умолчанию значения, указанные с помощью START, включаются в диапазон; значения, определенные с помощью END — нет. Партиции можно указывать как автоматически, так и вручную.

Автоматически:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by range(<column name>)
     (partition monthly start (date 'ХХХХ-ХХ-ХХ') inclusive end (date 'ХХХХ-ХХ-ХХ') exclusive every (interval '1 month'));

Вручную:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by range(<column name>)
     (partition Nov23 start(date '2023-11-01') inclusive,
      partition Dec23 start(date '2023-12-01') inclusive end(date '2024-01-01') exclusive);

— partition by list – на основе списков значений.

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by list (pet)
    (partition cats values ('Cat'),
     partition dogs values ('Dog'),
     default partition other);

#greenplum

Хороший тон партиционирования в Greenplum

— Делить на партиции нужно только большие AOT-таблицы.
— Не нужно разбивать таблицы на очень маленькие партиции.
— Ключ партиционирования должен использоваться в запросах в условии where.
— Ключ должен позволять разбить таблицу на примерно одинаковые части.
— Таблицы, созданные с использованием политики распределения данных DISTRIBUTED REPLICATED, не могут быть партиционированы.

#greenplum

Вставка данных в таблицу Greenplum через INSERT INTO

Базовый синтаксис

insert into sandbox.table_name ({column}, {column}, ...) values
            (123456, Ivan, '2023-11-17 19:45:00'),
            (123457, Olga, '2023-11-17 19:46:00');

Важно! тип вставляемых данных должен соответствовать типу столбцов.

insert into sandbox.table_name select * from another_table where column_with_date > '2023-09-07';

Особенности
Для вставки данных в партиционироанную таблицу указывается корневая таблица (созданная командой CREATE TABLE), либо отдельная партиция для вставки. Если данные для указанной дочерней таблицы не соответствуют условию партиционирования, возвращается ошибка.

Для вставки больших объемов данных следует использовать внешние таблицы (external table) или команду COPY. Эти механизмы загрузки более эффективны для вставки большого количества строк.

Не рекомендуется использовать оператор INSERT INTO для вставки одной строки в append-optimized таблицу (AOT). Greenplum Database поддерживает не более 127 одновременных транзакций INSERT в одну AOT.

#greenplum

Вакуум в Greenplum: путь к производительности

Эффективное хранилище данных — это не только оптимально подобранное оборудование (или облачные сервисы) и ПО. Для поддержания целостности данных, оптимизации производительности и обеспечения эффективного использования ресурсов также важно регулярно проводить различные мероприятия по поддержанию "здоровья" DWH.

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

vacuum sandbox.table_name;

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

Рекомендации по использованию VACUUM от VMvare:
— запускать VACUUM после крупных операций UPDATE и DELETE.
— избегать использования VACUUM FULL: вместо этого предпочтительнее использовать операцию CREATE TABLE...AS, а затем переименовать и удалить оригинальную таблицу.
— чаще выполнять VACUUM для системных каталогов, чтобы избежать их разрастания и необходимости запуска VACUUM FULL.
— никогда не прерывать выполнение VACUUM FULL для системного каталога и не используйте команду kill для этого процесса.

Для поддержания производительности чтения метаданных необходимо периодически запускать VACUUM FULL для таблиц системного каталога. Однако, стоит учитывать несколько важных моментов:
— Чистка системного каталога эквивалентна остановке сегмента кластера.
— Нельзя прерывать уже запущенный VACUUM FULL системного каталога, чтобы избежать поломки сервиса Greenplum на сегменте.
— Существует жёсткий лимит на объём "мёртвых" строк для VACUUM – не более 1 ГБ указателей строк на процесс.

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

Хотите почитать про вакуум ещё? На хабре есть неплохая статья на этот счёт: Особенности VACUUM в MPP-форках PostgreSQL.

#greenplum