3 ms·

> 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 shorta

> 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
        The funnest part of outputting JSON from the query is that your API now basically becomes a RPC, as you don't have to do any marshalling/deserialization before returning the response with Content-Type application/json Response times are really fast like that, as you probably already know. Always fun to see <10ms round trips in the browser network tab on some requests Also no worry about n+1 queries, as they're fundamentally impossible to do like that
        password4321 3y ago
        I've long wondered if there would be any performance advantage for an API server to zero-copy the DB response to the browser, deserializing from the DB wire protocol on the front end. Or perhaps sending DB query results directly from DB server to browser, with the API server just initializing and securing. Thanks for pointing out how JSON from the DB is another option for moving a bit of processing elsewhere in the stack.
- nicoburns 3y ago
  It’s still a big saving overall if the alternative is a JOIN causing a cartesian explosion in the number of rows
- dbanon9 3y ago
  1. Read the source. It's very efficient: https://github.com/postgres/postgres/blob/a14e75eb0b6a73821e0d66c0d407372ec8376105/src/backend/utils/adt/json.c#L285-L394 https://github.com/postgres/postgres/blob/a14e75eb0b6a73821e... 2. You don't have to do deserialization in the application layer. If all you're using JSON for is to convert to OOP objects, just deserialize in the db -- which again, trivial. 3a. This is wrong on many counts. If you want efficient passing of JSON, use JSONB which is the binary encode of the JSON, as a tree structure. It will not include the structural characters. 3b. Bandwidth is also cheap. 4. Don't optimize without profiling. A few extra CPU cycles is not going to make-or-break your scaling journey, you'll most likely run into larger problems before that happens. 5. You can get "non-uniform" tuples by using UNIONs and a smart flagging system that points to tuple schemas -- rather than using JSON; the difference is entirely ergonomic. 6. If you're in a low-latency environment and the CPU cycles are absolutely critical, write your own extensions to handle what you're trying to do, instead of twisting Postgres into doing your bidding.

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
      The funnest part of outputting JSON from the query is that your API now basically becomes a RPC, as you don't have to do any marshalling/deserialization before returning the response with Content-Type application/json Response times are really fast like that, as you probably already know. Always fun to see <10ms round trips in the browser network tab on some requests Also no worry about n+1 queries, as they're fundamentally impossible to do like that
      - password4321 3y ago
        I've long wondered if there would be any performance advantage for an API server to zero-copy the DB response to the browser, deserializing from the DB wire protocol on the front end. Or perhaps sending DB query results directly from DB server to browser, with the API server just initializing and securing. Thanks for pointing out how JSON from the DB is another option for moving a bit of processing elsewhere in the stack.
nicoburns 3y ago
It’s still a big saving overall if the alternative is a JOIN causing a cartesian explosion in the number of rows
dbanon9 3y ago
1. Read the source. It's very efficient: https://github.com/postgres/postgres/blob/a14e75eb0b6a73821e0d66c0d407372ec8376105/src/backend/utils/adt/json.c#L285-L394 https://github.com/postgres/postgres/blob/a14e75eb0b6a73821e... 2. You don't have to do deserialization in the application layer. If all you're using JSON for is to convert to OOP objects, just deserialize in the db -- which again, trivial. 3a. This is wrong on many counts. If you want efficient passing of JSON, use JSONB which is the binary encode of the JSON, as a tree structure. It will not include the structural characters. 3b. Bandwidth is also cheap. 4. Don't optimize without profiling. A few extra CPU cycles is not going to make-or-break your scaling journey, you'll most likely run into larger problems before that happens. 5. You can get "non-uniform" tuples by using UNIONs and a smart flagging system that points to tuple schemas -- rather than using JSON; the difference is entirely ergonomic. 6. If you're in a low-latency environment and the CPU cycles are absolutely critical, write your own extensions to handle what you're trying to do, instead of twisting Postgres into doing your bidding.