Micro-partition Consolidation for Database Query Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional relational database management systems require extensive computing and storage resources, are costly, and have limited scalability, making them inefficient for managing large datasets and prone to data loss during disasters.

Innovation Solution

A network-based database system with a compute service manager that utilizes micro-partitioning and expression property files to optimize query processing, allowing for efficient data organization and retrieval by scanning relevant micro-partitions based on metadata, thereby reducing computing resources and query response time.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Quantity of substance

If traditional relational database management systems are used to manage large datasets, then data storage and retrieval capabilities are provided, but extensive computing and storage resources are required, making the system costly and inefficient

Engineering Contradiction:

Improvedata storage capacityVSAvoidcomputing resource consumption

Core Design Contradiction:

Quantity of substanceVSUse of energy by moving object

Solution Approach 1:

The patent divides database tables into multiple micro-partitions based on column value ranges. Each micro-partition stores a subset of rows, allowing the system to scan only relevant micro-partitions during query execution rather than entire tables. This segmentation reduces computing resource consumption while maintaining data storage capacity.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system pre-computes and stores metadata about each micro-partition, including minimum and maximum values for each column. This preliminary action enables the query optimizer to quickly determine which micro-partitions are relevant to a query without scanning the entire table, thereby reducing computing resource requirements during query execution.

Inventive Principle:

Principle #10Preliminary action

2Adaptability or versatility

If traditional database systems store data across multiple computing devices, then data accessibility is improved, but the system becomes highly susceptible to data loss during power outages or disaster situations

Engineering Contradiction:

Improvedata accessibilityVSAvoiddata loss susceptibility

Core Design Contradiction:

Adaptability or versatilityVSReliability

Solution Approach 1:

The patent merges multiple micro-partitions into consolidated micro-partitions when possible, reducing the total number of partitions that need to be managed across distributed devices. This consolidation improves reliability by reducing the number of potential failure points while maintaining data accessibility through the distributed architecture.

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The system implements redundant storage and backup mechanisms for micro-partitions across the distributed computing devices. By pre-positioning backup copies of critical micro-partitions on multiple devices, the system cushions against data loss during power outages or disasters, ensuring data reliability while maintaining accessibility.

Inventive Principle:

Principle #11Beforehand cushioning (Prior cushioning)

3Reliability

If cloud-based database systems are used to mitigate data loss risk, then data security is improved, but the complexity of managing backend processes increases

Engineering Contradiction:

Improvedata loss mitigationVSAvoidbackend process management complexity

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The patent implements automatic micro-partition consolidation and management processes that operate without requiring manual intervention. The system automatically identifies candidate micro-partitions for consolidation, performs the consolidation operations, and updates metadata accordingly. This self-service approach reduces backend management complexity while maintaining data reliability through consistent micro-partition management.

Inventive Principle:

Principle #25Self-service

4Productivity

If micro-partitioning is implemented to reduce computing resources, then query processing efficiency is improved, but the complexity of data organization increases

Engineering Contradiction:

Improvequery processing efficiencyVSAvoiddata organization complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent creates a universal metadata structure that serves multiple functions: it describes micro-partition boundaries, stores minimum and maximum column values for query filtering, and tracks consolidation status. This multi-functional metadata approach simplifies the overall data organization complexity while enabling efficient query processing across all micro-partitions.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS11537613B1Merge small file consolidation

Publication Date: 2022.12.27 SNOWFLAKE INC

AI summary

The subject technology receives a query plan corresponding to a query. The subject technology executes the query based at least in part on the query plan, the executing including: filtering a first set of files that are to be modified by a merge statement, performing a split operation to send information related to a second set of files to a scan set builder operation in a first portion of the query plan and scan back operation in a second portion of the query plan, performing the scan set builder operation to remove the second set of files from the first set of files, performing a table scan operation based on a third set of files, and performing a first union all operation to combine the first set of data with a second set of data as a first set of combined data.