Materialized Views in SQL
Introduction
A materialized view is a database object that physically stores the result of a query. Instead of repeatedly joining, filtering, sorting, and aggregating the underlying tables for every request, consumers can read a previously computed result. The database refreshes or maintains that result according to product capabilities and the chosen strategy.
This storage can make suitable analytical queries much faster, but it introduces data duplication, refresh cost, operational ownership, and possible staleness. A materialized view is therefore a performance and data-product decision, not merely a different spelling of CREATE VIEW.
Syntax and behavior differ significantly among PostgreSQL, Oracle, SQL Server, MySQL, and SQLite. The primary examples below use PostgreSQL syntax unless a section identifies another product.
Why Materialized Views Exist
Regular views centralize query logic but usually do not avoid the underlying work. A dashboard that repeatedly scans millions of transactions, joins dimensions, and calculates totals can consume CPU, memory, I/O, and concurrency capacity each time it refreshes.
Materializing an appropriately stable result moves that cost to a controlled population or refresh operation. Readers then scan a smaller stored dataset that can have its own indexes. The tradeoff is that the stored result may lag behind source changes.
Regular View vs Materialized View
| Feature | Regular view | Materialized view |
|---|---|---|
| Stores query definition | Yes | Yes |
| Stores result rows | Normally no | Yes |
| Freshness | Reads current visible base data | Depends on maintenance or refresh |
| Read cost | Includes underlying query work | Reads precomputed data |
| Storage cost | Usually small metadata | Result rows plus indexes |
| Best use | Reusable logic and abstraction | Repeated expensive, freshness-tolerant reads |
How a Materialized View Works
The database executes the defining query, writes the rows into managed storage, records metadata and dependencies, and exposes the stored result through a relation-like name. Consumers query that object without executing the entire source query each time.
When source tables change, the stored rows do not automatically become current unless the product provides and uses automatic maintenance. A refresh may recompute all rows, apply eligible deltas, or use product-specific logs and rewrite mechanisms.
Sample Database
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
salary DECIMAL(10, 2) NOT NULL,
employment_status VARCHAR(20) NOT NULL,
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
One employee belongs to at most one department. These keys make department-level aggregation predictable.
Creating a Materialized View
CREATE MATERIALIZED VIEW employee_salary_summary AS
SELECT department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary
FROM employees
WHERE employment_status = 'ACTIVE'
GROUP BY department_id;
PostgreSQL executes the query during creation and stores one row per department. Later reads avoid rescanning every employee for these metrics until a refresh is required.
Creating a Materialized View with a Join
CREATE MATERIALIZED VIEW department_salary_report AS
SELECT d.department_id,
d.department_name,
COUNT(e.employee_id) AS employee_count,
COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments AS d
LEFT JOIN employees AS e
ON e.department_id = d.department_id
AND e.employment_status = 'ACTIVE'
GROUP BY d.department_id, d.department_name;
The join and aggregation run during population or refresh. Departments with no active employees remain present because the employee filter is in the join condition.
Creating a Filtered Materialized View
CREATE MATERIALIZED VIEW high_salary_employees AS
SELECT employee_id, employee_name,
department_id, salary
FROM employees
WHERE employment_status = 'ACTIVE'
AND salary >= 70000;
Materializing a simple filter is worthwhile only when the subset is expensive or heavily reused enough to justify duplicated storage and refresh work. A normal index may be a better solution.
Querying Stored Results
SELECT department_id,
employee_count,
total_salary
FROM employee_salary_summary
ORDER BY department_id;
To the consumer, the object behaves much like a read-only table. The important difference is ownership: applications should not treat it as authoritative source data when it can be stale.
Understanding Data Freshness
Freshness is the time difference between source truth and stored materialized results. After an employee salary changes, a PostgreSQL materialized summary continues showing the old total until refreshed. That is expected behavior, not corruption.
Define a freshness service level such as five minutes, hourly, or after the nightly load. Display or expose a last-successful-refresh timestamp when consumers need to judge result age.
Refreshing in PostgreSQL
REFRESH MATERIALIZED VIEW employee_salary_summary;
A normal PostgreSQL refresh recomputes and replaces the stored contents. It can block readers while the replacement occurs, depending on the operation and locks. Measure duration and schedule it according to workload.
Complete vs Incremental Refresh
A complete refresh reruns the defining query and rebuilds the full result. It is conceptually simple and reliable but can become expensive as data grows. Incremental or fast refresh applies eligible source changes instead of recomputing everything.
Incremental support is product-specific and may require logs, keys, restricted query shapes, or automatic maintenance. PostgreSQL's built-in REFRESH MATERIALIZED VIEW performs a full refresh; extensions or custom pipelines are needed for other patterns.
Concurrent Refresh in PostgreSQL
CREATE UNIQUE INDEX ux_employee_salary_summary_department
ON employee_salary_summary (department_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY
employee_salary_summary;
A concurrent refresh lets reads continue against the old result while PostgreSQL builds and reconciles the new result. It requires a qualifying unique index over plain columns that identifies each row and generally requires the view to be populated. It also has additional overhead and cannot run inside every transactional context.
Creating without Initial Data
CREATE MATERIALIZED VIEW department_salary_report AS
SELECT department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
WITH NO DATA;
PostgreSQL creates the object without executing the query. The materialized view is unscannable until it is populated with a normal refresh. This is useful when creation and initial loading must be scheduled separately.
Refreshing with NO DATA
REFRESH MATERIALIZED VIEW department_salary_report
WITH NO DATA;
This empties the materialized view and marks it unscannable until a later refresh with data. It is an operational tool, not a routine freshness strategy.
Creating Indexes on Stored Results
CREATE INDEX ix_department_salary_total
ON department_salary_report (total_salary DESC);
Indexes can accelerate filters, joins, and ordering against the materialized result. They consume storage and add maintenance during refresh. Choose indexes from actual consumer queries rather than copying every source-table index.
Using Multiple Indexes
A reporting object may need a unique key for concurrent refresh and another index for common access:
CREATE UNIQUE INDEX ux_department_salary_id
ON department_salary_report (department_id);
CREATE INDEX ix_department_salary_name
ON department_salary_report (department_name);
Monitor index size and usage. Too many indexes can make refresh slower without improving reads.
Can Materialized Rows Be Updated Directly?
PostgreSQL materialized views are not directly writable with ordinary INSERT, UPDATE, or DELETE. Their rows are generated from the defining query. Change source data and refresh the materialized object.
Other products implement different maintenance models, but consumers should still treat derived stored results as generated data rather than an independent source of truth.
Dropping a Materialized View
DROP MATERIALIZED VIEW IF EXISTS
reporting.department_salary_report;
This removes stored results, indexes attached to the object, and its definition. It does not delete employee or department rows. Dependency behavior and cascade options vary by product.
Renaming a Materialized View
ALTER MATERIALIZED VIEW department_salary_report
RENAME TO department_salary_summary;
This PostgreSQL operation changes the object name. Applications and dependent external SQL using the old name must migrate. Schema moves, ownership changes, and other alterations have separate syntax.
Changing the Definition
PostgreSQL does not offer a general CREATE OR REPLACE MATERIALIZED VIEW equivalent for changing the defining query. A common migration creates a new object, builds indexes, populates and validates it, switches consumers, and later drops the old object.
This versioned approach avoids a long destructive gap and supports comparison between old and new results.
PostgreSQL Materialized Views
PostgreSQL provides explicit creation, refresh, concurrent refresh, no-data states, ordinary indexes, ownership, schema changes, and drop syntax. Scheduling is external: use a job scheduler, extension, orchestration platform, or application process to trigger refreshes.
Built-in refresh recomputes the defining query. Concurrent refresh improves availability but has unique-index and operational requirements.
Oracle Materialized Views
Oracle has extensive materialized-view capabilities including complete, fast, and force refresh choices; on-demand or scheduled refresh; materialized view logs; query rewrite; and several consistency options. Eligibility depends on query shape and configuration.
Oracle terminology and syntax should be designed from Oracle documentation for the deployed version. Fast refresh is not automatic for every query, and logs add source-write and storage overhead.
SQL Server Indexed Views
SQL Server's closest built-in concept is an indexed view. A schema-bound view receives a unique clustered index, physically storing and maintaining its result as base data changes. Required session options and strict definition rules apply.
Unlike scheduled PostgreSQL refresh, indexed-view maintenance adds work to source writes. The optimizer's use of the indexed result can depend on edition, query, options, and hints. Evaluate read gains against write amplification.
MySQL Alternatives
MySQL does not provide native general-purpose materialized views. Teams commonly use summary tables populated by scheduled events, jobs, ETL pipelines, or triggers. The table, refresh procedure, atomic-swap process, and freshness metadata must be designed explicitly.
Calling such a table a materialized-view pattern does not give it database-managed dependencies or refresh semantics. Operational ownership is essential.
SQLite Alternatives
SQLite has ordinary views but no native materialized-view object. An application can populate a summary table and rebuild or incrementally maintain it in transactions. Triggers may help for simple changes but can become difficult for complex aggregates.
Materialized Views vs Tables
Both store rows and can be indexed. A table is normally authoritative data managed directly by application writes. A materialized view is derived from a defining query and refreshed or maintained from source objects.
A manually maintained summary table offers flexibility but requires custom lineage, refresh, consistency, recovery, and permission logic.
Materialized Views vs CTEs
A common table expression exists within one statement and improves query organization or recursion. It does not normally persist reusable result rows after the statement. A materialized view is a durable database object shared by many queries and refreshed independently.
Materialized Views vs Temporary Tables
A temporary table usually belongs to a session or transaction and supports intermediate processing. A materialized view is a persistent managed object intended for repeated use. Temporary tables can be written directly; materialized results follow their product's refresh model.
Materialized Views vs Caches
Both trade freshness for faster reads, but a materialized view lives within the database, participates in database permissions, and can be queried relationally. An application or distributed cache may serve objects closer to clients but needs explicit invalidation, serialization, and consistency behavior.
Performance Benefits
The largest gains occur when a small stored result replaces frequent expensive scans, joins, and aggregations. Benefits depend on reuse, source size, selectivity, refresh frequency, indexes, and concurrency. A materialized view that is rarely read can cost more to maintain than it saves.
Performance Costs
Population and refresh consume CPU, I/O, memory, temporary space, locks, logs, and replication bandwidth. Stored rows and indexes consume disk and backup capacity. Automatic maintenance or logs can slow base-table writes.
Measure the full lifecycle, including peak refresh load, not only dashboard latency after materialization.
Refresh Scheduling
Choose schedules from business freshness requirements and source-change patterns. Nightly refresh suits static daily reports, while operational dashboards may need minutes. Event-driven refresh can reduce lag but may cause excessive rebuilds during bursts.
A scheduler should prevent overlapping jobs, apply timeouts, record duration and row count, retry safely, and alert on failure.
Refresh Consistency
A refresh should compute from a coherent source snapshot according to database isolation. Multi-source systems can still represent different extraction times unless coordinated. Document the as-of meaning of each result.
Concurrent readers may see old data until an atomic replacement becomes visible. That predictable behavior is often preferable to exposing a partially rebuilt table.
Atomic Swap Pattern
For manually maintained summary tables, build a new version under a temporary name, validate it, create indexes, and switch consumers through an atomic rename or stable view where the product supports a safe operation. Keep the old version briefly for rollback.
Do not truncate a production summary and refill it while readers can observe an empty or partial result.
Incremental Maintenance Challenges
Insert-only sums are relatively simple; updates, deletes, distinct counts, minimums, maximums, late events, dimension changes, and many-to-many joins are harder. An incremental pipeline must be idempotent and able to reconcile missed or duplicated changes.
Periodic full reconciliation protects against silent drift even when deltas are used for routine freshness.
Security and Permissions
Grant consumers access only to approved stored columns and rows. Review owner, refresh account, source-table access, definer or invoker behavior, and whether users can bypass the interface. Stored sensitive data also affects encryption, backup, masking, and retention obligations.
Storage and Capacity Planning
Estimate result size, index size, temporary refresh space, write-ahead logs, replicas, backups, and growth. Concurrent or versioned refresh may temporarily require multiple copies. Set capacity alerts before the refresh fails for lack of disk.
Monitoring Refresh Health
Track last start and completion time, duration, status, row count, source watermark, freshness lag, failures, retries, lock waits, and storage growth. Alert on stale data, not only failed jobs, because a scheduler can stop running without producing an explicit database error.
Testing Correctness
Compare the stored result with the defining query on representative fixtures. Test empty sources, nulls, duplicate keys, unmatched joins, boundary dates, time zones, late changes, updates, and deletes. Reconcile counts and totals independently rather than copying the same flawed formula into the test.
Testing Refresh Behavior
Change source data, prove that the materialized result remains at its previous state, refresh it, and confirm the expected change. Test failures, retries, concurrent readers, overlapping-job prevention, no-data states, and recovery after interruption.
Testing Performance
Benchmark source queries and materialized reads with realistic data, filters, concurrency, and cache conditions. Measure refresh duration and source impact. A fast single read does not justify materialization if refresh overloads the production system.
Schema Changes and Dependencies
Renaming or removing source columns can break refreshes. Type and semantic changes can produce incorrect stored output without syntax failure. Maintain lineage, test migrations against dependencies, and update materialized objects in controlled order.
Changing Business Logic Safely
For a changed definition, create a versioned materialized view, populate it, compare old and new results, migrate consumers, and retain the old version through the rollback window. Record the effective date and reason for metric changes.
Designing the Refresh Job
A refresh job needs more than a schedule. It should acquire a run lock, record a unique execution identifier, capture start time and source watermark, apply a timeout, execute the refresh, validate row counts or checksums, update freshness metadata, and publish success only after all checks pass.
Use a dedicated service account with only the required source and object privileges. Keep scheduler history outside the materialized view so an empty or failed rebuild cannot erase its own operational evidence.
Preventing Overlapping Refreshes
If one refresh takes longer than its interval, the next job can overlap and double source load, compete for locks, or publish results out of order. Use scheduler concurrency controls, advisory locks, or a durable run table to ensure only one compatible refresh executes at a time.
Do not rely only on an in-memory flag when multiple scheduler instances exist. Define what happens when a stale lock remains after a process crash and how operators can clear it safely.
Handling Refresh Failures
A failed refresh should leave readers with the last known valid result whenever the product and strategy permit. Record the error, preserve diagnostic context, alert the owner, and avoid updating the last-successful timestamp. Retry only errors that are genuinely transient.
For custom rebuild tables, validate the new copy before switching names. Never replace a valid production result with an empty, partial, or unverified build merely because the load process reached its final step.
Source Watermarks and As-Of Metadata
A completion timestamp shows when a refresh ended, but not necessarily which source data it contains. Capture a source transaction identifier, ingestion watermark, maximum event time, or warehouse batch identifier that describes the included dataset.
Expose both refresh time and data-as-of time to consumers. A job can finish now while intentionally materializing data through yesterday, and that distinction matters for operational decisions and incident diagnosis.
Replication and Read Replicas
Materialized result storage, index builds, and refresh logs can increase replication traffic and lag. Refreshing independently on a replica may reduce primary load but can produce a different snapshot or violate the product's replica restrictions.
Decide where the object is built, how it reaches replicas, and which node consumers query. Monitor replication delay during refresh and include failover behavior in the runbook so a promoted replica does not serve unexpectedly stale or missing results.
Backup and Disaster Recovery
Because a materialized view is derived, teams may choose to rebuild it rather than back up every stored row. That choice must consider rebuild duration, source availability, recovery objectives, network cost, and whether the defining query depends on historical data no longer retained.
Store definitions, indexes, grants, schedules, and validation logic in version control. Test rebuilding after a restore, including the time and temporary capacity required before consumers can resume.
Partitioning Strategies
Large time-based summaries may benefit from partitioned tables maintained as a materialization pattern, especially when only recent periods change. Native materialized-view partitioning support varies, so teams often build partitioned summary tables through ETL.
Refresh mutable partitions, preserve closed periods, and handle late-arriving events with targeted restatement. Keep a reconciliation path that can detect drift across all partitions rather than trusting incremental maintenance forever.
Query Rewrite
Some systems, notably Oracle and SQL Server in applicable configurations, can match a query against a materialized or indexed result even when the query does not explicitly name it. This query-rewrite behavior depends on eligibility, constraints, freshness, optimizer settings, and product edition or version.
Verify actual execution plans instead of assuming rewrite occurs. A stored summary provides no read benefit when consumers continue scanning source tables and the optimizer cannot substitute it.
Cost Modeling
Estimate the baseline cost as source-query executions multiplied by frequency and concurrency. Compare it with refresh compute, source impact, storage, indexes, logs, backups, replication, monitoring, and operational support. Include peak rather than average demand.
Materialization is justified when measured read savings and workload stability exceed lifecycle costs while meeting freshness requirements. Reassess as source volume, query patterns, and infrastructure prices change.
Change Data Capture and Delta Pipelines
Change data capture can feed incremental summary maintenance, but a stream of row changes is not itself a correct aggregate algorithm. The pipeline must handle ordering, duplicate delivery, updates represented as before-and-after values, deletes, retries, schema evolution, and replay.
Use idempotent processing and durable checkpoints. Compare incremental output with periodic full recomputation so missed events or logic defects do not create permanent silent divergence.
Zero-Downtime Replacement
For an incompatible definition change, build a new materialized object under a versioned name, populate it, create indexes, apply grants, and run correctness and performance checks while the old object serves traffic. Then switch a stable regular view or consumers to the new version in a controlled release.
Retain the old version through the observation and rollback window. Remove it only after query telemetry and owner confirmation show that no supported consumer remains.
Capacity during Rebuilds
A refresh can require space for old and new copies, index construction, sorts, hashes, temporary files, transaction logs, and replication queues at the same time. A result occupying 100 GB may need substantially more than 100 GB of free capacity during maintenance.
Load-test with production-scale cardinality, set disk and temporary-space alerts, and define behavior when capacity is insufficient. Failing before replacement is safer than exhausting shared database storage.
Ownership and Documentation
Assign one accountable owner for the metric definition and one operational owner for refresh health when responsibilities differ. Document consumers, row grain, unique key, source lineage, freshness SLA, schedule, indexes, permissions, expected size, validation queries, alerts, and recovery procedure.
Without ownership, stale stored data can remain fast and available while being wrong, which is more dangerous than an obvious query failure.
When to Use a Materialized View
- The source query is expensive and executed frequently.
- The stored result is substantially smaller than the sources.
- Consumers can tolerate defined freshness lag.
- Metrics and row grain are stable and governed.
- Refresh cost fits the operational window.
- Indexes on the stored result improve common access paths.
When to Avoid a Materialized View
- Consumers require transaction-current results.
- The query is already fast with proper indexes.
- The result is rarely reused.
- Sources change so often that refresh never catches up.
- Storage or write overhead exceeds the read benefit.
- No team owns refresh, monitoring, and recovery.
Real-World Sales Dashboard
A daily sales summary by region, product category, and currency can replace repeated scans of transaction history. Refresh after the warehouse load, expose the data timestamp, and reconcile totals with the ledger. Late transactions may require restating previous periods.
Real-World Banking Reporting
Regulatory and risk reports can benefit from precomputed snapshots, but they require exact as-of timestamps, posting and reversal rules, currency precision, audit lineage, approvals, and reproducibility. Freshness and immutable reporting snapshots must not be confused.
Real-World Employee Analytics
Department headcount, salary bands, and attrition metrics are often read repeatedly and change less frequently than operational records. A scheduled materialized result can protect transactional systems, provided access to sensitive compensation remains controlled.
Real-World Test Dashboard
A test analytics object can precompute pass rates, duration percentiles, flaky-test indicators, and failure categories from millions of execution events. Define whether retries replace or add attempts and refresh after ingestion reaches a stable watermark.
Real-World Data Warehouse
Warehouse materialized summaries can support repeated dimensional queries and query rewrite. They need partition-aware refresh, late-arriving fact handling, dimension-change rules, workload management, and alignment with load orchestration.
Common Mistakes
- Assuming materialized results stay current automatically.
- Creating one without a freshness requirement or owner.
- Refreshing too often and overloading source tables.
- Ignoring unique-index requirements for concurrent refresh.
- Copying source indexes without workload evidence.
- Allowing readers to observe a partial manual rebuild.
- Using materialization before fixing the source query and indexes.
- Ignoring updates, deletes, and late data in incremental logic.
- Failing to expose and monitor last successful refresh.
- Applying PostgreSQL syntax to products with different concepts.
Best Practices
- Define row grain, owner, consumers, and freshness SLA.
- Measure read savings and full refresh cost.
- Use a unique stable key where possible.
- Index from consumer access patterns.
- Prevent overlapping refresh jobs.
- Expose refresh metadata and alert on staleness.
- Use atomic replacement for custom summary tables.
- Reconcile incremental results periodically.
- Version incompatible logic changes.
- Test correctness, failure recovery, concurrency, and capacity.
Interview Questions and Answers
What is a materialized view?
It is a database object that stores the result of a defining query for later reads.
How does it differ from a regular view?
A regular view normally stores only query logic; a materialized view also stores result rows.
Why can materialized data be stale?
Source changes are not reflected until the product's maintenance or refresh process updates the stored result.
What is concurrent refresh in PostgreSQL?
It rebuilds while allowing reads of the existing result, subject to a qualifying unique index and other requirements.
Can PostgreSQL materialized rows be updated directly?
No. Change source data and refresh the materialized view.
What is the SQL Server equivalent?
An indexed view is the closest built-in feature, with schema binding, indexing, session requirements, and automatic write-time maintenance.
When is materialization a poor choice?
When results must be current, reads are already inexpensive, reuse is low, or refresh and storage costs exceed the benefit.
Design Review Checklist
| Area | Required question |
|---|---|
| Contract | What does one row mean, and what is its unique key? |
| Freshness | How stale may the result be, and how is age exposed? |
| Refresh | Who schedules, retries, and monitors maintenance? |
| Performance | Do measured read savings exceed lifecycle cost? |
| Consistency | What snapshot and late-data rules apply? |
| Security | Are stored sensitive results protected and retained correctly? |
| Recovery | Can the object, indexes, grants, and data be rebuilt safely? |
Quick Reference
-- PostgreSQL creation
CREATE MATERIALIZED VIEW department_headcount AS
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
-- Index and refresh
CREATE UNIQUE INDEX ux_department_headcount
ON department_headcount (department_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY
department_headcount;
-- Removal
DROP MATERIALIZED VIEW department_headcount;
Key Takeaway
A materialized view stores query results so repeated expensive reads can use precomputed data. The speed comes from shifting work to creation or maintenance and accepting a defined relationship between freshness, storage, and refresh cost.
Use materialization only after measuring the workload. Define grain and freshness, select the correct product-specific feature, index from real access patterns, schedule and monitor refreshes, test failure and recovery, and reconcile stored results with source truth. A materialized view is an operated data product, not a free cache.