Hacker News Reader

Comment by mattmanser | Hacker News Reader

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.

View on HN

1. Read the source. It's very efficient: 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.

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

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

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

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

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.

You've basically described Firebase and its archetype of database.

TLS everywhere makes the whole point of zero copying obsolete. Encryption eats so much CPU that copying data around does not change anything.

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.

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.

It's not subjective, it's.objective.

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.

> it's.objective.

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?

They've presented plenty of drawbacks, which you've dismissed as "it's not that bad", while not presenting any benefit.

I've presented the bandwidth vs. memory and CPU trade-off, was that not obvious? Not to mention reduced network round trips, and less overhead on transaction management.

If the biggest issue in my product is JSON deser, I'd be a happy camper.

Bandwidth and memory are probably both worse, JSON adds overhead. Round trip latency only happens if you wait for the results of one query to send the other.

>You're just clearly someone who can't admit when they're wrong.

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.

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

> 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).

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.

Maybe, but this isn't the same as saying CPU cost doesn't matter because it's cheap.

> same as saying CPU cost doesn't matter because it's cheap

A straw man argument you've pulled out of thin air.

It’s still a big saving overall if the alternative is a JOIN causing a cartesian explosion in the number of rows

Reply on news.ycombinator.com