Views and Materialized Views

Introduction

A view is a saved SELECT that looks like a table—useful for simplifying joins, hiding columns, or granting read-only access. Materialized views store query results on disk and refresh on demand—faster reads, stale until refreshed. This chapter creates, queries, updates, and drops both on your users / posts schema. Compare with MySQL views—PostgreSQL adds native MATERIALIZED VIEW.

Prerequisites

• Joins and CTEs
• SELECT Queries Basics
• Sample data in users and posts

Create a View

Published posts with author email:

CREATE VIEW v_published_posts AS
SELECT
  p.id AS post_id,
  p.title,
  p.published_at,
  u.display_name AS author,
  u.email AS author_email
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL;

Query like a table:

SELECT post_id, title, author
FROM v_published_posts
ORDER BY published_at DESC
LIMIT 5;

Code explanation:

• A normal view stores definition only—runs the underlying query each time
• Base table changes appear immediately in view results

Inspect Views

-- psql
\d v_published_posts
 
-- SQL
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public' AND table_name = 'v_published_posts';

List views:

-- psql
\dv
 
SELECT schemaname, viewname FROM pg_views WHERE schemaname = 'public';

Replace or Alter View

Replace entire definition:

CREATE OR REPLACE VIEW v_published_posts AS
SELECT
  p.id AS post_id,
  p.title,
  p.published_at,
  u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL
  AND u.is_active = TRUE;

Rename:

ALTER VIEW v_published_posts RENAME TO v_posts_published;
ALTER VIEW v_posts_published RENAME TO v_published_posts;

Change owner or schema (permissions chapter):

-- ALTER VIEW v_published_posts SET SCHEMA reporting;

Drop View

DROP VIEW IF EXISTS v_published_posts;

Does not delete base table data.

Updatable Views (Limited)

Simple views on one table may allow INSERT/UPDATE/DELETE if PostgreSQL can map rows to a single base table:

CREATE VIEW v_active_users AS
SELECT id, email, display_name
FROM users
WHERE is_active = TRUE;

UPDATE v_active_users SET display_name = 'New Name' WHERE id = 1;

Complex joins, aggregates, DISTINCT, window functions, or expressions in the select list make the view read-only.

Check updatability:

SELECT table_name, is_updatable, is_insertable_into
FROM information_schema.views
WHERE table_schema = 'public' AND table_name = 'v_active_users';

Security Use Case

Grant SELECT on a view only—hide email from analysts:

CREATE VIEW v_posts_public AS
SELECT p.id, p.title, p.published_at, u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL;
 
-- GRANT SELECT ON v_posts_public TO analyst_role;

See Roles and Permissions.

Materialized Views

Store results physically—good for expensive aggregations refreshed periodically:

CREATE MATERIALIZED VIEW mv_posts_per_author AS
SELECT
  u.id AS user_id,
  u.display_name,
  COUNT(p.id) AS post_count,
  MAX(p.published_at) AS last_published
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.display_name;

Query instantly (until refresh):

SELECT * FROM mv_posts_per_author ORDER BY post_count DESC;

Refresh data from base tables:

REFRESH MATERIALIZED VIEW mv_posts_per_author;

Concurrent refresh (allows reads during rebuild—requires unique index):

CREATE UNIQUE INDEX ON mv_posts_per_author (user_id);
 
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_posts_per_author;

Code explanation:

• Without CONCURRENTLY, refresh takes an exclusive lock—readers block briefly
• Stale data between refreshes—acceptable for dashboards, not for real-time balances

Drop:

DROP MATERIALIZED VIEW IF EXISTS mv_posts_per_author;

View vs Materialized View

MySQL has no built-in materialized view—teams use summary tables or external OLAP stores.

CTE vs View

Use views when multiple apps or roles share the same definition.

FAQ

View vs table?

A view is a named query—no separate row storage (except materialized). SELECT through a view hits base tables.

When to use materialized views?

Heavy joins or aggregates on read-heavy dashboards where slightly stale data is OK—refresh nightly or on a schedule.

CREATE OR REPLACE and dependencies?

Replacing a view that changed column names can break dependent views—test CREATE OR REPLACE in migrations.

Can I index a view?

Not a normal view. Materialized views support indexes like tables—add indexes after create for filter/join performance.

Updatable view failed?

Check information_schema.views. Joins and aggregates require INSTEAD OF triggers or write to base tables.

Refresh in production?

Use CONCURRENTLY with a unique index to avoid long read locks; schedule during low traffic if non-concurrent.