Postgres Internals 102: Views & Materialized Views

Most engineers know views exist. Fewer know when a view is the wrong tool and a materialized view would be 50x faster. This is Part 2 of the Postgres Internals series — understanding the difference between a lens and a cache, and why it matters the moment your tables hit any real scale.

What is a view?

A view is a saved query with a name. No data is stored — every time you query a view, Postgres runs the underlying query fresh against the real tables.

-- Without a view — this join gets copy-pasted everywhere
SELECT m.name, m.email, g.name AS gym_name, m.expires_at
FROM members m
JOIN gyms g ON g.id = m.gym_id
WHERE m.status = 'active';

-- Create a view once
CREATE VIEW active_members AS
SELECT m.name, m.email, g.name AS gym_name, m.expires_at
FROM members m
JOIN gyms g ON g.id = m.gym_id
WHERE m.status = 'active';

-- Now query it like a table — anywhere, by anyone
SELECT * FROM active_members
WHERE expires_at < now() + interval '7 days';

The view is a lens — the data still lives in members and gyms. No storage cost. Always fresh. If the underlying tables change, you update the view once and every query that uses it picks up the fix automatically.

The catch: the view runs the full underlying query every single time. If that query takes 800ms, every request that hits the view takes 800ms.

What is a materialized view?

A materialized view is a view that stores its result on disk. Postgres runs the query once and caches the output. Subsequent reads hit the cached data — not the underlying tables.

CREATE MATERIALIZED VIEW gym_monthly_revenue AS
SELECT
    g.id          AS gym_id,
    g.name        AS gym_name,
    date_trunc('month', p.paid_at) AS month,
    COUNT(p.id)   AS total_payments,
    SUM(p.amount) AS total_revenue
FROM payments p
JOIN members m ON m.id  = p.member_id
JOIN gyms g    ON g.id  = m.gym_id
GROUP BY g.id, g.name, date_trunc('month', p.paid_at);

-- Instant read — no JOIN, no aggregation at query time
SELECT * FROM gym_monthly_revenue WHERE gym_id = 5;

-- Refresh when you want fresh data
REFRESH MATERIALIZED VIEW gym_monthly_revenue;

The trade-off is freshness. A materialized view is stale the moment new data is written to the underlying tables. You control when it updates via REFRESH — scheduled via cron, triggered after writes, or on demand.

The critical difference

                    Regular View          Materialized View
                    ─────────────────     ──────────────────────────
Storage             None                  Stores result on disk
Data freshness      Always fresh          Stale until refreshed
Read speed          Depends on query      Always fast
REFRESH needed      No                    Yes — manually or via cron
Can be indexed      No                    Yes
Best for            Code reuse, simple    Heavy queries, dashboards,
                    joins, fresh data     reports, analytics

Indexing a materialized view

This is the killer feature — and the main reason to choose a materialized view over a regular one for analytics. You can add indexes to a materialized view exactly like a table. A regular view cannot be indexed at all.

-- Index the materialized view like any table
CREATE UNIQUE INDEX idx_gym_revenue_unique
    ON gym_monthly_revenue(gym_id, month);

CREATE INDEX idx_gym_revenue_month
    ON gym_monthly_revenue(month);

-- Now this query is instant even with 10M rows in payments
SELECT * FROM gym_monthly_revenue
WHERE gym_id = 5 AND month = '2026-03-01';

The unique index on (gym_id, month) also unlocks concurrent refresh — which you always want in production.

REFRESH — blocking vs. concurrent

-- Default: locks the view during refresh — reads block
REFRESH MATERIALIZED VIEW gym_monthly_revenue;

-- Concurrent: allows reads during refresh — no downtime
-- Requires a UNIQUE index on the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY gym_monthly_revenue;

Without CONCURRENTLY, a refresh takes an exclusive lock. Every query hitting the view blocks until the refresh completes. On a dashboard with a heavy aggregation, that could mean seconds of dead time for every user. Always use CONCURRENTLY in production — but remember it requires a unique index first.

When to use which

Use a regular view when:

• The underlying query is fast (simple joins, indexed lookups, small tables)
• You need always-fresh data — held payments, live seat availability, current stock
• It's purely about code reuse and readability, not performance

Use a materialized view when:

• The query is expensive — heavy aggregations, many table joins, millions of rows
• Slightly stale data is acceptable — dashboards, monthly reports, analytics
• You need to index aggregated results for fast filtering
• You're building a read-heavy reporting layer on top of a write-heavy OLTP database

A real pattern: scheduled refresh

In production, materialized views are typically refreshed on a schedule — not on every write. For a gym dashboard showing monthly revenue, refreshing every hour is plenty.

-- Run this in a cron job or pg_cron every hour
REFRESH MATERIALIZED VIEW CONCURRENTLY gym_monthly_revenue;

-- Or after a batch of payments are processed
-- (call from application code post-transaction)
REFRESH MATERIALIZED VIEW CONCURRENTLY gym_monthly_revenue;

If you need near-real-time data with fast reads, consider a different pattern: maintain a summary table yourself with INSERT ... ON CONFLICT DO UPDATE on every write. That's incremental aggregation — but that's a topic for the Scale layer.

Common mistakes

❌  Using a materialized view for real-time data
    → Dashboard shows yesterday's revenue. Users are confused.

❌  Never scheduling REFRESH
    → View becomes weeks stale and silently useless.

❌  Using a regular view for a heavy report
    → 2-second aggregation query runs on every single page load.

❌  REFRESH without CONCURRENTLY in production
    → Exclusive lock during refresh blocks all dashboard reads.

❌  Forgetting the UNIQUE index before using CONCURRENTLY
    → Postgres throws an error: "cannot refresh concurrently without unique index".

Quick reference

-- Create a view
CREATE VIEW view_name AS SELECT ...;

-- Replace a view (non-destructive update)
CREATE OR REPLACE VIEW view_name AS SELECT ...;

-- Drop a view
DROP VIEW view_name;

-- Create a materialized view
CREATE MATERIALIZED VIEW view_name AS SELECT ...;

-- Refresh (blocking)
REFRESH MATERIALIZED VIEW view_name;

-- Refresh (non-blocking — needs UNIQUE index)
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

-- Drop a materialized view
DROP MATERIALIZED VIEW view_name;

-- Index a materialized view
CREATE UNIQUE INDEX idx_name ON view_name(col1, col2);

Test yourself

Q1. You're building an admin dashboard showing total active members per city. The query joins members, gyms, and cities and takes 800ms. It's shown on every admin page load. Would you use a view or a materialized view? Why?

Q2. What's wrong with this setup?

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', paid_at) AS day, SUM(amount) AS revenue
FROM payments GROUP BY 1;

-- App queries this on every API call
-- REFRESH is scheduled once per week

Q3. You want to run REFRESH MATERIALIZED VIEW CONCURRENTLY. What does Postgres require before you can do this?

-- First create the unique index
CREATE UNIQUE INDEX idx_daily_revenue_day ON daily_revenue(day);

-- Now CONCURRENTLY works
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Q4. A developer says "a regular view and a materialized view are basically the same — one just caches". What important capability does a materialized view have that a regular view doesn't?

Next up — Part 3: Schemas & Partitioning. How schemas keep large databases organised, and how table partitioning makes 500M-row tables manageable without sharding.