Materialized Views, Caching & Query Acceleration
Pre-compute expensive queries and cache results for instant dashboard performance.
🎯 What You'll Learn
- Regular views vs materialized views and when to use each
- Refresh strategies: full, concurrent, scheduled, and trigger-based
- Building instant dashboards with pre-computed aggregations
- Caching patterns: unlogged tables, summary tables, and TTLs
- Decision framework for choosing the right caching strategy
📸 Views vs Materialized Views
A regular view is like a saved Google search — it runs every time you open it. A materialized view is like a printed report — it's instant to read but needs reprinting when data changes. Materialized views trade freshness for speed.
Materialized View Basics
Create a materialized view with indexing
-- Regular View: re-runs the query every time
CREATE VIEW v_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(total) AS revenue,
COUNT(*) AS order_count,
AVG(total) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- This runs the full aggregation on every SELECT:
SELECT * FROM v_monthly_revenue; -- slow on millions of rows
-- Materialized View: stores the result physically
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
...🔄 Refresh Strategies
The key question: how do you keep the materialized view up to date? PostgreSQL offers several approaches, from simple full refreshes to concurrent updates that don't block readers.
💡 Pro Tip — CONCURRENTLY Requires a Unique Index
REFRESH MATERIALIZED VIEW CONCURRENTLY won't block reads, but it requires a unique index on the view. Without it, PostgreSQL can't diff old and new data efficiently.
Refresh Strategies
Full, concurrent, scheduled, and trigger-based refresh
-- Refresh strategies for materialized views
-- 1. FULL REFRESH (simplest — drops and rebuilds)
REFRESH MATERIALIZED VIEW mv_monthly_revenue;
-- Blocks reads during refresh!
-- 2. CONCURRENT REFRESH (no read blocking)
-- Requires a UNIQUE INDEX on the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;
-- Readers see old data during refresh, then see new data
-- Slightly slower than full refresh, but no downtime
-- 3. Scheduled refresh with pg_cron (PostgreSQL extensi
...📊 Dashboard Materialized Views
Dashboards that aggregate millions of rows are the perfect use case. Pre-compute customer LTV, product performance, and revenue trends — then serve them instantly.
Dashboard Views
Customer LTV and product performance scoreboard
-- Real-world: Dashboard materialized views
-- Customer Lifetime Value (expensive to compute live)
CREATE MATERIALIZED VIEW mv_customer_ltv AS
SELECT
c.id AS customer_id,
c.name,
c.email,
COUNT(o.id) AS total_orders,
SUM(o.total) AS lifetime_value,
MIN(o.order_date) AS first_order,
MAX(o.order_date) AS last_order,
AVG(o.total) AS avg_order_value,
MAX(o.order_date) - MIN(o.order_date) AS customer_lifespan
FROM customers c
LEFT JOIN orders o ON c.id = o.custome
...⚡ Beyond Materialized Views
When materialized views aren't enough, use unlogged tables as caches or summary tables for real-time counters. Each approach trades different things.
⚠️ Common Mistake
Using unlogged tables for important data. They're fast because they skip the write-ahead log, but all data is lost on crash. Only use them for data you can regenerate.
Caching Patterns
Unlogged tables, summary tables, and TTL caching
-- Caching layers beyond materialized views
-- 1. UNLOGGED TABLES as cache (fast writes, no WAL)
CREATE UNLOGGED TABLE cache_search_results (
query_hash TEXT PRIMARY KEY,
results JSONB,
cached_at TIMESTAMP DEFAULT NOW(),
expires_at TIMESTAMP DEFAULT NOW() + INTERVAL '1 hour'
);
-- Write to cache:
INSERT INTO cache_search_results (query_hash, results)
VALUES (md5('SELECT ... complex query'), '{"items": [...]}')
ON CONFLICT (query_hash) DO UPDATE
SET results = EXCLUDED.results,
...🧭 Choosing the Right Strategy
Caching Decision Framework
When to use each caching approach
-- When to use each caching strategy
-- MATERIALIZED VIEW:
-- ✅ Complex aggregations queried frequently
-- ✅ Data doesn't need to be real-time (stale OK)
-- ✅ Dashboard reports, analytics, leaderboards
-- ❌ Not for data that must be instantly fresh
-- UNLOGGED TABLE CACHE:
-- ✅ Expensive query results with TTL
-- ✅ Search results, API response caching
-- ⚠️ Data lost on crash (no WAL = no recovery)
-- SUMMARY TABLE:
-- ✅ Real-time counters and aggregates
-- ✅ Incrementally updated (no full re
...📋 Quick Reference
| Strategy | Freshness | Speed |
|---|---|---|
| Regular View | Real-time | Slow (re-runs query) |
| Materialized View | Stale until refresh | Fast (pre-computed) |
| Summary Table | Real-time (incremental) | Fast (maintained live) |
| Unlogged Cache | TTL-based | Very fast (no WAL) |
🎉 Lesson Complete!
You can now accelerate queries with materialized views and caching strategies. Next, explore advanced aggregation techniques!
Sign up for free to track which lessons you've completed and get learning reminders.