[nickjj]

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 but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query.

[tveita]

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 not using promises and an event loop.

https://www.npmjs.com/package/dataloader

[hinkley]

If you have multiple ways to 'see' the same X from multiple Y objects, then all of this get complicated quickly.

Once you're there a microservice has some advantages. Wrap a cache with a service, implement multi-get, anything not in the cache calls through to the database.

[stephen]

+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 traversals:

https://joist-orm.io/docs/goals/avoiding-n-plus-1s

[1]: https://github.com/facebook/Haxl

[arjvik]

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

can you explain what this means?

[eurasiantiger]

So, in other words, you want GraphQL.

[akx]

A dataloader sort of pattern tends to be on the implementing end of GraphQL.

[eurasiantiger]

Indeed, the parent was describing built-in features of the Apollo GraphQL Client, such as entity-level caching in the frontend.

However, a dataloader pattern can also be useful when implementing custom resolvers for a GraphQL API. For this purpose, Apollo provides data sources which handle caching at request level.

[mattbillenstein]

Now you have like three problems instead of one - N+1 queries in the cold-cache case is slow, cache invalidation when something changes, and much more overall complexity...

[nickjj]

> cache invalidation when something changes, and much more overall complexity

With Rails this style of caching is built into the framework, it's not too bad. It can be applied to other frameworks too because most of the "magic" is how the cache key is composed. Lots of frameworks have the bits and pieces to pull it off.

Here's a snippet from the Rails docs on caching[0]:

    <% @products.each do |product| %>
      <% cache product do %>
        <%= render product %>
      <% end %>
    <% end %>

That will automatically produce cache keys that look like this:

    views/products/index:bea67108094918eeba42cd4a6e786901/products/1

The key is scoped to the view responsible for rendering the snippet of HTML and each product. If any attribute of that product changes that would result in different HTML being produced then the cache will be busted automatically by Rails because that hash in the middle of the cache key will be different. You can also "help" Rails when it comes to associations by adding a `touch: true` argument to your relationship once at the model level so Rails knows to bust the cache if the association's attributes change.

Like everything with caching, it's not 100% bullet proof and perfect but if you know the rules of how the framework works you can solve 95% of your caching needs without much headache. For the other 5% you have options but you can keep them on a need to know basis and look them up when you encounter issues (you may never need them in the end).

[0]: https://guides.rubyonrails.org/caching_with_rails.html#fragm...

[Suor]

cacheops can do the same, but it never came to my mind to actually go this way. I would rather write some special code to fetch with `id__in=[...]` and then cache individually.

[hinkley]

Depending on cache timing you can get N/2 copies of old data and N/2 copies of new data. And then any recursive calls that also need cached values grab the new Data but now half of your request is using all new data, and half is using half old data, leading to confusing results.

[jayd16]

These are just the problems with any cache. And still, caches are quite useful.

[ralmidani]

Also, off-by-one errors.

[eurasiantiger]

GraphQL can handle all of this in an elegant manner.

[javamanfossils]

Could you be more specific? GraphQL itself as a query language does not handle much. For a given query, a GraphQL back-end normally needs to do some non-trivial work with the database. Doing that efficiently is hard. It is easy to encounter all the typical problems here. I don't see how GraphQL particularly helps.

[zdragnar]

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.

[ilyt]

> 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.

Once you have few million users then you can think about better solution.

You can fit a lot into server memory, and spilling out of RAM to NVMe isn't that bad either.

[Kamq]

> 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 more traffic than anything else (and probably a few more orders of magnitude more than the median post).

[zdragnar]

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.

[fiddlerwoaroof]

The downside here is a potential thundering herd issue if you’re forced to clear the cache.

[ilyt]

cache with grace period ("serve old record while new is updating") is good solution here

[tatersolid]

That doesn’t help on process/container/VM restarts.

[ilyt]

Right. Or just let the DB do the caching. If you're not making million user app or have hundreds of gigabytes to query it will be faster than anything done in Ruby code anyway.

> If you render the view without any cache with 10 things then you'd perform 20 queries but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query.

So instead of "do one query to the database, zero if cache is hot, one query again if you invalidate it", you do 20 queries, zero if cache is hot (but 20 cache queries), then 1 if you invalidate it.

That's nice method when single entry query is very expensive but in most cases it will be living in DB cache already.

It is only a win if DB is the bottleneck which by far is rarer case nowadays than RTT. Also you could just query all 20 records at once but write it to cache as separate records, then have write-thru logic for updating records. Best of both at cost of some complexity.

You can also just... cache the resulting page instead of data-level caching, and not engage app code in most requests in the first place. Partial caching with ESI is also possible although pretty involved.

[nickjj]

> zero if cache is hot (but 20 cache queries)

Rails lets you fetch_multi which means it would be 1 cache query if you used this feature.

> It is only a win if DB is the bottleneck

View rendering is pretty slow in Rails, rendering a view ends up being a real bottleneck. That's partly why caching that at the view layer is beneficial.

If Rails were able to cache templates as well as other frameworks then I would agree that in a lot of cases you wouldn't need to worry about it and could preload most things.

[JeremyNT]

> If you render the view without any cache with 10 things then you'd perform 20 queries but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query.

The technique you describe makes sense, but how often is that preload query actually that expensive? Usually if I see an expensive query in Rails, it's because either 1) the indexes are missing for the joins or 2) you're instantiating extra full-blown objects when only some tiny amount of data of a primitive type is required.

This kind of stuff is a good opportunity to just write the query directly that gets the data you really do need, rather than relying on the ORM and its overhead.

If the problem is "maybe the user will want to see this, maybe they won't" then in many cases the easiest win is to lazy load the more detailed view with a new query that is only fired on some user interaction.

[Suor]

Writing things directly is tightly coupling code. Sometimes quite distant portions of it, i.e. database access and presentation logic. With ORM and some smarter lazy technics or introspection at least you can untie it, with hand written SQL there is no way. And the reason is strings are poorly composable. Unless you use some query generator, but then we are back to ORM-like something.

[JeremyNT]

Of course, it's not a one-size fits all solution. I agree that ORMs are incredibly helpful and using them is almost always the right place to start for this sort of thing because they are very flexible and take away the cognitive load.

But we're talking about performance here... if/when you start to have issues, it is time to take a look and see where you can do better.

Rails' ActiveRecord gives you tools to help build queries whether or not you materialize them as full blown objects. If you need to do some specific data extraction but want to avoid a bunch of needless instantiation, you can still leverage the ORM to help build the queries (and avoid writing raw SQL as strings in your codebase) which is a very helpful halfway point.