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

Ban 1+N in Django

suor.github.io

Post reply on HN
Print view View original

61–70 of 153 posts

Re: Ban 1+N in Django

#61

Post by lmm »

Izkata wrote: post #60
lmm wrote: post #35

Earlier quoted context omitted.

It's extremely easy to write SQL queries that take a lot longer than they look like - an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different. So as far as I can see writing SQL manually is just extra drudgery for no real gain.

> an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different ORM queries compile down to SQL, so how would they look different?

Typescript compiles down to Javascript but an unchecked cast looks different from a known-safe assignment in Typescript even though they look the same in Javascript.

Re: Ban 1+N in Django

#62

Post by arjvik »

stephen wrote: post #21
tveita wrote: post #13

Earlier quoted context omitted.

You'd ideally want to do something like dataloader, where you look up your N Xs in a single cache query, and then do a single database lookup for the (N-C) Xs that weren't in cache. You can then either eagerly load the Ys with the Xs like you said, or do a secondary cache lookup for every Y, and potentially another single database query for the Ys not in cache. Unfortunately this pattern gets really hairy if you're n…

+1. The JS event loop auto-monad-izing Promises into Haxl [1]-esqe trees of implicitly-batched loads has been a big win for us building on JavaScript/TypeScript. If I had to move to another language, I'd really want to find a "powered by the event loop / dataloader" framework, i.e. Vert.x for Java. Also, per dataloader, a shameless plug for our ORM that has dataloader de-N+1-ing built natively into all object graph t…

> The JS event loop auto-monad-izing Promises into Haxl [1]-esqe trees of implicitly-batched loads

can you explain what this means?

Re: Ban 1+N in Django

#64

Post by danielheath »

wruza wrote: post #54

I wonder why ORMs still(?) work as simple wrappers and never track access patterns. If you see that `in books` generator’s results experience accesses through a relationship, it’s pretty obvious to join it in advance after few misses and serve `book.author.full_name` from cache. Of course that would make ORM more complex, but why would you need one otherwise. A good database interface should make good guesses, probab…

I don't want my app to change performance-sensitive behavior at runtime, that's a debugging nightmare.

I do want my framework to throw an error in development mode if I screw up the preloading.

Re: Ban 1+N in Django

#65

Post by marcosdumay »

nicoburns wrote: post #58
wruza wrote: post #54

I wonder why ORMs still(?) work as simple wrappers and never track access patterns. If you see that `in books` generator’s results experience accesses through a relationship, it’s pretty obvious to join it in advance after few misses and serve `book.author.full_name` from cache. Of course that would make ORM more complex, but why would you need one otherwise. A good database interface should make good guesses, probab…

Because people want their queries to have predictable performance.

Interestingly, they never get it. Every layer of the stack up to the client's database connector is unpredictable.

But yes, people want it. And will trade a lot of performance for a false promise of predictability.

Re: Ban 1+N in Django

#66

Post by bagels »

Usually it's the wrong pattern, but not always. If you have a very large dataset, it can be beneficial. You can make smaller transactions, smaller query results, and not fill up local memory. For offline backfills, or various reporting jobs this can be the difference between something that works, and something that doesn't.

Re: Ban 1+N in Django

#67

Post by Suor »

bagels wrote: post #66

Usually it's the wrong pattern, but not always. If you have a very large dataset, it can be beneficial. You can make smaller transactions, smaller query results, and not fill up local memory. For offline backfills, or various reporting jobs this can be the difference between something that works, and something that doesn't.

For that fetching in chunks is usually a way to go. I use something of these usually https://handy.readthedocs.io/en/latest/db.html#queryset_iter...

Re: Ban 1+N in Django

#69

Post by zdragnar »

Kamq wrote: post #17
zdragnar wrote: post #11

Earlier quoted context omitted.

That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. Personally, I'd much rather have sane queries in the first place, but rails isn't really my cup of tea either, so take my opinion with a large pinch of salt if you do.

> That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. You'd surely care about getting a significant chunk of your usage in server memory rather than what percentage of total data that is, no? To take the site we're on as an example, I'd be willing to bet the 30 things on the front page have one or two orders of magnitude…

That seems much more specialized than what I'd imagined based on the prior description.

In this example, would rails only cache models that fit certain query parameters? Or is it a configurable LRU? How does the in-memory cache work when you have multiple puma workers? Or does this mechanism rely on something more esoteric? Given that this technique is part of solving the performance problems of N+1, I'm assuming things like votes and comments are included, and the high degree of write volume would imply that all of the caches need to stay up to date- at least with a fairly high degree of consistency.

Re: Ban 1+N in Django

#70

Post by eurasiantiger »

tveita wrote: post #13
nickjj wrote: post #7

There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries…

You'd ideally want to do something like dataloader, where you look up your N Xs in a single cache query, and then do a single database lookup for the (N-C) Xs that weren't in cache. You can then either eagerly load the Ys with the Xs like you said, or do a secondary cache lookup for every Y, and potentially another single database query for the Ys not in cache. Unfortunately this pattern gets really hairy if you're n…

So, in other words, you want GraphQL.
Post reply on HN