Optimizing Relational and Multi-Model Database Performance: Insights from Software Maintainability, Code Smells, and Query Language Evolution

Abstract

The exponential growth of data complexity in contemporary software systems has necessitated a renewed focus on database performance, maintainability, and developer practices. Relational Database Management Systems (RDBMSs) continue to serve as the backbone of enterprise applications, while emerging multi-model and NewSQL databases attempt to address the limitations inherent in traditional architectures. This study investigates the interplay between maintainability predictors, code and SQL smells, and database optimization strategies, drawing from extensive surveys, empirical studies, and best-practice frameworks. Emphasis is placed on PostgreSQL as a case study for high-performance relational systems, highlighting strategies to reduce read and write latencies, improve query execution, and maintain code integrity. Theoretical insights from software engineering literature, including the impact of maintainability metrics and code smells on long-term system stability, are juxtaposed with contemporary advances in query languages and database paradigms. Results indicate that adherence to clean coding principles, coupled with awareness of common SQL antipatterns, significantly enhances maintainability and operational efficiency. Moreover, the adoption of multi-model databases introduces novel challenges and opportunities for query optimization, data distribution, and system scalability. Limitations of current research include the heterogeneity of database environments and the evolving nature of developer practices, which complicates generalizable recommendations. Future work is suggested in integrating automated detection of code and SQL smells with adaptive database tuning, fostering a symbiotic relationship between software craftsmanship and database optimization. This research contributes a comprehensive framework linking software quality practices to database performance outcomes, offering actionable insights for developers, database administrators, and system architects.