Chapter 15 · Views, Routines, and Database Automation
Materialized Views and Refresh Strategies
A materialized view trades freshness and refresh work for faster reads. The important design question is not merely how to store a query result, but how to keep its age, consistency, and operational cost within an explicit contract.
Learning outcomes
Engineer a stored query result with an explicit freshness contract
Contrast ordinary views, materialized views, summary tables, and caches.
Quantify staleness and choose a refresh cadence from business requirements.
Design full, concurrent, and incremental refresh strategies.
Emulate a transactionally refreshed materialized summary in SQLite.
Validate completeness and detect refresh failure.
Materialization changes the read path
A materialized view persists the output of a defining query. Reads can avoid repeating expensive joins or aggregation, but the stored rows can lag behind the source. PostgreSQL supports native materialized views; SQLite does not, so a table plus a controlled refresh process is the common emulation.
Materialization moves work from every read to refresh time and introduces a freshness boundary.
Ordinary versus materialized interfaces
| Property | Ordinary view | Materialized view / summary table |
|---|---|---|
| Storage | Definition only | Definition plus persisted result rows |
| Freshness | Reflects the query snapshot at read time | Reflects the most recent successful refresh |
| Read cost | Underlying query runs for each reference | Reads precomputed rows |
| Write path | Base objects are written | Source writes plus refresh maintenance |
| Failure mode | Slow or unavailable source query | Stale, incomplete, or blocked refresh |
Define a freshness service level
Let ts be the newest source event represented in the materialized result and tq the query time. The observed data age is:
For a periodic full refresh, a practical upper-bound estimate is:
A five-minute schedule is not a five-minute freshness promise when refresh duration or repeated failure is ignored.
PostgreSQL native materialized view
CREATE MATERIALIZED VIEW daily_paid_sales ASSELECT ordered_at::date AS sales_date, COUNT(*) AS paid_order_count, SUM(total_cents) AS paid_revenue_centsFROM sales_orderWHERE status = 'paid'GROUP BY ordered_at::dateWITH DATA;CREATE UNIQUE INDEX uq_daily_paid_sales_date ON daily_paid_sales (sales_date);REFRESH MATERIALIZED VIEW daily_paid_sales;-- Requires a qualifying UNIQUE index and an already populated view.REFRESH MATERIALIZED VIEW CONCURRENTLY daily_paid_sales;A nonconcurrent refresh replaces the contents and may block readers. PostgreSQL’s concurrent option reduces read disruption but imposes eligibility and resource constraints; only one refresh may run against a given materialized view at a time.
SQLite emulation with metadata
DROP TABLE IF EXISTS daily_paid_sales_mv;DROP TABLE IF EXISTS refresh_state;CREATE TABLE daily_paid_sales_mv ( sales_date TEXT PRIMARY KEY, paid_order_count INTEGER NOT NULL, paid_revenue_cents INTEGER NOT NULL) STRICT;CREATE TABLE refresh_state ( object_name TEXT PRIMARY KEY, refreshed_at TEXT NOT NULL, source_max_at TEXT, row_count INTEGER NOT NULL CHECK (row_count >= 0)) STRICT;BEGIN IMMEDIATE;DELETE FROM daily_paid_sales_mv;INSERT INTO daily_paid_sales_mv (sales_date, paid_order_count, paid_revenue_cents)SELECT date(ordered_at), COUNT(*), SUM(total_cents)FROM sales_orderWHERE status = 'paid'GROUP BY date(ordered_at);INSERT INTO refresh_state (object_name, refreshed_at, source_max_at, row_count)VALUES ('daily_paid_sales_mv', datetime('now'), (SELECT MAX(updated_at) FROM sales_order), (SELECT COUNT(*) FROM daily_paid_sales_mv))ON CONFLICT (object_name) DO UPDATE SET refreshed_at = excluded.refreshed_at, source_max_at = excluded.source_max_at, row_count = excluded.row_count;COMMIT;The transaction prevents readers from observing the summary after deletion but before repopulation. For large results, a build-and-swap strategy may reduce the replacement window, subject to the engine’s schema-lock behavior.
Refresh strategy choices
Full refresh
Recompute everything. It is simple and self-healing, but cost grows with source size.
Incremental refresh
Apply only changes since a checkpoint. It can scale better but must handle updates, deletes, late events, and replay.
Build and swap
Populate a replacement object, validate it, then switch readers. This separates build failure from the current result.
On demand
Refresh after an event or before a report. It improves timeliness but couples refresh load to operational activity.
Incremental maintenance is a correctness problem
An incremental design needs a complete change model. Inserts alone are easy; updates that move rows between groups and deletes require subtracting old contributions. A robust design commonly uses a change log, monotonic checkpoint, idempotent batches, and periodic full reconciliation.
| Change | Required action | Common risk |
|---|---|---|
| New paid order | Add count and amount to its day | Duplicate application after retry |
| Amount correction | Subtract old value; add new value | Old value unavailable |
| Status paid → cancelled | Remove prior contribution | Delete/update event omitted |
| Date correction | Move contribution between days | One side of move applied |
| Late event | Update an older partition | Refresh window too narrow |
Validate before publishing freshness
-- Summary rows and source groups should agree.WITH source AS ( SELECT date(ordered_at) AS sales_date, COUNT(*) AS paid_order_count, SUM(total_cents) AS paid_revenue_cents FROM sales_order WHERE status = 'paid' GROUP BY date(ordered_at))SELECT 'source_only' AS problem, s.sales_dateFROM source AS sLEFT JOIN daily_paid_sales_mv AS m USING (sales_date)WHERE m.sales_date IS NULLUNION ALLSELECT 'mismatch', s.sales_dateFROM source AS sJOIN daily_paid_sales_mv AS m USING (sales_date)WHERE s.paid_order_count <> m.paid_order_count OR s.paid_revenue_cents <> m.paid_revenue_centsUNION ALLSELECT 'materialized_only', m.sales_dateFROM daily_paid_sales_mv AS mLEFT JOIN source AS s USING (sales_date)WHERE s.sales_date IS NULL;SELECT object_name, refreshed_at, source_max_at, CAST((julianday('now') - julianday(refreshed_at)) * 86400 AS INTEGER) AS refresh_age_secondsFROM refresh_state;Indexes belong to the read contract
Materialized results should be indexed for their actual consumers. The defining query’s grouping keys are not automatically the only useful access path.
CREATE INDEX idx_daily_paid_sales_revenue ON daily_paid_sales_mv (paid_revenue_cents DESC, sales_date);EXPLAIN QUERY PLANSELECT sales_date, paid_revenue_centsFROM daily_paid_sales_mvORDER BY paid_revenue_cents DESCLIMIT 10;Operational checklist
| Control | Question |
|---|---|
| Freshness SLO | How old may the result become before it is unfit for use? |
| Ownership | Which service or job performs and monitors refresh? |
| Concurrency | Can readers continue during refresh? |
| Failure semantics | Does the last good result remain available? |
| Validation | How are counts, totals, and checkpoints reconciled? |
| Recovery | Can a failed or duplicated batch be retried idempotently? |
| Observability | Are duration, age, rows, errors, and source lag recorded? |
Check your understanding
- Why does materialization improve reads but not eliminate cost?
- What terms belong in a maximum-staleness estimate?
- Why is an incremental refresh harder than processing new inserts?
- Why should refresh metadata be updated in the same transaction as the result?
Review the answers
The work moves to refresh and storage maintenance. Maximum age includes interval, duration, and recovery delay. Updates, deletes, late data, and retries require inverse operations and idempotency. Atomic metadata prevents the system from claiming freshness for an incomplete result.
Summary and references
- Materialization is a freshness-for-speed tradeoff.
- Define the acceptable data age before choosing a refresh mechanism.
- Full refresh is simpler; incremental refresh needs complete change semantics.
- Keep the last valid result, validate replacements, and publish refresh metadata atomically.
- Index the stored result for its readers and monitor refresh age continuously.