Materialized Views

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