Views and Materialized Views

-- Regular view — query is re-run each time you SELECT from it
CREATE VIEW active_customers AS
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
WHERE  u.status = 'active'
GROUP  BY u.id, u.email;

SELECT * FROM active_customers WHERE order_count > 10;

-- Materialized view — result is cached on disk (PostgreSQL)
CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id, u.email,
       COUNT(o.id)    AS order_count,
       SUM(o.amount)  AS lifetime_value,
       MAX(o.created_at) AS last_order
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.email;

-- Index materialized views like a real table
CREATE INDEX idx_user_stats_ltv ON user_stats (lifetime_value DESC);

-- Refresh — blocks readers
REFRESH MATERIALIZED VIEW user_stats;
-- ... or refresh without blocking (requires a UNIQUE index on the MV)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;