Append-Only Data Store for Read Optimized Bulk Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Data warehouse databases face significant overhead and reduced read performance due to the per-record Multi-Version Concurrency Control (MVCC) overhead, which is exacerbated by the need to maintain large numbers of locks and redundant transaction visibility information, especially when dealing with billions of records.
Innovation Solution
Implementing an append-only data store that eliminates transaction visibility information per row and uses a read-only visibility manager to control access, allowing for efficient read operations without locks, and maintaining MVCC information out of line to reduce storage and computational overhead.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If MVCC information is maintained per record to ensure snapshot isolation, then consistency and isolation guarantees are improved, but storage overhead and read performance deteriorate
Solution Approach 1:
The patent extracts MVCC information from individual records and consolidates it into a separate transaction snapshot file. This separation removes the per-record overhead while preserving the isolation guarantees, as the consolidated file contains all necessary versioning information accessible to all records.
Solution Approach 2:
The system segments data storage into two distinct components: the main data store containing only transactional records, and a separate transaction snapshot file containing all MVCC metadata. This segmentation allows each component to be optimized independently for its specific function.
2Reliability
If locks are maintained to ensure ACID properties in data warehouse databases, then reliability is improved, but concurrency performance and system complexity worsen
Solution Approach 1:
The patent introduces a transaction snapshot file as an intermediary structure that mediates between multiple readers and the underlying data store. This intermediary provides the isolation guarantee without requiring locks on the actual data records, enabling high-concurrency read operations while maintaining ACID properties.
3Reliability
If per-record MVCC information is maintained in data warehouse databases, then isolation is improved, but read rate and storage efficiency worsen
Solution Approach 1:
By extracting MVCC information from individual records into a centralized transaction snapshot file, the system eliminates the per-record overhead that slowed down read operations. Readers can now access consolidated versioning information once per query rather than processing MVCC data with every record access.
Solution Approach 2:
The patent merges all MVCC metadata into a single transaction snapshot file that serves the entire data warehouse. This consolidation allows the system to maintain strong isolation guarantees while improving read performance, as the consolidated structure enables more efficient metadata management and access patterns.
Data Source
Figure 1
Figure 2
Figure 3~4
AI summary
A computer readable storage medium includes executable instructions to store load data in new rows of an append only table within a data warehouse, without updating or deleting existing rows in the append only table. The new rows are added in a database transaction that guarantees that the distributed reads and writes of all transactions are processed reliably and that the transactions obey snapshot isolation rules. Data changes associated with the load data are maintained in a separate update table. Data from the append only table is merged with data changes from the update table to supply read data.