When working with the Discourse rails application, whether building a plugin, or making a pull request to discourse/discourse there are a number of contexts where you’ll encounter N+1 query problems. This topic explains how to handle data preloading to address such problems and keep Discourse performant.
N+1 Query Problems in Rails
First, if you’re not already familiar with N+1 query problems, and how Rails addresses them, check out this section of the guides.
Topics
The N+1 issues to look out for when loading an topic are when you load tables associated with a post. Now, data serialized for an individual topic is prepared, and preloaded, in the TopicView class. That class has an on_preload hook which allows you to preload associated tables before it runs its primary query. There’s a good example of a use of TopicView.on_preload in the ActivityPub Plugin
This use of the hook is both preloading custom fields, which we’ll cover below, and loading additional associations for the posts in the topic. You’ll note that it’s using ActiveRecord::Associations::Preloader to do that. You can read about that class in the docs:
https://www.rubydoc.info/docs/rails/ActiveRecord/Associations/Preloader
Topic Lists
The N+1 issues to look out for when loading a topic list are when loading tables associated with a topic. Similar to the TopicView class for individual topics, there’s a TopicList class for topic lists. And just like TopicView, TopicList also has an on_preload method you can use to hook into the main topic list query to load associated tables before the query hits the database. There’s a good example of that in the Discourse Assign Plugin:
register_topic_preloader_associations
The TopicList class has a Server Plugin API method register_topic_preloader_associations, which essentially applies the same pattern we saw in the ActivityPub Plugin, using ``ActiveRecord::Associations::Preloader`. It will do that work for you if you pass it an array of associations.
register_topic_list_preload_user_ids
In addition to running one big query to get all the topics needed, the TopicList class also runs one big query to get all the users needed for the list, including each:
- topic user
- last post user
- topic featured user
- topic allowed users
If you’re loading other users for each topic, this api method lets you include their user ids in the big user query so their data will be preloaded along with the rest.
Categories
There’s a main list of categories used in various places in the site that is loaded, and cached, in the Site model. While this is technically not preloading in the strict sense, it’s doing a similar thing, so I thought I’d include it here.
site_all_categories_cache_query modifier
The all_categories_cache method has a Server Plugin API modifier which lets you modify the big categories query: site_all_categories_cache_query. You use this modifier like so in your plugin.rb:
register_modifier(:site_all_categories_cache_query) do |query|
query.where("categories.name LIKE 'Cool%'")
end
Search
Search preloading is also not technically described as such in the code, but it’s also doing a similar thing with its queries, i.e. eager loading.
register_search_topic_eager_load
The Server Plugin API method register_search_topic_eager_load allows you to eager load additional tables in Search. For example:
register_search_topic_eager_load do |opts|
%i(example_table)
end
Custom Fields
Custom Fields are powered by the HasCustomFields concern. That concern is included in the models that have associated custom fields, i.e. Post, Topic, Category and Group. The HasCustomFields module has its own preloading system which can be used in various ways. The best way of using HasCustomFields preloading is via the Server Plugin API methods, which should be relatively self explanatory given everything I’ve said so far.
register_preloaded_category_custom_fields
add_preloaded_group_custom_field
add_preloaded_topic_list_custom_field
You can also use the some of the lower level methods in HasCustomFields to do your own preloading when necessary, like we saw in the ActivityPub Plugin example. In that example HasCustomFields concern’s preload_custom_fields is used to preload post custom fields in TopicView.on_preload.
TopicView.on_preload do |topic_view|
##
Post.preload_custom_fields(topic_view.posts, activity_pub_post_custom_field_names)
##
end