С.Г. Ермаков, Н. Сабиров, М.С. Злобина, А.Ю. Ширяев

ВЛИЯНИЕ СТРАТЕГИЙ РАСПРЕДЕЛЕНИЯ И ПАРТИЦИОНИРОВАНИЯ ДАННЫХ НА ПРОИЗВОДИТЕЛЬНОСТЬ АНАЛИТИЧЕСКИХ ЗАПРОСОВ В GREENPLUM

Аннотация: Использование хэш-распределения по неоптимальному ключу увеличивает время выполнения запросов с операциями соединения таблиц раза по сравнению с оптимальным хэш-распределением. Диапазонное партиционирование по атрибуту даты обеспечивает ускорение выполнения соответствующих запросов. Комбинированная стратегия (диапазонное + хэш) даёт дополнительный прирост к одному диапазонному партиционированию.

Ключевые слова: greenplum, аналитическая субд, postgresql, производительность, параллельная обработка, архитектура mpp, распределение данных, маштабируемость, архитектура баз данных, большие данные.

 G. Ermakov, N. Sabirov, M. S. Zlobina, A. Y. Shiryaev

THE IMPACT OF DATA DISTRIBUTION AND PARTITIONING STRATEGIES ON THE PERFORMANCE OF ANALYTICAL QUERIES IN GREENPLUM

Summary: The goal. An experimental study of the quantitative impact of data distribution strategies (hash distribution by optimal and suboptimal key, random distribution, replicated allocation) and partitioning strategies (range, list, combined) on the performance of analytical queries in the Greenplum database management system. Materials and methods. The Greenplum Database 6.21 cluster in the configuration of 1 main node + 8 segment nodes was used as the object of research. Testing was conducted on the standard TPC-H (Transaction Processing Performance Council Decision Support Benchmark H) benchmark with a scale factor of 100 GB of data. From the full set of 22 TPC-H queries, 6 representative queries were selected, covering the main patterns of analytical workloads. Each query was run five times, and the final value was the arithmetic mean of the three average observations after the extreme values were discarded. Results. Using a hash distribution based on a non-optimal key increases the execution time of queries with join operations by a factor of three compared to the optimal hash distribution. The range partitioning by date attribute provides faster execution of the corresponding queries. The combined strategy (range + hash) gives an additional boost to a single range partitioning. Conclusions. The choice of the hash distribution key is a defining parameter of the physical design for queries with table join operations. Range partitioning by time attribute is effective only if the filtering predicates are highly selective. Skewing data causes non-linear degradation of performance and requires mandatory monitoring.

Keywords: greenplum, analytical database, postgresql, performance, parallel processing, mpp architecture, data distribution, scalability, database architecture, big data.

DOI: 10.34219/2078-8320-2026-16-2-111-119