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. Materialized views can be referenced across clusters.

To create materialized views, use the CREATE MATERIALIZED VIEW command:

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

See also:

• CREATE MATERIALIZED VIEW for complete syntax information

Hydration and materialized views

Materialized view undergoes hydration when it is created or when its cluster is restarted. Hydration refers to the reconstruction of in-memory state by reading data from Materialize’s storage layer; hydration does not require reading data from the upstream system.

During hydration, materialized views require memory proportional to both the input and output.

Indexes on materialized views

In Materialize, materialized views can be queried from any cluster. In addition, in Materialize, materialized views can be indexed to make the results available in memory within the cluster associated with the index. For example, in a 3-tier architecture where you have a separate source cluster(s), a separate compute/transform cluster(s) with materialized views, and a separate serving cluster(s), you can create in the serving cluster an index on the materialized views.

CREATE INDEX idx_on_my_view ON my_mat_view_name(...) ;

Because materialized views already maintain the up-to-date results in durable storage, indexes on materialized views can serve up-to-date results without having to perform additional computation.

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.

Some general guidelines for usage patterns include:

For example:

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.