Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

brandur.org

Post reply on HN
Print view View original

21–30 of 76 posts

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#21

Post by d0100 »

stephen wrote: post #16
sgarland wrote: post #12

Earlier quoted context omitted.

That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.

I can assure you that brandur knows SQL. :-) (I don't know him personally, but have been following his blog for years.) What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'". You want to write this logic in your preferred backend language, Go/TS/C/etc. This works fine, in the /account/X endp…

Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries

    sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#22

Post by dventimi »

unrealhoang wrote: post #17
sgarland wrote: post #12

Earlier quoted context omitted.

That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.

TFA is using raw SQL (via sqlc) if you read carefully. And no, raw SQL doesn't get rid of N+1 issues.

PostgREST for instance avoids the alleged problem by generating a single SQL statement. So does Hasura, PostGraphile, and probably Prisma for that matter.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#23

Post by dventimi »

hinkley wrote: post #5

I've always been sort of fond of 1 + 1. It's too often the case that there's a popular query that doesn't even need the child data to function, and unless you have some elaborate caching mechanism it would be a shame to pay the full cost of the join or however you want to implement it. Making one query that returns the base data and a second that pulls all of the associated data works often enough. Then it's only whe…

Agreed. The N+1 problem is not as big as people make it out to be.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#24

Post by xarope »

stephen wrote: post #16
sgarland wrote: post #12

Earlier quoted context omitted.

That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.

I can assure you that brandur knows SQL. :-) (I don't know him personally, but have been following his blog for years.) What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'". You want to write this logic in your preferred backend language, Go/TS/C/etc. This works fine, in the /account/X endp…

for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)".

however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#25

Post by tedunangst »

andrscyv wrote: post #2

So a super complicated work around instead of just doing sql queries or using a query builder ???

Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-anothe...

For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it work particularly well if you're joining against something that isn't a single string column.

So I just bang the rocks together like a caveman and write a second query to go back and fetch all the tags, then loop around assign them to the matching posts.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#26

Post by kstrauser »

dventimi wrote: post #19

I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.

I think it’s underblown. If more people were properly horrified about how much more needless work it causes the DB server, the network, and the client to do, they’d burn it with fire.

In my experience it’s the biggest culprit behind random system fallovers that “work on my machine”. Well sure. Your test page load only resulted in 8 round trips to the server with a hidden O(n^2) in-process merge. The same page on the fully populated prod server required 137 round trips, and it still fails under load even though you can’t physically fit more RAM in the thing.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#28

Post by saghm »

dventimi wrote: post #19

I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.

> The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.

Isn't that the point? Repeated network calls will cause latency even if the data is minimal; the total amount of data returned will always be the same, so the "N" extra network calls are pure overhead. For applications where the amount of data is usually small, network calls will likely be the bottleneck.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#29

Post by sbstp »

gregwebs wrote: post #6

Jet can automatically load joined objects into embedded Go structs: https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM... Depending on what you are doing there might be some duplication that you could remove by creating hash lookups as in this post, but I would reach for Jet first. sqlc supports embedding but not embedded slices?

How does jet compare to sqlboiler? I've been using sqlboiler quite successfully on a work project.

Re: Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

#30

Post by Mavvie »

tedunangst wrote: post #25
andrscyv wrote: post #2

So a super complicated work around instead of just doing sql queries or using a query builder ???

Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-anothe... For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it…

I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.
Post reply on HN