View vs Materialized View: Differences, Performance, and When to Use Each

What Is the Difference Between a View and a Materialized View?

A view is a saved SQL query that stores no data and runs against the base tables every time it is read, so results are always current but pay full query cost. A materialized view stores the query’s result on disk, so reads are fast and indexable, but the data is only as fresh as the last refresh.

View. A view is a named SELECT. When you query it, the database substitutes the view’s definition into your query and plans the whole thing against the underlying tables. The PostgreSQL docs put it plainly: “The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.” Nothing is stored except the definition.

Materialized view. A materialized view is a named SELECT whose output the database runs once and writes to disk as rows. Queries against it read those stored rows directly. Because the rows are physical, you can index them. Because they are a copy, they drift from the base tables the moment those tables change, until something refreshes them.

Put another way: a view moves the cost of the query to read time. A materialized view moves it to refresh time. For the full lifecycle of the materialized side, see what is a materialized view.

Views vs Materialized Views: Comparison Table

The rest of this post is about the rows in that table that bite in production: read cost against refresh cost, and staleness.

What Each One Stores

A view stores text, in effect. PostgreSQL keeps the parsed query in the system catalog, and at query time the planner inlines it. That has two useful consequences. First, a filter you apply to the view can be pushed down into the underlying tables, so SELECT * FROM paid_orders WHERE customer_id = 42 can use an index on orders.customer_id. Second, if the base data changes, the next read sees it. There is no copy to go stale.

A materialized view stores a heap of rows, just like a table. PostgreSQL lists it in pg_matviews, and you can give it a tablespace, storage parameters and indexes. What it does not store is any record of when it was last refreshed. pg_matviews.ispopulated tells you whether it has data at all, not how old that data is. If staleness matters to you, you have to track it yourself (more on that under common mistakes).

Read Cost vs Refresh Cost

This is the actual trade you are making.

With a view, every read pays the query. If the view joins four tables and aggregates ten million rows, every SELECT against it does that work. For a cheap query, a filtered projection of one table, say, that cost is trivial and a view is the right tool. For an expensive aggregation read by a dashboard that 200 people load each morning, you run the same heavy query 200 times.

With a materialized view, each refresh pays the query once, and reads are cheap. The 200 dashboard loads become 200 index lookups on a small pre-aggregated table. The cost moved to refresh time, which you control.

The catch is that PostgreSQL’s refresh is always a full recompute. REFRESH MATERIALIZED VIEW re-runs the entire defining query against every base row, even if one row changed. The CONCURRENTLY option does not change that; it changes how the new result is swapped in, not how much is recomputed. So refresh cost grows with the size of the base data, not the size of the change. The Postgres materialized views guide walks through that limit in detail.

A useful rule of thumb: a materialized view pays off when the read-to-change ratio is high and the query is expensive. If the data changes as often as it is read, you are recomputing for nothing.

The Staleness Trade-Off

A view is never stale. A materialized view is stale by design, for up to one refresh interval plus however long the refresh itself takes.

If you refresh every 15 minutes and the refresh takes 3 minutes, a reader can see data up to roughly 18 minutes old. Whether that matters depends entirely on who reads it:

Reporting and dashboards. A daily revenue chart that is 15 minutes behind is fine. This is where materialized views earn their keep.

User-facing counts that are “close enough.” Follower counts, product review averages, leaderboard pages. Minutes of lag is usually acceptable.

Decisions made at request time. A fraud check, a credit limit, an eligibility gate, an agent choosing an action. If the decision reads a derived value (a velocity count, an exposure total) from a materialized view, it acts on the state as of the last refresh, not the state now. That is stale data by construction, and refreshing faster only narrows the gap until refresh cost catches up with you.

PostgreSQL Examples: CREATE VIEW vs CREATE MATERIALIZED VIEW

Everything below runs on PostgreSQL 14 through 18. Start with a base table:

CREATE TABLE orders (
    order_id    bigint PRIMARY KEY,
    customer_id bigint        NOT NULL,
    amount      numeric(12,2) NOT NULL,
    status      text          NOT NULL,
    created_at  timestamptz   NOT NULL DEFAULT now()
);

CREATE INDEX ON orders (customer_id);

INSERT INTO orders (order_id, customer_id, amount, status)
SELECT g, (g % 1000) + 1, round((random() * 200)::numeric, 2),
       CASE WHEN g % 10 = 0 THEN 'refunded' ELSE 'paid' END
FROM generate_series(1, 1000000) AS g;

A view. This one filters and projects a single table:

CREATE VIEW paid_orders AS
SELECT order_id, customer_id, amount, created_at
FROM orders
WHERE status = 'paid';

Because it reads one table with no aggregates, DISTINCT, GROUP BY or set operations, PostgreSQL treats it as automatically updatable: an UPDATE paid_orders SET amount = 0 WHERE order_id = 1 is rewritten into an update on orders. Add WITH CHECK OPTION if you want to stop writes through the view from producing rows the view can’t see.

A materialized view. This one aggregates the same data per customer:

CREATE MATERIALIZED VIEW customer_revenue AS
SELECT customer_id,
       count(*)        AS paid_orders,
       sum(amount)     AS revenue,
       max(created_at) AS last_order_at
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
WITH DATA;

WITH DATA is the default and populates it immediately. Now compare the plans:

EXPLAIN ANALYZE
SELECT customer_id, sum(amount) FROM paid_orders
WHERE customer_id = 42 GROUP BY customer_id;

EXPLAIN ANALYZE
SELECT revenue FROM customer_revenue WHERE customer_id = 42;

The first plan touches orders every time. The second reads from a 1,000-row table (and, once indexed, reads a single row).

See the staleness for yourself. Insert a new paid order and query both:

INSERT INTO orders (order_id, customer_id, amount, status)
VALUES (1000001, 42, 500.00, 'paid');

SELECT sum(amount) FROM paid_orders WHERE customer_id = 42;   -- includes the 500.00
SELECT revenue FROM customer_revenue WHERE customer_id = 42;  -- does not, yet

The view reflects the insert immediately. The materialized view does not until you refresh it.

Refresh, and refresh without blocking readers. A plain refresh swaps in the new result but, as the PostgreSQL docs note, “could block other connections which are trying to read from the materialized view”:

REFRESH MATERIALIZED VIEW customer_revenue;

To keep reads flowing during refresh, use CONCURRENTLY. It requires at least one unique index on the materialized view that “uses only column names and includes all rows”, meaning no expression index and no WHERE clause:

CREATE UNIQUE INDEX customer_revenue_customer_id_uidx
    ON customer_revenue (customer_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_revenue;

Three rules from the docs worth knowing before you schedule this. CONCURRENTLY only works on a view that is already populated, so it can’t be combined with WITH NO DATA. Only one REFRESH can run against a given materialized view at a time. And a concurrent refresh does more total work than a plain one, because it computes the full result and then diffs it against the existing rows; you are buying availability, not speed.

The Postgres materialized views guide covers scheduling refreshes with pg_cron, indexing strategy and tablespaces in depth.

Materialized View vs Table

A materialized view is stored like a table, so the obvious question is why not just use a table.

Who writes the rows. A materialized view’s rows come from exactly one place: its defining query, re-run on refresh. You cannot INSERT, UPDATE or DELETE into it. A table’s rows come from whatever code you write, which means you can maintain a summary table incrementally with INSERT ... ON CONFLICT DO UPDATE or a trigger, applying only the change instead of recomputing everything.

Constraints and structure. ALTER MATERIALIZED VIEW supports a subset of ALTER TABLE that excludes constraints. You can index a materialized view, but you can’t declare a primary key or foreign key on it. A table gets the full set.

Where the logic lives. A materialized view keeps the derivation declarative: the SQL is the definition, and REFRESH is the whole maintenance story. A hand-maintained summary table spreads the derivation across triggers or application code that must stay correct through every schema change.

The practical split: use a materialized view when a full recompute on a schedule is acceptable. Use a table with incremental maintenance when the base data is large, changes constantly, and the derived value must stay close to current. Past a handful of such tables, hand-written maintenance gets hard to keep correct, which is the problem incremental materialized views exist to solve.

How Oracle, SQL Server, Snowflake, BigQuery, Redshift and Databricks Differ

PostgreSQL has the simplest implementation: full refresh, on demand, no automatic maintenance. Other engines narrow the staleness gap in different ways, each with restrictions.

Oracle: fast refresh and materialized view logs. Oracle supports COMPLETE refresh (recompute everything), FAST refresh (apply only the changes) and FORCE (try fast, fall back to complete). Fast refresh requires materialized view logs on the base tables, which record changed rows for the refresh to consume. Refresh can run ON DEMAND or ON COMMIT, where the view is updated as part of each committing transaction at the cost of longer commits. Oracle 12.2 added real-time materialized views (ENABLE ON QUERY COMPUTATION), which combine a stale view with logged changes at query time. Oracle can also rewrite queries against base tables to use a materialized view automatically (ENABLE QUERY REWRITE). This is why “materialized view vs view oracle” is a common interview question: Oracle’s materialized views carry far more machinery than PostgreSQL’s.

SQL Server: indexed views. SQL Server doesn’t have a MATERIALIZED VIEW statement. You create a view WITH SCHEMABINDING and then build a unique clustered index on it, which stores the result. From then on, the engine maintains it during every INSERT, UPDATE and DELETE on the base tables, so it stays current, but DML performance can “degrade significantly” when a table feeds many or complex indexed views. The restrictions are long: no outer joins, no subqueries, no AVG, MIN or MAX, and a GROUP BY view must include COUNT_BIG(*). On Standard edition you need the NOEXPAND hint for the optimizer to read the indexed view directly.

Snowflake: background-maintained, single table. Snowflake materialized views require Enterprise Edition and can query only a single table, so no joins. A background service keeps them up to date, and if maintenance is behind, queries combine the stored result with newer base-table data, so reads are current. You pay compute credits for that maintenance. For multi-table derivations Snowflake points you to dynamic tables, which trade freshness for flexibility with a target lag; see Snowflake dynamic tables vs materialized views.

BigQuery: automatic refresh, restricted SQL. BigQuery materialized views refresh automatically in the background. By default BigQuery attempts a refresh within 5 minutes of a base-table change, no more often than every 30 minutes, on a best-effort basis. Incremental materialized views merge cached results with base-table changes at query time, but only support a limited set of aggregates and join shapes. Setting allow_non_incremental_definition lifts the SQL limits at the cost of full recomputation and a max_staleness window.

Amazon Redshift: incremental where it can, auto refresh on request. A Redshift materialized view is refreshed with REFRESH MATERIALIZED VIEW, and Redshift picks the method: an incremental refresh that applies only base-table changes when the query uses supported constructs (joins, GROUP BY, SUM, COUNT, AVG, MIN, MAX), and a full recompute when it uses outer joins, set operations like UNION, window functions, subqueries or DISTINCT aggregates. AUTO REFRESH YES (off by default) refreshes the view after base tables change, but Redshift schedules it around your workload and “might stop autorefresh to preserve the performance of user workload,” so staleness is not guaranteed. Definitions can’t reference standard views, temporary tables or user-defined functions, or use ORDER BY or LIMIT, and auto refresh is unavailable on a materialized view built on another materialized view. Redshift can also rewrite queries against the base tables to use an up-to-date materialized view.

Databricks: a managed table refreshed by a pipeline. A Databricks materialized view is a Unity Catalog managed table that stores the query result, backed by a serverless pipeline that Databricks creates for it. Refresh is manual, on a cron SCHEDULE, or TRIGGER ON UPDATE when upstream data changes. Each refresh is incremental when Databricks can merge only the changed data and judges it cheaper, and a full recompute otherwise; incremental refresh needs row tracking on the source Delta tables. A standard Databricks view, like a view anywhere, computes its result on every query.

MySQL Server has views but no materialized views (only Oracle’s managed MySQL HeatWave adds them, as of 9.5); teams emulate them with summary tables and scheduled events or triggers.

View vs materialized view vs dynamic table. Snowflake adds a third option, and its own comparison lays out the split: a view stores nothing and computes on every query; a materialized view stores a single-table result that Snowflake keeps current in the background; a dynamic table stores the result of an arbitrary query, joins included, and refreshes on a target lag you set, so its freshness depends on that lag. Use a dynamic table when the transformation is too complex for a materialized view and a lag of a minute or more is acceptable. Snowflake dynamic tables vs materialized views covers the choice in depth.

The pattern across all of them: the more automatic the freshness, the narrower the SQL you are allowed to write, or the more you pay on the write path.

When to Use a View vs a Materialized View

Work through these in order.

1. Is the query cheap? If it filters or projects a small number of rows with good indexes, use a view. Materializing it adds refresh work and staleness for no read benefit.

2. Do readers need current data? If a read must reflect the latest committed writes, use a view (or query the tables directly). A materialized view can’t give you that without a refresh in between.

3. Is the query expensive and read far more often than the data changes? Use a materialized view. Aggregations over large fact tables, multi-join reporting queries, and precomputed rollups behind dashboards are the classic fit.

4. Is the use abstraction or security, not performance? Use a view. Views hide columns, rename things, and give a stable interface over a changing schema. In PostgreSQL 15+, security_invoker views check permissions as the calling user.

**5. Is it expensive and does it need to be fresh?** This is where neither built-in option is enough in PostgreSQL. You need incremental maintenance: an extension such as pg_ivm, a hand-maintained summary table, a stream processor, or a system whose materialized views refresh incrementally.

Common Mistakes

Materializing a cheap query. If the view’s query runs in a few milliseconds with an index, a materialized view just adds a refresh job and a staleness window.

Forgetting the unique index, then discovering it at 2 a.m. REFRESH ... CONCURRENTLY fails without a unique, non-partial, non-expression index. Create it at the same time as the materialized view.

Assuming CONCURRENTLY means incremental. It doesn’t. It still recomputes the full query; it just avoids blocking readers while it applies the result.

Not tracking when the data was refreshed. PostgreSQL doesn’t record a last-refresh time. Log it yourself so readers and alerts can see how old the data is:

CREATE TABLE matview_refresh_log (
    matview      text        NOT NULL,
    refreshed_at timestamptz NOT NULL DEFAULT now()
);

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_revenue;
INSERT INTO matview_refresh_log (matview) VALUES ('customer_revenue');

Refresh intervals shorter than refresh duration. If a refresh takes 4 minutes and the schedule fires every 2, jobs queue up (only one refresh runs per view at a time) and the data is never as fresh as the schedule suggests.

Stacking independently refreshed materialized views. Two views refreshed on different schedules, read together, describe two different moments. A velocity count and a spend total from different refreshes can disagree about the same customer.

Using a view where the planner can’t push filters down. Views with aggregates or window functions often can’t push an outer WHERE into the base scan, so “just add a filter” still runs the whole aggregation. Check with EXPLAIN.

When Full Refresh Can’t Keep Up

Everything above assumes someone can tolerate the refresh interval. For decisions made at request time, like a fraud check reading a velocity count or an agent reading an exposure total, the derived value has to reflect writes that happened moments ago, under many concurrent reads, and multiple derived values have to agree with each other.

Full-recompute refresh can’t get there: refresh cost grows with the data, so the interval can’t shrink far enough. Incremental maintenance changes the trade-off. Instead of re-running the query, each refresh applies only what changed since the last one, so refresh cost tracks the size of the change, not the size of the data, and the interval can shrink to seconds.

That is how derived state works in the Tacnode Context Lake™. You define the derived state as a SQL view (Tacnode presents the PostgreSQL wire protocol), source rows arrive by CDC from the Postgres or MySQL you already run, and the view is refreshed incrementally on a schedule you set in SQL, every few seconds where the decision needs it, without locking the table. Decisions read it at request concurrency, every read runs against one consistent snapshot, and what a decision concludes is written back as context for the next one. Your existing tables stay where they are; the decision reads derived state refreshed every few seconds instead of hourly or nightly. The incremental materialized views post goes deeper on how incremental maintenance works.

Frequently Asked Questions