Lifecycle-Based Horizontal Database Partitioning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face inefficiencies in handling complex business requirements due to the separation of OLTP and OLAP systems, which limits their ability to provide optimal performance and flexibility for both transactional and analytical processing.
Innovation Solution
A computer-implemented method for dynamically and horizontally partitioning a relational database table based on the states of records, allowing records to be stored in different partitions according to their usage patterns, thereby optimizing storage and improving performance by utilizing column storage and an insert-only approach.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data is stored in a single centralized warehouse, then data integration and analytics capability are improved, but system complexity and management overhead increase
Solution Approach 1:
The patent divides the centralized data warehouse into multiple partitioned tables, where each partition stores data for a specific lifecycle stage. This segmentation allows the system to maintain analytics capability across multiple smaller, manageable units rather than one large complex warehouse, reducing management overhead while preserving data integration benefits.
Solution Approach 2:
The patent introduces a new dimension of organization by adding lifecycle stage as a partitioning key. Instead of a single flat warehouse structure, data is organized across multiple dimensions (lifecycle stages), enabling efficient querying and analytics while distributing the complexity across manageable partitions.
2Productivity
If OLTP and OLAP are separated into different systems, then transactional performance is improved, but data redundancy and ETL complexity increase
Solution Approach 1:
The patent creates a universal table structure that serves both OLTP and OLAP functions. By organizing data in lifecycle-based partitions with consistent schemas, the same data storage can handle both transactional operations and analytical queries, eliminating the need for separate OLTP and OLAP systems and their associated data redundancy.
Solution Approach 2:
The patent merges the functionality of separate OLTP and OLAP systems into a single unified database structure. By combining transactional data storage with analytical capabilities in one system using lifecycle-based partitioning, it eliminates data redundancy while maintaining both transactional performance and analytics capability.
3Speed
If data is partitioned by lifecycle states, then query performance and storage efficiency are improved, but partition management complexity increases
Solution Approach 1:
The patent implements dynamic partition management where records automatically transition between lifecycle stage partitions based on their state changes. This dynamic approach simplifies management by using automated state-based routing rather than manual partition management, maintaining query performance while reducing operational complexity.
Solution Approach 2:
The system employs self-service partition management where the database automatically handles record placement and movement between partitions based on lifecycle state. This eliminates the need for complex manual partition management while maintaining the performance benefits of lifecycle-based organization, as the system manages itself through automated state transitions.
Data Source
AI summary
The invention relates to a computer-implemented method for dynamically and horizontally partitioning a table of a relational database, wherein the table comprises records, wherein the records are mapped to a plurality of states, wherein the table is horizontally partitioned into a plurality of partitions, and wherein the plurality of states is mapped to the plurality of partitions. The method comprises the steps of changing the assignment of a first record from a first state to a second state, wherein the first state is mapped to a first partition, wherein the second state is mapped to a second partition, wherein the first partition is not the second partition, and storing the first record in the second partition. The invention further relates to a computer system implementing the abovementioned method for dynamically and horizontally partitioning a table of a relational database.