View vs. Materialized View: The Differences That Matter

1. What a view really is: a stored query

A view is, at its core, nothing other than a named, stored SQL query. When a view is referenced in a SELECT statement, the database internally replaces the view name with the stored query and executes the whole thing as one combined statement. There is no physically stored data behind a view of its own, only the definition of the query itself is stored in the system catalog. That explains the central characteristic of every view: it is guaranteed to return current data on every access, because the underlying query is freshly executed against the base tables every single time.

This property makes a view a pure abstraction tool. Complex JOINs, aggregations or filter conditions can be hidden behind a simple, descriptive name, without the application needing to know the underlying complexity. Access rights can be controlled more granularly through views than through tables, for example by exposing only certain columns of a sensitive table in a view while the base table itself remains unreachable for the user.

The drawback follows directly from this mechanism: since a view stores no data of its own, every access costs the full execution time of the underlying query. A view over a complex aggregation with several JOINs across large tables is just as expensive for every single SELECT as the original query itself, no caching of the result takes place at all.

-- A standard view: stores the query definition, not the result
CREATE VIEW active_customer_summary AS
SELECT
  c.id,
  c.name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;

-- Every query against the view re-executes the underlying SQL
SELECT * FROM active_customer_summary WHERE lifetime_value > 1000;
-- Always reflects the current state of customers and orders,
-- but pays the full cost of the JOIN and aggregation on every call

2. What a materialized view really is: a stored result

A materialized view flips the principle of a regular view around: instead of storing only the query, the result of the query is physically stored on disk, similar to a regular table. Accessing a materialized view reads exclusively these stored result rows, without re-executing the underlying query. This makes reads against a materialized view as fast as reads against a regular table, regardless of how complex the original query was.

The price of this speed is freshness. As soon as the base tables change, the materialized view initially knows nothing about it, its stored data remains frozen at the state of the last refresh. This behavior is often called staleness, the time gap between the actual state of the base data and the state the materialized view reflects. Depending on the use case, this delay is either completely unproblematic or a disqualifying factor.

PostgreSQL natively supports materialized views via CREATE MATERIALIZED VIEW. Oracle and SQL Server offer similar concepts under the names materialized view and indexed view respectively, though SQL Server's indexed view is technically a special form that is kept in sync automatically rather than manually. MySQL still has no native materialized view to this day, the concept is typically emulated there via a regular table that is periodically repopulated through an event scheduler or trigger.

-- PostgreSQL: materialized view stores the result physically on disk
CREATE MATERIALIZED VIEW active_customer_summary_mv AS
SELECT
  c.id,
  c.name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;

-- Reading from a materialized view is as fast as reading a plain table
SELECT * FROM active_customer_summary_mv WHERE lifetime_value > 1000;

-- MySQL has no native materialized view: emulate it with a real table
CREATE TABLE active_customer_summary_table (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  order_count INT,
  lifetime_value DECIMAL(12,2)
);
-- Populated on a schedule via an EVENT or an application job

3. Performance differences for read access

The performance difference between a view and a materialized view becomes more pronounced the more complex and expensive the underlying query is. For a simple view that merely filters or renames some columns, the overhead compared to a direct table query is negligible, because the query optimizer can usually embed and optimize the view definition into the outer query without issue. For a view with multiple JOINs, sub-aggregations or window functions over large data volumes, on the other hand, every single access can take several seconds or longer, because the entire computation happens from scratch on every call.

A materialized view shifts this computational cost entirely from read time to refresh time. The expensive JOIN or aggregation is computed once during refresh, every subsequent read simply reads the already finished rows. For dashboards, reports or analytics applications that run the same complex query hundreds of times per hour, this difference can mean the factor between seconds and milliseconds per request.

4. Freshness vs. staleness: the core tradeoff

The choice between view and materialized view is, at its core, a decision between consistency and speed. A regular view guarantees that every query exactly reflects the current state of the base tables, because it is freshly computed on every call. This property is indispensable for use cases where stale data could lead to wrong decisions, such as a current account balance, inventory right before checkout, or a real-time price calculation.

A materialized view deliberately accepts a certain degree of staleness in exchange for speed. For many analytics and reporting use cases, a data state that is a few minutes or even hours old is entirely sufficient, because users do not expect second-accurate freshness anyway. The decisive point in the decision, therefore, is not which technique is fundamentally better, but which maximum data lag the specific use case can tolerate without decisions being made on stale numbers.

5. Refresh strategies for materialized views

The refresh mechanism is the central operational aspect of every materialized view. PostgreSQL offers two variants: REFRESH MATERIALIZED VIEW, which locks the entire view and blocks reads during recalculation, and REFRESH MATERIALIZED VIEW CONCURRENTLY, which continues to allow reads against the old data in parallel and only switches atomically once complete. The latter requires a unique index on the materialized view, but is the practical choice for production systems where a blocking refresh is not acceptable.

Besides a full refresh, which recomputes all the data, some database systems offer incremental refresh mechanisms that update only the rows that actually changed. These mechanisms are more complex to implement and not universally available, but for large materialized views with frequent, small changes they drastically reduce refresh time compared to a complete recalculation. The refresh frequency itself is usually controlled via a periodic job, a scheduler, or a trigger on relevant base table changes.

When choosing a refresh strategy, it is important to weigh how much load the refresh itself places on the system. A constant refresh on every tiny change to the base table starts to approach the behavior of a regular view in practice and loses the speed advantage, while a refresh that is too infrequent lets staleness grow beyond an acceptable amount.

-- PostgreSQL: blocking refresh (locks the materialized view during rebuild)
REFRESH MATERIALIZED VIEW active_customer_summary_mv;

-- PostgreSQL: non-blocking refresh, requires a unique index
CREATE UNIQUE INDEX idx_acs_mv_id ON active_customer_summary_mv (id);
REFRESH MATERIALIZED VIEW CONCURRENTLY active_customer_summary_mv;

-- Scheduled refresh via a periodic job (e.g. cron, pg_cron, or an EVENT in MySQL)
-- pg_cron example: refresh every 15 minutes
-- SELECT cron.schedule('*/15 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY active_customer_summary_mv');

-- MySQL: rebuild the emulated materialized view table inside an EVENT
CREATE EVENT refresh_active_customer_summary
ON SCHEDULE EVERY 15 MINUTE
DO
  REPLACE INTO active_customer_summary_table
  SELECT c.id, c.name, COUNT(o.id), SUM(o.total)
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  WHERE c.status = 'active'
  GROUP BY c.id, c.name;

6. Storage consumption and overhead

A regular view occupies virtually no storage beyond the query definition itself, a few hundred bytes in the system catalog. A materialized view, on the other hand, occupies exactly as much physical storage as a regular table with the same number of rows and columns, plus storage for any indexes on it. For a materialized view over a large aggregation, this amount of storage can nonetheless be significantly smaller than that of the base tables, because aggregations typically reduce the row count substantially.

This storage consumption is not a side effect, it is the actual tradeoff: you exchange storage space and refresh compute time for read speed. For systems with a tight storage budget or many different materialized views, this storage requirement adds up, and it is worth regularly checking whether a materialized view is actually still being used before it needlessly ties up storage and refresh cycles.

7. Indexing views and materialized views

On a regular view, most database systems do not allow creating dedicated indexes, because there is no physical data an index could reference. Instead, a view benefits exclusively from indexes on the underlying base tables, which the optimizer takes into account when embedding the view definition into the outer query. An exception is SQL Server's indexed view, which as a special form is actually physically materialized and indexed, but kept automatically rather than manually in sync with the base tables.

A materialized view, on the other hand, is a standalone physical structure and can be given its own indexes like any table. In practice, an index on the most frequently filtered columns of a materialized view is just as effective as on a regular table and can further speed up reads, especially when only a small slice of the materialized rows is needed per query.

-- A plain view has no indexes of its own, only the base tables can be indexed
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_customers_status ON customers (status);
-- The optimizer uses these base-table indexes when the view is expanded

-- SQL Server: indexed view, physically materialized and index-backed
CREATE VIEW dbo.active_customer_summary WITH SCHEMABINDING AS
SELECT
  c.id,
  c.name,
  COUNT_BIG(*) AS order_count,
  SUM(o.total) AS lifetime_value
FROM dbo.customers c
JOIN dbo.orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;

CREATE UNIQUE CLUSTERED INDEX idx_acs_id ON dbo.active_customer_summary (id);

-- PostgreSQL: index directly on a materialized view, like a regular table
CREATE INDEX idx_acs_mv_lifetime_value
  ON active_customer_summary_mv (lifetime_value);

8. When each view type is the right choice

A regular view is the right choice whenever absolute data freshness is indispensable, the underlying query stays moderately complex, or the view primarily serves abstraction and access control rather than performance optimization. Views are excellent for hiding complex business logic behind a stable interface, without application code needing to know the underlying table structures, and without creating the additional operational overhead of a refresh strategy.

A materialized view pays off as soon as an expensive query is executed repeatedly with the same or similar parameters and a certain data lag is tolerable. Classic use cases are dashboards with aggregated metrics, daily or hourly reports, search indexes over denormalized data, or API endpoints with high read volume where the underlying base data changes comparatively rarely. Where both apply, many systems combine both approaches: a materialized view for the expensive base aggregation, with a regular view on top for light, current filtering.

-- Combining both: a materialized view for the expensive base aggregation,
-- a plain view on top for light, always-current filtering

CREATE MATERIALIZED VIEW customer_revenue_base_mv AS
SELECT
  c.id AS customer_id,
  c.region,
  SUM(o.total) AS total_revenue,
  COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.region;

-- Cheap, always up to date filtering on top of the materialized base
CREATE VIEW top_customers_this_view AS
SELECT customer_id, region, total_revenue
FROM customer_revenue_base_mv
WHERE total_revenue > 5000
ORDER BY total_revenue DESC;

9. View and materialized view in direct comparison

The following table summarizes the central differences and serves as a quick decision aid for choosing between a regular view and a materialized view in a concrete use case.

In practice, the decision is rarely final. A query that started as a regular view and grows increasingly complex and slow over time is a clear signal to consider converting it into a materialized view with a suitable refresh strategy, as soon as the measured data lag is acceptable for the use case.

10. Summary

The difference between view and materialized view can be reduced to a single question: is the query recomputed on every access, or is its result stored and periodically refreshed? A regular view guarantees freshness without storage cost, but pays for it with full execution time on every access. A materialized view delivers consistently fast reads, but buys that with storage space, refresh effort and a certain, controllable data lag.

The right choice does not depend on which technique is fundamentally superior, but on how expensive the underlying query is, how often it runs, and how much staleness the respective use case tolerates. Anyone who explicitly answers these three questions for every complex query makes the choice between view and materialized view based on concrete requirements, not habit.

11. FAQ: View vs. Materialized View