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.

Which types are tracked. An INCREMENTAL view is refreshed when a transaction changes a record of any type its query names, anywhere in the query: the top-level FROM, a subquery in FROM, and a subquery in WHERE, in the projection or in LET, at any depth. Each of these types is tracked together with all its subtypes, including subtypes created after the view. They are listed in the sourceTypes column of schema:materializedViews, and DROP TYPE on any of them is refused while the view exists.

Sources that cannot be tracked. REFRESH INCREMENTAL is refused with an error, on CREATE MATERIALIZED VIEW and on ALTER MATERIALIZED VIEW …​ REFRESH INCREMENTAL, when the query reads a source that no type’s record events report: a TRAVERSE, a MATCH, a function call in FROM, a bucket (FROM bucket:…​), an index, schema metadata (FROM schema:…​), record ids or parameters in FROM. Use REFRESH MANUAL or REFRESH EVERY for such a view.

Values reached by following a link or an edge from a tracked record (for example address.city or out()) are not tracked, since they cannot be detected from the query text: a change to the linked record does not refresh the view.

This behavior changed in 26.11.1 (issue #10205). Before, only the type of the first FROM identifier was tracked, and only its own records, not those of its subtypes: a change to a type read by a subquery, or to a subtype, left the view out of date while its status stayed VALID. Existing views loaded from an older database have their source list completed from their query automatically on open, so they start refreshing on those changes and DROP TYPE of those types is refused. An existing INCREMENTAL view whose query reads one of the untrackable sources above keeps loading; creating a new one, or switching a view to INCREMENTAL, is refused.

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. Only PERIODIC accepts an interval: create() refuses one on a MANUAL or INCREMENTAL view, embedded and remote alike

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();

Changing the refresh mode

// Refresh every 5 minutes; pass 0 as the interval for MANUAL or INCREMENTAL
database.getSchema().alterMaterializedView("ActiveUsers", MaterializedViewRefreshMode.PERIODIC, 300_000);
database.getSchema().alterMaterializedView("ActiveUsers", MaterializedViewRefreshMode.INCREMENTAL, 0);

alterMaterializedView() is the Java form of ALTER MATERIALIZED VIEW …​ REFRESH. (Since v26.10.2) it works on a remote database too, where it is sent as that SQL statement, and the embedded and remote calls refuse the same arguments: a null mode or a negative interval (IllegalArgumentException) and an interval on a MANUAL or INCREMENTAL view (SchemaException). A remote PERIODIC interval must be a whole number of seconds, as in SQL. In v26.10.1 and earlier the remote call threw UnsupportedOperationException, and the embedded one stored an interval on a MANUAL or INCREMENTAL view, where nothing used it.

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();  // every type the query names, at any depth
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.

  • Source type protection: You cannot DROP TYPE on a type the defining query reads (any type listed in sourceTypes). Drop the materialized view first.

  • Renaming a type: ALTER TYPE <source> NAME <new> on a source type of a view carries the view with it: the source type name the view stores and the FROM of its defining query are rewritten to the new name, and the change is saved with the schema. A continuous aggregate follows the rename of its source type the same way, and so does a trigger bound to the type. The rename is refused, before anything moves, when the type is the backing type of a view (a view is named after its backing type, so it cannot be renamed on its own) or when the query names the type in a form that cannot be re-pointed with certainty (for example a subquery that reads the same type again): drop the view, rename the type and create the view again. A view defined through an alias of the type keeps working without being rewritten, since the alias survives the rename. (Since v26.11.1. Before, the rename left the view naming a type that no longer existed: its refresh failed, and DROP TYPE on the renamed type was let through. A database that already holds a view in that state is protected from the drop: DROP TYPE is refused, naming the view that is out of date, until that view is dropped.)

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

  • Source type validation: All types referenced by the query, including those in subqueries, 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

  • 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: