Comparing Oracle and PostgreSQL, Performance and Optimization
Pedro Martins,Paulo Tomé,Cristina Wanzeller,Filipe Sá,Maryam Abbasi
DOI: https://doi.org/10.1007/978-3-030-72651-5_46
2021-01-01
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.