RESEARCH ON THE IMPACT OF INDEXING AND PARTITIONING OF THE DATABASE ON THE PERFORMANCE OF OPERATIONS IN THE WAREHOUSE MANAGEMENT SYSTEM

Abstract

This paper addresses the problem of optimizing transaction performance in relational databases used in warehouse management systems. In response to the growing demands for scalability and efficient data processing in high-load environments, the research aims to evaluate the impact of structural configurations—particularly indexing and partitioning—on the execution speed of key business operations. PostgreSQL was selected as the experimental platform due to its advanced optimization capabilities and wide industrial adoption.

The study employs a relational data model comprising eight interrelated tables representing a typical warehouse system, including products, categories, suppliers, stock balances, and disposal records. Three architectural configurations of the main operational table (warehouse stock) were developed, including default indexing and combinations of partitioning and composite indexing on warehouse and product identifiers. Each configuration models a distinct approach to database optimization under intensive transactional load.

To assess performance objectively, the pgbench benchmarking tool was used with multithreaded workloads and fixed-duration tests. Four core operations—stock availability check, product transfer between warehouses, goods intake, and inventory disposal—were implemented as SQL procedures simulating real-world scenarios. Each test scenario yielded performance metrics in terms of transactions per second, enabling comparative evaluation of all configurations.

The proposed methodology allows for a systematic assessment of how architectural decisions influence relational database performance in transaction-heavy systems. The experiment provides a foundation for further research involving larger datasets, alternative partitioning schemes, or comparative evaluation with other DBMS platforms and hardware setups. The work holds practical significance for professionals involved in database architecture, performance optimization, and the development of scalable warehouse management solutions.