Glossary/Analytics & Querying

Materialized View

A materialized view is a database object that stores the precomputed results of a query, eliminating the need to re-execute the query for subsequent uses.

Standard database views are virtual, executing their defining query each time referenced. This ensures data freshness but incurs repetitive computational cost. Materialized views trade freshness for performance by computing query results once and storing the results as physical data. Subsequent queries reference the stored results directly, dramatically improving performance for frequently used aggregations.

Materialized views are particularly valuable for complex aggregations, joins across many tables, or queries with expensive calculations. Storing monthly revenue summaries as a materialized view eliminates the need to compute multi-billion-row aggregations repeatedly. Similarly, views combining operational data with enrichment tables benefit from materialization, avoiding expensive joins on every query.

The tradeoff is freshness. Materialized views become stale as underlying data changes, requiring periodic refresh to synchronize results. Strategies include scheduled refreshes (nightly updates), event-driven refreshes (trigger after ETL completes), or incremental refreshes (update only changed rows). Modern systems enable transparent rewriting of queries to use materialized views, automatically accelerating queries without explicit view references.

Key Characteristics

  • ▶Store precomputed query results as persistent data
  • ▶Eliminate query re-execution by serving results from storage
  • ▶Dramatically accelerate queries on complex aggregations or joins
  • ▶Become stale as underlying data changes
  • ▶Require periodic refresh to maintain consistency
  • ▶Enable query rewriting to transparently use materialized results

Why It Matters

  • ▶Reduces query latency from minutes to seconds through precomputation
  • ▶Eliminates repeated expensive aggregation calculations
  • ▶Enables interactive analysis on aggregate data
  • ▶Reduces infrastructure costs by avoiding repeated computation
  • ▶Simplifies query logic by encapsulating complex transformations
  • ▶Supports real-time dashboards through pre-aggregated data

Example

`
-- Standard view (virtual, recomputed on every access)
CREATE VIEW v_sales_by_region AS
SELECT 
  region,
  DATE_TRUNC('month', order_date) as month,
  SUM(order_value) as monthly_revenue,
  COUNT(*) as transaction_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY region, DATE_TRUNC('month', order_date);

-- Query must scan billions of rows and aggregate
SELECT * FROM v_sales_by_region WHERE month = '2024-04';
-- Execution time: 30 seconds

-- Materialized view (precomputed results)
CREATE MATERIALIZED VIEW mv_sales_by_region AS
SELECT 
  region,
  DATE_TRUNC('month', order_date) as month,
  SUM(order_value) as monthly_revenue,
  COUNT(*) as transaction_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY region, DATE_TRUNC('month', order_date);

-- Query returns from precomputed storage
SELECT * FROM mv_sales_by_region WHERE month = '2024-04';
-- Execution time: 0.1 seconds

-- Refresh materialized view after nightly ETL
REFRESH MATERIALIZED VIEW mv_sales_by_region;
`

Coginiti Perspective

CoginitiScript's publication system offers a governed alternative to ad hoc materialized views. Blocks can be published as views (for always-current results) or as tables (for precomputed, refreshable snapshots), with the choice defined declaratively in publication metadata. Incremental publication strategies (append, merge) provide refresh mechanics similar to materialized views, but with the added governance of version control, code review, and the analytics catalog's promotion workflow.

Related Concepts

Query OptimizationData AggregationIndexCacheQuery RewritingSemantic LayerOLAP Cube

More in Analytics & Querying

Ad Hoc Query

An ad hoc query is an unplanned SQL query executed on demand to answer a specific, immediate question about data without prior optimization or scheduling.

Analytical Query

An analytical query is a SQL operation that aggregates, transforms, or examines data across multiple rows to produce summary results, statistics, or insights for decision-making.

BI (Business Intelligence)

Business Intelligence is the process of collecting, integrating, analyzing, and presenting data to support strategic and operational decision-making across an organization.

Cost-Based Optimization

Cost-based optimization is a query execution strategy where the optimizer estimates the computational cost of alternative execution plans and selects the plan with the lowest projected cost.

Data Aggregation

Data aggregation is the process of combining multiple rows of data using aggregate functions to compute summary statistics, totals, averages, and other derived metrics.

Data Exploration

Data exploration is the systematic investigation of datasets to understand structure, quality, distributions, relationships, and characteristics before formal analysis or modeling.

See Semantic Intelligence in Action

Coginiti operationalizes business meaning across your entire data estate.

Request a Demo