Database management system performance comparisons : A systematic literature review

This is a self-archived version of an original article. This version may differ from the original in pagination and typographic details. Author(s): Title: Year: Version: Copyright: Rights: Rights url: Please cite the original version: CC BY 4.0 https://creativecommons.org/licenses/by/4.0/ Database management system performance comparisons : A systematic literature review © 2023 The Author(s). Published by Elsevier Inc. Published version Taipalus, Toni Taipalus, T. (2024). Database management system performance comparisons : A systematic literature review. Journal of Systems and Software, 208, Article 111872. https://doi.org/10.1016/j.jss.2023.111872 2024 The Journal of Systems and Software 208 (2024) 111872 Available online 27 October 2023 0164-1212/© 2023 The Author(s). Published by Elsevier Inc. This is an open access article under the CC BY license (http://creativecommons.org/licenses/by/4.0/). Contents lists available at ScienceDirect The Journal of Systems & Software journal homepage: www.elsevier.com/locate/jss Database management system performance comparisons: A systematic literature review✩ Toni Taipalus Faculty of Information Technology, University of Jyväskylä, P.O. Box 35, FI-40014, Finland ARTICLE INFO Keywords: Database Performance Comparison Database management system Relational database NoSQL NewSQL ABSTRACT Efficiency has been a pivotal aspect of the software industry since its inception, as a system that serves the end-user fast, and the service provider cost-efficiently benefits all parties. A database management system (DBMS) is an integral part of effectively all software systems, and therefore it is logical that different studies have compared the performance of different DBMSs in hopes of finding the most efficient one. This study systematically synthesizes the results and approaches of studies that compare DBMS performance and provides recommendations for industry and research. The results show that performance is usually tested in a way that does not reflect real-world use cases, and that tests are typically reported in insufficient detail for replication or for drawing conclusions from the stated results. 1. Introduction Efficiency is important in effectively all software systems, whether efficiency is measured by response times, how many concurrent users the system can serve, or how energy-efficient the system is (Toffola et al.,2018). Despite its importance, many software systems suffer from efficiency problems (Jin et al.,2012), as optimization has been largely recognized as a complex task (Toffola et al.,2018;Difallah et al.,2013). The more a system holds and handles data, the more the system’s performance depends on the database, and the database is often one of the first suspects when a performance issue is detected. The domain of database management systems (DBMS) saw rapid advancements in performance especially in the 1980s and 1990s, as benchmarking competitions between DBMS and hardware vendors led to innovations in DBMS technology that significantly improved DBMS performance (DeWitt and Levine,2008). Performance improvements are related to DBMS aspects such as different supporting data structures (Valduriez, 1987), and algorithms for sorting (Estivill-Castro and Wood,1992; Do et al.,2022) and joining (Schneider and DeWitt,1989;Patel and DeWitt,1996). Given that DBMSs are annually a multi-billion dollar industry, the performance of a DBMS is one of the most crucial aspects when a company chooses a DBMS for their product or service (Dietrich et al.,1992). As different DBMS performance comparison studies and DBMS vendor white-papers highlight the performance gains of one DBMS over another, it may seem tempting to either consider choosing the fastest DBMS for a business domain or to migrate from one DBMS to another for performance gains. However, as we show and argue in this ✩Editor: Dr. Jacopo Soldani. E-mail address: [email protected]. study, performance is typically tested in very specific contexts which are not necessarily generalizable, and there are other aspects besides performance to consider. This study was inspired by a study by Raasveldt et al. (2018), which claimed that ‘‘[...] we will explore the common pitfalls in database performance benchmarking that are present in a large number of scientific works [...]’’ while consciously refraining from citing example studies. While we agree with their claim based on our personal experiences, we wanted to systematically explore whether this phenomenon is common among performance comparisons, and whether such studies show performance gains of one DBMS over another in a setting that can be replicated. This study is not an attempt to criticize studies comparing DBMS performance, as no scientific study (ours included) is without threats to validity. Rather, based on the survey of the literature, the primary goals of our study are to propagate information on (i) how DBMS performance has been tested, (ii) how performance has been recommended to be tested, (iii) how the performance comparison results should be interpreted, (iv) what other aspects besides performance should be considered, and (v) what other avenues might be fruitful for DBMS performance testing. Additionally, we provide (vi) a relatively accessible background on database system performance, followed by (vii) a systematic review of literature on DBMS performance comparisons, (viii) describing which DBMSs and which types of DBMSs have been compared with each other, (ix) the outcomes of the performance comparisons, and (x) by which benchmarks the DBMSs have been compared. https://doi.org/10.1016/j.jss.2023.111872 Received 10 March 2023; Accepted 4 October 2023 The Journal of Systems & Software 208 (2024) 111872 2 T. Taipalus The rest of this study is structured as follows. In Sections 2and 3, we provide theoretical background for understanding the results and discussion provided by this study. These background sections are deliberately presented by refraining from using unnecessary information technology-related terms, acronyms, algorithms, or mathematics, to cater to the needs of readers from various backgrounds. For readers more technically inclined or interested, we have provided further reading at the end of Sections 2and 3. Section 4details how we searched, selected, and categorized the DBMS performance comparison studies, and Section 5presents a high-level overview of the results, which is complemented by the Appendix detailing the performance comparison outcomes. In Section 6, we discuss what these findings mean, how they are applicable in industry, and present our recommendations for industry and research based on the findings. Section 7concludes the study. 2. Database systems 2.1. Database system overview A database is a collection of interrelated data, typically stored according to a data model. Typically, the database is used by one or several software applications via a DBMS. Collectively, the database, the DBMS, and the software application are referred to as a database system (Elmasri and Navathe,2016, p. 7)(Connolly and Begg,2015, p. 65). The separation of the database and the DBMS, especially in the realm of relational databases, is typically impossible without exporting the database in another format. In these situations, the database is often unusable by the DBMS, unless the database is imported back to a format understood by the DBMS. Possibly due to this inseparability, both the DBMS and the underlying database are often colloquially referred to simply as database. It is worth noting, though, that the former is a piece of software that does, while the other is a collection of data that is. Fig. 1 shows a simplified example of a system where the components crucial for a database system and the scope of this study are emphasized. We refer to the components in the figure throughout this study. Several things are worth noting in considering the figure, as we have traded technical precision and comprehensiveness for ease of presentation by depicting only a single end-user, a single software application (some parts typically reside on the end-user’s device, while others reside on a separate server), a single DBMS, single hardware components, and a single database. Furthermore, we have not illustrated other DBMS components such as access control, data structures such as metadata, or outputs such as query execution plans. The figure also adopts the view that the database resides in persistent storage — this is not always the case. Additionally, we have depicted merely a centralized database system in which neither the DBMS nor the database has been distributed across multiple nodes. These are willful omissions given the scope of this study. 2.2. Data models Databases follow one or several data models, i.e., definitions of how and what data can be stored, and sometimes, what operations are available for data retrieval and manipulation. Data models may be conceptual, logical, or physical. Conceptual models such as the EntityRelationship model (Chen,1976) do not dictate how data should be stored, but are rather used to describe the interrelations and characteristics of the data. Logical data models such as the relational model (Codd,1970) are related to how data is stored and presented, but often without describing how the data is physically stored, e.g., which computing node is responsible for storing the data, where the data is located on a disk, and what types of indices (i.e., redundant data structures which facilitate query performance) and physical data retrieval operators are available. One DBMS is not limited to using a single data model (Forresi et al.,2022). There are several popular logical data models, some of which are inseparable from their underlying physical data models. One of the most prominent logical data models is the relational data model rooted in set theory (Codd,1970). Relational DBMSs (RDBMS) follow many of the concepts introduced in the relational model. Many of the popular RDBMSs such as PostgreSQL and Oracle Database have adopted data structures from other logical data models as well (Lu and Holubová, 2019). What is common for effectively all modern RDBMSs is that they utilize Structured Query Language (SQL) (ISO/IEC,2016a,b) to define data structures and to retrieve and manipulate data. Typically, RDBMSs also implement a strong data consistency model which dictates or allows that database operations grouped into a transaction must all succeed or all fail, data must follow defined business logic, successful transactions persist in storage, and concurrent transactions (cf. Bernstein and Goodman,1981) must result in the same data as if the transactions were serial. At least the last rule can often be loosened in modern implementations to various degrees. These constraints are collectively referred to as the ACID consistency model (Haerder and Reuter,1983). NoSQL is an umbrella term for several data models, typically developed or popularized in the first decade of the 2000s (Grolinger et al.,2013). Contrary to the relational model, the data models within NoSQL typically have no formal definitions, and different NoSQL DBMSs implement different data models such as key–value (e.g., Redis), document (e.g., MongoDB), wide-column (e.g., Cassandra) and graph (e.g., Neo4J) (Davoudian et al.,2018;Reniers et al.,2017). Furthermore, these DBMSs often have a distinct query language developed to cater to the particular data structures available in the DBMS’s implementation of a data model. While RDBMSs have favored data consistency (Chaudhry and Yousaf,2020) by eliminating redundant data through logical database design, and through a strong consistency model, NoSQL DBMSs have generally adopted the opposite approach. In several NoSQL data models such as key–value pairs and documents, redundant data are stored at the cost of storage space (Hecht and Jablonski,2011). This approach enables query languages to be simple (Dey et al.,2014), avoiding complex and potentially slow queries. Furthermore, consistency models are typically less strict than in RDBMSs (Stonebraker,2010), which facilitates higher performance demanded by, e.g., web applications with a large number of concurrent users (Ramakrishnan,2012). Although NoSQL DBMSs popularized several database-related approaches such as non-strict database structures, data availability over data consistency, and relatively effortless database replication (i.e., data is copied over computing nodes) and sharding (i.e., data is divided between computing nodes) (Grolinger et al.,2013), some industry leaders such as Google deemed a strong consistency model and an expressive query language important enough to design a DBMS which incorporates features from both RDBMSs and NoSQL DBMSs (Corbett et al.,2013). These so-called NewSQL DBMSs use the relational model, often with extensions, SQL as their primary query language, and a distributed database architecture (Pavlo and Aslett,2016). In addition to these three main categories of RDBMS, NoSQL, and NewSQL data models, others such as object stores (Kulshrestha and Sachdeva,2014) and GPU-intensive (Suh et al.,2022) systems are used in specific contexts. 2.3. Query execution The word query typically refers to query language statements that retrieve some data from the database. However, in this study, we use the word query to refer to any data retrieval and manipulation statement for brevity. In times it is necessary to differentiate between data retrieval and manipulation, we use appropriate terms such as read operations for data retrieval, and write operations for data insertion, updates, and deletes. In this subsection, we describe how queries are The Journal of Systems & Software 208 (2024) 111872 3 T. Taipalus Fig. 1. A simplified view of a database system and the end-user with the emphasis on components relevant to this study; the arrows represent the flow of information from the end-user’s device to the database residing in persistent storage; the flow of information back to the software application is not illustrated here; gray rectangles represent boundaries of physical devices. executed, using mainly general (i.e., not specific to a single DBMS) literature from the domain of RDBMS query execution. When a user — were it a human actor directly using a terminal, a transaction processing software application, or a database benchmark software — submits a query to a DBMS, a multitude of events must take place before the user receives feedback. Illustrated in a general fashion in Fig. 1, the query parser checks, among other things, that the query is syntactically valid (Hellerstein et al.,2007). If the query passes these (and other) checks, the query is translated to a lower-level presentation and passed to the query optimizer. The optimizer generates one or several query execution plans. These plans consist of physical operators for implementing, e.g., which physical data structures will be utilized in executing the query, and in RDBMSs in particular, how tables are joined together (Graefe,1993). If several plans are generated, the optimizer evaluates which of these plans is the most effective in regards to, e.g., query execution time (Hellerstein et al.,2007). The accuracy of the optimizer relies on aspects such as database metadata (Christodoulakis, 1984), statistics of previous query executions, and the indices available (Chaudhuri,1998). Generating effective query execution plans is a complex effort and takes time (Graefe,1993;Chaudhuri,1998), but once formulated, the plans can be re-used to a degree. Next, the query execution engine implements the query execution plan, using the physical operators therein. Simplified, the data objects required by the query are typically first searched from a memory area called the buffer pool which is allocated and maintained by the DBMS. If some or all data is not found, the data is requested from disk. Before accessing the disk, many systems may additionally utilize other areas of memory to avoid disk access (Yang and Lilja,2018). Effectively all database systems function in an environment where multiple concurrent end-users use the database. This concurrency presents challenges particularly when the users execute write operations on the same database, e.g., when two or more users withdraw money from the same bank account, concurrently updating the balance (Bernstein and Goodman,1981). To guarantee that the write operations do not interfere with each other in a way that would cause the data to not represent the real world, DBMSs typically implement concurrency control through locking or versioning data. Effectively, the simpler implementations of locking restrict data objects from being accessed by other operations while the data objects are being modified (Hellerstein et al.,2007). These locking mechanisms may be implemented to ensure that no anomalies happen, or with implementations that theoretically allow some anomalies (Berenson et al.,1995). Typically, the business domain dictates what types of anomalies are tolerated. Finally, as strong consistency models often require that transactions persist in the database and that all or none of the operations in a transaction succeed, locking is typically complemented by transaction logs. These logs are written before write operations are committed to the database, and can be used in reversing earlier write operations if a later write operation in the same transaction fails. All these considerations discussed in this section play a significant role from a performance perspective, which is discussed in the next section. Further reading on database systems: for readers interested in the basics of database systems, either the undergraduate level textbook by Connolly and Begg (2015), or Elmasri and Navathe (2016) are excellent albeit lengthy introductions covering the topic from several points of view and with the focus on RDBMSs. For readers interested in query processing, we point to studies by Chaudhury (1998), and Hellerstein, Stonebraker and Hamilton (2007). If you are interested in logical relational database design, the book by Date (2019) is an indepth resource covering both formal and informal approaches. For a survey of literature on NoSQL data models, the study by Davoudian et al. (2018) is an accessible starting point. 3. Performance 3.1. Performance measurement In general, performance is a measurement of how efficiently a software system completes its tasks. Performance is typically measured in response time, throughput (Hellerstein et al.,2007), or in some cases, utilization of computing resources (Cortellessa et al.,2011, p. 4). Response time is the time taken for a call in the system to traverse to some other part of the system and back. This is also sometimes called latency (Gunther,2011, p. 10), and in the context of database systems, the response time may be measured as the response time to the first or the last result item (Graefe,1993). In a broad perspective described in Fig. 1, the response time might be the time taken after the enduser sends a request to the software application (e.g., an online store), which passes the request to a DBMS, which returns a set of data to the software application, which finally presents the data to the end-user’s device. In database benchmarking, however, response time might be measured by running the benchmark on the same device the DBMS and the database reside, effectively eliminating inter-device-induced performance drawbacks such as network latency (Patounas et al.,2020; The Journal of Systems & Software 208 (2024) 111872 4 T. Taipalus Delis and Roussopoulos,1993) and firewalls, and mitigating the effects of other software running on the devices. Although DBMSs perform other tasks besides querying, querying is typically what is measured in DBMS performance testing (Dietrich et al.,1992). While response time is perhaps the least arduous performance metric to measure, it is not often enough for reliable measurement of transaction processing environments (Dietrich et al.,1992) (often dubbed online transaction processing, OLTP). That is, response time might be a metric better suited for long-running queries in decision support environments (often dubbed online analytical processing, OLAP), but as transaction processing environments often process a large number of concurrent transactions, response time alone might not reliably account for the effects of concurrent transactions, unless response time is measured as an average of multiple concurrent transactions. Performance can also be measured by throughput, i.e., how many transactions the DBMS can execute in a given time frame. Throughput is often expressed as transactions per second (Dietrich et al.,1992) and requires a more sophisticated approach, e.g., benchmarking software. Again, throughput may be measured either locally (i.e., using only the hardware the DBMS and the database reside on), or over a network in case the database is distributed. Alternatively, throughput may be measured by connecting the benchmarking software to the software application, which simulates the throughput of the whole database system by accounting for, e.g., network and the software application (e.g., Kumar and Grot,2022;Sundaresan et al.,2013). Such an approach arguably requires significantly more investment, but provides a holistic perspective on the performance of the whole system, also uncovering potential performance issues unrelated to the DBMS and the database. Finally, performance may be measured by resource utilization, either CPU time, I/O, memory allocation, or energy consumption (Graefe, 1993) in systems striving for energy-efficiency due to, e.g., limited battery power, or due to environmental concerns (Guo et al.,2022). In summary, we might consider the measurement of throughput a process that typically requires a simulation of some level, and the measurement of response time as an exact or approximated mathematical method. The former approach requires relatively high investments into the development of such simulations (Cortellessa et al.,2011, p. 142), while the latter often relies on a set of assumptions that do not necessarily reflect real-world scenarios due to inaccuracies in predicting what the real-world scenario ultimately is and how it can change. 3.2. Factors affecting performance Hardware: An intuitive factor in performance is the power of hardware (Osterhage,2013, p. 1), and while it is true that most of the local response time is attributed to time taken by CPU processing, memory and disk access, and software waiting for other tasks to complete (Cortellessa et al.,2011, p. 5), first investing in software performance rather than hardware performance is often more cost-effective. That being said, it is generally accepted that memory access is at least four orders of magnitude faster than disk access (e.g., Gunther,2011, p. 42). That is, if memory access takes minutes (nanoseconds), disk access takes months (milliseconds). These numbers are largely dependent on the speed of memory and the type of disk, but paint a picture of how zealously DBMS optimization strives to minimize disk access. Since memory is typically more expensive than disk storage, keeping the whole database in memory is often unfeasible. Additionally, the underlying hardware is important, as, e.g., some DBMSs have been shown to utilize multi-processor or multi-core environments more effectively than others (Tu et al.,2013). Intuitively, how well a DBMS can exploit parallelism affects the performance of query execution (Tallent and Mellor-Crummey,2009;Tözün et al.,2013). Ultimately, performance measurement is about gains or losses in percentages, not in, e.g., response times. Data models: Data models described in Section 2.2 have indirect effects on DBMS performance. Relational databases often follow design guidelines that strive to minimize redundancy to eliminate potential data anomalies caused by redundant data (Codd,1972,1975), and to minimize the need for storage space, which in turn typically causes queries to run slower due to a larger number of table joins. In contrast, different NoSQL data models — especially key–value, document, and wide-column — follow design guidelines according to which data structures are designed to efficiently satisfy predetermined business logic queries, with the elimination of redundant data being a secondary concern (Davoudian et al.,2018). It follows that because many NoSQL data structures are designed to serve queries, queries are typically simple (Dey et al.,2014), and their execution requires less computational resources than complex queries in relational databases. As discussed in Section 2.3, locking data objects (both on disk and in memory, and both primary data structures as well as indices), logging write operations, and how memory is managed by the DBMS all play a significant role in DBMS performance (Hellerstein et al.,2007;Stonebraker,2010). For example, preventing write operation-induced anomalies is a costly action, and the level of granularity of database locks presents significant considerations on write operation performance, which is largely dictated by the ratio of read and write operations. Distribution: Write operations in distributed configurations pose nontrivial challenges to both performance and data consistency (Delis and Roussopoulos,1993). In distributed database systems, effectively all transactions must choose either data consistency or data availability (Brewer,2012;Gilbert and Lynch,2002). The former guarantees that the data the end-user receives are not stale, with the cost of performance, while the latter guarantees to a degree that the end-user receives data faster, but with no guarantees that the dataset received is the most recent. The preferred approach is largely dictated by business logic. DBMS and OS parameters: Moving from data models and database system distribution to lower levels of abstraction, operating system (OS) and DBMS parameters and their interrelationships (e.g., page size) can have direct or indirect effects on performance (Dietrich et al., 1992). Additionally, DBMS parameters such as the amount of memory the DBMS is allowed to use for data processing is typically closely related to the amount of memory available. Furthermore, as a query is sent to the optimizer (cf. Fig. 1), it depends on the DBMS internals how efficiently the optimizer can select the most efficient physical operations to implement the query, and what physical operations are available to the optimizer in the first place (Chaudhuri,1998). For example, MySQL implemented only one physical operation for table joins until 2018,1limiting the number of options the optimizer could choose from. Regarding query optimization, the optimizers of RDBMSs in particular are relatively mature and can spot some unnecessary complications in queries, while overlooking others (Brass and Goldberg, 2006). Despite the benefits brought by the optimizers, some queries are inherently slow and can only be optimized through query rewrites. Physical database design: Last, but definitely not least, physical database design plays a key role in DBMS performance. It has been argued that performance bottlenecks are difficult to find in large systems (Ammons et al.,2004), and that efficiency is gained by focusing on the vital few areas instead of the trivial many (Juran and De Feo, 2010, p. 450). One of the most vital areas in database systems is physical design. In relational databases, efficient physical design is largely achieved through indices, and in NoSQL databases, typically through database distribution over computing nodes. In contrast to a holistic system overview, performance bottlenecks may be easier to find in queries, since many DBMSs provide detailed information on query execution (Fig. 2). PostgreSQL (Fig. 2(a)) lists the physical operations used to execute the query, which of the operations took the most time units, and which indices, if any, were used. For example, it can be seen in Fig. 2(a) that the sequential scan on line 12 accounted for 1https://dev.mysql.com/doc/refman/5.6/en/explain-output.html The Journal of Systems & Software 208 (2024) 111872 5 T. Taipalus Fig. 2. Query execution plans illustrating the physical operators such as hash join and seq scan chosen by the optimizer. approximately 94% of the execution time of the whole query (178 time units out of 189 ms), probably because the query fetched a large number of records from the database. The query could be optimized by, e.g., selecting a smaller number of records, and showing the results to the end-user by paging them, i.e., showing a subset of results first, and fetching more later if necessary. In NoSQL systems, the query optimizer plays a smaller role due to typically less expressive query languages (cf. Fig. 2(b)). Some NoSQL systems such as Cassandra do not permit the execution of queries that do not utilize the physical structures effectively. 3.3. Database performance benchmarks There are several database performance benchmarks available, each typically consisting of a sample database and a workload that simulates how the database could be used (Difallah et al.,2013;Qu et al., 2022b). The benchmarks usually measure the efficiency of querying while taking into account factors such as concurrency but disregarding other DBMS tasks such as efficiency in data structure definition or bulk loading (Dietrich et al.,1992). In the domain of relational databases, the Transaction Processing Council (TPC) benchmarks (e.g., Gray,1992) are perhaps the most utilized (Dreseler et al.,2020;Tözün et al.,2013), and test the throughput of the DBMS with various parameters. For example, the TPC-A benchmark simulates a database of a bank with four tables and with one transaction, the TPC-B benchmark a database of a wholesale supplier with nine tables and with five transactions, and the TPC-E benchmark a brokerage database with 33 tables and 12 transactions. All these benchmarks have the option for simulating strong consistency, and while TPC-A and TPC-B have transactions typical for transaction processing, TPC-E includes also decision support transactions (Tözün et al.,2013). TPC-A simulates human end-user thinking by waiting between transactions, as a human arguably would wait between clicks in an online bank. TPC-B, on the other hand, does not wait and can be used as a precursor for TPC-A in adjusting DBMS parameters (Dietrich et al.,1992). Alternatively to transaction processing, TPC-H benchmark measures the performance of a DBMS in decision support (Barata et al., 2015;Dreseler et al.,2020). In the more general DBMS domain, the Yahoo! Cloud Serving Benchmark (YCSB) is a framework for benchmarking transaction processing in systems with different data models and architectures (Cooper et al., 2010). Due to its extensibility, YCSB can be adapted to different NoSQL data models. YCSB contains different workloads, each with a different ratio of read and write operations. YCSB and its extensions such as YCSB+T typically utilize transactions which consist of single operations and do not enforce strong consistency (Qu et al.,2022b;Dey et al., 2014). The benchmarks described above are by no means an exhaustive list but cover the most popular benchmarks (cf. Section 2.1). Other benchmarks include LUBM (Guo et al.,2005), OLTP-Bench (Difallah et al.,2013), and JOB (Leis et al.,2015). Regardless of the data model and DBMS, transaction processing benchmarks have typically been the de facto method of comparing different DBMSs and hardware (Tözün et al.,2013). Further reading on performance: for readers interested in physical database operations and query execution from a performance perspective, Graefe (1993) provides an in-depth, DBMS-independent survey. For more information on physical database design, especially indices and how they work, the book by Lightstone et al. (2010) is a detailed and descriptive source. For a practical and concise guide on SQL query optimization, we point readers towards Winand’s (2012) book. Regarding NoSQL DBMS optimization, we suggest referring to the manual of the DBMS of your choice, and always making sure that the source of information is current, as NoSQL systems tend to evolve rapidly. The Journal of Systems & Software 208 (2024) 111872 6 T. Taipalus Fig. 3. The study selection process; the numbers refer to the number of primary studies selected in each stage of the process. Table 1 Search strings. Database Search string ACM DL [Abstract: performance] AND [Abstract: comparison] AND [[Abstract: database] OR [Abstract: dbms]] AND [Publication Date: (01/01/2000 TO 03/31/2022)] IEEE Xplore (‘‘Abstract’’:performance AND ‘‘Abstract’’:comparison AND (‘‘Abstract’’:database OR ‘‘Abstract’’:dbms)) ScienceDirect Title, abstract, keywords: performance AND comparison AND (database OR dbms) Google Scholar database performance comparison 4. Study selection 4.1. Process and criteria The DBMSs in this study were selected based on the selected primary studies. That is, we did not choose, e.g., the most popular DBMSs to include, but reported the DBMSs yielded by the primary studies. The results herein may be considered the most popular DBMSs in terms of benchmarking reported in scientific literature. Fig. 3 describes the primary study selection process starting from ACM Digital Library, IEEE Xplore, and ScienceDirect, complemented by subsequent Google Scholar searches. The search strings are detailed in Table 1. To account for potentially missing relevant studies, we conducted three rounds of backward snowballing (i.e., following the lists of references in selected studies), until snowballing revealed no additional studies. A total of 117 primary studies comparing DBMS performance were selected. Table 2 describes our inclusion criteria applied in the primary study selection. The first four criteria are related to bibliographic details, while the last three criteria are concerned with article focus and content. Regarding criterion #3, we excluded academic theses and dissertations (e.g., Coates,2009) due to the fact that they are typically not peer-reviewed. We also excluded white and gray literature for the same reason, and because those studies are often written or published by partial parties, e.g., DBMS vendors. We only selected studies that compared query (i.e., retrieving or modifying data) execution performance, not regarding e.g., database replication performance (Elnikety et al.,2006) or performance of different join operations (Kim and Patel,2010). We also excluded studies that compared a single DBMS performance in different configurations such as hardware, replication strategy, database structure, or query language (Holzschuher and Peinl,2013) and studies that compared a DBMS with different data-related platforms (Purbo et al.,2020). Studies that reported pseudonymized DBMS names were also excluded. Finally, we only included studies that reported results based on at least seemingly objective metrics and empirical results. That is, studies simply stating the opinions of the authors such as ‘‘based on our experiences, we believe MySQL is faster than SQL Server’’ were not considered. 4.2. Selected studies The selected 117 primary studies compared the performance of a total of 44 different DBMSs. We categorized these DBMSs into three top-level types defined and discussed in Section 2.2: RDBMSs, NoSQL systems, and NewSQL systems. Five DBMSs not clearly pertaining to any of these three categories were categorized under other systems (Table 3). It is worth noting that these DBMS types are not always clear-cut due to the lack of specificity and changing nature of the definitions, and should be interpreted as merely means to compartmentalize the results of this study into a more readable form. Five selected primary studies did not report results implying the performance of one DBMS over another (Padhy and Kumaran,2019;Schmid et al.,2015b; KumarDwivedi et al.,2012;Faraj et al.,2014;Jing et al.,2009). Fig. 4 shows the distribution of publication years and the types of DBMSs discussed in the selected studies. Although our criteria allowed for studies from the year 2000, the first studies selected were published in 2008. The figure shows that generally, there is a somewhat constant number of DBMS performance comparison studies each year. It is worth noting that one study may pertain to several types of DBMSs. 5. Performance comparison results The most popular DBMS performance comparisons compared one or several RDBMSs to one or several NoSQL systems, one NoSQL system to another NoSQL system, or one RDBMS to another RDBMS, respectively. A total of 48 studies compared solely read performance, while 6 studies compared solely write performance. The rest of the studies compared both read and write performance, with the exception of two studies (Cheng et al.,2019;Nepaliya and Gupta,2015) which were unclear whether they compared write operations. All comparisons and their results per DBMS type are summarized in Fig. 5. Fig. 6 presents an overview of which DBMSs and DBMS types the primary studies compared. The figure perhaps conveys how both other and NewSQL systems are typically compared within their respective DBMS type groups, while RDBMS and NoSQL systems are both The Journal of Systems & Software 208 (2024) 111872 7 T. Taipalus Table 2 Primary study selection criteria. # Inclusion criterion 1 Article is written in English. 2 Full article can be accessed. 3 Article is published in a scientific journal, or conference or workshop proceedings. 4 Article is published between 2000 and March 2022. 5 Article focus is on query language statement execution performance comparison. 6 Article focus is on comparing the performance of two or more different DBMSs. 7 Article is based on at least seemingly objective metrics. Table 3 DBMSs discussed in this study divided into four types. DBMS type DBMSs RDBMS Access, Azure SQL, Interbase, DB/2, H2, Hive, MariaDB, MySQL Cluster, MySQL, Oracle Database, PostgreSQL, PostgresXL, SQL Server, SQLite NoSQL ArangoDB, Azure Document Database, Cassandra, Couchbase, CouchDB, Elasticsearch, Firebase, HBase, Hypertable, memcached, MongoDB, Neo4J, Oracle NoSQL, OrientDB, RavenDB, Redis, RethinkDB, Riak, Scalaris, Tarantool, Voldemort NewSQL CockroachDB, MemSQL (now known as SingleStoreDB), NuoDB, VoltDB Other BlazingSQL, Caché, Db4o, OmniSciDB, PG-Strom Fig. 4. The number of publications by publication year and DBMS type; the year 2022 was only considered until March. Fig. 5. DBMS performance comparisons overview; a directed edge from node ato node brepresents the number of studies according to which a system of type aoutperformed a system or systems of type bin (r)ead and (w)rite operations, e.g., a NoSQL system outperformed a NewSQL system in read operations in one study, and in write operations in one study; thicker edges visualize the most popular comparisons. The Journal of Systems & Software 208 (2024) 111872 8 T. Taipalus Fig. 6. An overview of read operation performance comparisons between NoSQL systems (green, upper right), NewSQL systems (yellow, lower right), RDBMSs (red, lower left), and other systems (blue, upper left); a clockwise turning edge from node ato node bdepicts node aoutperforming node b, and the color of the edge corresponds to the type of the outperforming node, e.g., Caché outperforms PostgreSQL according to one or several studies; the size of a node represents out-degree, i.e., larger nodes have outperformed more systems than smaller nodes. compared within their respective groups as well as with each other. Additionally, the size of the nodes such as MongoDB, Redis, Cassandra, and MySQL show that these DBMSs typically outperform the DBMSs they are compared to. Due to their length, the detailed results from the primary study comparisons are presented in the Appendix, which includes tables detailing which DBMSs outperformed which. Regarding the benchmarks defined in earlier scientific literature, the most popular was YCSB, which was utilized by 15 primary studies (approximately 13%) (Abramova and Bernardino,2013;Abramova et al., 2014a,b;Gandini et al.,2014;Schreiner et al.,2019;Seghier and Kazar, 2021;Yassien and Desouky,2016;Abubakar et al.,2014;Kashyap et al.,2013;Swaminathan and Elmasri,2016;Tang and Fan,2016; Klein et al.,2015;Araujo et al.,2021;Hendawi et al.,2018;Rabl et al., 2012). The second most popular benchmark was the TPC-H benchmark and its variations, utilized by five primary studies (4%) (Almeida et al., 2015;Fotache and Hrubaru,2016;Oliveira and Bernardino,2017;Suh et al.,2022;Vershinin and Mustafina,2021). It is worth noting, though, that two of the studies (Oliveira and Bernardino,2017;Vershinin and Mustafina,2021) seemed to have executed the queries of TPC-H, instead of running the benchmark and accounting for, e.g., the effects of concurrent transactions. One primary study utilized the OLTP-Bench benchmark (Tongkaw and Tongkaw,2016), one the LUBM benchmark (Franke et al.,2013), and one, in addition to TPC-H, the JOB benchmark (Suh et al.,2022). Regarding the benchmarks formulated by the primary study authors, 25 primary studies (21%) reported using ad hoc queries instead of earlier defined benchmarks to compare the performance of DBMSs. These queries were defined verbatim in the primary studies. In contrast, 70 of the primary studies (60%) compared DBMS performance using undisclosed ad hoc queries, likely formulated by the study authors. In other words, 22 primary studies (19%) used some type of earlier defined database benchmarking suite. The performance tests of these 22 primary studies and what aspects of the environment they reported are detailed in Table 4. The Journal of Systems & Software 208 (2024) 111872 15 T. Taipalus Hajjaji, Yosra, Farah, Imed Riadh, 2018. Performance investigation of selected NoSQL databases for massive remote sensing image data storage. In: 2018 4th International Conference on Advanced Technologies for Signal and Image Processing. ATSIP, IEEE. Hassan, Mahmudul, Bansal, Srividya K., 2018. Semantic data querying over NoSQL databases with Apache Spark. In: 2018 IEEE International Conference on Information Reuse and Integration. IRI, IEEE, Salt Lake City, UT, pp. 364–371. Ilić, Miloš, Kopanja, Lazar, Zlatković, Dragan, Trajković, Milica, Ćurguz, Dejana, 2021. Microsoft SQL Server and Oracle: Comparative performance analysis. In: Book of Proceedings of the 7th International Conference Knowledge Management. Jaiswal, Garima, 2013. Comparative analysis of relational and graph databases. IOSR J. Eng. 03 (08), 25–27. Jandaeng, Chanankorn, 2015. Comparison of RDBMS and document oriented database in audit log analysis. In: 2015 7th International Conference on Information Technology and Electrical Engineering. ICITEE, IEEE, Chiang Mai, Thailand, pp. 332–336. Jose, Benymol, Abraham, Sajimon, 2020. Performance analysis of NoSQL and relational databases with MongoDB and MySQL. Mater. Today: Proc. 24, 2036–2043. Jung, Min-Gyue, Youn, Seon-A., Bae, Jayon, Choi, Yong-Lak, 2015. A study on data input and output performance comparison of MongoDB and PostgreSQL in the big data environment. In: 2015 8th International Conference on Database Theory and Application. DTA, IEEE, Jeju Island, South Korea, pp. 14–17. Kabakus, Abdullah Talha, Kara, Resul, 2017. A performance evaluation of in-memory databases. J. King Saud Univ. - Comput. Inf. Sci. 29 (4), 520–525. Kaur, Karambir, Sachdeva, Monika, 2017. Performance evaluation of NewSQL databases. In: 2017 International Conference on Inventive Systems and Control. ICISC, IEEE. Khan, Wisal, Ahmad, Waqas, Luo, Bin, Ahmed, Ejaz, 2019. SQL Database with physical database tuning technique and NoSQL graph database comparisons. In: 2019 IEEE 3rd Information Technology, Networking, Electronic and Automation Control Conference. ITNEC, IEEE, Chengdu, China, pp. 110–116. Khan, Wisal, ahmed, Ejaz, Shahzad, Waseem, 2017. Predictive performance comparison analysis of relational & NoSQL graph databases. Int. J. Adv. Comput. Sci. Appl. 8 (5). Khanna, Deepti, Aggarwal, V.B., Director, J.I.M.S., Dave, India Meenu, 2018. Performance analysis for select, project and join operations of Oracle, My-SQL and microsoft access DBMSS. Int. J. Comput. Eng. Technol. (IJCET). Kumar, Lokesh, Rajawat, Shalini, Joshi, Krati, 2015. Comparative analysis of NoSQL (MongoDB) with MySQL database. Int. J. Modern Trends Eng. Res. 2 (5), 120–127. Kumar, K.B. Sundhara, Srividya, Mohanavalli, S., 2017. A performance comparison of document oriented NoSQL databases. In: 2017 International Conference on Computer, Communication and Signal Processing. ICCCSP, IEEE, Chennai, India, pp. 1–6. Laksono, Dany, 2018. Testing spatial data deliverance in SQL and NoSQL database using nodejs fullstack web app. In: 2018 4th International Conference on Science and Technology. ICST, IEEE, Yogyakarta, pp. 1–5. Lazarska, Malgorzata, Siedlecka-Lamch, Olga, 2019. Comparative study of relational and graph databases. In: 2019 IEEE 15th International Scientific Conference on Informatics. IEEE, pp. 000363–000370. Lee, Chao-Hsien, Shih, Zhe-Wei, 2018. A comparison of NoSQL and SQL databases over the hadoop and spark cloud platforms using machine learning algorithms. In: 2018 IEEE International Conference on Consumer Electronics-Taiwan. ICCE-TW, IEEE, Taichung, pp. 1–2. Li, Yishan, Manoharan, Sathiamoorthy, 2013. A performance comparison of SQL and NoSQL databases. In: 2013 IEEE Pacific Rim Conference on Communications, Computers and Signal Processing. PACRIM, IEEE. Lorincz, Josip, Huljic, Vlatka, Begusic, Dinko, 2020. Transforming product catalog relational into graph database: A performance comparison. In: 2020 43rd International Convention on Information, Communication and Electronic Technology. MIPRO, IEEE, Opatija, Croatia, pp. 523–528. Magdum, Junaid, Barhate, Rahul, 2018. Performance analysis of DML operations on NoSQL databases for streaming data. In: 2018 Fourth International Conference on Computing Communication Control and Automation. ICCUBEA, IEEE, Pune, India, pp. 1–6. Mahmood, Khalid, Orsborn, Kjell, Risch, Tore, 2019. Comparison of NoSQL datastores for large scale data stream log analytics. In: 2019 IEEE International Conference on Smart Computing. SMARTCOMP, IEEE, Washington, DC, USA, pp. 478–480. Makris, Antonios, Tserpes, Konstantinos, Spiliopoulos, Giannis, Anagnostopoulos, Dimosthenis, 2019. Performance evaluation of MongoDB and PostgreSQL for spatio-temporal data. In: EDBT/ICDT Workshops. Makris, Antonios, Tserpes, Konstantinos, Spiliopoulos, Giannis, Zissis, Dimitrios, Anagnostopoulos, Dimosthenis, 2021. MongoDB Vs PostgreSQL: A comparative study on performance aspects. GeoInformatica 25 (2), 243–268. Marrero, Luciano, Olsowy, Verena, Tesone, Fernando, Thomas, Pablo, Delia, Lisandro, Pesado, Patricia, 2020. Performance analysis in NoSQL databases, relational databases and NoSQL databases as a service in the cloud. In: Argentine Congress of Computer Science. Springer, pp. 157–170. Mavrogiorgos, Konstanitnos, Kiourtis, Athanasios, Mavrogiorgou, Argyro, Kyriazis, Dimosthenis, 2021. A comparative study of MongoDB, ArangoDB and CouchDB for big data storage. In: 2021 5th International Conference on Cloud and Big Data Computing. ICCBDC, ACM, Liverpool United Kingdom, pp. 8–14. Murazza, Muh. Rafif, Nurwidyantoro, Arif, 2016. Cassandra and SQL database comparison for near real-time Twitter data warehouse. In: 2016 International Seminar on Intelligent Technology and Its Applications. ISITIA, IEEE, Lombok, Indonesia, pp. 195–200. Nyati, Suyog S., Pawar, Shivanand, Ingle, Rajesh, 2013. Performance evaluation of unstructured NoSQL data over distributed framework. In: 2013 International Conference on Advances in Computing, Communications and Informatics. ICACCI, IEEE, Mysore, pp. 1623–1627. Ohyver, Margaretha, Moniaga, Jurike V., Sungkawa, Iwa, Subagyo, Bonifasius Edwin, Chandra, Ian Argus, 2019. The comparison firebase realtime database and MySQL database performance using wilcoxon signed-rank test. Procedia Comput. Sci. 157, 396–405. Parker, Zachary, Poe, Scott, Vrbsky, Susan V., 2013. Comparing NoSQL MongoDB to an SQL DB. In: Proceedings of the 51st ACM Southeast Conference on - ACMSE ’13. ACM Press, Savannah, Georgia, p. 1. Patil, Mayur M., Hanni, Akkamahadevi, Tejeshwar, C.H., Patil, Priyadarshini, 2017. A qualitative analysis of the performance of MongoDB vs MySQL database based on insertion and retriewal operations using a web/android application to explore load balancing — sharding in MongoDB and its advantages. In: 2017 International Conference on I-SMAC (IoT in Social, Mobile, Analytics and Cloud). I-SMAC, IEEE. Pereira, Diogo Augusto, Ourique de Morais, Wagner, Pignaton de Freitas, Edison, 2018. NoSQL real-time database performance comparison. Int. J. Parallel Emergent Distrib. Syst. 33 (2), 144–156. Poljak, R., Poscic, P., Jaksic, D., 2017. Comparative analysis of the selected relational database management systems. In: 2017 40th International Convention on Information and Communication Technology, Electronics and Microelectronics. MIPRO, IEEE, Opatija, Croatia, pp. 1496–1500. Puangsaijai, Wittawat, Puntheeranurak, Sutheera, 2017. A comparative study of relational database and key-value database for big data applications. In: 2017 International Electrical Engineering Congress. IEECON, IEEE, Pattaya, Thailand, pp. 1–4. Rafamantanantsoa, Fontaine, Laha, Maherindefo, 2018. Analysis and neural networks modeling of web server performances using MySQL and PostgreSQL. Commun. Network 10 (04), 142–151. Rautmare, Sharvari, Bhalerao, D.M., 2016. MySQL and NoSQL database comparison for IoT application. In: 2016 IEEE International Conference on Advances in Computer Applications. ICACA, IEEE, Coimbatore, pp. 235–238. Ribeiro, Jardel, Henrique, Jonas, Ribeiro, Rodrigo, Neto, Rosalvo, 2017. NoSQL vs relational database: A comparative study about the generation of the most frequent N-grams. In: 2017 4th International Conference on Systems and Informatics. ICSAI, IEEE, Hangzhou, pp. 1568–1572. Roopak, K.E., Rao, K.S. Swati, Ritesh, S., Chickerur, Satyadhyan, 2013. Performance comparison of relational database with object database (DB4o). In: 2013 5th International Conference on Computational Intelligence and Communication Networks. IEEE. Saikia, Amlanjyoti, Joy, Sherin, Dolma, Dhondup, Mary R, Roseline, 2015. Comparative performance analysis of MySQL and SQL server relational database management systems in windows environment. IJARCCE 160–164. Samanta, Ashis Kumar, Sarkar, Bidut Biman, Chaki, Nabendu, 2018. Query performance analysis of NoSQL and big data. In: 2018 Fourth International Conference on Research in Computational Intelligence and Communication Networks. ICRCICN, IEEE. Schmid, Stephan, Galicz, Eszter, Reinhardt, Wolfgang, 2015a. Performance investigation of selected SQL and NoSQL databases. In: Proceedings of the AGILE. pp. 1–5. Seda, Pavel, Hosek, Jiri, Masek, Pavel, Pokorny, Jiri, 2018. Performance testing of NoSQL and RDBMS for storing big data in e-applications. In: 2018 3rd International Conference on Intelligent Green Building and Smart Grid. IGBSG, IEEE. Sharma, Monika, Sharma, Vishal Deep, Bundele, Mahesh M., 2018. Performance analysis of RDBMS and No SQL databases: Postgresql, MongoDB and Neo4j. In: 2018 3rd International Conference and Workshops on Recent Advances and Innovations in Engineering. ICRAIE, IEEE, Jaipur, India, pp. 1–5. Sholichah, Rahmatian Jayanty, Imrona, Mahmud, Alamsyah, Andry, 2020. Performance analysis of Neo4j and MySQL databases using public policies decision making data. In: 2020 7th International Conference on Information Technology, Computer, and Electrical Engineering. ICITACEE, IEEE, Semarang, Indonesia, pp. 152–157. Sirish Shetty, B., Akshay, Kc, 2019. Performance analysis of queries in RDBMS vs NoSQL. In: 2019 2nd International Conference on Intelligent Computing, Instrumentation and Control Technologies. ICICICT, IEEE, Kannur,Kerala, India, pp. 1283–1286. Stancu-Mara, Sorin, Baumann, Peter, 2008. A comparative benchmark of large objects in relational databases. In: Proceedings of the 2008 International Symposium on Database Engineering & Applications - IDEAS ’08. ACM Press, Coimbra, Portugal, p. 277. Truica, Ciprian-Octavian, Radulescu, Florin, Boicea, Alexandru, Bucur, Ion, 2015. Performance evaluation for CRUD operations in asynchronously replicated document oriented database. In: 2015 20th International Conference on Control Systems and Computer Science. IEEE, Bucharest, Romania, pp. 191–196. van der Veen, Jan Sipke, van der Waaij, Bram, Meijer, Robert J., 2012. Sensor data storage performance: SQL or NoSQL, physical or virtual. In: 2012 IEEE Fifth International Conference on Cloud Computing. IEEE. The Journal of Systems & Software 208 (2024) 111872 16 T. Taipalus Vicknair, Chad, Macias, Michael, Zhao, Zhendong, Nan, Xiaofei, Chen, Yixin, Wilkins, Dawn, 2010. A comparison of a graph database and a relational database: A data provenance perspective. In: Proceedings of the 48th Annual Southeast Regional Conference on - ACM SE ’10. ACM Press, Oxford, Mississippi, p. 1. Wei-ping, Zhu, Ming-xin, Li, Huan, Chen, 2011. Using MongoDB to implement textbook management system instead of MySQL. In: 2011 IEEE 3rd International Conference on Communication Software and Networks. IEEE. Wiseso, Linggis Galih, Imrona, Mahmud, Alamsyah, Andry, 2020. Performance analysis of Neo4j, MongoDB, and PostgreSQL on 2019 national election big data management database. In: 2020 6th International Conference on Science in Information Technology. ICSITech, IEEE. Xu, Wei, Zhou, Zhonghua, Zhou, Hong, Zhang, Wu, Xie, Jiang, 2014. MongoDB improves big data analysis performance on electric health record system. In: Communications in Computer and Information Science. Springer Berlin Heidelberg, pp. 350–357. Yinfeng Wang, Guiquan Zhong, Lin Kun, Longxiang Wang, Huang Kai, Fuliang Guo, Chengzhe Liu, Xiaoshe Dong, 2015. The performance survey of in memory database. In: 2015 IEEE 21st International Conference on Parallel and Distributed Systems. ICPADS, IEEE, Melbourne, VIC, pp. 815–820. Zhou, Zhonghai, Zhou, Bin, Li, Wenwen, Griglak, Brian, Caiseda, Carmen, Huang, Qunying, 2009. Evaluating query performance on object-relational spatial databases. In: 2009 2nd IEEE International Conference on Computer Science and Information Technology. IEEE.