Views vs Materialized Views
Overview
If you have a massive, complex query with 5 Joins and 3 CTEs that you use constantly (like a 'Daily Active Users Dashboard'), typing it out every time is tedious. A VIEW is a saved SQL query that acts like a virtual table. You query the View, and it runs the underlying SQL on the fly. A MATERIALIZED VIEW goes a step further: it actually runs the complex query, takes the results, and physically saves them to the hard drive, making subsequent reads instantaneous.
Syntax
-- 1. Standard VIEW (Virtual - runs the query on the fly every time)
CREATE VIEW active_customer_emails AS
SELECT c.name, c.email, MAX(o.created_at) as last_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name, c.email;
-- You can now query it just like a normal table!
SELECT * FROM active_customer_emails WHERE name = 'Alice';
-- 2. MATERIALIZED VIEW (Physical - caches the result to disk!)
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT month, SUM(amount) as revenue
FROM massive_sales_table
GROUP BY month;
-- It's fast because it's cached, BUT you must manually refresh it when data changes!
REFRESH MATERIALIZED VIEW monthly_sales_summary;Common Pitfalls
- Querying a Standard View for performance. Standard Views offer ZERO performance benefits. They are purely for developer convenience and security (hiding sensitive columns). If the underlying query takes 10 seconds to run, querying the View will take exactly 10 seconds.
- Stale Materialized Views. Because Materialized Views save a physical snapshot of the data, if a user places a new order, the view is instantly out-of-date. You must set up a CRON job or Database Trigger to run
REFRESH MATERIALIZED VIEWon a schedule.
Interview Questions
VIEW be used as a security mechanism?You can create a View over the users table that explicitly excludes the password_hash and ssn columns. You then revoke all access to the main users table, and only grant Data Analysts access to query the restricted View.
Real-World Example
Using Materialized Views to build lightning-fast Analytical Dashboards without crushing the production database.
-- The CEO's dashboard queries this table instantly.
-- A backend CRON job refreshes this at 3:00 AM every night when traffic is low.
REFRESH MATERIALIZED VIEW CONCURRENTLY executive_dashboard_summary;Check Your Knowledge
Test your understanding of Views vs Materialized Views with these quick questions.