Postgres Materialized Views: Fast Reports Without Slowing Your App
A view is a saved query; a materialized view also saves the result. When materialized views make dashboards and reports fast, REFRESH vs REFRESH CONCURRENTLY, indexing them, scheduling refreshes with pg_cron or your job runner, staleness, and alternatives like summary tables.
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);
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
SELECTto 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?
Keep reading
UUIDv7 Explained: Time-Ordered IDs and Why Postgres 18 Added uuidv7()
UUIDv7 is a UUID that starts with a millisecond timestamp, so new IDs sort in creation order. Why that makes database indexes faster than random UUIDv4, the format in detail, generating them in Postgres 18, Node.js and Python, what the timestamp leaks, and when to still use v4 or bigint.
SQLite WAL Mode: Faster Writes and Readers That Don't Block
Write-ahead logging (WAL) mode is the single most important setting for using SQLite in a web app: readers no longer block the writer, writes get faster, and "database is locked" errors mostly disappear. How to turn it on, the companion pragmas, checkpoints, the -wal and -shm files, safe backups, and its limits.