An Adaptive Physical Design Optimization Framework for SQL-Based Systems Using Incremental Workload Analysis and Cost-Driven Configuration Search

Abstract

This paper presents a structured algorithmic framework for automated physical design tuning in SQL-based relational databases. The approach centers on an incremental, anytime-capable optimization process that intelligently parses SQL workloads, identifies syntactically relevant and workload-significant table subsets and column groups, and iteratively refines candidate physical design structures including B+-tree indexes, materialized views, and partitioned indexes (conceptual framework support; see Section 4.1 for implementation status). Through a multi-phase methodology involving workload compression, candidate selection, index merging, and greedy enumeration, the system efficiently navigates the configuration space under storage and time constraints. A novel prioritization mechanism ensures early focus on high-impact queries while maintaining the ability to refine recommendations progressively. This SQL-native methodology is fully integrated with the query optimizer's what-if analysis interface (implemented as a prototype extension for PostgreSQL 14, using the HypoPG extension version 1.3.1 for hypothetical index evaluation) and is designed to support large-scale, dynamic workload environments with practical time-bounded tuning scenarios.