Materialized Views
Database views that physically store their query results on disk, trading freshness for dramatically faster read performance on expensive aggregations.
Description
A materialized view executes its underlying query once and stores the results as a physical table. Subsequent reads hit the stored data instead of re-executing the query, providing the same performance as a regular table. This is ideal for expensive analytical queries (complex JOINs, aggregations over millions of rows) that are read frequently but do not need real-time freshness. In PostgreSQL, you create one with CREATE MATERIALIZED VIEW and refresh it with REFRESH MATERIALIZED VIEW.
The key trade-off is staleness: materialized view data is only as fresh as the last refresh. REFRESH MATERIALIZED VIEW acquires an ACCESS EXCLUSIVE lock, blocking all reads during the refresh. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids this lock by maintaining a temporary copy and swapping, but it requires a unique index on the view and takes longer to execute. Choose a refresh strategy based on your freshness requirements: on-demand (trigger after data changes), scheduled (cron job every 5-15 minutes), or change-data-capture driven.
Materialized views can be indexed just like regular tables, further accelerating read performance. Use them for dashboards, leaderboards, search results, and any read path where computing the result on every request is too expensive. Monitor refresh duration and schedule refreshes during low-traffic periods. If a materialized view's refresh takes too long, consider incremental materialization using application-level change tracking or tools like pg_ivm (incremental view maintenance for PostgreSQL).
Prompt Snippet
Create materialized views for dashboard aggregations and reporting queries that scan more than 100K rows. Add a unique index to enable REFRESH MATERIALIZED VIEW CONCURRENTLY, which allows reads during refresh. Schedule refreshes via pg_cron (CREATE EXTENSION pg_cron) at an interval matching your freshness SLA -- e.g., every 5 minutes for near-real-time dashboards. Monitor refresh duration in pg_stat_user_tables and alert if it exceeds 50% of the refresh interval. For sub-second freshness requirements, use application-level caching (Redis) instead of materialized views.
Tags
Related Terms
Database Views
Named SQL queries stored in the database that act as virtual tables, simplifying complex queries and providing a stable abstraction layer over the underlying schema.
Denormalization Strategies
Intentionally introducing redundant data into a normalized schema to optimize read performance by reducing expensive JOIN operations.
Database Indexing Strategies
Creating and managing indexes to accelerate query performance while balancing write overhead and storage costs.