Database Replica Partitioning for Multi-Access Pattern Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database systems face performance deterioration when scaling up or out, especially for tables with multiple access patterns involving columns other than the partitioning column, leading to inefficiencies in query processing and inability to meet stringent performance goals, such as generating billing documents for millions of customers within a short timeframe.

Innovation Solution

The database system is modified to create replica tables with different partition specifications than the original, allowing queries to be routed to either the original or replica tables based on real-time performance predictions, optimizing access patterns and distributing partitions across multiple servers for improved performance.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of time

If database tables are partitioned to improve query performance for specific access patterns, then query execution time is reduced, but the system cannot efficiently handle multiple different access patterns involving different columns

Engineering Contradiction:

Improvequery execution timeVSAvoidability to handle multiple access patterns

Core Design Contradiction:

Loss of timeVSAdaptability or versatility

Solution Approach 1:

The patent creates replica tables that are copies of the original partitioned table, but with different partition specifications. Each replica table is partitioned according to a specific access pattern, allowing the system to efficiently handle multiple different query types by routing them to the appropriate replica table.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The patent changes the partitioning parameters (partition column and partition method) to create different replica tables. By varying these parameters, the system generates specialized tables optimized for different access patterns, thereby improving adaptability while maintaining fast query performance.

Inventive Principle:

Principle #35Parameter changes

2Productivity

If the database system creates multiple replica tables with different partition specifications, then it can handle multiple access patterns efficiently, but the system complexity increases

Engineering Contradiction:

Improvequery processing efficiencyVSAvoidsystem architecture complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent introduces a query routing mechanism that acts as an intermediary between incoming queries and the multiple replica tables. This router analyzes the query's access pattern and directs it to the most appropriate replica table, managing the complexity of having multiple tables while maintaining high processing efficiency.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent segments the database system into multiple specialized replica tables, each handling a specific access pattern. This segmentation allows independent optimization of each table for its intended purpose while the routing mechanism coordinates them, improving overall productivity without overwhelming complexity.

Inventive Principle:

Principle #1Segmentation

3Speed

If replica tables are created to optimize specific access patterns, then query performance improves, but storage requirements and maintenance overhead increase

Engineering Contradiction:

Improvequery processing speedVSAvoidstorage space

Core Design Contradiction:

SpeedVSQuantity of substance

Solution Approach 1:

The patent makes the same underlying data serve multiple functions by creating replica tables with different partition specifications. Each replica table provides the same data but organized differently, allowing a single data set to be optimized for multiple access patterns without requiring completely separate data stores.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS10838981B2Database optimization using special partition specifications for replicas

Publication Date: 2020.11.17 SAP SE

AI summary

A method of modifying a database architecture to improve database performance is disclosed. A replication database table is generated from an original database table. The original database table is divided into a first plurality of partitions optimized for handling accesses for satisfying a first type of query. The replication database table is divided into a second plurality of partitions optimized for handling accesses for satisfying a second type of database query. Based on receiving the database query of the first type, the query of the first type is routed to one host of a plurality of hosts to satisfy the query, the one host hosting one of the first plurality of partitions; based on receiving the database query of the second type, the query is routed to the same or another host of the plurality of hosts to satisfy the query, the second host hosting one of the second plurality of partitions.