Materialized Views Tutorial

Target Audience: Developers and Database Administrators

Introduction
Creating Materialized Views
Querying Materialized Views
Manual Refresh
Automatic Refresh (Planned)
Incremental Refresh
Monitoring
Best Practices
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:

-- 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

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

Example: Simple Aggregation

-- 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:

-- 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:

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

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

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

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

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:

-- 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:

-- 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:

-- 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

Add indexes on frequently-filtered columns:

-- Note: Indexes are created on the internal __mv_{view_name} table
CREATE INDEX idx_product_stats_sales ON __mv_product_stats(total_sales DESC);

Use projection to minimize data transfer:

-- Only select needed columns
SELECT product_id, total_sales
FROM product_stats
WHERE category = 'Electronics';

Combine filters efficiently:

-- 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:

-- 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:

Re-executes the original query on base tables
Replaces stored results with new data
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:

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

How It Works:

Creates temporary table with unique timestamp suffix
Populates temporary table with refreshed data
Atomically renames old table to backup, temp to current
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):

-- After bulk data load
COPY sales FROM '/data/sales_batch.csv';
REFRESH MATERIALIZED VIEW sales_summary;

Scheduled Refresh (Externally Triggered):

# 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):

-- 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:

-- 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:

# 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"

-- 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 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:

-- 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:

-- 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:

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:

-- 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:

\timing on
SELECT * FROM sales_summary;
-- Expected: <100ms for most queries

Refresh Duration Tracking:

\timing on
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
-- Full refresh: 10-60s
-- Incremental: 0.1-1s

CPU and Memory Usage:

# 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:

-- 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:

-- 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:

# 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 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:**

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

** Poor Use Cases:**

Real-Time Transactional Data: Queries requiring up-to-the-second accuracy
Highly Volatile Data: Base tables changing more frequently than refresh interval
Simple Queries: Single-table scans that already execute in <100ms
Rarely-Accessed Data: Views queried less than once per day
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):

# 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:

-- Common query patterns
SELECT * FROM customer_activity WHERE customer_id = ?;
SELECT * FROM product_stats WHERE category = ? ORDER BY total_sales DESC;

Creating Indexes:

-- 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:

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

Naming Conventions

Establish consistent naming for maintainability:

Recommended Patterns:

-- 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:

-- 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.

-- 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.

-- 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.

-- 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.

-- 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):

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:

Use materialized views for expensive aggregations and frequently-executed queries
Enable incremental refresh for 10-100x faster updates on large datasets
Schedule refreshes (full, concurrent or incremental) to match how fresh each view must be
Monitor staleness using pg_mv_staleness() to ensure SLA compliance
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

Materialized Views Tutorial

Table of Contents

Introduction

What are Materialized Views?

Views vs Materialized Views

Use Cases

1. Dashboards and Reporting

2. Analytics Queries

3. API Response Caching

4. Edge Computing

Creating Materialized Views

Basic Syntax

IF NOT EXISTS Clause

WITH Options

Examples with Aggregations

COUNT and SUM

AVG, MIN, MAX

Complex Aggregations with Filtering

Time-Series Rollups

Querying Materialized Views

SELECT from Materialized Views

Filtering and Ordering

Joins with Materialized Views

Query Optimization Tips

Manual Refresh

REFRESH MATERIALIZED VIEW

REFRESH CONCURRENTLY

When to Use Each

Refresh Timing Strategies

Automatic Refresh (Planned)

Incremental Refresh

How It Works

Delta Tracking

Performance Benefits

Forcing Full Refresh

Monitoring

pg_mv_staleness View

Refresh History

Performance Metrics

Alerting Strategies

Best Practices

When to Use Materialized Views

Refresh Strategy Selection

Index Optimization

Naming Conventions

Documentation Standards

Maintenance Checklist

Real-World Examples

Example 1: E-Commerce Dashboard Metrics

Example 2: IoT Sensor Data Rollup

Example 3: Customer Activity Report

Example 4: API Response Caching

Conclusion