PostgreSQL Materialized Views in 2026: Refresh Strategies, Incremental Updates, and Query Rewriting

A materialized view is a saved query result stored as a physical table. Unlike regular views (which re-execute the query on every access), a materialized view lets you pay the computation cost once and read it many times at near-zero cost.

For SaaS dashboards—MRR charts, usage analytics, team activity summaries—materialized views can turn 5-second analytics queries into 10ms reads. This post covers creation, refresh strategies, the new pg_ivm extension for incremental refresh, and patterns for keeping dashboard data fresh without hammering your production database.

---

When to Use Materialized Views

---

Creating Materialized Views

-- Team MRR snapshot (recomputed from subscription events)
CREATE MATERIALIZED VIEW team_mrr_snapshot AS
SELECT
  s.team_id,
  t.name AS team_name,
  t.plan,
  COUNT(DISTINCT s.id) AS active_subscriptions,
  SUM(
    CASE
      WHEN s.billing_period = 'monthly' THEN s.amount
      WHEN s.billing_period = 'annual' THEN s.amount / 12.0
    END
  ) AS mrr_usd,
  MAX(s.created_at) AS latest_subscription_at,
  now() AS computed_at
FROM subscriptions s
JOIN teams t ON t.id = s.team_id
WHERE s.status = 'active'
GROUP BY s.team_id, t.name, t.plan;

-- Always create a unique index for CONCURRENT refresh
CREATE UNIQUE INDEX team_mrr_snapshot_team_id ON team_mrr_snapshot(team_id);

-- Per-project task analytics
CREATE MATERIALIZED VIEW project_task_stats AS
SELECT
  p.id AS project_id,
  p.team_id,
  p.name AS project_name,
  COUNT(*) AS total_tasks,
  COUNT(*) FILTER (WHERE t.status = 'done') AS completed_tasks,
  COUNT(*) FILTER (WHERE t.status = 'in_progress') AS in_progress_tasks,
  COUNT(*) FILTER (WHERE t.due_date < now() AND t.status != 'done') AS overdue_tasks,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE t.status = 'done') / NULLIF(COUNT(*), 0),
    1
  ) AS completion_pct,
  AVG(
    EXTRACT(EPOCH FROM (t.completed_at - t.created_at)) / 3600
  ) FILTER (WHERE t.completed_at IS NOT NULL) AS avg_completion_hours,
  MAX(t.updated_at) AS last_activity_at,
  now() AS computed_at
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id AND t.deleted_at IS NULL
WHERE p.deleted_at IS NULL
GROUP BY p.id, p.team_id, p.name;

CREATE UNIQUE INDEX project_task_stats_project_id ON project_task_stats(project_id);
CREATE INDEX project_task_stats_team_id ON project_task_stats(team_id);

---

Refresh Strategies

1. Full Refresh (Simple)

-- Blocking: takes a lock during refresh — use for non-critical data
REFRESH MATERIALIZED VIEW team_mrr_snapshot;

-- Non-blocking: requires a unique index, allows reads during refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY team_mrr_snapshot;

Key difference: CONCURRENTLY computes the new data in a temp table, then swaps it in — reads continue uninterrupted. Always prefer CONCURRENTLY for production.

2. Scheduled Refresh via pg_cron

-- Install pg_cron extension (available on RDS, Aurora, Cloud SQL)
CREATE EXTENSION pg_cron;

-- Refresh MRR snapshot every 15 minutes
SELECT cron.schedule(
  'refresh-mrr-snapshot',
  '*/15 * * * *',    -- Every 15 minutes
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY team_mrr_snapshot$$
);

-- Refresh task stats every 5 minutes during business hours
SELECT cron.schedule(
  'refresh-task-stats',
  '*/5 8-20 * * 1-5',   -- Every 5 min, Mon–Fri, 8AM–8PM
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY project_task_stats$$
);

-- Verify scheduled jobs
SELECT jobid, schedule, command, active FROM cron.job;

3. Trigger-Based Refresh (Near Real-Time)

For data that needs to be fresher but you still want the caching benefit:

-- Track when source tables change
CREATE TABLE materialized_view_refresh_queue (
  view_name    TEXT NOT NULL,
  queued_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (view_name)   -- ON CONFLICT DO NOTHING for deduplication
);

-- Trigger: queue refresh when subscriptions change
CREATE OR REPLACE FUNCTION queue_mrr_refresh()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO materialized_view_refresh_queue (view_name)
  VALUES ('team_mrr_snapshot')
  ON CONFLICT (view_name) DO UPDATE SET queued_at = now();
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_subscriptions_changed
AFTER INSERT OR UPDATE OR DELETE ON subscriptions
FOR EACH STATEMENT
EXECUTE FUNCTION queue_mrr_refresh();

Then a worker checks the queue and refreshes:

// lib/db/refresh-worker.ts
import { pool } from "@/lib/db";

export async function processRefreshQueue() {
  const client = await pool.connect();

  try {
    // Acquire advisory lock — only one refresh worker runs at a time
    const acquired = await client.query(
      `SELECT pg_try_advisory_lock(hashtext('mv-refresh-worker')) as acquired`
    );

    if (!acquired.rows[0].acquired) return; // Another worker is running

    // Get views that need refreshing
    const pending = await client.query<{ view_name: string }>(
      `DELETE FROM materialized_view_refresh_queue
       WHERE queued_at < now() - INTERVAL '10 seconds'  -- 10-second debounce
       RETURNING view_name`
    );

    for (const row of pending.rows) {
      const viewName = row.view_name.replace(/[^a-z_]/g, ""); // Sanitize
      console.log(`Refreshing materialized view: ${viewName}`);
      await client.query(
        `REFRESH MATERIALIZED VIEW CONCURRENTLY ${viewName}`
      );
    }
  } finally {
    await client.query(`SELECT pg_advisory_unlock(hashtext('mv-refresh-worker'))`);
    client.release();
  }
}

---

Incremental Refresh with pg_ivm

The pg_ivm extension enables incremental materialized view maintenance — instead of recomputing the full result, it applies only the changes (INSERT/UPDATE/DELETE deltas). This can be 10–100x faster for large tables.

-- Install pg_ivm (available as an extension on major providers)
CREATE EXTENSION pg_ivm;

-- Create an incrementally maintainable materialized view
SELECT create_immv(
  'daily_active_users',
  $$
    SELECT
      date_trunc('day', created_at)::date AS day,
      COUNT(DISTINCT user_id) AS dau
    FROM page_views
    WHERE created_at >= now() - INTERVAL '90 days'
    GROUP BY 1
  $$
);
-- pg_ivm creates triggers that automatically keep this up to date
-- No manual REFRESH needed — updates apply incrementally on each INSERT/DELETE

pg_ivm limitations (as of 2026):

• No support for OUTER JOINs or DISTINCT in the view query
• No window functions
• Works best for aggregation-only views (COUNT, SUM, AVG)
• Available on PostgreSQL 14+, some managed providers (check docs before using)

---

Query Rewriting Patterns

PostgreSQL doesn't automatically rewrite queries to use materialized views (unlike some other databases). You need to route your application queries to the view explicitly:

// lib/analytics/dashboard.ts
import { db } from "@/lib/db";

// ❌ Slow — recomputes from raw data every time
export async function getTeamMRRSlow(teamId: string) {
  return db.$queryRaw`
    SELECT SUM(
      CASE
        WHEN billing_period = 'monthly' THEN amount
        WHEN billing_period = 'annual' THEN amount / 12.0
      END
    ) as mrr
    FROM subscriptions
    WHERE team_id = ${teamId}::uuid AND status = 'active'
  `;
}

// ✅ Fast — reads from materialized view
export async function getTeamMRR(teamId: string) {
  const result = await db.$queryRaw<[{ mrr_usd: number; computed_at: Date }]>`
    SELECT mrr_usd, computed_at
    FROM team_mrr_snapshot
    WHERE team_id = ${teamId}::uuid
  `;

  return {
    mrr: result[0]?.mrr_usd ?? 0,
    asOf: result[0]?.computed_at ?? new Date(),
  };
}

// Dashboard endpoint — combines multiple materialized views
export async function getDashboardStats(teamId: string) {
  const [mrr, taskStats, recentActivity] = await Promise.all([
    db.$queryRaw<[{ mrr_usd: number; active_subscriptions: number }]>`
      SELECT mrr_usd, active_subscriptions
      FROM team_mrr_snapshot
      WHERE team_id = ${teamId}::uuid
    `,
    db.$queryRaw<Array<{ project_name: string; completion_pct: number; overdue_tasks: number }>>`
      SELECT project_name, completion_pct, overdue_tasks
      FROM project_task_stats
      WHERE team_id = ${teamId}::uuid
      ORDER BY last_activity_at DESC
      LIMIT 10
    `,
    db.activity.findMany({
      where: { teamId },
      orderBy: { createdAt: "desc" },
      take: 5,
    }),
  ]);

  return {
    mrr: mrr[0]?.mrr_usd ?? 0,
    activeSubscriptions: mrr[0]?.active_subscriptions ?? 0,
    topProjects: taskStats,
    recentActivity,
  };
}

---

Monitoring Materialized View Freshness

-- Check when each materialized view was last refreshed
-- (PostgreSQL doesn't track this natively — add computed_at to your views)
SELECT
  schemaname,
  matviewname,
  hasindexes,
  ispopulated
FROM pg_matviews
ORDER BY matviewname;

-- Custom freshness check using embedded computed_at column
SELECT
  'team_mrr_snapshot' AS view_name,
  computed_at,
  now() - computed_at AS age,
  CASE
    WHEN now() - computed_at > INTERVAL '1 hour' THEN 'STALE'
    WHEN now() - computed_at > INTERVAL '15 minutes' THEN 'AGING'
    ELSE 'FRESH'
  END AS freshness
FROM team_mrr_snapshot
LIMIT 1;

---

API Response with Freshness Metadata

// app/api/dashboard/route.ts
export async function GET(req: NextRequest) {
  const { teamId } = await getTeamFromRequest(req);

  const stats = await getDashboardStats(teamId);

  return NextResponse.json(
    {
      data: stats,
      meta: {
        // Tell the client when data was computed — show "as of X" in the UI
        mrrAsOf: stats.mrrComputedAt,
        cacheStatus: "materialized",
      },
    },
    {
      headers: {
        // Allow browser to cache for 5 minutes
        "Cache-Control": "private, max-age=300, stale-while-revalidate=60",
      },
    }
  );
}

---

Performance Comparison

---

Cost and Timeline

---

See Also

• PostgreSQL Window Functions — Analytics queries inside materialized views
• PostgreSQL Partitioning — Partitioning the underlying tables
• PostgreSQL Advisory Locks — Coordinating refresh workers
• SaaS Dunning Management — MRR tracking and subscription analytics

---

Working With Viprasol

We optimize PostgreSQL analytics for SaaS dashboards—from identifying slow queries through implementing full materialized view pipelines with automated refresh. Our team has reduced dashboard load times by 100–500× for clients with millions of records.

What we deliver:

• Slow query identification and materialized view design
• CONCURRENT refresh scheduling with pg_cron
• Trigger-based refresh for near-real-time data
• pg_ivm incremental refresh for high-write tables
• Freshness monitoring and alerting

Explore our web development services or contact us to speed up your PostgreSQL analytics.