---
# === IDENTITY ===
id: software/patterns/sql-materialized-views/2026
canonical_question: "How do I use materialized views effectively?"
aliases:
  - "materialized view refresh strategies"
  - "when to use materialized views"
  - "materialized views vs regular views"
  - "postgres materialized view"
  - "SQL Server indexed views"
  - "Oracle materialized view refresh"
  - "materialized view performance optimization"
  - "REFRESH MATERIALIZED VIEW CONCURRENTLY"
entity_type: software_reference
domain: software > patterns > sql_materialized_views
region: global
jurisdiction: global
temporal_scope: 2020-2026

# === VERIFICATION ===
last_verified: 2026-02-24
confidence: 0.88
version: 1.0
first_published: 2026-02-24

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: null
  next_review: 2026-08-23
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "Materialized views store stale data by design — never use for real-time transactional reads where consistency is required"
  - "PostgreSQL REFRESH MATERIALIZED VIEW (non-concurrent) acquires an exclusive lock — blocks all reads during refresh"
  - "REFRESH CONCURRENTLY requires at least one UNIQUE index covering all rows (no WHERE clause, no expression indexes)"
  - "SQL Server indexed views require SCHEMABINDING — underlying table schema cannot be altered while the view exists"
  - "MySQL has no native materialized view support — all workarounds require manual refresh logic via triggers, events, or stored procedures"
  - "Oracle FAST refresh requires materialized view logs on all base tables — logs consume storage and add write overhead"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Need real-time, always-consistent query results with zero staleness tolerance"
    use_instead: "Use regular views with proper indexing on base tables, or application-level caching with cache invalidation"
  - condition: "Query is already fast (<50ms) and runs infrequently (<10 times/day)"
    use_instead: "Regular view or direct query — materialized view adds complexity without meaningful benefit"
  - condition: "Need to write/update data through the view"
    use_instead: "Use updatable views or direct table operations — materialized views are read-only snapshots"

# === AGENT HINTS ===
inputs_needed:
  - key: database_engine
    question: "Which database engine are you using?"
    type: choice
    options: ["PostgreSQL", "MySQL", "SQL Server", "Oracle"]
  - key: data_freshness
    question: "How stale can the data be?"
    type: choice
    options: ["Seconds (near real-time)", "Minutes", "Hours", "Daily refresh is fine"]
  - key: base_table_size
    question: "How large are the base tables?"
    type: choice
    options: ["Small (<100K rows)", "Medium (100K-10M rows)", "Large (10M-1B rows)", "Very large (>1B rows)"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/patterns/sql-materialized-views/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-24)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/patterns/sql-query-optimization/2026"
      label: "SQL Query Optimization"
    - id: "software/patterns/database-indexing-strategies/2026"
      label: "Database Indexing Strategies"
    - id: "software/patterns/caching-patterns/2026"
      label: "Caching Patterns"
  often_confused_with:
    - id: "software/patterns/sql-regular-views/2026"
      label: "SQL Regular Views (non-materialized)"

# === SOURCES (7 authoritative sources) ===
sources:
  - id: src1
    title: "PostgreSQL Documentation: CREATE MATERIALIZED VIEW"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/rules-materializedviews.html
    type: official_docs
    published: 2024-11-21
    reliability: authoritative
  - id: src2
    title: "PostgreSQL Documentation: REFRESH MATERIALIZED VIEW"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
    type: official_docs
    published: 2024-11-21
    reliability: authoritative
  - id: src3
    title: "Refreshing Materialized Views — Oracle Database Data Warehousing Guide"
    author: Oracle Corporation
    url: https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/refreshing-materialized-views.html
    type: official_docs
    published: 2023-01-01
    reliability: authoritative
  - id: src4
    title: "Create Indexed Views — SQL Server Documentation"
    author: Microsoft
    url: https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views
    type: official_docs
    published: 2024-03-15
    reliability: authoritative
  - id: src5
    title: "CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)"
    author: Microsoft
    url: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql
    type: official_docs
    published: 2024-03-15
    reliability: authoritative
  - id: src6
    title: "MySQL 9.0 FAQ: Views"
    author: Oracle Corporation (MySQL)
    url: https://dev.mysql.com/doc/refman/9.0/en/faqs-views.html
    type: official_docs
    published: 2024-07-01
    reliability: authoritative
  - id: src7
    title: "SQL Materialized View: Enhancing Query Performance"
    author: DataCamp
    url: https://www.datacamp.com/tutorial/sql-materialized-view
    type: technical_blog
    published: 2025-01-15
    reliability: moderate_high
---

# Materialized Views: Complete Reference

## TL;DR

- **Bottom line**: Materialized views precompute and store query results as a physical table, trading storage space and data freshness for dramatically faster read performance on expensive queries (often 100-1000x speedup).
- **Key tool/command**: `CREATE MATERIALIZED VIEW mv_name AS SELECT ...;` (PostgreSQL) / `CREATE VIEW ... WITH SCHEMABINDING` + `CREATE UNIQUE CLUSTERED INDEX` (SQL Server)
- **Watch out for**: Forgetting to refresh — stale data is the #1 source of bugs. In PostgreSQL, non-concurrent refresh blocks all reads on the view.
- **Works with**: PostgreSQL 9.3+ (native), Oracle 8i+ (native with FAST refresh), SQL Server 2005+ (indexed views), MySQL (manual workaround only).

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- Materialized views store stale data by design — never use for real-time transactional reads where consistency is required
- PostgreSQL `REFRESH MATERIALIZED VIEW` (non-concurrent) acquires an exclusive lock — blocks all reads during refresh
- `REFRESH CONCURRENTLY` requires at least one `UNIQUE` index covering all rows (no `WHERE` clause, no expression indexes) [src2]
- SQL Server indexed views require `SCHEMABINDING` — underlying table schema cannot be altered while the view exists [src4]
- MySQL has no native materialized view support — all workarounds require manual refresh logic [src6]
- Oracle FAST refresh requires materialized view logs on all base tables — logs consume storage and add write overhead [src3]

## Quick Reference

| Scenario | Pattern | Refresh Cost | Storage Overhead | Trade-off |
|---|---|---|---|---|
| Dashboard aggregations (daily) | Full refresh on schedule | O(base_table) | Low-Medium | Simple; blocks reads briefly |
| Dashboard aggregations (live) | Concurrent refresh + cron | O(delta) comparison | Medium | No read blocking; needs unique index |
| Reporting on large joins | Full refresh nightly | O(join_result) | High | Massive read speedup; stale during day |
| Near-real-time analytics | Oracle ON COMMIT / trigger-based | O(delta) per commit | Medium | Fresh data; slows writes |
| Search/filter on computed cols | Indexed materialized view | O(index_maintenance) | Medium | Fast reads; write amplification |
| Remote/federated data caching | Materialized view on foreign table | O(network_transfer) | Matches result set | Eliminates network latency on reads |
| Time-series rollups (hourly) | Partitioned MV + incremental | O(partition) | High | Refresh only latest partition |
| MySQL workaround | Summary table + triggers/events | O(trigger_logic) | Matches result set | Full manual maintenance |
| High-availability refresh | Oracle out-of-place refresh | O(base_table) | 2x during refresh | Zero downtime; temporary double storage |
| Multi-level aggregations | Nested/stacked MVs | O(each_level) | Multiplied per level | Complex dependency chain |
| Write-heavy OLTP tables | Avoid materialized views | N/A | N/A | Write overhead exceeds read benefit |
| Small tables (<10K rows) | Regular view + indexes | N/A | None | MV adds complexity without benefit |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (29 lines)

```
START: Do you need precomputed query results?
├── Query < 50ms already?
│   ├── YES → Use a regular view or direct query (no MV needed)
│   └── NO ↓
├── Can you tolerate stale data?
# ... (see full script)
```

## Step-by-Step Guide

### 1. Create the materialized view (PostgreSQL)

Define the expensive query you want to precompute. The view is populated immediately on creation. [src1]

```sql
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    date_trunc('month', order_date) AS month,
    product_category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
JOIN order_items USING (order_id)
JOIN products USING (product_id)
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
WITH DATA;
```

**Verify**: `SELECT COUNT(*) FROM mv_monthly_sales;` --> should return row count > 0

### 2. Add indexes for query performance

Materialized views are physical tables — index them like any other table. A unique index is required for `REFRESH CONCURRENTLY`. [src2]

```sql
-- Required for REFRESH CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_monthly_sales_pk
    ON mv_monthly_sales (month, product_category);

-- Additional indexes for common query patterns
CREATE INDEX idx_mv_monthly_sales_category
    ON mv_monthly_sales (product_category);

CREATE INDEX idx_mv_monthly_sales_revenue
    ON mv_monthly_sales (revenue DESC);
```

**Verify**: `\di+ mv_monthly_sales*` --> should list all three indexes

### 3. Set up concurrent refresh

Use `CONCURRENTLY` to avoid blocking reads during refresh. This compares old and new data, applying only deltas. [src2]

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;
```

**Verify**: `SELECT * FROM mv_monthly_sales ORDER BY month DESC LIMIT 5;` --> should show updated data

### 4. Automate refresh with pg_cron

Schedule periodic refresh using pg_cron (or external cron). [src1]

```sql
-- Install pg_cron extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Refresh every hour at minute 0
SELECT cron.schedule(
    'refresh-monthly-sales',
    '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales'
);
```

**Verify**: `SELECT * FROM cron.job WHERE jobname = 'refresh-monthly-sales';` --> should show scheduled job

### 5. Monitor view freshness

Check when the view was last refreshed and whether it needs updating. [src1]

```sql
-- PostgreSQL: check last refresh time (requires pg_stat_user_tables)
SELECT
    schemaname,
    matviewname,
    hasindexes,
    ispopulated
FROM pg_matviews
WHERE matviewname = 'mv_monthly_sales';

-- Check approximate staleness via row modification stats
SELECT
    relname,
    last_vacuum,
    last_analyze,
    n_tup_ins,
    n_tup_upd
FROM pg_stat_user_tables
WHERE relname = 'mv_monthly_sales';
```

**Verify**: `ispopulated` should be `true`; check `last_analyze` for approximate refresh time

### 6. Handle refresh failures gracefully

Wrap refresh in error handling for production pipelines. [src2]

```sql
-- Wrap in a function with error handling
CREATE OR REPLACE FUNCTION refresh_mv_safely(view_name text)
RETURNS void AS $$
BEGIN
    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', view_name);
    RAISE NOTICE 'Successfully refreshed %', view_name;
EXCEPTION WHEN OTHERS THEN
    RAISE WARNING 'Failed to refresh %: %', view_name, SQLERRM;
    -- Fallback: try non-concurrent refresh
    BEGIN
        EXECUTE format('REFRESH MATERIALIZED VIEW %I', view_name);
        RAISE NOTICE 'Fallback refresh succeeded for %', view_name;
    EXCEPTION WHEN OTHERS THEN
        RAISE EXCEPTION 'All refresh attempts failed for %: %', view_name, SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;
```

**Verify**: `SELECT refresh_mv_safely('mv_monthly_sales');` --> should print success notice

## Code Examples

### PostgreSQL: Materialized View with Concurrent Refresh

> Full script: [postgresql-materialized-view-with-concurrent-refre.sql](scripts/postgresql-materialized-view-with-concurrent-refre.sql) (25 lines)

```sql
-- Input:  orders + products tables with millions of rows
-- Output: precomputed category-level daily aggregations
-- 1. Create the materialized view
CREATE MATERIALIZED VIEW mv_daily_category_stats AS
SELECT
# ... (see full script)
```

### Oracle: FAST Refresh with Materialized View Logs

> Full script: [oracle-fast-refresh-with-materialized-view-logs.sql](scripts/oracle-fast-refresh-with-materialized-view-logs.sql) (25 lines)

```sql
-- Input:  sales table with frequent inserts
-- Output: incrementally refreshed aggregation (only processes new rows)
-- 1. Create materialized view log on base table (required for FAST refresh)
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, customer_id, sale_date, amount)
# ... (see full script)
```

### SQL Server: Indexed View (Materialized View Equivalent)

```sql
-- Input:  orders + order_items tables
-- Output: automatically maintained materialized aggregation

-- 1. Create the view WITH SCHEMABINDING (required)
CREATE VIEW dbo.vw_category_revenue
WITH SCHEMABINDING
AS
SELECT
    p.category,
    COUNT_BIG(*) AS order_count,  -- COUNT_BIG required, not COUNT
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM dbo.order_items oi
INNER JOIN dbo.products p ON oi.product_id = p.product_id
GROUP BY p.category;
GO

-- 2. Create unique clustered index to materialize the view
CREATE UNIQUE CLUSTERED INDEX idx_vw_category_revenue
ON dbo.vw_category_revenue (category);
GO

-- 3. Query — Enterprise Edition auto-uses indexed view
--    Standard Edition: use WITH (NOEXPAND) hint
SELECT category, total_revenue
FROM dbo.vw_category_revenue WITH (NOEXPAND)
ORDER BY total_revenue DESC;
```

### MySQL: Manual Materialized View Pattern

> Full script: [mysql-manual-materialized-view-pattern.sql](scripts/mysql-manual-materialized-view-pattern.sql) (35 lines)

```sql
-- Input:  orders table (MySQL has no native materialized views)
-- Output: manually maintained summary table simulating a materialized view
-- 1. Create the summary table
CREATE TABLE mv_daily_sales (
    sale_date DATE NOT NULL,
# ... (see full script)
```

## Anti-Patterns

### Wrong: Refreshing without CONCURRENTLY on a production view

```sql
-- BAD — blocks all SELECT queries during refresh (can take minutes on large tables)
-- Application queries will timeout or queue
REFRESH MATERIALIZED VIEW mv_dashboard_stats;
```

### Correct: Use CONCURRENTLY with a unique index

```sql
-- GOOD — allows concurrent reads during refresh
-- Requires a unique index on the materialized view
CREATE UNIQUE INDEX ON mv_dashboard_stats (report_date, department_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;
```

### Wrong: No indexes on the materialized view

```sql
-- BAD — materialized view is a physical table but queries do sequential scans
CREATE MATERIALIZED VIEW mv_user_activity AS
SELECT user_id, COUNT(*) as actions, MAX(created_at) as last_active
FROM activity_log GROUP BY user_id;

-- This query seq-scans the entire MV
SELECT * FROM mv_user_activity WHERE user_id = 12345;
```

### Correct: Index materialized views like regular tables

```sql
-- GOOD — add indexes matching your query patterns
CREATE MATERIALIZED VIEW mv_user_activity AS
SELECT user_id, COUNT(*) as actions, MAX(created_at) as last_active
FROM activity_log GROUP BY user_id;

CREATE UNIQUE INDEX ON mv_user_activity (user_id);
CREATE INDEX ON mv_user_activity (last_active DESC);

-- Now uses index scan: sub-ms response
SELECT * FROM mv_user_activity WHERE user_id = 12345;
```

### Wrong: Using materialized views for frequently changing data without a refresh strategy

```sql
-- BAD — created the view but never refreshes it
-- Data becomes increasingly stale with no monitoring
CREATE MATERIALIZED VIEW mv_inventory_levels AS
SELECT product_id, SUM(quantity) AS stock
FROM inventory GROUP BY product_id;
-- ... weeks later, stock levels are completely wrong
```

### Correct: Always pair MV creation with an automated refresh schedule

```sql
-- GOOD — materialized view + scheduled refresh + staleness monitoring
CREATE MATERIALIZED VIEW mv_inventory_levels AS
SELECT product_id, SUM(quantity) AS stock
FROM inventory GROUP BY product_id;

CREATE UNIQUE INDEX ON mv_inventory_levels (product_id);

-- Refresh every 15 minutes
SELECT cron.schedule('refresh-inventory', '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_inventory_levels');

-- Monitor: alert if refresh hasn't run in 30 minutes
-- (implement in your monitoring system)
```

### Wrong: Oracle MV without materialized view logs attempting FAST refresh

```sql
-- BAD — FAST refresh fails because no MV log exists; silently falls back to COMPLETE
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FORCE ON DEMAND AS  -- FORCE means: try FAST, fall back to COMPLETE
SELECT product_id, SUM(amount) FROM sales GROUP BY product_id;

-- User thinks they get incremental refresh, but it's doing full refresh every time
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', '?');
```

### Correct: Create MV logs first, then use explicit FAST refresh

```sql
-- GOOD — MV log enables true incremental refresh
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, amount)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FAST ON DEMAND AS
SELECT product_id, SUM(amount) AS total, COUNT(*) AS cnt
FROM sales GROUP BY product_id;

-- Now FAST refresh processes only changed rows
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'F');
```

## Common Pitfalls

- **Stale data served to users**: Materialized view data is a snapshot. If refresh fails silently, applications serve outdated results for days. Fix: Monitor refresh jobs and alert on failure; add `last_refreshed` column or check `pg_stat_user_tables`. [src1]
- **REFRESH CONCURRENTLY fails with no unique index**: PostgreSQL raises `ERROR: cannot refresh materialized view concurrently without at least one unique index` but developers don't read error messages carefully. Fix: `CREATE UNIQUE INDEX ON mv_name (col1, col2);` before using CONCURRENTLY. [src2]
- **SQL Server COUNT vs COUNT_BIG**: Indexed views in SQL Server require `COUNT_BIG(*)` instead of `COUNT(*)`. Using COUNT causes creation to fail. Fix: Always use `COUNT_BIG(*)` in indexed view definitions. [src4]
- **SQL Server SCHEMABINDING locks schema changes**: Adding `WITH SCHEMABINDING` prevents `ALTER TABLE` on any referenced column. Developers discover this when a migration fails in production. Fix: Plan schema changes carefully; drop and recreate indexed view around migrations. [src4]
- **Oracle MV log bloat**: Materialized view logs grow unboundedly if refresh doesn't run. On high-write tables this can fill the tablespace. Fix: Schedule regular MV refreshes and monitor log sizes; set `PURGE IMMEDIATE` or `PURGE START WITH ... NEXT ...`. [src3]
- **PostgreSQL MV eats disk on refresh**: `REFRESH` (non-concurrent) writes an entirely new copy of the data before swapping. For a 10GB MV, you need 20GB free during refresh. Fix: Monitor disk space; schedule refresh during low-usage periods; use `CONCURRENTLY` for delta-based updates. [src2]
- **MySQL trigger-based MV slows writes**: Implementing materialized views via triggers on every INSERT/UPDATE/DELETE adds latency to every write operation. Fix: Use scheduled EVENT-based refresh instead of triggers for write-heavy tables. [src6]
- **Querying MV created WITH NO DATA**: `CREATE MATERIALIZED VIEW ... WITH NO DATA` creates the structure without populating it. Querying it raises `ERROR: materialized view has not been populated`. Fix: Run `REFRESH MATERIALIZED VIEW mv_name;` before first query. [src1]

## Diagnostic Commands

> Full script: [diagnostic-commands.sql](scripts/diagnostic-commands.sql) (29 lines)

```sql
-- PostgreSQL: List all materialized views and their state
SELECT schemaname, matviewname, hasindexes, ispopulated
FROM pg_matviews ORDER BY matviewname;
-- PostgreSQL: Check materialized view size on disk
SELECT pg_size_pretty(pg_total_relation_size('mv_monthly_sales')) AS total_size;
# ... (see full script)
```

## Version History & Compatibility

| Database | Version | Materialized View Support | Key Feature |
|---|---|---|---|
| PostgreSQL 18+ | Current (2025) | Native | `EXPLAIN ANALYZE BUFFERS` default; improved planner for MVs |
| PostgreSQL 9.4+ | LTS/Stable | Native | `REFRESH CONCURRENTLY` (requires unique index) |
| PostgreSQL 9.3 | Original MV release | Native | `CREATE MATERIALIZED VIEW` (no CONCURRENTLY) |
| Oracle 23ai | Current (2024) | Native + FAST + Query Rewrite | Automatic MV management improvements |
| Oracle 12cR2+ | Stable | Native + FAST + PCT | `ON STATEMENT` refresh; out-of-place refresh |
| Oracle 8i+ | Legacy | Native | Basic materialized views with ON COMMIT/DEMAND |
| SQL Server 2022 | Current | Indexed Views | Performance improvements; same SCHEMABINDING requirement |
| SQL Server 2005+ | Stable | Indexed Views | `WITH SCHEMABINDING` + clustered index = materialized |
| Azure Synapse | Current | Native `CREATE MATERIALIZED VIEW` | Different syntax from SQL Server on-prem |
| MySQL 9.0 | Current | No native support | Must use summary tables + triggers/events/procedures |
| MySQL 5.7+ | Stable | No native support | Event Scheduler available for workarounds |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Expensive aggregation queries run frequently (>10x/day) | Base data changes every second and staleness is unacceptable | Regular views with optimized indexes |
| Dashboard/reporting queries with complex JOINs | Query is already fast (<50ms) without materialization | Direct query or application-level caching |
| Read-heavy workloads with predictable query patterns | Write-heavy OLTP where MV maintenance overhead exceeds read savings | Write-through application cache (Redis/Memcached) |
| Foreign/remote data that is expensive to fetch | Small tables where full scan is trivial | Regular views (zero maintenance overhead) |
| Data warehouse / OLAP analytical queries | Schema changes frequently (SQL Server SCHEMABINDING is restrictive) | CTEs or temporary tables per-session |
| Precomputing search indexes or denormalized data | Need to update data through the view | Updatable views or direct table access |
| Reducing load on a hot table during peak hours | Query results vary per user/session (not cacheable) | Per-request computation with connection pooling |

## Important Caveats

- **PostgreSQL has no built-in auto-refresh**: Unlike Oracle's ON COMMIT or SQL Server's auto-maintained indexed views, PostgreSQL materialized views require explicit refresh via `pg_cron`, external scheduler, or application-triggered refresh. There is no trigger-based automatic refresh without custom code.
- **REFRESH CONCURRENTLY is slower than full refresh for large changes**: When most of the data changes, CONCURRENTLY must diff the old and new result sets row-by-row. For full reloads, plain `REFRESH` is faster — only use CONCURRENTLY when a small fraction of data changes per refresh cycle. [src2]
- **SQL Server indexed views auto-update on writes**: Unlike other databases, SQL Server indexed views are automatically maintained when base tables change. This means every INSERT/UPDATE/DELETE on base tables incurs maintenance overhead on the indexed view — which is beneficial for reads but harmful for write-heavy workloads. [src4]
- **Oracle Query Rewrite can silently redirect queries**: When `ENABLE QUERY REWRITE` is set, Oracle may transparently rewrite queries against base tables to use the materialized view. This is usually beneficial but can serve stale data if the MV is not fresh. Monitor `STALENESS` in `USER_MVIEWS`. [src3]
- **Storage cost is real**: A materialized view storing aggregated results of a 100GB table might only be 1MB, but a materialized view that denormalizes joins can be larger than the base tables. Always check `pg_total_relation_size()` or equivalent after creation.

## Related Units

- [SQL Query Optimization](/software/patterns/sql-query-optimization/2026)
- [Database Indexing Strategies](/software/patterns/database-indexing-strategies/2026)
- [Caching Patterns](/software/patterns/caching-patterns/2026)