196: Views and Materialized Views: Stable Read Models and Precomputation

Learning outcomes

By the end of this lesson, you should be able to:

explain what a view is and apply one in a realistic implementation;
explain what makes a view updatable and apply an updatable view in a realistic implementation;
explain how views can form security boundaries and apply them appropriately in a realistic implementation;
explain what a materialized view is and apply one in a realistic implementation;
choose and apply a refresh strategy in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the 01–06 foundation and the preceding lessons in this module. Before you continue, retrieve one concrete example from an earlier project where this same design concern appeared. Perhaps a report query was repeated in several places, a schema migration had to preserve an old read contract, or a derived result had to be served faster than the underlying joins could be calculated. The point is not to memorize terms in isolation. It is to make a defensible decision in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency interact.

Terminology

Views: A normal view stores a query definition, not the rows produced by that query. Each read evaluates the definition against the underlying data.
Updatable views: Simple views may be automatically updatable, while views involving complex joins or aggregation often are not. A view that looks writable is not automatically a safe write interface.
Security boundaries: A view can expose only selected columns or rows, but its security is effective only when privileges, security-barrier or security-definer behavior, functions, and access to the underlying objects are all considered. Row-level security may be a better fit for tenant isolation.
Materialized views: A materialized view stores the output of a query. Unlike a normal view, it holds data that can be indexed, but that data can become stale.
Refresh strategy: Select a full refresh, a concurrent refresh, or a separate incremental pipeline according to data size, freshness requirements, locking behavior, and uniqueness requirements.
API stability: During a schema migration, a view can preserve an older column contract for callers while the physical storage underneath it changes.

Mental model

Treat Views and Materialized Views: Stable Read Models and Precomputation as a design problem with observable inputs, outputs, invariants, and failure modes. A normal view is a reusable relational interface: it gives callers a named query without storing another copy of its result. A materialized view goes one step further by persisting query output, which can make expensive reads faster, but introduces freshness, storage, and refresh concerns.

A sound implementation makes its assumptions visible, narrows uncertainty at system boundaries, and leaves enough evidence to demonstrate why the design is safe. That evidence might be tests, types, constraints, metrics, or diagrams. The same discipline is useful in an interview and in production:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not move directly from a requirement to a library call or a database feature. First state what must remain true. Then choose the mechanism that enforces that invariant and decide how you will observe failures when it does not.

Deep dive

1. Views

When the same join, filter, or column selection appears in several consumers, duplicating the SQL creates several contracts to maintain. A normal view gives that relational shape a name and a single query definition. It can simplify read permissions, present a stable read model, and hide storage complexity from callers. It does not, however, store the result. The database still has to plan and execute the underlying query when the view is read, and a deep stack of views can make plans, ownership, and debugging difficult to understand.

Decision rule: Use a view deliberately when it makes the contract or invariant easier to prove. If it merely saves keystrokes while hiding an assumption, an explicit query or repository boundary may be easier to reason about.

2. Updatable views

The confusing case is a view that looks like a table but does not accept writes in the way a caller expects. PostgreSQL can automatically update some simple views, while complex views that aggregate or combine data through joins often cannot be updated automatically. Even when an update is technically possible, that does not make the view a safe write API: you still need to understand which base table changes, which constraints run, and whether the apparent row maps unambiguously to stored data.

Decision rule: Use an updatable view deliberately when it makes the write contract or invariant easier to prove. If it only makes a write look simpler while hiding which base rows change, use an explicit write path or a carefully designed function instead.

3. Security boundaries

A view that selects only approved columns or filters rows can be a useful boundary between database storage and a consumer. It is not security by appearance alone. Effective protection depends on the caller's privileges, whether the view needs security-barrier or security-definer behavior, what functions it invokes, and whether the caller can reach the underlying tables through another path. For tenant isolation, row-level security may express the policy more directly than a view alone.

Treat the view as one part of the authorization design. Test both the allowed query and the forbidden paths, and verify the actual database roles rather than relying on the client to request only safe columns or rows.

Decision rule: Use security boundaries deliberately when they make the contract or invariant easier to prove. If the design leaves another privilege path to the same sensitive data, the view is not sufficient as the security boundary.

4. Materialized views

Some read models are expensive because they scan large tables, join many relations, or aggregate a substantial amount of history. A materialized view stores that query output, and the stored result can have indexes. That makes it useful for reporting and derived read models where a bounded amount of staleness is acceptable.

The trade-off is explicit: a normal view gives current underlying data but pays the query cost at read time; a materialized view can make reads cheaper but serves the last successfully refreshed result. Its storage also has to be maintained, and consumers need a clear definition of how fresh the data is allowed to be.

Decision rule: Use a materialized view deliberately when the read-time cost justifies persisted derived data and the product can state an acceptable freshness window. If the read must always reflect the latest transaction, use a normal query or another design instead.

5. Refresh strategy

Refreshing is part of the materialized-view design, not an operational detail to decide later. Choose a full refresh, a concurrent refresh, or a different incremental pipeline based on the result size, freshness target, lock tolerance, and uniqueness requirements. A concurrent refresh can help preserve read availability, but it has its own prerequisites and cost; it is not a universal replacement for a full refresh. An incremental pipeline may be justified when rebuilding the entire result is too expensive, but it adds tracking and correctness complexity.

Refreshing on every request defeats the point of precomputation. Define who triggers refreshes, how failures are retried or reported, what timestamp represents the result's freshness, and what callers should do while the data is stale.

Decision rule: Use a refresh strategy deliberately when it makes the freshness and operational contract easier to prove. If the refresh cost, locking behavior, or uniqueness assumptions are unknown, measure and inspect them before committing to the design.

6. API stability

A schema migration does not always let every caller move at once. A view can preserve an older column contract while storage evolves underneath it: the view can rename a new column, select a compatible subset, or reshape the stored data for existing consumers. This makes the view a compatibility layer, not merely a convenience query.

That layer still needs ownership and a removal plan. Document which clients depend on it, test the old contract, and avoid allowing the compatibility view to become an unexamined stack of permanent indirection.

Decision rule: Use API stability deliberately when it makes the compatibility contract or migration invariant easier to prove. If it only postpones an unclear migration, make the dependency and retirement criteria explicit instead.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Begin by stating the requirement in one sentence, listing the input and output contracts, and identifying which concept owns each failure mode. The useful separation is this: parsing and validation belong at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing those concerns can make a happy-path demo shorter, but it makes retries, malformed data, and edge cases much harder to reason about.

For example, a customer summary might be defined as follows:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN keeps active customers with no orders in the result, and COUNT(o.id) gives those customers a count of zero rather than counting the null-extended join row. The grouping defines the result's grain as one row per active customer. If this query becomes a shared read contract, a normal view may centralize that contract. If it becomes expensive and bounded staleness is acceptable, a materialized view may be a better fit. Either way, inspect the plan and confirm the indexes and permissions rather than assuming the abstraction makes the query cheap or secure.

Walk through at least four cases: the normal path; an empty or missing value; a duplicate, retry, or concurrent path where that concern applies; and a dependency failure. For each case, identify the layer that detects the problem and what the caller observes. That is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after evidence identifies the bottleneck or risk.

For a materialized view, production questions include how old the result may be, how refresh duration is measured, what happens after a failed refresh, and whether an index or refresh operation changes the locking or storage profile. For a normal view, ask whether nested definitions make the execution plan or ownership path too opaque.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted.

Guided lab

Create an active_customer_summary view and a materialized monthly sales view. Add appropriate permissions and indexes, write a refresh runbook, and measure the stale-data window the product is accepting. The lab should make the operational trade-off visible: which reads need current data, which can tolerate a delay, and what happens when a refresh fails.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

Views: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes.
Updatable views: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Confirm which writes are actually accepted and which base rows they affect.
Security boundaries: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Test unauthorized roles and alternate access paths, not only the intended query.
Materialized views: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Test stale output, an empty result, an index assumption, and a failed refresh.
Refresh strategy: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Observe refresh duration, locking, retry behavior, and the freshness timestamp.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case, inspect the actual result or execution plan, and trace the boundary where the invariant first becomes false. With a normal view, inspect its definition and the expanded plan. With an updatable view, verify the update rules and the affected base rows. With a security boundary, inspect the active role and all reachable privileges. With a materialized view, compare the result's refresh timestamp with the source data and inspect the refresh logs. Fix the owning layer rather than adding a downstream patch.

Interview questions

What problem do Views solve, and what trade-off or failure mode would make you choose a different approach?
What problem do Updatable views solve, and what trade-off or failure mode would make you choose a different approach?
What problem do Security boundaries solve, and what trade-off or failure mode would make you choose a different approach?
What problem do Materialized views solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Refresh strategy solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Views and Materialized Views: Stable Read Models and Precomputation to another developer in five minutes. Include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson's SQL.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.