Query-Time Mixing of Compressed and Raw Time-Series Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face challenges in supporting high write rates and efficient query execution for time-series data, particularly in scenarios involving large volumes of complex data from sources like IoT devices, while also managing storage costs effectively.
Innovation Solution
A database system that employs a dual-table approach, where one table stores compressed data in a columnar form to reduce storage needs and enhance query performance, while maintaining the ability to handle high ingest rates and complex queries through a hypertable partitioned across multiple servers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is stored in row-based format to support high write rates, then write performance is improved, but storage efficiency and query performance deteriorate
Solution Approach 1:
The patent segments data storage into two distinct formats: row-based storage for recent data requiring high write rates, and columnar storage for historical data where compression and query efficiency are priorities. This segmentation allows the system to optimize for different operational requirements simultaneously, resolving the contradiction between write performance and storage efficiency.
Solution Approach 2:
The system dynamically transitions data from row-based to columnar storage as it ages, creating a time-based hierarchy where data automatically moves between storage formats. This dynamic approach allows the system to maintain optimal write performance for recent data while achieving high compression ratios for historical data, effectively resolving the storage efficiency contradiction.
2Quantity of substance
If data is compressed to reduce storage costs, then storage capacity is improved, but query execution time worsens
Solution Approach 1:
The system performs preliminary compression of data during the write operation or in background processes, so that when queries are executed, the compressed data is already ready for efficient retrieval. This preliminary action eliminates the need for real-time compression during queries, thus reducing query execution time while maintaining storage efficiency.
Solution Approach 2:
The patent applies different compression strategies to different data characteristics and query patterns. Frequently queried columns or recently accessed data may use lighter compression, while less frequently accessed historical data uses aggressive compression. This local quality approach ensures that compression does not uniformly degrade query performance across all data.
3Quantity of substance
If columnar storage is used to improve compression ratios, then storage efficiency is improved, but write performance deteriorates
Solution Approach 1:
The patent segments the workload by directing high-velocity write operations to row-based storage formats that maintain optimal write performance, while columnar storage is used for data where compression is prioritized. This segmentation prevents columnar storage bottlenecks from impacting overall write rates.
Solution Approach 2:
The system introduces an intermediary layer or buffer that receives writes in row-based format and gradually transforms them to columnar format in the background. This intermediary approach allows writes to proceed at high speed without waiting for the slower columnar transformation process, thus maintaining write performance while achieving compression benefits.
4Adaptability or versatility
If all data is kept in uncompressed row-based format, then query flexibility is improved, but storage costs worsen
Solution Approach 1:
The system dynamically selects between row-based and columnar storage formats based on data age, query patterns, and access frequency. Recently written data remains in flexible row-based format to support diverse query operations, while older data transitions to compressed columnar format. This dynamic format selection maintains query flexibility where needed while reducing storage costs for historical data.
Data Source
AI summary
A database system stores both compressed and uncompressed data in a row-based database system. The database system converts a representation of data involving a set of rows of a source database table, each row comprising multiple values, into a representation involving a single row stored in a target database table, each column of the row comprising arrays of values from the set. The database system may perform type-specific compression of data when storing in the target database table. Accordingly, the database system may apply different compression schemes for different columns or sets of values obtained from the source database table and may group or order selected rows or store additional summary information to improve query performance to the target database table. The database system allows users to query data stored in compressed form. The database system combines compressed and uncompressed data at query time for efficient database analytics.