Materialized View Maintenance
Materialized view maintenance keeps a precomputed query result fresh as the base tables change, so reads against the view are fast and the refresh cost is paid on write, not on read.
Definition
Materialized view maintenance is the process of updating a stored query result when the base data changes — incrementally if the view supports it, fully otherwise — so that the view reflects a consistent state of the base tables and reads are served from the precomputed result rather than recomputed.
Context and scope
Applied in analytical dashboards, reporting tables, and any read-heavy workload where the query is expensive and the result is read many times per write.
Common misconceptions
- Materialized views are just cached queries — a cache is invalidatable; a materialized view is maintained, with a defined freshness and refresh mechanism
- Incremental maintenance is always possible — only some views (select-project-join without aggregation, or specific aggregates) can be maintained incrementally; others require a full refresh
Anti-patterns
- Refreshing a materialized view fully on every write, making the view more expensive than the base query
- Serving a materialized view that is stale without a staleness indicator, so readers believe they see current data
Related concepts
Authoritative source
https://www.postgresql.org/docs/current/rules-materializedviews.html
What it is
Materialized view maintenance is the process of updating a stored query result when the base data changes — incrementally if the view supports it, fully otherwise — so that the view reflects a consistent state of the base tables and reads are served from the precomputed result rather than recomputed. Applied in analytical dashboards, reporting tables, and any read-heavy workload where the query is expensive and the result is read many times per write. The data model and the access patterns are co-designed: a schema that is correct in the relational sense can be catastrophic for the queries the workload actually issues.
How it works
Indexing decisions follow the query shapes, not the column importance: a covering index turns a table scan into an index-only scan, but every index adds write amplification and storage cost. Partitioning aligns with the access dimension so a query touches one partition, not all. Transaction scope is minimized to hold locks briefly; long transactions are the root of contention and deadlock.
When to apply and pitfalls
Apply when data volume or query latency has a concrete business cost. The common pitfall is indexing for the current workload and ignoring the access patterns that growth will introduce — the index that helps today can serialize writes tomorrow. Another is treating the database as a black box, missing that the optimizer’s plan choice depends on statistics that drift as data changes.