Adaptive Column Store Database Building from Row Store

Solution Overview

Problem

Traditional hybrid row/column store database systems overbuild column stores with irrelevant data, struggle with real-time analytics due to predictive approaches, and incur performance penalties during system crashes, as they are built statically and rely on ETL processes that disrupt accessibility.

Innovation Solution

A computer system and method for adaptively building and updating a column store database from a row store database using query demands, where attribute vectors are dynamically created and updated based on received queries, ensuring only relevant data is stored and maintaining real-time analytics capabilities without disrupting system accessibility.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If a column store database is built statically before any analytic queries are received using traditional ETL processes, then the database system can handle OLTP transactions efficiently, but the column store will be overbuilt to include data that is irrelevant for any subsequent query and real-time analytics cannot be provided

Engineering Contradiction:

Core Design Contradiction:

ReliabilityVSLoss of substance

Solution Approach 1:

The patent applies preliminary action by pre-building only the primary key attribute vector in the column store before queries are received, rather than pre-building all attribute vectors. This allows the system to have some column store structure in place for efficient lookups while avoiding the waste of storing all possible attribute data statically.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent implements dynamics by dynamically building attribute vectors in the column store based on actual query demands rather than statically pre-building them. The system adapts the column store structure in real-time by identifying which attributes are actually needed for analytic queries and building only those attribute vectors, thus avoiding overbuilding while maintaining query performance.

Inventive Principle:

Principle #15Dynamics

2Reliability

If an ETL process is executed during off-peak periods to transfer data from row store to column store, then the column store can be built, but database accessibility is severely affected during the ETL operation and there are no longer any off-peak periods available

Engineering Contradiction:

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The patent applies partial action by building only the necessary attribute vectors in the column store based on actual query demands rather than transferring all data from the row store. This selective approach allows the system to maintain database accessibility while still providing real-time analytics capabilities for the specific attributes that are actually needed.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The system performs preliminary building of the primary key attribute vector and then dynamically builds other attribute vectors as needed based on query patterns, rather than performing a complete ETL transfer. This staged approach minimizes disruption to database accessibility while still achieving real-time analytics performance.

Inventive Principle:

Principle #10Preliminary action

3Reliability

If the entire column store database is rebuilt during system recovery after a crash, then data consistency is restored, but the system remains down during the recovery process and suffers huge performance penalty

Engineering Contradiction:

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The patent applies preliminary action by maintaining a log of change transactions that are applied to the column store before analytic queries are executed. This allows the column store to be kept synchronized with the row store without requiring a complete rebuild during recovery, thus reducing system downtime while maintaining data consistency.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system uses feedback from change transaction logs to selectively update only the attribute vectors that have been modified since the last synchronization point, rather than rebuilding the entire column store. This feedback mechanism enables efficient recovery by applying only the necessary updates to maintain data consistency.

Inventive Principle:

Principle #23Feedback

Data Source

PatentEP4137965A1Method and system for adaptively building and updating a column store database from a row store database based on query demands

Publication Date: 2023.02.22

Figure 1
Figure 2A
Figure 2B

AI summary

A method for adaptively building and updating a column store database from a row store database using queries. The method includes establishing the row store database for storing data, wherein at least one row of the row store includes attributes. The method includes establishing the column store including data structured to satisfy received analytic queries, wherein the column store includes attribute vectors corresponding to attributes in the row store, wherein at least one of the attribute vectors includes data used to satisfy previously received analytic queries. The method includes receiving change transactions directed to the row store, wherein the change transactions are ordered. The method includes when a first referenced attribute referenced by an analytic query corresponds to an attribute vector in the column store, updating the attribute vector based on log information corresponding to change transactions directed to the referenced attribute to satisfy the analytic query.