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.

Database performance, code smells, maintainability, multi-model databases

Riaz, M., Mendes, E., Tempero, E.D. Maintainability predictors for relational database-driven software applications: Results from a survey. In: SEKE, pp. 420–425. (2011).

Sharma, T., Spinellis, D. A survey on software smells. The Journal of Systems and Software 138, 158–173 (2018). https://doi.org/10.1016/j.jss.2017.12.034

Yamashita, A., Moonen, L. Do developers care about code smells? An exploratory survey. In: 20th Working Conference on Reverse Engineering, pp. 242–251. IEEE (2013). https://doi.org/10.1109/WCRE.2013.6671299

Martin, R.C. Clean Code. A Handbook of Agile Software Craftsmanship. Pearson Education (2009).

Karwin, B. SQL Antipatterns. Avoiding the Pitfalls of Database Programming. The Pragmatic Bookshelf (2010)

PostgreSQL Global Development Group. PostgreSQL Documentation: Performance Optimization. Retrieved from https://www.postgresql.org/docs/ (2023)

Ferguson, D. PostgreSQL High-Performance Optimization. O'Reilly Media (2021)

Finkel, M. Mastering PostgreSQL: Advanced Performance Tuning. Packt Publishing (2022)

Guo, Q., Zhang, C., Zhang, S., Lu, J. Multi-model query languages: taming the variety of big data. Distributed and Parallel Databases, 42, 31–71 (2024)

Lu, J., Holubova, I. Multi-model Databases: A New Journey to Handle the Variety of Data. ACM Computing Surveys (2019)

Michels, J., Hare, K., Kulkarni, K., Zuzarte, C., Liu, Z.H., Hammerschmidt, B., Zemke, F. The New and Improved SQL: 2016 Standard. SIGMOD Record, 47(2), June 2018

Ong, K.W., Papakonstantinou, Y., Vernoux, R. The SQL++ Unifying Semi-structured Query Language, and an Expressiveness Benchmark of SQL-on-Hadoop, NoSQL and NewSQL Databases. arXiv: 1405.3631 (2015)

Krishnappa, M.S., Harve, B.M., Jayaram, V., Nagpal, A., Ganeeb, K.K., Ingole, B.S. ORACLE 19C Sharding: A Comprehensive Guide to Modern Data Distribution. IJCET, 15(5), Sep-Oct 2024

Akinola, S. Trends in Open Source RDBMS: Performance, Scalability and Security Insights. Journal of Research in Science and Engineering (JRSE), 6(7), July 2024

Natti, M. Reducing PostgreSQL read and write latencies through optimized fillfactor and HOT percentages for high-update applications. International Journal of Science and Research Archive, 9(2), 1059–1062 (2023)

Miryala, N.K. Emerging Trends and Challenges in Modern Database Technologies: A Comprehensive Analysis. International Journal of Science and Research (IJSR), 13(11), Nov 2024

Muhammed, A., Abdullah, Z.H., Ismail, W., Aldailamy, A.Y., Radman, A., Hendradi, R., Afandi, R.R. A Survey of NewSQL DBMSs focusing on Taxonomy, Comparison and Open Issues. IJCSMC, 11(4), Dec 2021