Column Store Database for OLTP and OLAP Convergence
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems, particularly relational databases, face inefficiencies in handling complex business requirements due to the separation of OLTP and OLAP systems, which limits their ability to provide flexible and fast data management and analysis, and are not ideally suited for modern multi-core CPUs, leading to performance bottlenecks in memory access and parallel processing.
Innovation Solution
Implementing a column store database approach that uses vertical compression and parallel processing to optimize data storage and retrieval, allowing for efficient set operations and reducing the need for indexing, with an insert-only method for updating binary status variables to minimize locking issues and data redundancy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If row storage with indexing is used for fast access, then single tuple access speed is improved, but access speed decreases as the number of requested tuples increases and memory access efficiency deteriorates
Solution Approach 1:
The patent segments data storage into columnar units rather than storing complete rows together. This allows the database to store and process only the specific columns needed for a query, improving memory efficiency and access speed for bulk operations while maintaining fast single-tuple access through selective column retrieval.
Solution Approach 2:
The patent transitions from traditional row-oriented storage to column-oriented storage, fundamentally changing the dimensional organization of data. This dimensionality change enables efficient compression of identical data types and allows parallel processing of columns, significantly improving bulk data processing productivity while maintaining indexing capabilities for single-tuple access.
2Reliability
If OLTP and OLAP are separated into different systems, then transactional processing reliability is improved, but system complexity and data management overhead increase
Solution Approach 1:
The patent implements a universal database system that can perform both OLTP and OLAP operations using the same columnar storage engine. The system provides transactional processing with ACID compliance while simultaneously supporting analytical queries with aggregations and complex operations, eliminating the need for separate OLTP and OLAP systems and reducing overall system complexity.
Solution Approach 2:
The patent merges previously separate OLTP and OLAP functionalities into a single unified database system. By combining transactional processing capabilities with analytical processing capabilities in one system using columnar storage, it reduces data management overhead and eliminates the complexity of maintaining separate systems while preserving the reliability of transactional operations.
3Reliability
If traditional locking mechanisms are used for updates, then data consistency is maintained, but parallel processing capability and CPU utilization are limited
Solution Approach 1:
The patent uses versioning and copying mechanisms where each tuple maintains its own version history. When updates occur, the system creates new versions of affected tuples rather than using traditional locks, allowing multiple transactions to proceed in parallel without interfering with each other. This maintains data consistency through version control while enabling full utilization of multi-core CPU parallel processing capabilities.
Solution Approach 2:
The patent replaces the mechanical locking system with a software-based version control mechanism. Instead of using hardware or database-level locks that block parallel operations, the system uses tuple versioning and metadata tracking to maintain consistency, allowing concurrent transactions to execute in parallel on multi-core processors while preserving data integrity.
4Speed
If materialized views and indexes are used for query optimization, then query processing speed is improved, but memory consumption and storage requirements increase
Solution Approach 1:
The patent retrieves and processes only the specific columns needed for each query rather than loading entire rows or pre-computed materialized views. This partial action approach loads minimal data into memory, reducing memory consumption while maintaining query processing speed by focusing computational resources only on the necessary data subsets.
Solution Approach 2:
The patent changes the fundamental parameter of data organization from row-based to column-based storage. This parameter change enables efficient compression of columns with similar data types and allows the system to store more data in available memory, reducing the need for materialized views and indexes while maintaining fast query processing through selective column access.
Data Source
Figure 1~2
Figure 3
Figure 4
AI summary
The invention relates to a computer-implemented method for operating a database comprising the step of updating a binary status of a tuple in the database. The binary status field either contains a null value or a time stamp. The update can be done in-place or by using an insert-only approach.