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

31–40 of 76 posts

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

#31

Post by jimbokun »

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.

Now that Go has generics, you can write “map_slice” in a few lines then use it everywhere. I’ve done it myself.

Not sure why they haven’t added it to the standard library.

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

#32

Post by superice »

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…

This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations.

Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched.

The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.

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

#33

Post by Izkata »

superice wrote: post #32
tedunangst wrote: post #25

Earlier quoted context omitted.

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…

This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations. Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them.…

Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch.

It's also had this for over a decade so I have to wonder how rare it actually is in ORMs in general...

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

#34

Post by erikpukinskis »

As a side note, I think I am adding “render” to my list of words which are meaningless and therefore banned from using in code:

- render

- container

- context

- base

- model

- action

- component

- bake

- build

- generate

- view

- control

- process

- resource

Any I should add/remove?

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

#35

Post by superice »

Izkata wrote: post #33
superice wrote: post #32

Earlier quoted context omitted.

This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations. Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them.…

Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch. It's also had this for over a decade so I have to wonder how rare it act…

Interesting. Does it do this separate query for all entities in scope, or per entiry like Hibernates SELECT FetchMode? I find a separate SELECT is usually possible, but doesn’t quite solve this in the general case.

Perhaps I missed Django, but as far as I could tell, Hibernate and jooq can’t do this, Ecto can’t do it, ActiveRecord can’t, and the entirety of ORMs for JS can’t (TypeORM, Prisma, Drizzle, Sequelize and a bunch more).

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

#36

Post by nurettin »

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…

> Some of us just aren't smart enough for sql.

I've heard this all my career. And I went through several phases with SQL including the banging rocks phase.

No, some of us just haven't had time to improve. I wrote my first sql in the early 2000s. I wasn't aware of window functions and lateral joins until 10 years later. Took me another couple of years to start using CTEs.

I don't even write SQL that much. I know enough SQL to make ORMs just do what I want.

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

#38

Post by fragmede »

slotrans wrote: post #37

ORMs are such a toxic idea, we'd be better off if we could un-invent them. Look how thoroughly this poor author's mind has been poisoned by long-term exposure. Please get out there and write some SQL. I promise it won't hurt you.

Little Bobby Tables would love that to pieces.

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

#39

Post by philosopher1234 »

fragmede wrote: post #38
slotrans wrote: post #37

ORMs are such a toxic idea, we'd be better off if we could un-invent them. Look how thoroughly this poor author's mind has been poisoned by long-term exposure. Please get out there and write some SQL. I promise it won't hurt you.

Little Bobby Tables would love that to pieces.

SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.

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

#40

Post by tristor »

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…

SQL is easy to understand from the perspective of syntax, but quickly becomes challenging to understand depending on the data model how to actually get what you want out of a query. I have spent many years working in SQL across different server products (PostgreSQL, MySQL, Microsoft SQL Server, and others), and I still sometimes find myself struggling to build the most efficient query for task. Most of the time, it's not SQL that's the problem, it's a broken data model that preceded me (often created by an ORM) that I am forced to work with.

All that said, while it may be painful sometimes, learning it can at least let you poke your ORM more towards the right direction, or give you an opportunity to identify the hot spots where it's worth investing the effort to write a more efficient query directly, much like where sometimes it makes sense to write some subset of an application in assembly for performance, while most of the time you're better off just letting the compiler optimize for you.

For the problem you're talking about above, you're trying to avoid a Cartesian explosion, which is probably best handled through query splitting as you mentioned, but it depends on the database engine how best to approach that. For all its warts, Microsoft SQL Server is pretty good about things like the ability to use query splitting within the context of a single transaction to make it serializable and ensure data consistency (although that can come with its own challenges for performance).

The example you provided is nearly exactly the case where I would expect an ORM to produce a bad query, where-as a direct query build would be significantly more performant because you can incorporate a clear understanding of the data model and use limits and ordering to minimize the size of the result set you need to deal with.

Post reply on HN