Materialized Views and REFRESH Strategies
Persist the result of an expensive query with MATERIALIZED VIEW and refresh on a schedule or on demand.
Materialized Views and REFRESH Strategies is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Materialized View = Saved Result
Unlike a regular view, a materialized view stores the result of its SELECT to disk. Queries hit the stored data — fast. Refresh on demand.
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
SUM(total) AS revenue
FROM orders
GROUP BY 1;Querying
Queries are just SELECTs on the materialised data:
SELECT * FROM daily_revenue WHERE day >= NOW() - INTERVAL '30 days';REFRESH MATERIALIZED VIEW
Recompute the result. Locks the view exclusively:
REFRESH MATERIALIZED VIEW daily_revenue;
-- Selects on the view block during refresh.REFRESH MATERIALIZED VIEW CONCURRENTLY
Non-blocking refresh — readers stay served from the old data until the new is ready. Requires a unique index on the view:
CREATE UNIQUE INDEX ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;Indexes on Materialized Views
You can index a materialised view like any table — speeds up queries:
CREATE INDEX daily_revenue_day_idx ON daily_revenue (day DESC);When to Materialize
Use materialised views when:
- The underlying query is expensive
- The result tolerates being slightly stale
- The query runs many times between updates
Scheduled Refresh
No built-in scheduler. Use pg_cron, an OS cron job, or your app scheduler:
-- Via pg_cron extension:
SELECT cron.schedule('refresh_daily', '0 1 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;');Incremental Refresh
PostgreSQL has no native incremental refresh — every REFRESH recomputes from scratch. For incremental aggregation, look at:
- Hand-rolled trigger-based "summary tables"
- TimescaleDB continuous aggregates
- pg_ivm extension (incremental matviews)
Reading Underlying Tables Without Affecting Reads
CONCURRENT refresh reads the underlying tables in a transaction snapshot — it doesn't block them. But the refresh itself can be slow on huge data.
Disk Usage
A materialised view doubles storage of its data. Plan disk accordingly.
Combining With Partitioning
For huge time-series, partition the materialised view (or use TimescaleDB):
CREATE MATERIALIZED VIEW orders_summary AS ... ;
CREATE INDEX ON orders_summary (day);
-- For very large views, consider regular table + manual upsert pattern.When Not to Use
If your reads tolerate fresh-data SQL and the underlying query is already fast, save yourself the refresh maintenance. Materialised views are a tradeoff — staleness for speed.
Recap
Materialised views cache query results.
- Stored on disk, indexed, fast
- REFRESH manually or on schedule
- CONCURRENTLY needs a UNIQUE index
- Use for expensive, slightly-stale aggregations
Quick Check
What does REFRESH MATERIALIZED VIEW CONCURRENTLY require that the basic REFRESH does not?
Frequently asked questions
Is the “Materialized Views and REFRESH Strategies” lesson free?
Yes — the full text of “Materialized Views and REFRESH Strategies” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Materialized Views and REFRESH Strategies”?
Persist the result of an expensive query with MATERIALIZED VIEW and refresh on a schedule or on demand. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Materialized Views and REFRESH Strategies” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Plain Views: Logical Reuse
- Updatable Views and INSTEAD OF Triggers
- Materialized Views and REFRESH Strategies
- When to Pre-Aggregate