)];
```
See [`CREATE INDEX`](/sql/create-index/) for the syntax details.
### Indexes on sources, tables, and subsources
> **Note:** In practice, you may find that you rarely need to index a source and its tables
> or subsources without performing some transformation using a view, etc.
In Materialize, you can create indexes on [sources, tables, or
subsources](/fundamentals/concepts/sources/) to maintain up-to-date data in the memory of
the cluster where you create the index. This can help improve [query
performance](#indexes-and-query-optimizations), for example when [using
joins](/transform-data/optimization/#join) in your transformation. However, in
practice, you may find that you rarely need to index these objects directly.
```mzsql
CREATE INDEX idx_on_my_source_table ON my_source_table(...);
```
### Indexes on views
In Materialize, you can [create indexes](/sql/create-index/) on a
[view](/fundamentals/concepts/views/#views "query saved under a name") to maintain
**up-to-date view results in memory** within the [cluster](/fundamentals/concepts/clusters/)
where you create the index.
- To create the index in the current active cluster (you can use the `SET
CLUSTER` command to change the active cluster):
```mzsql
CREATE INDEX idx_on_my_view ON my_view_name(...);
```
- To create the index in a specified cluster:
```mzsql
CREATE INDEX idx_on_my_view IN CLUSTER serving_cluster ON my_view_name(...);
```
During the index creation, the view is executed and the view results are stored
in memory within the cluster. **As new data arrives**, the index **incrementally
updates** the view results in memory.
Querying a view from a cluster where the view is indexed is **fast** because
the results are already computed and are served from memory. Querying a view
from a cluster where the view isn't indexed requires executing the view each
time you query it.
### Indexes on materialized views
In Materialize, materialized view results are stored in durable storage and
**incrementally updated** as new data arrives. [Indexing](/sql/create-index/) a
materialized view makes the already up-to-date view results available **in
memory** within the [cluster](/fundamentals/concepts/clusters/) where you create the index.
That is, indexes on materialized views require no additional computation to keep
results up-to-date.
> **Note:** A materialized view can be queried from any cluster whereas its indexed results
> are available only within the cluster where you create the index. Querying a
> materialized view from any cluster, whether the materialized view is indexed or
> not, is fast because the results are already computed. However, querying an
> indexed materialized view from a cluster where the materialized view is indexed
> is faster since the results are served from memory rather than from storage.
- To create the index in the current active cluster (you can use the `SET
CLUSTER` command to change the active cluster):
```mzsql
CREATE INDEX idx_on_my_mat_view ON my_mat_view_name(...);
```
- To create the index in a specified cluster:
```mzsql
CREATE INDEX idx_on_my_mat_view IN CLUSTER serving_cluster ON my_mat_view_name(...);
```
## Properties
### Cluster-local
Indexes are accessible only from their own cluster. Indexed results reside
in the memory of the cluster where the index is created, and a [cluster's
memory](/fundamentals/concepts/clusters/#resource-isolation) cannot be accessed from
another cluster.
As
such, references to the indexed object from a different cluster cannot use the
index.
### Data distribution and ordering
The index data is distributed across the cluster's
workers by a hash of the key, which spreads the maintenance and lookup work
across the cluster.
Within each worker, index keys are ordered by their internal representation
(the encoded key's length, then its bytes), not by the data types' natural
ordering.
### Serving ad-hoc queries
Within a cluster, all ad-hoc queries that reference an indexed object read from
the index, regardless of whether the index is optimized for the query. This
includes queries that do not specify a `WHERE` condition on the index key.
Because the indexed results are already up-to-date and in memory, reading from
an index avoids recomputing the results.
- **Point lookups**: For queries that specify an equality condition on the full
index key, Materialize can perform a point lookup, reading only the matching
records from the index. Point lookups are the most efficient use of an index.
See [Point lookups](#point-lookups) for the exact requirements.
- **Index scans**: Otherwise, Materialize scans the index. Although the indexed
results are already up-to-date and in memory, a full index scan must examine
the indexed results and is less efficient than a point lookup. The performance
of full index scans degrades with data volume.
### Index use by objects
Within a cluster, an index can be used not only by ad-hoc queries but also
by other indexes and materialized views. For an index or materialized view
to use another index, however, that index must exist when the dependent
object is created. That is:
-
When you create an index or a materialized view, Materialize plans how
to compute its results at creation time. As part of planning, Materialize
checks whether it can reuse an existing index in the same
cluster.
-
Because the plan is bound at creation time, creation order matters. An
index or materialized view that is already running will not adopt an
index created afterward. To have an existing index or materialized view
use a newer index, drop and recreate the existing object. However,
recreating an index or a materialized view triggers
hydration.
Ad-hoc queries, by contrast, are planned at query time. They can use any
index that exists in the cluster when the query runs.
> **Note:** Reusing an index saves computation since the dependent objects read the
> index's maintained results instead of recomputing them from the base data.
> However, each new index has costs related to cluster memory and ongoing
> maintenance, especially indexes on regular views.
To inspect index reuse and dependencies:
- To check whether a new index would reuse an existing index before creating
it, use [`EXPLAIN CREATE INDEX`](/sql/explain-plan/).
- To find which indexes and materialized views use an index, query
[`mz_internal.mz_materialization_dependencies`](/sql/system-catalog/mz_internal/#mz_materialization_dependencies).
### Limitations
Materialize indexes are not optimized for:
-
Ordered access, including:
-
Range queries, that is, queries using >, >=, <, <=, or BETWEEN
(e.g., WHERE quantity > 10, WHERE price >= 10 AND price <= 50, and
WHERE quantity BETWEEN 10 AND 20).
-
Queries that use ORDER BY on the index key.
-
Lookups on a prefix of a multi-column index key. For example, an index
with the key (a, b) is not optimized for a query that specifies an
equality condition on a but not on b.
-
Lookups that do not match the exact index key expression. For example,
for an index with the key lower(a), an equality condition on a does
not match the index key; the query must specify an equality condition on
lower(a) for a point lookup.
-
GROUP BY aggregations. An index on the grouping key does not reduce the work of computing the aggregation: Materialize reads the full index and maintains the aggregation separately.
## Point lookups vs index scans
### Point lookups
Point lookups read just the matching records from the index and are the most
efficient use of an index. Materialize performs a point lookup if the query's
`WHERE` clause:
- Specifies equality (`=` or `IN`) condition and **only** equality conditions on
**all** the indexed fields. The equality conditions must specify the **exact**
index key expression (including type) for point lookups. For example:
- If the index is on `round(quantity)`, the query must specify equality
condition on `round(quantity)` (and not just `quantity`) for Materialize to
perform a point lookup.
- If the index is on `quantity * price`, the query must specify equality
condition on `quantity * price` (and not `price * quantity`) for Materialize
to perform a point lookup.
- If the index is on the `quantity` field which is an integer, the query must
specify an equality condition on `quantity` with a value that is an integer.
- Only uses `AND` (conjunction) to combine conditions for **different** fields.
For queries whose `WHERE` clause meets the point lookup criteria and includes
conditions on additional fields (also using `AND` conjunction), Materialize
performs a point lookup on the index keys and then filters the results using the
additional conditions on the non-indexed fields.
### Index scans
For queries that do not meet the [point lookup criteria](#point-lookups),
Materialize performs a full index scan (including for range queries). That is,
Materialize performs a full index scan if the `WHERE` clause:
- Does not specify **all** the indexed fields.
- Does not specify only equality conditions on the index fields or specifies an
equality condition that specifies a different value type than the index key
type.
- Uses `OR` (disjunction) to combine conditions for **different** fields.
Full index scans are less efficient than point lookups. The performance of full
index scans will degrade with data volume; i.e., as you get more data, full
scans will get slower.
### Examples
Within a cluster, indexes can serve queries that reference an indexed object,
regardless of whether the index is optimized for the query.
Consider the following index on the `orders_view`:
```mzsql
CREATE INDEX idx_orders_view_qty ON orders_view (quantity);
```
Materialize can use the index to serve various queries on the `orders_view`
(and not just queries that specify conditions on `orders_view.quantity`). For
example:
```mzsql
SELECT * FROM orders_view; -- scans the index
SELECT * FROM orders_view WHERE status = 'shipped'; -- scans the index
SELECT * FROM orders_view WHERE quantity = 10; -- point lookup on the index
```
For the queries that do not satisfy the [point-lookup
conditions](#point-lookups), Materialize scans the index.
The following table shows various queries and whether Materialize performs a
point lookup or an index scan.
| Query | Index Usage |
| --- | --- |
| SELECT * FROM orders_view;
SELECT * FROM orders_view WHERE quantity = 10;
SELECT * FROM orders_view WHERE quantity IN (10, 20);
SELECT * FROM orders_view WHERE quantity = 10 OR quantity = 20;
OR to combine conditions on the same field. |
| SELECT * FROM orders_view WHERE quantity = 10 AND price = 5.00;
quantity, then filter on price. |
| SELECT * FROM orders_view WHERE (quantity, price) = (10, 5.00);
quantity, then filter on price. |
| SELECT * FROM orders_view WHERE quantity = 10 OR price = 5.00;
OR to combine conditions on different fields. |
| SELECT * FROM orders_view WHERE quantity <= 10;
SELECT * FROM orders_view WHERE round(quantity) = 20;
-- Assume quantity is an integer SELECT * FROM orders_view WHERE quantity = 'hello'; SELECT * FROM orders_view WHERE quantity::TEXT = 'hello';
quantity field in orders_view is an integer. In the first query, the quantity is implicitly cast to text. In the second query, the quantity is explicitly cast to text. |
Consider that the view has an index on the `quantity` and `price` fields
instead of an index on the `quantity` field:
```mzsql
DROP INDEX idx_orders_view_qty;
CREATE INDEX idx_orders_view_qty_price on orders_view (quantity, price);
```
| Query | Index Usage |
| --- | --- |
| SELECT * FROM orders_view;
SELECT * FROM orders_view WHERE quantity = 10;
SELECT * FROM orders_view WHERE quantity = 10 AND price = 2.50;
SELECT * FROM orders_view WHERE quantity = 10 OR price = 2.50;
OR to combine conditions on different fields. |
| SELECT * FROM orders_view WHERE quantity = 10 AND (price = 2.50 OR price = 3.00);
OR to combine conditions on same field and AND to combine conditions on different fields. |
| SELECT * FROM orders_view WHERE quantity = 10 AND price = 2.50 AND item = 'cupcake';
quantity and price, then filter on item. |
| SELECT * FROM orders_view WHERE quantity = 10 AND price = 2.50 OR item = 'cupcake';
OR to combine conditions on different fields. |
## Usage
### Indexes on views vs. materialized views
In Materialize, both [indexes](/fundamentals/concepts/indexes) on views and [materialized
views](/fundamentals/concepts/views/#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:
| Usage Pattern | General Guideline |
|--------------------------------------------------------------------------------|--------------------|
| View results are accessed from a single cluster only;
such as in a 1-cluster or a 2-cluster architecture. | View with an [index](/sql/create-index) |
| View used as a building block for stacked views; i.e., views not used to serve results. | View |
| View results are accessed across [clusters](/fundamentals/concepts/clusters);
such as in a 3-cluster architecture. | Materialized view (in the transform cluster)
Index on the materialized view (in the serving cluster) |
| Use with a [sink](/export-data/) or a [`SUBSCRIBE`](/sql/subscribe) operation | Materialized view |
| Use with [temporal filters](/transform-data/patterns/temporal-filters/) | Materialized view |
For example:
**3-tier architecture:**
In a [3-tier
architecture](/clusters/operational-guidelines/#three-tier-architecture)
where queries are served from a cluster different from the compute/transform
cluster that maintains the view results:
- Use materialized view(s) in the compute/transform cluster for the query
results that will be served.
If you are using stacked views (i.e., views whose definition depends
on other views) to reduce SQL complexity, generally, only the topmost
view (i.e., the view whose results will be served) should be a
materialized view. The underlying views that do not serve results do not
need to be materialized.
- Index the materialized view in the serving cluster(s) to serve the results
from memory.
**2-tier architecture:**
In a [2-tier
architecture](/clusters/operational-guidelines/appendix-alternative-cluster-architectures/#two-tier-architecture)
where queries are served from the same cluster that performs the
compute/transform operations:
- Use view(s) in the shared cluster.
- Index the view(s) to incrementally update the view results and serve the
results from memory.
> **Tip:** Except for when used with a [sink](/export-data/),
> [subscribe](/sql/subscribe/), or [temporal
> filters](/transform-data/patterns/temporal-filters/), avoid creating
> materialized views on a shared cluster used for both compute/transform
> operations and serving queries. Use indexed views instead.
**1-tier architecture:**
In a [1-tier
architecture](/clusters/operational-guidelines/appendix-alternative-cluster-architectures/#one-tier-architecture)
where queries are served from the same cluster that performs the
compute/transform operations:
- Use view(s) in the shared cluster.
- Index the view(s) to incrementally update the view results and serve the
results from memory.
> **Tip:** Except for when used with a [sink](/export-data/),
> [subscribe](/sql/subscribe/), or [temporal
> filters](/transform-data/patterns/temporal-filters/), avoid creating
> materialized views on a shared cluster used for both compute/transform
> operations and serving queries. Use indexed views instead.
### Indexes and query optimizations
By making up-to-date results available in memory, indexes can help [optimize
query performance](/transform-data/optimization/), such as:
- Provide faster sequential access than unindexed data.
- Provide fast random access for lookup queries (i.e., selecting individual
keys).
Specific instances where indexes can be useful to improve performance include:
- When used in ad-hoc queries.
- When used by multiple queries within the same cluster.
- When used to enable [delta
joins](/transform-data/optimization/#optimize-multi-way-joins-with-delta-joins).
For more information, see [Optimization](/transform-data/optimization).
### Best practices
Before creating an index, consider the following:
- If you create stacked views (i.e., views that depend on other views) to
reduce SQL complexity, we recommend that you create an index **only** on the
view that will serve results, taking into account the expected data access
patterns.
- Materialize can reuse indexes across queries that concurrently access the same
data in memory, which reduces redundancy and resource utilization per query.
In particular, this means that joins do **not** need to store data in memory
multiple times.
- For queries that have no supporting indexes, Materialize uses the same
mechanics used by indexes to optimize computations. However, since this
underlying work is discarded after each query run, take into account the
expected data access patterns to determine if you need to index or not.
## Related pages
- [Optimization](/transform-data/optimization)
- [Views](/fundamentals/concepts/views)
- [`CREATE INDEX`](/sql/create-index)
---
## Reaction Time, Freshness, and Query Latency
In operational data systems, the performance and responsiveness of queries depend not only on how fast a query runs, but also on how current the underlying data is. This page introduces three foundational concepts for evaluating and understanding system responsiveness in Materialize:
* **Freshness**: the time it takes for a change in an upstream system to become visible in the results of a query.
* **Query latency**: the time it takes to compute and return the result of a SQL query once the data is available in the system.
* **Reaction time**: the total delay from data change to observable result.
Together, these concepts form the basis for understanding how Materialize enables timely, accurate insights across operational and analytical workloads.
---
## Freshness
**Freshness** measures the time it takes for a change in an upstream system to become visible in the results of a query. In other words, it captures the end-to-end latency between when data is produced and when it becomes part of the transformed, queryable state.
| System | Performance | Explanation |
| -------------- | ------------ | -------------------------- |
| OLTP Database | Excellent | Freshness is effectively zero. Queries run directly against the source of truth, and changes are visible immediately. |
| Data Warehouse | Poor (stale) | Freshness is often poor due to scheduled batch ingestion. Changes may take minutes to hours to propagate. |
| Materialize | Excellent | Freshness is low, typically within milliseconds to a few seconds, due to continuous ingestion and incremental view maintenance. |
### Monitoring Freshness
You can monitor data freshness in Materialize by querying wallclock lag measurements from the [`mz_internal.mz_wallclock_global_lag`](/sql/system-catalog/mz_internal/#mz_wallclock_global_lag) system catalog view.
Wallclock lag indicates how far behind real-world wall-clock time your data objects are, helping you understand freshness across your materialized views, indexes, and sources.
```sql
SELECT object_id, lag
FROM mz_internal.mz_wallclock_global_lag;
```
---
## Query Latency
**Query latency** refers to the time it takes to compute and return the result of a SQL query once the data is available in the system. It is affected by the system's execution model, indexing strategies, and the complexity of the query itself.
| System | Performance | Explanation |
| -------------- | ------------ | -------------------------- |
| OLTP Database | Poor (slow) | Optimized for transactional workloads and point lookups. Complex analytical queries involving joins, filters, and aggregations tend to exhibit poor query latency. |
| Data Warehouse | Excellent | Designed for analytical processing, and generally provide excellent query latency even for complex queries over large datasets. |
| Materialize | Excellent | Maintains low query latency by incrementally updating and indexing the results of complex views. Queries that read from indexed views typically return results in milliseconds. |
---
## Reaction Time
**Reaction time** is defined as the sum of freshness and query latency. It captures the total time from when a data change occurs upstream to when a downstream consumer can query and act on that change.
```
reaction time = freshness + query latency
```
This is the most comprehensive measure of system responsiveness and is particularly relevant for applications that depend on timely and accurate decision-making.
| System | Reaction Time |
| -------------- | ------------- |
| OLTP Database | High |
| Data Warehouse | High |
| Materialize | Low |
## Example
Consider an e-commerce application that needs to monitor order fulfillment rates in real time. This requires both timely access to new orders and the ability to compute aggregates across multiple related tables.
Let’s compare how this plays out across three systems:
| **System** | **Data Freshness** | **Query Latency** |
|-------------------|-------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------|
| **OLTP System** | The order and fulfillment data is always current, as queries run directly against the transactional system. | Computing fulfillment rates involves joins and aggregations over multiple tables, which transactional databases are not optimized for. Queries may be slow or resource-intensive. |
| **Data Warehouse**| The data is typically ingested in batches, so it may lag behind by minutes or hours. Freshness depends on the ETL schedule. | Analytical queries, including aggregations and joins, are well-optimized and typically return quickly. |
| **Materialize** | Updates stream in continuously from the operational database. Materialize incrementally maintains the fulfillment rate. | Because the computation is performed ahead of time and maintained in an indexed view, queries return promptly—even for complex logic. |
## Design Implications
Optimizing reaction time is essential for building systems that depend on timely decision-making, accurate reporting, and responsive user experiences. Materialize enables this by ensuring:
* **Low freshness lag**: Data changes are ingested and transformed in near real time.
* **Low query latency**: Results are precomputed and maintained through indexed views.
* **Minimal operational complexity**: Users define transformations using standard SQL. Materialize handles the complexity of incremental view maintenance internally.
This architecture removes the traditional trade-off between fast queries and fresh data. Unlike OLTP systems and data warehouses, which optimize for one at the expense of the other, Materialize provides both simultaneously.
---
## Summary
| Concept | Definition | How Materialize Optimizes It |
| ------------- | --------------------------------------------- | ------------------------------------------------ |
| Freshness | Time from upstream change to queryability | Streaming ingestion + incremental transformation |
| Query Latency | Time to execute and return results of a query | Indexes + real-time maintained views |
| Reaction Time | Total time from data change to insight | Combines low freshness and low query latency |
Materialize is built to minimize all three. The result is a system that delivers fast, consistent answers over fresh data, enabling use cases that were previously too costly or complex to implement.
---
## Sinks
## Overview
Sinks are the inverse of sources and represent a connection to an external
stream where Materialize outputs data. You can sink data from a **materialized**
view, a source, or a table.
## Sink methods
To create a sink, you can:
| Method | External system | Guide(s) or Example(s) |
| --- | --- | --- |
| Use COPY TO command | Amazon S3 or S3-compatible storage | |
| Use Census as an intermediate step | Census supported destinations | |
| Use COPY TO S3 or S3-compatible storage as an intermediate step | Snowflake and other systems that can read from S3 | |
| Use a native connector | Kafka/Redpanda | |
| Use the Kafka sink + Kafka Connect | Elasticsearch | |
| Use the Kafka sink + Kafka Connect | OpenSearch | |
| Use the Kafka sink + mz-tpuf-sink | turbopuffer | |
| Use a native connector | Apache Iceberg hosted on AWS S3 Tables | |
| Use SUBSCRIBE | Various | |
## Clusters and sinks
Avoid putting sinks on the same cluster that hosts sources.
See also [Operational guidelines](/clusters/operational-guidelines/).
## Hydration considerations
During creation, Kafka sinks need to load an entire snapshot of the data in
memory.
## Related pages
- [`CREATE SINK`](/sql/create-sink)
---
## Snapshotting
Snapshotting is the initial sync of a table's data. It reads from the upstream
system and writes the data into Materialize's storage. The initial snapshot is
committed to storage atomically, with all records assigned the same ingestion
timestamp.
## When snapshotting occurs
When snapshotting occurs depends on the syntax.
- With the legacy [`CREATE SOURCE ... FOR `](/sql/create-source/#legacy-syntax), you run a single
statement to create both the source and the tables that ingest data.
Snapshotting begins when you run the statement. For an existing source, the
legacy [`ALTER SOURCE ... ADD SUBSOURCE`](/sql/alter-source/) starts the
snapshotting for the added table.
- With the source-versioning syntax, you create the source and its tables
separately using [`CREATE SOURCE ...`](/sql/create-source/#new-syntax) and
[`CREATE TABLE ... FROM SOURCE`](/sql/create-table/). Snapshotting begins when
you run `CREATE TABLE ... FROM SOURCE`.
## Snapshot duration
Snapshot duration depends on:
- Volume of upstream data
- Size of the source's cluster
- Upstream capacity to serve the read, on top of its normal workload
- Network path between the upstream system and Materialize
In cloud environments, an instance's network and disk throughput are typically
capped by its instance type, so a busy or throughput-limited upstream, or a
constrained network path, can be the bottleneck regardless of the source
cluster's size.
For **upsert** sources, snapshotting can be especially resource-intensive
(compared to append-only), and large upsert sources can take hours to snapshot.
### Parallelism
Materialize can parallelize snapshotting across the workers of the cluster
hosting the source.
- **PostgreSQL sources** are parallelized by table, i.e., different tables
are read concurrently by different workers. On PostgreSQL 14 and later,
Materialize additionally attempts to partition each table's read across
workers. Tables that cannot be partitioned fall back to a single worker.
- **MySQL sources** are parallelized by table, i.e., different tables are
read concurrently by different workers. For tables that meet certain
requirements, Materialize can additionally partition the table's read
across workers. See [MySQL snapshot
parallelism](/ingest-data/mysql/snapshot-parallelism/).
- **Kafka sources** are parallelized by topic partition, with partitions
distributed across workers, so parallelism is bounded by the topic's
partition count.
- **SQL Server sources** are not parallelized: a single worker reads all
tables.
The degree of snapshot parallelism depends on the number of workers. A
cluster's [size](/sql/create-cluster/#available-sizes) determines its number
of workers, so a larger cluster can shorten the snapshot, to the extent the
work parallelizes and the upstream database keeps up. The volume read from
the upstream database is unchanged, it is compressed into a shorter window
of more concurrent queries and connections. To determine whether
snapshotting is overloading the upstream database, and for ways to mitigate
the load, see [Is the upstream database
overloaded?](/ingest-data/troubleshooting/#is-the-upstream-database-overloaded)
## Queries during snapshotting
Queries on a table that is snapshotting are blocked until its snapshot
completes.
- With the legacy `CREATE` syntax:
- None of the subsources created as part of `CREATE SOURCE ... FOR ...` are
queryable until they have all finished snapshotting.
- When altering a source to add a new subsource (`ALTER SOURCE ... ADD
SUBSOURCE`), only the new subsource snapshots. The source's other subsources
remain queryable. **However**, ingestion for these subsources is temporarily
blocked, so they stop advancing until the snapshot completes.
- With the source-versioning `CREATE TABLE FROM SOURCE` syntax:
- None of the tables created within a [transaction
block](/sql/begin/#ddl-only-transactions) are queryable until all their
snapshots complete.
- When you create new tables from a source that already has tables, only the
new tables snapshot. The source's existing tables remain queryable.
**However**, ingestion for the existing tables is temporarily blocked, so
they stop advancing until the snapshots for the new tables complete.
## Impact on upstream system
Snapshotting has the following upstream impacts:
- **Read load.** Snapshotting puts read, CPU, and network load on the upstream
system. The total load is proportional to the volume of data being
snapshotted, while the source cluster's [parallelism](#parallelism) affects
the peak load: more workers compress the reads into a shorter window.
- **Change-log retention for CDC database sources.** When ingesting data from
CDC database sources (PostgreSQL, MySQL, SQL Server), the upstream system must
retain its change-log data until Materialize consumes it. During the initial
snapshot, changes accumulate from the source's starting position until the
snapshot completes and Materialize has consumed the accumulated changes. A
stalled or long-running snapshot can therefore increase disk usage on the
upstream database.
## Related pages
- [Ingest data](/ingest-data/)
- [Sources](/fundamentals/concepts/sources/)
- [Troubleshooting data ingestion](/ingest-data/troubleshooting/)
---
## Sources
## Overview
A source in Materialize represents an external data source. More concretely, it
specifies the connection and the ingestion configuration to use for a particular
external data source (e.g., PostgreSQL, Kafka). For those familiar with
PostgreSQL's foreign servers and foreign tables, a source is like a foreign
server, and the tables (or subsources) created from the source are like foreign
tables.
## Supported external systems
Materialize supports ingesting data from the following external systems:
| Type | External system |
|------|-----------------|
| **Databases (CDC): native connectors** | [PostgreSQL](/ingest-data/postgres/)
[MySQL](/ingest-data/mysql/)
[SQL Server](/ingest-data/sql-server/) |
| **Databases (CDC): via the Kafka connector** | [CockroachDB](/ingest-data/cdc-cockroachdb/) (using changefeeds)
[MongoDB](/ingest-data/mongodb/) (using Debezium) |
| **Message brokers** | [Kafka](/ingest-data/kafka/)
[Redpanda](/sql/create-source/kafka) |
| **Webhooks** | [Amazon EventBridge](/ingest-data/webhooks/amazon-eventbridge/)
[Segment](/ingest-data/webhooks/segment/)
[HubSpot](/ingest-data/webhooks/hubspot/)
[RudderStack](/ingest-data/webhooks/rudderstack/)
[SnowcatCloud](/ingest-data/webhooks/snowcatcloud/)
[Stripe](/ingest-data/webhooks/stripe/)|
## Creating a source
### Prerequisites
Before creating a source in Materialize, you must ensure that the external data
source is properly configured and accessible so that Materialize can establish a
connection and ingest its data. The exact configuration depends on the type of
data source.
### CREATE SOURCE syntax
To create a source, you use the [`CREATE SOURCE`](/sql/create-source/) syntax.
There are two versions of the syntax:
- *Recommended.* The new [`CREATE SOURCE`](/sql/create-source/#new-syntax)
syntax, used with [`CREATE TABLE ... FROM SOURCE`](/sql/create-table/). The
new syntax allows Materialize to handle certain upstream schema changes,
specifically adding or dropping columns, **without** downtime.
- The legacy [`CREATE SOURCE ... FOR `](/sql/create-source/#legacy-syntax) syntax, which
creates a source and its subsources. *Subsource* is the legacy term for the
read-only tables created from a source. With the legacy `CREATE SOURCE ...
FOR ...` syntax, the subsources are automatically created when the `CREATE
SOURCE ...` command is issued.
### Tables and subsources
A source makes external data available in Materialize through:
- The [tables](/sql/create-table/) created from it, when using the new
`CREATE SOURCE` syntax.
- The subsources, when using the legacy `CREATE SOURCE` syntax.
Both the tables and subsources created from a source are **read-only**.
Materialize populates them by ingesting changes from the upstream system, and
you cannot insert, update, or delete their data directly.
## Snapshotting
When you create a table from a source (or, with the legacy syntax, when the
subsources are created), Materialize [snapshots](/fundamentals/concepts/snapshotting/) the
data currently available in the upstream system for that table.
Queries on a table that is snapshotting are blocked until its snapshot
completes.
- With the legacy `CREATE` syntax:
- None of the subsources created as part of `CREATE SOURCE ... FOR ...` are
queryable until they have all finished snapshotting.
- When altering a source to add a new subsource (`ALTER SOURCE ... ADD
SUBSOURCE`), only the new subsource snapshots. The source's other subsources
remain queryable. **However**, ingestion for these subsources is temporarily
blocked, so they stop advancing until the snapshot completes.
- With the source-versioning `CREATE TABLE FROM SOURCE` syntax:
- None of the tables created within a [transaction
block](/sql/begin/#ddl-only-transactions) are queryable until all their
snapshots complete.
- When you create new tables from a source that already has tables, only the
new tables snapshot. The source's existing tables remain queryable.
**However**, ingestion for the existing tables is temporarily blocked, so
they stop advancing until the snapshots for the new tables complete.
See [Snapshotting](/fundamentals/concepts/snapshotting/) for more information.
## Hydration
Hydration is the reconstruction of an object's in-memory state by reading
from Materialize's storage layer and existing indexes; hydration does not
read from the upstream system.
- For Kafka upsert sources, their associated read-only tables (or
subsources if using the legacy syntax) rebuild their internal upsert
index from storage on replica (re)start or cluster resize.
- For other sources, the hydration process is negligible or not applicable.
See [Hydration](/fundamentals/concepts/hydration/) for more information.
## Sources and clusters
Sources require compute resources in Materialize. That is, sources must be
associated with a [cluster](/fundamentals/concepts/clusters/). If possible, dedicate a
cluster just for sources.
See also [Operational guidelines](/clusters/operational-guidelines/).
## Related pages
- [`CREATE SOURCE`](/sql/create-source)
- [`CREATE TABLE`](/sql/create-table)
- [Snapshotting](/fundamentals/concepts/snapshotting/)
- [Hydration](/fundamentals/concepts/hydration/)
---
## Views
## Overview
Views represent queries that are saved under a name for reference. Views provide
a shorthand for the underlying query.
Type |
-----------------------|-------------------
[ **Views** ]( #views ) | Results are recomputed from scratch each time the view is accessed. You can create an **[index](/fundamentals/concepts/indexes/)** on a view to keep its results **incrementally updated** and available **in memory** within a cluster. |
[**Materialized views**](#materialized-views) | Results are persisted in **durable storage** and **incrementally updated**. You can create an [**index**](/fundamentals/concepts/indexes/) on a materialized view to make the results available in memory within a cluster.
## Views
A view saves a query under a name to provide a shorthand for referencing the
query. Views are not associated with a [cluster](/fundamentals/concepts/clusters/) 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.
```mzsql
CREATE VIEW my_view_name AS
SELECT ... FROM ... ;
```
**However**, in Materialize, you can create an [index](/fundamentals/concepts/indexes/) 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.
```mzsql
CREATE INDEX idx_on_my_view ON my_view_name(...) ;
```
See [Indexes and views](#indexes-on-views) for more information.
See also:
- [`CREATE VIEW`](/sql/create-view) for complete syntax information
- [`CREATE INDEX`](/sql/create-index/) for complete syntax information
### Indexes on views
In Materialize, views can be [indexed](/fundamentals/concepts/indexes/). 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](/fundamentals/concepts/clusters/).
For example, to create an index in the current cluster:
```mzsql
CREATE INDEX idx_on_my_view ON my_view_name(...) ;
```
You can also explicitly specify the cluster:
```mzsql
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](/fundamentals/concepts/clusters/). Within the cluster, the
**in-memory up-to-date** results are immediately available to query.
See also:
- [Indexes](/fundamentals/concepts/indexes)
- [Optimization](/transform-data/optimization)
- [`CREATE INDEX`](/sql/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](/fundamentals/concepts/clusters/).
To create materialized views, use the [`CREATE MATERIALIZED
VIEW`](/sql/create-materialized-view) command:
```mzsql
CREATE MATERIALIZED VIEW my_mat_view_name AS
SELECT ... FROM ... ;
```
See also:
- [`CREATE MATERIALIZED VIEW`](/sql/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.
```mzsql
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.
> **Note:** Querying a materialized view, whether indexed or not, from any cluster is fast
> since the results are already computed. However, querying an indexed
> materialized view within the cluster associated with the index is faster since
> the results are served from memory rather than from storage.
See also:
- [Indexes](/fundamentals/concepts/indexes)
- [Optimization](/transform-data/optimization)
- [`CREATE INDEX`](/sql/create-index/) for complete syntax information
### Updating the materialized view definition
> **Public Preview:** This feature is in public preview.
You can use [`CREATE REPLACEMENT MATERIALIZED
VIEW`](/sql/create-materialized-view/) with [`ALTER MATERIALIZED VIEW ... APPLY
REPLACEMENT`](/sql/alter-materialized-view) to replace materialized views
in-place without recreating dependent objects or incurring downtime.
For a step-by-step tutorial, see [Replace Materialized
Views](/transform-data/updating-materialized-views/replace-materialized-view/).
See also:
- [Choosing an update
strategy](/transform-data/updating-materialized-views/#choosing-an-update-strategy)
## Indexed views vs. materialized views
In Materialize, both [indexes](/fundamentals/concepts/indexes) on views and [materialized
views](/fundamentals/concepts/views/#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:
| Usage Pattern | General Guideline |
|--------------------------------------------------------------------------------|--------------------|
| View results are accessed from a single cluster only;
such as in a 1-cluster or a 2-cluster architecture. | View with an [index](/sql/create-index) |
| View used as a building block for stacked views; i.e., views not used to serve results. | View |
| View results are accessed across [clusters](/fundamentals/concepts/clusters);
such as in a 3-cluster architecture. | Materialized view (in the transform cluster)
Index on the materialized view (in the serving cluster) |
| Use with a [sink](/export-data/) or a [`SUBSCRIBE`](/sql/subscribe) operation | Materialized view |
| Use with [temporal filters](/transform-data/patterns/temporal-filters/) | Materialized view |
For example:
**3-tier architecture:**
In a [3-tier
architecture](/clusters/operational-guidelines/#three-tier-architecture)
where queries are served from a cluster different from the compute/transform
cluster that maintains the view results:
- Use materialized view(s) in the compute/transform cluster for the query
results that will be served.
If you are using stacked views (i.e., views whose definition depends
on other views) to reduce SQL complexity, generally, only the topmost
view (i.e., the view whose results will be served) should be a
materialized view. The underlying views that do not serve results do not
need to be materialized.
- Index the materialized view in the serving cluster(s) to serve the results
from memory.
**2-tier architecture:**
In a [2-tier
architecture](/clusters/operational-guidelines/appendix-alternative-cluster-architectures/#two-tier-architecture)
where queries are served from the same cluster that performs the
compute/transform operations:
- Use view(s) in the shared cluster.
- Index the view(s) to incrementally update the view results and serve the
results from memory.
> **Tip:** Except for when used with a [sink](/export-data/),
> [subscribe](/sql/subscribe/), or [temporal
> filters](/transform-data/patterns/temporal-filters/), avoid creating
> materialized views on a shared cluster used for both compute/transform
> operations and serving queries. Use indexed views instead.
**1-tier architecture:**
In a [1-tier
architecture](/clusters/operational-guidelines/appendix-alternative-cluster-architectures/#one-tier-architecture)
where queries are served from the same cluster that performs the
compute/transform operations:
- Use view(s) in the shared cluster.
- Index the view(s) to incrementally update the view results and serve the
results from memory.
> **Tip:** Except for when used with a [sink](/export-data/),
> [subscribe](/sql/subscribe/), or [temporal
> filters](/transform-data/patterns/temporal-filters/), avoid creating
> materialized views on a shared cluster used for both compute/transform
> operations and serving queries. Use indexed views instead.
## General information
- Views can be referenced across [clusters](/fundamentals/concepts/clusters/).
- Materialized views can be referenced across [clusters](/fundamentals/concepts/clusters/).
- [Indexes](/fundamentals/concepts/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.
