SQL Views and Materialized Views

SQL Views and Materialized Views

A view is a virtual table defined by a SQL query. It provides abstraction, simplifies complex queries, and enhances security by exposing only specific data to users.

Creating a View

CREATE VIEW active_employees AS
SELECT e.id, e.name, e.email, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.is_active = true;

SELECT * FROM active_employees WHERE dept_name = 'Engineering';

Updatable Views

Simple views can support INSERT, UPDATE, and DELETE:

CREATE VIEW engineering_team AS
SELECT id, name, email, salary
FROM employees
WHERE department = 'Engineering'
WITH CHECK OPTION;

UPDATE engineering_team SET salary = salary * 1.1 WHERE name = 'Alice';

WITH CHECK OPTION ensures modifications through the view still satisfy the view's WHERE condition.

Materialized Views (PostgreSQL)

Materialized views physically store the query result, trading freshness for performance:

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT DATE_TRUNC('month', sale_date) AS month,
       product_category,
       SUM(amount) AS total_sales,
       COUNT(*) AS transaction_count
FROM sales
GROUP BY DATE_TRUNC('month', sale_date), product_category;

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

Views vs Materialized Views

Feature	View	Materialized View
Storage	None (virtual)	Physical
Performance	Same as underlying query	Fast (precomputed)
Data freshness	Always current	Stale until refreshed
Indexable	No	Yes
Use case	Abstraction, security	Reporting, dashboards

Security with Views

CREATE VIEW public_products AS
SELECT name, description, price FROM products WHERE is_published = true;

GRANT SELECT ON public_products TO readonly_role;

Best Practices

• Use views to simplify complex joins and subqueries reused across queries
• Avoid deeply nested views (view referencing view referencing view) as they become hard to debug
• Use materialized views for expensive aggregations accessed frequently
• Schedule materialized view refreshes during low-traffic periods
• Name views descriptively to distinguish them from base tables

Views are an essential tool for database abstraction, access control, and query simplification.