6 ms·

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

masklinn 3y ago
That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.
- tomnipotent 3y ago
  > further increase the complexity of the queries Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.
  - mattmanser 3y ago
    I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth. 1. The data has to be serialised into JSON on the DB server, which costs CPU. 2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem is that you're using javascript instead of a better, compiled language) 3. The network bandwidth is actually larger as you've got all those extra {} in your result set, compared to the raw data in column format It might "look" bigger to a human as there's more columns, but the data is exactly the same. So by definition, youre doing extra CPU work of serialising/deserliazing JSON and adding all the object markers of extra characters like {} and "" and : means the payload is bigger too.
    - tomnipotent 3y ago
      > which costs CPU Which costs very little CPU in 2023. > deserialised on the application server This is true regardless. The low-level libraries are still parsing the stream into meaningful in-memory structures. With JSON, the low-level library only has to parse a variable length string, then JSON decode. I'm unfamiliar with any language in 2023 that doesn't have incredibly fast and efficient JSON parsers. > bandwidth is actually larger as you've got all those extra This is true, but generally negligible. I run into very few scenarios where network saturation is more of a problem than CPU or memory issues. If you have network-constrained problems, obviously optimize accordingly.
      - mattmanser 3y ago
        No, you can't use 'negigibly' worse as a defence. It's either better, or not. Your comment does not make it better, it's still worse. So it won't improve performance, but degrade it, even if it's negilible. So there's no reason to do it. Plus you've made a crazy SQL select instead of a normal one, which is harder to maintain. So it's worse performance and worse maintenance. i.e. don't do this, it's dumb, especially for the reasons claimed which are factually incorrect as it will not improve throughout but make it worse
        tomnipotent 3y ago
        > you can't use 'negigibly' worse as a defence Absolutely you can when the increase in something (bandwidth) in a system with surplus supply with the trade-off of optimizing a more constrained supply (CPU or memory). > made a crazy SQL select instead of a normal one, which is harder to maintain. Purely subjective. Myself nor the people I've hired would have a problem maintaining a more complex SQL query using CTE's and JSON serialization than not. > it's worse performance I cannot imagine that's the case in the context we've been discussing. An RDBMS duplicating JSON output of tuples multiple times in a single transaction is not particularly expensive compared to the alternative.
        411111111111111 3y ago
- throwaway2990 3y ago
  I donno about other databases but it doesn’t add complexity to PostgreSQL. And you can convert json array to text array.
- n_e 3y ago
  select array_agg(e) from jsonb_array_elements_text('["a","b"]'::jsonb) e; ?
deleted 3y ago
[deleted]
tomnipotent 3y ago
Absolutely. JSON serialization is so well optimized in most RDBMS vendors now it puts very little additional CPU work on the database, and duplicating data in the output for large graph-based results is a small price to pay to breaking out of set-based constraints.
DaiPlusPlus 3y ago
> This is no longer true in database that have JSON support Doing that means losing foreign-key referential integrity...
- mattmanser 3y ago
  He specifically addressed that in the comment. In the sentence right after the bit you quoted.
- jmull 3y ago
  The comment alludes to this, but to clarify: You can have your data stored in tables with all the constraints you might want, but then use json in queries to return the results in whatever form you want.
  - DaiPlusPlus 3y ago
    My mistake - to be fair, it is 5am here
- conradev 3y ago
  Generated columns (i.e. some_json->>'id') can have constraints just fine, maintaining whatever integrity you want
yencabulator 3y ago
The crap mini-language of Postgres functions to manipulate JSON makes otherwise reasonable queries unreadable, though.
- DaiPlusPlus 3y ago
  That “crap mini-language” is now in ISO SQL. Now we’re stuck with it forever. Even in Oracle :o
- riku_iki 3y ago
  I think they are adding many readable function versions lately
mborch 3y ago
JSON has pretty limited choice of data types though. Want better precision for numbers, use a string. Want dates? Use a string. Etc.
erikpukinskis 3y ago
> You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query) For anyone (like me) who is not quite able to visualize this, here is an example: SELECT json_agg(trips) FROM ( SELECT json_agg( json_build_object( 'recorded_at', created_at, 'latitude', latitude, 'longitude', longitude ) ) as trips FROM data_tracks GROUP by trip_log_id )s From StackOverflow user S-man https://stackoverflow.com/a/53087015 https://stackoverflow.com/a/53087015
- 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 ago
    Very 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 ago
    You might be interested in PostGraphile. [1] [1] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
  - mplanchard 3y ago
    Like 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.
paulddraper 3y ago
But now you have to work in...shudder...JSON. It's nice being able to use datetimes, 64 bit ints, binary, etc

masklinn 3y ago

That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.

tomnipotent 3y ago
> further increase the complexity of the queries Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.
- mattmanser 3y ago
  I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth. 1. The data has to be serialised into JSON on the DB server, which costs CPU. 2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem is that you're using javascript instead of a better, compiled language) 3. The network bandwidth is actually larger as you've got all those extra {} in your result set, compared to the raw data in column format It might "look" bigger to a human as there's more columns, but the data is exactly the same. So by definition, youre doing extra CPU work of serialising/deserliazing JSON and adding all the object markers of extra characters like {} and "" and : means the payload is bigger too.
  - tomnipotent 3y ago
    > which costs CPU Which costs very little CPU in 2023. > deserialised on the application server This is true regardless. The low-level libraries are still parsing the stream into meaningful in-memory structures. With JSON, the low-level library only has to parse a variable length string, then JSON decode. I'm unfamiliar with any language in 2023 that doesn't have incredibly fast and efficient JSON parsers. > bandwidth is actually larger as you've got all those extra This is true, but generally negligible. I run into very few scenarios where network saturation is more of a problem than CPU or memory issues. If you have network-constrained problems, obviously optimize accordingly.
    - mattmanser 3y ago
      No, you can't use 'negigibly' worse as a defence. It's either better, or not. Your comment does not make it better, it's still worse. So it won't improve performance, but degrade it, even if it's negilible. So there's no reason to do it. Plus you've made a crazy SQL select instead of a normal one, which is harder to maintain. So it's worse performance and worse maintenance. i.e. don't do this, it's dumb, especially for the reasons claimed which are factually incorrect as it will not improve throughout but make it worse
      - tomnipotent 3y ago
        > you can't use 'negigibly' worse as a defence Absolutely you can when the increase in something (bandwidth) in a system with surplus supply with the trade-off of optimizing a more constrained supply (CPU or memory). > made a crazy SQL select instead of a normal one, which is harder to maintain. Purely subjective. Myself nor the people I've hired would have a problem maintaining a more complex SQL query using CTE's and JSON serialization than not. > it's worse performance I cannot imagine that's the case in the context we've been discussing. An RDBMS duplicating JSON output of tuples multiple times in a single transaction is not particularly expensive compared to the alternative.
        411111111111111 3y ago
throwaway2990 3y ago
I donno about other databases but it doesn’t add complexity to PostgreSQL. And you can convert json array to text array.
n_e 3y ago
select array_agg(e) from jsonb_array_elements_text('["a","b"]'::jsonb) e; ?

deleted 3y ago

[deleted]

tomnipotent 3y ago

Absolutely. JSON serialization is so well optimized in most RDBMS vendors now it puts very little additional CPU work on the database, and duplicating data in the output for large graph-based results is a small price to pay to breaking out of set-based constraints.

DaiPlusPlus 3y ago

> This is no longer true in database that have JSON support Doing that means losing foreign-key referential integrity...

mattmanser 3y ago
He specifically addressed that in the comment. In the sentence right after the bit you quoted.
jmull 3y ago
The comment alludes to this, but to clarify: You can have your data stored in tables with all the constraints you might want, but then use json in queries to return the results in whatever form you want.
- DaiPlusPlus 3y ago
  My mistake - to be fair, it is 5am here
conradev 3y ago
Generated columns (i.e. some_json->>'id') can have constraints just fine, maintaining whatever integrity you want

yencabulator 3y ago

The crap mini-language of Postgres functions to manipulate JSON makes otherwise reasonable queries unreadable, though.

DaiPlusPlus 3y ago
That “crap mini-language” is now in ISO SQL. Now we’re stuck with it forever. Even in Oracle :o
riku_iki 3y ago
I think they are adding many readable function versions lately

mborch 3y ago

JSON has pretty limited choice of data types though. Want better precision for numbers, use a string. Want dates? Use a string. Etc.

erikpukinskis 3y ago

> You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query) For anyone (like me) who is not quite able to visualize this, here is an example: SELECT json_agg(trips) FROM ( SELECT json_agg( json_build_object( 'recorded_at', created_at, 'latitude', latitude, 'longitude', longitude ) ) as trips FROM data_tracks GROUP by trip_log_id )s From StackOverflow user S-man https://stackoverflow.com/a/53087015 https://stackoverflow.com/a/53087015

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 ago
  Very 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 ago
  You might be interested in PostGraphile. [1] [1] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
- mplanchard 3y ago
  Like 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.

paulddraper 3y ago

But now you have to work in...shudder...JSON. It's nice being able to use datetimes, 64 bit ints, binary, etc