Comparative analysis of indexing strategies in PostgreSQL under various load scenarios

The subject of the study is the effectiveness of various indexing strategies implemented in PostgreSQL and their impact on the performance of SELECT, UPDATE and INSERT operations in conditions of different data scales. The object of the study are the B-Tree, GIN and BRIN indexes used to optimize the operation of databases. The author examines in detail such aspects of the topic as the time characteristics of operations, the size of indexes and their resource intensity. Special attention is paid to the impact of data volume on the performance of indexes and their suitability for working with various types of data, including JSONB. The research is aimed at systematizing knowledge about the use of indexes to improve the efficiency of highly loaded systems, which require optimization of access operations, updating and inserting data, as well as analysis of resource consumption. The leading research method is an empirical approach, which includes the development of a test database with orders, customers, and products tables. Experiments were conducted for SELECT, UPDATE, and INSERT operations on small, medium, and large amounts of data. The metrics of query execution time and index size obtained using PostgreSQL tools were used for the analysis. The novelty of the research lies in conducting a comprehensive performance analysis of the B-Tree, GIN, and BRIN indexes in PostgreSQL when performing typical operations on various amounts of data. The main conclusions of the conducted research are recommendations on the choice of indexes depending on the types of queries and their execution conditions. B-Tree indexes have proven their versatility, demonstrating high performance for SELECT and UPDATE operations. GIN indexes have shown advantages for working with JSONB data, but their use is limited by high resource intensity. BRIN indexes have proven to be effective for large amounts of data, especially for SELECT operations, due to their compactness and low overhead. A special contribution of the author to the research of the topic is the creation of recommendations for database developers, which makes it possible to improve application performance by choosing an optimal indexing strategy.