# Postgres Materialized Views as Cache Before reaching for an external cache (Redis, Memcached), consider using Postgres materialized views. A materialized view stores the result of a query physically, returning cached data on subsequent reads until explicitly refreshed. ## Key Properties - **Creation**: `CREATE MATERIALIZED VIEW mv_name AS SELECT ...` - **Refresh**: `REFRESH MATERIALIZED VIEW mv_name` (can be scheduled via cron or triggered from app code) - **Concurrency**: `REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name` allows reads during refresh (requires a unique index) - **Trade-off**: Data staleness vs. query performance -- appropriate when slightly stale data is acceptable ## When to Use - Expensive aggregation queries that don't need real-time freshness - Dashboard or reporting queries run repeatedly - Replacing application-level caching that adds infrastructure complexity ## When NOT to Use - Data must be real-time (use regular views or optimize the query instead) - The underlying data changes so frequently that refresh cost exceeds cache benefit