- Posted on
- admin
- No Comments
Materialize Tutorial for Beginners
Imagine you’ve written a SQL query that joins three tables and aggregates a few million rows. It’s correct, and it takes several seconds. Now your product team wants that answer on a live screen, always up to date, with sub-second reads. The usual options are unappealing: run the query constantly and hammer the database, or build a custom stream processing pipeline and keep it in sync with your logic.
Materialize offers a third option. You write the query once as a view, and Materialize keeps its result current as the underlying data changes, doing only the small amount of work each change requires. This tutorial walks through that idea hands-on. You’ll run a sample data stream, build views, add an index, watch results update live with SUBSCRIBE, and use time-based filters. If you’ve read our RisingWave beginner’s guide or compared RisingWave and Flink, Materialize is the other well-known streaming database, and the contrast is instructive.
What Is Materialize?
(cite index=”134-1″>Materialize is a PostgreSQL-compatible streaming database built on Microsoft Research’s Timely Dataflow and Differential Dataflow frameworks, and it maintains strictly serializable, incrementally updated SQL materialized views. Let’s translate that sentence into plain terms.
PostgreSQL-compatible. You connect with psql or standard drivers and write SQL, so there’s no new language to learn.
Streaming database. Data can arrive continuously from sources such as databases and message brokers, and queries over it stay fresh.
Incrementally updated views. When one row changes, Materialize adjusts the result by the effect of that change instead of recomputing from scratch.
Strictly serializable. The strongest consistency guarantee databases offer, which we’ll unpack shortly.
The company’s positioning has shifted over time. (cite index=”134-1″>In 2023 it repositioned from a streaming data warehouse to an operational data warehouse, emphasizing real-time operational workloads. The practical meaning: it’s meant to power things like live dashboards, alerting, and in-app features, not nightly reports.
The Core Idea: Incremental View Maintenance
In most databases, a materialized view is a cached query result that you refresh periodically. Between refreshes, it’s stale. (cite index=”131-1″>Traditional databases typically have only limited support for materialized views, and Materialize’s stated mission is to manage them better.
The engine that makes this possible is worth a quick look. (cite index=”131-1″>Materialize’s engine is built on Timely Dataflow and Differential Dataflow, an incremental, data-parallel compute engine. Differential Dataflow represents data as a stream of changes, inserts and deletes, rather than a sequence of full snapshots. When an input row changes, the engine propagates just that difference through the query plan.
Think of a running total. If a new sale arrives, you don’t re-add every historical sale, you add one number to the existing sum. Differential Dataflow generalizes that trick to joins, aggregations, and more complex SQL.
Strict Serializability in Plain English
Many streaming systems are eventually consistent. Materialize isn’t, and this is one of its headline features. (cite index=”136-1″>Strict serializability means the system behaves as if every event occurred at a specific moment between its start and end, which is the strongest guarantee databases provide. (cite index=”133-1″>It avoids common pitfalls like eventual consistency.
Here’s why you’d care. Suppose a customer’s balance changes and you query a view that depends on it. In an eventually consistent system, you might read the old balance for a while, or see two views disagree with each other. Under strict serializability, results are consistent with each other and reflect your writes, which makes the system far easier to reason about when real decisions, like approving a payment, depend on the answer.
Key Concepts You Need
Materialize has a small vocabulary. Learn these six and the rest of the documentation becomes much easier.
Sources. Where data comes from. They can be databases through CDC, Kafka-style brokers, or built-in load generators for testing.
Views. A saved query. A plain view doesn’t store results by itself, it’s just a name for a query.
Materialized views. (cite index=”133-1″>Indexed views and materialized views both incrementally maintain the results of views. A materialized view stores its results durably and updates them continuously.
Indexes. (cite index=”133-1″>Indexes represent query results stored in memory. Creating an index on a view makes its results available for fast, always-fresh reads.
Clusters. (cite index=”133-1″>Clusters are isolated pools of compute resources for sources, sinks, indexes, materialized views, and ad hoc queries. They let you separate workloads so a heavy job doesn’t slow an interactive one.
Arrangements. (cite index=”133-1″>Arrangements are the in-memory data structures that maintain indexes and materialized views. You rarely touch them directly, but they explain why indexes consume memory.
One more you’ll use constantly is SUBSCRIBE, which streams changes from a query to your client as they happen.
Ways to Run Materialize
You have a few options, and the right one depends on what you want to learn.
- Materialize Cloud, the managed service, which is the full experience.
- Self-Managed, which you install on your own infrastructure. (cite index=”124-1″>Materialize announced Self-Managed v26 as a new release.
- The Materialize Emulator, a Docker image for local development. (cite index=”118-1″>The documentation notes that the Emulator does not provide the full experience of using Materialize, so treat it as a learning and testing tool.
On licensing, an older database catalog entry lists Materialize under the Business Source License, (cite index=”128-1″>with each version converting to Apache 2.0 four years after its release. Licensing terms change, so check the current license page before relying on that for a decision.
This tutorial uses the Emulator so you can follow along on your own machine, and every SQL statement works the same in the cloud console.
Hands-On Tutorial
Step 1: Start Materialize and Connect
Pull and run the Emulator image:
docker run -d --name materialized \
-p 6874:6874 -p 6875:6875 \
materialize/materialized:latest
Port flags and options can change between releases, so if this doesn’t work, check the Emulator page in the docs. When it’s up, connect with psql. (cite index=”122-1″>Materialize listens on port 6875, and you connect with a standard Postgres connection string:
psql "postgresql://materialize@localhost:6875/materialize"
You can also open the web console in your browser if you mapped its port. (cite index=”118-1″>By default you work in the quickstart cluster, in a database named materialize.
Step 2: Create a Schema and a Data Source
Keep things tidy with a dedicated schema:
CREATE SCHEMA IF NOT EXISTS qck;
SET search_path TO qck;
Now create a source. Materialize ships with sample data generators, so you don’t need Kafka. (cite index=”118-1″>The quickstart uses the sample Auction load generator, which simulates an auction site.
CREATE SOURCE auction_house
FROM LOAD GENERATOR AUCTION (TICK INTERVAL '1s', AS OF 100000);
(cite index=”126-1″>The tick interval tells the generator to emit new data every second. The AS OF clause starts the stream at a point that already has history, so there’s data to query immediately. Next, expose the generated data as tables. Current documentation shows doing this inside a transaction:
BEGIN;
CREATE TABLE auctions FROM SOURCE auction_house (REFERENCE auctions);
CREATE TABLE bids FROM SOURCE auction_house (REFERENCE bids);
COMMIT;
The exact syntax for this step has evolved across versions, so if you hit an error, compare against the current quickstart page.
Step 3: Look at the Raw Data
SELECT * FROM auctions LIMIT 5;
SELECT * FROM bids LIMIT 5;
SELECT COUNT(*) FROM bids;
Run the count twice a few seconds apart. It grows, because the generator keeps adding bids.
Step 4: Create a View
We want to know who won each auction, meaning the highest bid placed before the auction ended. Here’s the view:
CREATE VIEW winning_bids AS
SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
FROM auctions AS a
JOIN bids AS b
ON a.id = b.auction_id
WHERE b.bid_time < a.end_time AND mz_now() >= a.end_time
ORDER BY a.id,
b.amount DESC,
b.bid_time,
b.buyer;
Two things are worth noticing. DISTINCT ON picks one row per auction, the highest bid. And mz_now() appears in the WHERE clause. We’ll come back to why that function exists, because it’s one of Materialize’s most important ideas.
At this point winning_bids is just a saved query. Nothing is being computed continuously yet.
Step 5: Add an Index to Make It Live
Now the key step. (cite index=”126-1″>In Materialize, you can create indexes on views to provide always fresh, up-to-date view results in memory within a cluster.
CREATE INDEX wins_by_item ON winning_bids (item);
Materialize now builds the result, holds it in memory, and keeps it current. Query it:
SELECT item, COUNT(*) AS wins
FROM winning_bids
GROUP BY item
ORDER BY wins DESC;
Run it again after a few seconds. As auctions end and new winners appear, the counts change, and the read is fast because the data is already computed and in memory. You didn’t schedule a refresh, and you didn’t write a pipeline.
Step 6: Create a Materialized View
An index stores results in memory inside one cluster. A materialized view stores them durably and can be read from anywhere. Let’s make one:
CREATE MATERIALIZED VIEW wins_per_item AS
SELECT item, COUNT(*) AS wins
FROM winning_bids
GROUP BY item;
SELECT * FROM wins_per_item ORDER BY wins DESC;
Both objects maintain results incrementally. The difference is where results live and how they’re used. We’ll compare them in a moment.
Step 7: Watch Changes Live With SUBSCRIBE
Querying repeatedly works, but Materialize can push changes to you instead. (cite index=”126-1″>SUBSCRIBE returns data from a source, table, view, or materialized view as it changes. In psql:
COPY (SUBSCRIBE (SELECT * FROM wins_per_item)) TO STDOUT;
Rows start streaming in. Each output includes a timestamp, a diff showing whether a row was added or removed, and the row’s values. When a count changes from 4 to 5, you’ll see the old row retracted and the new one inserted. Press Ctrl+C to stop.
This is how an application would learn about changes without polling: it subscribes to a query and reacts as updates arrive. Think of live dashboards, notifications, or cache invalidation.
Step 8: Time-Based Views With mz_now()
Many real-time questions involve a window of time, such as “events in the last five minutes.” In most databases you’d write now() in a filter, but a query using now() can’t be maintained, since the answer changes as the clock ticks, not because data changed.
(cite index=”136-1″>Materialize provides mz_now(), which returns the transaction time of your command, and queries containing mz_now() can be materialized, unlike queries containing now(). (cite index=”136-1″>Temporal filters allow mz_now() in WHERE clauses because Materialize can invert the clause and work out exactly when results will change.
Try it with a small table you control:
CREATE TABLE events (
id INT,
received_at TIMESTAMPTZ
);
CREATE VIEW last_minute AS
SELECT * FROM events
WHERE mz_now() < received_at + INTERVAL '1 minute';
CREATE INDEX last_minute_idx ON last_minute (id);
INSERT INTO events VALUES (1, now()), (2, now());
SELECT * FROM last_minute;
You’ll see both rows. Wait a bit more than a minute and run the final SELECT again. The rows are gone, though nobody deleted them and nothing ran on a schedule. Materialize knew in advance when each row would expire and retracted it at that moment. A similar pattern appears in published examples of fraud detection, where a view filters to events received within a recent time window.
Step 9: Inspect Clusters and Objects
A few commands show what you’ve built:
SHOW CLUSTERS;
SHOW SOURCES;
SHOW VIEWS;
SHOW MATERIALIZED VIEWS;
SHOW INDEXES;
SHOW CLUSTERS is especially worth a look. You’ve been working in the default quickstart cluster, which is fine for learning. In production, you’d typically create separate clusters for ingestion, transformation, and serving, so each workload has its own resources and a heavy query can’t slow down a latency-sensitive one. The documentation covers sizes and the CREATE CLUSTER options.
Step 10: Clean Up
DROP INDEX last_minute_idx;
DROP VIEW last_minute;
DROP TABLE events;
DROP MATERIALIZED VIEW wins_per_item;
DROP INDEX wins_by_item;
DROP VIEW winning_bids;
DROP SOURCE auction_house CASCADE;
Then stop the container:
docker stop materialized && docker rm materialized
View, Indexed View, or Materialized View?
Beginners often ask which one to use. A simple rule of thumb helps.
- A plain view is a named query. It does no continuous work. Use it to organize SQL and break logic into readable steps.
- An indexed view keeps the results in memory in a specific cluster. Use it when an application needs fast, point-style reads of fresh results, because the data is ready to serve.
- A materialized view keeps results durably, readable from any cluster, and is good as a stable data product other systems or teams depend on.
You can combine them. A common pattern is to build intermediate logic as plain views, materialize the final result, and add indexes where you need low-latency reads. (cite index=”131-1″>Materialize supports both indexed views and materialized views, and picking between them is largely about where you want the results to live.
Common Beginner Mistakes to Avoid
Expecting a plain view to be live. A view without an index or materialization recomputes when queried. It only becomes incrementally maintained once you index or materialize it.
Using now() in a view you want maintained. As the earlier section showed, now() can’t be maintained incrementally. Use mz_now() in a temporal filter instead.
Forgetting that indexes use memory. (cite index=”133-1″>Indexes are stored in memory, in arrangements. Indexing a very large, high-cardinality view can use a lot of RAM, so index what you need to serve, not everything.
Running every workload in one cluster. The default cluster is convenient for learning. In production, isolate ingestion and serving so one can’t starve the other.
Ignoring hydration time. When you create a materialized view or index, Materialize has to build its initial state, which takes time proportional to the data. (cite index=”133-1″>Hydration draws on the storage layer and existing indexes, so don’t be alarmed if a new object takes a while before it’s fully ready.
Treating the Emulator as production. It’s great for local development. As noted earlier, it doesn’t offer the full experience, so test production behavior against the cloud or self-managed product.
Materialize and RisingWave: A Short Comparison
Because both are PostgreSQL-compatible streaming databases, people often compare them. The overlap is real: both let you define materialized views in SQL and keep them updated, and both can ingest from databases and brokers.
The differences are architectural. Materialize is built on Timely and Differential Dataflow, keeps its working state in in-memory arrangements, and emphasizes strict serializability. RisingWave, covered in our guides, is written in Rust and stores streaming state in object storage with a Hummock engine. On licensing, Materialize has historically used the Business Source License, while RisingWave is Apache 2.0. Both have evolved quickly, so verify current features, deployment options, and pricing with each vendor before deciding, and test both on a representative workload.
Who Should Use Materialize?
Materialize tends to be a good fit when:
- You need always-fresh results from complex SQL, including multi-way joins, without writing a custom pipeline.
- Consistency matters, and you can’t tolerate views that disagree with each other or lag behind writes.
- You’re building operational features such as live dashboards, alerting, or customer-facing data products.
- Your team knows SQL and PostgreSQL and wants to stay there.
- You want to push changes to applications through
SUBSCRIBErather than polling.
It’s a weaker fit for purely batch analytics, for heavy ad hoc exploration over huge historical datasets, or for workloads where you need low-level custom operators in a programming language.
Frequently Asked Questions
What is Materialize used for? It’s used for real-time operational workloads: live dashboards, alerts, fraud checks, customer-facing data products, and any case where you need fresh results from SQL over changing data.
Is Materialize the same as PostgreSQL? No, but it’s PostgreSQL-compatible. You connect with Postgres tools and write familiar SQL, but its engine is built for incrementally maintained views over streaming data, not general transactional workloads.
What is incremental view maintenance? Updating a view’s result by applying only the effect of each input change, instead of recomputing the whole query. Materialize does this using Differential Dataflow.
What’s the difference between an indexed view and a materialized view? An indexed view keeps its results in memory within a cluster for fast, fresh reads. A materialized view stores results durably and can be read from other clusters. Both are incrementally maintained.
What does SUBSCRIBE do? It streams the changes to a query’s results to your client as they happen, so applications can react to updates instead of polling.
What is mz_now() and why not use now()? mz_now() returns the logical time of the transaction. Queries using it in temporal filters can be maintained incrementally, because Materialize can compute when results will change. Queries using now() can’t be maintained that way.
Can I run Materialize locally? Yes, with the Materialize Emulator Docker image. It’s meant for development and doesn’t provide the full experience of the cloud or self-managed product.
Is Materialize open source? Materialize has used the Business Source License, which converts to Apache 2.0 after a set period. License terms can change, so check the current license page for details.
Final Thoughts
The shift Materialize asks for is the same one every streaming database does: stop thinking of queries as things you run and start thinking of them as things that stay true. Its distinguishing bets are strict serializability, so views always agree with each other, and a dataflow engine built from the ground up for incremental computation.
The tutorial above gives you the working loop: a source, a view, an index for fast reads, a materialized view for durable results, SUBSCRIBE for pushing changes, and mz_now() for time-based logic. Replace the load generator with your own PostgreSQL CDC or Kafka source and you have the skeleton of a real pipeline. For authoritative reference on every statement and option, Materialize’s official documentation is the best place to go deeper, and our ClickHouse vs PostgreSQL guide shows the change-data-capture pattern that often feeds systems like this one.
For more hands-on guides to open-source data infrastructure and real-time analytics, keep exploring CourseDrill.
Popular Courses
