Performance Benchmarking on
Many-to-Many (M:N) Relationships
with Variations in Indexing and
Sizes of Object Databases

RQL Queries with Variations in Indexing and Object Database Sizes

12 Object Databases Tested in PostgreSQL

The search conditions used for performance testing of the object databases described above are shown in the image below.

In all tests, the same search conditions are used, which searches M1 class objects from the database. The red X marks indicate the classes N3, M4, and S4, where search conditions are applied to the value fields within the data structure of class M1. The paths from the root class M1 to the search condition classes are shown with red dashed lines.

The found M1 objects are returned as complete data structures, including all objects that are reachable from the M1 root class. This of course includes classes N3, M4, and S4, which have the value search conditions, but also objects from classes M2, M3, and N4, which are reachable from class M1. All reachable classes from class M1 are marked with blue X marks.

Blue arrow relationships between classes are M:N (many-to-many). All M:N relationships have 1-3 child objects at random.

Black arrow relationships between classes are M:1 (many-to-one).

The search conditons for the three value fields in classes N3 (value=976), S4 (value=50) and M4 (value=770) are represented as a RQL query expression below.

(m2.n3.value = 976, m2.m3.s4.value = 50, m2.n3.m4.value = 770)                        

The Java source code that executes the RQL query expression for the class M1 in the object database is shown below.

//Variable rdb is the opened RootDB object database.
//RootStore object is created for the M1 class.
RootStore r2 = rdb.getRootStore("fi.rootrql.ex100Knx.M1");
Query query =  Query.createQuery()
        		.addQ1Query(r2,
                    "(m2.n3.value = 976," +
                    " m2.m3.s4.value = 50," +
                    " m2.n3.m4.value = 770"
                    + ")");
List<M1> list = query.queryQ1Objects();
rdb.close();

An example of query results is shown below, containing the complete data structures of the retrieved four M1 objects. Note that, for example, the expanded [0]=M2 object has a list of three M3 objects in its field m3 and only the expanded object [2]=M3 has the searched value 50 in its s4=S4 object. Thus, the lists may contain objects that do not match the search criteria, but are included in the results because the returned M1 objects contain their complete data structures.

Query Result Example: The four found M1 objects are returned in a list, with the first M1 object expanded to display the value fields where the search conditions were applied.

The twelve object databases with variations in indexing and object counts were all tested using the same query described above. The results categorized by database size and indexing strategy are presented in the table below.

Note the column 'MariaDB/PostgreSQL Search Coeff.,' which compares the values in the 'Search' column to those provided by the MariaDB database.

The number of M1 objects found is 4 in the smallest databases with 12 x 100K objects. The number of objects found increases with the size of the database, so that a database with 12 x 2M objects has 80 matches for the query conditions.

The focus of the results is of course on execution times and their changes depending on the size of the database and the indexing used. Three execution times are shown, which are the search time of the query (Search), the creation time of found M1 objects (Create Objects), and the total time (Total) summing up these two times. The results show that the performance can be evaluated solely on the basis of the search time of the query (Search), since the object creation time (Create Objects) per result object seems to be approximately the same in all cases.

However, PostgreSQL's performance can be assessed and compared to the results of the same tests conducted on MariaDB, which are presented in a similar table at this link. To facilitate comparison, the PostgreSQL results table includes an additional column, MariaDB/PostgreSQL Search Coeff., which quantifies the relative performance of searches in PostgreSQL compared to MariaDB. A value greater than 1 indicates that PostgreSQL performs better, while a value less than 1 signifies that MariaDB is faster. For example, a coefficient of 2.0 means that PostgreSQL executes the same search twice as fast as MariaDB, whereas a coefficient of 0.5 indicates that MariaDB is twice as fast as PostgreSQL.

According to the results, MariaDB outperforms PostgreSQL when no relationships are indexed, specifically in tests using the none and value fields indexing strategies. MariaDB demonstrates greater efficiency, with a MariaDB/PostgreSQL Search Coefficient ranging from 0.41 to 0.81, corresponding to a 2.44× to 1.23× faster search time compared to PostgreSQL.

PostgreSQL outperforms MariaDB when indexing strategies include relationships, specifically in the all relations and value fields, all relations strategies. The highest MariaDB/PostgreSQL Search Coefficient indicates a 2.92× faster search time compared to MariaDB, and all but one coefficient are substantially greater than one, highlighting PostgreSQL’s significant efficiency in these scenarios.

Summary

PostgreSQL appears to be a superior database platform for RootDB compared to MariaDB when indexing strategies that include relationships are applied. This advantage remains consistent regardless of database size. PostgreSQL's superior performance is particularly evident in its ability to write objects with complex indexes at remarkable speed - approximately five times faster than MariaDB.

RootDB demonstrates a significant advantage in that, even without indexing, query execution times scale roughly linearly with database size, even when queries traverse multiple many-to-many relationships. This near-linear growth ensures that object database performance remains predictable and reliable without indexing. Although query times in large databases can become considerably longer, there is no risk of queries freezing due to excessive complexity.

Naturally, the complexity of the object model and the number of relationships within the database influence execution time. However, indexing relationships alone already yields significant performance improvements. By additionally indexing the fields referenced in queries, RootDB can meet even the most demanding requirements for fast query execution in large databases.