Views & Materialized Views

Views

A view is a stored query that behaves like a table and holds no data of its own.

CREATE VIEW v_student_summary AS
SELECT
  s.id, s.name_en, s.cgpa,
  d.name_en AS department,
  count(e.id) AS courses_taken
FROM students s
LEFT JOIN departments d ON s.department = d.code
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, d.name_en;

SELECT * FROM v_student_summary WHERE cgpa > 3.5;

CREATE OR REPLACE VIEW v_active AS SELECT * FROM students WHERE status = 'active';
DROP VIEW IF EXISTS v_active;
\dv

CREATE OR REPLACE can add columns at the end but cannot remove or reorder existing ones — for that you must drop and recreate, which fails if other views depend on it.

Why use views

• Simplify — hide a five-table join behind one name.
• Secure — grant access to a view that excludes salary or national ID, without granting access to the base table.
• Stabilise — restructure the underlying tables while the view's output stays the same.
• Standardise — one shared definition of "active customer".

Updatable views

A simple view over one table with no aggregation, DISTINCT, GROUP BY or window functions is automatically updatable:

CREATE VIEW v_cse AS
SELECT id, name_en, cgpa, department FROM students WHERE department = 'CSE'
WITH CHECK OPTION;

UPDATE v_cse SET cgpa = 3.9 WHERE id = 1;                       -- works
INSERT INTO v_cse (name_en, department) VALUES ('X', 'EEE');    -- rejected by CHECK OPTION

For complex views, write an INSTEAD OF trigger and you can make any view writable.

Materialized views

A materialized view stores the result on disk. This is the real caching tool Postgres has and MySQL does not.

CREATE MATERIALIZED VIEW mv_dept_stats AS
SELECT department, count(*) AS total, round(avg(cgpa),2) AS avg_cgpa
FROM students
GROUP BY department
WITH DATA;

SELECT * FROM mv_dept_stats;   -- instant, no recomputation

The data is a snapshot and goes stale until you refresh it:

REFRESH MATERIALIZED VIEW mv_dept_stats;

-- refresh without blocking readers (requires a unique index)
CREATE UNIQUE INDEX ON mv_dept_stats (department);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_stats;

CONCURRENTLY is slower overall but keeps the view readable throughout, which is what you want in production. Schedule refreshes with cron or pg_cron.

You can index a materialized view like a normal table — often the point of creating one.

Choosing between them

Need	Use
Always-current data, cheap query	View
Expensive aggregate read many times, slightly stale is fine	Materialized view
Expensive aggregate that must be exact	Summary table maintained by triggers

A plain view costs exactly as much as its underlying query every time you read it. If a dashboard is slow because of a view, a materialized view is usually the fix.