Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL Views vs Materialized Views: Syntax, Refresh Strategies, and Performance

Why a view is inlined into your query while a materialized view is a cached table, how CONCURRENTLY refreshes work, and the RLS trap hidden in views.

~6 min readSQL
#SQL#Views#Performance
3D illustration of a virtual dynamic query view compared with disk-cached materialized view data blocks and a background refresh engine

Quick Summary

A view is a saved query, not a table. When you select from it, the database substitutes the view definition into your statement and plans the whole thing as if you had typed the join yourself. Nothing is stored, nothing goes stale, and the optimizer can push your WHERE clause down into it. A materialized view is the opposite trade: the query runs once, the result is written to disk, and every later read is a table scan over that stored copy — fast, but only as fresh as the last refresh.

That single difference decides almost every design question. Use a view for encapsulation, column naming, and permission boundaries where freshness matters. Use a materialized view when a query is expensive, runs often, and tolerates being minutes or hours behind — dashboards, reporting aggregates, search indexes, denormalised feeds.

The pitfalls cluster on the materialised side: a refresh that locks out readers, one that cannot run concurrently because you forgot the unique index, a restored database whose materialized views come back empty, and a view that bypasses row-level security because it runs as its owner.

Basic syntax and query signatures

Standard views:

sql
-- PostgreSQL / MySQL / SQL Server / SQLite CREATE VIEW active_customers AS SELECT id, email, created_at FROM customers WHERE deleted_at IS NULL; CREATE OR REPLACE VIEW active_customers AS -- PostgreSQL: replace, keep grants SELECT id, email, created_at, plan FROM customers WHERE deleted_at IS NULL; DROP VIEW IF EXISTS active_customers;

Materialized views, where the engine has them:

sql
-- PostgreSQL CREATE MATERIALIZED VIEW daily_revenue AS SELECT date_trunc('day', paid_at) AS day, sum(amount_cents) AS revenue, count(*) AS orders FROM payments WHERE status = 'captured' GROUP BY 1; CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day); -- enables CONCURRENTLY REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
sql
-- Oracle CREATE MATERIALIZED VIEW daily_revenue REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT ...;
sql
-- SQL Server: an indexed view, not a materialized one CREATE VIEW dbo.daily_revenue WITH SCHEMABINDING AS SELECT CAST(paid_at AS date) AS day, SUM(amount_cents) AS revenue, COUNT_BIG(*) AS orders FROM dbo.payments WHERE status = 'captured' GROUP BY CAST(paid_at AS date); CREATE UNIQUE CLUSTERED INDEX IX_daily_revenue ON dbo.daily_revenue (day);

The dialects diverge sharply. PostgreSQL, Oracle, Redshift, Snowflake, BigQuery and Databricks have real materialized views with a refresh command. MySQL has none — the workaround is a summary table plus the event scheduler or an application job. SQL Server's indexed view is a different mechanism: the index is maintained synchronously by every DML statement, so it is always fresh and always costs write throughput.

Key comparison table

PropertyViewMaterialized view
Stores rowsNoYes, on disk
Read costFull query each timeScan of stored result
StalenessNever staleStale until refreshed
Write costNoneRefresh or synchronous index maintenance
Indexes on the resultNot possibleYes (PostgreSQL, Oracle, SQL Server indexed view)
Can be updated directlySometimes, if simply updatableNo
Predicate pushdownYes, optimizer rewrites into the outer queryNo; the filter applies after the stored scan
Refreshed on transaction commitn/aOracle ON COMMIT, SQL Server indexed view
Blocks readers during refreshNoREFRESH yes; REFRESH ... CONCURRENTLY no
Included in pg_dump datan/aNo — restored WITH NO DATA
EngineMaterialized viewConcurrent refreshNotes
PostgreSQLCREATE MATERIALIZED VIEWCONCURRENTLY, needs a unique indexCannot run inside a transaction block
OracleCREATE MATERIALIZED VIEWREFRESH FAST with materialized view logsON COMMIT or ON DEMAND
SQL ServerIndexed view (SCHEMABINDING + unique clustered index)Always currentNeeds NOEXPAND on Standard edition
MySQLNone—Summary table + EVENT
SnowflakeCREATE MATERIALIZED VIEWAutomatic, serverlessEnterprise edition and above
BigQueryCREATE MATERIALIZED VIEWAutomatic, best effortMust be a simple aggregate over one table

Practical edge cases and deep dives

1. A view does not guarantee predicate pushdown in every dialect

In PostgreSQL the view is inlined as a subquery and the planner flattens it, so a date filter against a view lands on the base table's index. That is not universal. MySQL has two explicit algorithms — MERGE folds the view into the outer query, TEMPTABLE materialises it first — and a view containing UNION, DISTINCT, GROUP BY, or a subquery in FROM cannot be merged:

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

With ALGORITHM=MERGE that statement fails outright; with the default the server silently switches to a temporary table, and your carefully indexed filter now runs against a full materialisation of the view. Check with EXPLAIN and look for <temporary> in the output. When a view must stay mergeable, keep it to a single-table projection with no aggregation.

2. Refreshing a materialized view that readers depend on

The default REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock. Every query against the view blocks for the duration of the refresh — and a refresh that rebuilds two million rows takes minutes. CONCURRENTLY builds a second copy and diffs it against the live one, so readers keep working, but it requires three things that are easy to miss:

sql
-- 1. a unique index on the view, otherwise: -- ERROR: cannot refresh materialized view "daily_revenue" concurrently -- HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view. CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day); -- 2. it cannot run inside a transaction block -- ERROR: REFRESH MATERIALIZED VIEW CONCURRENTLY cannot run inside a transaction block REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue; -- 3. the view must already have data; the first load is always non-concurrent REFRESH MATERIALIZED VIEW daily_revenue; -- initial population

It is also slower than a plain refresh because it compares old and new rows, and it fails with duplicate key value violates unique constraint when the underlying query is not actually unique — for example a GROUP BY that truncates to the day while the source is still writing. If a refresh can outlast the interval between refreshes, jobs stack up; guard them with pg_try_advisory_lock.

3. Views and row-level security

Before PostgreSQL 15, a view executed with the privileges of its owner, not the caller. A view owned by a superuser therefore returned every row of an RLS-protected table, even to a limited role that had been granted only SELECT on the view — a privilege escalation hiding inside a convenience object. PostgreSQL 15 added the option, and 17 makes it a creation-time choice:

sql
CREATE VIEW my_orders WITH (security_invoker = true) AS SELECT * FROM orders WHERE tenant_id = current_setting('app.tenant_id')::uuid; -- existing view ALTER VIEW my_orders SET (security_invoker = true);

The same distinction exists in SQL Server (EXECUTE AS CALLER versus the default EXECUTE AS OWNER) and in Oracle (definer's rights versus invoker's rights views). Audit any view that crosses a tenant or role boundary: if the view sits in the public schema and its owner is privileged, assume it bypasses RLS until you verify otherwise.

4. Altering a view in place

CREATE OR REPLACE VIEW preserves grants, dependent objects, and the OID, which is why it beats drop-and-create. Its limits are easy to forget: columns may be added only at the end, and an existing column's name, order, and type are fixed.

code
ERROR: cannot change name of view column "email" to "email_address" HINT: Use ALTER VIEW ... RENAME COLUMN ... instead.

ALTER VIEW active_customers RENAME COLUMN email TO email_address; changes the name without touching dependents. A different type or order means drop and recreate — then restore grants and dependent views, because DROP VIEW ... CASCADE takes them down too.

5. Restores, replicas, and empty materialized views

pg_dump writes a materialized view's definition with WITH NO DATA and does not copy its rows. Restore a production database into staging and every materialized view is present but empty — dashboards return zeros, and nothing raises an error. The same happens on a logical replica unless the refresh job is replicated too. Make the restore runbook end with a refresh of every materialized view:

sql
DO $$ DECLARE r record; BEGIN FOR r IN SELECT schemaname, matviewname FROM pg_matviews LOOP EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', r.schemaname, r.matviewname); END LOOP; END $$;

On the read side, a materialized view over a partitioned table does not track new partitions by itself; the refresh re-reads the whole definition, correct but increasingly expensive. For an append-only source, a summary table that upserts the last N days beats a full rebuild at any real volume.

Common mistakes and runtime errors

Error or symptomCauseFix
cannot refresh materialized view ... concurrentlyNo unique index on the viewCreate a unique index with no WHERE clause
REFRESH ... CONCURRENTLY cannot run inside a transaction blockStatement issued in an explicit transaction (or a framework that wraps everything)Run it on an autocommit connection
Readers block for minutes during refreshPlain REFRESH holds ACCESS EXCLUSIVEUse CONCURRENTLY, or refresh a shadow copy and swap
Materialized view empty after restorepg_dump does not dump the dataRefresh after restore
cannot change name of view columnCREATE OR REPLACE cannot rename or reorderALTER VIEW ... RENAME COLUMN, or drop and recreate
ERROR 1349: View's SELECT contains a subquery in the FROM clauseMySQL cannot merge that shapeUse the default TEMPTABLE algorithm and accept the materialisation, or rewrite
View returns rows the caller should not seeRuns as owner, bypassing RLSsecurity_invoker = true (PG 15+), or move the view behind a function with checks
Cannot create index on view ... because it is not schema-bound (SQL Server)Missing WITH SCHEMABINDINGRecreate the view schema-bound, then create the index
Indexed view unused despite existingStandard edition ignores it without a hintQuery with WITH (NOEXPAND)
duplicate key value violates unique constraint during concurrent refreshView output is not actually uniqueAdd the missing grouping key to make rows distinct

State the structural decision plainly: a materialized view is a cache, and every cache needs an owner, an invalidation rule, and a way to detect staleness. If you cannot say how stale the data may be, or monitor the refresh, use a view and fix the query — or build a summary table with a watermark column, which gives incremental refresh, backfill, and an audit trail a materialized view never will.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.