Views and Materialized Views

# CHAPTER 17

Views and Materialized Views #

1. Introduction #

2. Learning Objectives #

• Understand the concept of Database Abstraction.

• Use CREATE VIEW to save complex queries as virtual tables.

• Query a View just like a normal table.

• Understand the performance differences between Standard Views and Materialized Views.

• Create and refresh Materialized Views.

3. Creating a Standard View #

Let's save our complex joining logic:

CREATE VIEW active_sales_report AS
SELECT 
    u.first_name, 
    u.email, 
    p.product_name, 
    o.order_date, 
    oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'Completed';

4. Querying a View #

-- The complex logic is completely hidden!
SELECT * FROM active_sales_report 
WHERE order_date = CURRENT_DATE;

*(Every time you run this SELECT, PostgreSQL secretly unpacks the View and runs the massive 4-table join behind the scenes).*

5. Security Benefits of Views #

You do NOT give the interns access to the employees table. Instead, you create a View:

CREATE VIEW public_employee_directory AS
SELECT first_name, last_name, email, department 
FROM employees;

You give the interns access to publicemployeedirectory. They can query it all day long, but it is physically impossible for them to access the SSN column!

6. The Problem with Standard Views #

7. Materialized Views (The Performance Turbocharger) #

CREATE MATERIALIZED VIEW monthly_financial_summary AS
SELECT 
    category, 
    SUM(price * quantity) AS total_revenue
FROM orders 
JOIN products...
GROUP BY category;

*(Because the data is pre-calculated and saved, querying monthlyfinancialsummary takes 1 millisecond, even if the underlying tables have 50 million rows!)*

8. Refreshing Materialized Views #

-- Run this every night at 2:00 AM using a cron job
REFRESH MATERIALIZED VIEW monthly_financial_summary;

9. Common Mistakes #

• Trying to INSERT into a View: In most cases, Views are Read-Only. If you try to run INSERT INTO activesalesreport..., PostgreSQL will throw an error because the "table" is just a virtual projection of 4 different tables.

• Overusing Materialized Views: Do not use Materialized Views for data that must be accurate up to the exact second (like a user's bank account balance). Only use them for historical reporting where a 24-hour delay is acceptable.

10. Best Practices #

• Use CONCURRENTLY: When you run REFRESH MATERIALIZED VIEW, PostgreSQL completely locks the table, preventing anyone from reading it while it rebuilds. If you create a UNIQUE INDEX on the view, you can use REFRESH MATERIALIZED VIEW CONCURRENTLY. This rebuilds the data in the background without locking the table!

11. Exercises #

1. 1. Write the SQL to create a standard View named bannedusers that only selects users where isbanned = TRUE.

1. 2. Why does querying a standard View on a massive dataset still result in slow performance?

12. SQL Challenges #

DROP MATERIALIZED VIEW IF EXISTS old_yearly_report;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Describe a business reporting scenario where you would strongly advocate for creating a Materialized View instead of a standard View.

• Q: Explain the mechanical limitations of a Materialized View. How do you handle the problem of data staleness?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #