PostgreSQL Materialized Views in Rails

Materialized view is a object that contains the query’s results. Unlike database table it doesn’t support INSERT/UPDATE/DELETE opertaions. Since all this operations unsupported to update materialized view you need to call refresh view opertaion. In PostgreSQL materialized views support was introduced in version 9.3. From PostgreSQL documentation you can see how to create materialized view. So, you need query that you want to materialize and… that all.

Let’s assume you have next structure: books, authors and feedbacks. Each author has many books, each book has many feedbacks

# author.rb
class Author < ActiveRecord::Base
  has_many :books
  has_many :feedbacks, through: :books
end

# book.rb
class Book < ActiveRecord::Base
  belongs_to :author
  has_many :feedbacks
end

# feedback.rb
class Feedback < ActiveRecord::Base
  belongs_to :book
end

And let’s assume you need to we need to show top authors by their feedbacks. We have Rails ActiveRecord and it seems easy

Author.joins(:feedbacks, :books)
      .group("authors.name")
      .order("sum(feedbacks.mark) DESC")
      .sum("feedbacks.mark")
# {"Alexander Pushkin"=>360, "Mikhail Lermontov"=>330, "Nikolai Gogol"=>180}

But three INNER JOINS, isn’t too much? Let’s use our materialized views! Starting from migration:

class CreateAuthorsFeedbacks < ActiveRecord::Migration
  def self.up
    query = %Q{
      CREATE MATERIALIZED VIEW authors_feedbacks  AS
      #{Author.joins(:feedbacks, :books)
          .select("sum(feedbacks.mark)", "authors.name", "authors.id as author_id")
          .group("authors.name", "authors.id")
          .order("sum(feedbacks.mark) DESC")
          .to_sql};
    }
    execute query
    add_index :authors_feedbacks, :author_id, unique: true
  end

  def self.down
    execute <<-SQL
      DROP MATERIALIZED VIEW IF EXISTS authors_feedbacks;
    SQL
  end
end

To be honest, I’m not big fun of using Rails to_sql method and so on and prefer to write pure SQL for such migrations. But it’s sample and we will keep it so. Each selected column will be materialized view column, that’s why we used as for authors.id, in our table “authors.id” will be stored in “author_id” column. Unlike simple views, we can index any materialized view column, additionaly, we will make it index unique. As I said before, to actualize data in view we need to call refresh view. In pure PostgreSQL it will be:

REFRESH MATERIALIZED VIEW authors_feedbacks

But in PostgreSQL 9.4 we can do it concurrently! It will refresh the materialized view without locking out concurrent selects on the materialized view, but… You need uniq index on materialized view for this. Wait a minute, we alredy have one!

REFRESH MATERIALIZED VIEW CONCURRENTLY authors_feedbacks

As I can see, concurrently refreshing much more quick if your index very simple. With complex index it can be even slower then unconcurrent refreshing. As it behave like table we can even use it as ActiveRecord model.

# authors_feedback.rb
class AuthorsFeedback < ActiveRecord::Base
  include ReadOnlyModel

  belongs_to :author

  # Refresh materialized view by reaggregating data from each connected table
  def self.refresh_view
    connection = ActiveRecord::Base.connection
    connection.execute("REFRESH MATERIALIZED VIEW #{table_name}")
  end

  # Concurrently refresh materialized view by reaggregating data from each
  # connected table
  def self.refresh_view_concurrently
    connection = ActiveRecord::Base.connection
    connection.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY #{table_name}")
  end
end

As I said before, materialized view doesn’t support any editing operations, for this we will use concern ReadOnlyModel

module ReadOnlyModel
  extend ActiveSupport::Concern

  included do
    # Required to block update_attribute and update_column
    attr_readonly(*column_names)
  end

  def readonly?
    # Does not block destroy or delete
    true
  end

  def destroy
    raise ActiveRecord::ReadOnlyRecord
  end

  def delete
    raise ActiveRecord::ReadOnlyRecord
  end
end

And we need to add association for authors

class Author < ActiveRecord::Base
  has_many :books
  has_many :feedbacks, through: :books
  has_one  :authors_feedback
end

And that’s all, just use it.

Author.last.authors_feedback
# => #<AuthorsFeedback sum: 180, name: "Nikolai Gogol", author_id: 3>
AuthorsFeedback.all
#=> #<ActiveRecord::Relation [
  #<AuthorsFeedback sum: 360, name: "Alexander Pushkin", author_id: 1>,
  #<AuthorsFeedback sum: 330, name: "Mikhail Lermontov", author_id: 2>,
  #<AuthorsFeedback sum: 180, name: "Nikolai Gogol", author_id: 3>
#]>

P.S. Don’t forget to chnage your dump format, schema.rb can’t store materialized view structure, so, you need to store all this in SQL. Change in application.rb

config.active_record.schema_format = :sql