OPTIMIZATION OF RELATIONAL DATABASE PERFORMANCE THROUGH THE COMBINED USE OF PARTITIONING AND INDEXING MECHANISMS

Abstract

This paper presents a comprehensive study of methods for improving the performance of relational database management systems through the integration of partitioning and indexing mechanisms. The relevance of the study is driven by the rapid growth of big data volumes, which necessitates finding an optimal balance between architectural complexity and hardware costs. The research methodology is based on a series of experiments conducted in the PostgreSQL 18.1 database management system environment. To ensure objective results, a synthetic dataset consisting of 1 million records was generated, simulating the structure and business logic of a modern e-commerce system. The experimental architecture includes a non-partitioned table (Plain) and three types of partitioned structures: date range partitioning (Range), categorical list partitioning (List), and identifier hash partitioning (Hash). The testing framework covers six key query scenarios, including aggregation queries, complex range-based selections, point lookups, and join operations. The primary evaluation metrics are query execution time (latency) and input/output subsystem resource utilization (I/O cost), measured in units of system buffers. The comparative analysis demonstrates that partitioning without additional indexing provides significant performance improvements (up to 11.9× for the Range strategy) only when the query filtering condition matches the partitioning key, which is achieved through the partition pruning mechanism. At the same time, it was found that for point queries, non-partitioned tables with indexes outperform partitioned counterparts by 40–60%, due to the absence of planner overhead associated with analyzing the partition hierarchy. The highest efficiency was achieved by combining partitioning with composite indexes, resulting in up to a 99.1% reduction in resource consumption for complex multi-factor queries. The impact of data fragmentation was also analyzed, revealing that during large-scale aggregation queries, a non-partitioned table may perform approximately 20% faster due to a reduced number of file descriptor operations. Based on the findings, practical recommendations are formulated for selecting optimization strategies depending on workload characteristics. The study confirms that partitioning and indexing are not interchangeable but complementary technologies, whose maximum potential is realized only through their coordinated application within a unified database architecture.