Postgres Materialized Views: Fast Reports Without Slowing Your App

View vs materialized view

A view is a saved query with a name. Querying it runs the underlying query every time:

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL AND plan <> 'free';

Convenient, always up to date — and exactly as slow as the query inside.

A materialized view saves the result to disk, like a table:

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       count(*)    AS orders,
       sum(total)  AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

Reading it is as fast as reading a small table. The catch: the data is a snapshot from when it was last refreshed.

	View	Materialized view
Stores	The query	The query and its result
Read speed	Same as the query	Fast
Freshness	Always current	As of last refresh
Can be indexed	No (indexes on base tables)	Yes

When to use one

• Dashboards and reports over large tables: revenue by day, active users per week, usage per customer. (Postgres window functions, SQL GROUP BY)
• Expensive joins/aggregations read often but needed only roughly current.
• Search or leaderboard data rebuilt on a schedule.

Not for data that must be exactly current at all times (account balances, stock levels).

Refreshing

REFRESH MATERIALIZED VIEW daily_revenue;

Re-runs the query and replaces the contents. By default this locks the view against reads while it runs — dashboards wait.

Without blocking readers

CREATE UNIQUE INDEX ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY lets reads continue during the refresh. It requires a unique index on the view, and it's slower (it compares old and new results and applies the differences), but it's what you want for anything user-facing.

Indexing

Since the result is stored, you can index it like a table:

CREATE INDEX ON customer_usage (customer_id);

(Database indexes)

Scheduling refreshes

Postgres doesn't refresh materialized views automatically. Options:

• pg_cron inside the database (where available):

  SELECT cron.schedule('refresh-revenue', '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue');
  

• Your app's job runner — a scheduled job every N minutes. (Background jobs and cron, Cron expressions)

• After a batch import finishes.

Pick a frequency based on how stale is acceptable and how long the refresh takes. Show "updated 10 minutes ago" in the UI so users know.

Things to watch

• Refresh cost — a full refresh re-runs the whole query. On very large tables, consider summary tables updated incrementally instead.
• Overlapping refreshes — if a refresh takes longer than the schedule interval, runs pile up. Use an advisory lock or a job runner that prevents overlap. (Postgres advisory locks)
• Migrations — changing the query means dropping and recreating the view (and its indexes). Views that depend on it must be recreated too.
• Disk space and vacuum — refreshes create dead rows like updates do. (Postgres VACUUM and bloat)
• Permissions — grant SELECT to your read-only/reporting roles. (Postgres roles and permissions)

Alternatives

• Summary tables updated incrementally — triggers or jobs add to daily totals as orders arrive; always current, more code. (Postgres triggers)
• Caching in Redis for API responses. (Redis: when you need it)
• A read replica to keep heavy reports off the primary. (Postgres read replicas)
• An analytics database when reporting outgrows Postgres.

In ORMs

Most ORMs don't manage materialized views; create them in a raw SQL migration and query them like a read-only table (Prisma supports views as a preview feature; Drizzle has pgMaterializedView). (Database migrations explained)

---

EasySpawn runs Postgres next to your app with background workers for scheduled refreshes, and Claude Code can turn a slow dashboard query into an indexed materialized view. See how it works or join the waitlist.

Related: Postgres Window Functions · Database Indexes · Postgres CTEs · Why Is My Website Slow?