Materialized View

## What a materialized view is A materialized view is a pattern where you take the result of a query — often one that joins several tables, aggregates rows, or filters a large dataset — and physically persist that result as its own stored data structure, rather than recomputing it from scratch on every read. This is distinct from an ordinary ("virtual") database view, which is just a saved query definition: every time you select from a virtual view, the database re-executes the underlying SQL against the live source tables. A materialized view instead writes the computed rows to disk (or to a separate read store) once, and subsequent reads simply fetch those precomputed rows. ## How the copy is built and refreshed **Mechanism, step by step:** - **Step 1.** You define a query over one or more source tables/services — for example, "total order value per customer per month" joining an orders table and an `order_items` table and grouping by customer and month. - **Step 2.** A refresh process runs that query and writes the output into a dedicated table, index, cache, or even a different storage engine optimized for reads (a search index, a columnar store, a key-value cache). - **Step 3.** Application read paths are pointed at the materialized copy instead of the source tables. - **Step 4.** On some cadence — on demand, on a schedule, or in response to change events from the source — the view is refreshed, meaning the stored result is recomputed (fully or incrementally) to catch up with changes made to the source data since the last refresh. ## Why it exists The pattern exists to solve the mismatch between how data is naturally structured for writes (normalized, transactional, optimized to avoid duplication and preserve integrity) and how it's consumed for reads (denormalized, precomputed, shaped exactly for a specific query or UI need). Recomputing a five-table join with aggregations on every page load doesn't scale — CPU cost, lock contention, and I/O all balloon under load, and the query planner has to redo work that produced an identical answer moments ago. By moving that computation out of the read's critical path — either into a background job or into the write path — you turn an O(expensive-join) read into an O(single-table-lookup) read. This is especially valuable for: - **reporting/analytics dashboards**; - **product catalogs** with computed inventory or pricing; - **search indexes**; - and any "read amplifies write" scenario where one write event has to be reflected across many downstream queries. ## What you gain, what you pay **Trade-offs, cost on both sides.** The benefit is read latency and read-side scalability — you can also scale the materialized store independently (different hardware, different indexing strategy, even a different database technology) from the source of truth. The cost is threefold. 1. **First, staleness:** the view is a snapshot as of its last refresh, so reads can return data that lags reality by anywhere from milliseconds (event-driven refresh) to hours (nightly batch refresh) depending on strategy. 2. **Second, storage and operational overhead:** you now maintain a second copy of derived data, with its own schema, its own refresh pipeline, its own monitoring, and its own failure modes — more moving parts than a single source of truth. 3. **Third, refresh cost:** recomputing the view, especially a full recompute, can itself be expensive and needs to be scheduled so it doesn't compete with production load; incremental refresh reduces this but adds implementation complexity. ## Failure modes in production - **The refresh job falls behind** (queue backpressure, a slow batch window, a crashed cron job) and the view silently serves increasingly stale data — dangerous when the staleness isn't visible to the consumer, e.g., a dashboard that looks fresh but is actually 6 hours old because last night's job failed. - **A "thundering herd" on full refresh** is another common failure: if the view is rebuilt by re-running the full query against source tables, that query itself can degrade the source system's performance, sometimes worse than the original problem the view was built to solve. - **A third failure mode is drift** between the view's schema assumptions and the source schema — a column rename or a new valid state value the transform logic doesn't account for, producing silently wrong aggregates rather than an error. - **Finally, refresh races:** if a refresh reads source rows mid-write without a snapshot/transaction boundary, the materialized result can be internally inconsistent. ## Concrete example An e-commerce platform's "top selling products this week" widget, shown on every homepage load, would be prohibitively expensive to compute live from the orders table for every visitor. Instead, a scheduled job (say, every 15 minutes) or a stream processor consuming order-completed events maintains a materialized `product_sales_rollup` table keyed by `product_id` with a running count and revenue sum; the homepage reads that single-row lookup instead of aggregating millions of order rows per request. PostgreSQL's native `CREATE MATERIALIZED VIEW ... WITH DATA` (refreshed via `REFRESH MATERIALIZED VIEW`) is a concrete, widely-known instance of this same underlying pattern.