Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 2 · Lesson 10 · 5 min read

PostgreSQL Views vs Materialized Views

On this page
  1. View vs materialized view
  2. How views work
  3. Updatable views and WITH CHECK OPTION
  4. Materialized views: a stored snapshot
  5. REFRESH MATERIALIZED VIEW CONCURRENTLY
  6. Check yourself

View vs materialized view

Your dashboard runs a three-table join that takes 40 seconds. Every page load runs it again. You can wrap the query in a view, which saves typing but still runs the full query every time. Or you can use a materialized view, which stores the result so reads are instant, but shows data only as fresh as its last refresh.

ViewMaterialized view
Stores dataNo, it's a saved queryYes, like a table
FreshnessAlways currentAs of the last refresh
Read speedSame as running the queryFast, and you can index it
WritableSimple ones, yesNo
Disk spaceNoneAs much as the result

Start with a regular view. Switch to a materialized view when the query is expensive and readers can live with data that's minutes or hours old.

How views work

A view stores the query, not its result. Each time you select from it, PostgreSQL slots the query in and runs it against the live tables.

SQL
CREATE TABLE orders (
  order_id     int PRIMARY KEY,
  customer_id  int NOT NULL,
  order_date   date NOT NULL,
  status       text NOT NULL,
  amount       numeric(10,2) NOT NULL
);

CREATE VIEW recent_shipped_orders AS
SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE status = 'shipped'
  AND order_date >= CURRENT_DATE - INTERVAL '90 days';

SELECT * FROM recent_shipped_orders ORDER BY order_date DESC;

Views earn their keep in three ways:

  • One definition. Every report uses the same meaning of "recent" and "shipped". Change it in one place and everyone gets the change.
  • Simpler queries. Analysts query a clean name without knowing the joins behind it.
  • Access control. Grant SELECT on a view that leaves out sensitive columns, without granting access to the table. Day 21 covers permissions.

Two limits to know. CREATE OR REPLACE VIEW can add columns at the end, but it can't remove, rename or reorder them. For that you drop and recreate the view. And a view doesn't make anything faster. If the underlying query is slow, the view is slow.

Updatable views and WITH CHECK OPTION

Simple views can be written to directly. If a view reads from one table, with no joins, GROUP BY, DISTINCT, LIMIT or UNION, PostgreSQL turns INSERT, UPDATE and DELETE on the view into changes to the table.

That creates a trap. Here's a view of active customers:

SQL
CREATE TABLE customers (
  customer_id  int PRIMARY KEY,
  name         text NOT NULL,
  is_active    boolean NOT NULL DEFAULT false
);

CREATE VIEW active_customers AS
SELECT customer_id, name, is_active
FROM customers
WHERE is_active;

INSERT INTO active_customers (customer_id, name) VALUES (1, 'Ghost Co');
SELECT count(*) FROM active_customers;   -- 0

The insert succeeded, but is_active defaulted to false, so the row vanished from the view straight away. Add WITH CHECK OPTION and PostgreSQL rejects any write through the view that the view itself couldn't see:

SQL
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, name, is_active
FROM customers
WHERE is_active
WITH CHECK OPTION;

INSERT INTO active_customers (customer_id, name) VALUES (2, 'Ghost Two');
-- ERROR: new row violates check option for view "active_customers"

Use it on any writable view whose job is to show a subset of rows, such as active records or one tenant's data. Views with joins or aggregates aren't writable automatically. If you really need that, an INSTEAD OF trigger (Day 16) lets you write the logic yourself.

Materialized views: a stored snapshot

A materialized view runs its query once and stores the rows on disk. Reads hit the stored rows, not the base tables:

SQL
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', order_date) AS month,
       sum(amount) AS revenue,
       count(*)    AS orders
FROM orders
GROUP BY 1;

SELECT * FROM monthly_revenue ORDER BY month DESC;

From then on, new orders don't appear in it. PostgreSQL doesn't refresh it for you on any schedule. You run the refresh, usually from cron, the pg_cron extension or your application:

Code
REFRESH MATERIALIZED VIEW monthly_revenue;

A plain refresh locks the materialized view while it rebuilds, so anyone reading it waits. On a big view that can mean minutes of a blank dashboard.

REFRESH MATERIALIZED VIEW CONCURRENTLY

REFRESH ... CONCURRENTLY builds the new result on the side, compares it with the old one, and applies only the differences. Readers keep seeing the old data the whole time. It needs a unique index on the materialized view, so PostgreSQL can match old rows to new ones:

SQL
CREATE UNIQUE INDEX ON monthly_revenue (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Two things people get wrong:

  • CONCURRENTLY isn't faster. It's usually slower, because it has to compare old and new rows. What you gain is that readers aren't blocked.
  • A materialized view created WITH NO DATA can't be read until its first refresh, and that first refresh can't be concurrent.

A common setup combines both. A materialized view does the expensive aggregation every hour, and a thin regular view on top renames columns for the BI tool.

Check yourself

Check whether the scheduled REFRESH MATERIALIZED VIEW is still running, waiting on a lock, or failing. A materialized view is a stored snapshot that only changes when something refreshes it, so a broken refresh job leaves it showing old data. Look at the job's logs and pg_stat_activity for a stuck refresh.

Add a unique index to the materialized view and switch to REFRESH MATERIALIZED VIEW CONCURRENTLY. Readers then keep the old data until the new data is ready.

Add WITH CHECK OPTION. Without it, an insert through the view that doesn't satisfy the view's WHERE clause, such as a customer with region = 'US', still succeeds, but the row is then invisible through the view. With the option, PostgreSQL rejects such inserts and updates.

No. A regular view is a saved query that PostgreSQL expands into each query that uses it, so it reads the underlying tables every time. It simplifies queries and permissions but doesn't cache anything. Use a materialized view when you need a stored, faster result.

A unique index on the materialized view, so PostgreSQL can compare old and new rows and apply only the differences. In return, readers aren't blocked during the refresh, although it takes longer and does more work than a plain refresh.