Abstract
Relational databases are getting bigger and more complex. Also, current Database Management Systems (DBMSs) need to respond efficiently to operations on their data. In this context, database optimization is evident as a process of refining database systems, aiming to improve their throughput and performance. This paper evaluates and compares the performance of Oracle and PostgreSQL database systems with the TPC-H benchmark, following a strategy of adding column-based indexes to optimize query execution. Ten TPC-H queries are performed on tables without any restrictions, with primary and foreign keys and with index constraints. The performance in each set of executions is analyzed. The results allow inferring a positive impact when using constraints with a significant speedup as well as better throughput. Oracle has shown stability and robustness for queries, with best results in scenarios with poor performance conditions. However, PostgreSQL showed shorter execution times after the optimizations made and proved to be more sensitive. Global performance results show that Oracle can improve 7% performance with indexes and PostgreSQL 91%. When comparing the results of Oracle with PostgreSQL, no indexes, Oracle/PostgreSQL is 64% faster, and with indexes, PostgreSQL/Oracle is 75% faster.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Similar content being viewed by others
References
Burleson, D.K.: Oracle Tuning: the Definitive Reference. Rampant TechPress, North Carolina (2010)
Chaudhuri, S., Dayal, U., Ganti, V.: Database technology for decision support systems. Computer 34(12), 48–55 (2001)
Chaudhuri, S., Narasayya, V.: Self-tuning database systems: a decade of progress. In: Proceedings of the 33rd International Conference on Very Large Data Bases, pp. 3–14 (2007)
Colley, D., Stanier, C.: Identifying new directions in database performance tuning. Procedia Comput. Sci. 121, 260–265 (2017)
Coronel, C., Morris, S.: Database Systems: Design, Implementation, & Management. Cengage Learning, Boston (2016)
T.P.P. Council. TPC-H Documentation
T.P.P. Council. TPC-H Homepage
Dageville, B., Dias, K.: Oracle’s self-tuning architecture and solutions. IEEE Data Eng. Bull. 29(3), 24–31 (2006)
Date, C.J.: An Introduction to Database Systems. Pearson/Addison Wesley, Boston (2004)
Duan, S., Thummala, V., Babu, S.: Tuning database configuration parameters with ituned. Proc. VLDB Endow. 2(1), 1246–1257 (2009)
Graefe, G.: A survey of b-tree locking techniques. ACM Trans. Database Syst. 35(3), 1–26 (2010)
Graefe, G., Kuno, H.: Self-selecting, self-tuning, incrementally optimized indexes. In Proceedings of the 13th International Conference on Extending Database Technology, EDBT 2010, pp. 371–381, New York, NY, USA. Association for Computing Machinery (2010)
Harrison, G.: SQL High-Performance Tuning, Second Edition. Prentice Hall Professional Technical Reference, 2nd edition (2000)
Jimenez, I., LeFevre, J., Polyzotis, N., Sanchez, H., Schnaitter, K.: Benchmarking online index-tuning algorithms. IEEE Data Eng. Bull. 34(4), 28–35 (2011)
Kamatkar, S.J., Kamble, A., Viloria, A., Hernández-Fernandez, L., Cali, E.G.: Database performance tuning and query optimization. In: Tan, Y., Shi, Y., Tang, Q. (eds.) Data Mining and Big Data, pp, pp. 3–11. Springer, Cham (2018)
Kraska, T., Beutel, A., Chi, E.H., Dean, J., Polyzotis, N.: The case for learned index structures. SIGMOD 2018, pp. 489–504, New York, NY, USA, Association for Computing Machinery (2018)
Li, D., Han, L., Ding, Y.: Sql query optimization methods of relational database system. In: 2010 Second International Conference on Computer Engineering and Applications, vol. 1, pp. 557–560 (2010)
Mehrotra, S.: Sql performance tuning. GlobalLogic Inc., https://www.globallogic.com/wp-content/uploads/2016/02/SQL-Performance-Tuning.pdf. Accessed 14 Jan 2019
Mithani, F., Machchhar, S., Jasdanwala, F.: A novel approach for sql query optimization. In: 2016 IEEE International Conference on Computational Intelligence and Computing Research (ICCIC), pp. 1–4 (2016)
Myalapalli, V.K., Shiva, M.B.: An appraisal to optimize sql queries. In: 2015 International Conference on Pervasive Computing (ICPC), pp. 1–6. IEEE (2015)
Oracle. SQL*Plus oracle database online documentation, 10g release 2 (10.2)
Pavlo, A., Angulo, G., Arulraj, J., Lin, H., Lin, J., Ma, L., Menon, P., Mowry, T.C., Perron, M., Quah, I. et al. Self-driving database management systems. In: CIDR, vol. 4, p. 1 (2017)
PostgreSQL. PSQL postgresql client application
Rupley Jr, M.L.: Introduction to query processing and optimization. Indiana University at South Bend (2008)
Shasha, D., Bonnet, P.: Database tuning: principles, experiments, and troubleshooting techniques. Elsevier (2002)
Thanopoulou, A., Carreira, P., Galhardas, H.: Benchmarking with TPC-h on off-the-shelf hardware. ICEIS 1, 205–208 (2012)
Van Aken, D., Pavlo, A., Gordon, G.J. and Zhang, B.: Automatic database management system tuning through large-scale machine learning. In: Proceedings of the 2017 ACM International Conference on Management of Data, SIGMOD 2017, pp. 1009–1024, New York, NY, USA. Association for Computing Machinery (2017)
Wu, S., Jiang, D., Ooi, B.C., Wu, K.-L.: Efficient b-tree based indexing for cloud data processing. Proc. VLDB Endow. 3(1–2), 1207–1218 (2010)
Acknowledgement
“This work is funded by National Funds through the FCT - Foundation for Science and Technology, IP, within the scope of the project Ref UIDB/05583/2020. Furthermore, we would like to thank the Research Centre in Digital Services (CISeD), the Polytechnic of Viseu for their support.” Special thanks to the 2020 ESTGV MSc students, Ricardo Fernandes and Tiago Amorim for their collaboration.
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2021 The Author(s), under exclusive license to Springer Nature Switzerland AG
About this paper
Cite this paper
Martins, P., Tomé, P., Wanzeller, C., Sá, F., Abbasi, M. (2021). Comparing Oracle and PostgreSQL, Performance and Optimization. In: Rocha, Á., Adeli, H., Dzemyda, G., Moreira, F., Ramalho Correia, A.M. (eds) Trends and Applications in Information Systems and Technologies . WorldCIST 2021. Advances in Intelligent Systems and Computing, vol 1366. Springer, Cham. https://doi.org/10.1007/978-3-030-72651-5_46
Download citation
DOI: https://doi.org/10.1007/978-3-030-72651-5_46
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-030-72650-8
Online ISBN: 978-3-030-72651-5
eBook Packages: Intelligent Technologies and RoboticsIntelligent Technologies and Robotics (R0)