Materialized Views in Postgres: Trading Freshness for Speed

Every dashboard query that aggregates millions of rows on the fly is a ticking time bomb. It works fine with test data, gets slow with real data, and eventually someone adds a cron job that runs the same expensive query every five minutes "just to cache it." Postgres already has a primitive for this — materialized views — and most teams either don't know it exists or use it wrong.

The core tradeoff is simple: materialized views trade freshness for speed. The hard part isn't creating them; it's deciding how stale is acceptable and building a refresh strategy that doesn't lock your application while it runs.

What a Materialized View Actually Buys You

A regular view is just a saved query — every time you select from it, Postgres runs the underlying SQL. A materialized view runs the query once, stores the result set as a physical table, and serves reads from that snapshot until you explicitly refresh it.

-- ❌ Recomputed on every request, scans the full orders table
CREATE VIEW daily_revenue AS
SELECT
  date_trunc('day', created_at) AS day,
  sum(amount) AS revenue,
  count(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1;
 
-- ✅ Computed once, stored as a table, read is a simple index scan
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  date_trunc('day', created_at) AS day,
  sum(amount) AS revenue,
  count(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1;
 
CREATE UNIQUE INDEX ON daily_revenue (day);

The unique index isn't optional decoration — without one, you can't use REFRESH MATERIALIZED VIEW CONCURRENTLY, and that changes everything about how disruptive refreshes are.

The Locking Problem Nobody Warns You About

The naive refresh command locks the view for reads during the entire rebuild:

-- ❌ Blocks all SELECTs against daily_revenue until the rebuild finishes
REFRESH MATERIALIZED VIEW daily_revenue;

On a small view this is invisible. On a view backing a customer-facing dashboard with a few seconds of rebuild time, it means intermittent query timeouts every time the refresh job runs. I've seen this cause a production incident where a "harmless" nightly refresh job started taking 40 seconds after a data volume spike, and every dashboard request during that window returned a 504.

CONCURRENTLY fixes this by building a new copy of the data alongside the old one and swapping atomically:

-- ✅ Readers keep hitting the old snapshot until the new one is ready
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

It requires the unique index mentioned above, costs more disk and CPU during the refresh (it's building a second copy, not truncating in place), and takes longer wall-clock time. That's the tradeoff: concurrent refreshes are slower but non-blocking. For anything user-facing, non-blocking wins every time.

Scheduling Refreshes Without a Cron Job Sprawl

Most teams wire up pg_cron or an external scheduler to call REFRESH on a timer. That works, but a fixed interval is a blunt instrument — you're either refreshing too often (wasted CPU) or too rarely (stale data during traffic spikes).

A better pattern is to make refreshes event-driven, triggered by the write path that actually invalidates the data:

-- Track staleness explicitly instead of guessing
CREATE TABLE view_refresh_state (
  view_name text PRIMARY KEY,
  last_refreshed_at timestamptz NOT NULL DEFAULT now(),
  is_stale boolean NOT NULL DEFAULT false
);
 
-- Mark stale whenever an order completes
CREATE OR REPLACE FUNCTION mark_revenue_stale()
RETURNS trigger AS $$
BEGIN
  UPDATE view_refresh_state
  SET is_stale = true
  WHERE view_name = 'daily_revenue';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_mark_stale
AFTER INSERT OR UPDATE OF status ON orders
FOR EACH ROW
WHEN (NEW.status = 'completed')
EXECUTE FUNCTION mark_revenue_stale();

A worker polls view_refresh_state, refreshes only views flagged is_stale, and resets the flag on success. You get refreshes that happen shortly after real writes instead of on an arbitrary clock, and you can expose last_refreshed_at directly in the UI so users know exactly how fresh the data is — which matters more than most teams admit.

Handling Downstream Query Patterns

Materialized views shine for aggregation-heavy read paths, but they're not a substitute for proper indexing on the base tables, and they don't compose well with row-level security if you need per-tenant filtering baked into the same view.

-- ❌ One giant view mixing all tenants — RLS can't help you here
CREATE MATERIALIZED VIEW tenant_revenue AS
SELECT tenant_id, date_trunc('day', created_at) AS day, sum(amount) AS revenue
FROM orders
GROUP BY 1, 2;
 
-- ✅ Filter at query time on top of the materialized aggregate,
-- with an index that makes the filter cheap
CREATE INDEX ON tenant_revenue (tenant_id, day);
 
SELECT day, revenue
FROM tenant_revenue
WHERE tenant_id = $1
ORDER BY day DESC
LIMIT 30;

If tenant isolation is a hard security requirement, don't rely on materialized views alone — enforce it at the application layer or through a security-definer function that wraps the query with the correct WHERE tenant_id = current_tenant() clause.

When Materialized Views Are the Wrong Tool

The mistake I see most often is reaching for a materialized view as a generic "make it fast" button without asking how often the underlying data actually changes. If your orders table gets thousands of writes per second, a naive refresh-on-every-write trigger will hammer your refresh worker constantly — you need debouncing or batched refresh windows, not per-row triggers.

Debounced Refresh in Practice

// ❌ Refreshes on every single write — thrashes the database
async function onOrderCompleted(order: Order) {
  await db.query("REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue");
}
 
// ✅ Batches refreshes with a debounce window
class ViewRefresher {
  private pending = new Map<string, NodeJS.Timeout>();
 
  schedule(viewName: string, debounceMs = 5000): void {
    const existing = this.pending.get(viewName);
    if (existing) clearTimeout(existing);
 
    const timer = setTimeout(async () => {
      this.pending.delete(viewName);
      try {
        await db.query(
          `REFRESH MATERIALIZED VIEW CONCURRENTLY ${viewName}`,
        );
      } catch (error) {
        console.error(`Refresh failed for ${viewName}`, error);
        // Retry logic or dead-letter queue goes here
      }
    }, debounceMs);
 
    this.pending.set(viewName, timer);
  }
}

This gives you predictable freshness (worst case: debounceMs behind real time) without turning every write into a database-wide rebuild.

Key Takeaways

1. Always create a unique index on materialized views you plan to refresh concurrently — it's the difference between a non-blocking refresh and a production outage.
2. Expose staleness to users rather than hiding it — a "last updated 2 minutes ago" label builds more trust than silent inconsistency.
3. Trigger refreshes from writes, not fixed timers — event-driven invalidation keeps data fresher without wasting cycles on unchanged data.
4. Debounce refresh triggers on high-write tables to avoid turning every insert into a full view rebuild.
5. Don't use materialized views for real-time requirements or tenant-scoped ACLs — they solve aggregation cost, not data freshness or access control.

Materialized views are one of the highest-leverage tools in Postgres for read-heavy analytical workloads, but they're often deployed carelessly — as a cache with no invalidation strategy and no visibility into staleness. Treat the refresh strategy as a first-class design decision, not an afterthought, and they'll save you from building a separate caching layer for problems Postgres already solves.