4 ms·
As someone who would like to use SQL to return a tree-like structure, how well does this scale (in terms of readability, performance, etc.) when the query is ex
by rbalicki 3y ago
As someone who would like to use SQL to return a tree-like structure, how well does this scale (in terms of readability, performance, etc.) when the query is extremely large and nested? For example, if we are attempting to replace GraphQL with some sort of SQL.
I'm super unfamiliar with this space, and would love to know whether it is a feasible/worthy goal to replace GraphQL queries with generated SQL.
- summarity 3y agoVery well, I run huge nested CTEs with json aggregation in SQLite for findsight.ai and there’s really not any noticeable performance overhead. A good editor (DataGrip) helps.
- paulddraper 3y agoYou might be interested in PostGraphile. [1] [1] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
- mplanchard 3y agoLike with most performance questions, it depends. I see someone else said that it scaled well for sqlite. For postgres, it very much depends. Building a json object is much, much slower than selecting a row. Updating JSON objects involves building a new one, since you need to replace the entire object, so avoid building your schema in a way that requires updating JSON objects. (Experience is with postgres 14. To be fair, performance was generally fine up until the 100s of millions of rows) JSON query performance in postgres though is generally quite good. If you have static data, throwing it into a JSONB column with a jsonb_path_ops GIN index (or the default if you need the extra query flexibility) scales well up to billions of rows.
- netcraft 3y agobuilding a json object is slow compared to returning rows. but its far faster than multiple trips to the database to build a json object in the application layer.