Hacker News Reader

> 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

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.

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.

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.

building a json object is slow compared to returning rows. but its far faster than multiple trips to the database to build a json object in the application layer.

You might be interested in PostGraphile. [1]

[1] https://www.graphile.org/postgraphile/

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.

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

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

That “crap mini-language” is now in ISO SQL. Now we’re stuck with it forever. Even in Oracle :o

I think they are adding many readable function versions lately

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.

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

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.

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.

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

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.

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.

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

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.

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

    select array_agg(e)  from jsonb_array_elements_text('["a","b"]'::jsonb) e;

?

I donno about other databases but it doesn’t add complexity to PostgreSQL. And you can convert json array to text array.

But now you have to work in...shudder...JSON.

It's nice being able to use datetimes, 64 bit ints, binary, etc

> This is no longer true in database that have JSON support

Doing that means losing foreign-key referential integrity...

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.

My mistake - to be fair, it is 5am here

He specifically addressed that in the comment. In the sentence right after the bit you quoted.

Generated columns (i.e. some_json->>'id') can have constraints just fine, maintaining whatever integrity you want