Time-Series Data Partitioning for Efficient Archival and Purging
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional data processing systems for handling large volumes of time-series data, such as meter data management, require significant system resources and costly storage solutions due to inefficient processing based on read time, leading to high CPU usage and performance issues with data deletion.
Innovation Solution
Implementing a data processing system that organizes time-series data by insert time through data partitioning and utilizes a separate table to track and manage data removal, reducing the need for resource-intensive queries and I/O loads by dropping partitions instead of deleting records.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is organized by read time with row-wise deletion statements, then data can be archived and removed, but CPU usage increases and storage costs increase
Solution Approach 1:
The patent segments the datastore into multiple time-based partitions, allowing data to be organized chronologically by insert time. This segmentation enables efficient archival operations by allowing entire partitions to be moved or dropped as units rather than processing individual rows, thereby reducing CPU overhead while maintaining data archival capability.
Solution Approach 2:
The system performs preliminary organization of data into time-based partitions before archival operations. By pre-grouping data by time intervals and maintaining metadata about partition contents, the system prepares data for efficient bulk archival and purging operations, reducing the computational burden during actual archival execution.
2Quantity of substance
If records are deleted from the datastore after archiving, then storage space is freed, but performance is negatively impacted
Solution Approach 1:
The datastore is divided into discrete time-based partitions that can be independently managed. When data is archived, entire partitions are dropped rather than deleting individual records, which minimizes the performance impact on the running datastore while effectively freeing storage space. This partition-based approach isolates deletion operations from active data access paths.
Solution Approach 2:
The patent extracts archived data from the active datastore into external archival storage systems. By moving archived partitions outside the primary datastore, the system frees up storage space in the main system while maintaining data accessibility through the external archive, thereby improving performance without sacrificing storage capacity.
3Adaptability or versatility
If data is organized by read time, then late arriving reads can be handled, but immense system resources are required
Solution Approach 1:
The system segments data storage into time-based partitions with metadata tracking. This structure allows late-arriving reads to be routed to appropriate historical partitions without requiring the entire datastore to be scanned or processed, thereby handling diverse read patterns with reduced system resources.
Solution Approach 2:
The patent introduces a time-based partitioning dimension to the datastore organization. By adding this temporal dimension and tracking partition metadata, the system can efficiently route reads to specific time partitions, handling late-arriving reads without requiring comprehensive resource allocation across the entire dataset.
4Productivity
If partition drop is supported by the database engine, then data removal is more efficient, but data integrity must be ensured
Solution Approach 1:
Before dropping partitions, the system performs preliminary verification using metadata to confirm that all data in a partition has been successfully archived. This preliminary check ensures data integrity by validating the archival completion status before permanent removal, preventing accidental loss of unarchived data while enabling efficient partition dropping.
Solution Approach 2:
The system uses metadata tables to provide feedback about the archival status of each partition. This feedback mechanism allows the system to make informed decisions about which partitions are safe to drop, ensuring data integrity by only removing partitions whose contents have been verified as archived, while maintaining high removal efficiency through automated status checking.
Data Source
AI summary
There is described a system and method for managing timeseries data. A timeseries consumer receives timeseries data from multiple devices, provides the timeseries data to a particular partition of a data table based on an insert time of the timeseries data to the data table, and records metadata associated with the timeseries data provided to the particular partition at a track table. A timeseries archiver identifies recent records of the data table based on insert time(s) and archival time(s) of a milestone table, archives the timeseries data of the data table associated with the recent records to an external system, and updates the archival time(s) of the milestone table and the track table. The timeseries purger drops an archived partition of the data table based on determining that all records of the archived partition have been archived.