How to avoid N+1 query using SQL views (materialized) in Rails application

Consider the way to reduce the queries when calculating the average values and find the place where to do it in the Ruby on Rails application #

In the first part, we considered the solution with extracting logic to the separate class and implementing the Facade pattern. SQL views are another way for solving the N+1 problem when finding the average value in Ruby on Rails application.

The Problem Setup #

There is a Ruby on Rails application with models Film and Review. Every Review has its own rate value.

class Film < ApplicationRecord
  has_many :reviews
end

class Review < ApplicationRecord
  belongs_to :film
end

We can calculate the average rate of each film:

class Film < ApplicationRecord
  has_many :reviews

  def avg_rating
    reviews.average(:rate)
  end
end

But when we need to show the average rates for the batch of films:

# frozen_string_literal: true

class FilmsController < ApplicationController
  def index
    render json: find_films
  end

  private

  def find_films
    Film.all.map do |film|
      {
        title: film.title,
        genre: film.genre,
        rate:  film.reviews.average(:rate)
      }
    end
  end
end

it will produce a lot of individual queries:

The issue is:

How to reduce the queries and where to do it?

Calculate average value on the database level #

Another way to reduce the number of DB queries is by utilizing SQL views. An SQL view is a searchable object in a database that is defined by a query. It has some features:

• A view doesn’t store data (Some refer to views as “virtual tables”)

• You can query a view like you can a table

• A view can combine data from two or more tables, using joins, and also just contain a subset of information

This makes them convenient to abstract, or hide, complicated queries.

Our query is looking like:

SELECT row_number() OVER () AS id,
AVG(rate) AS avg_rate,
Films.id AS film_id,
Films.title AS film_title,
Films.genre AS film_genre
FROM Reviews
RIGHT OUTER JOIN Films ON Reviews.film_id=Films.id
GROUP BY Films.id;

Now we can hook it up to our Ruby on Rails application:

• wrap our query in a CREATE VIEW statement

• create the migration with raw SQL in it

But it’s not exactly a Rails way. There is a useful tool for dealing with the database views in Ruby on Rails.

*Scenic* gem #

This gem gives us the ability to define migrations that create, update, or drop SQL views, just as we’re used to doing with regular tables in Ruby on Rails. By default Scenic gem supports PostgreSQL, but there are adapters for other databases.

Run in the terminal command:

rails g scenic:view film_rating

It will generate two files db/views/film_ratings_v01.sqland *db/migrate/[date]_create_film_ratings.rb.*In the first one, we will place the raw SQL query. The second file will contain the migration to migrate/rollback the creation of our view.

class CreateFilmRatings < ActiveRecord::Migration[6.0]
  def change
    create_view :film_ratings, materialized: true
  end
end

materialized: true — makes the view materialized. The difference is that such views save the result of the query to a cached/temporary table. When you query a materialized view, you aren’t querying the source data, rather the cached result.

Now we can create the related model and interact with it as with the usual Ruby on Rails model.

class FilmRating < ApplicationRecord
  def self.refresh
    Scenic.database.refresh_materialized_view(table_name, concurrently: false, cascade: false)
  end
end

As soon as materialized views cache the underlying query’s result to a temporary table we have to control when the cache is refreshed. A refresh method can be called at any time to update the data.

Now, we will deal with the FilmRatingin the controller

# frozen_string_literal: true

module SqlView
  class FilmsController < ApplicationController
    def index
      render json: find_films
    end

    private

    def find_films
      FilmRating.select(:film_title, :film_genre, :avg_rate).as_json(except: :id)
    end
  end
end

the query for getting average rates of all films will look like:

The pros of this way #

• Convenience (all features of Active Record are available).

• Easy to test.

• Performance, all heavy lifting is done by the Database.

• Aggregation values are precalculated in a case when the materialized view is used.

The cons of this way #

• Control when the cache needs to be refreshed

Sergey Sviridov is a Software Engineer at JetThoughts. Follow him on LinkedIn or GitHub.