Views and materialized views

A view is a saved query that acts like a virtual table. Instead of writing the same complex 20-line JOIN every time, save it as a view and SELECT from it like a table. Materialized views go further: they cache the results for fast reads.

views.sql
sql
-- Create a reusable view for enriched order details
CREATE OR REPLACE VIEW v_order_details AS
SELECT
  o.order_id,
  o.order_status,
  o.order_purchase_timestamp,
  c.customer_city,
  c.customer_state,
  oi.product_id,
  oi.price,
  oi.freight_value,
  p.product_category_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- Now query it simply:
SELECT customer_state, SUM(price) AS revenue
FROM v_order_details
GROUP BY customer_state
ORDER BY revenue DESC;

CREATE OR REPLACE VIEW saves a query. The view is re-executed every time you query it, so results are always fresh.

Regular views re-execute the query every time. Materialized views store the results physically, making reads instant at the cost of needing a manual refresh.

materialized_views.sql
sql
-- Materialized view: cached results, fast reads
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_purchase_timestamp) AS month,
  COUNT(*) AS order_count,
  SUM(oi.price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_TRUNC('month', order_purchase_timestamp)
ORDER BY month;

-- Query is instant (reads cached data)
SELECT * FROM mv_monthly_revenue;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW mv_monthly_revenue;
-- CONCURRENTLY allows reads during refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Materialized views store results physically. Lightning fast reads but stale until you REFRESH. Great for dashboards.

Use a regular view when you need always-fresh data and the query is fast enough. Use a materialized view when the query is expensive (seconds to run) and you can tolerate slightly stale data. Common pattern: materialized views for dashboards, refreshed on a schedule (hourly/daily).

Matching exercise: Views concepts

Loading practice…

Quiz: Quiz

Loading practice…

Fill in the blanks: Create a view

Loading practice…

Code playground: Seller performance view

Loading practice…