Overview

With Materialize, you can use SQL to transform, deliver, and act on fast-changing data.

Materialize follows the SQL standard (SQL-92) implementation and aims for compatibility with the PostgreSQL dialect. It does not aim for compatibility with a specific version of PostgreSQL. This means that Materialize might support syntax from any released PostgreSQL version, but does not provide full coverage of the PostgreSQL dialect. The implementation and performance of specific features (like window functions) might also differ, because Materialize uses an entirely different database engine based on Timely and Differential Dataflow.

If you need specific syntax or features that are not currently supported in Materialize, please submit a feature request.

SELECT statement

To build your transformations, you can SELECT from sources, tables, views, and materialized views.

SELECT [ ALL | DISTINCT [ ON ( col_ref [, ...] ) ] ]
    [ { * | projection_expr [ [ AS ] output_name ] } [, ...] ]
    [ FROM table_expr [ join_expr | , ] ... ]
    [ WHERE condition_expr ]
    [ GROUP BY grouping_expr [, ...] ]
    [ OPTIONS ( option = val[, ...] ) ]
    [ HAVING having_expr ]
    [ ORDER BY projection_expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...] ]
    [ LIMIT { integer  } [ OFFSET { integer } ] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] { SELECT ...} ]

In Materialize, the SELECT statement supports (among others):

For more information, see:

Views and materialized views

A view represent queries that are saved under a name for reference.

CREATE VIEW my_view_name AS
SELECT ...   ;

In Materialize, you can create indexes on views. When you to create an index on a view, the underlying query is executed and the results are stored in memory within the cluster you create the index. As new data arrives, Materialize incrementally updates the view results.

CREATE INDEX idx_on_my_view ON my_view_name(...) ;

You can also create materialized views. A materialized view is a view whose results are persisted in durable storage. As new data arrives, Materialize incrementally updates the view results.

CREATE MATERIALIZED VIEW my_mat_view_name AS
SELECT ...  ;

You can also create an index on a materialized view to make the results available in memory within the cluster you create the index.

For more information, see:

Indexes

In Materialize, indexes represent query results stored in memory within a cluster. By making up-to-date view results available in memory, indexes can help improve performance within the cluster. Indexes can also help optimize query performance.

For more information, see:

Back to top ↑