Materialized views earn their keep by trading storage and freshness for speed: precompute an expensive aggregation once, and every dashboard that reads it is instant. Apache Doris's asynchronous materialized views push that idea to production scale, but they come with choices - how often to refresh, what to refresh, and how queries find them - that decide whether the feature saves you or quietly costs you. Getting those choices right is the whole job.

The problem async MVs solve

A synchronous MV updates in lock-step with the base table, which keeps it perfectly fresh but ties write performance to the cost of maintaining the view. At scale, that coupling hurts. An asynchronous MV decouples the two: writes to the base table stay fast, and the view refreshes on its own schedule. You accept some staleness in exchange for not paying the maintenance cost on every write - usually the right trade for analytical aggregations that don't need to be correct to the millisecond.

Concepts in five minutes

An async MV is a stored query result that Doris refreshes on a policy you set. Two features make it powerful in practice. Partitioned refresh lets Doris rebuild only the partitions whose base data changed, rather than the whole view - so a daily-partitioned MV updates only today's partition when only today's data moved. Transparent rewrite means queries against the base tables can be automatically rerouted to the MV when the optimizer sees the MV already answers them - users get the speedup without rewriting their SQL to name the view.

Refresh policies that won't bite you

The refresh schedule is where MVs go wrong. Too frequent and you spend compute rebuilding views nobody read; too infrequent and dashboards show stale numbers. Match the cadence to the data and the tolerance: a view feeding an hourly report doesn't need minute-level refresh. Prefer incremental, partition-level refresh over full rebuilds wherever the data model allows it - rebuilding one changed partition is cheap, rebuilding a year of history because one row changed is not.

The partitioned MV pattern

The workhorse setup aligns the MV's partitioning with the base table's, typically by date. New data lands in the current partition, the MV refreshes only that partition, and historical partitions stay untouched and cheap. This pattern is what makes async MVs scale: refresh cost grows with how much data changed, not with how much data exists.

A practical recipe

CREATE MATERIALIZED VIEW daily_sales_mv
BUILD IMMEDIATE
REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
PARTITION BY (sale_date)
AS
SELECT sale_date, region,
       SUM(amount) AS total_amount,
       COUNT(*)    AS order_count
FROM orders
GROUP BY sale_date, region;

Partitioning the MV by sale_date lets Doris refresh only the partitions whose source rows changed, and transparent rewrite lets existing GROUP BY sale_date, region queries hit the MV automatically.

Design checklist

• Align MV partitioning with the base table so refresh stays incremental.
• Set refresh cadence to the freshness the consumer actually needs, not the fastest possible.
• Prefer partition-level refresh over full rebuilds.
• Confirm transparent rewrite is firing - check query plans, don't assume.
• Monitor refresh duration and cost; an MV that takes longer to refresh than the query it replaces is a net loss.

Common pitfalls

• Over-frequent refresh on views few queries read - wasted compute.
• Full rebuilds where incremental would do, because partitioning wasn't aligned.
• Assuming rewrite happens - if the optimizer can't match the query to the MV, you're maintaining a view nobody uses.
• Ignoring staleness in places that actually need fresh data - async MVs are for tolerant workloads.

The takeaway

Asynchronous materialized views in Doris are a scale tool: they keep writes fast and reads instant by decoupling freshness from maintenance. The leverage is in partition-aligned incremental refresh and verified transparent rewrite - get those two right and an MV refreshes cheaply and accelerates queries no one had to rewrite. Get them wrong and you've built an expensive table that's always slightly out of date.