class CreateTopSellersMv < ActiveRecord::Migration[7.0]
def up
execute <<~SQL
CREATE MATERIALIZED VIEW top_sellers AS
SELECT p.id AS product_id,
p.name AS product_name,
SUM(oi.quantity) AS total_sold,
SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 100
WITH DATA;
SQL
# Required for REFRESH ... CONCURRENTLY
execute "CREATE UNIQUE INDEX index_top_sellers_on_product_id ON top_sellers (product_id);"
end
def down
execute "DROP MATERIALIZED VIEW IF EXISTS top_sellers;"
end
end
class TopSeller < ApplicationRecord
self.table_name = "top_sellers"
self.primary_key = "product_id"
after_initialize { readonly! }
scope :ranked, ->(limit = 25) { order(total_sold: :desc).limit(limit) }
def self.refresh!
connection.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY top_sellers;")
end
def self.cache_version
Rails.cache.read("top_sellers:version").to_i
end
def self.bump_cache_version!
Rails.cache.increment("top_sellers:version", 1, initial: 1)
end
def revenue_dollars
revenue_cents.to_i / 100.0
end
end
class RefreshTopSellersJob
include Sidekiq::Job
sidekiq_options queue: :maintenance, retry: 2
LOCK_KEY = "lock:refresh_top_sellers".freeze
LOCK_TTL = 5.minutes.to_i
def perform
return unless acquire_lock
TopSeller.refresh!
TopSeller.bump_cache_version!
Rails.logger.info("[RefreshTopSellersJob] refreshed at #{Time.current.iso8601}")
ensure
release_lock
end
private
def acquire_lock
Sidekiq.redis { |r| r.set(LOCK_KEY, jid, nx: true, ex: LOCK_TTL) }
end
def release_lock
Sidekiq.redis do |r|
r.del(LOCK_KEY) if r.get(LOCK_KEY) == jid
end
end
end
class LeaderboardController < ApplicationController
def index
version = TopSeller.cache_version
@sellers = Rails.cache.fetch("top_sellers:list:v#{version}", expires_in: 10.minutes) do
TopSeller.ranked(25).map do |s|
{
product_id: s.product_id,
name: s.product_name,
units: s.total_sold,
revenue: s.revenue_dollars
}
end
end
render json: { generated_version: version, sellers: @sellers }
end
end
Computing a "top N" list — best-selling products, highest-scoring players — on every request is expensive because it forces the database to scan, aggregate, and sort large tables repeatedly. A materialized view solves this by precomputing the aggregate once and storing the result as a physical table that reads are cheap against. This snippet shows the full loop in Rails: a migration that defines the view, a read-only model that queries it, and a background job that refreshes it on a schedule.
The CreateTopSellersMV migration defines top_sellers with execute because materialized views are not part of the ActiveRecord schema DSL. The aggregation joins order_items to products, sums quantities, and keeps only the top 100 rows via ORDER BY ... LIMIT. Crucially it also creates a UNIQUE INDEX on product_id; that index is not an optimization here — it is a hard prerequisite for REFRESH MATERIALIZED VIEW CONCURRENTLY, which Postgres refuses to run without one.
In TopSeller model, the class points at the view as its table_name and calls readonly! in an after_initialize hook so no code accidentally attempts to write to a view. The ranked scope orders by the precomputed total_sold and applies a LIMIT, and refresh! wraps the raw REFRESH ... CONCURRENTLY statement. Concurrent refresh is the key trade-off: it rebuilds the view without taking an ACCESS EXCLUSIVE lock, so readers keep seeing the old snapshot until the new one is ready — at the cost of doing more work and requiring that unique index.
The RefreshTopSellersJob is a Sidekiq worker that calls TopSeller.refresh! and then bumps a cache key so any application-level cache of the rendered list is invalidated in lockstep. It guards against overlap using a Redis SETNX lock, because two concurrent REFRESH CONCURRENTLY calls on the same view would serialize and waste resources.
The pattern fits data that tolerates slight staleness: the view is eventually consistent with the base tables, refreshed every few minutes rather than per write. Pitfalls include the unique-index requirement, the disk and CPU cost of frequent refreshes, and remembering to schedule the job. When freshness must be exact, a plain query or a trigger-maintained summary table is more appropriate instead.
Related snips
class CommentsController < ApplicationController
before_action :set_post
def create
@comment = @post.comments.build(comment_params)System test: asserting Turbo Stream responses
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
module Api
module V1
class UsersController < BaseController
def show
user = User.includes(:profile).find(params[:id])ETags for conditional requests and caching
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
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
Share this code
Here's the card — post it anywhere.
