Transparent Multi-Temperature Database Access via Row Store Index
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional approaches fail to provide transparent and efficient access to data in databases with varying 'hot' and 'cold' data temperatures, leading to performance penalties and increased costs due to unnecessary data processing and I/O latency.
Innovation Solution
A computer-implemented system that stores a database across partitions on different storage devices with varying access times, using a row store index to direct queries to the correct partition, allowing for efficient aging of data and reducing access time by storing frequently used index rows on a faster storage device.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is stored redundantly at different levels of storage hierarchy, then data availability is improved, but storage costs increase
Solution Approach 1:
The database is segmented into hot data partitions and cold data partitions, with each partition stored at appropriate storage hierarchy levels. Hot data is stored in fast storage while cold data is stored in slower, cheaper storage, eliminating redundant storage while maintaining data availability through proper partitioning and indexing.
Solution Approach 2:
Different storage quality levels are applied to different data partitions based on their access patterns. Hot data partitions use high-performance storage media while cold data partitions use lower-cost storage media, optimizing the balance between data availability and storage costs through localized quality differentiation.
2Measurement precision
If user specifies whether cold data is to be considered, then query precision is improved, but ease of operation deteriorates
Solution Approach 1:
The system automatically determines whether to access hot or cold data partitions based on the query parameters and data temperature metadata, without requiring user specification. The row store index and temperature metadata work together to enable the system to self-determine the appropriate data partition to query, maintaining query precision while improving ease of operation.
3Measurement precision
If application logic derives determination of hot/cold data access, then query precision is improved, but device complexity increases
Solution Approach 1:
A row store index structure serves as an intermediary between application queries and the partitioned database storage. The index contains temperature metadata that automatically guides query routing to appropriate data partitions, eliminating the need for complex application logic while maintaining query precision through structured intermediate data organization.
4Speed
If hot data occupies top levels of storage hierarchy, then access speed is improved, but storage costs increase
Solution Approach 1:
The system dynamically manages data partition locations in the storage hierarchy based on changing data temperature and access patterns. Data partitions can be moved between hot and cold storage levels as their access characteristics change, allowing the system to optimize the balance between access speed and storage costs through dynamic repositioning rather than static allocation.
Data Source
AI summary
A system, a computer-implemented method, and a computer readable medium having stored thereon a computer executable program code for providing access to a database on the system. The database comprises entries stored across partitions. The system comprises a first storage device, a second storage device, and a computing device. The first storage device comprises one partition of the partitions. The second storage device comprises the other partitions except the one of the first storage device. Each of the partitions has a respective partition identification. Each of the entries comprises at least one data value indicative of allocation of the each of the entries in one of the partitions. Each of the entries is stored in one or more data rows of data tables stored in the database. Each of the data rows comprises a respective primary key for identification of that data row. The computing device comprises a memory storing processor-executable program code and a computer processor to execute the processor-executable program code in order to cause the computing device to perform the computer-implemented method.