Use synchronous materialized views for transparent rewriting

What are synchronous materialized views

A synchronous materialized view is a pre-computed structure attached to a base table that stores a subset of columns with optional pre-aggregation (SUM, COUNT, MIN, MAX, HLL_UNION, BITMAP_UNION). All associated views are updated within the same write transaction, ensuring strong consistency.

Typical use cases include:

• Accelerating repetitive aggregation queries on large detail tables.

• Providing an alternative prefix index order for queries that filter on non-leading columns.

• Supporting both detail and summary queries on the same base table.

Create a synchronous materialized view

Use the CREATE MATERIALIZED VIEW statement or ALTER TABLE ADD ROLLUP to define a synchronous materialized view.

-- Create a synchronous materialized view for daily sales aggregation
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    order_date,
    product_id,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM orders_detail
GROUP BY order_date, product_id;

-- Alternative: use ALTER TABLE ADD ROLLUP for prefix index reordering
ALTER TABLE user_events ADD ROLLUP rollup_by_event_type (
    event_type, event_date, user_id, event_count
);

After you create the materialized view, the system builds the data in the background. Run SHOW ALTER TABLE MATERIALIZED VIEW to monitor build progress.

Transparent rewrite mechanism

When a query is submitted, the optimizer evaluates whether a materialized view can satisfy it. The rewrite follows these rules:

• The query must reference only columns present in the materialized view.

• Aggregation functions in the query must be compatible with the pre-aggregations in the view. For example, a SUM query can use a SUM view, and a COUNT query can use a COUNT view.

• Filter conditions in the query must be applicable to the columns in the materialized view.

• The GROUP BY columns in the query must be a subset of the GROUP BY columns in the materialized view.

The rewrite is fully automatic. Your SQL statements do not need to reference the materialized view by name.

Verify transparent rewriting with EXPLAIN

Use EXPLAIN to confirm that the optimizer selects the materialized view instead of the base table.

EXPLAIN
SELECT order_date, SUM(amount) AS total_amount
FROM orders_detail
WHERE order_date >= '2024-06-01'
GROUP BY order_date;

-- In the output, look for the TABLE field in the scan node:
-- VOlapScanNode
--   TABLE: orders_detail(mv_daily_sales), PREAGGREGATION: ON
--   ^^ The view name in parentheses indicates the rewrite is active
--
-- At the end of the EXPLAIN output:
-- MaterializedViewRewriteSuccessAndChose:
--   mv_daily_sales chose    <-- Confirms the view was selected

If the TABLE field shows the base table name without a materialized view in parentheses (for example, TABLE: orders_detail(orders_detail)), the query was not rewritten. This typically occurs when the query references columns or functions not covered by the view.

Common use cases

• Dashboard acceleration: Create materialized views for frequently queried metric combinations (e.g., daily revenue by region) to serve dashboard queries in milliseconds.

• Alternative prefix index: When some queries filter on a column that is not the first key column, create a rollup with that column as the leading column to provide an efficient prefix index.

• Approximate distinct count: Use HLL_UNION or BITMAP_UNION in a materialized view to accelerate COUNT(DISTINCT) queries.

-- Materialized view for approximate UV (unique visitors) count
CREATE MATERIALIZED VIEW mv_daily_uv AS
SELECT
    visit_date,
    page_id,
    HLL_HASH(user_id) AS uv_hll
FROM page_visits
GROUP BY visit_date, page_id;

Limitations

• Synchronous materialized views support only single-table queries. Multi-table join acceleration requires asynchronous materialized views.

• Only specific aggregation functions are supported: SUM, MIN, MAX, COUNT, HLL_UNION, and BITMAP_UNION.

• Each additional materialized view increases write amplification. Limit the number of views per table to avoid impacting ingestion throughput.

• Schema changes to the base table (such as adding columns) may require rebuilding associated materialized views.