Hacker News Reader

Comment by ruslan_talpa | Hacker News Reader

Doing json serialisation in the db is the only way to extract tree like data from the db. Another point to consider is that this is a much lower burden on the database then you think (roughly speaking, it adds 15%-20% more cpu load then a normal query), this is C code doing this, can't get much faster that. This type of load is easily horizontally scalable using read replicas which in RDS is basically one click.

But forget all that talk about horizontal scalability, 99% of the projects will never outgrow a single (big) database so it's no use in complicating things with "elastic" setups and "webscale". People don't realise just how fast postgres is http://akorotkov.github.io/blog/2016/05/09/scalability-towar...

Who among us have worked on projects that have 1M queries per second, not many.

View on HN

It's not really the only way (PostgreSQL has recursive queries), but it's definitely the most convenient (and in many cases the most efficient) way if you're not worried about referential integrity.

Care to explain how you would extract in a single query tree like data (without duplicate data going over the wire)?

I use `WITH RECURSIVE` to traverse trees:

https://www.postgresql.org/docs/current/static/queries-with....

it's not about traversing trees, it's about "returning" trees, there is a difference :)

Yes, and I use it to do things like "starting from this point in the tree, recursively return all of its children". Usually I ask for `parent_id`, other times I ask PostgreSQL to accumulate an array describing the path the database took to get from here to there. In no case have I found `WITH RECURSIVE` to be insufficiently powerful for working with trees.

I don't think you understand what i am referring to when i say "returning data". How do you return this http://pastebin.com/9cqzQETj information from the database, in a single query, without duplicate data.

consider there are 3 tables clients/projects/tasks with FKs between them.

Please paste the query that returns all the information above using "with recursive"

This is how the communication between the client an PG goes https://www.pgcon.org/2014/schedule/attachments/330_postgres...

- client sends an SQL command - server replies with RowDescription detailing the structure - after that a DataRow message is sent for every row - finally, the server sends CommandComplete and ReadyForQuery

So inherently the protocol can only represent a 2D array.

You’d use these [0] functions to build a json string and return your structure, one row per root of each hierarchy (you might only return one row if you’re after a single hierarchical json object). [0] https://www.postgresql.org/docs/9.5/static/functions-json.ht...

Please read the whole thread, this is the exact point i was making, json is the only way to return tree like data. willglynn was saying you can do it with "with recursive" and i think he was confusing "traversing/inspecting" tree like data with "returning" tree like data.

Reply on news.ycombinator.com