Materialized Views

Materialized views store the result of a query physically, letting you trade storage space for query speed. HeliosDB-Lite supports creating, refreshing, and automatically rewriting queries to use materialized views.

Prerequisites

HeliosDB-Lite v3.5 or later
Access to the SQL shell (REPL, PostgreSQL wire protocol, or REST API)

Step 1 — Create a Base Table and Load Data

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,
    product     TEXT,
    amount      FLOAT8,
    created_at  TEXT
);

INSERT INTO orders VALUES (1, 100, 'Widget',  29.99, '2025-03-01');
INSERT INTO orders VALUES (2, 100, 'Gadget',  49.99, '2025-03-02');
INSERT INTO orders VALUES (3, 200, 'Widget',  29.99, '2025-03-03');
INSERT INTO orders VALUES (4, 200, 'Gadget',  49.99, '2025-03-04');
INSERT INTO orders VALUES (5, 300, 'Widget',  29.99, '2025-03-05');

Step 2 — Create a Materialized View

CREATE MATERIALIZED VIEW order_summary AS
SELECT customer_id,
       COUNT(*)    AS order_count,
       SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

HeliosDB executes the query immediately and stores the results in an internal data table. Subsequent queries against order_summary read from this pre-computed data rather than scanning and aggregating orders each time.

Step 3 — Query the Materialized View

SELECT * FROM order_summary ORDER BY total_spent DESC;

Expected output:

 customer_id | order_count | total_spent
-------------+-------------+------------
         100 |           2 |       79.98
         200 |           2 |       79.98
         300 |           1 |       29.99

Step 4 — Refresh After Data Changes

Materialized views are snapshots. When the underlying data changes, the view becomes stale. Refresh it explicitly:

INSERT INTO orders VALUES (6, 300, 'Gizmo', 99.99, '2025-03-06');

REFRESH MATERIALIZED VIEW order_summary;

Now query again:

SELECT * FROM order_summary WHERE customer_id = 300;

 customer_id | order_count | total_spent
-------------+-------------+------------
         300 |           2 |      129.98

Step 5 — Concurrent Refresh (Concept)

REFRESH MATERIALIZED VIEW CONCURRENTLY builds a new copy of the view data while the old copy remains available for reads. Once complete, the swap is atomic. This avoids locking readers during long refreshes.

REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

Note: Concurrent refresh requires the materialized view to have a unique index or otherwise be diffable. If this is not possible, HeliosDB falls back to a regular refresh.

Step 6 — Automatic Refresh Options

You can configure auto-refresh behavior at creation time using WITH options:

CREATE MATERIALIZED VIEW hourly_stats AS
SELECT product, COUNT(*) AS cnt
FROM orders
GROUP BY product
WITH (
    auto_refresh = true,
    threshold_dml_rate = 100,
    max_cpu_percent = 15
);

Available options:

Option	Description
`auto_refresh`	Enable/disable automatic refresh
`threshold_table_size`	Min base table size to trigger refresh
`threshold_dml_rate`	DML ops/sec threshold before auto-refresh
`max_cpu_percent`	CPU cap for background refresh
`lazy_update`	Defer refresh until next read
`lazy_catchup_window`	Max staleness before forced refresh

Step 7 — Check Refresh Status

HeliosDB provides a system view to monitor materialized view refresh activity:

SELECT * FROM helios_mv_refresh_status;

 view_name     | refresh_interval | last_refresh          | next_refresh          | status
---------------+------------------+-----------------------+-----------------------+--------
 order_summary | manual           | 2025-03-26 10:05:00   |                       | idle
 hourly_stats  | auto (100 dml/s) | 2025-03-26 10:00:00   | 2025-03-26 11:00:00   | active

Step 8 — Optimizer MV Rewrite

The query optimizer can transparently rewrite queries against base tables to use materialized view data when the view satisfies the query. No changes to your application SQL are needed.

-- This query hits the base 'orders' table:
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

-- The optimizer rewrites it to read from order_summary instead,
-- avoiding a full scan of the orders table.

Use EXPLAIN to confirm the rewrite:

EXPLAIN SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

Look for MV Rewrite: order_summary in the plan output.

Step 9 — Drop a Materialized View

DROP MATERIALIZED VIEW order_summary;
DROP MATERIALIZED VIEW IF EXISTS hourly_stats;

The IF EXISTS clause prevents an error when the view does not exist.

Tips and Troubleshooting

Staleness: Materialized views do not update automatically unless auto_refresh = true is set. Always REFRESH after bulk loads if you need up-to-date results.
Storage cost: Each materialized view stores a full copy of the query result. Monitor disk usage if you create views over large datasets.
MV rewrite eligibility: The optimizer rewrites a query only when the materialized view’s query is a semantic match (same GROUP BY, compatible WHERE). If EXPLAIN does not show a rewrite, check that columns and aggregation functions match.
Concurrent refresh limits: The default configuration allows 2 concurrent refreshes. Adjust max_concurrent_refreshes in heliosdb.toml under [materialized_views] if needed.
Dropping base tables: Dropping a table that a materialized view depends on will cause refresh to fail. Drop dependent views first.