Designing Async Materialized Views in VeloDB

Marcus Tran|Aug 14, 2025|5 min read

Designing Async Materialized Views in VeloDB (Apache Doris) for Sub-Second Dashboards

The problem

Stakeholders want "live" dashboards, not nightly reports. Async materialized views (MVs) precompute joins/aggregations, then route queries to those results automatically - so dashboards feel instant without burning compute on every request. In VeloDB (built on Apache Doris), async MVs support multi-table definitions, incremental refresh, and transparent query rewrite, which is what user-facing analytics need. (VeloDB Docs)

Concept in plain terms

Two flavors of MVs exist:

  • Synchronous MV (sync MV): maintained in real-time, single-table only; suited to strong freshness requirements. (VeloDB Docs)
  • Asynchronous MV (async MV): single- or multi-table, refreshed on a schedule or on demand, with options for partition-incremental refresh. This fits sub-second reads with minute-level freshness. (VeloDB Docs)

Transparent rewrite: Doris/VeloDB automatically matches eligible queries to the MV (SPJG algorithm), so BI tools keep the original SQL while the engine serves results from the MV. Use EXPLAIN to confirm rewrites. (Apache Doris)

Quick mental model (and constraints)

  • Build & refresh: BUILD IMMEDIATE | DEFERRED, then REFRESH COMPLETE | AUTO with ON MANUAL or ON SCHEDULE EVERY <n> <unit>. AUTO attempts partition-incremental refresh. (Apache Doris)
  • Partitioning: Choose MV partitions that align with the base table's time partition (e.g., daily), otherwise incremental refresh and pruning are forfeited. (Apache Doris)
  • Grace period: For transparent rewrite during ingest churn, a bounded staleness window can be allowed via grace_period. (Apache Doris)
  • External sources: Incremental change detection currently covers internal tables and Hive; other sources may require REFRESH COMPLETE. (Apache Doris)

Sync vs. Async MVs (choose deliberately)

FeatureSync MVAsync MV
Query rewriteAutomaticAutomatic
Direct queryNot supportedSupported
Tables allowedSingle-table onlySingle- or multi-table
FreshnessReal-timeScheduled or manual (eventual)
Incremental refreshN/APartition-incremental via AUTO

Source: VeloDB/Doris MV overviews and async MV docs. (VeloDB Docs)

A practical blueprint (orders → 1-minute rollup)

Goal: live minute-bucket KPIs (orders, revenue) refreshed every 5 minutes with partition-incremental updates.

1) Base events (already time-partitioned)

CREATE TABLE orders_events (
  event_time    DATETIME NOT NULL,
  order_id      BIGINT   NOT NULL,
  customer_id   BIGINT   NOT NULL,
  status        VARCHAR(16),
  amount_usd    DECIMAL(12,2)
)
DUPLICATE KEY(event_time, order_id)
PARTITION BY RANGE(event_time) (
  FROM ("2025-01-01") TO ("2026-01-01") INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(order_id) BUCKETS 16;

Ingest continuously (Kafka → Routine Load or Flink connector). (Apache Doris)

2) Async MV (minute rollup, scheduled refresh)

CREATE MATERIALIZED VIEW mv_orders_1min
BUILD IMMEDIATE
REFRESH AUTO ON SCHEDULE EVERY 5 MINUTE
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY RANDOM BUCKETS 16
AS
SELECT
  date_trunc('minute', event_time) AS ts_minute,
  COUNT(*)                         AS orders,
  SUM(amount_usd)                  AS revenue,
  SUM(CASE WHEN status='FAILED' THEN 1 ELSE 0 END) AS failed
FROM orders_events
GROUP BY date_trunc('minute', event_time);
  • REFRESH AUTO enables partition-incremental refresh (changed base partitions only).
  • PARTITION BY date_trunc('day', …) aligns MV partitions with the table's time window, preserving pruning and incremental behavior. (Apache Doris)

3) Verify rewrites (and that the MV is hit)

EXPLAIN MEMO PLAN
SELECT date_trunc('minute', event_time) AS ts_minute,
       COUNT(*), SUM(amount_usd)
FROM orders_events
WHERE event_time >= now() - INTERVAL 1 HOUR
GROUP BY date_trunc('minute', event_time);

Look for the MATERIALIZATIONS section showing the chosen MV. (Apache Doris)

4) Operate and observe

  • Check MV status:
SELECT *
FROM mv_infos('database'='analytics')
WHERE Name = 'mv_orders_1min';
  • Force refresh: REFRESH MATERIALIZED VIEW mv_orders_1min AUTO; or ... COMPLETE;
  • Pause/Resume: PAUSE MATERIALIZED VIEW ...; / RESUME MATERIALIZED VIEW ...;
  • Zero-downtime change: ALTER MATERIALIZED VIEW mv_old REPLACE WITH MATERIALIZED VIEW mv_new; (atomic swap). (Apache Doris)

Best practices

  1. Design to the query, not the schema. Start from the hot BI queries and mirror their shape (grouping keys, filters, joins). The closer the MV to the query, the bigger the speedup - at the cost of generality. Balance both. (Apache Doris)
  2. Partition for incremental refresh. Pick daily (or hourly) partitions that match the SLA; misalignment kills pruning and forces full refresh. (Apache Doris)
  3. Use grace_period to tolerate ingestion lag. It lets rewrites proceed within a small staleness window, keeping dashboards responsive during heavy writes. (Apache Doris)
  4. Limit MV sprawl. Fewer, well-used MVs beat dozens of near-duplicates. Validate with EXPLAIN and usage metrics before creating another. (Apache Doris)
  5. Mind external sources. If an MV reads JDBC/external tables, Doris can't always detect changes - prefer COMPLETE or land the data internally/Hive for incremental detection. (Apache Doris)
  6. Schedule by business need, not habit. Start at 5-15 minutes for product dashboards; tighten only if users truly need it.

Common pitfalls (and how to avoid them)

  • Wrong MV type: Sync MVs can't handle multi-table logic - use async for joins/rollups. (VeloDB Docs)
  • Over-wide GROUP BY: MV cardinality explodes; aggregate at the grain users actually slice by.
  • Unaligned time buckets: If users query minute buckets but the MV is hourly, rewrites often miss. Align date_trunc() with query buckets. (Apache Doris)
  • One giant nightly refresh: Prefer incremental (AUTO) plus a periodic full (e.g., daily) to heal drift. (Apache Doris)

Optional: speed up data retrieval from Python

To extract MV results to services or notebooks, use Arrow Flight SQL with the Python ADBC driver for high-throughput reads:

import adbc_driver_flightsql.dbapi as flightsql
conn = flightsql.connect(uri="grpc://<fe-host>:<port>", db="analytics")
cur  = conn.cursor()
cur.execute("""
  SELECT ts_minute, orders, revenue
  FROM mv_orders_1min
  WHERE ts_minute >= now() - interval 1 hour
""")
rows = cur.fetchall()

Docs: Arrow Flight SQL connection guides for Doris/VeloDB. (Apache Doris)

Takeaways

Async MVs fit fast, predictable dashboards with minute-level freshness. Design the MV from the top query, align partitions and time buckets, enable incremental refresh, and set a grace period to ride out ingest spikes. Use EXPLAIN and mv_infos to verify results, and evolve definitions with atomic replacement for no downtime. Start with one high-value dashboard, ship a single async MV, confirm the rewrite, then iterate on schedule and grain based on real usage. (Apache Doris)

Reference docs

  • Async MV overview and creation syntax (VeloDB/Doris) (VeloDB Docs)
  • Transparent query rewriting details (Doris) (Apache Doris)
  • Routine Load from Kafka (Doris) (Apache Doris)
  • Arrow Flight SQL connection guide (Doris/VeloDB) (Apache Doris)

Leave a Comment