COMBINED DATA PARTITIONING AND INDEXING METHOD TO IMPROVE OLTP/OLAP SYSTEM EFFICIENCY

Abstract

The article is devoted to solving one of the key problems of modern information systems - ensuring efficient simultaneous processing of transactional (OLTP) and analytical (OLAP) loads. Such a need is critically important for businesses that require decision-making based on real-time data. An in-depth analysis of the limitations of traditional approaches is conducted. On the one hand, systems that rely exclusively on indexing demonstrate high performance for point OLTP queries, but significantly degrade when executing complex analytical queries due to significant overhead for index maintenance and inefficient data scanning. On the other hand, strategies based only on partitioning, although they speed up OLAP queries by cutting off unnecessary data blocks, are often not flexible enough for fast access to individual rows, which is typical for OLTP operations. To overcome these shortcomings, a combined method has been developed that combines two-level data partitioning with an adaptive indexing strategy. The first level of partitioning is performed according to a time criterion, which allows logically separating “hot” (current) data from “cold” (historical) data. The second level, applied within each time partition, is based on a categorical criterion (e.g., region, product type), which additionally localizes data. The key feature of the method is adaptive indexing: for active, frequently changed partitions, lightweight non-clustered indexes are used, which minimize the overhead of write operations (INSERT, UPDATE, DELETE). At the same time, for historical partitions, which are mainly used for reading, indexes are automatically transformed into column (column-store), which is ideal for OLAP queries, as they provide a high level of compression and minimize the amount of reading from disk. To automate the data life cycle, an algorithm has been developed that dynamically manages the creation of new partitions and changes in index types. The algorithm works on the basis of specified threshold values, such as the volume of data in the partition and the intensity of accesses, automatically transferring partitions from the "active" to "historical" state. The effectiveness of the developed approach was confirmed by experimental analysis on a synthetic data model that simulated the activities of a large retail chain. The results showed that the combined method allowed to achieve several times the acceleration of complex OLAP queries compared to systems that use only indexes or only partitioning, while maintaining high throughput for transaction operations at the level of specialized OLTP systems. Thus, the developed method provides an optimal balance between speed and scalability, eliminating the conflict between the two types of loads, and can be recommended for implementation in modern high-performance hybrid systems, where real-time analytics is a business requirement.