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

# Materialized Views

:::caution[What works today]
Materialized views run as views in the current release. `CREATE MATERIALIZED VIEW` is
accepted and registers the view's query, which runs each time the view is read, so
results are always current. `REFRESH MATERIALIZED VIEW` is accepted and has nothing to
refresh. Stored (pre-computed) results, incremental refresh, refresh scheduling (on
commit, interval, cron) and automatic query rewriting are planned. View definitions are
held in server memory: re-create them after a server restart.
:::

---

## Overview

HeliosDB accepts the PostgreSQL materialized-view statements today and runs each materialized view as a view. The planned design adds pre-computed results that are kept up to date by incremental refresh, refresh scheduling and transparent query rewriting, to speed up read-heavy workloads.

## Key Capabilities

| Capability | Description | Benefit | Availability |
|------------|-------------|---------|--------------|
| **View definition** | `CREATE MATERIALIZED VIEW ... AS SELECT`, `DROP MATERIALIZED VIEW` | PostgreSQL-compatible DDL | Available (runs as a view) |
| **Incremental Refresh** | Update only changed data | 10-100x faster refresh | Planned |
| **Flexible Scheduling** | Manual, on-commit, interval, cron | Match the workload pattern | Planned |
| **Query Rewriting** | Automatic view substitution | Transparent optimization | Planned |
| **Multiple Storage Backends** | LSM, Memory, Columnar, Hybrid | Storage chosen per use case | Planned |
| **Join Elimination** | Pre-compute expensive joins | Order of magnitude speedup | Planned |
| **Aggregation Pushdown** | Pre-compute aggregations | Real-time analytics | Planned |

## Architecture (planned)

```
                        Materialized Views Architecture

  ┌─────────────────────────────────────────────────────────────────────┐
  │                         Query Layer                                  │
  │  ┌─────────────┐    ┌──────────────────┐    ┌─────────────────────┐│
  │  │ SQL Parser  │ -> │  Query Rewriter  │ -> │  Query Executor     ││
  │  └─────────────┘    └──────────────────┘    └─────────────────────┘│
  └──────────────────────────────┬──────────────────────────────────────┘
                                 │
                                 v
  ┌─────────────────────────────────────────────────────────────────────┐
  │                    Materialized View Manager                         │
  │  ┌───────────────┐  ┌─────────────────┐  ┌────────────────────────┐│
  │  │ View Registry │  │ Refresh Manager │  │ Dependency Tracker     ││
  │  └───────────────┘  └─────────────────┘  └────────────────────────┘│
  └──────────────────────────────┬──────────────────────────────────────┘
                                 │
                                 v
  ┌─────────────────────────────────────────────────────────────────────┐
  │                        Storage Layer                                 │
  │  ┌─────────┐  ┌─────────┐  ┌───────────┐  ┌────────────────────────┐│
  │  │   LSM   │  │ Memory  │  │ Columnar  │  │       Hybrid           ││
  │  └─────────┘  └─────────┘  └───────────┘  └────────────────────────┘│
  └─────────────────────────────────────────────────────────────────────┘
```

## Planned Feature Highlights

### 1. Incremental Refresh

Update views by processing only changed data.

### 2. Flexible Refresh Strategies

| Strategy | Use Case | Latency |
|----------|----------|---------|
| **Manual** | Development, testing | On-demand |
| **OnCommit** | Real-time requirements | Milliseconds |
| **Interval** | Regular updates | Seconds to minutes |
| **Cron** | Scheduled updates | Any schedule |

### 3. Query Rewriting

Automatically substitute materialized views to accelerate queries:

- **Exact Match**: Query matches view definition
- **Join Elimination**: View pre-computes expensive joins
- **Aggregation Pushdown**: View pre-computes aggregations
- **Superset Match**: View contains query data plus more
- **Filter Pushdown**: View has beneficial filters

## Performance Benefits

The figures below are indicative, measured on representative fixtures; reproduce on your own hardware.

| Scenario | Without MV | With MV | Speedup |
|----------|-----------|---------|---------|
| Complex Join | 500ms | 5ms | 100x |
| Large Aggregation | 2s | 10ms | 200x |
| Dashboard Query | 800ms | 8ms | 100x |
| OLAP Analytics | 5s | 50ms | 100x |

## Use Cases

Recommended refresh strategies once stored materialized views are available:

| Use Case | Recommended Strategy | Description |
|----------|---------------------|-------------|
| **Real-time Dashboards** | OnCommit | Instant updates |
| **Daily Reports** | Cron (midnight) | Scheduled refresh |
| **Session Analytics** | Interval (5min) | Near-real-time |
| **Historical Analysis** | Manual | On-demand refresh |

---

**See Also**: HeliosDB Feature Index