# Materialized views > A materialized view stores the result of a query so reads do not have to repeat expensive joins, scans, or aggregations. In system design, precomputed views make feeds, dashboards, and counters fast by moving work to write time or background processing, at the cost of extra storage and update complexity. Source: https://sysround.com/concepts/materialized-views Updated: 2026-11-05 ## How materialized views works Start with a repeated, expensive read. A restaurant page might calculate its rating by scanning every review and computing an average. Instead, store one row per restaurant with rating_sum and review_count, then calculate the average from those two values. Keep the original records as the source of truth. The view is derived data organized for a particular read pattern: one row per restaurant, one bucket per ad per minute, or an ordered list of post IDs per user's feed. A regular SQL view stores a query definition; a materialized view also stores its results. Choose how to maintain it. A full refresh reruns the query periodically. Incremental maintenance applies only changes, either within the source transaction or asynchronously through change data capture or reliably published events. Transactional maintenance adds work to writes; asynchronous maintenance introduces lag. Native database support varies, so name the behavior you need rather than assuming a feature exists. Handle changes, not just inserts. A new four-star review adds four to the sum and one to the count. Editing that review to two stars subtracts two from the sum without changing the count. Deleting it removes its current rating and decrements the count. These operations require before-and-after values or enough stored state to calculate the difference. Make asynchronous updates safe to retry. For aggregate deltas, commit the view update and deduplication record atomically, then advance the consumer checkpoint. Where ordering matters, preserve order per entity or use source versions to reject older updates. Simply retrying an increment can count the same change twice. Plan initialization and rebuilding. Build from a consistent snapshot paired with a change-log position, then apply subsequent changes without gaps or double application. For a large rebuild, populate a separate version, catch it up, validate it, and switch readers over. Monitor processing lag and expose data freshness where it matters. ## Worked example: Precomputing restaurant ratings Suppose a restaurant has 10,000 reviews totaling 42,000 stars. Instead of scanning those reviews for every page load, store a view row containing rating_sum = 42,000 and review_count = 10,000; its displayed average is 4.2. When a reviewer changes a rating from five stars to three, the processor subtracts two from the sum and leaves the count unchanged. It commits that update and the change's deduplication record in one transaction, so a retry cannot subtract twice. The page accepts up to five seconds of lag. A periodic reconciliation compares the view with source reviews, and excessive lag raises an alert rather than silently appearing current. ## Trade-offs - **Faster reads, more write work.** Precomputation replaces repeated query work with maintenance work, which can become expensive when one source change affects many view entries. - **Freshness versus write latency.** Asynchronous updates let writes finish independently but serve stale results, while transactional maintenance couples write latency and availability to the view update. - **Extra storage and narrower query coverage.** Each view consumes storage and usually serves a specific query shape, so new filters or grouping dimensions may require additional views or raw-data queries. - **More complicated correctness.** Retries, out-of-order changes, edits, deletions, and late events must all preserve the intended result rather than silently corrupting aggregates. - **Recovery and operational cost.** Backfills, schema changes, reconciliation, and rebuilding compete with live traffic and require retained source data or another reliable reconstruction path. ## When to use it in a system design interview - A frequent read repeatedly performs the same expensive join, aggregation, or fan-out over source records. - A dashboard needs time-bucketed totals or rollups without scanning every underlying event. - A home feed needs a prebuilt list of posts instead of querying and merging every followed account on each request. - The product can define an acceptable freshness delay, or the view can be maintained within the source transaction at an acceptable cost. - The query patterns are predictable enough that a small number of precomputed representations will cover most reads. ## How to bring it up in the interview - Name the exact query and stored representation: for example, restaurant_id maps to rating_sum and review_count. Explain what work disappears from the read path. - State the freshness requirement before choosing the update path. Interviewers will probe whether stale results are merely inconvenient or can violate a business rule. - Walk through one insert, edit, deletion, and retry. Explain where source versions or deduplication state live and what is committed atomically. - Estimate maintenance amplification as well as read savings. A single post added to many follower feeds can cost much more than updating one aggregate row. - Describe worker failure and rebuild behavior. Expect questions about durable change capture, lag alerts, retention limits, reconciliation, and switching to a rebuilt view safely. ## Common mistakes - Calling any cached response a materialized view without defining its source query, maintenance rules, or freshness contract. - Assuming a successful source write guarantees a separately published event; use change data capture or a transactional outbox when the handoff must be reliable. - Applying increments without retry protection, or assuming message ordering alone prevents duplicate updates. - Storing only an average instead of the sum and count, which makes correct edits, deletions, and merging harder. - Using a stale view as the authority for inventory allocation, spendable funds, or access control without an authoritative check or stronger consistency mechanism. ## Interview questions where it comes up - [Design a news feed like Facebook](https://sysround.com/questions/design-news-feed): Fan-out on write materializes each user's feed, making update amplification, lag, and reliable propagation central design decisions. - [Design an ad click aggregator](https://sysround.com/questions/design-ad-click-aggregator): Precomputed time-bucketed click totals enable fast queries while requiring duplicate protection, late-event corrections, and billing reconciliation. - [Design a restaurant review app like Yelp](https://sysround.com/questions/design-yelp): Restaurant rating summaries must remain correct as reviews are created, edited, deleted, and delivered more than once. - [Design a metrics monitoring system like Datadog](https://sysround.com/questions/design-metrics-monitoring-system): Precomputed rollups make historical metric queries affordable but require careful aggregation semantics and handling of retries and late samples. - [Design Google Analytics](https://sysround.com/questions/design-google-analytics): Dashboard aggregates and distinct-user summaries need maintained query-specific results with explicit rules for retries and late-arriving data. - [Design a top K trending system](https://sysround.com/questions/design-top-k): Trending rankings are maintained derived results that must reflect new events, window expiration, duplicate suppression, and recovery. - [Design Facebook likes at scale](https://sysround.com/questions/design-facebook-likes): Displayed like totals are materialized aggregates over user like state and must converge correctly through retries, unlikes, and burst traffic. ## FAQ ### What is the difference between a view and a materialized view? A regular database view stores a query definition and evaluates it against underlying data when queried. A materialized view stores the query's results, which can make reads cheaper but requires a refresh or maintenance mechanism. Whether it updates automatically depends on the database and configuration. ### What is the difference between a materialized view and a cache? A cache usually stores copies of values or responses and often fills on demand, with expiration or eviction. A materialized view stores a derived query result with defined maintenance rules, often including joins or aggregates. The categories overlap: a precomputed feed stored in Redis can function as both a cache and a materialized read model. ### How do you keep a materialized view up to date? Use periodic full refreshes, incremental transactional updates, or asynchronous processing of source changes. Choose based on acceptable staleness, update volume, and the database's capabilities. For asynchronous maintenance, address reliable change capture, retry safety, ordering, and rebuilding before treating the view as dependable. ## Related concepts - [Denormalization](https://sysround.com/concepts/denormalization) - [CQRS](https://sysround.com/concepts/cqrs) - [Change data capture](https://sysround.com/concepts/change-data-capture) - [Stream processing](https://sysround.com/concepts/stream-processing) --- SysRound (https://sysround.com) is an AI practice tool for system design interviews: a voice or typed AI interviewer, a shared whiteboard, then feedback on every skill and a model answer. Its feedback is AI-generated and can be wrong.