Views and Materialized Views

• Views and materialized views are tools that allow you to create virtual or precomputed tables to simplify queries, improve security, and boost performance, especially in reporting and analytics workloads.

Views

• A View is a saved SQL query that behaves like a virtual table.

• It does not store data, only the query definition.

• Data is computed every time the view is queried.

• Useful for simplifying complex queries or restricting access.

-- Create a view to only show customers that are active
CREATE VIEW active_customers_vw AS
SELECT id, first_name, last_name, email
FROM customers
WHERE status = 'active';

-- Use the view
SELECT * FROM active_customers_vw;

-- Create a view to only show the columns in the view become visible to users.
CREATE VIEW public_products_vw AS
SELECT id, name, price
FROM products;

-- Use the view
SELECT * FROM public_products_vw;

View pro

• Always returns the most up-to-date data.

View con

• Can be slow if based on expensive queries.

When to Use a View

• To simplify repeated complex queries.

• To hide sensitive columns like salary or passwords.

• To give users access to a limited "window" of the data.

• To maintain consistent business logic in one place via query definition.

Materialized Views

• A Materialized View stores the result of a query physically on disk.

• It is like a snapshot of the data.

• It must be refreshed manually or at intervals.

• Much faster for read-heavy analytics.

• Ideal for expensive aggregation queries.

-- Create a materialized view
CREATE MATERIALIZED VIEW sales_summary_mvw AS
SELECT product_id, SUM(quantity) AS total_qty, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

-- Use the materialized view
SELECT * FROM sales_summary_mvw;

-- Refresh to update data
REFRESH MATERIALIZED VIEW sales_summary_mvw;

-- Create a materialized view
CREATE MATERIALIZED VIEW daily_signups_mvw AS
SELECT DATE(created_at) AS signup_date, COUNT(*) AS total
FROM customers
GROUP BY DATE(created_at);

-- Use the materialized view
SELECT * FROM daily_signups_mvw;

-- Refresh to update data
REFRESH MATERIALIZED VIEW daily_signups_mvw;

Materialized view pro

• Very fast for repeated reads.

• Great for dashboards and reporting.

Materialized view pro

• May show stale data until refreshed.

• Requires storage space.

When to Use a Materialized View

• The underlying data is huge.

• You run the same expensive query many times.

• Real-time accuracy is not required (Near-real-time is fine).

• You want fast dashboard performance (Power BI / Tableau / Looker).

Views vs Materialized Views

Feature	View	Materialized View
Stores data?	No	Yes
Performance	Slower (Computed each query)	Very fast
Always up to date?	Yes	No (Needs refresh)
Storage required	None	Requires storage
Best for	Security, abstraction	Reporting, analytics