Rails Materialized Views with Scenic: Fast Postgres Read Models Without Killing Writes

The dashboard took eleven seconds to load on a good day. On a bad day, the p95 was closer to thirty. It was a SaaS analytics screen for a marketplace, and it aggregated eight tables — orders, order items, sellers, buyers, disputes, refunds, payouts and a category tree — into a single “seller health” grid. The team had already added every index Postgres was willing to accept, denormalised two counters into the sellers table, and reached for Redis. That is the point at which the CTO called me and asked the question I hear at least once a quarter: “Should we shard, or should we move this to a read replica?” The answer that afternoon was neither. The answer was Rails materialized views with the Scenic gem, a nightly refresh, and about ninety minutes of work.

After nineteen years of Rails I have watched teams reach for Elasticsearch, ClickHouse, or a data warehouse when the actual problem is that a handful of expensive read queries are running on every page load against tables optimised for writes. Rails materialized views are the boring answer that keeps working. Postgres computes the aggregate once, stores it on disk like a table, and lets you index it as if it were one. The Scenic gem gives Rails a first-class way to version those views in migrations. This post is the playbook I hand teams when a reporting query becomes the outage they did not plan for.

What a Materialized View Actually Is

A regular Postgres view is a saved query. Every time you SELECT from it, Postgres runs the underlying SQL. That is fine for simple joins and terrible for anything that aggregates a million rows.

A materialized view is a saved query whose result is written to disk as a physical table. Reads hit that table directly — no joins, no aggregations, no sorting at query time. The tradeoff is that the data is stale until you explicitly REFRESH MATERIALIZED VIEW. In practice, most reporting workloads are perfectly happy with data that is thirty seconds, five minutes, or an hour old. Your leadership team looking at “sellers with the most disputes this week” does not need real-time truth. They need a page that loads in 80 milliseconds.

The right mental model: a materialized view is a read model built out of your write-side tables. You keep the source of truth normalised, you keep the reporting shape denormalised, and you decide the refresh cadence per view.

Why Not Just Cache the Query in Rails

I get asked this every time. “We could just wrap it in Rails.cache.fetch.” You can, and for a lot of one-off endpoints that is the right move. But a Rails cache has three failure modes that a materialized view does not.

First, invalidation. The moment two writers can invalidate the same key, you are back in the “why is this stale for one user and fresh for another” bug ticket. Second, the query still runs on cold cache — right after a deploy, at 03:00 when Redis evicts, on Black Friday when a customer refreshes twice. Third, you cannot index the cache. You cannot say “sort seller health by dispute rate, filter by category, paginate.” A materialized view is a real table. You can add CREATE INDEX seller_health_dispute_rate_idx ON seller_health (dispute_rate DESC) and every filter/sort combination is fast.

Postgres also gives you REFRESH MATERIALIZED VIEW CONCURRENTLY, which lets readers keep hitting the old copy while the new one is being built. Redis has no equivalent. If you want more on Rails-native caching options, I compare them in Rails caching beyond basics.

Installing Scenic and Writing Your First View

Scenic is the boring, well-maintained gem for managing Postgres views inside Rails migrations. Add it and generate your first view:

# Gemfile
gem "scenic", "~> 1.8"

bundle install
bin/rails generate scenic:model seller_health

Scenic creates three things: a migration that will CREATE VIEW seller_health, a model file (app/models/seller_health.rb) that is a read-only ActiveRecord class, and a SQL file at db/views/seller_health_v01.sql where you write the actual query.

Here is the SQL for the dashboard we replaced:

-- db/views/seller_health_v01.sql
SELECT
  s.id                                                AS seller_id,
  s.tenant_id                                         AS tenant_id,
  s.name                                              AS seller_name,
  COUNT(DISTINCT o.id)                                AS orders_30d,
  COALESCE(SUM(oi.total_cents), 0)                    AS gmv_cents_30d,
  COUNT(DISTINCT d.id)                                AS disputes_30d,
  CASE
    WHEN COUNT(DISTINCT o.id) = 0 THEN 0
    ELSE (COUNT(DISTINCT d.id)::float / COUNT(DISTINCT o.id)::float)
  END                                                 AS dispute_rate_30d,
  MAX(o.created_at)                                   AS last_order_at
FROM sellers s
LEFT JOIN orders o
  ON o.seller_id = s.id
 AND o.created_at >= NOW() - INTERVAL '30 days'
LEFT JOIN order_items oi
  ON oi.order_id = o.id
LEFT JOIN disputes d
  ON d.order_id = o.id
 AND d.created_at >= NOW() - INTERVAL '30 days'
GROUP BY s.id, s.tenant_id, s.name;

Now turn it into a materialized view. Scenic supports this with a single option:

# db/migrate/20260705120000_create_seller_health.rb
class CreateSellerHealth < ActiveRecord::Migration[8.0]
  def change
    create_view :seller_health, materialized: true
  end
end

Run the migration and Postgres builds the view once from the SQL file. Now SellerHealth.count returns instantly because it is querying a physical table.

The Model Layer That Makes This Feel Native Rails

Scenic’s generator gives you a read-only ActiveRecord model. Wire it into your write-side model so the code reads naturally:

# app/models/seller_health.rb
class SellerHealth < ApplicationRecord
  self.primary_key = :seller_id

  belongs_to :seller
  belongs_to :tenant

  scope :top_gmv,       -> { order(gmv_cents_30d: :desc) }
  scope :high_disputes, -> { where("dispute_rate_30d > ?", 0.05) }

  def readonly?
    true
  end
end

# app/models/seller.rb
class Seller < ApplicationRecord
  has_one :health, class_name: "SellerHealth", foreign_key: :seller_id
end

Two things worth flagging. First, self.primary_key = :seller_id. Postgres materialized views do not have a synthesized id column unless you write one, and Rails freaks out about that. Pick a stable identifier from your source data as the primary key. Second, override readonly?. Scenic will warn you if you forget, but I have watched a junior developer call SellerHealth.first.update!(...) in the console and stare at the error for twenty minutes. Be explicit.

Now the controller reads the way you always wanted:

# app/controllers/admin/sellers_controller.rb
class Admin::SellersController < AdminController
  def index
    @sellers = SellerHealth
      .where(tenant_id: Current.tenant.id)
      .high_disputes
      .top_gmv
      .page(params[:page])
  end
end

The eleven-second dashboard is now eighty milliseconds. Not because I optimised the query, but because I stopped running the query on every request.

Refreshing Without Blocking Writes

This is the part that separates a working prototype from something you actually put in production. REFRESH MATERIALIZED VIEW seller_health takes an exclusive lock. Reads and writes on the view block until it finishes. On a 40M-row source, that is minutes of downtime.

REFRESH MATERIALIZED VIEW CONCURRENTLY seller_health builds the new version in a scratch area and swaps it in atomically. Reads continue against the old version the whole time. There is one prerequisite: the view must have at least one unique index. Add it via Scenic’s add_index in a follow-up migration:

# db/migrate/20260705120100_add_indexes_to_seller_health.rb
class AddIndexesToSellerHealth < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :seller_health, :seller_id,
              unique: true, algorithm: :concurrently
    add_index :seller_health, [:tenant_id, :dispute_rate_30d],
              algorithm: :concurrently
    add_index :seller_health, [:tenant_id, :gmv_cents_30d],
              algorithm: :concurrently
  end
end

Now the refresh job is boring:

# app/jobs/refresh_seller_health_job.rb
class RefreshSellerHealthJob < ApplicationJob
  queue_as :low

  def perform
    Scenic.database.refresh_materialized_view(
      :seller_health,
      concurrently: true,
      cascade: false
    )
  end
end

Schedule it with Solid Queue’s recurring jobs (see Solid Queue recurring jobs) or plain cron:

# config/recurring.yml
production:
  refresh_seller_health:
    class: RefreshSellerHealthJob
    schedule: every 5 minutes

Five minutes is a reasonable default for most dashboards. For nightly reporting views, once a night at a low-traffic hour is fine. For a “leaderboard” that people expect to feel live, thirty seconds is achievable — but at that point you should be asking whether an event-driven denormalised counter is a better fit than a view.

If Strong Migrations is set up on your project it will flag any non-concurrent refresh or missing unique index. It is worth having — I wrote about it in Rails Strong Migrations.

Versioning Views Like Migrations

The reason to use Scenic instead of writing raw SQL is that views change, and change is where teams break production. Say the product team wants to add a refunds_30d column. Do not edit db/views/seller_health_v01.sql. Generate a new version:

bin/rails generate scenic:view seller_health

Scenic copies the previous SQL to db/views/seller_health_v02.sql and generates a migration:

class UpdateSellerHealthToVersion2 < ActiveRecord::Migration[8.0]
  def change
    update_view :seller_health,
                version: 2,
                revert_to_version: 1,
                materialized: { no_data: false }
  end
end

Edit v02.sql, add your new column, and deploy. The migration replaces the view atomically and can be rolled back to v01. Your db/views/ directory becomes a versioned history of every schema decision the reporting layer has made. When a data engineer asks “when did we start tracking dispute rate,” git log db/views/ answers in one command.

When Materialized Views Are the Wrong Answer

I have shipped this pattern to probably twenty companies. There are three cases where I refuse to.

The first is data that must be strictly consistent with a user’s own write. If a user submits an order and expects to see it on the next screen, do not read from a view that refreshes every five minutes. Read from the source table for that user’s own recent activity, and use the view for the aggregate context. You can even query both in one endpoint.

The second is a view whose full refresh takes longer than your acceptable staleness. If the query behind the view takes ninety seconds to run and you want five-minute freshness, you are going to burn a chunk of a Postgres core continuously. At that point, an incremental read model built with triggers, or a proper event-sourced projection (see Rails event sourcing) is the right call.

The third is data that is genuinely per-user and unbounded — 500k users each with their own dashboard slice. Materializing that view means materialising 500k slices. The right answer there is usually a real-time query with the right indexes, or a per-tenant partitioned table. I go into partitioning in Rails Postgres table partitioning.

Everywhere else — leaderboards, admin dashboards, cohort reports, seller/buyer health, revenue rollups, moderation queues — a materialized view refreshed concurrently on a cadence is the answer you should try before you type the word “Kafka.”

Monitoring That the Refresh Is Actually Happening

The failure mode nobody sees coming is the refresh job that silently stops running. Your dashboard keeps showing data. It is just week-old data. Two things I put in every Rails app that uses materialized views.

First, expose the last refresh timestamp on the view itself. Add a refreshed_at column via a small trick — join to a metadata table or wrap the refresh in a job that also writes to a matview_refreshes table:

class RefreshSellerHealthJob < ApplicationJob
  queue_as :low

  def perform
    Scenic.database.refresh_materialized_view(:seller_health, concurrently: true)
    MatviewRefresh.create!(name: "seller_health", refreshed_at: Time.current)
  end
end

Then a controller filter or a small footer partial reads MatviewRefresh.where(name: "seller_health").last.refreshed_at and renders “data as of 3 minutes ago.” Users trust a stale-but-labelled dashboard. They lose faith in a dashboard that lies about being live.

Second, alert on staleness. Something like:

# In a periodic health check job
class MatviewFreshnessCheckJob < ApplicationJob
  STALENESS = { "seller_health" => 15.minutes, "revenue_rollup" => 2.hours }

  def perform
    STALENESS.each do |name, threshold|
      last = MatviewRefresh.where(name: name).order(:refreshed_at).last
      if last.nil? || last.refreshed_at < threshold.ago
        Sentry.capture_message("Matview #{name} is stale", level: :error)
      end
    end
  end
end

Wire this into your error tracker (I wrote about that setup in Rails Sentry error tracking) and the failure mode where “the refresh job died silently in July” cannot happen unnoticed.

The Cost You Pay and Why It Is Worth It

Materialized views are not free. Every view is a copy of data, so your disk usage grows. The refresh burns CPU proportional to the source query’s cost. You add a job to the queue that has to actually run, and one more thing that can be misconfigured.

The cost you save is bigger and comes in three shapes. Response time drops from seconds to milliseconds for expensive read paths. Database load stops spiking every time the marketing team opens the admin. And most importantly, you decouple write-side schema decisions from read-side product decisions. Product wants a new report next quarter? You add a view. You do not add indexes to a write-heavy table to make a report faster and then wonder why the write path got slower.

For most Rails apps between one and fifty million rows in the hot tables, this is the single most cost-effective performance investment you can make. It buys you a year or two before you have to think seriously about read replicas, sharding, or a warehouse. And when you do outgrow it, the views become the natural contract for what to load into whatever comes next.

FAQ

How often should I refresh a Rails materialized view?

Match the refresh cadence to the freshness the product actually requires. Executive dashboards are fine at hourly. Ops dashboards are usually happy at five minutes. Leaderboards visible to end users typically want thirty to sixty seconds. Nightly reporting views should refresh once a night at a low-traffic hour. Always render a “data as of X ago” indicator so users know what they are looking at.

Can I query a materialized view with ActiveRecord like a normal model?

Yes. Scenic generates a normal ActiveRecord model backed by the view. You can call where, order, joins, use scopes, and paginate exactly as with any table. Set self.primary_key explicitly and override readonly? to return true so nothing tries to write to it. You can even join the view to your normal write-side models — the view is just a table to Postgres.

What is the difference between a Postgres view and a materialized view in Rails?

A regular Postgres view is a saved query that re-runs on every SELECT. A materialized view stores the query result on disk as a physical table and only re-runs when you REFRESH. Reads from a materialized view are fast and can be indexed, at the cost of staleness. Use a regular view for lightweight abstractions over one or two tables, and a materialized view when the underlying query is expensive to run repeatedly.

Does REFRESH MATERIALIZED VIEW CONCURRENTLY require a unique index?

Yes. Postgres uses the unique index to reconcile old and new rows during the concurrent refresh. Without it, the CONCURRENTLY keyword throws an error. Add a unique index on the natural primary key of the view — usually the source model’s id — using add_index :view_name, :id, unique: true, algorithm: :concurrently in a Scenic migration. This is the single most common Scenic setup mistake I see.

Need help turning a slow Rails reporting layer into a boring, fast, well-monitored one? TTB Software specialises in Rails performance, Postgres tuning, and the read-model patterns that actually scale. We have been doing this for nineteen years.