Views and Materialized Views

What is it? #

A view is a saved query with a name. Selecting from it runs the underlying query every time. It stores no data — it is a shortcut, not a copy.

A materialized view is a saved query plus its results, stored on disk like a table. Selecting from it is fast because the work was already done. The trade-off is that the data is only as fresh as the last refresh.

The choice is a single question: do you need current data, or fast data?

A view is always current and costs whatever the query costs. A materialized view is always fast and is as stale as you allow it to be.

Think of it like this #

A view is a saved search. Every time you open it, it searches again — so the results are current, and you wait for the search.

A materialized view is a printed report. Opening it is instant, because someone already did the work and printed it. But it shows the situation as of when it was printed, and it stays that way until somebody prints it again.

Nobody would reprint the report on every glance, and nobody would trust last month's printout for today's numbers. Choosing between them is choosing which of those two problems you would rather have.

Simple example #

A dashboard shows revenue per city. The query joins three tables and scans a year of orders — about four seconds.

On a page loaded a few times a day, a plain view is fine. On a dashboard loaded by two hundred staff every few minutes, four seconds each time is unacceptable, and the numbers do not need to be accurate to the second. That is a materialized view refreshed every fifteen minutes.

Code #

-- ---------- A plain view: a named query ----------

CREATE VIEW completed_orders AS
SELECT o.id,
       o.customer_id,
       c.name AS customer_name,
       o.total,
       o.placed_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed';

-- Now use it exactly like a table:
SELECT * FROM completed_orders WHERE total > 500;

-- PostgreSQL rewrites that into the full underlying query before running it.
-- No data is stored. The result is ALWAYS current.

-- ---------- Replacing and dropping views ----------

CREATE OR REPLACE VIEW completed_orders AS ...;
-- Allowed only if the columns keep the same names, types and order.
-- To change the shape, drop and recreate it.

DROP VIEW IF EXISTS completed_orders;
-- Safe: this removes only the definition, never any underlying data.

-- DROP VIEW completed_orders CASCADE;
--   CASCADE also drops views built ON TOP of this one. Check first:
SELECT dependent_ns.nspname, dependent_view.relname
FROM pg_depend
JOIN pg_rewrite  ON pg_depend.objid       = pg_rewrite.oid
JOIN pg_class    dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace dependent_ns  ON dependent_view.relnamespace = dependent_ns.oid
WHERE pg_depend.refobjid = 'completed_orders'::regclass;

-- ---------- Updatable views ----------

-- A SIMPLE view (one table, no joins, no GROUP BY, no DISTINCT) is
-- automatically updatable — you can INSERT/UPDATE/DELETE through it:
CREATE VIEW indian_customers AS
SELECT id, name, email FROM customers WHERE country = 'IN';

UPDATE indian_customers SET name = 'Asha V.' WHERE id = 1;   -- works

-- WITH CHECK OPTION stops writes that would fall outside the view:
CREATE VIEW indian_customers AS
SELECT id, name, email, country FROM customers WHERE country = 'IN'
WITH CHECK OPTION;
-- Now setting country to 'US' through this view is rejected.

-- A view WITH a join or aggregate is NOT automatically updatable.
-- You would need an INSTEAD OF trigger (see the triggers lesson).

-- ---------- Materialized view: stores the results ----------

CREATE MATERIALIZED VIEW revenue_by_city AS
SELECT COALESCE(city, 'Unknown')            AS city,
       date_trunc('month', placed_at)::date AS month,
       count(*)                             AS orders,
       sum(total)                           AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1, 2;

SELECT * FROM revenue_by_city ORDER BY revenue DESC;   -- instant: already computed

-- ---------- Refreshing: bringing it up to date ----------

REFRESH MATERIALIZED VIEW revenue_by_city;
-- !! This takes an ACCESS EXCLUSIVE lock: readers are BLOCKED until it finishes.
-- !! On a dashboard that people are using, this is a visible freeze.

-- The better option needs a UNIQUE index on the materialized view:
CREATE UNIQUE INDEX revenue_by_city_key
    ON revenue_by_city (city, month);

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_city;
-- Readers keep working throughout. Slower overall, but no outage.
-- CONCURRENTLY REQUIRES that unique index — it is not optional.

-- ---------- Indexing a materialized view ----------

-- Because it is stored like a table, it can be indexed like one:
CREATE INDEX revenue_by_city_month_idx ON revenue_by_city (month DESC);

-- This is a real advantage: you can index the RESULT of an expensive
-- aggregate, which you cannot do with a plain view.

# ---------- Refreshing on a schedule with cron ----------

# Refresh every 15 minutes, logging failures.
# crontab -e   then add:

*/15 * * * * psql -U reporting -d shop -c "REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_city;" >> /var/log/pg_refresh.log 2>&1

# Note: needs a password file (~/.pgpass) so no password appears in the command.
# The automated-backup lesson covers .pgpass and its required permissions.

-- ---------- Is my materialized view populated? ----------

SELECT matviewname, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';

-- CREATE MATERIALIZED VIEW ... WITH NO DATA creates it empty and fast.
-- Querying an unpopulated materialized view raises an error until refreshed.

How it works #

A plain view is stored as a rule, not as data. When you query it, PostgreSQL substitutes the view's definition into your query and then plans the combined whole. That last point matters: a WHERE clause you add outside the view is usually pushed down into it, so SELECT * FROM completed_orders WHERE total > 500 does not compute every completed order and then filter — the planner merges the conditions first. A view is therefore not inherently slower than writing the query out by hand.

Because there is no stored data, a view is always current and takes no disk space. Dropping one is completely safe: it removes a definition, never rows.

A simple view — one table, no joins, no grouping, no DISTINCT — is automatically updatable, because PostgreSQL can work out unambiguously which underlying row a change refers to. Add a join or an aggregate and that becomes ambiguous, so writes are refused unless you supply an INSTEAD OF trigger. WITH CHECK OPTION closes a subtle gap in updatable views: without it, you could update a row through the view in a way that moves it outside the view's own WHERE clause, making it vanish.

A materialized view runs its query once and writes the results to disk. Subsequent reads touch only those stored rows, which is why a four-second aggregate becomes a few milliseconds. The data does not change again until you refresh it.

The refresh is where the operational care is needed. A plain REFRESH takes an ACCESS EXCLUSIVE lock, blocking all readers for the duration — on a busy dashboard that is a visible freeze. REFRESH ... CONCURRENTLY builds the new version alongside the old one and swaps them, so readers are never blocked. It does more total work, and it requires a unique index on the materialized view so PostgreSQL can match old rows to new ones. Creating that unique index is not an optimisation; it is a precondition.

Because a materialized view is stored, it can carry its own indexes — which means you can index the output of an aggregation. That is something a plain view fundamentally cannot offer.

Real-world use #

Views are most valuable as a stable interface over a schema. If reporting queries all select from completed_orders, you can change how "completed" is defined, or restructure the tables beneath, in one place. They also serve security: grant a role access to a view exposing only non-sensitive columns rather than to the underlying table.

The honest caution about views is layering. A view built on a view built on a view is easy to create and genuinely hard to debug — one EXPLAIN suddenly spans hundreds of lines, and nobody is sure which layer the slow part lives in. Two levels is usually plenty.

Materialized views suit dashboards, analytics, leaderboards, search indexes and anything where "accurate as of fifteen minutes ago" is acceptable. Before using one, get explicit agreement on how stale the data may be. That single answer decides the refresh interval, and it is much easier to agree up front than after someone queries a number they believe is live.

Always create the unique index and always refresh CONCURRENTLY for anything users can see. A non-concurrent refresh on a production dashboard is an outage that looks like a slow page.

Watch the refresh duration over time. A materialized view that takes 30 seconds today may take 8 minutes next year, and if it is refreshed every 5 minutes the refreshes will eventually overlap and pile up. Log the duration and monitor it — that is the monitoring lesson applied here.

Common mistakes #

• Running REFRESH MATERIALIZED VIEW without CONCURRENTLY on a live dashboard, blocking every reader.
• Forgetting the required unique index, so CONCURRENTLY refuses to run.
• Treating a materialized view as live data when it is only as fresh as the last refresh.
• Stacking views on views on views until no one can work out which layer is slow.
• Never monitoring refresh duration, until refreshes take longer than the interval and overlap.

Practice #

Create a plain view over your orders and customers that exposes only completed orders. Query it and confirm with EXPLAIN that a WHERE clause you add outside is pushed down into the view. Then create a materialized view of revenue per city per month, add the unique index it needs, refresh it CONCURRENTLY, and time both the view and the materialized view with \timing on to see the difference for yourself.