Views and Materialized Views
CREATE VIEW, materialized views with REFRESH, CTEs with WITH
Skills taught: pg.views, pg.materialized-views, pg.cte
Views — stored queries
-- A view is a stored SELECT query — query it like a table
CREATE OR REPLACE VIEW published_posts AS
SELECT
p.id,
p.title,
p.created_at,
u.username AS author
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.published = TRUE;
-- Use the view like a table
SELECT * FROM published_posts ORDER BY created_at DESC LIMIT 10;
SELECT * FROM published_posts WHERE author = 'alice';
-- Views are virtual — no data stored, query runs every time
-- DROP VIEW IF EXISTS published_posts;
Materialized Views — cached query results
-- Materialized view: stores results on disk
-- Fast to query, but must be refreshed when data changes
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS orders,
SUM(total_price) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
-- Create index on the materialized view
CREATE INDEX idx_monthly_rev_month ON monthly_revenue(month);
-- Query it (data is from last refresh)
SELECT * FROM monthly_revenue WHERE month >= '2026-01-01';
-- Refresh the data (blocks reads during refresh)
REFRESH MATERIALIZED VIEW monthly_revenue;
-- Non-blocking refresh (keeps old data available during refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
-- Requires a UNIQUE index on the view
CTEs — readable complex queries
-- CTEs make complex queries readable
WITH
-- Step 1: get users with their order counts
user_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
),
-- Step 2: classify users
classified AS (
SELECT
u.id,
u.email,
uo.order_count,
CASE
WHEN uo.order_count >= 10 THEN 'VIP'
WHEN uo.order_count >= 3 THEN 'Regular'
ELSE 'New'
END AS tier
FROM users u
JOIN user_orders uo ON uo.user_id = u.id
)
SELECT tier, COUNT(*) AS count
FROM classified
GROUP BY tier;
Frequently Asked Questions
Should I use a CTE or a subquery?
CTEs are better for readability when you reference the result multiple times or when the logic has multiple steps. Subqueries are fine for one-off use. PostgreSQL 12+ inlines CTEs by default (optimizes them as subqueries), so performance is usually the same.