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 strives for compatibility with the PostgreSQL dialect.

You can build complex analytical workloads using any type of join (including non-windowed joins and joins on arbitrary conditions). You can also leverage exciting new SQL patterns enabled by streaming, like Change Data Capture (CDC), temporal filters, and subscriptions.

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 ↑