Materialize: incremental materialized views over PostgreSQL, MySQL and Kafka

The problem Materialize solves: stale reads and cache invalidation

Most teams that need live data end up choosing between two bad options. They query a read replica, which returns a consistent snapshot of the primary but puts heavy analytical reads on the same system that serves transactions. Or they build a cache, which is fast until someone has to decide when to evict it.

Materialize takes a third position. You declare a SQL view, and the engine keeps its results up to date as the underlying tables change. The README describes three adoption patterns: query offload (CQRS), where complex reads move off the transactional system; an integration hub, where data from several sources is loaded and incrementally transformed into live views; and an operational data mesh, where SQL produces real-time data products for other services.

The audience is engineers who already know SQL and do not want to operate a stream processor with a separate programming model. The README states that the SQL interface is meant to democratize serving and accessing live data, and that the common production pairing is dbt Core.

How dataflows keep views correct instead of approximate

The core mechanism is described in the README: Materialize recasts your SQL queries as dataflows, which react efficiently to changes in your data as they happen. A dataflow is a maintained computation, not a query plan that reruns from scratch.

Two details matter for correctness. First, the README says Materialize does not ask you to accept approximate answers or eventual consistency, and that every answer is the correct result on some specific and recent version of your data. That guarantee is stated to hold even when a view joins data from multiple upstream systems. Second, incremental maintenance covers arbitrary inserts, updates and deletes, not just appends, and the README says this with no asterisks.

The engine also handles join planning differently from systems limited to nested binary joins. The README describes delta-joins, which avoid intermediate state blowup, and says they have been tested on joins of up to 64 relations. Subqueries are decorrelated by the optimizer, so a query written with a subquery does not need to be manually rewritten as a join. Views can be nested on views, and overlapping subplans can share underlying indexes.

Data arrives through sources. The README lists PostgreSQL and MySQL replication streams, Kafka and Kafka API-compatible systems such as Redpanda, and SaaS applications via webhooks. Reads go out over the PostgreSQL protocol, so an existing psql client works.

Installing Materialize and creating your first materialized view

The README does not give a package manager command. It points to two distribution routes: a free cloud trial at materialize.com/register, and a community edition download at materialize.com/download, described as free forever for deployments using less than 24 GiB of memory and 48 GiB of disk. Enterprise and community editions are both listed as self-managed options.

Once an instance is reachable, the README's own example is a TPC-H query that loads a generated dataset, defines a view and then materializes it. The source is created with a load generator rather than a real upstream, which makes it usable without any external system:

CREATE SOURCE tpch
  FROM LOAD GENERATOR TPCH (SCALE FACTOR 1)
  FOR ALL TABLES;

The next statement defines a reusable view over lineitem, filtering by ship date and grouping by supplier. The README comments that views define commonly reused subqueries:

CREATE VIEW revenue (supplier_no, total_revenue) AS
    SELECT
        l_suppkey,
        SUM(l_extendedprice * (1 - l_discount))
    FROM
        lineitem
    WHERE
        l_shipdate >= DATE '1996-01-01'
        AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' month
    GROUP BY
        l_suppkey;

The MATERIALIZED keyword is what triggers eager, consistent and incremental maintenance, with results stored in durable storage. The README's example then joins supplier against that view and orders the result:

CREATE MATERIALIZED VIEW tpch_q15 AS
  SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
FROM
    supplier,
    revenue
WHERE
    s_suppkey = supplier_no
    AND total_revenue = (
        SELECT
            max(total_revenue)
        FROM
            revenue
    )
ORDER BY
    s_suppkey;

Finally, an index keeps the results in memory and up to date. The README says the index in this example allows fast point lookups of individual supply keys:

CREATE INDEX tpch_q15_idx ON tpch_q15 (s_suppkey);

After that, the README says you can stream inserts, updates and deletes into the underlying tables and query SELECT * FROM tpch_q15 to see current results immediately. The distinction to internalize is that CREATE VIEW alone only defines the query; CREATE MATERIALIZED VIEW is what starts maintaining it, and the index is what makes point lookups fast.

Where Materialize is the wrong tool

Materialize is a read-serving layer, not a system of record. The README describes it as reading from PostgreSQL, MySQL, Kafka and webhooks; nothing in it describes accepting arbitrary application writes into a primary table. If your workload is transactional writes with occasional reads, a PostgreSQL primary and a read replica will be simpler and cheaper than a stateful streaming engine.

The resource floor is another real constraint. The community edition is described as free forever only below 24 GiB of memory and 48 GiB of disk. Above that, you are into the enterprise or cloud offerings, which changes the cost picture and the operational surface. The cloud service is described with high availability through multi-active replication, horizontal scalability across machines, and storage in cloud object storage such as Amazon S3. Self-managing the equivalent of that is not a small commitment, and the repository layout reflects it: the Cargo workspace includes orchestrator-kubernetes, orchestratord, balancerd, clusterd and environmentd as separate members, which is a lot of moving parts to run yourself.

There is also a compatibility caveat the README states plainly. Materialize supports a large fraction of PostgreSQL features and is actively expanding support for more built-in PostgreSQL functions, which means some functions you rely on may not exist yet. And the license is not a standard open source license. The package metadata reports NOASSERTION, and the pyproject.toml header states that use is governed by the Business Source License with a change date, after which it converts to Apache License 2.0. If your organization has rules about source-available licenses, read the LICENSE file at the repository root before anything else.

Materialize compared with PostgreSQL materialized views and batch refresh

The closest comparison is PostgreSQL's own materialized views. In PostgreSQL, a materialized view stores a result set and stays stale until you run REFRESH MATERIALIZED VIEW. There is no incremental maintenance and no dependency tracking: the refresh recomputes the query. Materialize's CREATE MATERIALIZED VIEW is a different contract. The README says the keyword is the trigger to eagerly, consistently and incrementally maintain results in durable storage, and that inserts, updates and deletes on the underlying tables are reflected without a refresh command.

The trade-off is operational weight. A PostgreSQL materialized view costs you nothing beyond the storage and the refresh job. Materialize costs you a running cluster, a durable storage backend, and the source connections to keep alive. For a dashboard that refreshes hourly, the PostgreSQL view is the better answer.

The second comparison is against batch transformation tools. A dbt model on a warehouse recomputes on a schedule, so the freshness of the output equals the schedule interval. Materialize is not positioned as a replacement for that pattern; the README notes that production customers tend to use dbt Core with it, which suggests the two are used together rather than chosen between. If your consumers tolerate a fifteen-minute lag, a scheduled model is simpler. If they need the answer to be correct on a recent version of the data at the moment they ask, that is the case Materialize is built for.

Maintenance, releases and licence cost

The repository is not archived, and the last push was on 2026-09-10. Releases are frequent and dated: v26.39.0 on 2026-08-27, v26.38.0 on 2026-08-20, and v26.37.0 on 2026-08-13. That cadence means an upgrade path exists roughly weekly, and pinning a version is a deliberate choice rather than the default.

Upgrade cost depends on how you deploy. On the managed service, the vendor handles version movement. Self-managed, you own it, and the workspace layout shows the components involved. The README does not document rollback, downgrade or version compatibility between the SQL layer and the storage layer, so that is a question to raise before you commit to a self-managed deployment.

On licensing, the pyproject.toml header is the clearest statement available: use of the software is governed by the Business Source License included in the LICENSE file at the repository root, and as of a change date the software converts to Apache License 2.0. The repository metadata reports NOASSERTION, which means the licence could not be classified automatically. If you plan to embed Materialize in a product or offer it as a service, read the LICENSE file and the change date yourself. This is a factual description of what the repository states, not legal advice.

Editorial conclusion

Adopt Materialize when your readers need consistent answers to joins and aggregations over data that changes continuously, and when you can run a stateful service with durable storage. Do not adopt it if a scheduled batch refresh already satisfies your readers, or if you need a database that accepts arbitrary writes: the README describes it as a platform that reads from PostgreSQL, MySQL, Kafka and webhooks, and its SQL surface is a PostgreSQL dialect for defining sources, views and indexes. Before committing, check the LICENSE file at the repository root, since the package metadata reports NOASSERTION and the pyproject.toml header points to the Business Source License with a change date.

Frequently asked questions

Official sources

1. Issues
2. MaterializeInc/materialize on GitHub
3. Project website
4. README
5. Releases