Views

Overview

Views represent queries that are saved under a name for reference. Views provide a shorthand for the underlying query.

Views

A view saves a query under a name to provide a shorthand for referencing the query. Views are not associated with a cluster and can be referenced across clusters.

During view creation, the underlying query is not executed. Each time the view is accessed, view results are recomputed from scratch.

CREATE VIEW my_view_name AS
  SELECT ... FROM ...  ;

However, in Materialize, you can create an index on a view to keep view results incrementally updated in memory within a cluster. That is, with indexed views, you do not recompute the view results each time you access the view in the cluster; queries can access the already up-to-date view results in memory.

CREATE INDEX idx_on_my_view ON my_view_name(...) ;

See Indexes and views for more information.

See also:

• CREATE VIEW for complete syntax information
• CREATE INDEX for complete syntax information

Indexes on views

In Materialize, views can be indexed. Indexes represent query results stored in memory. Creating an index on a view executes the underlying view query and stores the view results in memory within that cluster.

For example, to create an index in the current cluster:

CREATE INDEX idx_on_my_view ON my_view_name(...) ;

You can also explicitly specify the cluster:

CREATE INDEX idx_on_my_view IN CLUSTER active_cluster ON my_view (...);

As new data arrives, the index incrementally updates view results in memory within that cluster. Within the cluster, the in-memory up-to-date results are immediately available and computationally free to query.

See also:

• Indexes
• Optimization
• CREATE INDEX for complete syntax information

Materialized views

In Materialize, a materialized view is a view whose underlying query is executed during the view creation. The view results are persisted in durable storage, and, as new data arrives, incrementally updated.

CREATE MATERIALIZED VIEW my_mat_view_name AS
  SELECT ... FROM ...  ;

Materialized views can be referenced across clusters.

You can also index a materialized view to maintain the results in memory within the cluster. This enables queries within the cluster to use the index to access view results from memory.

See also:

• CREATE MATERIALIZED VIEW for complete syntax information

Indexes on materialized views

In Materalize, materialized views can be indexed.

CREATE INDEX idx_on_my_view ON my_mat_view_name(...) ;

Because materialized views maintain the up-to-date results in durable storage, indexes on materialized views serve up-to-date results without themselves performing the incremental computation. However, unlike materialized views that are accessible across clusters, indexes are accessible only within the cluster.

See also:

• Indexes
• Optimization
• CREATE INDEX for complete syntax information

Indexed views vs. materialized views

In Materialize, both indexes on views and materialized views incrementally update the view results when Materialize ingests new data. Whereas materialized views persist the view results in durable storage and can be accessed across clusters, indexes on views compute and store view results in memory within a single cluster.

Maintaining a materialized view in durable storage has resource and latency costs that should be carefully considered depending on the main usage of the view, while maintaining an index has memory costs.

Some general guidelines for usage patterns include:

General information

• Views can be referenced across clusters.

• Materialized views can be referenced across clusters.

• Indexes are local to a cluster.

• Views can be monotonic; that is, views can be recognized as append-only.

• Materialized views are not monotonic; that is, materialized views cannot be recognized as append-only.