Database Table Caching via Sliding Window Partitions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
High query latency in databases due to rapid generation of time-sensitive records, which leads to performance issues and unacceptably high latency during record archiving and purging processes, especially when systems require low-latency read and write access.
Innovation Solution
A system that divides a database table into logical partitions, where new records are inserted into specific partitions based on time intervals, and older records are automatically deleted after a predetermined period, without affecting other partitions, thereby maintaining low latency and minimizing data storage needs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If records are archived or purged at the end of a lengthy given interval, then data storage requirements are managed, but query latency becomes unacceptably high near the end of the interval
Solution Approach 1:
The patent divides the database table into multiple partitions based on time intervals. Each partition stores records for a specific time period, allowing the system to manage storage by selectively dropping entire partitions rather than individual records. This segmentation enables efficient purging of old data without impacting query performance on recent data, as queries only need to search through current and recent partitions.
2Loss of time
If records are archived or purged more often, then query latency is reduced, but read access must be temporarily locked during updates
Solution Approach 1:
The patent implements periodic partitioning where new partitions are created at regular time intervals and old partitions are dropped periodically. This structured periodic approach allows the system to maintain a predictable cycle of data retention and purging, reducing query latency while minimizing disruption to read access through planned, rather than frequent ad-hoc, updates.
Solution Approach 2:
The system performs preliminary actions by creating new partitions before they are needed and preparing for drops in advance. Partitions are created with predetermined time boundaries, and the system proactively manages their lifecycle, allowing smooth transitions without sudden locking events that would block read access.
3Reliability
If all records are kept in a single table, then data integrity is maintained, but the database size increases and query performance degrades
Solution Approach 1:
The patent segments the single table into multiple time-based partitions while maintaining the logical integrity of the database. Each partition is a separate physical storage unit that can be independently managed, dropped, or archived. This segmentation reduces the size of active data sets for queries while preserving referential integrity through proper foreign key constraints and partition management.
Solution Approach 2:
The patent introduces a time dimension to the database structure by organizing records into time-based partitions. This dimensional organization allows the system to efficiently query recent data by only accessing current partitions, while older partitions can be archived or dropped without affecting the integrity of recent data. Queries automatically or manually target only relevant time ranges.
Data Source
AI summary
A system for low latency caching of time-sensitive data is disclosed. The system comprises a database, one or more processors, and non-transitory memory. The non-transitory memory comprises instructions that cause the one or more processors to divide a table of the database into a plurality of logical partitions; during a first interval of time, insert new records exclusively into a first partition of the plurality of logical partitions; during a second interval of time, insert new records exclusively into a second partition of the plurality of logical partitions; during a subsequent interval of time, determine that a predetermined period of time has passed since conclusion of the first interval of time; and responsive to the determination that the predetermined period of time has passed, delete records from the first partition without changing contents of the second partition or any other partition of the plurality of logical partitions.