Create and Optimize Materialized Views

This skill designs, creates, and optimizes materialized views across PostgreSQL, Oracle, SQL Server, and warehouses like Snowflake and BigQuery, weighing storage, refresh strategy, and query performance trade-offs. Materialized views fit expensive repeated aggregations, frequently accessed joins across large tables, rolling time-series calculations, predictable data-warehouse query patterns, and network-intensive cross-database queries. Refresh strategy is chosen deliberately: complete refresh for small datasets or heavily changed data, incremental/fast refresh for append-only data with proper logging, on-demand for ad-hoc analysis with flexible freshness needs, and scheduled refresh for regular reporting with known update patterns.

Use it when repeated expensive aggregations or joins need to be materialized with a deliberate refresh strategy, not a plain view recomputed on every query.

CREATE MATERIALIZED VIEW sales_monthly_summary AS
SELECT
    DATE_TRUNC('month', order_date) as month,
    product_category,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE order_date >= '2020-01-01'
GROUP BY DATE_TRUNC('month', order_date), product_category
WITH DATA;

Database-specific patterns cover Oracle fast refresh (a MATERIALIZED VIEW LOG ... WITH ROWID on each source table plus REFRESH FAST ON DEMAND), PostgreSQL rolling time-series windows (7-day and 30-day OVER (PARTITION BY ... ROWS BETWEEN ... PRECEDING) aggregates), PostgreSQL REFRESH MATERIALIZED VIEW CONCURRENTLY for non-blocking refreshes, and Snowflake CREATE DYNAMIC TABLE with a TARGET_LAG for auto-refreshing views without manual scheduling. Optimization strategies include partitioning very large materialized views by date range with a CHECK constraint, and storage optimization via tighter column types plus fillfactor tuning for views that see frequent updates. Maintenance patterns include a PL/pgSQL procedure refreshing dependent views in order and logging success/failure to an mv_refresh_log table, plus a monitoring query joining pg_stat_user_tables and pg_matviews to surface size and change-volume per view.

Database engineers and analytics teams designing materialized views who need concrete refresh-strategy and optimization patterns across PostgreSQL, Oracle, and Snowflake - not a single generic CREATE MATERIALIZED VIEW example. Key recommendations tie it together: always index to match actual query patterns, weigh refresh cost against query-performance gain, prefer incremental refresh to minimize resource usage where possible, monitor actual usage to confirm the view still earns its maintenance cost, plan explicitly for concurrent access during refresh, implement error handling and logging for refresh jobs, consider partitioning once a view gets very large, and document dependencies and refresh schedules for the teams that maintain it.