Performance comparison between two relational database management systems : B-tree indexing in PostgreSQL and MySQL

Sammanfattning

Databases are used in modern applications and are employed across a wide range of industries and services. Since there are hundreds of different databases to choose from, there are some critical factors to consider, with two of the most important being query speed and memory usage. Numerous studies have compared various databases and their features, employing different technologies to evaluate performance. This thesis compares the performance between two of the most popular relational databases, PostgreSQL and MySQL (InnoDB engine). It focuses on the speed of the CRUD operations (insert, select, update, remove) and memory allocation, in terms of single-column B-tree indexes, which are employed to accelerate data retrieval operations in database tables. The experiment consists of four different database sizes (104 to 107), where each CRUD operation and space allocation is evaluated both with and without the use of indexes for both databases. The results show that indexed MySQL exhibits the fastest performance for select, update, and remove operations on larger datasets, while indexed PostgreSQL is the fastest for these operations on smaller datasets. The results also indicate that using indexes requires additional memory and increases the time needed for the insertion operation in both databases.