Materialized Views — SoliDB Docs

Overview

Materialized Views in SoliDB allow you to pre-compute and store the results of complex SDBQL queries. Unlike standard views (which run the query every time they are accessed), materialized views persist the data to disk in a system-managed collection.

This is ideal for expensive operations like multi-collection joins, heavy aggregations, or complex filtering that doesn't need to be strictly real-time.

High Performance

Read pre-computed results instantly without re-executing complex logic.

Persisted Storage

Data survives restarts and can be indexed just like any other collection.

Snapshot Isolation

Provides a consistent snapshot of data at the time of the last refresh.

Creating Views

Use the CREATE MATERIALIZED VIEW statement. Passing the AS clause defines the query used to populate the view.

CREATE MATERIALIZED VIEW high_value_customers AS
FOR u IN users
  JOIN orders ON u._key == orders.user_key
  LET total = SUM(orders[*].total)
  FILTER total > 1000
  RETURN {
    customer_id: u._key,
    name: u.name,
    lifetime_value: total
  }

This command creates a new system collection (e.g., _system:high_value_customers) and populates it immediately with the query results.

Refreshing Data

By default, a materialized view does not update automatically when the underlying data changes — you trigger a refresh manually. You can also have SoliDB refresh it for you on a schedule (see below).

REFRESH MATERIALIZED VIEW high_value_customers

Note: Refreshing a view truncates the existing collection and repopulates it from scratch using the stored query definition. This operation is atomic in terms of visibility (the collection is replaced), but may take time depending on data size.

Scheduled (automatic) refresh

Add a REFRESH "<interval>" clause when you create the view (after the name, before AS) and SoliDB re-runs the query on that cadence in the background — no manual REFRESH needed.

CREATE MATERIALIZED VIEW high_value_customers REFRESH "5m" AS
FOR u IN users
  JOIN orders ON u._key == orders.user_key
  LET total = SUM(orders[*].total)
  FILTER total > 1000
  RETURN { customer_id: u._key, name: u.name, lifetime_value: total }

The interval is a quoted string. A plain number is seconds, or use a suffix — s (seconds), m (minutes), h (hours), d (days). So "30s", "5m", "1h" and "1d" are all valid; "90" means 90 seconds. Omit the clause entirely for a manual-refresh-only view.

Cluster note: a scheduled refresh runs the same truncate-and-repopulate as a manual one. In a cluster it is node-local — each node recomputes its own copy on the interval from its local view of the source data (with replicated source data every node converges to the same result). The schedule is tracked in memory per node, so a refresh also runs shortly after a restart.

Querying & Management

Querying

Once created, a materialized view acts just like a regular read-only collection. You can query it, filter it, and even join it with other collections.

FOR c IN high_value_customers
  FILTER c.lifetime_value > 5000
  RETURN c.name

Managing via Dashboard

You can manage your views through the SoliDB Dashboard:

Navigate to Data > Materialized Views in the sidebar.
View all existing views and their definitions.
Trigger a Refresh with a single click.
Delete views when they are no longer needed.

Real-World Example: Daily Revenue Dashboard

Scenario: You have an e-commerce platform with an orders collection containing millions of records. You want to display key metrics like "Daily Revenue by Category" on your admin dashboard.

Problem: Running an aggregation query on the live orders collection every time the dashboard loads is too slow (taking seconds) and puts unnecessary load on the system.

Solution: Create a Materialized View that pre-aggregates this data.

-- 1. Create the view
CREATE MATERIALIZED VIEW daily_revenue_stats AS
FOR o IN orders
  -- Truncate timestamp to day
  LET day = DATE_FORMAT(o.created_at, "%Y-%m-%d")
  -- Group by day and category
  COLLECT date = day, cat = o.category WITH SUM(o.total_amount) INTO revenue
  RETURN {
    date: date,
    category: cat,
    total_revenue: revenue,
    generated_at: DATE_NOW()
  }

-- 2. Query the view (Instant!)
FOR stat IN daily_revenue_stats
  FILTER stat.date >= "2023-01-01"
  SORT stat.date DESC
  RETURN stat