Views & Materialized Views

A view is a saved SQL query that we can use like a table. It doesn’t store any data — it just runs the query every time we access it. A materialized view actually stores the result on disk, like a cached snapshot.

Think of it like this: a view is a saved recipe (we cook fresh every time), and a materialized view is a meal-prepped container (already cooked, just reheat).

Regular Views

Creating a View

-- Create a view that shows employee details with department names
CREATE VIEW employee_details AS
SELECT
    e.id,
    e.name,
    e.email,
    e.salary,
    d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Now we can query it like a regular table
SELECT * FROM employee_details WHERE department = 'Engineering';

-- Under the hood, the database runs the full JOIN query every time

Why Use Views?

1. Simplify complex queries — write the complex JOIN once, use the simple view name everywhere
2. Security — expose only certain columns to certain users (hide salary, SSN, etc.)
3. Abstraction — if the underlying table structure changes, we update the view definition and all dependent queries keep working
4. Consistency — everyone uses the same business logic (e.g., “active users” always means the same thing)

-- Security: create a view that hides sensitive data
CREATE VIEW public_employees AS
SELECT id, name, department_id
FROM employees;
-- No salary, no email, no SSN

GRANT SELECT ON public_employees TO analyst_role;
-- Analysts can query this view but not the underlying table

Updating Through Views

Simple views can sometimes be updated (INSERT, UPDATE, DELETE through the view), but it only works if the view maps directly to a single underlying table without aggregations or JOINs.

-- This view is updatable (simple, single table)
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = true;

-- We can insert through it
INSERT INTO active_users (name, email, is_active)
VALUES ('Manish', 'manish@example.com', true);

-- WITH CHECK OPTION prevents inserting rows that don't match the view's filter
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = true
WITH CHECK OPTION;

-- This would FAIL (violates the WHERE condition)
INSERT INTO active_users (name, email, is_active)
VALUES ('Ghost', 'ghost@example.com', false);

Dropping and Modifying Views

-- Replace an existing view (or create if it doesn't exist)
CREATE OR REPLACE VIEW employee_details AS
SELECT e.id, e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- Drop a view
DROP VIEW IF EXISTS employee_details;

Materialized Views

A materialized view stores the query result physically on disk. When we query it, the database reads from the stored result — no recomputation needed.

This is huge for performance when we have expensive queries (complex joins, aggregations over millions of rows) that don’t need to be real-time.

Creating a Materialized View

-- Create a materialized view for a slow analytics query
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- This query runs ONCE and stores the result
-- Subsequent reads are instant — just reading from stored data
SELECT * FROM monthly_revenue WHERE month >= '2024-01-01';

Refreshing Materialized Views

The data in a materialized view gets stale. We need to refresh it periodically.

-- Full refresh: recomputes the entire view
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Concurrent refresh: allows reads during refresh (needs a UNIQUE index)
CREATE UNIQUE INDEX idx_monthly_revenue_month ON monthly_revenue(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Refresh Strategies

There are several ways to keep materialized views up to date:

• Manual refresh — trigger it from our app or a cron job after a data load
• Scheduled refresh — use pg_cron or a similar scheduler to refresh every N minutes/hours
• On-demand refresh — refresh when a user requests the dashboard (with a staleness check)
• Trigger-based — use a database trigger to refresh after inserts (careful with performance)

-- Example: refresh every hour using pg_cron (PostgreSQL extension)
-- SELECT cron.schedule('refresh-monthly-revenue', '0 * * * *',
--     'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue');

View vs Materialized View

When to Use Each

Use a regular view when:

• We want to simplify a complex query for reuse
• We need real-time, always-current data
• We want to restrict column access for security
• The underlying query is fast enough

Use a materialized view when:

• The query is expensive (complex joins, heavy aggregations)
• We can tolerate slightly stale data
• It’s for dashboards, reports, or analytics
• The underlying data doesn’t change every second

Interview Tip

Know the difference between views and materialized views cold — it’s a common question. The key distinction: views are virtual (re-run every time), materialized views are physical (stored, need refreshing). Bonus: mention REFRESH MATERIALIZED VIEW CONCURRENTLY as a way to avoid blocking reads during refresh.