Database Dictionary Lazy Materialization for Transaction Throughput
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face inefficiencies in writing new unique values to dictionary blocks, leading to resource consumption and performance degradation due to immediate storage requirements in both memory and persistent storage.
Innovation Solution
Implementing a lazy materialization approach where new unique values are written to an in-memory array without immediate storage in dictionary blocks, using a pending materialization bit to defer actual storage until a bulk materialization process, allowing for efficient copying of complete ranges of values rather than individual writes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If new unique values are immediately written to dictionary blocks in persistent storage, then data consistency is maintained, but resource consumption increases and performance degrades due to frequent I/O operations
Solution Approach 1:
The patent applies preliminary action by pre-allocating space in dictionary blocks on persistent storage before actual data is written. When a new unique value is added to the in-memory dictionary, the corresponding space is reserved on disk but not immediately written. This allows transactions to proceed without waiting for I/O operations, improving throughput while maintaining consistency through later bulk materialization of the deferred write-set.
2Reliability
If new unique values are immediately stored in both in-memory array and dictionary blocks, then data consistency is ensured, but cache misses increase and performance decreases
Solution Approach 1:
The patent extracts the I/O operation from the critical transaction path by separating the in-memory dictionary update from the persistent storage write. New unique values are added to the in-memory array immediately, while the corresponding dictionary block writes are deferred to a separate materialization process. This extraction eliminates cache misses during transactions, as disk I/O no longer blocks transaction completion.
3Reliability
If individual values are written one-by-one to dictionary blocks, then data integrity is maintained, but resource consumption and time overhead increase
Solution Approach 1:
The patent merges multiple individual write operations into a single bulk materialization operation. Instead of writing each new unique value to the dictionary block immediately, the system accumulates changes in an in-memory array and then performs a single bulk write to persistent storage. This combining of operations reduces I/O overhead and improves efficiency while maintaining data integrity through the coordinated update of the write-set.
Data Source
AI summary
A new unique value to be added to a column of a database table can be written to an in-memory array representing a dictionary of the column, and space can be allocated in at least one dictionary block on at least one page maintained on a persistent storage without writing the new unique value to the at least one dictionary block. A pending materialization bit for the at least one page can be set to identify the at least one page for deferred materialization.