What Is a Materialized View? How It Works Across Postgres, Oracle, SQL Server, and Snowflake

What Is a Materialized View?

A materialized view is a database object that stores the result of a SQL query as physical data. Unlike a regular view, which re-runs its query every time it is read, a materialized view returns precomputed rows. It must be refreshed, manually, on a schedule, or incrementally, to reflect changes in the underlying tables.

The word “materialized” is the whole definition. A regular view is a named query: the database stores the SQL text and expands it into whatever query reads from it. A materialized view is a named result: the database runs the SQL once, writes the rows to storage, and serves reads from those rows. (British spelling, materialised view, means the same thing; Oracle once called them “snapshots,” and you will still see that word in older docs.)

Here is the same definition as a regular view and as a materialized view in PostgreSQL:

-- A regular view: stores the query, runs it on every read
CREATE VIEW customer_revenue AS
SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;

-- A materialized view: stores the result, reads are a table scan or index lookup
CREATE MATERIALIZED VIEW customer_revenue_mv AS
SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;

CREATE UNIQUE INDEX ON customer_revenue_mv (customer_id);

A SELECT from customer_revenue aggregates the whole orders table every time. A SELECT from customer_revenue_mv reads one indexed row. If orders has 500 million rows, that is the difference between seconds and a millisecond.

The catch is that customer_revenue_mv does not change when orders changes. Insert a new order and the materialized view keeps returning the old total until it is refreshed. That tradeoff, fast reads in exchange for managed staleness, is what every design decision about materialized views comes down to. For a side-by-side of the two object types, see view vs materialized view.

Materialized view meaning, in one line: a cached query result that the database owns, stores like a table, and knows how to rebuild.

How a Materialized View Works

Every materialized view implementation has the same three parts.

1. A defining query. Any SELECT the engine accepts for materialization. Some engines accept nearly any query (PostgreSQL); others restrict it heavily to keep maintenance cheap (Snowflake allows a single table with no joins; SQL Server bans outer joins, MIN/MAX, and subqueries in indexed views).

2. Stored results. The rows are written to storage like a table. You can usually index them, and in most engines you query them with plain SELECT. Some engines also use them transparently: the optimizer rewrites a query against the base tables to read the materialized view instead. Oracle calls this query rewrite; SQL Server Enterprise, Snowflake and BigQuery do the same thing automatically.

3. A refresh mechanism. Something has to bring the stored result back in line with the source tables. This is where engines differ most, and where most production problems come from.

The lifecycle looks like this:

source tables change  →  stored result is now stale  →  refresh runs  →  stored result matches source (as of refresh start)

Two numbers describe any materialized view in production: staleness (how far behind the source the stored result can be when a reader looks) and refresh cost (the compute and locking it takes to catch up). Every refresh strategy trades one against the other.

Materialized View Refresh Strategies

There are five ways engines keep a materialized view current. Most engines support two or three of them.

Full refresh (complete rebuild). Re-run the defining query from scratch and replace the stored result. Simple, and it supports any query, but the cost scales with the size of the source data, not the size of the change. PostgreSQL’s REFRESH MATERIALIZED VIEW and Oracle’s REFRESH COMPLETE work this way. A 10-row change to a billion-row table costs a billion-row query.

Incremental refresh (fast refresh). Apply only the changes since the last refresh. If three orders were inserted, update three customers’ totals. The engine needs a record of what changed (Oracle uses materialized view logs; Snowflake, BigQuery and Databricks read change metadata from their own storage) and a way to compute the delta for each operator in the query. Sums and counts are easy. MIN/MAX under deletes, outer joins, and window functions are hard, which is why every incremental engine restricts the SQL it will maintain. Oracle calls this REFRESH FAST. The incremental materialized views guide goes deep on how delta computation works.

On commit (synchronous). Maintain the view inside the same transaction that changed the source. Readers never see a stale result, and writers pay for it: every insert, update or delete now also updates every dependent view. Oracle’s ON COMMIT and ON STATEMENT modes, SQL Server indexed views, and the pg_ivm extension for PostgreSQL all work this way. Under heavy concurrent writes to the same aggregate rows, this becomes lock contention on the view.

Scheduled. Run a full or incremental refresh on a timer. Hourly, nightly, every five minutes. Staleness is bounded by the interval plus the refresh duration. In PostgreSQL this usually means an external scheduler or pg_cron; Databricks has a SCHEDULE clause built in; Oracle uses START WITH ... NEXT or DBMS_SCHEDULER.

Automatic background maintenance. The engine watches for changes and refreshes on its own, asynchronously, without you scheduling anything. Snowflake, BigQuery, Databricks (with TRIGGER ON UPDATE), and streaming systems like Materialize and RisingWave work this way. Staleness depends on how quickly the background process keeps up, which depends on write volume and how much compute it has.

How Each Database Implements Materialized Views

This is where most “what is a materialized view” explanations stop being useful: the concept is portable, but the behavior is not. Here is what you actually get in each engine.

PostgreSQL

PostgreSQL has had CREATE MATERIALIZED VIEW since 9.3 and REFRESH ... CONCURRENTLY since 9.4. Every refresh is a full rebuild. Per the PostgreSQL 18 docs, refresh “completely replaces the contents of a materialized view” and “the old contents are discarded.” There is no incremental refresh and no automatic refresh.

CREATE MATERIALIZED VIEW daily_order_totals AS
SELECT date_trunc('day', created_at) AS order_day,
       customer_id,
       COUNT(*)    AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY 1, 2
WITH DATA;

-- CONCURRENTLY needs a unique index on plain columns, no WHERE clause
CREATE UNIQUE INDEX ON daily_order_totals (order_day, customer_id);

-- Rebuild without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_totals;

A plain REFRESH takes an ACCESS EXCLUSIVE lock and blocks reads for the duration. CONCURRENTLY lets reads continue by computing the new result and diffing it against the old one, which is slower and uses more resources, and it only works on a view that is already populated. Scheduling is external, usually cron or pg_cron. For incremental, trigger-based maintenance there is the pg_ivm extension, which updates an “incrementally maintainable materialized view” in the same transaction as the base-table change, with restrictions (no window functions, HAVING, UNION, or aggregates over outer joins). The full walkthrough, with indexing and refresh scheduling, is in Postgres materialized views: create, refresh, and optimize.

Oracle

Oracle has the most complete implementation, and the longest history. Refresh methods are COMPLETE (truncate and re-run), FAST (apply changes recorded in materialized view logs), and FORCE (fast if possible, otherwise complete; the default). Refresh timing is ON DEMAND, ON COMMIT, or, since 12c Release 2, ON STATEMENT. Oracle 12.2 also added real-time materialized views: with ENABLE ON QUERY COMPUTATION, a query against a stale view applies the pending log changes on the fly for that statement, without persisting them (ORACLE-BASE).

-- Fast refresh needs a log on the source table
CREATE MATERIALIZED VIEW LOG ON orders
  WITH ROWID, SEQUENCE (customer_id, amount)
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW customer_revenue_mv
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  ENABLE QUERY REWRITE
AS
SELECT customer_id,
       SUM(amount)   AS revenue,
       COUNT(amount) AS amount_count,
       COUNT(*)      AS order_count
FROM orders
GROUP BY customer_id;

The extra COUNT(amount) and COUNT(*) columns are not decoration: Oracle requires them to fast-refresh a SUM through deletes. ENABLE QUERY REWRITE lets the optimizer answer queries against orders from the materialized view without the application naming it. The fast-refresh rules (which aggregates, which joins, which log options) are long; DBMS_MVIEW.EXPLAIN_MVIEW tells you why a view is not fast-refreshable.

SQL Server (indexed views)

SQL Server has no CREATE MATERIALIZED VIEW statement in its core engine. The equivalent is an indexed view: a view created WITH SCHEMABINDING with a unique clustered index on it. The clustered index stores the result, and SQL Server maintains it synchronously as part of every INSERT, UPDATE and DELETE on the base tables.

CREATE VIEW dbo.customer_revenue
WITH SCHEMABINDING
AS
SELECT customer_id,
       SUM(ISNULL(amount, 0)) AS revenue,
       COUNT_BIG(*)           AS order_count
FROM dbo.orders
GROUP BY customer_id;
GO

CREATE UNIQUE CLUSTERED INDEX ix_customer_revenue
  ON dbo.customer_revenue (customer_id);

The restrictions are strict: COUNT_BIG(*) is required with GROUP BY; AVG, MIN, MAX, HAVING, outer joins, self-joins, subqueries, CTEs, DISTINCT and window functions are not allowed; SUM over a nullable column must be wrapped in ISNULL. Microsoft warns that DML on tables referenced by many or complex indexed views “can degrade significantly.” Automatic use of indexed views by the optimizer happens in Enterprise edition and Azure SQL; on Standard edition you must query the view with the NOEXPAND hint. (Azure Synapse dedicated SQL pools do support CREATE MATERIALIZED VIEW, with their own rules.)

MySQL

MySQL Server has no materialized views, in 8.4 LTS or in the 9.x innovation releases; since 9.5 the MATERIALIZED keyword works only on Oracle’s managed MySQL HeatWave. CREATE VIEW exists, but every view is expanded or computed at query time. Teams build the equivalent with a summary table plus triggers, a scheduled INSERT ... ON DUPLICATE KEY UPDATE through the Event Scheduler, or an external system fed by the binlog. The patterns and their failure modes are in MySQL materialized views. MariaDB is in the same position.

Snowflake

Snowflake materialized views require Enterprise Edition and are maintained automatically by a background service: no refresh command, no schedule. If maintenance falls behind, Snowflake reads the up-to-date parts from the view and the out-of-date micro-partitions from the base table, so query results stay correct. The optimizer can also rewrite base-table queries to use the view.

CREATE MATERIALIZED VIEW customer_revenue_mv AS
SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

The limits are the price of that automation. A Snowflake materialized view can query only a single table; joins, including self-joins, are not supported; no window functions, HAVING, ORDER BY, LIMIT or UDFs; and only a fixed list of aggregates. Background maintenance consumes credits billed per second, so a view over a frequently changing table has a running cost even if nobody reads it. For joins and multi-step pipelines Snowflake points you to dynamic tables, which take a TARGET_LAG (minimum 60 seconds) and a warehouse, and refresh incrementally or fully. The tradeoffs are covered in Snowflake dynamic tables vs materialized views.

Google BigQuery

BigQuery materialized views are also automatic. BigQuery “attempts to start a refresh within 5 minutes of a change in the base table (if the previous refresh was done earlier than 30 minutes ago)”; refresh_interval_minutes changes that cap, and enable_refresh = false turns it off (managing materialized views). Incremental materialized views combine the stored result with changes read from the base table at query time, so queries return current results when the changes do not invalidate the view. Smart tuning reroutes base-table queries to the view.

CREATE MATERIALIZED VIEW mydataset.customer_revenue_mv
OPTIONS (enable_refresh = true, refresh_interval_minutes = 30)
AS
SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS order_count
FROM mydataset.orders
GROUP BY customer_id;

-- Manual refresh when you need it
CALL BQ.REFRESH_MATERIALIZED_VIEW('myproject.mydataset.customer_revenue_mv');

Non-incremental materialized views accept broader SQL but recompute fully and can be read with max_staleness (30 minutes to 3 days) to trade freshness for cost.

Amazon Redshift

Redshift materialized views accept joins and aggregates across multiple tables, and Redshift chooses the refresh method from the query. An incremental refresh applies only the base-table changes since the last refresh, for queries built from SELECT, FROM, inner joins, WHERE, GROUP BY, HAVING and aggregates such as SUM, COUNT, AVG, MIN and MAX. Outer joins, UNION and other set operations, window functions, subqueries and DISTINCT aggregates force a full recompute. Refresh is manual by default; AUTO REFRESH YES lets Redshift refresh after base tables change, but it schedules around your workload and “might stop autorefresh to preserve the performance of user workload.” Redshift can also rewrite queries against the base tables to use an up-to-date materialized view.

CREATE MATERIALIZED VIEW customer_revenue_mv
  AUTO REFRESH YES
AS
SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- Manual refresh; CASCADE also refreshes materialized views it depends on
REFRESH MATERIALIZED VIEW customer_revenue_mv;

Per the CREATE MATERIALIZED VIEW reference, a definition can’t reference standard views, temporary tables or user-defined functions, or use ORDER BY, LIMIT or OFFSET, and AUTO REFRESH YES isn’t allowed on a view that uses mutable functions or external schemas or is built on another materialized view.

Databricks

Databricks materialized views live in Unity Catalog and are refreshed by serverless pipelines. On serverless compute, the default refresh attempts an incremental refresh, recomputing only results affected by source changes, and falls back to full recompute when it cannot. Refresh can be manual, scheduled, or triggered by upstream changes (CREATE MATERIALIZED VIEW):

CREATE MATERIALIZED VIEW customer_revenue_mv
  SCHEDULE EVERY 1 HOUR
AS
SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- Or refresh when sources change, at most once a minute:
-- TRIGGER ON UPDATE AT MOST EVERY INTERVAL 1 MINUTE

REFRESH MATERIALIZED VIEW customer_revenue_mv;

ClickHouse

ClickHouse uses the same keyword for something different. A standard ClickHouse materialized view is an insert trigger: when a block of rows is inserted into the source table, the view’s SELECT runs over that block only and writes the output to a target table. There is no refresh; the view is maintained per insert. It does not see updates or deletes to the source, and it only fires on inserts to the left-most table in its query.

CREATE TABLE customer_revenue
(
    customer_id UInt64,
    revenue     Decimal(18, 2),
    order_count UInt64
)
ENGINE = SummingMergeTree
ORDER BY customer_id;

CREATE MATERIALIZED VIEW customer_revenue_mv TO customer_revenue AS
SELECT customer_id, sum(amount) AS revenue, count() AS order_count
FROM orders
GROUP BY customer_id;

Because SummingMergeTree merges partial sums in the background, reads should still aggregate: SELECT customer_id, sum(revenue) FROM customer_revenue GROUP BY customer_id. ClickHouse also has refreshable materialized views (REFRESH EVERY 1 MINUTE) that re-run the full query on a schedule, for complex joins the incremental form cannot handle.

Streaming systems: Materialize, RisingWave, Flink

Streaming systems treat incremental view maintenance as the core product rather than a feature. In Materialize and RisingWave, CREATE MATERIALIZED VIEW defines a view that is maintained incrementally as change events arrive, typically from Kafka or database CDC, with support for joins and aggregations that batch engines restrict. Apache Flink’s materialized tables take a FRESHNESS interval and run either a continuous streaming job (freshness tied to the checkpoint interval) or scheduled batch refreshes. The cost is a separate system to run: the view lives outside your operational database, and its results are only as current as the pipeline feeding it.

Materialized Views Compared Across Engines

Materialized View Use Cases

Materialized views earn their keep when the same expensive query is read many more times than its inputs change.

Dashboards and reporting. Daily revenue, weekly active users, cohort retention. The classic use: hundreds of dashboard loads a day read one precomputed table instead of each scanning the fact table. Hourly or nightly refresh is usually fine.

Pre-joined, denormalized read models. An order-with-customer-and-product view that the application reads constantly. The join runs at refresh time, not on every page load.

Aggregates for APIs. Leaderboards, per-account totals, counts shown in a product UI. The read needs to be a single indexed lookup; the aggregate would be too slow to compute per request.

Data warehouse acceleration. In Snowflake, BigQuery and Oracle, materialized views plus automatic query rewrite speed up BI queries without changing the SQL the BI tool sends.

Distributing a subset of data. Oracle’s original use for snapshots: replicate a filtered or aggregated slice of a central database to a remote site.

Feature computation for models and rules. Per-customer velocity counts, rolling sums, risk signals. This is where freshness starts to matter, because the reader is making a decision, not drawing a chart.

Limitations of Materialized Views

Staleness. Outside synchronous engines, a materialized view is behind its source by some amount. That is fine for a dashboard and a real problem for anything that acts on the answer: a fraud check reading a velocity count that is ten minutes old will approve the eleventh transaction it would have blocked.

Refresh cost scales with the wrong thing. A full refresh costs as much as the data, not the change. As source tables grow, refresh times grow, and eventually the refresh interval you want is shorter than the refresh itself.

Write amplification for synchronous views. On-commit and indexed views move the cost into the write path. Every write to the source pays for every dependent view, and hot aggregate rows (a popular product’s total, a large merchant’s count) become lock contention points.

SQL restrictions. The more automatic the maintenance, the narrower the SQL: no joins in Snowflake, no MIN/MAX or outer joins in SQL Server, per-block semantics in ClickHouse, long fast-refresh rules in Oracle. Teams often end up restructuring a query to fit the engine rather than the question.

Storage and compute cost. The result is stored in full, often with indexes. In consumption-priced warehouses, background maintenance bills continuously whether the view is read or not.

Locking and availability during refresh. A plain PostgreSQL REFRESH blocks readers; CONCURRENTLY avoids that but runs longer and needs a unique index. An Oracle complete refresh either deletes and reinserts every row in one transaction (atomic, the default) or truncates and reloads, leaving the view empty mid-refresh.

Chains of views compound lag. When view B reads view A, B’s staleness is A’s staleness plus its own. Multi-layer derived pipelines accumulate delay at every hop.

Staleness, Freshness and Cost: Choosing a Refresh Strategy

The practical question is not “should I use a materialized view” but “how stale can a reader afford to be, and who pays for freshness?”

Stale by hours is fine (reporting, BI, finance close): full refresh on a schedule. PostgreSQL plus pg_cron, Oracle COMPLETE via DBMS_SCHEDULER, Databricks SCHEDULE EVERY 1 DAY. Cheapest to reason about.

Stale by minutes is fine (operational dashboards, near-real-time analytics): incremental or automatic background refresh. Oracle FAST on a short schedule, Snowflake, BigQuery, Databricks TRIGGER ON UPDATE, ClickHouse insert-triggered views.

Stale by seconds or less, low write volume: synchronous maintenance. Oracle ON COMMIT, SQL Server indexed views, pg_ivm. Correct and current, as long as write throughput and contention on the aggregated rows stay modest.

Stale by seconds or less, high write volume, many readers: this is where the classic implementations run out. Synchronous maintenance puts the cost in the write path; full refreshes cannot run that often, and background services refresh on their own timetable; streaming engines deliver fresh results but in a separate system, so a request that reads the view and the source rows reads two systems.

Materialized Views in Tacnode

That last case is the one Tacnode is built for: decisions that read derived state (a velocity count, a running exposure, an aggregate over the last hour) alongside the raw rows, many times per second, and need both to be fresh.

The Tacnode Context Lake™ holds the decision context: source data arriving by CDC or streams from the systems you already run, derived state computed from it, and the decisions written back. Its incremental materialized views refresh on a schedule you set in SQL, and each refresh applies only what changed since the last one, so it is cheap enough to run every few seconds where the decision needs it, without locking the table. The views are served at the concurrency of the request path and live in the same system as the source rows they derive from, so a decision reads the aggregate and the rows it summarizes in one place instead of joining a separate pipeline’s output to a read from another database. Tacnode presents the PostgreSQL wire protocol, so the definition is familiar SQL:

CREATE MATERIALIZED VIEW account_velocity
WITH (refresh_mode = 'incremental') AS
SELECT account_id,
       COUNT(*)    AS txn_count,
       SUM(amount) AS txn_total
FROM transactions
GROUP BY account_id;

Most reads in Tacnode run on demand against the latest committed data; incremental views are for the expensive aggregates worth keeping precomputed. Syntax and supported query shapes are in the Tacnode materialized view docs.

Frequently Asked Questions