Missing Materialized Views

Dashboards, admin pages, and periodic reports that recompute the same heavy aggregate query on every page load, when a materialized view refreshed on a schedule would return identical data in milliseconds.

Before / After

# Admin dashboard controller
def index
# 12-second query on every page load,
# joins 5 tables, aggregates 50M rows.
@metrics = Order
  .joins(:line_items, :customer)
  .where(created_at: 30.days.ago..)
  .group('DATE(orders.created_at)')
  .select(
    'DATE(orders.created_at) AS day,
     SUM(line_items.amount) AS revenue,
     COUNT(DISTINCT customer_id) AS customers'
  )
end

# db/migrate/20260424_create_daily_order_metrics_mv.rb
class CreateDailyOrderMetricsMv < ActiveRecord::Migration[7.1]
def up
  execute <<~SQL
    CREATE MATERIALIZED VIEW daily_order_metrics AS
    SELECT
      DATE(orders.created_at) AS day,
      SUM(line_items.amount) AS revenue,
      COUNT(DISTINCT customer_id) AS customers
    FROM orders
    JOIN line_items ON line_items.order_id = orders.id
    WHERE orders.created_at >= NOW() - INTERVAL '90 days'
    GROUP BY DATE(orders.created_at)
    WITH NO DATA;

    CREATE UNIQUE INDEX
      ON daily_order_metrics (day);
  SQL
end
end

# app/models/daily_order_metric.rb
class DailyOrderMetric < ApplicationRecord
self.primary_key = :day

def self.refresh
  connection.execute(
    'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_metrics'
  )
end
end

# Sidekiq cron: DailyOrderMetric.refresh every 5 minutes.
# Controller is now:
@metrics = DailyOrderMetric.where(day: 30.days.ago..)

Why this hurts

Every request recomputes the same aggregate over the same source rows, which pins a PostgreSQL worker backend for the duration of the query. On a 50M-row orders table joined with line_items and customers, a single pass can take 10-15 seconds of CPU time on the database host. Because PostgreSQL uses one process per connection, each concurrent dashboard session consumes a separate backend. Five admins opening the panel simultaneously saturate shared buffers, evict the hot OLTP working set from cache, and degrade performance on unrelated endpoints.

The query planner’s prepared statement cache offers no benefit. Dates shift per request (30.days.ago), so bind parameters differ across calls and each query requires plan selection. Sequential scans on old partitions populate the OS page cache with cold data, pushing hot indexes out. Autovacuum and analyze run on different priorities, so the first request after a vacuum pause sees the worst plan and the slowest execution.

Dashboard latency grows with business success: more orders, more line items, longer aggregation time. Product teams respond by narrowing the visible time window (“admin panel shows last 7 days only”), which is a product-level apology for a database-level problem. Replicas help throughput but do not help latency because the same query still runs end-to-end. Read-through caching at the Rails layer works until cache invalidation becomes complex, at which point the cache’s behavior is indistinguishable from a materialized view without the database engine’s transactional guarantees.

A materialized view refreshed every 5 minutes is the correct abstraction: the expensive work runs on a predictable schedule, dashboards read indexed rows, and REFRESH CONCURRENTLY allows the refresh to run without blocking reads.

See also: Rails schema.rb to structure.sql Migration.

Get Expert Help

Inheriting a legacy Rails codebase with this problem? Request a Technical Debt Audit.