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.

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:
-- 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:
-- 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;-- Oracle
CREATE MATERIALIZED VIEW daily_revenue
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT ...;-- 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
| Property | View | Materialized view |
|---|---|---|
| Stores rows | No | Yes, on disk |
| Read cost | Full query each time | Scan of stored result |
| Staleness | Never stale | Stale until refreshed |
| Write cost | None | Refresh or synchronous index maintenance |
| Indexes on the result | Not possible | Yes (PostgreSQL, Oracle, SQL Server indexed view) |
| Can be updated directly | Sometimes, if simply updatable | No |
| Predicate pushdown | Yes, optimizer rewrites into the outer query | No; the filter applies after the stored scan |
| Refreshed on transaction commit | n/a | Oracle ON COMMIT, SQL Server indexed view |
| Blocks readers during refresh | No | REFRESH yes; REFRESH ... CONCURRENTLY no |
Included in pg_dump data | n/a | No — restored WITH NO DATA |
| Engine | Materialized view | Concurrent refresh | Notes |
|---|---|---|---|
| PostgreSQL | CREATE MATERIALIZED VIEW | CONCURRENTLY, needs a unique index | Cannot run inside a transaction block |
| Oracle | CREATE MATERIALIZED VIEW | REFRESH FAST with materialized view logs | ON COMMIT or ON DEMAND |
| SQL Server | Indexed view (SCHEMABINDING + unique clustered index) | Always current | Needs NOEXPAND on Standard edition |
| MySQL | None | — | Summary table + EVENT |
| Snowflake | CREATE MATERIALIZED VIEW | Automatic, serverless | Enterprise edition and above |
| BigQuery | CREATE MATERIALIZED VIEW | Automatic, best effort | Must 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:
-- 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 populationIt 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:
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.
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:
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 symptom | Cause | Fix |
|---|---|---|
cannot refresh materialized view ... concurrently | No unique index on the view | Create a unique index with no WHERE clause |
REFRESH ... CONCURRENTLY cannot run inside a transaction block | Statement issued in an explicit transaction (or a framework that wraps everything) | Run it on an autocommit connection |
| Readers block for minutes during refresh | Plain REFRESH holds ACCESS EXCLUSIVE | Use CONCURRENTLY, or refresh a shadow copy and swap |
| Materialized view empty after restore | pg_dump does not dump the data | Refresh after restore |
cannot change name of view column | CREATE OR REPLACE cannot rename or reorder | ALTER VIEW ... RENAME COLUMN, or drop and recreate |
ERROR 1349: View's SELECT contains a subquery in the FROM clause | MySQL cannot merge that shape | Use the default TEMPTABLE algorithm and accept the materialisation, or rewrite |
| View returns rows the caller should not see | Runs as owner, bypassing RLS | security_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 SCHEMABINDING | Recreate the view schema-bound, then create the index |
| Indexed view unused despite existing | Standard edition ignores it without a hint | Query with WITH (NOEXPAND) |
duplicate key value violates unique constraint during concurrent refresh | View output is not actually unique | Add 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.