NoSQL Secondary Index Elimination via Time-Series Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The performance of storage systems using non-relational databases (NOSQL) is degraded due to the high workload of creating and maintaining secondary indexes for multiple columns, especially when dealing with large datasets, leading to speed issues in data storage and query processes.
Innovation Solution
The method involves classifying data into time-independent and time-related contents, storing time-independent contents in a first information table and time-related data in a second information table, where the second table does not require secondary indexes, and preprocessing data to store a storage identity, target time, and target numerical value, reducing the need for index creation and maintenance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If secondary indexes are created for multiple columns in a table to enable flexible querying, then query capability is improved, but the workload of index creation and maintenance increases severely
Solution Approach 1:
The patent segments the data storage structure into multiple tables (first information table, second information table, third information table) with specific roles. The first table stores time-independent data with multi-dimensional identification fields, the second table stores time-series data without requiring secondary indexes, and the third table stores aggregated results. This segmentation eliminates the need for secondary indexes on the main data storage tables while maintaining flexible query capabilities through the specialized structure of each table.
Solution Approach 2:
The patent introduces a time dimension as a primary organizing principle, creating a time-series database structure where time is explicitly modeled. By organizing data along the time dimension and using multi-dimensional identification fields for other dimensions, the system enables flexible querying without requiring traditional secondary indexes on each column, thus resolving the contradiction between query versatility and maintenance workload.
2Adaptability or versatility
If secondary indexes are maintained for large datasets to support multiple query conditions, then query flexibility is improved, but data storage performance degrades
Solution Approach 1:
The patent divides the storage system into specialized tables: the first information table for time-independent data with multi-dimensional keys, the second information table for time-series data organized by time stamps, and the third information table for pre-computed aggregated results. This segmentation eliminates the need for secondary indexes on large datasets while maintaining query flexibility through the specialized structure of each table, thereby improving data storage performance.
Solution Approach 2:
The system performs preliminary aggregation and preprocessing of data during the storage phase, computing aggregated results and storing them in advance in the third information table. This preliminary action eliminates the need for expensive index operations during query time, improving both storage performance and query efficiency for aggregate operations.
3Adaptability or versatility
If traditional RDBMS table structure with multiple columns is used in NOSQL, then SQL-like querying is enabled, but the performance degradation from index maintenance occurs
Solution Approach 1:
The patent implements a segmented storage architecture where the first information table handles time-independent queries using multi-dimensional identification fields, the second information table handles time-series queries without indexes, and the third information table provides aggregated results. This segmentation enables SQL-like querying capabilities across multiple dimensions while avoiding the performance degradation associated with traditional RDBMS secondary index maintenance.
Solution Approach 2:
The patent changes the fundamental parameter of how data is organized from traditional column-based RDBMS structure to a time-series structure with multi-dimensional identification fields. By changing the organizational parameter from columns to time-series sequences with dimensional keys, the system enables flexible querying without requiring secondary indexes, thus maintaining both SQL-like capability and high performance.
Data Source
AI summary
The present application discloses methods for storing data, methods for querying data, and apparatus thereof. A method for storing data may include receiving a data record to be stored. When the data record includes a timestamp, a numerical value generated at a time point identified by the timestamp, and a first combination of identification-field values that can identify the numerical value, the method may also include obtaining a target numerical value and a target time satisfying user query needs, and a second combination of identification-field values as a query condition by the preprocessing of the data record based on the user query needs. The method may further include storing a storage identity representing the second combination of identification-field values, the target time, and the target numerical value into a second information table correspondingly.