4 ms·

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

> 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.
      - tomnipotent 3y ago
        You've basically described Firebase and its archetype of database.
      - vbezhenar 3y ago
        TLS everywhere makes the whole point of zero copying obsolete. Encryption eats so much CPU that copying data around does not change anything.
        411111111111111 3y ago
        It does add a few ms, but you're overstating it. For LAN traffic, it's usually 5-8ms last time I checked on my servers.
    - mattmanser 3y ago
      That not what we're talking about though, we're talking about aggregating the the result of a subselect to reduce the throughput of a SQL query. I'm just pointing out it doesn't achieve that.
- deleted 3y ago
  [deleted]
- throwaway2990 3y ago
  > No, you can't use 'negigibly' worse as a defence Absolutely you can when I can absolutely guarantee you that you’ve written worse performing queries in the past that caused more cpu issues than the overhead of working with JSONB.
littlestymaar 3y ago
> Which costs very little CPU in 2023. That's a very bad argument for an RDBMS, since you want to make it scale vertically as much as you can (unlike your application server, horizontally scaling your database is a completely different matter, and isn't straightforward at all).
- tomnipotent 3y ago
  The added cost of JSON serialization is easily offset by reducing the total number of overall queries and implicit (or explicit) transactions. The additional parallelization the RDBMS can achieve is generally greater than the added JSON serialization and extra network bandwidth.
  - littlestymaar 3y ago
    Maybe, but this isn't the same as saying CPU cost doesn't matter because it's cheap.
    - tomnipotent 3y ago
      > same as saying CPU cost doesn't matter because it's cheap A straw man argument you've pulled out of thin air.

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.
    - tomnipotent 3y ago
      You've basically described Firebase and its archetype of database.
    - vbezhenar 3y ago
      TLS everywhere makes the whole point of zero copying obsolete. Encryption eats so much CPU that copying data around does not change anything.
      - 411111111111111 3y ago
        It does add a few ms, but you're overstating it. For LAN traffic, it's usually 5-8ms last time I checked on my servers.
  - mattmanser 3y ago
    That not what we're talking about though, we're talking about aggregating the the result of a subselect to reduce the throughput of a SQL query. I'm just pointing out it doesn't achieve that.
deleted 3y ago
[deleted]
throwaway2990 3y ago
> No, you can't use 'negigibly' worse as a defence Absolutely you can when I can absolutely guarantee you that you’ve written worse performing queries in the past that caused more cpu issues than the overhead of working with JSONB.

littlestymaar 3y ago

> Which costs very little CPU in 2023. That's a very bad argument for an RDBMS, since you want to make it scale vertically as much as you can (unlike your application server, horizontally scaling your database is a completely different matter, and isn't straightforward at all).

tomnipotent 3y ago
The added cost of JSON serialization is easily offset by reducing the total number of overall queries and implicit (or explicit) transactions. The additional parallelization the RDBMS can achieve is generally greater than the added JSON serialization and extra network bandwidth.
- littlestymaar 3y ago
  Maybe, but this isn't the same as saying CPU cost doesn't matter because it's cheap.
  - tomnipotent 3y ago
    > same as saying CPU cost doesn't matter because it's cheap A straw man argument you've pulled out of thin air.