---
title: "Materialized Views Tutorial"
---

# Materialized Views Tutorial

**Target Audience:** Developers and Database Administrators

---

## Table of Contents

1. [Introduction](#introduction)
2. [Creating Materialized Views](#creating-materialized-views)
3. [Querying Materialized Views](#querying-materialized-views)
4. [Manual Refresh](#manual-refresh)
5. [Automatic Refresh (Planned)](#automatic-refresh-planned)
6. [Incremental Refresh](#incremental-refresh)
7. [Monitoring](#monitoring)
8. [Best Practices](#best-practices)
9. [Real-World Examples](#real-world-examples)

---

## Introduction

### What are Materialized Views?

Materialized views are database objects that contain the results of a query. Unlike regular views, which are virtual and execute their query every time they're accessed, materialized views physically store the query results on disk. This makes them significantly faster for complex queries involving aggregations, joins, or large table scans.

**Key Benefits:**
- **Performance:** Query results are pre-computed and stored, eliminating expensive calculations
- **Consistency:** Data snapshots remain stable until explicitly refreshed
- **Resource Efficiency:** Reduces CPU and I/O load for frequently-executed queries
- **Dashboard Optimization:** Enables sub-100ms dashboard queries on embedded devices

### Views vs Materialized Views

| Feature | Regular View | Materialized View |
|---------|-------------|-------------------|
| **Storage** | No physical storage (query alias) | Physical storage of results |
| **Query Speed** | Executes full query on every access | Instant read from stored results |
| **Data Freshness** | Always current (real-time) | Refreshed on-demand or scheduled |
| **Resource Usage** | High CPU/IO on each query | Low CPU/IO after refresh |
| **Best For** | Simple queries, real-time data | Complex aggregations, dashboards |

**Example Comparison:**

```sql
-- Regular View (executes full query every time)
CREATE VIEW active_users AS
SELECT COUNT(*) as total
FROM users
WHERE last_login > strftime('%s', 'now', '-30 days');

-- Materialized View (stores result, refreshed periodically)
CREATE MATERIALIZED VIEW active_users AS
SELECT COUNT(*) as total
FROM users
WHERE last_login > strftime('%s', 'now', '-30 days');
```

**Performance Impact:**
- Regular View: 5-15 seconds (scans entire users table)
- Materialized View: <100ms (reads single stored row)

### Use Cases

#### 1. Dashboards and Reporting
Pre-compute KPIs and metrics for instant dashboard loading:
- Sales totals by region
- Daily active user counts
- Revenue summaries by product category

#### 2. Analytics Queries
Cache results of complex aggregations:
- Time-series rollups (hourly, daily, monthly)
- Multi-table joins with grouping
- Statistical calculations (averages, percentiles)

#### 3. API Response Caching
Accelerate API endpoints with predictable queries:
- "Top 10 products by sales"
- "Customer activity summary"
- "System health metrics"

#### 4. Edge Computing
Enable real-time analytics on resource-constrained devices:
- IoT sensor data aggregation (Raspberry Pi)
- Embedded analytics in microservices
- Offline-first applications

---

## Creating Materialized Views

### Basic Syntax

```sql
CREATE MATERIALIZED VIEW view_name AS
SELECT ... FROM ... WHERE ... GROUP BY ...;
```

**Example: Simple Aggregation**

```sql
-- Create base table
CREATE TABLE sales (
    product_id INT,
    amount REAL,
    sale_date TEXT
);

-- Insert sample data
INSERT INTO sales VALUES (1, 100.50, '2025-01-15');
INSERT INTO sales VALUES (2, 250.00, '2025-01-15');
INSERT INTO sales VALUES (1, 150.75, '2025-01-16');

-- Create materialized view
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    COUNT(*) as total_sales,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_sale_amount
FROM sales;

-- Query the materialized view (instant)
SELECT * FROM sales_summary;
```

**Output:**
```
total_sales | total_revenue | avg_sale_amount
------------|---------------|----------------
3           | 501.25        | 167.08
```

### IF NOT EXISTS Clause

Prevents errors when creating views that might already exist:

```sql
-- Safe creation (no error if view exists)
CREATE MATERIALIZED VIEW IF NOT EXISTS product_stats AS
SELECT
    product_id,
    COUNT(*) as order_count,
    SUM(amount) as total_sales
FROM sales
GROUP BY product_id;
```

### WITH Options

`CREATE MATERIALIZED VIEW` accepts these options and stores them with the view. Automatic
background refresh is planned for the Lite server, so the refresh-related options take
effect once it is available; today, refresh views with `REFRESH MATERIALIZED VIEW`:

```sql
CREATE MATERIALIZED VIEW view_name AS
SELECT ... FROM ...
WITH (
    auto_refresh = true,
    threshold_table_size = '1GB',
    threshold_dml_rate = 100,
    max_cpu_percent = 15,
    lazy_update = true,
    lazy_catchup_window = '1 hour',
    distribution = 'hash(user_id)',
    replication_factor = 3
);
```

**Option Reference:**

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `auto_refresh` | boolean | false | Mark the view for automatic background refresh (planned) |
| `threshold_table_size` | string | - | Table size threshold for triggering refresh |
| `threshold_dml_rate` | integer | - | DML operations/sec threshold |
| `max_cpu_percent` | float | 100.0 | CPU limit for background refresh (planned) |
| `lazy_update` | boolean | false | Defer refresh until query time |
| `lazy_catchup_window` | string | - | Time window for lazy updates |
| `distribution` | string | - | Distribution strategy for sharded deployments |
| `replication_factor` | integer | 1 | Number of replicas |

### Examples with Aggregations

#### COUNT and SUM
```sql
CREATE MATERIALIZED VIEW order_statistics AS
SELECT
    status,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue
FROM orders
GROUP BY status;
```

#### AVG, MIN, MAX
```sql
CREATE MATERIALIZED VIEW product_price_stats AS
SELECT
    category,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category;
```

#### Complex Aggregations with Filtering
```sql
CREATE MATERIALIZED VIEW recent_customer_activity AS
SELECT
    customer_id,
    COUNT(*) as total_orders,
    SUM(amount) as total_spent,
    AVG(amount) as avg_order_value,
    MAX(order_date) as last_order_date
FROM orders
WHERE order_date > strftime('%s', 'now', '-90 days')
GROUP BY customer_id
HAVING COUNT(*) >= 2;
```

#### Time-Series Rollups
```sql
CREATE MATERIALIZED VIEW hourly_metrics AS
SELECT
    (timestamp / 3600) * 3600 AS hour_bucket,
    sensor_id,
    AVG(temperature) as avg_temp,
    MAX(temperature) as max_temp,
    MIN(temperature) as min_temp,
    COUNT(*) as sample_count
FROM sensor_readings
WHERE timestamp > strftime('%s', 'now', '-24 hours')
GROUP BY hour_bucket, sensor_id;
```

---

## Querying Materialized Views

### SELECT from Materialized Views

Materialized views are queried exactly like regular tables:

```sql
-- Simple SELECT
SELECT * FROM sales_summary;

-- With WHERE clause
SELECT * FROM product_stats WHERE order_count > 10;

-- With ORDER BY
SELECT * FROM customer_activity
ORDER BY total_spent DESC
LIMIT 10;
```

**Performance Characteristics:**
- **Query Latency:** <100ms for most queries (compared to seconds for base table scans)
- **Throughput:** 50,000+ queries/second
- **Memory Overhead:** 20-50MB for cached results

### Filtering and Ordering

Materialized views support all standard SQL clauses:

```sql
-- Filtering
SELECT product_id, total_sales
FROM product_stats
WHERE total_sales > 1000;

-- Ordering and limiting
SELECT * FROM customer_rankings
ORDER BY total_spent DESC
LIMIT 25;

-- Aggregations on materialized views
SELECT
    AVG(avg_order_value) as overall_avg,
    SUM(total_spent) as grand_total
FROM customer_activity;
```

### Joins with Materialized Views

Combine materialized views with tables or other views:

```sql
-- Join MV with base table
SELECT
    c.customer_name,
    ca.total_orders,
    ca.total_spent
FROM customers c
JOIN customer_activity ca ON c.customer_id = ca.customer_id
WHERE ca.total_spent > 5000;

-- Join multiple MVs
SELECT
    ps.product_id,
    ps.total_sales,
    pr.avg_rating
FROM product_stats ps
JOIN product_reviews pr ON ps.product_id = pr.product_id
WHERE ps.total_sales > 100 AND pr.avg_rating > 4.0;
```

### Query Optimization Tips

1. **Add indexes on frequently-filtered columns:**
```sql
-- Note: Indexes are created on the internal __mv_{view_name} table
CREATE INDEX idx_product_stats_sales ON __mv_product_stats(total_sales DESC);
```

2. **Use projection to minimize data transfer:**
```sql
-- Only select needed columns
SELECT product_id, total_sales
FROM product_stats
WHERE category = 'Electronics';
```

3. **Combine filters efficiently:**
```sql
-- Indexed filters first
SELECT * FROM customer_activity
WHERE customer_id = 12345 AND total_orders > 5;
```

---

## Manual Refresh

### REFRESH MATERIALIZED VIEW

Update the materialized view with current data from base tables:

```sql
-- Basic refresh
REFRESH MATERIALIZED VIEW sales_summary;

-- Refresh after data changes
INSERT INTO sales VALUES (4, 300.00, '2025-01-17');
INSERT INTO sales VALUES (5, 450.50, '2025-01-17');
REFRESH MATERIALIZED VIEW sales_summary;
```

**Refresh Process:**
1. Re-executes the original query on base tables
2. Replaces stored results with new data
3. Updates metadata (last_refresh timestamp, row count)

**Performance:**
- **Full Refresh:** 10-60 seconds for 100K-1M base rows
- **CPU Usage:** 80-100% during refresh (blocks application threads)
- **Memory:** 100-200MB temporary storage

### REFRESH CONCURRENTLY

Zero-downtime refresh using atomic table swap:

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
```

**How It Works:**
1. Creates temporary table with unique timestamp suffix
2. Populates temporary table with refreshed data
3. Atomically renames old table to backup, temp to current
4. Drops backup table

**Benefits:**
- Queries continue reading old data during refresh
- No query blocking or failures
- Atomic cutover ensures consistency

**Use Cases:**
- Production dashboards requiring 24/7 availability
- High-traffic materialized views
- Large refresh operations (minutes to complete)

### When to Use Each

| Scenario | Use REFRESH | Use REFRESH CONCURRENTLY |
|----------|-------------|--------------------------|
| Development/Testing | Yes | No (unnecessary overhead) |
| Small MVs (<1000 rows) | Yes | No (fast enough) |
| Short refresh time (<1s) | Yes | No (overhead not worth it) |
| Production dashboards | No | Yes (zero downtime) |
| Large MVs (>100K rows) | No | Yes (long refresh time) |
| High query load | No | Yes (avoid blocking) |

### Refresh Timing Strategies

**Immediate Refresh (On-Demand):**
```sql
-- After bulk data load
COPY sales FROM '/data/sales_batch.csv';
REFRESH MATERIALIZED VIEW sales_summary;
```

**Scheduled Refresh (Externally Triggered):**
```bash
# crontab: refresh every hour through the PostgreSQL port
0 * * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary"
```

**Conditional Refresh (Staleness-Based):**
```sql
-- Check staleness before refreshing
SELECT staleness_sec
FROM pg_mv_staleness()
WHERE view_name = 'sales_summary';

-- Refresh only if stale (>1 hour)
-- (Logic implemented in application layer)
```

---

## Automatic Refresh (Planned)

Automatic background refresh is planned for the HeliosDB Lite server. The server already
accepts the refresh options and stores them with the view:

```sql
-- At creation
CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT DATE(created_at) AS day, COUNT(*) AS orders
FROM orders
GROUP BY DATE(created_at)
WITH (auto_refresh = true, max_cpu_percent = 30);

-- After creation
ALTER MATERIALIZED VIEW dashboard_metrics SET (
    staleness_threshold = 300,
    priority = 2,
    refresh_strategy = 'incremental'
);
```

It does not run a refresh worker yet, so these settings do not refresh the view on their
own. Until the worker is available, refresh from a scheduler and use `pg_mv_staleness()`
to decide what to refresh:

```bash
# crontab: incremental refresh every 5 minutes through the PostgreSQL port
*/5 * * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW dashboard_metrics INCREMENTALLY"
```

```sql
-- Views with unrefreshed changes, most stale first
SELECT view_name, pending_changes, staleness_sec
FROM pg_mv_staleness()
WHERE pending_changes > 0
ORDER BY staleness_sec DESC;
```

The [Auto-Refresh Integration Guide](/docs/lite/features/materialized_views/auto_refresh_integration/)
lists the accepted options and the planned design.

---

## Incremental Refresh

### How It Works

Incremental refresh applies only the changes (deltas) since the last refresh, avoiding full query re-execution:

**Traditional Full Refresh:**
```
1. Execute entire query on base tables (scan all 1M rows)
2. Replace all MV data (write 50K result rows)
3. Time: 45 seconds, CPU: 95%
```

**Incremental Refresh:**
```
1. Identify changed rows since last refresh (5K deltas)
2. Re-compute only affected aggregates
3. Merge deltas into existing MV data
4. Time: 1.2 seconds, CPU: 25%
```

**Performance Improvement:**
- **10-100x faster** for typical workloads (1-10% change rate)
- **90% less CPU usage**
- **Minimal memory overhead** (delta tracking)

### Delta Tracking

HeliosDB-Lite automatically tracks changes to base tables:

```sql
-- Enable delta tracking (automatic for all base tables)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount REAL,
    order_date INT
);

-- Create materialized view (delta tracking enabled)
CREATE MATERIALIZED VIEW order_stats AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
GROUP BY customer_id;

-- Insert new rows (tracked as deltas)
INSERT INTO orders VALUES (1, 100, 50.0, 1735689600);
INSERT INTO orders VALUES (2, 101, 75.0, 1735689600);

-- Update existing rows (tracked as deltas)
UPDATE orders SET amount = 60.0 WHERE order_id = 1;

-- Delete rows (tracked as deltas)
DELETE FROM orders WHERE order_id = 2;

-- Incremental refresh applies only deltas
REFRESH MATERIALIZED VIEW order_stats;
```

**Delta Storage:**
- Deltas stored in internal `__delta_{table_name}` tables
- Cleared after successful incremental refresh
- Automatically managed (no user intervention required)

### Performance Benefits

Indicative figures, measured on representative fixtures; reproduce on your own hardware.

**Benchmark Comparison** (100K base rows, 1K changes):

| Refresh Type | Duration | CPU Usage | Memory |
|--------------|----------|-----------|--------|
| Full Refresh | 45s | 95% | 200MB |
| Incremental Refresh | 1.2s | 25% | 50MB |
| **Improvement** | **37x faster** | **3.8x less** | **4x less** |

**When Incremental Refresh Excels:**
- **Low Change Rate:** 0.1-10% of base table rows modified since last refresh
- **Large Base Tables:** 100K+ rows (where full scan is expensive)
- **Complex Aggregations:** Multiple GROUP BY, JOIN operations
- **Frequent Refreshes:** Hourly or more frequent (small delta batches)

**When to Force Full Refresh:**
- **High Change Rate:** a large share of the base table modified (delta overhead exceeds full scan)
- **Schema Changes:** Base table structure modified
- **Correctness Verification:** Periodic full refresh to ensure consistency

### Forcing Full Refresh

Override incremental refresh and perform full recomputation:

```sql
-- Force full refresh (ignores deltas)
REFRESH MATERIALIZED VIEW order_stats;  -- Currently always full

-- Check if incremental is due for full refresh
SELECT
    view_name,
    delta_count_since_full,
    row_count
FROM pg_mv_staleness()
WHERE view_name = 'order_stats';

-- The system automatically forces a full refresh when accumulated deltas grow large relative to the view size
```

---

## Monitoring

### pg_mv_staleness View

HeliosDB-Lite provides a built-in system view for monitoring materialized view freshness:

```sql
SELECT * FROM pg_mv_staleness();
```

**Schema:**
```
view_name        TEXT    -- Materialized view name
last_update      INT     -- Last refresh timestamp (Unix epoch)
pending_changes  INT     -- Number of base table changes since last refresh
staleness_sec    INT     -- Seconds since last refresh
status           TEXT    -- Refresh status (fresh, stale, refreshing)
```

**Example Output:**
```
view_name          | last_update | pending_changes | staleness_sec | status
-------------------|-------------|-----------------|---------------|--------
sales_summary      | 1735689600  | 42             | 127           | stale
customer_activity  | 1735689700  | 5              | 27            | fresh
hourly_metrics     | 1735689650  | 0              | 77            | fresh
```

### Refresh History

Track refresh operations and performance over time:

```sql
-- Check staleness for specific view
SELECT
    view_name,
    staleness_sec,
    pending_changes,
    status
FROM pg_mv_staleness()
WHERE view_name = 'sales_summary';

-- Find stale views (>10 minutes old)
SELECT
    view_name,
    staleness_sec / 60 as staleness_minutes
FROM pg_mv_staleness()
WHERE staleness_sec > 600
ORDER BY staleness_sec DESC;

-- Monitor all views sorted by staleness
SELECT * FROM pg_mv_staleness() ORDER BY staleness_sec DESC;
```

### Performance Metrics

**Query Latency Monitoring:**
```sql
\timing on
SELECT * FROM sales_summary;
-- Expected: <100ms for most queries
```

**Refresh Duration Tracking:**
```sql
\timing on
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
-- Full refresh: 10-60s
-- Incremental: 0.1-1s
```

**CPU and Memory Usage:**
```bash
# Monitor during refresh
top -p $(pgrep heliosdb-lite)

# Or use system metrics API
SELECT
    used_memory_mb,
    cpu_percent
FROM pg_system_metrics();
```

### Alerting Strategies

**Staleness Alerts:**
```sql
-- Alert if critical views are stale (>5 minutes)
SELECT
    view_name,
    staleness_sec
FROM pg_mv_staleness()
WHERE view_name IN ('revenue_dashboard', 'customer_kpis')
  AND staleness_sec > 300;
```

**Refresh Failure Detection:**
```sql
-- Check for views that haven't refreshed recently
SELECT
    view_name,
    staleness_sec / 3600 as hours_stale
FROM pg_mv_staleness()
WHERE staleness_sec > 7200  -- 2 hours
  AND status != 'refreshing';
```

**Integration with Monitoring Tools:**

**Prometheus/Grafana:**
```python
# Expose view staleness as Prometheus gauges (prometheus_client + psycopg2)
import psycopg2
from prometheus_client import Gauge, start_http_server

staleness = Gauge("heliosdb_mv_staleness_seconds", "Materialized view staleness", ["view"])
conn = psycopg2.connect(host="127.0.0.1", port=5432, user="postgres", dbname="heliosdb")

def collect():
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM pg_mv_staleness()")
        for row in cur.fetchall():
            staleness.labels(view=row[0]).set(row[3])
```

**PagerDuty/Opsgenie Alerts:**
```python
# Python monitoring script (psycopg2 over the PostgreSQL port)
import time
import psycopg2

db = psycopg2.connect(host="127.0.0.1", port=5432, user="postgres", dbname="heliosdb")

def check_mv_health(db):
    with db.cursor() as cur:
        cur.execute("SELECT * FROM pg_mv_staleness()")
        rows = cur.fetchall()

    for row in rows:
        view_name, _, _, staleness_sec, status = row

        if staleness_sec > 600:  # 10 minutes
            alert_ops_team(
                severity="warning",
                message=f"MV {view_name} is stale ({staleness_sec}s)"
            )

        if staleness_sec > 1800:  # 30 minutes
            alert_ops_team(
                severity="critical",
                message=f"MV {view_name} critically stale ({staleness_sec}s)"
            )

# Run every 5 minutes
while True:
    check_mv_health(db)
    time.sleep(300)
```

---

## Best Practices

### When to Use Materialized Views

** Good Use Cases:**

1. **Expensive Aggregations:** Queries with COUNT, SUM, AVG, GROUP BY on large tables
2. **Complex Joins:** Multi-table joins that scan millions of rows
3. **Dashboard KPIs:** Pre-computed metrics for real-time dashboards
4. **Reporting:** Scheduled reports requiring consistent data snapshots
5. **API Responses:** Frequently-accessed endpoints with predictable queries
6. **Time-Series Rollups:** Hourly/daily aggregations of event data
7. **Edge Analytics:** Real-time computations on resource-constrained devices

** Poor Use Cases:**

1. **Real-Time Transactional Data:** Queries requiring up-to-the-second accuracy
2. **Highly Volatile Data:** Base tables changing more frequently than refresh interval
3. **Simple Queries:** Single-table scans that already execute in <100ms
4. **Rarely-Accessed Data:** Views queried less than once per day
5. **Unique Queries:** Ad-hoc queries with unpredictable filters/joins

### Refresh Strategy Selection

**Decision Matrix:**

| Base Table Size | Change Rate | Query Frequency | Recommended Strategy |
|-----------------|-------------|-----------------|---------------------|
| <10K rows | Any | Any | Full refresh on-demand |
| 10K-100K rows | <1% | Hourly+ | Scheduled incremental refresh |
| 100K-1M rows | <5% | Frequent | Scheduled incremental refresh (5-15 min) |
| 1M+ rows | <10% | Frequent | Scheduled incremental refresh + concurrent |
| Any size | >50% | Any | Full refresh (incremental overhead too high) |

**Refresh Timing (from a scheduler such as cron):**

```bash
# Real-time dashboards: every 5 minutes
*/5 * * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW dashboard_kpis INCREMENTALLY"

# Standard reports: every hour
0 * * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_reports"

# Nightly batch: once a day, off-peak
30 2 * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW daily_summaries"
```

### Index Optimization

Materialized views benefit from indexes on frequently-filtered columns:

**Identifying Index Candidates:**
```sql
-- Common query patterns
SELECT * FROM customer_activity WHERE customer_id = ?;
SELECT * FROM product_stats WHERE category = ? ORDER BY total_sales DESC;
```

**Creating Indexes:**
```sql
-- Index on internal MV storage table
CREATE INDEX idx_customer_activity_id
ON __mv_customer_activity(customer_id);

CREATE INDEX idx_product_stats_category_sales
ON __mv_product_stats(category, total_sales DESC);
```

**Index Strategy:**
1. **Primary Filters:** Index columns used in WHERE clauses
2. **Sort Columns:** Index columns in ORDER BY (with DESC if descending)
3. **Composite Indexes:** Combine multiple frequently-filtered columns
4. **Avoid Over-Indexing:** Each index adds write overhead during refresh

### Naming Conventions

Establish consistent naming for maintainability:

**Recommended Patterns:**
```sql
-- Suffix with purpose
CREATE MATERIALIZED VIEW sales_summary_mv AS ...;
CREATE MATERIALIZED VIEW user_stats_daily_mv AS ...;

-- Or prefix with category
CREATE MATERIALIZED VIEW mv_dashboard_kpis AS ...;
CREATE MATERIALIZED VIEW mv_report_revenue AS ...;

-- Time-series views: include granularity
CREATE MATERIALIZED VIEW hourly_metrics_mv AS ...;
CREATE MATERIALIZED VIEW daily_rollup_mv AS ...;
```

### Documentation Standards

Document materialized views in application code or database comments:

```sql
-- Materialized View: sales_summary_mv
-- Purpose: Pre-computed sales metrics for dashboard
-- Refresh Strategy: incremental refresh every 5 minutes (cron)
-- Base Tables: sales, products
-- Owner: Analytics Team
-- Created: 2025-01-01
CREATE MATERIALIZED VIEW sales_summary_mv AS
SELECT
    product_id,
    COUNT(*) as total_orders,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM sales
JOIN products USING (product_id)
WHERE sale_date > strftime('%s', 'now', '-90 days')
GROUP BY product_id;
```

### Maintenance Checklist

**Weekly:**
- Review `pg_mv_staleness()` for consistently stale views
- Check refresh error logs

**Monthly:**
- Analyze query patterns to identify new MV candidates
- Review and optimize slow-refreshing views
- Force full refresh on long-running incremental views

**Quarterly:**
- Audit unused materialized views (low query frequency)
- Update refresh intervals based on usage patterns

---

## Real-World Examples

### Example 1: E-Commerce Dashboard Metrics

**Scenario:** Real-time sales dashboard showing revenue by product category, updated every 5 minutes.

```sql
-- Base tables
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name TEXT,
    category TEXT,
    price REAL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    order_date INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Materialized view for dashboard
CREATE MATERIALIZED VIEW dashboard_sales_by_category AS
SELECT
    p.category,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.quantity) as units_sold,
    SUM(o.quantity * p.price) as total_revenue,
    AVG(o.quantity * p.price) as avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date > strftime('%s', 'now', '-30 days')
GROUP BY p.category;

-- Refresh every 5 minutes from a scheduler
REFRESH MATERIALIZED VIEW dashboard_sales_by_category INCREMENTALLY;

-- Query for dashboard (instant)
SELECT
    category,
    total_revenue,
    units_sold
FROM dashboard_sales_by_category
ORDER BY total_revenue DESC;
```

**Results:**
- Query latency: 45ms (vs 8.5s without MV)
- Refresh time: 1.2s (incremental)
- Dashboard loads 189x faster

### Example 2: IoT Sensor Data Rollup

**Scenario:** Aggregate 10,000 sensors reporting every 10 seconds into hourly summaries.

```sql
-- Raw sensor data
CREATE TABLE sensor_readings (
    reading_id INT PRIMARY KEY,
    sensor_id TEXT,
    temperature REAL,
    humidity REAL,
    timestamp INT
);

-- Hourly rollup materialized view
CREATE MATERIALIZED VIEW hourly_sensor_stats AS
SELECT
    (timestamp / 3600) * 3600 AS hour_bucket,
    sensor_id,
    COUNT(*) as sample_count,
    AVG(temperature) as avg_temp,
    MIN(temperature) as min_temp,
    MAX(temperature) as max_temp,
    AVG(humidity) as avg_humidity
FROM sensor_readings
WHERE timestamp > strftime('%s', 'now', '-7 days')
GROUP BY hour_bucket, sensor_id;

-- Refresh every hour from a scheduler
REFRESH MATERIALIZED VIEW hourly_sensor_stats INCREMENTALLY;

-- Query last 24 hours for specific sensor
SELECT
    datetime(hour_bucket, 'unixepoch') as hour,
    avg_temp,
    max_temp,
    sample_count
FROM hourly_sensor_stats
WHERE sensor_id = 'sensor_42'
  AND hour_bucket > strftime('%s', 'now', '-24 hours')
ORDER BY hour_bucket DESC;
```

**Results:**
- Storage: 1.7M raw readings → 7K aggregated rows
- Query latency: 18ms (vs 12s scanning raw data)
- Memory footprint: 95MB total on Raspberry Pi

### Example 3: Customer Activity Report

**Scenario:** Generate customer engagement reports showing purchase history and lifetime value.

```sql
-- Base tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email TEXT,
    signup_date INT
);

CREATE TABLE purchases (
    purchase_id INT PRIMARY KEY,
    customer_id INT,
    amount REAL,
    purchase_date INT
);

-- Customer activity summary
CREATE MATERIALIZED VIEW customer_activity_report AS
SELECT
    c.customer_id,
    c.email,
    COUNT(p.purchase_id) as total_purchases,
    SUM(p.amount) as lifetime_value,
    AVG(p.amount) as avg_purchase_value,
    MIN(p.purchase_date) as first_purchase,
    MAX(p.purchase_date) as last_purchase,
    (strftime('%s', 'now') - MAX(p.purchase_date)) / 86400 as days_since_last_purchase
FROM customers c
LEFT JOIN purchases p ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.email;

-- Refresh nightly (off-peak hours) from a scheduler
REFRESH MATERIALIZED VIEW customer_activity_report;

-- Query top customers by lifetime value
SELECT
    email,
    total_purchases,
    lifetime_value
FROM customer_activity_report
WHERE lifetime_value > 1000
ORDER BY lifetime_value DESC
LIMIT 100;
```

**Results:**
- Report generation: <200ms (vs 45s without MV)
- Nightly refresh: 5 minutes (incremental)
- Serves 10,000 customer queries/hour

### Example 4: API Response Caching

**Scenario:** Cache "top products by sales" API endpoint response.

```sql
-- Materialized view for API endpoint
CREATE MATERIALIZED VIEW api_top_products AS
SELECT
    product_id,
    product_name,
    category,
    total_sales,
    avg_rating,
    stock_quantity
FROM (
    SELECT
        p.product_id,
        p.name as product_name,
        p.category,
        SUM(o.quantity * p.price) as total_sales,
        AVG(r.rating) as avg_rating,
        i.stock_quantity,
        ROW_NUMBER() OVER (ORDER BY SUM(o.quantity * p.price) DESC) as rank
    FROM products p
    JOIN orders o ON p.product_id = o.product_id
    LEFT JOIN reviews r ON p.product_id = r.product_id
    JOIN inventory i ON p.product_id = i.product_id
    WHERE o.order_date > strftime('%s', 'now', '-30 days')
    GROUP BY p.product_id, p.name, p.category, i.stock_quantity
)
WHERE rank <= 100;

-- Refresh every 15 minutes from a scheduler
REFRESH MATERIALIZED VIEW CONCURRENTLY api_top_products;

-- API endpoint query (sub-100ms)
SELECT * FROM api_top_products WHERE category = ? LIMIT 10;
```

**API Handler (Python, parameterized):**
```python
def top_products(conn, category=None, limit=10):
    with conn.cursor() as cur:
        if category:
            cur.execute(
                "SELECT * FROM api_top_products WHERE category = %s LIMIT %s",
                (category, limit),
            )
        else:
            cur.execute("SELECT * FROM api_top_products LIMIT %s", (limit,))
        return cur.fetchall()
```

**Results:**
- API response time: 12ms P99 (vs 5-8s without MV)
- Throughput: 15,000 req/sec per instance
- Eliminated Redis dependency (saved 512MB memory)

---

## Conclusion

Materialized views speed up repeated expensive queries in HeliosDB-Lite, especially for analytics, dashboards, and reporting workloads. By pre-computing and storing query results, materialized views can reduce query latency from seconds to milliseconds, enabling real-time analytics on resource-constrained devices.

**Key Takeaways:**

1. **Use materialized views for expensive aggregations** and frequently-executed queries
2. **Enable incremental refresh** for 10-100x faster updates on large datasets
3. **Schedule refreshes** (full, concurrent or incremental) to match how fresh each view must be
4. **Monitor staleness** using `pg_mv_staleness()` to ensure SLA compliance
5. **Optimize with indexes** on frequently-filtered columns in materialized views

**Next Steps:**

- Review your application's slowest queries using `EXPLAIN` or query logs
- Identify 3-5 queries that would benefit from materialization
- Create materialized views and measure performance improvements
- Configure appropriate refresh strategies based on data freshness requirements
- Monitor using `pg_mv_staleness()` and adjust refresh intervals as needed

---