丁久

Posted on May 17 • Originally published at dingjiu1989-hue.github.io

Materialized Views

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Materialized Views

What are Materialized Views?

Materialized views pre-compute and store query results. Unlike regular views, they persist data on disk, trading storage for query speed.

Creating Materialized Views

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- PostgreSQL: materialized view for daily sales

CREATE MATERIALIZED VIEW daily_sales_summary AS

SELECT

d.date,

p.category,

COUNT(*) as sale_count,

SUM(s.amount) as total_sales

FROM fact_sales s

JOIN dim_date d ON s.date_id = d.date_id

JOIN dim_product p ON s.product_id = p.product_id

GROUP BY d.date, p.category;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Add index for faster queries

CREATE INDEX idx_daily_sales_date ON daily_sales_summary(date);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Query is much faster than the original

SELECT * FROM daily_sales_summary

WHERE date >= '2026-01-01';

Refresh Strategies

Complete Refresh

Rebuilds the entire view:

REFRESH MATERIALIZED VIEW daily_sales_summary;

Simple but locks the view during refresh. Best for small datasets or low-frequency refreshes.

Concurrent Refresh

Creates a new version and swaps atomically. No lock but requires a unique index:

CREATE UNIQUE INDEX idx_daily_sales_pk ON daily_sales_summary(date, category);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

Incremental Refresh

Only processes changed data. Not natively supported in PostgreSQL but available in specialized databases.

Use Cases

| Use Case | Refresh Frequency | Benefit | |----------|------------------|---------| | Dashboards | Hourly | Sub-second queries | | Aggregations | Daily | Avoid full table scans | | Pre-joined data | On-demand | Eliminate expensive joins | | Reporting | Nightly | Consistent snapshot |

Performance Considerations

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Compare query performance

EXPLAIN ANALYZE

SELECT date, SUM(total_sales)

FROM daily_sales_summary

WHERE date >= '2026-01-01'

GROUP BY date;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- vs the base query (much slower)

EXPLAIN ANALYZE

SELECT d.date, SUM(s.amount)

FROM fact_sales s

JOIN dim_date d ON s.date_id = d.date_id

WHERE d.date >= '2026-01-01'

GROUP BY d.date;

Conclusion

Materialized views are essential for dashboard and reporting performance. Use concurrent refresh to avoid locks. Add indexes on frequently filtered columns. Choose refresh strategy based on freshness requirements. Monitor storage overhead for large views.

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Materialized Views

Materialized Views