Compression Aware Physical Database Design

TL;DR

This paper explores how data compression impacts physical database design choices, proposing integrated techniques to optimize performance and storage in SQL Server, validated through experiments on real and benchmark workloads.

Contribution

It introduces a novel approach to incorporate compression considerations into physical database design, improving decision-making for index selection and workload performance.

Findings

Compression-aware design improves storage efficiency.

Integrated techniques outperform decoupled approaches.

Validated on real and benchmark workloads.

Abstract

Modern RDBMSs support the ability to compress data using methods such as null suppression and dictionary encoding. Data compression offers the promise of significantly reducing storage requirements and improving I/O performance for decision support queries. However, compression can also slow down update and query performance due to the CPU costs of compression and decompression. In this paper, we study how data compression affects choice of appropriate physical database design, such as indexes, for a given workload. We observe that approaches that decouple the decision of whether or not to choose an index from whether or not to compress the index can result in poor solutions. Thus, we focus on the novel problem of integrating compression into physical database design in a scalable manner. We have implemented our techniques by modifying Microsoft SQL Server and the Database Engine Tuning Advisor (DTA) physical design tool. Our techniques are general and are potentially applicable to DBMSs that support other compression methods. Our experimental results on real world as well as TPC-H benchmark workloads demonstrate the effectiveness of our techniques.