Best practices for asynchronous materialized views

Usage principles

• Data freshness: Asynchronous materialized views are ideal for scenarios that do not require high data freshness, such as T+1 data analysis. If your use case demands high freshness (second-level latency), consider using synchronous materialized views.

• Acceleration and consistency: For query acceleration, DBAs should group common SQL query patterns and minimize their overlap when creating materialized views. Clearly defined groups lead to higher-quality views.

• Trade-off between definition and build cost:

  • A materialized view definition that closely matches a query provides better acceleration but reduces generality and reusability, increasing the build cost.

  • A more general definition—such as one without a WHERE clause or with more aggregation dimensions—offers lower acceleration but provides better reusability at a lower build cost.

• Number of views: More materialized views are not always better. Building and refreshing them consumes computing resources, and the time required for the cost model to evaluate a transparent rewrite increases with the number of views. As a best practice, regularly check materialized view usage with the mv_infos() function and drop any that are unused.

• grace_period (data freshness tolerance): Setting the grace_period property (in seconds) allows a materialized view to be used for transparent rewrites for a specified period after its base table is modified. This prevents the materialized view from being continuously invalidated by frequent updates to the base table. For example, setting grace_period = 3600 allows a data lag of 1 hour.

Refresh strategy

Create a partitioned materialized view if the following conditions are met:

• The base table is a large partitioned table.

• Other tables in the view definition, besides the partitioned table, change infrequently.

• The SQL definition of the materialized view and its partitioning key allow for partition inference.

• The materialized view does not have an excessive number of partitions, which can cause long build times.

If some partitions of a materialized view become invalid, transparent rewrite can still return correct results by combining the valid partitions with the base table using UNION ALL.

If you cannot create a partitioned materialized view, consider using a materialized view with a full refresh.

Query acceleration

Optimize query performance by designing the SQL definition for a materialized view in the following ways:

Join pre-computation

If your queries frequently involve multi-table JOINs, precompute the JOIN in the materialized view to avoid the overhead of runtime JOINs.

-- Pre-compute the JOIN between orders and items
CREATE MATERIALIZED VIEW mv_order_item
REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
AS SELECT
    o.order_id, o.order_date, o.customer_id,
    i.item_name, i.category, o.amount
FROM orders o
JOIN items i ON o.item_id = i.item_id;

Aggregation pre-computation

For frequently executed aggregation queries, pre-calculate the results in a materialized view. Include enough dimension columns to enable transparent rewrite to serve queries of different granularities through rollup.

-- Pre-aggregate sales by day and category (supports rollup to week/month)
CREATE MATERIALIZED VIEW mv_daily_sales
PARTITION BY (sale_date)
REFRESH AUTO ON SCHEDULE EVERY 1 DAY
AS SELECT
    date_trunc(order_date, 'day') AS sale_date,
    category,
    region,
    sum(amount) AS total_amount,
    count(*) AS order_count
FROM orders
GROUP BY date_trunc(order_date, 'day'), category, region;

Filter optimization

If queries focus on a data subset, add a WHERE clause to the materialized view definition to reduce its data volume. Transparent rewrite supports predicate compensation, so a query can still hit the materialized view even if its filter condition is stricter.

-- Materialize only active orders from the last year
CREATE MATERIALIZED VIEW mv_active_orders
PARTITION BY (order_month)
REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
AS SELECT
    date_trunc(order_date, 'month') AS order_month,
    customer_id, status, amount
FROM orders
WHERE status = 'active' AND order_date >= '2025-01-01';

Expression pre-computation

Pre-compute complex expressions within the materialized view to avoid repeated calculations at query time.

-- Pre-compute tax-inclusive amount and profit rate
CREATE MATERIALIZED VIEW mv_profit
REFRESH AUTO ON SCHEDULE EVERY 1 DAY
AS SELECT
    order_id, product_id,
    amount * (1 + tax_rate) AS amount_with_tax,
    (amount - cost) / amount AS profit_rate
FROM order_details;

Typical use cases

Use case 1: Accelerate joins and aggregations

In reporting and BI analytics scenarios, queries often involve JOINs and aggregations across multiple fact tables and dimension tables. Pre-computing these operations in materialized views can reduce complex query response times from minutes to seconds.

-- Pre-compute a sales report: JOIN orders, customers, and products
CREATE MATERIALIZED VIEW mv_sales_report
PARTITION BY (sale_month)
REFRESH AUTO ON SCHEDULE EVERY 1 DAY
AS SELECT
    date_trunc(o.order_date, 'month') AS sale_month,
    c.region, p.category,
    sum(o.amount) AS total_sales,
    count(DISTINCT o.customer_id) AS customer_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
GROUP BY date_trunc(o.order_date, 'month'), c.region, p.category;

Use case 2: Data modeling and ETL

Use materialized views to implement data warehouse layering (ODS → DWD → DWS → ADS). A triggered refresh can create cascading updates between layers and reduce redundant computations:

• DWD layer (wide detail table): Based on ODS tables, use a materialized view to perform multi-table JOINs and data cleansing. Use a scheduled refresh (ON SCHEDULE).

• DWS layer (summary table): Based on the DWD materialized view, perform further aggregations. Use a triggered refresh (ON COMMIT) so that the DWS refresh is automatically triggered after the DWD refresh completes.

• ADS layer (application table): Based on the DWS materialized view, this layer is tailored for final report queries. Use either a triggered refresh or a manual refresh.

Use case 3: Federated query acceleration

Asynchronous materialized views let you cache frequently queried results from the data lake in SelectDB's local storage, leveraging its high-performance engine for significantly faster queries.

-- Cache user behavior logs from Hive into SelectDB
CREATE MATERIALIZED VIEW mv_hive_user_behavior
PARTITION BY (event_date)
REFRESH AUTO ON SCHEDULE EVERY 4 HOUR
PROPERTIES (
    'excluded_trigger_tables' = 'hive_catalog.db.dim_user'
)
AS SELECT
    date_trunc(event_time, 'day') AS event_date,
    u.user_name, e.event_type,
    count(*) AS event_count
FROM hive_catalog.db.events e
JOIN hive_catalog.db.dim_user u ON e.user_id = u.id
GROUP BY date_trunc(event_time, 'day'), u.user_name, e.event_type;

Use case 4: Write efficiency

In high-frequency write scenarios, concurrent aggregation queries can compete with write operations for resources. A materialized view with a scheduled refresh separates these computations, avoiding resource contention.

• Schedule the materialized view refresh to run during off-peak hours, such as early morning. Use the STARTS clause to specify the first refresh time.

• Use the grace_period property to allow the materialized view to serve queries with slightly stale data for a certain period. This can reduce the refresh frequency.

Partitioned materialized views

When the base table for a materialized view is large and partitioned, a partitioned materialized view is the most cost-effective solution.

CREATE MATERIALIZED VIEW rollup_partition_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem
    LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
GROUP BY l_linestatus, ps_partkey, date_trunc(l_ordertime, 'day');

Notes:

• Base table partition changes invalidate corresponding materialized view partitions: If you change a base table partition (e.g., by adding, dropping, or modifying its data), the corresponding materialized view partition becomes invalid. Invalid partitions cannot be used for transparent rewrite, though they can still be accessed by querying the materialized view directly. During a transparent rewrite, SelectDB returns correct results by combining the valid partitions of the materialized view with the base table using UNION ALL. You can check the validity status of each partition with SHOW PARTITIONS FROM mv_name.

• Non-partitioned table changes invalidate all partitions: If a non-partitioned table in the view's definition changes, all partitions of the materialized view become invalid. In this case, the materialized view cannot be used for transparent rewrite. You must use REFRESH MATERIALIZED VIEW mv_name AUTO; to refresh all partitions whose data has changed. As a best practice, design frequently changing tables as partitioned tables, and use slowly changing dimension tables as non-partitioned tables in the computation.

• If a non-partitioned table only receives inserts (no modifications), you can exclude it from triggering a refresh using the excluded_trigger_tables property when you create the materialized view (e.g., 'excluded_trigger_tables' = 'table_name1,table_name2'). This prevents inserts into the non-partitioned table from causing a full invalidation of the materialized view. At the next refresh, only invalid partitions that correspond to the partitioned table are refreshed.

Recent partition retention

If your base table has many partitions but you only query recent data, configure the materialized view to retain only recent partitions. This reduces build costs and storage, as expired partitions are dropped automatically during each refresh. Use the following combination of properties:

• partition_sync_limit: When the partitioning key of the base table is a time-based type, this property configures the range of partitions to sync from the base table. It must be used with partition_sync_time_unit. For example, if partition_sync_limit = 3 and partition_sync_time_unit = DAY, only the partitions and data from the last 3 days of the base table are synchronized.

• partition_sync_time_unit: The time unit for the partition sync range. Supported values are DAY, MONTH, and YEAR. The default is DAY.

• partition_date_format: When the partitioning key of the base table is a string type, this property specifies the date format, such as yyyy-MM-dd. This allows partition_sync_limit to correctly parse the time range.

Example: The following materialized view retains only data from the last 3 days. Partitions older than 3 days are automatically deleted during refresh. If no data exists in the last 3 days, querying this materialized view directly returns no results.

CREATE MATERIALIZED VIEW latest_partition_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
    'partition_sync_limit' = '3',
    'partition_sync_time_unit' = 'DAY',
    'partition_date_format' = 'yyyy-MM-dd'
)
AS SELECT
    l_linestatus,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem
    LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
GROUP BY l_linestatus, ps_partkey, date_trunc(l_ordertime, 'day');

Partitioned materialized views with UNION ALL

A partitioned materialized view definition cannot directly include a UNION ALL clause. As a workaround, create a separate materialized view for each branch of the UNION ALL, and then create a standard view to merge them.

-- Create a partitioned materialized view for each part of the UNION ALL
CREATE MATERIALIZED VIEW union_sub_mv1
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
GROUP BY l_linestatus, ps_partkey, date_trunc(l_ordertime, 'day');

CREATE MATERIALIZED VIEW union_sub_mv2
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    l_extendedprice AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey;

-- Create a view to merge the results of the two materialized views
CREATE VIEW union_view AS
SELECT * FROM union_sub_mv1
UNION ALL
SELECT * FROM union_sub_mv2;