Materialized Views: Pre-computing Slow Queries

WHY IT EXISTS Complex queries with joins and aggregations on large tables are slow and resource-intensive. Running the same expensive query for multiple users or frequent dashboard refreshes creates a huge, repetitive load on the database, slowing down the entire system.

THE MENTAL MODEL Think of a materialized view as a query's "greatest hits" album. Instead of searching through an artist's entire massive catalog (the source tables) every time you want to hear a popular song (the query result), you play it from a pre-made compilation (the materialized view). You get the result instantly, but the compilation might not have the brand-new single on it yet. It's a direct tradeoff: speed for freshness.

HOW IT WORKS You define a materialized view with a standard SQL query, like CREATE MATERIALIZED VIEW daily_sales AS SELECT region, SUM(amount) FROM sales GROUP BY region. The database executes this query and stores the output as a physical table. Unlike a regular view, which is just a stored query definition, a materialized view holds actual data. This data must be refreshed periodically. Refresh strategies vary by database but typically include manual triggers, updates on a schedule, or automatic updates when the underlying source data changes.

WHEN TO USE IT Use materialized views for read-heavy workloads on data that can tolerate some latency. Three common scenarios are: first, business intelligence dashboards that summarize massive amounts of data; second, caching results from remote or federated databases to avoid network latency on every query; third, simplifying access patterns for application developers who can query a simple, fast view instead of writing complex joins.

WHEN NOT TO USE IT Avoid materialized views for systems requiring real-time data, as the view will always be slightly stale. They are also a poor fit for tables with very high write volumes, because the cost and complexity of frequently refreshing the view can outweigh the read performance benefits. For simple queries that are already fast, a materialized view just adds unnecessary complexity and storage overhead.

ONE CANONICAL EXAMPLE An e-commerce platform wants a dashboard showing total sales per product category. A query joining sales, products, and categories tables with billions of rows might take minutes to run. By creating a materialized view sales_by_category_daily that refreshes every hour, the dashboard can query this small, pre-aggregated table and get a response in milliseconds. The business accepts that the sales total might be up to an hour out of date.