Insert-Only Database Search Optimization via Partitioning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Insert-only databases and write-once data storage systems face inefficiencies in search operations due to the inclusion of both valid and invalid entries, particularly when querying non-key fields, leading to inaccurate results and increased scan times, as they cannot efficiently differentiate between outdated and current records.
Innovation Solution
Implement a method that uses a search string to identify both valid and invalid entries in the database, leveraging timestamps to differentiate between them, and utilizing a secondary data storage to store deleted or modified records, allowing for efficient removal of invalid entries and optimizing search results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If insert-only database stores all entries including outdated records, then data integrity and write-once capability are maintained, but search accuracy and scan efficiency deteriorate
Solution Approach 1:
The patent segments the database into multiple tables partitioned by primary key ranges. Each table contains only entries within its specific key range, allowing searches to be performed on smaller, more efficient subsets of data. This segmentation maintains data integrity while improving search accuracy by reducing the number of invalid entries that need to be scanned and filtered out.
2Stability of the object's composition
If insert-only database retains all historical entries, then write-once storage capability is preserved, but search operation time increases
Solution Approach 1:
The patent applies preliminary action by pre-partitioning the database into multiple tables based on primary key ranges before search operations occur. This advance organization allows search operations to quickly identify and query only the relevant table(s) containing the search key, rather than scanning all tables. The partitioning structure is established in advance, enabling efficient search operations while preserving all historical entries.
3Reliability
If database searches all tables for non-key field queries, then comprehensive results are obtained, but query performance deteriorates
Solution Approach 1:
The patent applies local quality by creating local indexes on non-key fields within each partitioned table. These local indexes allow the database system to quickly locate relevant entries within a specific table without scanning the entire table. When a search is performed, the system can use these local indexes to efficiently find matching entries in the appropriate partition, maintaining result completeness while significantly improving query performance.
Data Source
Figure 1~2
AI summary
A search of an insert-only database is performed using a search string in a non-key field to obtain a result set having both valid and invalid entries. Each entry includes a primary key field and the primary key field is subsequently scanned for entries in the result set to determine which entries are valid. Thereafter, invalid entries are removed from the result set to result in a modified result set. Such removal can be based, for example, on the timestamp of the respective entries. In some implementations, a secondary persistence is utilized. Related apparatus, systems, techniques and articles are also described.