PostgreSQL Views and Materialized Views

1. What You'll Learn

• CREATE VIEW / ALTER VIEW / DROP VIEW
• Updatable views (simple views support INSERT/UPDATE/DELETE)
• WITH CHECK OPTION to prevent rows from escaping the view
• Materialized views (MATERIALIZED VIEW)—a PostgreSQL specialty
• REFRESH MATERIALIZED VIEW / CONCURRENTLY
• Choosing between views, materialized views, and temp tables

2. The Story

Alice is a data engineer at an e-commerce platform. The operations team reviews a sales summary report every day at 8:00, which joins 5 tables, aggregates 3 million order records, and takes 2 hours to query.

Her boss said: "Can the report come back instantly?"

Alice defined the query as a materialized view, refreshed automatically every day at 4:00 AM. Querying the precomputed report dropped from 2 hours to 3 seconds. But materialized-view data isn't real-time—that's the trade-off between regular views and materialized views.

3. Concept: Regular Views

(1) CREATE VIEW Syntax

CREATE [OR REPLACE] VIEW view_name [(column_aliases)] AS
  SELECT ...;

A view is a stored query definition—it stores no data. Each time you query a view, PostgreSQL expands (rewrites) the view definition into the underlying query and executes it.

▶ Example: Create a Sales Summary View

CREATE VIEW v_daily_sales AS
SELECT
  order_date,
  region,
  COUNT(*)               AS order_count,
  SUM(amount)            AS total_revenue,
  AVG(amount)            AS avg_order_value
FROM orders
GROUP BY order_date, region;

Output:

 count 
-------
     5
(1 row)

▶ Example: Query the View

SELECT * FROM v_daily_sales
WHERE order_date >= '2025-01-01'
ORDER BY total_revenue DESC;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Equivalent to writing the underlying SELECT directly—PostgreSQL expands it automatically.

(2) ALTER VIEW and DROP VIEW

▶ Example: Modify and Drop a View

ALTER VIEW v_daily_sales RENAME TO v_daily_summary;

DROP VIEW IF EXISTS v_daily_summary CASCADE;

Output:

-- SQL statement executed successfully

(3) View Expansion Mechanism

flowchart LR
    A["SELECT * FROM v_daily_sales"] --> B["Rewrite<br/>expand view definition"]
    B --> C["SELECT order_date, region, COUNT(*)...<br/>FROM orders<br/>GROUP BY ..."]
    C --> D[Optimizer]
    D --> E[Execute]
    style B fill:#fff9c4
    style D fill:#c8e6c9

flowchart LR
    A["SELECT * FROM v_daily_sales"] --> B["Rewrite<br/>expand view definition"]
    B --> C["SELECT order_date, region, COUNT(*)...<br/>FROM orders<br/>GROUP BY ..."]
    C --> D[Optimizer]
    D --> E[Execute]
    style B fill:#fff9c4
    style D fill:#c8e6c9

4. Concept: Updatable Views

(1) Which Views Are Updatable

In PostgreSQL, simple views that meet all the following conditions are automatically updatable (support INSERT / UPDATE / DELETE):

▶ Example: Updatable View

CREATE VIEW v_active_users AS
SELECT user_id, name, email, status
FROM users
WHERE status = 'active';

Output:

CREATE TABLE

UPDATE v_active_users SET name = 'Alice Wang' WHERE user_id = 1;

DELETE FROM v_active_users WHERE user_id = 99;

INSERT INTO v_active_users (user_id, name, email, status)
VALUES (101, 'Charlie', 'charlie@example.com', 'active');

(2) WITH CHECK OPTION

By default, rows updated/inserted through a view can "escape" the view's scope (e.g., change status to 'inactive', and that row no longer appears in the view). WITH CHECK OPTION forbids such escaping.

▶ Example: WITH CHECK OPTION Prevents Escaping

CREATE VIEW v_active_users_strict AS
SELECT user_id, name, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

UPDATE v_active_users_strict SET status = 'inactive' WHERE user_id = 1;

ERROR: new row violates check option for view "v_active_users_strict"
DETAIL: Failing row contains (1, ..., inactive).

▶ Example: Insert into Updatable View Then Query

INSERT INTO v_active_users (user_id, name, email, status)
VALUES (200, 'Bob', 'bob@example.com', 'active');

SELECT * FROM v_active_users WHERE user_id = 200;

 user_id | name |       email        | status
---------+------+--------------------+--------
     200 | Bob  | bob@example.com    | active

5. Concept: Materialized Views

(1) MATERIALIZED VIEW Overview

A materialized view actually stores the query result on disk; queries read the precomputed data directly, without re-executing the underlying query. This is a PostgreSQL specialty.

▶ Example: Create a Materialized View

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
  DATE_TRUNC('month', order_date)::date AS month,
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date), region
WITH DATA;

Output:

 count 
-------
     5
(1 row)

▶ Example: Query the Materialized View

SELECT * FROM mv_monthly_sales
WHERE month >= '2025-01-01'
ORDER BY total_revenue DESC;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Returns in milliseconds, because the data is already precomputed and stored.

(2) REFRESH

CONCURRENTLY is a PostgreSQL specialty: the view stays queryable during refresh—business isn't blocked.

▶ Example: Full Refresh

REFRESH MATERIALIZED VIEW mv_monthly_sales;

Output:

-- SQL statement executed successfully

All SELECTs on this materialized view are blocked during the refresh.

▶ Example: CONCURRENTLY Incremental Refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

Output:

-- SQL statement executed successfully

Prerequisite: the materialized view must have at least one unique index.

CREATE UNIQUE INDEX idx_mv_monthly_sales_pk
  ON mv_monthly_sales (month, region);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

(3) WITH DATA vs WITH NO DATA

▶ Example: Deferred Population

CREATE MATERIALIZED VIEW mv_expensive_report AS
SELECT ... FROM ... WITH NO DATA;

REFRESH MATERIALIZED VIEW mv_expensive_report;

Output:

CREATE TABLE

▶ Example: Automatic Scheduled Refresh (pg_cron extension)

SELECT cron.schedule(
  'refresh_monthly_sales',
  '0 4 * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales$$
);

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Refreshes automatically every day at 4:00 AM.

6. Views vs Materialized Views vs Temp Tables

classDiagram
    class View {
        +stores query text
        +real-time data
        +simple view DML-able
        +WITH CHECK OPTION
    }
    class MaterializedView {
        +stores query text+data
        +REFRESH refresh
        +CONCURRENTLY
        +indexable
        +WITH DATA/NO DATA
    }
    class TempTable {
        +stores data only
        +gone after session
        +fully DML-able
        +indexable
    }
    View <|-- MaterializedView : extends
    MaterializedView ..|> TempTable : similar perf

classDiagram
    class View {
        +stores query text
        +real-time data
        +simple view DML-able
        +WITH CHECK OPTION
    }
    class MaterializedView {
        +stores query text+data
        +REFRESH refresh
        +CONCURRENTLY
        +indexable
        +WITH DATA/NO DATA
    }
    class TempTable {
        +stores data only
        +gone after session
        +fully DML-able
        +indexable
    }
    View <|-- MaterializedView : extends
    MaterializedView ..|> TempTable : similar perf

7. View Management Best Practices

(1) Naming Conventions

(2) View Dependencies and Security

▶ Example: Column-Level Privileges with a View

CREATE VIEW v_user_public AS
SELECT user_id, name
FROM users;

GRANT SELECT ON v_user_public TO reporter_role;

REVOKE SELECT ON users FROM reporter_role;

Output:

CREATE TABLE

reporter_role can only see user_id and name, not sensitive columns like email.

8. Comprehensive Example

Alice's report optimization—materialized-view precomputation + CONCURRENTLY refresh + view to simplify queries:

CREATE MATERIALIZED VIEW mv_sales_report AS
SELECT
  DATE_TRUNC('month', o.order_date)::date AS month,
  p.category,
  o.region,
  COUNT(*)                                AS order_count,
  COUNT(DISTINCT o.customer_id)           AS unique_customers,
  SUM(o.amount)                           AS total_revenue,
  SUM(o.amount) FILTER (WHERE o.amount >= 50000) AS big_deal_revenue,
  AVG(o.amount)                           AS avg_order_value,
  SUM(oi.quantity * oi.unit_price)        AS total_gmv
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY DATE_TRUNC('month', o.order_date), p.category, o.region
WITH DATA;

CREATE UNIQUE INDEX idx_mv_sales_report_pk
  ON mv_sales_report (month, category, region);

CREATE VIEW v_sales_dashboard AS
SELECT
  month,
  region,
  SUM(total_revenue)  AS region_revenue,
  SUM(order_count)    AS region_orders,
  SUM(unique_customers) AS region_customers
FROM mv_sales_report
GROUP BY month, region;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_report;

9. Execution Flow

The creation and refresh flow of a materialized view:

flowchart TD
    A[CREATE MATERIALIZED VIEW] --> B[Execute underlying query]
    B --> C[Write results to disk]
    C --> D[Can create indexes]
    D --> E[Query reads disk data directly]
    E --> F{Need refresh?}
    F -->|REFRESH| G[Re-execute underlying query]
    G --> H[Replace old data]
    H --> E
    F -->|CONCURRENTLY| I[Incremental compare refresh]
    I --> J[Doesn't block reads]
    J --> E

    style A fill:#e1f5fe
    style E fill:#c8e6c9
    style I fill:#fff9c4

flowchart TD
    A[CREATE MATERIALIZED VIEW] --> B[Execute underlying query]
    B --> C[Write results to disk]
    C --> D[Can create indexes]
    D --> E[Query reads disk data directly]
    E --> F{Need refresh?}
    F -->|REFRESH| G[Re-execute underlying query]
    G --> H[Replace old data]
    H --> E
    F -->|CONCURRENTLY| I[Incremental compare refresh]
    I --> J[Doesn't block reads]
    J --> E

    style A fill:#e1f5fe
    style E fill:#c8e6c9
    style I fill:#fff9c4

❓ FAQ

📖 Summary

• A regular view only stores query text, expands on query, data always real-time
• Simple views (single table, no aggregation) are automatically updatable, support INSERT/UPDATE/DELETE
• WITH CHECK OPTION prevents DML from causing rows to escape the view's scope
• A materialized view stores the query result on disk, extremely fast but not real-time
• REFRESH MATERIALIZED VIEW does a full refresh; CONCURRENTLY does an incremental refresh that doesn't block reads
• CONCURRENTLY refresh prerequisite: the materialized view must have a unique index
• Views can enforce column-level privileges, hiding sensitive columns

📝 Exercises

1. ⭐ Create a view v_recent_orders that queries orders from the last 30 days, including customer_name and product_name.
2. ⭐ Create an updatable view v_active_customers that shows only customers with status = 'active', and add WITH CHECK OPTION.
3. ⭐⭐ Create a materialized view mv_daily_category_sales that summarizes sales by day and category, and add a unique index to support CONCURRENTLY refresh.
4. ⭐⭐ Design a nested-view scheme: a materialized view precomputes detail data, and a regular view does dimension aggregation on top of the materialized view.
5. ⭐⭐⭐ Write a pg_cron scheduled job that refreshes mv_daily_category_sales every day at 3:00 AM, and logs an alert when the refresh takes longer than 5 minutes.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us