Materialized Views

A materialized view is a database object that stores the result of a query physically on disk, providing fast read access to precomputed data that would otherwise require expensive joins or aggregations.

What It Really Means

A regular view is a saved SQL query — every time you select from it, the database re-executes the underlying query. A materialized view runs the query once, stores the results in a table, and serves subsequent reads directly from that stored result. It trades storage space and data freshness for dramatic read performance improvements.

Materialized views are the database-level equivalent of caching. Instead of caching query results in Redis or your application, you let the database manage a precomputed result set. The database handles storage, indexing, and refresh — you do not need external cache invalidation logic.

This approach works exceptionally well for dashboards, reports, and API endpoints that aggregate data across many tables. A dashboard query that joins five tables and aggregates millions of rows might take 30 seconds. A materialized view serves the same data in 5ms.

How It Works in Practice

Example: E-commerce Analytics Dashboard

Refresh Strategies

Full refresh: Recompute the entire materialized view from scratch.

Scheduled refresh: Use pg_cron or application scheduler.

Incremental refresh (not native in PostgreSQL but possible with triggers):

Implementation

Regular view vs materialized view decision:

Trade-offs

Benefits:

• Orders of magnitude faster reads for complex queries
• Can be indexed like regular tables
• Simpler than external caching (no cache invalidation logic)
• Database handles storage and consistency

Costs:

• Data staleness between refreshes
• Storage overhead (full copy of query results)
• Refresh cost (full recompute can be expensive)
• CONCURRENTLY refresh requires a unique index

When to use:

• Analytics dashboards queried frequently
• API endpoints serving aggregated data
• Reports that tolerate minutes-old data
• Complex queries joining many tables

When NOT to use:

• Data must be real-time (use optimized queries or caching)
• The underlying data changes faster than you can refresh
• The materialized view would be as large as the base tables (no benefit)

Common Misconceptions

• "Materialized views update automatically" — In PostgreSQL, you must explicitly refresh them. Some databases (Oracle, SQL Server) support automatic incremental refresh.
• "Materialized views replace caching" — They complement caching. Use materialized views for database-level precomputation and Redis/Memcached for application-level caching.
• "You cannot index a materialized view" — You can and should add indexes. A materialized view without indexes is just a slow table.
• "CONCURRENTLY refresh is always better" — It avoids locking but takes longer and requires a unique index. For small materialized views refreshed during off-peak hours, a regular refresh is simpler.

How This Appears in Interviews

1. "Your analytics dashboard is slow" — Materialized views for precomputed aggregations, refreshed on a schedule that matches the dashboard's SLA.
2. "How do you serve leaderboard data efficiently?" — Materialized view of top-N aggregations, refreshed every few minutes.
3. "Compare materialized views vs application-level caching" — Materialized views are managed by the database and survive application restarts. Application caches offer more flexibility but require invalidation logic.

Related Concepts

• Database Indexing — index materialized views for fast lookups
• Normalization vs Denormalization — materialized views are a form of controlled denormalization
• Read Replicas — offload expensive materialized view refreshes to a replica
• Change Data Capture — trigger materialized view refresh on data changes
• System Design Interview Guide
• Algoroq Pricing — access all concept deep-dives