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.
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.
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.
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.
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.
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).
A straw man argument you've pulled out of thin air.
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
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.
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
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.
I'm just pointing out it doesn't achieve that.
This solution for tuples is objectively worse in every way, throughout, network bandwidth, code complexity, maintainability, error likeliness.
You're just clearly someone who can't admit when they're wrong.
Prove it.
> You're just clearly someone who can't admit when they're wrong
You don't have a good technical argument so you jump to ad-hominem's?
If the biggest issue in my product is JSON deser, I'd be a happy camper.
I suggest avoiding statements like this. Yes they help vent your frustrations but they destroy the otherwise constructive and interesting debate the two of you were having.
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.
select array_agg(e) from jsonb_array_elements_text('["a","b"]'::jsonb) e;
?