Article from Matthias Schmid, University of Passau Germany "An approach to efficiently storing property graphs in relational databases"

[ThomasMic]

Hello, reposting there because old Discord seems to be outdated and didn't find an appropriate channel on the new one, maybe some of you will find this (main idea is redundant storage to optimize specific type of queries ) useful...

Followed Apache AGE project and before Bitnine AgensGraph, as anybody read the work of Matthias Schmid (University of Passau Germany) ? https://doi.org/10.1145/3366030.3366046 , I'm not expert on these subjects but my view is that it somewhere "confirms" that the model (RDBMS + JSONB for attributes) of AGE is great for "paths of variable length. This type of queries requires recursive SQL queries. Recursive queries with the use of the edge attributes table outperform any recursive query that uses adjacency tables"

The article also introduces an optimization (from a previous article from differents authors) around "redundant" storage of edge data in "adjacency" tables, claiming these tables are more efficient (than the edge table) "if the queried path is of fixed length"

The drawback seems to have adajency tables , it requires a "grouping" of frequently common edge "labels" per vertice : a coloration algorithm is used on the graph datas to design a hash function which reduces the number of needed "triples" columns aka EID(k)-Label(k)-Targets(k) in adjency tables.

You can see in this figure one of the 2 (one from incoming edges, one for outgoing edges) adjacency table, and the optimisation using array of vertices, avoiding use of joins on a "secondary" adjacency table which was proposed in the previous article from differents authors ("SQLGraph: An Efficient Relational-Based Property Graph Store" https://doi.org/10.1145/2723372.2723732)

[ThomasMic]

Another, recent, interesting publication, with lot of information on storage of property graph in PostgreSQL ( with "pseudo implementation" of the concepts above) is "Towards Storing 3D Model Graphs in Relational
Databases" available here https://opus4.kobv.de/opus4-uni-passau/frontdoor/index/index/searchtype/authorsearch/author/Schmid%2C+Matthias/docId/1035/start/0/rows/10

" Motivated by the use case to integrate Building Information Modeling (BIM) data into the MonArch system, we propose a solution that transforms the BIM data into a property graph and stores this graph in the database system.

We present a novel approach to efficiently store property graph data in a relational database management system using JSON functionality and redundant storage of edges in adjacency lists and show how to import huge data sets into this schema."

[aked21]

Hello @ThomasMic, though it is a little late, thank you for sharing a meaningful insight!

[markgomer]

@ThomasMic this is really useful, maybe someone would bring a performance comparison between Apache AGE and other graph databases, like Neo4j...
A friend of mine has shared an article from the University of Washington comparing PostgreSQL with Neo4j:
https://courses.cs.washington.edu/courses/csed516/20au/projects/p06.pdf
The results showed that PostgreSQL greatly outperforms Neo4j across most evaluated metrics. Neo4j's advantages become apparent only in specific scenarios involving large datasets and complex joins.
Perhaps Apache AGE would satisfy that performance concern on Neo4j, while maintaining the simplicity of a graph database?