What is a Materialized View

• REFRESH MATERIALIZED VIEW mv_name — full rebuild
• Incremental refresh: Postgres 14+ with pg_ivm extension
• Index can be created — faster queries
• Stale data: read-time can be N minutes behind source
• Alternative: DB triggers + summary table (hand-managed incremental)

-- Create materialized view
CREATE MATERIALIZED VIEW orders_daily_summary AS
SELECT
  DATE(created_at) AS day,
  status,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
GROUP BY day, status;

-- Index for fast queries
CREATE INDEX ON orders_daily_summary (day);

-- Refresh periodically (cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY orders_daily_summary;

• CDC
• CQRS

A materialized view is fundamentally a stored query result, which allows for improved performance in data retrieval. Unlike a standard view that generates results on-the-fly, a materialized view stores the data physically on disk, making it significantly faster for SELECT operations. This performance gain comes at the cost of requiring manual refreshes to synchronize with the underlying data changes.

When you create a materialized view, the database engine executes the defined query and saves the result set as a physical table. This table can then be queried directly, which reduces the computational overhead involved in complex queries, especially those involving aggregations and multiple joins.

However, the refresh mechanism is critical. Depending on the database system, you can set up manual, scheduled, or incremental refreshes:

• Manual Refresh: The user explicitly invokes the refresh command.
• Scheduled Refresh: The database can be configured to refresh the materialized view at regular intervals.
• Incremental Refresh: Only the changes since the last refresh are applied, making it more efficient.

Different databases have varying syntax and capabilities for materialized views, which can influence their implementation.

Materialized views are particularly valuable in scenarios where query performance is paramount. Here are some common use cases:

• Data Warehousing: In environments where large datasets are aggregated, materialized views can pre-compute complex queries to enhance performance in reporting and analysis.
• Analytics Dashboards: Dashboards that require real-time insights can leverage materialized views to reduce loading times for large datasets, providing users with quick access to critical metrics.
• Reporting: For applications that generate regular reports, materialized views can store the results of expensive calculations, allowing for faster report generation.
• Join Operations: Materialized views can simplify complex join operations across large tables, reducing the need for repeated computations and enhancing performance.

While materialized views improve query speed, it is essential to evaluate the trade-offs, particularly related to data freshness and maintenance overhead.

Creating a materialized view involves defining the query that produces the desired dataset and specifying any refresh options. Below are practical examples for PostgreSQL and Oracle:

PostgreSQL Example:

This command creates a materialized view named sales_summary that aggregates sales data by product_id.

To refresh this view manually, use:

Oracle Example:

In this Oracle example, a materialized view named employees_mv is created to count employees per department, with an immediate build and on-demand refresh.

For MySQL, you can simulate a materialized view using a combination of tables and triggers. Create a table to store the results and set up a trigger to update it upon data changes. While not as straightforward as native support in other databases, this approach can still yield performance benefits.