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

61–70 of 76 posts

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

#61

Post by sgarland »

xarope wrote: post #24
stephen wrote: post #16

Earlier quoted context omitted.

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.

Stored procedures are also screeched at as being anti-patterns, somehow. I don’t get it; you can store them in version control just like anything else, and add comments where necessary in the code base indicating what the DB is doing.

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

#62

Post by sgarland »

dventimi wrote: post #22
unrealhoang wrote: post #17

Earlier quoted context omitted.

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.

Prisma “avoids” problems (at least until recently) by just doing SELECT * UNION ALL SELECT *… and then handling joins internally. Utter madness.

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

#63

Post by sgarland »

ec109685 wrote: post #18
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.

Without two phases: figure out what you need for the resource, execute query, how can you avoid N+1 problem, raw sql or not.

Recursive CTEs, LEFT JOINs, Stored Procedures…

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

#64

Post by sgarland »

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…

> because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).

I think the idea that doing business logic inside the DB is an anti-pattern is cargo culting. You can quite easily store schema definitions, stored procedures, etc. inside version control. You can document it just as you would any other code. You don’t have to see what precisely the call is doing at all times, provided you understand its signature.

Letting the DB do more than just be a dumb data store is a great idea, IMO, and one that is too often eschewed in favor of overly-complicated schemes.

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

#65

Post by [deleted] »

sgarland wrote: post #62
dventimi wrote: post #22

Earlier quoted context omitted.

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

Prisma “avoids” problems (at least until recently) by just doing SELECT * UNION ALL SELECT *… and then handling joins internally. Utter madness.

[deleted]

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

#66

Post by dventimi »

superice wrote: post #55
dventimi wrote: post #52

Earlier quoted context omitted.

That's also 101 entities. That's a half a thousand values if A and B have ~5 attributes. That's too much data. No human can make use of it and no application should offer it. Good ones won't. They'll impose limits and when they do they'll naturally also be imposing a limit on the size of the N+1 problem. That's why I say that in my opinion, it's overblown. You're welcome to form your own opinion, of course.

Happy to name a concrete use case: showing a manifest of containers to load/unload from a ship. Search needs to happen client-side for many reasons, ballpark figures may be 1000 loads, 1000 unloads, the info about the shipvisit, and a stowage plan of 1000 records loading/unloading (separate from the manifest). Give you 4001 individual entities, clocking in at at least 20 attributes per entity (container number, type,…

If I'm reading you correctly you're saying there's an industrial grade application with a UI that routinely shows up to 80000 attributes to a user, or at least needs that information for client-side processing. Setting aside whether that's justified for this particular use case, do you think that's a common use case in let's say consumer grade web and mobile applications?

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

#68

Post by bbkane »

sgarland wrote: post #64
stephen wrote: post #16

Earlier quoted context omitted.

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…

> because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures). I think the idea that doing business logic inside the DB is an anti-pattern is cargo culting. You can quite easily store schema definitions, stored procedures, etc. inside version control. You can document i…

I know putting business logic in the DB has been used very successfully, but it also has some large downsides.

It's harder to express some things as SQL and your team will be less familiar with SQL than your preferred language. SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.

It's harder to debug when things go wrong. Where do you put a breakpoint?

It's likely your team ends up splitting your business logic between your app and your DB, and then you have to figure out which part is writing incorrect data when there's a problem.

For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.

Honestly, it's too soon to tell whether my apps will be successful with this approach. They haven't yet gone through years of real world usage and requirements changing. But so far it's gone well!

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

#69

Post by sgarland »

bbkane wrote: post #68
sgarland wrote: post #64

Earlier quoted context omitted.

> because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures). I think the idea that doing business logic inside the DB is an anti-pattern is cargo culting. You can quite easily store schema definitions, stored procedures, etc. inside version control. You can document i…

I know putting business logic in the DB has been used very successfully, but it also has some large downsides. It's harder to express some things as SQL and your team will be less familiar with SQL than your preferred language. SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages. It's harder to debug when things go wrong. Where do you put a breakpoint? I…

> It's harder to express some things as SQL

This is true, but then, it's also harder to write Rust than Python. There are tradeoffs which are made for every choice.

> and your team will be less familiar with SQL than your preferred language.

Also true, but given how SQL is practically everywhere and is not likely to go away any time soon, I strongly feel that nearly every dev could benefit from learning it.

> SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.

Again, true. Personally I dislike autocomplete functions (they break my flow, needing to parse the suggestion and accepting it, rather than just typing from muscle memory), but I get that others like them. Re: testing, I have an unpopular opinion: this matters very little compared to most other languages. Declarative languages like SQL or Terraform generally do not produce surprises. If you get an unexpected output, it's probably because you have incorrect logic. There are some testing frameworks that exist for various flavors of SQL should you wish, but IMO as long as you have some rigorous E2E tests, and your dev/staging environments are accurate and representative of prod, you'll be fine.

> For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.

Hey, you're doing better than most! I've never understood people's unwillingness to use things like length constraints. Sure, your app should ideally catch basic issues so they never have to make it to the DB, but I'd rather have the guarantee. The last thing you want is for someone to find a way to inject the entirety of Moby Dick into a `name` field.

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

#70

Post by gregwebs »

sbstp wrote: post #29
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.

They seem quite comparable in terms of features. The way they do relationships is a little different though. Sqlboiler is a little more ORM in its approach with relationships- it adds methods to retrieve relationships and it adds optional fields to its models. Jet on the other hand expects you to write out a type-safe join and then will load relationships into your struct automatically if you define a struct with the appropriate fields. I prefer the Jet approach- there's not ORM, just SQL and something that loads SQL results into structs. There is down-side that the loading into structs is not fully type-safe once you write custom structs rather than use generated models although I am not sure if that is even possible in sqlboiler.

sqlboiler shows a lot of examples of using strings for queries which is odd because they seem to support type-safe queries as well.

Post reply on HN