Materialized Views

Let's say you work at Uber. Your finance team has a dashboard that shows the total revenue generated in every city, grouped by hour, for the last 30 days.

To get this data, the database has to execute a query that joins the Rides, Cities, and Payments tables, filtering millions of rows, and calculating aggregations (SUMs and averages).

Every time the finance manager opens the dashboard, the database grinds its gears, spins its disks, maxes out its CPU, and takes 45 seconds to generate the report. It's incredibly inefficient because the database is doing the exact same math over and over again for historical data that hasn't changed.

How do we solve this? We could build an application-level cron job that runs the query once an hour and saves the result to a new table. Or, we could use a powerful built-in database feature: The Materialized View.

What is a View? (The Non-Materialized Kind)

Before we talk about materialized views, we need to understand standard database Views.

A standard View is just a saved SQL query. You define it like this: CREATE VIEW daily_revenue AS SELECT date, SUM(amount) FROM payments GROUP BY date;

Now, you can run SELECT * FROM daily_revenue.

It looks like a table, but it's not real. It's a virtual table. Every single time you query a standard View, the database executes the underlying complex query from scratch. It saves you from typing a long query, but it provides zero performance benefit.

The Materialized Upgrade

A Materialized View is entirely different. When you create a Materialized View, the database runs the complex query, calculates all the joins and sums, and physically saves the result to the hard disk as if it were a real, concrete table. (It is "materialized" onto the disk).

When the finance manager queries the Materialized View, the database doesn't do any math. It just reads the pre-computed results directly off the disk. A query that used to take 45 seconds now takes 5 milliseconds.

The Trade-off: Staleness and Refreshing

This sounds like magic, but there is a massive catch.

Because the result is physically saved to disk, what happens when a new ride happens and new payment data is inserted into the main tables? The Materialized View does not magically update. It is now completely out of date (stale).

To get the new data, you have to explicitly tell the database to update the view, usually by running: REFRESH MATERIALIZED VIEW daily_revenue;

When you run this, the database throws away the old saved table, runs the heavy query from scratch, and saves the new result to disk.

Because refreshing is expensive, you have to decide how stale your data is allowed to be.

• If the finance team only needs data accurate to the previous day, you run the refresh job once every night at 2:00 AM.
• If they need it accurate to the hour, you refresh it every hour.

Advanced Concept: Incremental Refresh

Some advanced databases (like PostgreSQL with certain extensions, or data warehouses like Snowflake and BigQuery) support Incremental Refresh (or Concurrent Refresh).

Instead of throwing away the entire materialized view and recalculating it from scratch, the database tracks exactly which new rows were added to the base tables since the last refresh. It then only calculates the math for those specific new rows and merges the result into the materialized view. This is significantly faster but complex to maintain under the hood.

When to use Materialized Views

Materialized Views are a specialized tool. You don't use them for transactional features like user login or updating a shopping cart.

They are the bedrock of Data Warehousing and Analytics. When building internal dashboards, business intelligence reports, or data pipelines where read speed is paramount and slight data staleness is acceptable, Materialized Views are the perfect solution. They sit neatly between raw normalized tables and completely separate application-level caching systems.

Summary

• A standard View is just a saved query. It executes from scratch every time and offers no performance benefits.
• A Materialized View pre-executes a complex query and physically saves the resulting data to the disk.
• Querying a Materialized View is blazingly fast because the database just reads pre-computed data without doing any joins or math.
• The catch is that the data becomes stale as underlying tables change.
• The view must be manually or periodically Refreshed to pull in new data, making it ideal for analytics and dashboards where slight delays are acceptable.