Integrating vertical and horizontal partitioning into automated physical database design

TLDR

Partitioning—both horizontal and vertical—plays a critical role in relational database performance and manageability, yet its integration with indexes and materialized views expands the design space and complicates automation. This study introduces scalable techniques that jointly optimize performance and manageability in automated physical design. The authors implemented these techniques in Microsoft SQL Server and evaluated them experimentally. Experiments demonstrate that an integrated approach improves scalability and underscores the necessity of considering partitioning alongside indexes and views.

Abstract

In addition to indexes and materialized views, horizontal and vertical partitioning are important aspects of physical design in a relational database system that significantly impact performance. Horizontal partitioning also provides manageability; database administrators often require indexes and their underlying tables partitioned identically so as to make common operations such as backup/restore easier. While partitioning is important, incorporating partitioning makes the problem of automating physical design much harder since: (a) The choices of partitioning can strongly interact with choices of indexes and materialized views. (b) A large new space of physical design alternatives must be considered. (c) Manageability requirements impose a new constraint on the problem. In this paper, we present novel techniques for designing a scalable solution to this integrated physical design problem that takes both performance and manageability into account. We have implemented our techniques and evaluated it on Microsoft SQL Server. Our experiments highlight: (a) the importance of taking an integrated approach to automated physical design and (b) the scalability of our techniques.