Recursive CTE can be very fast in Postgres. I can create a closure table of 2 Million rows for indirect and direct relations i.e. All paths from 15000 source direct relations in about 12 seconds on my MBPro 1Tb ssd 16 Ram. The makes it practical to just recreate all paths as a materialzed view refresh instead of worring about complicated incremental graph logic such as moving branches. With such a closure view - any graph traversal query is lightning fast - no need for neo4j !

Can you post the code for this somewhere? Thanks!

You mean like

    SELECT key, value1,
      ARRAY(SELECT value2 FROM bar WHERE bar.key = foo.key)
        AS value2_array
    FROM foo;

?

(In Postgres; no "JSON capabilities" needed.)

In the old days, SQL operated on rectangular tables and produced rectangular tables ... which doesn't work so well for data that's better modelled as a tree or graph. But there now lots of ways to produced multi-valued output and to operate on graph-structured data. In addition to PG's JSON, you might checkout recursive unions - which supports a subset of graph navigation. But while SQL may have grown just a tad more expressive, I'm not sure that performance is up to snuff. And even if it's now possible to express some things in SQL, it doesn't mean it's easy or readable.

It works pretty well and fairly quickly. We use the array_agg and other json features in a Postgres to Elasticsearch ETL. Postgres is able to spit out the exact format that we need, so the application code isn't really doing much more than coordinating the reads and writes.

What you need is good old array_agg(expression) with group by. Json capabilities may be needed for more complex hierarchical structures.

> It looks like Postgres can do it [1] with its JSON capabilities

Postgres could do it long before its JSON capabilities, array_agg was added in Postgres 8.4 (2009) and hstore in 8.3 (2008)

Also, ARRAY(), since 7.4 in 2003.