Home  /  Serve results

SELECT and SUBSCRIBE

You can query results from Materialize using SELECT and SUBSCRIBE SQL statements. Because Materialize uses the PostgreSQL wire protocol, it works out-of-the-box with a wide range of SQL clients and tools that support PostgreSQL.

SELECT

You can query data in Materialize using the SELECT statement. For example:

SELECT region.id, sum(purchase.total)
FROM mysql_simple_purchase AS purchase
JOIN mysql_simple_user AS user ON purchase.user_id = user.id
JOIN mysql_simple_region AS region ON user.region_id = region.id
GROUP BY region.id;

Performing a SELECT on an indexed view or a materialized view is Materialize’s ideal operation. When Materialize receives such a SELECT query, it quickly returns the maintained results from memory.

Materialize also quickly returns results for queries that only filter, project, transform with scalar functions, and re-order data that is maintained by an index.

Queries that can’t simply read out from an index will create an ephemeral dataflow to compute the results. These dataflows are bound to the active cluster, which you can change using:

SET cluster = <cluster name>;

Materialize will remove the dataflow as soon as it has returned the query results to you.

For more information, see SELECT reference page. See also the following client library guides:

SUBSCRIBE

You can use SUBSCRIBE to stream query results. For example:

BEGIN;
DECLARE c CURSOR FOR SUBSCRIBE (SELECT * FROM mv_counter_sum);
FETCH 10 c WITH (timeout='1s');
FETCH 20 c WITH (timeout='1s');
COMMIT;

The SUBSCRIBE statement is a more general form of a SELECT statement. While a SELECT statement computes a relation at a moment in time, a SUBSCRIBE operation computes how a relation changes over time.

You can use SUBSCRIBE to:

  • Power event processors that react to every change to a relation or an arbitrary SELECT statement.

  • Replicate the complete history of a relation while SUBSCRIBE is active.

💡 Tip: Use materialized view (instead of an indexed view) with SUBSCRIBE.

For more information, see SUBSCRIBE reference page. See also the following client library guides:

Back to top ↑