Support for recursive queries (for graph / tree use-cases)

[hpvd]

Support for recursive queries to use Pinot for typical graph use cases

Many DBs already support recursive queries for several years. This enable many graph / tree like queries with good performance
without the need for a dedicated graph database.

The with recursive clause is defined in ISO/IEC 9075-2:2023 §7.17 as part of optional feature T131

-> A good intro on

• what it is and
• what it made possible
• with which syntax

can be found here:
https://www.linkedin.com/pulse/you-dont-need-graph-database-modeling-graphs-trees-viktor-qvarfordt-efzof/

databases with support for recursive queries are, e.g.

• PostgreSQL
• MySQL https://dev.mysql.com/worklog/task/?id=3634
• MariaDB
• H2
• SQLite

see https://modern-sql.com/caniuse/with_recursive_(top_level)

and https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

=> Is there way to make this happen for Pinot too?
Or is this hardly possible with our architecture?

[hpvd]

Motivation:

for using Pinot (or any other non graph native DB) for typical Graph-Usecases like

• deep personalisation
• social networks
• RAG
• fraud detection

-> making it possible to build systems without having to use an additional database type like neo4j
which saves lots of effort during dev and maintenance and even costs.
In addition its much easier to build a team being expert in one tech stack than in two...

[hpvd]

Is this realistic from a performance pov in production?

imho for several usecases: YES!

When looking into fair comparisons / benchmarks of relational and graph databases
where each one is optimized by using indexes,
you are able to even find performance benefits for relational databases in several graph like uses-cases because they take much more advantage from indexes than graph DBs do.

Example: Performance of Graph and Relational Databases in Complex Queries https://www.mdpi.com/2076-3417/12/13/6490

And Pinot already proofed being pretty strong on benefitting from indexes...

[hpvd]

from strategic USP POV: being able to efficiently use Pinot for production graph use-cases, may give a new unique solution, hardly to match in this combination by "real" graph DBs:

• low-latency (P95/99)
• high throughput
• data freshness at scale (real time ingest)
• pure size
• storage efficiency
• benefits of no need for a dedicated graph db as discussed above

would be also a great extension to anomaly detection (third eye)...

[hpvd]

on the long run, one could also look into integrating approaches like RelGo (kind of Calcite extension) to make even "Property Graph Queries" (PGQ) possible in highly efficient manner (>10x)
(PGQs are now part of ISO SQL:2023 -> part 16 SQL/PGQ)

for RelGo approach, including benchmarks see:
Towards a Converged Relational-Graph Optimization Framework https://arxiv.org/html/2408.13480v1