Advanced Database Caching: When to Use PostgreSQL Views vs. Materialized Views
Every backend engineer eventually hits the same wall: a query that used to run in milliseconds now takes seconds. Joins pile up, aggregations get heavier, and the dashboard your product manager loves starts timing out.
PostgreSQL gives you two tools that sound similar but solve very different problems: views and materialized views.
Understanding when to reach for each one is the difference between a system that scales gracefully and one that quietly rots under load.
What a View Actually Is
A standard PostgreSQL view is nothing more than a saved query.
It doesn't store data. It doesn't cache results. Every time you query a view, PostgreSQL runs the underlying SQL fresh, against the live tables.
CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';
Think of a view as a shortcut, not a copy. It exists purely for convenience and abstraction:
- Hiding complex joins behind a simple name
- Enforcing consistent business logic across the app
- Restricting which columns or rows certain roles can see
- Keeping query logic DRY across multiple services
Because a view has no storage of its own, it's always accurate. There's no risk of stale data. But that accuracy comes at a cost: the full underlying query runs every single time.
What a Materialized View Actually Is
A materialized view looks similar on the surface, but behaves completely differently underneath.
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT region, SUM(total) AS revenue
FROM orders
GROUP BY region;
Unlike a regular view, PostgreSQL actually executes this query once and physically stores the result on disk, exactly like a table.
From that point forward, querying the materialized view doesn't touch the original tables at all. It just reads the stored snapshot.
That means:
- Reads are fast, because the heavy computation already happened
- The data reflects a point in time, not the live state
- You must explicitly refresh it to update the snapshot
REFRESH MATERIALIZED VIEW monthly_sales_summary;
Until you run that refresh, the data can be minutes, hours, or even days out of date. This is a tradeoff, not a flaw.
The Core Tradeoff: Freshness vs. Speed
Everything about this decision comes down to one question:
Does this query need to reflect the current state of the database, or can it tolerate being slightly stale?
Regular views prioritize freshness. Materialized views prioritize speed.
There is no version that gives you both for free. If you want instant reads on expensive aggregations, someone has to pay the computation cost ahead of time, and that's exactly what materialization does.
When to Use a Regular View
Reach for a standard view when:
- The underlying query is cheap to compute, even at scale
- The data must always be current, such as account balances or inventory counts
- You're mainly using it for abstraction, security, or readability, not performance
- The table it queries is small or well-indexed
A view for active_customers, order_status_summary, or a role-restricted employee_directory makes sense. These are cheap to compute, and staleness would actually be a bug, not a feature.
When to Use a Materialized View
Reach for a materialized view when:
- The query involves expensive joins, aggregations, or window functions
- The same expensive query runs repeatedly, by many users or services
- The data doesn't need to be real-time, such as analytics, reports, or dashboards
- You can tolerate a defined refresh interval, such as every 5 minutes, hourly, or nightly
Classic use cases include monthly revenue summaries, product recommendation scores, leaderboard rankings, and analytics dashboards. In all of these, recomputing from scratch on every page load is wasteful when yesterday's numbers are perfectly acceptable today.
Refresh Strategies Matter
A materialized view is only as useful as its refresh strategy.
PostgreSQL gives you two options:
REFRESH MATERIALIZED VIEW monthly_sales_summary;
This locks the view against reads while it rebuilds. For small views, that's fine. For large ones, it can cause visible downtime on that view.
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;
This allows reads to continue during the refresh, but it requires a unique index on the materialized view first, and it takes longer to complete.
Most production systems schedule refreshes using a cron job, a scheduled task in their job queue, or a tool like pg_cron, rather than refreshing on every write.
A Real-World Scenario
Imagine an e-commerce admin dashboard that shows total revenue per region.
Built as a live view, this query joins orders, order_items, products, and regions, then aggregates across potentially millions of rows. Every time an admin opens the dashboard, PostgreSQL recalculates everything from scratch.
At low traffic, this is barely noticeable. At scale, with multiple admins refreshing the page and background jobs hitting the same tables, this query becomes a bottleneck that slows down the entire database.
Converted into a materialized view refreshed every 15 minutes, the dashboard becomes instant. The admin sees numbers that are, at most, 15 minutes old, which is a completely acceptable tradeoff for a revenue summary.
Materialized Views Are Not a Replacement for Indexing
It's worth being clear about what materialized views don't solve.
They don't fix poorly designed schemas. They don't replace proper indexing. They don't help with queries that already run fast.
Using a materialized view to paper over a missing index is treating a symptom instead of the cause. Materialization should be reserved for queries that are inherently expensive even when properly indexed, typically ones involving heavy aggregation across large datasets.
Combining Both Approaches
These two tools aren't mutually exclusive. Many production schemas use both together:
- Regular views for live, low-cost abstractions used throughout the application
- Materialized views for expensive, repeatable aggregations used in analytics and reporting
- Indexes on the materialized views themselves, since they behave like normal tables once created
You can even build a regular view on top of a materialized view, layering readability on top of performance.
Rule of Thumb
If the query is cheap and needs to be accurate right now, use a view.
If the query is expensive and slightly stale data is acceptable, use a materialized view.
Everything else is just tuning the refresh interval.
Final Thoughts
Views and materialized views aren't competing features. They're different tools solving different problems: one favors correctness, the other favors performance.
The engineers who use PostgreSQL well don't default to one or the other out of habit. They look at each query, ask how expensive it is, how often it runs, and how fresh the data truly needs to be, and let that answer decide.
Caching at the database layer, done deliberately, is one of the simplest ways to make a system feel dramatically faster without touching a single line of application code.
References
- PostgreSQL Documentation – Views (https://www.postgresql.org/docs/current/sql-createview.html)
- PostgreSQL Documentation – Materialized Views (https://www.postgresql.org/docs/current/rules-materializedviews.html)
- PostgreSQL Documentation – REFRESH MATERIALIZED VIEW (https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html)
- PostgreSQL Documentation – pg_cron Scheduling (https://github.com/citusdata/pg_cron)