Performance Benchmarking of
PostgreSQL 17.5-1
Using a Java Object Model

RQL Queries with Variations in Indexing and Object Database Sizes

12 Object Databases Tested on 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.

Cold Cache Test Scenario

Many database systems can accelerate subsequent searches if the same, or even partially overlapping, data has already been retrieved during a previous query. Therefore, the results shown below represent a cold start scenario, where the database service has just been restarted and no prior data is cached. In other words, from a database performance standpoint, the search results are expected to reflect the longest possible execution times.

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.

No Indexes (none)

The query execution times (Search) are 9.331, 67.060, and 139.121 seconds for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). Although these durations are long for interactive applications, they are good, possibly even very good, considering that no indexing was used. In scenarios where the primary objective is to maximize data insertion efficiency and minimize overhead, leaving indexes out is not only acceptable but can also be advantageous.

A notable strength of RootDB is its ability to maintain linear query execution time growth relative to database size, even without indexing and even when traversing complex many-to-many relationships. This linear scaling behavior ensures that query performance remains predictable and reliable, even with very large databases. Although search times can become lengthy, there is no risk of query execution freezing or crashing.

Naturally, the complexity of the object model and the number of relationships have a significant impact on execution time. However, even with highly complex structures, RootDB is capable of successfully executing queries, regardless of whether indexing is used.

Indexes of Value Fields (value fields)

An effective way to improve database query performance is to apply indexing to all value fields in the object model. However, indexing every value field is not always feasible, as it can significantly slow down data insertion and maintenance due to the overhead of managing multiple indexes and increased memory consumption. Fortunately, it is often possible to identify the most frequently queried fields during the database design phase and limit indexing to those fields only.

The query execution times (Search) are 4.074, 21.912, and 42.101 seconds for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). Indexing only the value fields is sufficient to substantially improve query performance, reducing execution times to approximately one third of those observed in the unindexed case. When this improvement is combined with the excellent write performance of approximately 18,000 objects per second, which remains consistent regardless of database size, the overall result can be considered very strong.

Indexes of All Relations (all relations)

With smaller numbers of objects, indexing all relationships improves query performance more effectively than indexing only value fields. However, as the number of objects increases, the situation reverses, since value-based searches must rely on sequential scanning when value fields are not indexed. The query execution times (Search) are 2.479, 38.405, and 74.882 seconds for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). Once again, query execution time increases approximately linearly with the growth of the database size.

Write performance remains consistent at approximately 6,000 objects per second, even when value fields are included in the indexing. Therefore, with larger numbers of objects, it appears beneficial to index both value fields and relationships to achieve balanced performance for both reading and writing.

Indexes of Value Fields and All Relations (value fields, all relations)

The best overall database search performance is naturally achieved when both value fields and all relationships are indexed. The query execution times (Search) are 0.686, 5.215, and 9.652 seconds for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). As with all other query configurations, execution time increases approximately linearly with the growth of the database size.

When evaluating the quality of these query results, execution times should be considered in relation to the size of the database involved. A query time of 9.652 seconds may seem long for an interactive application, but when scaled to a database containing 24 million objects with multiple many to many relationships, it can be considered acceptable or even very good. At the other end, a query time well under one second with 1.2 million objects is more than sufficient for interactive use.

It is also worth emphasizing that the query results consist of complete objects of class M1. Each retrieved M1 object includes its entire data structure, containing all objects that are reachable from the M1 root. This level of completeness is a native feature of object databases and is difficult to replicate using traditional relational databases and the SQL language, where achieving similar results typically requires complex and costly programming effort.

Warm Cache Test Scenario

When a query is executed for the first time, the database engine must:

• Read data blocks from disk into memory,

• Possibly parse and optimize the query,

• Execute the query by scanning or using indexes,

• And build a result set.

On subsequent executions, if the same data pages are still in the buffer pool (i.e., cached in memory), the system can:

• Bypass disk I/O entirely for those blocks,

• Access data directly from memory,

• Possibly reuse compiled query plans or intermediate results.

The situation described above, where the same database search is executed again immediately, represents a warm cache test scenario. In such cases, it is very likely that execution times will improve significantly compared to the initial run, because data and index pages remain in memory. The table below presents the results of the previous cold cache tests when the same queries are re-executed, and therefore reflect execution times in a warm cache situation. Distinguishing between cold and warm cache performance is essential in benchmarking, as it reveals how well the database behaves both in first-time access scenarios and in repeated or steady-state workloads.

When comparing warm and cold cache results, execution times are significantly improved for all database queries except those using the value-only indexing strategy (value fields). This is because value indexing helps locate root objects quickly but provides no support for resolving the full object structure, which apparently remains the dominant cost in query execution and is not included in the cache by the first run. Below, the effects of warm cache conditions on query execution times are briefly reviewed for each of the four indexing strategies.

No Indexes (none)

The query execution times (Search) are 65.81%, 45.95%, and 43.54% of the corresponding cold cache times for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). As the database size increases, the relative benefit of warm cache also increases. However, the improvement from 45.95% to 43.54% is only marginal, suggesting that 43.54% may be close to the maximum achievable warm cache performance in this configuration.

As is typical for RootDB, the execution time increases linearly with the size of the database. This is clearly demonstrated by the ratio of the query times for the 12 × 1M and 12 × 2M databases, which is almost exactly two to one, matching the relative sizes of the databases.

Indexes of Value Fields (value fields)

The warm and cold cache execution times are nearly identical, indicating that value indexing offers no real performance benefit when the same data is queried again immediately. This is somewhat unexpected, given that the no-indexing strategy shows a clear improvement under warm cache conditions. Since both approaches perform the same relationship resolution without the aid of relationship indexing, it raises the question of why value indexing fails to make more effective use of cached data.

Although both the no-indexing (none) and value-only (value fields) indexing strategies ultimately resolve the same object data through relationship traversal, only the no-indexing case shows a significant benefit from warm cache. This is somewhat unexpected, as the same records are accessed to construct the full object structures in both cases. It is not entirely clear why value-only indexing does not result in similar caching behavior, but it may relate to differences in how PostgreSQL internally handles memory and cache management based on query structure or access patterns. This discrepancy highlights a need for further investigation to fully understand the underlying cause.

Indexes of All Relations (all relations)

The query execution times (Search) are 45.58%, 26.42%, and 26.63% of the corresponding cold cache times for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). As the database size increases, the relative benefit of warm cache initially grows. However, the improvement appears to reach its maximum at the 12 × 1M size, since there is no further reduction in execution time when moving from 12 × 1M to 12 × 2M.

Indexes of Value Fields and All Relations (value fields, all relations)

The query execution times (Search) are 40.82%, 46.92%, and 45.99% of the corresponding cold cache times for databases containing 12 × 100K, 12 × 1M, and 12 × 2M objects respectively (where K = thousand, M = million). As the database size increases, the relative benefit of warm cache appears to decrease. However, this decline seems to level off at the 12 × 1M size, as there is no further change in performance when moving from 12 × 1M to 12 × 2M.

The observed leveling off in warm cache benefit at the 12 × 1M database size may be explained by the increasing size of the combined index structures for both value fields and relationships. While smaller databases likely allow all index pages to remain in memory, larger databases may exceed available cache capacity, requiring parts of the indexes to be reloaded from disk during query execution. As a result, the relative advantage of warm cache diminishes. This behavior suggests that beyond a certain point, the efficiency of caching is limited not by the presence of indexes, but by their ability to remain resident in memory.

RootDB on PostgreSQL: Test Summary

PostgreSQL has emerged as the most effective database platform for RootDB among all systems tested to date. It is capable of writing nearly 20,000 objects per second when no indexing is used or when only value fields are indexed, which is an outstanding result. Even when indexing is extended to cover all relationships in the object model, write performance remains strong, reaching approximately 6,000 objects per second.

When assessing PostgreSQL query performance, execution times should be evaluated relative to the size and complexity of the database. For example, a query time of 9.652 seconds may appear long in isolation, but for a database containing 24 million objects with multiple many to many relationships, it is a reasonable or even impressive result. At the smaller end, query times well under one second for 1.2 million objects are more than adequate for interactive applications.

It is also important to emphasize that the query results return complete objects according to the object model. Each root object includes its full data structure, containing all objects that are reachable from it. This level of completeness is a built-in feature of object databases and is difficult to replicate with traditional relational databases and SQL, where such results typically require complex and labor intensive programming.

Overall, PostgreSQL has proven to be a remarkably efficient and reliable platform for RootDB object databases. Its write performance stands out clearly among the systems tested, remaining stable regardless of database size or growth. Furthermore, PostgreSQL handles complex object models, including those with many to many relationships, with high efficiency even when indexing is applied. All of this is complemented by PostgreSQL's consistently predictable query performance, which is effectively utilized by RootDB's query execution algorithms. As a result, query times increase approximately linearly with database size, which reflects one of the key goals in the development of RootDB.