Materialized Views

A materialized view is a schema-level object that stores the result of a SQL SELECT query as a backing document type. Unlike regular views (which re-execute the query on every access), a materialized view holds a pre-computed snapshot of data that can be queried directly for fast reads.

A materialized view:

  • Wraps a SQL SELECT query as its defining query

  • Stores results in a backing DocumentType with standard buckets

  • Supports three refresh modes: manual, incremental (post-commit), and periodic (scheduled)

  • Persists its definition and metadata in schema.json alongside other schema objects

  • Can be created, dropped, refreshed, and altered via SQL DDL statements or the Java Schema API

Refresh Modes

MANUAL

The view data is never automatically updated. You must trigger a refresh explicitly via REFRESH MATERIALIZED VIEW or the Java API. Use this when you control refresh timing yourself or when source data changes infrequently.

INCREMENTAL

After every committed transaction that modifies a source type, ArcadeDB automatically refreshes the view in a post-commit callback. The refresh is:

  • Transactionally safe: runs after the source transaction commits successfully; rolled-back transactions do not trigger a refresh

  • Batched per transaction: multiple record changes in a single transaction result in one refresh, not one per record

  • For simple queries (single source type, no aggregates, no GROUP BY, no subqueries, no `JOIN`s): performs a full refresh

  • For complex queries (aggregates, GROUP BY, etc.): also performs a full refresh (per-record incremental optimization is planned for a future release)

If the refresh fails, the view is marked STALE and a warning is logged. A manual refresh can recover it.

PERIODIC

A background scheduler thread runs a full refresh at the specified interval after each successful refresh completes. Intervals are specified in seconds, minutes, or hours:

REFRESH EVERY 30 SECOND
REFRESH EVERY 5 MINUTE
REFRESH EVERY 1 HOUR

The scheduler uses a single daemon thread (ArcadeDB-MV-Scheduler) shared across all periodic views. If the database is closed, all scheduled tasks are cancelled automatically.

View Status

Each view tracks a status field that reflects its current state:

Status Meaning

VALID

Data is up to date with the last refresh

STALE

A refresh failed or was interrupted; data may be outdated

BUILDING

A refresh is currently in progress

ERROR

The last refresh encountered a fatal error

If the database crashes while a view is BUILDING, the status is reset to STALE on the next startup to signal that the data may be incomplete.

Querying a Materialized View

Query a materialized view exactly like any other document type:

SELECT * FROM ActiveUsers
SELECT name FROM ActiveUsers WHERE name LIKE 'A%'
SELECT count(*) FROM RecentOrders

Java API

Creating a view

database.transaction(() -> {
    database.getSchema().buildMaterializedView()
        .withName("ActiveUsers")
        .withQuery("SELECT name, email FROM User WHERE active = true")
        .withRefreshMode(MaterializedViewRefreshMode.MANUAL)
        .create();
});

Builder options:

Method Description

withName(String)

Name for the view (required)

withQuery(String)

Defining SQL SELECT query (required)

withRefreshMode(MaterializedViewRefreshMode)

MANUAL, INCREMENTAL, or PERIODIC (default: MANUAL)

withTotalBuckets(int)

Number of buckets for the backing type

withPageSize(int)

Page size for the backing type

withRefreshInterval(long)

Interval in milliseconds for PERIODIC mode

withIgnoreIfExists(boolean)

When true, returns existing view instead of throwing

Querying schema

Schema schema = database.getSchema();

// Check existence
boolean exists = schema.existsMaterializedView("ActiveUsers");

// Get a specific view
MaterializedView view = schema.getMaterializedView("ActiveUsers");

// List all views
MaterializedView[] views = schema.getMaterializedViews();

Refreshing and dropping

// Programmatic refresh
database.getSchema().getMaterializedView("ActiveUsers").refresh();

// Drop via schema
database.getSchema().dropMaterializedView("ActiveUsers");

// Drop via the view itself
database.getSchema().getMaterializedView("ActiveUsers").drop();

Inspecting a view

MaterializedView view = database.getSchema().getMaterializedView("HourlySummary");

view.getName();             // "HourlySummary"
view.getQuery();            // the defining SQL query
view.getRefreshMode();      // MaterializedViewRefreshMode.PERIODIC
view.getStatus();           // "VALID", "STALE", "BUILDING", or "ERROR"
view.getLastRefreshTime();  // epoch millis of last successful refresh
view.isSimpleQuery();       // true if eligible for per-record optimization
view.getSourceTypeNames();  // list of source type names parsed from the query
view.getBackingType();      // the underlying DocumentType

Behavior and Constraints

  • Backing type protection: You cannot DROP TYPE on a type that backs a materialized view. Drop the materialized view first.

  • Name uniqueness: The view name must not match any existing type or materialized view.

  • Source type validation: All types referenced in the FROM clause must exist when the view is created.

  • Persistence: View definitions are stored in schema.json under a "materializedViews" key and survive database restarts. Listener registration for INCREMENTAL views and scheduler tasks for PERIODIC views are re-established on startup.

  • Transaction safety: The initial full refresh and all subsequent refreshes run inside their own transactions.

  • Query result columns: Only non-internal properties (those not starting with @) are copied into the backing type during refresh.

  • No schema on backing type: The backing document type is schema-less; property types are not enforced.

Error Handling

  • If a post-commit refresh fails (INCREMENTAL mode), the view is marked STALE and a WARNING is logged. The source transaction is unaffected.

  • If a periodic refresh fails, the view is marked ERROR and a SEVERE log entry is written. The scheduler continues running and will retry on the next interval.

  • Callback errors in the transaction callback system are logged at WARNING level and do not affect the triggering transaction or other callbacks.

Limitations

  • ALTER MATERIALIZED VIEW is not yet implemented.

  • Per-record incremental refresh (tracking _sourceRID to update individual view rows) is a planned future optimization. Currently, all refresh operations perform a full truncate-and-reload.

  • No support for cross-database queries in the defining query.

  • Server replication: materialized view data lives in the local backing type and is replicated like any other document type in an HA cluster, but refresh triggering is local to the node that executes the write.

Example: Sales Dashboard

-- Source type
CREATE DOCUMENT TYPE Sale;

-- A periodic summary refreshed every minute
CREATE MATERIALIZED VIEW SalesByProduct
  AS SELECT product, sum(amount) AS total, count(*) AS count
     FROM Sale
     GROUP BY product
  REFRESH EVERY 1 MINUTE;

-- An incremental view of recent activity (simple query)
CREATE MATERIALIZED VIEW RecentSales
  AS SELECT product, amount, date
     FROM Sale
     WHERE date >= '2026-01-01'
  REFRESH INCREMENTAL;

-- Query the views
SELECT * FROM SalesByProduct ORDER BY total DESC;
SELECT product, amount FROM RecentSales WHERE amount > 1000;

-- Manual refresh after a bulk import
REFRESH MATERIALIZED VIEW SalesByProduct;

-- Teardown
DROP MATERIALIZED VIEW SalesByProduct;
DROP MATERIALIZED VIEW RecentSales;

For more information, see: