Abstract
Performance issues in relational databases are often diagnosed reactively through the isolated analysis of metrics, making it difficult to identify their underlying causes. The objective of this study was to measure the effectiveness of a structured methodology based on logs and wait events. A quantitative approach with an applied quasi-experimental design was employed to compare two conditions on the same PostgreSQL 16 instance: a baseline condition without supporting indexes and an optimized condition following a diagnosis-driven intervention. A concurrent workload was generated using `pgbench` on a synthetic dataset comprising 32,050,000 records. Performance Insights, EXPLAIN ANALYZE, and `pg_stat_statements` were used to analyze wait events, execution plans, and query statistics. During the three complete minutes of the baseline test, the average active load was 14.867 sessions, compared with 2 available vCPUs. Following the intervention, throughput increased from 0.43 to 203.48 transactions per second, while mean latency decreased from 18,409 to 39 milliseconds. The findings provided favorable evidence of the methodology's effectiveness under the evaluated conditions, although its applicability to other environments requires further validation.
References
Abadi, D., Ailamaki, A., Andersen, D. G., Bailis, P., Balazinska, M., Bernstein, P. A., Boncz, P. A., Chaudhuri, S., Cheung, A., Doan, A., Dong, L., Franklin, M. J., Freire, J., Halevy, A. Y., Hellerstein, J. M., Idreos, S., Kossmann, D., Kraska, T., Krishnamurthy, S., ... Suciu, D. (2022). The Seattle report on database research. Communications of the ACM, 65(8), 72–79. https://doi.org/10.1145/3524284
Abbasi, M., Bernardo, M. V., Váz, P., Silva, J., & Martins, P. (2024). Revisiting database indexing for parallel and accelerated computing: A comprehensive study and novel approaches. Information, 15(8), Article 429. https://doi.org/10.3390/info15080429
Anchlia, A. (2024). Enhancing query performance through relational database indexing. International Journal of Computer Trends and Technology, 72(8), 130–133. https://doi.org/10.14445/22312803/IJCTT-V72I8P119
Barbosa, D., Gruenwald, L., D’Orazio, L., & Bernardino, J. (2024). QRLIT: Quantum reinforcement learning for database index tuning. Future Internet, 16(12), Article 439. https://doi.org/10.3390/fi16120439
Currim, S., Snodgrass, R. T., & Suh, Y. K. (2024). Identifying the root causes of DBMS suboptimality. ACM Transactions on Database Systems, 49(1), Article 4. https://doi.org/10.1145/3636425
Das, S., Grbic, M., Ilic, I., Jovandic, I., Jovanovic, A., Narasayya, V. R., Radulovic, M., Stikic, M., Xu, G., & Chaudhuri, S. (2019). Automatically indexing millions of databases in Microsoft Azure SQL Database. In Proceedings of the 2019 International Conference on Management of Data (pp. 666–679). Association for Computing Machinery. https://doi.org/10.1145/3299869.3314035
Harizopoulos, S., Abadi, D. J., Madden, S., & Stonebraker, M. (2018). OLTP through the looking glass, and what we found there. En M. L. Brodie (Ed.), Making databases work: The pragmatic wisdom of Michael Stonebraker (pp. 409–439). Association for Computing Machinery. https://doi.org/10.1145/3226595.3226635
Hashim, H. B. (2026). Performance evaluation of B-Tree and hash indexing under varying data sizes in relational database systems. Computing and Artificial Intelligence, 4(1), Article 4104. https://doi.org/10.59400/cai4104
Huang, S., Wang, Z., Wu, Y., Tu, Y., Wang, J., & Cui, B. (2025). OpDiag: Unveiling database performance anomalies through query operator attribution. IEEE Transactions on Knowledge and Data Engineering, 37(6), 3613–3626. https://doi.org/10.1109/TKDE.2025.3557049
Kleppmann, M. (2017). Designing data-intensive applications: The big ideas behind reliable, scalable, and maintainable systems. O’Reilly Media.
Kompalli, K. (2024). Automated monitoring and alerting frameworks for enterprise databases: Architecture, thresholds, and root-cause identification. International Journal of Scientific Research in Science and Technology, 11(4), 766–780. https://doi.org/10.32628/IJSRST25125216
Kurmanji, M., & Triantafillou, P. (2023). Detect, distill and update: Learned DB systems facing out of distribution data. Proceedings of the ACM on Management of Data, 1(1), 1–27. https://doi.org/10.1145/3588713
Lao, J., Wang, Y., Li, Y., Wang, J., Zhang, Y., Cheng, Z., Chen, W., Tang, M., & Wang, J. (2024). GPTuner: A manual-reading database tuning system via GPT-guided Bayesian optimization. Proceedings of the VLDB Endowment, 17(8), 1939–1952. https://doi.org/10.14778/3659437.3659449
Leis, V., Gubichev, A., Mirchev, A., Boncz, P., Kemper, A., & Neumann, T. (2015). How good are query optimizers, really? Proceedings of the VLDB Endowment, 9(3), 204–215. https://doi.org/10.14778/2850583.2850594
Li, Z., Tu, Y., & Ma, Z. (2023). A sample-aware database tuning system with deep reinforcement learning. Journal of Database Management, 35(1), 1–25. https://doi.org/10.4018/JDM.333519
Lim, W. S., Ma, L., Zhang, W., Butrovich, M., Arch, S., & Pavlo, A. (2024). Hit the gym: Accelerating query execution to efficiently bootstrap behavior models for self-driving database management systems. Proceedings of the VLDB Endowment, 17(11), 3680–3693. https://doi.org/10.14778/3681954.3682030
Ma, L., Van Aken, D., Hefny, A., Mezerhane, G., Pavlo, A., & Gordon, G. J. (2018). Query-based workload forecasting for self-driving database management systems. In Proceedings of the 2018 International Conference on Management of Data (pp. 631–645). Association for Computing Machinery. https://doi.org/10.1145/3183713.3196908
Ma, L., Zhang, W., Jiao, J., Wang, W., Butrovich, M., Lim, W. S., Menon, P., & Pavlo, A. (2021). MB2: Decomposed behavior modeling for self-driving database management systems. In Proceedings of the 2021 International Conference on Management of Data (pp. 1248–1261). Association for Computing Machinery. https://doi.org/10.1145/3448016.3457276
Shin, Y., Ahn, J., & Im, D. (2022). Join optimization for inverted index technique on relational database management systems. Expert Systems with Applications, 198, Article 116956. https://doi.org/10.1016/j.eswa.2022.116956
Solohub, V., & Pashkevych, V. (2026). Comparative analysis of query optimization techniques in modern relational database systems. Journal of Computer Sciences Institute, 39, 132–137. https://doi.org/10.35784/jcsi.8944
Stonebraker, M., & Pavlo, A. (2024). What goes around comes around... and around. ACM SIGMOD Record, 53(2), 21–37. https://doi.org/10.1145/3685980.3685984
Tong, Y., Liu, F., Xu, J., Wang, H., Zhou, K., & Miao, J. (2025). BISLearner: Block-aware index selection using attention-based reinforcement learning for data analytics. ACM Transactions on Database Systems, 51(1), Article 2. https://doi.org/10.1145/3760773
Trummer, I. (2022). DB-BERT: A database tuning tool that reads the manual. In Proceedings of the 2022 International Conference on Management of Data (pp. 190–203). Association for Computing Machinery. https://doi.org/10.1145/3514221.3517843
Van Aken, D., Yang, D., Brillard, S., Fiorino, A., Zhang, B., Billian, C., & Pavlo, A. (2021). An inquiry into machine learning-based automatic configuration tuning services on real-world database management systems. Proceedings of the VLDB Endowment, 14(7), 1241–1253. https://doi.org/10.14778/3450980.3450992
Zhang, J., Liu, Y., Zhou, K., Li, G., Xiao, Z., Cheng, B., Xing, J., Wang, Y., Cheng, T., Liu, L., Ran, M., & Li, Z. (2019). An end-to-end automatic cloud database tuning system using deep reinforcement learning. In Proceedings of the 2019 International Conference on Management of Data (pp. 415–432). Association for Computing Machinery. https://doi.org/10.1145/3299869.3300085

This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.
Copyright (c) 2026 Angel Enrique Romero Cuevas, Gabriel Sánchez Bautista
