Does anyone know whether it’s possible to emulate a graph database with a relational by adding specific meta-data tables or indices? Emulate in the sense that you can do efficient graph traversals that drill along a path for a certain non-trivial depth.

  • lurkingllama
    link
    fedilink
    English
    arrow-up
    2
    ·
    1 year ago

    You can do many common graph search operations in only one query using recursive CTEs (common table expressions). You should probably look into the documentation of your RDBMS because details may vary, but here’s an example from PostgreSQL’s documentation on recursive CTEs:

    WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
    ) CYCLE id SET is_cycle USING path
    SELECT * FROM search_graph;
    
    • MattolOPM
      link
      fedilink
      English
      arrow-up
      2
      ·
      1 year ago

      How do sql query engines backed by S3 like Trino (Athena) behave for euch queries?

      • lurkingllama
        link
        fedilink
        English
        arrow-up
        2
        ·
        1 year ago

        I don’t know. But recursive CTEs habe been part of the standard since SQL:1999, so if they advertise compliance with SQL:1999 or a newer version of the standard, they should at least be able to execute the query. To find out whether they do so efficiently, you could do an experiment (remember most SQL engines allow you to look at the query plan for a given query) or read their documentation.