Views & Materialized Views

A View is a virtual table based on the result-set of an SQL statement. It looks and acts like a table but does not store data itself.

Types of Views

1. Simple View: Based on a single table. Often updatable.
2. Complex View: Contains joins, aggregates, or multiple tables. Usually read-only.
3. Materialized View: A physical copy of the query result stored on disk. It must be refreshed manually or on a schedule.

Creating a View

CREATE VIEW ActiveCustomers AS
SELECT id, name, email, last_purchase_date
FROM customers
WHERE status = 'active';

Once created, you query it like a regular table:

SELECT * FROM ActiveCustomers WHERE last_purchase_date > '2024-01-01';

Why Use Views?

• Security: Show only specific columns/rows — users can access the view without knowing the underlying table structure.
• Simplicity: Hide complex joins behind a simple name.
• Consistency: Provide a stable interface even if the underlying table structure changes.
• Reusability: Write a complex query once and reference it from multiple applications.

Updatable Views

A view is updatable if it is based on a single table without aggregates, DISTINCT, GROUP BY, or set operations:

CREATE VIEW HighValueOrders AS
SELECT * FROM orders WHERE amount > 1000;

-- This updates the underlying orders table
UPDATE HighValueOrders SET status = 'processed' WHERE id = 42;

Materialized Views

A materialized view stores the query result physically on disk. It is much faster than a regular view for complex queries, but the data can become stale.

-- PostgreSQL
CREATE MATERIALIZED VIEW MonthlySales AS
SELECT EXTRACT(YEAR FROM sale_date) AS year,
       EXTRACT(MONTH FROM sale_date) AS month,
       SUM(amount) AS total
FROM sales
GROUP BY year, month;

-- Refresh on demand
REFRESH MATERIALIZED VIEW MonthlySales;

Use cases for materialized views:

• Pre-computed dashboards and reports.
• Data warehouse aggregates.
• Caching expensive joins for read-heavy systems.

View Limitations

• Performance: Every query against a regular view runs the underlying query — if the base query is slow, the view is slow.
• Nesting: Views based on other views can become unmanageable and perform poorly.
• No indexes: Regular views cannot be indexed directly (the underlying tables can be indexed).

Regular View vs Materialized View vs Table

CREATE VIEW DailyRevenue AS
SELECT sale_date, SUM(amount) as total_revenue
FROM sales
GROUP BY sale_date;

CREATE MATERIALIZED VIEW MonthlyStats AS
SELECT month, category, COUNT(*)
FROM orders
GROUP BY month, category;

-- Refreshing the data
REFRESH MATERIALIZED VIEW MonthlyStats;

CREATE VIEW ActiveUsers AS SELECT * FROM users WHERE status = 'active';

-- This will update the 'users' table
UPDATE ActiveUsers SET last_login = NOW() WHERE id = 101;

CREATE VIEW HighValueCustomers AS
SELECT * FROM CustomerStats WHERE total_spent > 1000;

DROP VIEW IF EXISTS DailyRevenue;

CREATE VIEW PublicUserData AS
SELECT id, name, city FROM users;
-- email, password, phone are hidden