ruby sql 68 lines · 4 tabs

Database Views for Read Models

Shared by codesnips Jan 2026

4 tabs

class CreateCustomerRevenueSummaries < ActiveRecord::Migration[7.0]
  def change
    create_view :customer_revenue_summaries, materialized: true, version: 1

    add_index :customer_revenue_summaries,
              :customer_id,
              unique: true,
              name: "idx_customer_revenue_summaries_on_customer_id"

    add_index :customer_revenue_summaries, :total_revenue_cents
  end
end

SELECT
  c.id                                   AS customer_id,
  c.name                                 AS customer_name,
  COUNT(DISTINCT o.id)                    AS orders_count,
  COALESCE(SUM(li.quantity * li.unit_price_cents), 0) AS total_revenue_cents,
  MAX(o.completed_at)                     AS last_order_at
FROM customers c
JOIN orders o
  ON o.customer_id = c.id
 JOIN line_items li
  ON li.order_id = o.id
WHERE o.status = 'completed'
GROUP BY c.id, c.name;

class CustomerRevenueSummary < ApplicationRecord
  self.primary_key = :customer_id

  belongs_to :customer, foreign_key: :customer_id, inverse_of: false

  scope :top_spenders, -> { order(total_revenue_cents: :desc) }
  scope :active_since, ->(time) { where("last_order_at >= ?", time) }

  def readonly?
    true
  end

  def total_revenue
    Money.new(total_revenue_cents, "USD")
  end

  def self.refresh
    Scenic.database.refresh_materialized_view(
      table_name,
      concurrently: true,
      cascade: false
    )
  end
end

class RefreshRevenueSummariesJob < ApplicationJob
  queue_as :low

  retry_on ActiveRecord::StatementInvalid, wait: 30.seconds, attempts: 3

  def perform
    started = Time.current
    CustomerRevenueSummary.refresh
    elapsed = (Time.current - started).round(2)

    Rails.logger.info(
      "[revenue] refreshed customer_revenue_summaries in #{elapsed}s"
    )
  rescue ActiveRecord::StatementInvalid => e
    # Concurrent refresh fails without a unique index; surface it loudly.
    Rails.logger.error("[revenue] refresh failed: #{e.message}")
    raise
  end
end

4 files · ruby, sql Explain with highlit

This snippet shows how to back a read-heavy reporting screen with a Postgres materialized view instead of computing aggregates on every request. The pattern is a lightweight form of CQRS: writes go to normalized tables (orders, line_items), while reads come from a denormalized, pre-aggregated view that is refreshed on a schedule. It trades absolute freshness for fast, predictable reads and a simple query surface.

The CreateCustomerRevenueSummaries migration uses the Scenic gem's create_view with materialized: true. Scenic keeps the SQL for each view version in a db/views directory, so the migration only names the view and its version rather than embedding a large SQL string inline. Because it is materialized, the view stores its result set on disk; a unique index on customer_id is added so the view can later be refreshed with CONCURRENTLY, which requires at least one unique index and avoids locking readers during a refresh.

The customer_revenue_summaries_v01.sql tab is the actual view definition. It joins orders to line_items, filters to completed orders, and rolls the data up per customer with count, sum, and max. This is exactly the kind of query that is expensive to run repeatedly and cheap to read once materialized.

In CustomerRevenueSummary model, the view is treated as an ordinary read-only Active Record model. readonly? returns true so no code accidentally tries to write through it, and refresh wraps Scenic.database.refresh_materialized_view with concurrently: true. The scopes (top_spenders, active_since) show that a view behaves like any other relation for querying and pagination.

The RefreshRevenueSummariesJob handles staleness. Materialized views do not update themselves, so a scheduled job calls CustomerRevenueSummary.refresh periodically; the concurrently refresh keeps the dashboard queryable throughout. The main pitfalls to weigh are refresh cost on large datasets, the bounded staleness window between refreshes, and the requirement that the unique index exists before a concurrent refresh will work. This approach fits dashboards, leaderboards, and analytics endpoints where slightly stale data is acceptable and read latency matters.

Related snips

ruby

class CommentsController < ApplicationController
  before_action :set_post

  def create
    @comment = @post.comments.build(comment_params)

System test: asserting Turbo Stream responses

rails hotwire turbo

by codesnips 4 tabs

ruby

class Post < ApplicationRecord
  belongs_to :author, class_name: 'User'
  has_many :comments, dependent: :destroy

  scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
  scope :draft, -> { where(published_at: nil) }

ActiveRecord scopes for reusable query logic

rails activerecord patterns

by Alex Kumar 1 tab

ruby

module Api
  module V1
    class UsersController < BaseController
      def show
        user = User.includes(:profile).find(params[:id])

ETags for conditional requests and caching

rails caching http-caching

by Alex Kumar 1 tab

ruby

class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author)
                 .order(created_at: :desc)
                 .page(params[:page])
                 .per(10)

Turbo Frames: infinite scroll with lazy-loading frame

rails turbo hotwire

by codesnips 4 tabs

package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions

by Leah Thompson 1 tab

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

Database Views for Read Models — share card

Link copied