Using json_agg to avoid cartesian explosions like Hasura and Postgraphile

[aaron-manning]

Hasura and Postgraphile use PG in a way that:

1. Avoids the need to over fetch data (using a single query with many joins resulting in cartesian explosion. aka asSingleQuery())
2. Avoids the need to send multiple queries to the database to perform what is logically a single query (aka asSplitQuery())

This is discussed in the following HN article:
https://news.ycombinator.com/item?id=27550151

The current best solution with EF to avoid cartesian explosion results in multiple calls to the db, incurring latency, and causes possible data synchronization issues due to that latency. This is asSplitQuery().

Would it be possible (hypothetically) for EF + npgsql to query data from PG in a way that compiles a complex query involving multiple tables into a single query using the same techniques as Hasura? (row_to_json, json_agg etc)

This would bring (possibly significant) performance improvements, and remove the data sync issue as only a single query needs sending to the db, regardless of how much related data is required.

Thanks

[roji]

Duplicate of #1691

[roji]

Note that #1691 is about using PG (non-JSON) arrays to load one-to-many relationships in a single query without the "cartesian explosion" problem. This is different from using JSON-based techniques in that that the array would contain a PG record/composite type, representing a (dependent) row in PG. Using JSON, you get back JSON documents which you must then deserialize; but JSON documents lose most of the type information, encoding everything as text. In other words, we'd have to be able to decode e.g. a timestamp from text. In addition, since JSON uses text encoding, it is less efficient than the binary encoding you get with record/composite types.

Note that lots of preliminary work has been done:

• Read arbitrary trees of arrays of composite types, with specific type handlers for inner elements npgsql#3558 (in 6.0) at the ADO level allows reading a record type via an internal/nested NpgsqlDataReader.
• Basic PostgreSQL custom aggregate translations #2395 (in 7.0) adds custom aggregate function translation, including array_agg (but also json_agg/json_object_agg)

The main remaining task here would be to do the actual shape in the PG EF provider, which is likely a bit complicated and would also probably require changes on the EF side. I hope to be able to tackle this in 8.0.

[roji]

Closing as the plan for now is to go with non-JSON PG arrays, for the above reasons. I'd be happy to continue the conversation though.