SQL 67 🛢️ Materialized Views and Refresh Strategies
A regular view is a stored query. Every time it is referenced, the database re-executes the query against the underlying tables. The result is always current, but the cost is paid on every read. A materialized view is a stored result. The query runs once, at the creation, and its output is written to disk as a physical table. Subsequent reads query the stored data directly, and the performance is the same as reading a table. The trade-off is the freshness: the stored data is stale until the view is refreshed, and the refresh is the operation that re-runs the query and replaces the stored result. The materialized view is the performance optimization for the expensive queries that are read far more often than the underlying data changes.
The use case is specific. A dashboard that aggregates millions of rows into a few hundred summary rows does not need to re-aggregate on every page load. A reporting query that joins ten tables and computes the year-over-year comparisons does not need to re-run the joins on every request. The materialized view computes the result once, and the dashboard reads it. The refresh runs on a schedule — hourly, nightly, after the ETL job — and the freshness requirement determines the schedule. The materialized view is the precomputed answer, and the refresh is the recomputation.
PostgreSQL is the database with the most mature materialized view support, and it is the reference for this chapter. The CREATE MATERIALIZED VIEW statement defines the view, the REFRESH MATERIALIZED VIEW re-runs the query, and the REFRESH MATERIALIZED VIEW CONCURRENTLY refreshes without blocking the readers. The concurrent refresh requires a unique index on the view, and it is slower than the plain refresh because it computes the difference and applies it rather than replacing the whole table. The WITH NO DATA option creates the view without populating it, which is useful when the initial population is expensive and should be deferred. The REFRESH MATERIALIZED VIEW with the WITH DATA option is the default, and it populates the view immediately.
This chapter covers three areas. First, why materialized views exist — the expensive-query problem and the read-heavy workload. Second, how to create and refresh them — the CREATE MATERIALIZED VIEW syntax, the WITH NO DATA option, the plain refresh, the concurrent refresh, and the unique index requirement. Third, the refresh strategies — the full refresh, the incremental refresh, the on-demand refresh, the scheduled refresh, and the trade-offs between the freshness and the cost. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the materialized view lifecycle.
Key point: A materialized view stores the result of a query as a physical table. It is created with CREATE MATERIALIZED VIEW, refreshed with REFRESH MATERIALIZED VIEW, and refreshed without blocking the readers with REFRESH MATERIALIZED VIEW CONCURRENTLY. The concurrent refresh requires a unique index on the view. The WITH NO DATA option creates the view without populating it. The data is stale until the refresh, so the refresh strategy must match the freshness requirement.
Why materialized views exist
The expensive-query problem. A query that aggregates a large table, joins many tables, or computes the complex expressions takes time. The time is paid on every reference to the regular view. If the query takes five seconds and the dashboard is loaded a thousand times a day, the total cost is five thousand seconds, and the database is doing the same computation over and over. The materialized view computes the result once, and the reads are cheap. The five seconds are paid on the refresh, not on the read.
The read-heavy problem. The workload that benefits from the materialized view is the read-heavy workload: many reads, few writes. The data warehouse, the reporting database, and the analytics dashboard are the examples. In these workloads, the data changes infrequently — the nightly ETL, the hourly batch — and the reads are frequent. The materialized view is the cache that the database manages, and the refresh is the cache invalidation.
The freshness problem. The materialized view’s data is stale. The staleness is the trade-off: the cheaper read is paid for by the older data. The freshness requirement determines the refresh strategy. If the dashboard can tolerate the data that is up to an hour old, the hourly refresh is fine. If the data must be current, the materialized view is the wrong choice, and the regular view or the indexed query is the right one.
The blocking problem. The plain REFRESH MATERIALIZED VIEW locks the view and blocks the readers until the refresh is complete. For the large views, the refresh takes minutes, and the readers are blocked for the whole time. The REFRESH MATERIALIZED VIEW CONCURRENTLY avoids the lock: it computes the new data, compares it to the old, and applies the difference. The readers see the old data during the refresh and the new data after, without the interruption. The cost is the slower refresh and the unique index requirement.
The index problem. A materialized view can have the indexes. The indexes are created on the view like on a table, and they speed up the queries that filter or join on the view’s columns. The CREATE INDEX ON the_materialized_view (column) is the standard pattern. The unique index is required for the concurrent refresh, and it is the way to identify the rows.
The trade-off. The materialized view adds the storage cost and the refresh cost. The storage is the size of the result, which can be significant for the large views. The refresh is the computation of the query, which is the cost that the regular view pays on every read. The materialized view moves the cost from the read to the refresh. The trade-off is between the read performance and the freshness, and the choice depends on the workload and the requirement.
a. Creating a materialized view
The CREATE MATERIALIZED VIEW statement defines the view. The syntax is the CREATE VIEW with the MATERIALIZED keyword.
CREATE MATERIALIZED VIEW customer_totals_mat AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS lifetime_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;The view is created, and the query runs immediately. The result is stored as a physical table. The SELECT * FROM customer_totals_mat reads the stored data, not the underlying tables.
The WITH NO DATA option creates the view without populating it:
CREATE MATERIALIZED VIEW customer_totals_mat AS
SELECT ...
WITH NO DATA;The view is created, but the data is not computed. The view is in the “unpopulated” state, and the queries against it fail with the “materialized view has not been populated” error until the REFRESH MATERIALIZED VIEW is run. The WITH NO DATA is useful when the initial population is expensive and should be deferred, or when the view is created in a migration and populated later.
The TABLESPACE option specifies the tablespace, and the WITH option passes the storage parameters:
CREATE MATERIALIZED VIEW customer_totals_mat
TABLESPACE fast_storage
AS SELECT ...;The indexes are created on the view like on a table:
CREATE UNIQUE INDEX ON customer_totals_mat (customer_id);
CREATE INDEX ON customer_totals_mat (lifetime_total DESC);The unique index on the customer_id is the requirement for the concurrent refresh. The index on the lifetime_total speeds up the ordered queries.
b. Refreshing a materialized view
The REFRESH MATERIALIZED VIEW re-runs the query and replaces the stored data.
REFRESH MATERIALIZED VIEW customer_totals_mat;The refresh takes the AccessExclusiveLock on the view, which blocks the readers and the writers until the refresh is complete. For the small views, the block is brief. For the large views, the block can be minutes.
The WITH DATA and the WITH NO DATA options control the population:
REFRESH MATERIALIZED VIEW customer_totals_mat WITH DATA;
REFRESH MATERIALIZED VIEW customer_totals_mat WITH NO DATA;The WITH DATA is the default and populates the view. The WITH NO DATA empties the view and leaves it in the unpopulated state.
The CONCURRENTLY option refreshes without blocking the readers:
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;The concurrent refresh computes the new data, compares it to the old data, and applies the difference. The readers see the old data during the refresh and the new data after. The requirements are: the view must have at least one unique index that uses only the column names and includes all the rows, and the refresh is slower than the plain refresh.
The concurrent refresh is the standard for the production views that cannot be blocked. The plain refresh is the choice for the views that are small or that can tolerate the brief block.
The pg_stat_activity monitoring. The refresh is a long-running operation, and it is visible in the pg_stat_activity:
SELECT pid, query, state
FROM pg_stat_activity
WHERE query LIKE 'REFRESH MATERIALIZED VIEW%';The pg_stat_progress_cluster and the other progress views show the refresh’s progress.
The \d+ in the psql. The \d+ customer_totals_mat shows the view’s columns, the indexes, and the “Unpopulated” flag if the view has no data.
c. The refresh strategies
The full refresh. The REFRESH MATERIALIZED VIEW re-runs the whole query. The strategy is the simplest, and it is the right choice when the query is not too expensive and the view is not too large. The full refresh replaces the whole table, so the old data is discarded, and the new data is written.
The concurrent refresh. The REFRESH MATERIALIZED VIEW CONCURRENTLY re-runs the whole query but applies the difference. The strategy is the right choice when the readers must not be blocked. The cost is the slower refresh and the unique index requirement.
The scheduled refresh. The refresh is scheduled with the cron, the pg_cron extension, or the application’s scheduler. The pg_cron is the PostgreSQL extension that runs the SQL on the schedule:
SELECT cron.schedule(
'refresh-customer-totals',
'0 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat'
);The '0 * * * *' is the cron expression for the hourly refresh. The pg_cron is the standard for the database-level scheduling.
The on-demand refresh. The refresh is triggered by the application after the data changes. The ETL job, the batch process, or the data-loading script runs the refresh at the end. The strategy is the right choice when the data changes are known and the refresh can be tied to them.
The incremental refresh. The plain and the concurrent refreshes are the full refreshes: they re-run the whole query. The incremental refresh applies only the changes, which is much faster for the large views. PostgreSQL does not have the built-in incremental refresh for the materialized views, but the extensions like the pg_ivm provide it. The pg_ivm creates the incrementally maintainable materialized views, and the refresh applies only the deltas:
CREATE INCREMENTAL MATERIALIZED VIEW customer_totals_ivm AS
SELECT ...;
REFRESH MATERIALIZED VIEW customer_totals_ivm; -- applies the deltaThe pg_ivm is the extension, and it is the advanced topic. The plain and the concurrent refreshes are the built-in options.
The unlogged materialized view. The CREATE UNLOGGED MATERIALIZED VIEW creates the view without the write-ahead log. The view is faster to write and faster to refresh, but it is truncated after the crash. The unlogged view is the choice for the cache that can be rebuilt.
The trade-off. The refresh strategy is the trade-off between the freshness, the cost, and the blocking. The full refresh is the cheapest to implement but the most expensive to run and the most blocking. The concurrent refresh is the least blocking but the most expensive to run. The incremental refresh is the fastest to run but the most complex to set up. The scheduled refresh is the most predictable but the least fresh. The choice depends on the workload, the freshness requirement, and the tolerance for the blocking.
Complete Example Session
-- ============================================
-- PART 1: THE BASE TABLES
-- ============================================
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
order_date DATE NOT NULL,
total NUMERIC(10, 2) NOT NULL
);
-- ============================================
-- PART 2: THE MATERIALIZED VIEW
-- ============================================
CREATE MATERIALIZED VIEW customer_totals_mat AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS lifetime_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
SELECT * FROM customer_totals_mat ORDER BY lifetime_total DESC;
-- ============================================
-- PART 3: THE UNIQUE INDEX
-- ============================================
CREATE UNIQUE INDEX ON customer_totals_mat (customer_id);
CREATE INDEX ON customer_totals_mat (lifetime_total DESC);
-- ============================================
-- PART 4: THE WITH NO DATA
-- ============================================
CREATE MATERIALIZED VIEW customer_totals_empty AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
WITH NO DATA;
-- The view is created but not populated.
-- SELECT * FROM customer_totals_empty; -- the error
REFRESH MATERIALIZED VIEW customer_totals_empty;
-- ============================================
-- PART 5: THE PLAIN REFRESH
-- ============================================
REFRESH MATERIALIZED VIEW customer_totals_mat;
-- ============================================
-- PART 6: THE CONCURRENT REFRESH
-- ============================================
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;
-- The readers are not blocked.
-- The unique index is required.
-- ============================================
-- PART 7: THE CONCURRENT REFRESH FAILURE
-- ============================================
-- Without the unique index:
DROP INDEX customer_totals_mat_customer_id_idx;
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;
-- ERROR: cannot refresh materialized view "customer_totals_mat" concurrently
-- HINT: Create a unique index with no WHERE clause on one or more columns
-- of the materialized view.
-- The fix:
CREATE UNIQUE INDEX ON customer_totals_mat (customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;
-- ============================================
-- PART 8: THE SCHEDULED REFRESH
-- ============================================
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule(
'refresh-customer-totals',
'0 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat'
);
-- The hourly refresh.
SELECT * FROM cron.job;
-- ============================================
-- PART 9: THE VIEW MANAGEMENT
-- ============================================
-- The listing
SELECT schemaname, matviewname, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';
-- The size
SELECT
matviewname,
pg_size_pretty(pg_total_relation_size(format('%I.%I', schemaname, matviewname))) AS size
FROM pg_matviews
WHERE schemaname = 'public';
-- The definition
SELECT pg_get_viewdef('customer_totals_mat', true);
-- The drop
DROP MATERIALIZED VIEW IF EXISTS customer_totals_empty;
DROP MATERIALIZED VIEW IF EXISTS customer_totals_mat CASCADE;
-- ============================================
-- PART 10: THE COMPLETE LIFECYCLE
-- ============================================
-- 1. Create the materialized view
CREATE MATERIALIZED VIEW order_summary_mat AS
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(total) AS revenue,
AVG(total) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date);
-- 2. Create the unique index (for the concurrent refresh)
CREATE UNIQUE INDEX ON order_summary_mat (month);
-- 3. Create the additional indexes
CREATE INDEX ON order_summary_mat (revenue DESC);
-- 4. Query the view
SELECT * FROM order_summary_mat ORDER BY month;
-- 5. Refresh the view
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_mat;
-- 6. Schedule the refresh
SELECT cron.schedule(
'refresh-order-summary',
'0 2 * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_mat'
);
-- 7. Monitor the refresh
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE query LIKE 'REFRESH MATERIALIZED VIEW%';
-- 8. Check the size
SELECT pg_size_pretty(pg_total_relation_size('order_summary_mat'));
-- 9. Analyze the view for the planner
ANALYZE order_summary_mat;
-- 10. Drop the view
DROP MATERIALIZED VIEW IF EXISTS order_summary_mat CASCADE;The ten parts show the base tables, the materialized view, the unique index, the WITH NO DATA, the plain refresh, the concurrent refresh, the concurrent refresh failure, the scheduled refresh, the view management, and the complete lifecycle.
Quick Reference
The Materialized View Statements
| Statement | Purpose |
|---|---|
CREATE MATERIALIZED VIEW | Create the view |
CREATE MATERIALIZED VIEW ... WITH NO DATA | Create without the data |
REFRESH MATERIALIZED VIEW | Refresh the data |
REFRESH MATERIALIZED VIEW CONCURRENTLY | Refresh without the lock |
DROP MATERIALIZED VIEW | Remove the view |
DROP MATERIALIZED VIEW CASCADE | Remove with the dependents |
ALTER MATERIALIZED VIEW | Rename, change the owner |
The Index Requirements
| Requirement | Purpose |
|---|---|
| The unique index | Required for the concurrent refresh |
| The index on the columns | Speeds up the queries |
The index without the WHERE | The concurrent refresh requirement |
| The index includes all the rows | The concurrent refresh requirement |
The Refresh Comparison
| Aspect | Plain Refresh | Concurrent Refresh |
|---|---|---|
| The lock | The AccessExclusiveLock | The ExclusiveLock (brief) |
| The readers | Blocked | Not blocked |
| The speed | Fast | Slower |
| The unique index | Not required | Required |
| The use case | Small views | Production views |
The Management Queries
| Query | Purpose |
|---|---|
SELECT * FROM pg_matviews | The view listing |
SELECT * FROM pg_matviews WHERE ispopulated = false | The unpopulated |
SELECT pg_get_viewdef('name', true) | The definition |
pg_total_relation_size('name') | The size |
ANALYZE name | The planner statistics |
\d+ name | The psql description |
The Scheduling
| Tool | Purpose |
|---|---|
pg_cron | The database-level scheduling |
cron | The OS-level scheduling |
| The application | The on-demand refresh |
| The ETL job | The post-load refresh |
Best Practices
✅ Do This:
-- Create the unique index for the concurrent refresh
CREATE UNIQUE INDEX ON customer_totals_mat (customer_id); -- ✅
-- Use the CONCURRENTLY for the production views
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat; -- ✅
-- Use the WITH NO DATA for the deferred population
CREATE MATERIALIZED VIEW mv AS ... WITH NO DATA; -- ✅
-- Create the indexes on the frequently queried columns
CREATE INDEX ON customer_totals_mat (lifetime_total DESC); -- ✅
-- Analyze the view after the refresh
ANALYZE customer_totals_mat; -- ✅
-- Schedule the refresh to match the freshness requirement
SELECT cron.schedule('refresh', '0 * * * *', 'REFRESH ...'); -- ✅❌ Don’t Do This:
-- Don't use the CONCURRENTLY without the unique index
REFRESH MATERIALIZED VIEW CONCURRENTLY mv; -- error // ❌
-- Don't refresh the large view without the concurrent
REFRESH MATERIALIZED VIEW large_mv; -- blocks the readers // ❌
-- Don't forget the initial refresh with the WITH NO DATA
CREATE MATERIALIZED VIEW mv AS ... WITH NO DATA; -- the view is empty // ❌
-- Don't use the materialized view for the real-time data
-- The data is stale until the refresh // ❌
-- Don't forget to analyze after the refresh
ANALYZE mv; -- the planner has the stale statistics // ❌
-- Don't create the materialized view for the data that changes constantly
-- The refresh cost exceeds the benefit // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| The concurrent refresh fails | No unique index | Create the unique index |
| The view is empty | The WITH NO DATA | Refresh the view |
| The readers are blocked | The plain refresh | Use the concurrent |
| The stale data | No refresh | Schedule the refresh |
| The slow refresh | The full refresh of the large view | Use the incremental or the concurrent |
| The planner is slow | The stale statistics | Analyze the view |
| The storage grows | The unpopulated views | Drop the unused |
Real-World Examples
1. The Basic Materialized View
CREATE MATERIALIZED VIEW customer_totals_mat AS SELECT ...;2. The With No Data
CREATE MATERIALIZED VIEW mv AS SELECT ... WITH NO DATA;3. The Plain Refresh
REFRESH MATERIALIZED VIEW customer_totals_mat;4. The Concurrent Refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;5. The Unique Index
CREATE UNIQUE INDEX ON customer_totals_mat (customer_id);6. The Query Index
CREATE INDEX ON customer_totals_mat (lifetime_total DESC);7. The Scheduled Refresh
SELECT cron.schedule('refresh', '0 * * * *', 'REFRESH ...');8. The Listing
SELECT * FROM pg_matviews WHERE schemaname = 'public';9. The Size
SELECT pg_size_pretty(pg_total_relation_size('customer_totals_mat'));10. The Complete Lifecycle
CREATE MATERIALIZED VIEW order_summary_mat AS SELECT ...;
CREATE UNIQUE INDEX ON order_summary_mat (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_mat;Visual
The Materialized View Lifecycle
┌──────────────────────────────────────────────────────────────┐
│ THE MATERIALIZED VIEW LIFECYCLE │
│ │
│ 1. CREATE MATERIALIZED VIEW │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The query runs once. │ │
│ │ The result is stored as a physical table. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 2. CREATE UNIQUE INDEX │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The unique index on the view. │ │
│ │ Required for the concurrent refresh. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 3. QUERY │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The reads are the table reads. │ │
│ │ Fast and predictable. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 4. REFRESH │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The query re-runs. │ │
│ │ The stored data is replaced. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 5. REPEAT │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The refresh runs on the schedule. │ │
│ │ The data is stale between the refreshes. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Plain vs the Concurrent Refresh
┌──────────────────────────────────────────────────────────────┐
│ THE PLAIN vs THE CONCURRENT REFRESH │
│ │
│ THE PLAIN REFRESH: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ REFRESH MATERIALIZED VIEW mv; │ │
│ │ │ │
│ │ The AccessExclusiveLock is taken. │ │
│ │ The readers are BLOCKED. │ │
│ │ The old data is discarded. │ │
│ │ The new data is written. │ │
│ │ The lock is released. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE CONCURRENT REFRESH: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ REFRESH MATERIALIZED VIEW CONCURRENTLY mv; │ │
│ │ │ │
│ │ The new data is computed. │ │
│ │ The difference is computed. │ │
│ │ The difference is applied. │ │
│ │ The readers are NOT blocked. │ │
│ │ The unique index is required. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The concurrent refresh is slower but does not block. │
│ │
└──────────────────────────────────────────────────────────────┘The Refresh Strategies
┌──────────────────────────────────────────────────────────────┐
│ THE REFRESH STRATEGIES │
│ │
│ THE FULL REFRESH: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The whole query re-runs. │ │
│ │ The whole table is replaced. │ │
│ │ The simplest. The most expensive. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE CONCURRENT REFRESH: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The whole query re-runs. │ │
│ │ The difference is applied. │ │
│ │ The readers are not blocked. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE INCREMENTAL REFRESH (the pg_ivm): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Only the changes are computed. │ │
│ │ The deltas are applied. │ │
│ │ The fastest. The most complex. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE SCHEDULED REFRESH: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The refresh runs on the schedule. │ │
│ │ The pg_cron or the cron. │ │
│ │ The predictable freshness. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Freshness Trade-off
┌──────────────────────────────────────────────────────────────┐
│ THE FRESHNESS TRADE-OFF │
│ │
│ THE REGULAR VIEW: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The data is always current. │ │
│ │ The query runs on every read. │ │
│ │ The cost is the query. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE MATERIALIZED VIEW (the frequent refresh): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The data is nearly current. │ │
│ │ The reads are fast. │ │
│ │ The refresh runs often. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE MATERIALIZED VIEW (the rare refresh): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The data is stale. │ │
│ │ The reads are fast. │ │
│ │ The refresh runs rarely. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The freshness requirement determines the refresh schedule. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Materialized view | The stored result of a query |
CREATE MATERIALIZED VIEW | Create the view |
WITH NO DATA | Create without the data |
REFRESH MATERIALIZED VIEW | The plain refresh |
REFRESH ... CONCURRENTLY | The non-blocking refresh |
| The unique index | Required for the concurrent refresh |
pg_matviews | The view listing |
pg_get_viewdef | The definition |
pg_cron | The database-level scheduling |
pg_ivm | The incremental refresh |
Key takeaways:
- A materialized view stores the result of a query as a physical table. The query runs once, at the creation, and the result is stored. The subsequent reads query the stored data, and the performance is the same as reading a table. The trade-off is the freshness: the data is stale until the refresh .
- The
CREATE MATERIALIZED VIEWstatement defines the view. The syntax is theCREATE VIEWwith theMATERIALIZEDkeyword. TheWITH NO DATAoption creates the view without populating it, and theREFRESHpopulates it later . - The plain
REFRESH MATERIALIZED VIEWreplaces the whole table. It takes theAccessExclusiveLockand blocks the readers until the refresh is complete. The plain refresh is the choice for the small views or the views that can tolerate the brief block . - The
REFRESH MATERIALIZED VIEW CONCURRENTLYdoes not block the readers. It computes the new data, compares it to the old, and applies the difference. The requirements are the unique index on the view, and the cost is the slower refresh . - The unique index is the requirement for the concurrent refresh. The index must use only the column names, include all the rows, and be unique. Without it, the concurrent refresh fails with the “cannot refresh concurrently” error .
- The refresh strategy must match the freshness requirement. The frequent refresh keeps the data nearly current, and the rare refresh leaves the data stale. The scheduled refresh with the
pg_cronis the standard for the production views . - The
pg_ivmextension provides the incremental refresh. The incremental refresh applies only the changes, which is much faster than the full refresh for the large views. The extension is the advanced topic, and the plain and the concurrent refreshes are the built-in options . - The materialized view can have the indexes. The indexes are created on the view like on a table, and they speed up the queries that filter or join on the view’s columns. The
ANALYZEupdates the planner statistics after the refresh .
Remember: A materialized view is the stored result of a query, and the refresh is the operation that recomputes it. The CREATE MATERIALIZED VIEW defines the view, the REFRESH MATERIALIZED VIEW recomputes it, and the REFRESH MATERIALIZED VIEW CONCURRENTLY recomputes it without blocking the readers. The concurrent refresh requires the unique index, and the refresh strategy must match the freshness requirement. The result is the fast reads for the expensive queries, at the cost of the storage and the freshness.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!