What is the difference between a regular view and a materialized view?

Regular view: Saved query, no data stored, executes every time you query it, always current, no performance benefit. Materialized view: Saved query results stored on disk, fast queries, data can be stale, requires refresh. Use regular views for security (hide columns) and abstraction (simplify queries). Use materialized views for expensive queries that don't need real-time data (analytics, reporting). Example: Employee directory view (regular) vs daily sales dashboard (materialized).

When would you use a materialized view vs denormalization?

Materialized view: Database-managed, easy to refresh, good for analytics/reporting where staleness is acceptable. Refresh periodically (hourly, daily). Denormalization: Application-managed, real-time consistency, good for operational queries. Update on every write. Trade-offs: Materialized views are simpler (database handles refresh) but data is stale. Denormalization is more complex (triggers/application logic) but data is always current. Choose based on staleness tolerance and update frequency.

How do you refresh a materialized view without blocking queries?

Use REFRESH MATERIALIZED VIEW CONCURRENTLY (PostgreSQL). This creates a new version in background, then atomically swaps it. Queries continue reading old version during refresh. Requires unique index on materialized view. Alternative: Use two materialized views, refresh one while queries use the other, then swap. Without CONCURRENTLY, refresh locks the materialized view and blocks all queries.

Create a materialized view for a product sales report that shows total sales by product category for the last 30 days. Include refresh strategy.

CREATE MATERIALIZED VIEW product_category_sales AS

SELECT

p.category,

COUNT(DISTINCT o.order_id) as total_orders,

SUM(oi.quantity) as total_quantity,

SUM(oi.quantity * oi.price) as total_revenue,

AVG(oi.price) as avg_price

FROM products p

JOIN order_items oi ON p.product_id = oi.product_id

JOIN orders o ON oi.order_id = o.order_id

WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'

GROUP BY p.category

ORDER BY total_revenue DESC;

-- Create index for fast queries

CREATE INDEX idx_category_sales_revenue

ON product_category_sales(total_revenue DESC);

-- Refresh hourly using pg_cron

SELECT cron.schedule(

'refresh-category-sales',

'0 * * * *',

'REFRESH MATERIALIZED VIEW CONCURRENTLY product_category_sales'

);

-- Query (fast)

SELECT * FROM product_category_sales;

Materialized view pre-computes expensive aggregation across 3 tables. Index on total_revenue enables fast sorting. CONCURRENTLY refresh avoids blocking queries. Hourly refresh keeps data reasonably current for reporting. Query time: 45 seconds -> 10ms.