Time-Series Tables for Balance Tracking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
High transaction volume and increasing data sizes in database environments lead to reduced transaction throughput, and the risk of inaccurate timestamp-based updates in time-series tables, which can jeopardize the accuracy of accumulating values like account balances.
Innovation Solution
Implementing a balance tracker application that performs idempotency checks and asynchronously archives value updates into time-series tables based on timestamps, using a separate archival data store for transaction history to maintain high transaction throughput and accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a single large table is used to store all transaction data, then data completeness is maintained, but transaction throughput decreases as table size increases
Solution Approach 1:
The patent divides a single large transaction table into multiple smaller partitioned tables based on time ranges (e.g., daily, weekly, monthly partitions). Each partition stores transactions for a specific time period, allowing the system to maintain high transaction throughput by operating on smaller tables while still storing the complete transaction history across all partitions.
2Productivity
If table partitioning is implemented to maintain throughput, then transaction throughput is improved, but risk of inaccurate timestamp-based updates increases
Solution Approach 1:
The patent implements a checksum column that automatically calculates and stores a verification value (e.g., sum of transaction amounts) for each partition. When transactions are updated or retrieved, the system verifies the checksum to ensure data integrity. This feedback mechanism detects and prevents inaccurate updates caused by timestamp issues or concurrent modifications, maintaining reliability across partitioned tables.
3Loss of information
If all transaction history is stored in the main table, then historical data availability is maintained, but system resources are significantly strained during searches
Solution Approach 1:
The patent segments historical transaction data into time-based partitions that can be independently managed and queried. When retrieving historical data, the system only searches the relevant time-range partitions rather than scanning the entire transaction table. This segmentation maintains complete historical data availability while dramatically reducing the computational resources required for search operations.
4Productivity
If a high-performance data storage solution is used for current values, then transaction throughput is improved, but cost increases
Solution Approach 1:
The patent applies different storage strategies to different data based on their access patterns and performance requirements. Hot data (recent transactions and current account balances) are stored in high-performance storage for fast access and throughput, while cold data (historical transactions) are stored in lower-cost storage. This local quality differentiation optimizes transaction throughput for active data while reducing overall storage costs.
Data Source
AI summary
A balance tracker application is disclosed that can track a numerical value, an accumulating value, or any other balance. Time-series tables are employed to store a transaction history. The time-series tables facilitate higher transaction throughput and reduces the likelihood that a managed database will partition tables storing transaction data.