Using Materialized Views in Ruby on Rails with PostgreSQL

When to Use and When to Avoid

Man, what a Senic view

Materialized views in PostgreSQL allow you to store the result of a complex query on disk, giving you fast, precomputed data. In Rails applications, they can be a great way to optimize performance — but only if used in the right circumstances. This article explores how to integrate materialized views with Rails, provides concrete examples, and discusses scenarios when you should or shouldn’t use them.

Why Materialized Views in Rails?

When you build a Rails application that interacts with PostgreSQL, you might encounter situations where queries involve multiple joins, aggregations, or subqueries. Running these queries on the fly can cause slow responses and heavy database loads. Materialized views solve this problem by:

Precomputing results: They execute and store complex queries once, allowing for rapid read operations.

Reducing load: They minimize the repetitive execution of expensive queries, especially useful for dashboards and reports.

Setting Up Materialized Views in Rails

Creating Materialized Views with Migrations

Rails migrations can manage your materialized views as part of your schema evolution. For example, you could create a migration like this:

class CreateSalesSummaryView < ActiveRecord::Migration[6.0]
  def up
    execute <<-SQL
      CREATE MATERIALIZED VIEW sales_summary AS
      SELECT product_id,
             COUNT(*) AS total_orders,
             SUM(amount) AS total_sales
      FROM orders
      GROUP BY product_id;
    SQL
  end

  def down
    execute "DROP MATERIALIZED VIEW IF EXISTS sales_summary"
  end
end

This migration makes sure that your view is created alongside your schema and can be versioned in source control.

Integrating with Rails Models

Even though a materialized view is not a table in the strict sense, you can wrap it in an ActiveRecord model for read operations:

class SalesSummary < ApplicationRecord
  self.table_name = "sales_summary"

  # Prevent updates as this view is read-only
  def readonly?
    true
  end
end

Using this model, you can query your view like any other ActiveRecord model:

# Example query to fetch data for a particular product
sales_data = SalesSummary.find_by(product_id: 42)

Using the Scenic Gem

For cleaner management, consider the Scenic gem. It simplifies creating and versioning both standard and materialized views. The process is similar:

Add Scenic to your Gemfile:

gem 'scenic'

Generate a materialized view:

rails generate scenic:view sales_summary --materialized

Edit the generated SQL file to define your view.

Use Materialized Views: Examples

Example 1: Reporting Dashboards

Scenario: You have a reporting dashboard that displays daily or hourly aggregated data (e.g., sales totals, user statistics).

Why Use:

• Fast retrieval: The heavy lifting is done once when the view is refreshed.
• Performance: It reduces the response time for users who rely on up-to-date but not real-time data.

Implementation Tip: Schedule background jobs (using Sidekiq or ActiveJob) to refresh the view periodically:

# In a background job
def perform
  ActiveRecord::Base.connection.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary")
end

Data Aggregation for Analytics

Scenario: An application requires complex aggregations over large datasets to generate insights (e.g., monthly active users, product popularity).

Why Use:

Resource efficiency: Aggregations that involve joining several tables or performing multiple calculations can be precomputed.

Simplification: Application logic remains clean since the heavy SQL is handled in the database layer.

Usage in Rails:
Wrap the view in a model, and use it for your analytics queries:

analytics_data = SalesSummary.where("total_sales > ?", 1000)

When NOT to Use Materialized Views: Counter Examples

Highly Transactional Data with Real-Time Requirements

Scenario: Your application requires up-to-the-second updates, such as a live bidding platform or a chat application.

Why Not Use:

Data staleness: Materialized views are static until refreshed. Relying on them in real-time systems can result in outdated data.

Refresh Overhead: Frequent refreshes may negate performance benefits and add complexity.

Simple, Lightweight Queries

Scenario: Your application performs simple queries that execute quickly without additional overhead (e.g., retrieving user profiles by ID).

Why Not Use:

Unnecessary Complexity: Materialized views add an extra layer to manage. If the query is trivial, the benefits do not outweigh the added maintenance.

Simplicity: Rely on standard queries and Rails caching if needed instead of introducing materialized views.

Conclusion

Materialized views in Rails with PostgreSQL can be a powerful tool when used appropriately. They excel in scenarios involving complex, read-heavy queries where precomputed data significantly boosts performance. However, they are not a one-size-fits-all solution — using them for real-time data or trivial queries can lead to unnecessary complexity and even performance drawbacks. By evaluating your application’s requirements and implementing best practices, you can harness the full potential of materialized views to build efficient, scalable Rails applications.