Creation and refresh: fundamentals for your first runnable MV

Lesson overview

You decided your case is a materialized view (lesson 02). Now comes the implementation. This lesson takes you from CREATE MATERIALIZED VIEW to your first REFRESH end-to-end, with runnable code against a local database. You're going to create mv_top_posts_weekly (top 10 most-viewed posts in the last 7 days), query it, refresh it, measure the cost, drop it, and recreate it. Leaving the lesson without having a live MV in your test database means something didn't land.

The focus is on the fundamentals: the exact syntax, what happens internally when you create an MV, why the first refresh is always FULL (even if you'll later use CONCURRENTLY), how to query the size of an MV, and how to drop it cleanly. Lesson 04 goes deeper into CONCURRENTLY vs FULL and the locking trade-offs; lesson 05 covers indexes. This lesson gives you the skeleton so those next two can go deeper on something that already works.

By the end you'll have an MV created, refreshed, and queried in your local environment, with pure SQL code and SQLAlchemy 2.0 async code ready to integrate into your FastAPI app.

Mental model: an MV is a "timestamped snapshot" of the SELECT

Remember the framing from the previous lesson: an MV is a precooked, refrigerated dish. Let's land it a bit more:

When you run CREATE MATERIALIZED VIEW mv_x AS SELECT ..., PostgreSQL does two things:

1. Creates a new table called mv_x with the columns and types of the SELECT result.
2. Runs the SELECT immediately and loads the result into that table.

After that, mv_x is a normal table — you can query it with SELECT FROM mv_x, index it with CREATE INDEX, see its size with pg_relation_size. The difference from a normal table is that you can't do direct INSERT/UPDATE/DELETE: the only way to change its data is with REFRESH MATERIALIZED VIEW.

┌─────────────────────────────────────────────────────────────────┐
│  Time T=0 (creation):                                           │
│                                                                 │
│  CREATE MATERIALIZED VIEW mv_top_posts AS                       │
│    SELECT post_id, count(*) AS views                            │
│    FROM views WHERE created_at > NOW() - INTERVAL '7 days'      │
│    GROUP BY post_id ORDER BY count(*) DESC LIMIT 10;            │
│                                                                 │
│  PostgreSQL runs the SELECT (4.2s) and stores the result:       │
│                                                                 │
│  ┌──────────────────────────────────┐                           │
│  │ mv_top_posts (table on disk)     │                           │
│  │  post_id │ views                 │                           │
│  │  ────────┼──────                 │                           │
│  │  142     │ 8421                  │                           │
│  │  87      │ 6133                  │                           │
│  │  ...                             │                           │
│  │  computed_at: 2026-05-02 14:30   │  ← timestamped snapshot   │
│  └──────────────────────────────────┘                           │
└─────────────────────────────────────────────────────────────────┘

           Time passes, new views arrive in the `views` table.
           The MV doesn't notice. It keeps showing 14:30 data.

┌─────────────────────────────────────────────────────────────────┐
│  Time T=1h (query):                                             │
│                                                                 │
│  SELECT * FROM mv_top_posts;                                    │
│                                                                 │
│  PostgreSQL does a direct lookup (8ms). Returns the data        │
│  captured at 14:30 — even though it's now 15:30.                │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│  Time T=1h (refresh):                                           │
│                                                                 │
│  REFRESH MATERIALIZED VIEW mv_top_posts;                        │
│                                                                 │
│  PostgreSQL re-runs the SELECT (4.2s) and replaces the data.   │
│                                                                 │
│  ┌──────────────────────────────────┐                           │
│  │ mv_top_posts (updated table)     │                           │
│  │  post_id │ views                 │                           │
│  │  ────────┼──────                 │                           │
│  │  142     │ 9012  ← changed      │                            │
│  │  91      │ 7234  ← newly in     │                            │
│  │  ...                             │                           │
│  │  computed_at: 2026-05-02 15:30   │  ← new snapshot           │
│  └──────────────────────────────────┘                           │
└─────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. CREATE already loads data. There's no separate step of "create the empty MV and then refresh." Creation includes the first computation. If you want an "empty" MV to refresh later, use CREATE MATERIALIZED VIEW ... WITH NO DATA (we'll see it below).

2. REFRESH re-runs the full SELECT. It's not an "incremental diff" — PostgreSQL doesn't know what changed. It re-runs the query, gets the new result, and replaces the data. This matters: if your SELECT is 60 seconds, your refresh takes 60 seconds (or more with CONCURRENTLY). Compute isn't free.

3. The refresh timestamp lives wherever you put it. PostgreSQL doesn't automatically store "when it was last refreshed" in an accessible column. By convention, you add a computed_at TIMESTAMPTZ DEFAULT NOW() column to the SELECT to have that metadata. That column is recomputed on every refresh.

Creation syntax

The canonical form:

CREATE MATERIALIZED VIEW <name> AS
    <SELECT statement>
[WITH [NO] DATA];

Variants:

• WITH DATA (default): runs the SELECT and loads the data on creation. It's what you want in 95% of cases.
• WITH NO DATA: creates the table structure without loading data. The MV stays "empty" and you need a later REFRESH to populate it. Useful if you want to separate creation (fast, part of a migration) from loading (slow, part of a later job).

Simple example:

CREATE MATERIALIZED VIEW mv_total_users AS
SELECT count(*) AS total FROM users;

PostgreSQL creates the mv_total_users table with a total BIGINT column and one row with the current count. Done.

Module example (top posts):

CREATE MATERIALIZED VIEW mv_top_posts_weekly AS
SELECT
    p.id AS post_id,
    p.title,
    p.slug,
    count(v.id) AS view_count,
    NOW() AS computed_at
FROM posts p
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title, p.slug
ORDER BY view_count DESC
LIMIT 10;

PostgreSQL creates mv_top_posts_weekly with 5 columns (post_id, title, slug, view_count, computed_at) and loads the 10 resulting rows.

Post-creation inspection:

-- What columns does it have?
\d mv_top_posts_weekly

-- Or in pure SQL:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'mv_top_posts_weekly';

-- How many rows?
SELECT count(*) FROM mv_top_posts_weekly;

-- How much disk does it take up?
SELECT pg_size_pretty(pg_relation_size('mv_top_posts_weekly'));
-- Example output: '16 kB' (because they're 10 small rows)

Complete worked example: your first MV end-to-end

Let's build mv_top_posts_weekly from scratch, assuming you have a blog with posts and views tables. The code is runnable against local PostgreSQL 16.

Step 1: setup of tables and test data

-- Minimal tables for the exercise
CREATE TABLE IF NOT EXISTS posts (
    id           BIGSERIAL PRIMARY KEY,
    title        TEXT NOT NULL,
    slug         TEXT UNIQUE NOT NULL,
    author_id    BIGINT NOT NULL,
    published_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS views (
    id         BIGSERIAL PRIMARY KEY,
    post_id    BIGINT NOT NULL REFERENCES posts(id),
    user_id    BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_views_post_created
    ON views (post_id, created_at);
CREATE INDEX IF NOT EXISTS idx_views_created
    ON views (created_at);

Load test data (50K posts, 5M views) — useful for seeing the real cost:

-- 50K posts (50% published)
INSERT INTO posts (title, slug, author_id, published_at, created_at)
SELECT
    'Post ' || gs,
    'post-' || gs,
    (random() * 1000)::int + 1,
    CASE WHEN random() < 0.5 THEN NOW() - (random() * 365) * INTERVAL '1 day' ELSE NULL END,
    NOW() - (random() * 730) * INTERVAL '1 day'
FROM generate_series(1, 50000) gs;

-- 5M views spread over the last 30 days
INSERT INTO views (post_id, user_id, created_at)
SELECT
    (random() * 50000)::int + 1,
    (random() * 100000)::int + 1,
    NOW() - (random() * 30) * INTERVAL '1 day'
FROM generate_series(1, 5000000);

This may take 1-2 minutes. Once loaded, measure the cost of the "live" query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    p.id AS post_id,
    p.title,
    count(v.id) AS view_count
FROM posts p
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title
ORDER BY view_count DESC
LIMIT 10;

Expected output (varies by hardware, reference value):

Limit  (cost=89231.45..89231.48 rows=10)
       (actual time=3812.234..3812.241 rows=10 loops=1)
  Buffers: shared read=78421
  ...
Planning Time: 0.421 ms
Execution Time: 3814.012 ms

~3.8 seconds. That's the cost every time a user loads the dashboard without an MV.

Step 2: create the MV

CREATE MATERIALIZED VIEW mv_top_posts_weekly AS
SELECT
    p.id AS post_id,
    p.title,
    p.slug,
    count(v.id) AS view_count,
    NOW() AS computed_at
FROM posts p
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title, p.slug
ORDER BY view_count DESC
LIMIT 10;

PostgreSQL runs the SELECT (~3.8s) and creates the MV with the data. If all goes well, it returns:

CREATE MATERIALIZED VIEW

Verify:

SELECT * FROM mv_top_posts_weekly;

Output (example):

 post_id │      title      │       slug       │ view_count │      computed_at
─────────┼─────────────────┼──────────────────┼────────────┼─────────────────────────
   142   │ Post 142        │ post-142         │      8421  │ 2026-05-02 14:30:01+00
    87   │ Post 87         │ post-87          │      6133  │ 2026-05-02 14:30:01+00
   ...

Measure the cost of the query against the MV:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mv_top_posts_weekly;

Expected output:

Seq Scan on mv_top_posts_weekly  (cost=0.00..1.10 rows=10)
                                 (actual time=0.012..0.015 rows=10 loops=1)
  Buffers: shared hit=1
Planning Time: 0.082 ms
Execution Time: 0.041 ms

~0.04 milliseconds (lookup over a 10-row table). Compared to the 3814ms of the live query, it's ~95,000× faster.

Step 3: add the unique index (preview of lesson 04)

To support REFRESH CONCURRENTLY later, you need a unique index. The simplest here is on post_id (we know each post appears only once in the MV):

CREATE UNIQUE INDEX idx_mv_top_posts_pk
    ON mv_top_posts_weekly (post_id);

If you don't have a naturally unique column, use a combination or an artificial row_id column. More on this in lesson 04.

Step 4: refresh the MV

Modify some data to simulate the passage of time:

-- Simulate more views on post 142
INSERT INTO views (post_id, user_id, created_at)
SELECT 142, gs, NOW()
FROM generate_series(1, 5000) gs;

Query the MV. It still shows the old data:

SELECT post_id, view_count, computed_at FROM mv_top_posts_weekly WHERE post_id = 142;
-- view_count: 8421 (unchanged)
-- computed_at: 2026-05-02 14:30:01+00 (original timestamp)

Refresh:

REFRESH MATERIALIZED VIEW mv_top_posts_weekly;

It takes the same as the original SELECT (~3.8s). Verify:

SELECT post_id, view_count, computed_at FROM mv_top_posts_weekly WHERE post_id = 142;
-- view_count: 13421 (5000 new views added)
-- computed_at: 2026-05-02 15:45:22+00 (refresh timestamp)

Important: the REFRESH you just ran is FULL (without CONCURRENTLY). It blocks reads during those 3.8 seconds. If in production another process had wanted to read mv_top_posts_weekly during that window, it would have waited. That's why CONCURRENTLY is the default in production — but we'll get to that in lesson 04.

Step 5: drop the MV (when you no longer need it)

DROP MATERIALIZED VIEW IF EXISTS mv_top_posts_weekly;

This removes the MV and all its indexes. If you have objects that depend on it (another MV or VIEW that queries it), use CASCADE:

DROP MATERIALIZED VIEW IF EXISTS mv_top_posts_weekly CASCADE;

Useful variants of CREATE

WITH NO DATA — separate creation from loading

If the creation is part of an Alembic migration and the initial load is slow, you can separate them:

-- Part of the migration (fast, seconds):
CREATE MATERIALIZED VIEW mv_top_posts_weekly AS
SELECT
    p.id AS post_id,
    p.title,
    p.slug,
    count(v.id) AS view_count,
    NOW() AS computed_at
FROM posts p
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title, p.slug
ORDER BY view_count DESC
LIMIT 10
WITH NO DATA;

-- Part of a later job (real loading):
REFRESH MATERIALIZED VIEW mv_top_posts_weekly;

After the CREATE WITH NO DATA, the MV exists but SELECT * FROM mv_top_posts_weekly returns 0 rows. Any query to an "empty" MV returns an error if it's marked as unscannable:

ERROR: materialized view "mv_top_posts_weekly" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

PostgreSQL warns you explicitly. Refresh to populate.

Create from a SELECT with a complex CTE

CREATE MATERIALIZED VIEW mv_active_authors AS
WITH active_in_period AS (
    SELECT author_id, count(*) AS post_count
    FROM posts
    WHERE published_at > NOW() - INTERVAL '30 days'
    GROUP BY author_id
),
total_views AS (
    SELECT p.author_id, count(v.id) AS total_views
    FROM posts p
    JOIN views v ON v.post_id = p.id
    WHERE v.created_at > NOW() - INTERVAL '30 days'
    GROUP BY p.author_id
)
SELECT
    u.id AS author_id,
    u.username,
    a.post_count,
    COALESCE(t.total_views, 0) AS total_views,
    NOW() AS computed_at
FROM users u
JOIN active_in_period a ON a.author_id = u.id
LEFT JOIN total_views t ON t.author_id = u.id
ORDER BY total_views DESC NULLS LAST;

CTEs work inside CREATE MATERIALIZED VIEW AS just like in any SELECT.

Running a refresh from SQLAlchemy 2.0 async

REFRESH MATERIALIZED VIEW is not an ORM operation — there's no session.refresh_mv() method. You run it as raw SQL via text():

# services/mv_refresh.py
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def refresh_top_posts_weekly(session: AsyncSession) -> None:
    """Refresh the mv_top_posts_weekly MV. Blocks reads while it runs.

    For production, use refresh_top_posts_weekly_concurrent (lesson 04).
    """
    await session.execute(text("REFRESH MATERIALIZED VIEW mv_top_posts_weekly"))
    await session.commit()

Important notes:

• REFRESH MATERIALIZED VIEW is implicit DDL — it runs with its own locking. It's not a "normal" transaction you can wrap in a typical BEGIN/COMMIT. But the commit() afterward is necessary in SQLAlchemy async because the session manages the state.
• If the connection dies during the refresh, PostgreSQL aborts the refresh and leaves the old data intact. It doesn't corrupt the MV.
• In SQLAlchemy 2.0, text() gives you safe parameterizable SQL. For dynamic table names, use careful concatenation with a whitelist (not parameters — identifiers can't be parameterized).

FastAPI endpoint that refreshes and reports

# api/admin.py
from datetime import datetime
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.deps import get_session

router = APIRouter(prefix="/admin", tags=["admin"])


@router.post("/mv/top-posts-weekly/refresh")
async def refresh_top_posts(
    session: AsyncSession = Depends(get_session),
) -> dict:
    """Refresh the top posts MV. Admin endpoint — protect with auth.

    Returns the refresh duration and the final timestamp.
    """
    started_at = datetime.utcnow()
    try:
        await session.execute(text("REFRESH MATERIALIZED VIEW mv_top_posts_weekly"))
        await session.commit()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Refresh failed: {e}")
    finished_at = datetime.utcnow()
    duration_ms = (finished_at - started_at).total_seconds() * 1000

    return {
        "status": "ok",
        "duration_ms": round(duration_ms, 2),
        "refreshed_at": finished_at.isoformat(),
    }

This endpoint serves for manual refresh (an admin forcing a refresh) or for tests. In production, the real refresh is fired by cron or an external scheduler (lessons 04 and 06).

Endpoint that queries the MV

# api/dashboard.py
from datetime import datetime
from fastapi import APIRouter, Depends
from pydantic import BaseModel
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.deps import get_session
from app.models.mv import TopPostsWeekly  # mapped from mv_top_posts_weekly

router = APIRouter(prefix="/dashboard", tags=["dashboard"])


class TopPostResponse(BaseModel):
    post_id: int
    title: str
    slug: str
    view_count: int


class TopPostsResponse(BaseModel):
    posts: list[TopPostResponse]
    last_updated: datetime
    staleness_minutes: int


@router.get("/top-posts", response_model=TopPostsResponse)
async def get_top_posts(
    session: AsyncSession = Depends(get_session),
) -> TopPostsResponse:
    """Return the top 10 posts of the week from the MV.

    Includes `last_updated` and `staleness_minutes` so the frontend
    can show 'Last updated: X minutes ago'.
    """
    stmt = select(TopPostsWeekly).order_by(TopPostsWeekly.view_count.desc())
    result = await session.execute(stmt)
    rows = result.scalars().all()

    if not rows:
        return TopPostsResponse(posts=[], last_updated=datetime.utcnow(), staleness_minutes=0)

    last_updated = rows[0].computed_at
    staleness = (datetime.utcnow().replace(tzinfo=last_updated.tzinfo) - last_updated)
    staleness_minutes = int(staleness.total_seconds() / 60)

    return TopPostsResponse(
        posts=[
            TopPostResponse(
                post_id=r.post_id,
                title=r.title,
                slug=r.slug,
                view_count=r.view_count,
            )
            for r in rows
        ],
        last_updated=last_updated,
        staleness_minutes=staleness_minutes,
    )

Mapping the MV in SQLAlchemy 2.0

# models/mv.py
from datetime import datetime
from sqlalchemy import BigInteger, String, DateTime
from sqlalchemy.orm import Mapped, mapped_column

from app.db import Base


class TopPostsWeekly(Base):
    """Read-only mapping of the mv_top_posts_weekly materialized view.

    Defines no migrations — the MV is created with pure SQL or an Alembic op.execute().
    """
    __tablename__ = "mv_top_posts_weekly"

    post_id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    title: Mapped[str] = mapped_column(String)
    slug: Mapped[str] = mapped_column(String)
    view_count: Mapped[int] = mapped_column(BigInteger)
    computed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))

Note: __tablename__ = "mv_top_posts_weekly" and primary_key=True on post_id tell SQLAlchemy to treat the MV as a table (read-only in practice). The primary key is needed so SQLAlchemy can map the rows; it matches the unique index you created for CONCURRENTLY.

Basic inspection and maintenance

What MVs exist in the database?

SELECT
    schemaname,
    matviewname,
    matviewowner,
    hasindexes,
    ispopulated,
    definition
FROM pg_matviews;

Output:

 schemaname │       matviewname        │ matviewowner │ hasindexes │ ispopulated
────────────┼──────────────────────────┼──────────────┼────────────┼─────────────
 public     │ mv_top_posts_weekly      │ postgres     │ t          │ t
 public     │ mv_active_authors        │ postgres     │ t          │ t

ispopulated = false means it was created WITH NO DATA and never refreshed.

How much disk does each MV take up?

SELECT
    matviewname,
    pg_size_pretty(pg_relation_size(matviewname::regclass)) AS size,
    pg_size_pretty(pg_total_relation_size(matviewname::regclass)) AS total_size_with_indexes
FROM pg_matviews
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(matviewname::regclass) DESC;

pg_relation_size gives only the table. pg_total_relation_size includes associated indexes. Useful for spotting giant MVs that nobody queries anymore.

When was each MV's last refresh?

PostgreSQL doesn't expose this directly. The convention is to add NOW() AS computed_at to the MV's SELECT (as we did above) and query:

SELECT computed_at FROM mv_top_posts_weekly LIMIT 1;

If you need centralized tracking of all refreshes, maintain a mv_refresh_log table:

CREATE TABLE IF NOT EXISTS mv_refresh_log (
    id            BIGSERIAL PRIMARY KEY,
    mv_name       TEXT NOT NULL,
    started_at    TIMESTAMPTZ NOT NULL,
    finished_at   TIMESTAMPTZ NOT NULL,
    duration_ms   NUMERIC(10, 2) NOT NULL,
    success       BOOLEAN NOT NULL,
    error_message TEXT
);

And log each refresh from the job/cron. Module 6 expands this pattern with advisory locks.

Why does this matter on the job?

1. The exact syntax is what gets forgotten most under pressure. When the dashboard goes down at 3 AM and the on-call needs to refresh the MV manually, it's not the time to google "create materialized view postgres syntax." Having the command memorized and understanding the flags (WITH NO DATA, CONCURRENTLY) makes the difference between 5 minutes and 30 minutes of downtime.

2. The SQLAlchemy integration is where most people get stuck. The ORM has no MV abstraction — you have to mix text() for refresh with classic mapping for reads. Knowing the clean pattern (a Mapped model for reads + a function with text() for refresh) saves you from reinventing it every time.

3. WITH NO DATA is the trick for deploys with large MVs. When a migration adds an MV whose initial SELECT takes 10 minutes, you don't want to block the deploy waiting. CREATE ... WITH NO DATA takes milliseconds; the real refresh runs as a later job. It's the standard pattern in production deploys.

4. Inspection with pg_matviews and pg_relation_size is basic operations. When the ticket "disk is at 90%" arrives, one of the first actions is to identify MVs that take up a lot. Without these commands, you go to tables, indexes, and bloat — wasting time. With them, in 30 seconds you know whether the problem is an abandoned 50GB MV.

5. The computed_at timestamp is the convention that communicates staleness. Without it, the frontend doesn't know when the data was refreshed. Without that info, it can't show "Last updated: X minutes ago." The user reports "incorrect data" when it's simply old. Building the MV with the timestamp column from day 1 avoids refactors later.

Pitfalls and common mistakes

Mistake 1 (conceptual): assuming REFRESH computes the diff

Symptom: "The refresh takes the same as the creation. I thought it would only update the changes."

Why it happens: PostgreSQL has no native incremental refresh. REFRESH MATERIALIZED VIEW re-runs the full SELECT and replaces all the data. There's no "compute what changed since the last refresh."

How to tell: measure the duration. If your first creation took 30s and your refresh also takes ~30s, that's normal behavior. If you expected 1s, that was an incorrect expectation.

How to fix: assume the refresh cost = cost of the original SELECT. Design your refresh strategy with that in mind. If you need real incremental refresh, the options are: triggers that update a normal table (not an MV), extensions like pg_ivm (incremental view maintenance), or an external service (Materialize, Flink). Out of scope for this module.

Mistake 2 (conceptual): querying an MV created WITH NO DATA without refreshing

Symptom: you run CREATE MATERIALIZED VIEW ... WITH NO DATA, then SELECT * FROM mv_x and get:

ERROR: materialized view "mv_x" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

Why it happens: WITH NO DATA leaves the MV in an "unpopulated" state. PostgreSQL prefers to fail explicitly rather than return 0 rows (which you might misinterpret as "there's no data").

How to tell: SELECT ispopulated FROM pg_matviews WHERE matviewname = 'mv_x'; returns false.

How to fix: run REFRESH MATERIALIZED VIEW mv_x to populate it. After that it works normally.

Mistake 3 (practical): forgetting that REFRESH (without CONCURRENTLY) blocks reads

Symptom: you run REFRESH MATERIALIZED VIEW mv_x in production, the dashboard goes down for 30 seconds, users report "the page won't load."

Why it happens: REFRESH (without CONCURRENTLY) takes an ACCESS EXCLUSIVE LOCK on the MV. Any SELECT that arrives during the refresh waits for the lock. For an MV queried from a popular dashboard, that's a visible outage.

How to tell: during the refresh, pg_stat_activity shows pending queries with wait_event_type = 'Lock' and wait_event = 'relation'.

How to fix: use REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x (requires a unique index — lesson 04). For the first initial refresh (after WITH NO DATA), run it in a maintenance window — because CONCURRENTLY doesn't work on unpopulated MVs.

Mistake 4 (practical): running REFRESH from SQLAlchemy without commit() and seeing it not apply

Symptom: you run await session.execute(text("REFRESH MATERIALIZED VIEW mv_x")), it seems to work, but the MV still has old data.

Why it happens: SQLAlchemy 2.0 async operates in implicit transactions. Without commit(), other sessions/connections don't see the result of the refresh.

How to tell: from another session (psql), you query the MV and see new data — the refresh was applied. From your SQLAlchemy session, you keep seeing old data until the commit.

How to fix: always await session.commit() after the refresh. For jobs/crons, consider using session.execution_options(isolation_level="AUTOCOMMIT") to avoid the transactional cycle.

Mistake 5 (conceptual): treating the MV as a mutable table

Symptom: you try INSERT INTO mv_top_posts_weekly VALUES (...) and get:

ERROR: cannot change materialized view "mv_top_posts_weekly"

Why it happens: MVs are read-only from the client. The only way to modify their data is REFRESH. PostgreSQL blocks direct INSERT/UPDATE/DELETE.

How to tell: the error is explicit.

How to fix: if you need to modify data, it's not an MV case. Consider a normal table with a job that maintains it (UPSERT from a trigger or application). An MV is strictly a "snapshot of the SELECT."

Exercises

Exercise 1: create and refresh your first MV

On the test database with posts and views:

1. Create an MV mv_post_count_by_author that has: author_id, post_count, computed_at.
2. Verify with \d mv_post_count_by_author that the columns are as expected.
3. Insert 5 new posts for an existing author.
4. Query the MV — it should still show the old data.
5. Refresh and verify that the count went up.

-- Step 1: create
CREATE MATERIALIZED VIEW mv_post_count_by_author AS
SELECT
    author_id,
    count(*) AS post_count,
    NOW() AS computed_at
FROM posts
WHERE published_at IS NOT NULL
GROUP BY author_id;

-- Step 2: verify columns
\d mv_post_count_by_author
-- Output should show: author_id (bigint), post_count (bigint), computed_at (timestamptz)

-- Step 3: insert new posts
INSERT INTO posts (title, slug, author_id, published_at)
SELECT
    'New Post ' || gs,
    'new-post-' || gs || '-' || extract(epoch from now())::text,
    42,  -- existing author_id
    NOW()
FROM generate_series(1, 5) gs;

-- Step 4: old query
SELECT post_count, computed_at FROM mv_post_count_by_author WHERE author_id = 42;
-- post_count: <original value> | computed_at: <creation timestamp>

-- Step 5: refresh and query
REFRESH MATERIALIZED VIEW mv_post_count_by_author;
SELECT post_count, computed_at FROM mv_post_count_by_author WHERE author_id = 42;
-- post_count: <original value + 5> | computed_at: <refresh timestamp>

Exercise 2: use WITH NO DATA and populate later

1. Create mv_views_per_day (views grouped by day) with WITH NO DATA.
2. Verify with pg_matviews that ispopulated = false.
3. Try to query — it should fail.
4. Refresh and verify that it now works.

-- Step 1: create without data
CREATE MATERIALIZED VIEW mv_views_per_day AS
SELECT
    date_trunc('day', created_at) AS day,
    count(*) AS view_count,
    NOW() AS computed_at
FROM views
GROUP BY 1
WITH NO DATA;

-- Step 2: verify state
SELECT matviewname, ispopulated FROM pg_matviews WHERE matviewname = 'mv_views_per_day';
-- Output: mv_views_per_day | f

-- Step 3: query (should fail)
SELECT * FROM mv_views_per_day;
-- ERROR:  materialized view "mv_views_per_day" has not been populated
-- HINT:  Use the REFRESH MATERIALIZED VIEW command.

-- Step 4: refresh
REFRESH MATERIALIZED VIEW mv_views_per_day;

-- Verify populated
SELECT matviewname, ispopulated FROM pg_matviews WHERE matviewname = 'mv_views_per_day';
-- Output: mv_views_per_day | t

-- Final query
SELECT * FROM mv_views_per_day ORDER BY day DESC LIMIT 5;
-- Returns results

Exercise 3: refresh from SQLAlchemy with error handling

Implement a safe_refresh_mv(session, mv_name) function that:

1. Runs the refresh.
2. Catches exceptions (timeout, lock conflict, query error).
3. Returns a dict with success: bool, duration_ms: float, error: str | None.
4. Is idempotent (running it twice in a row breaks nothing).

# services/mv_refresh.py
import time
from typing import Optional
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession


# Explicit whitelist of refreshable MVs (prevents SQL injection via mv_name).
ALLOWED_MVS = {
    "mv_top_posts_weekly",
    "mv_post_count_by_author",
    "mv_views_per_day",
    "mv_active_authors",
}


async def safe_refresh_mv(
    session: AsyncSession,
    mv_name: str,
    concurrent: bool = False,
) -> dict:
    """Refresh an MV with error handling and reporting.

    Args:
        session: SQLAlchemy async session.
        mv_name: name of the MV (must be in ALLOWED_MVS).
        concurrent: if True, uses REFRESH CONCURRENTLY (requires a unique index).

    Returns:
        dict with keys: success (bool), duration_ms (float), error (str | None).
    """
    if mv_name not in ALLOWED_MVS:
        return {
            "success": False,
            "duration_ms": 0.0,
            "error": f"MV '{mv_name}' not in allowed list",
        }

    mode = "CONCURRENTLY " if concurrent else ""
    sql = f"REFRESH MATERIALIZED VIEW {mode}{mv_name}"

    started_at = time.monotonic()
    try:
        await session.execute(text(sql))
        await session.commit()
        duration_ms = (time.monotonic() - started_at) * 1000
        return {
            "success": True,
            "duration_ms": round(duration_ms, 2),
            "error": None,
        }
    except SQLAlchemyError as e:
        await session.rollback()
        duration_ms = (time.monotonic() - started_at) * 1000
        return {
            "success": False,
            "duration_ms": round(duration_ms, 2),
            "error": str(e),
        }


# Usage:
# result = await safe_refresh_mv(session, "mv_top_posts_weekly")
# if not result["success"]:
#     logger.error(f"MV refresh failed: {result['error']}")

Exercise 4: measure the refresh cost

On mv_top_posts_weekly, measure how long REFRESH MATERIALIZED VIEW takes with a dataset of 5M views. Compare with the cost of the direct SELECT.

-- 1. Cost of the direct SELECT (the "live" query)
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    p.id AS post_id, p.title, count(v.id) AS view_count
FROM posts p
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title
ORDER BY view_count DESC LIMIT 10;
-- Execution Time: ~3814 ms

-- 2. Cost of the SELECT against the MV
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mv_top_posts_weekly;
-- Execution Time: ~0.04 ms

-- 3. Cost of the REFRESH (measured with \timing in psql)
\timing on
REFRESH MATERIALIZED VIEW mv_top_posts_weekly;
-- Time: 3902.421 ms (3.9s)

Summary and next step

In this lesson you built your first MV end-to-end:

• CREATE MATERIALIZED VIEW name AS SELECT ... creates the MV and loads data immediately. With WITH NO DATA you separate creation (fast) from loading (can be slow).

• REFRESH MATERIALIZED VIEW name re-runs the full SELECT and replaces the data. Blocks reads during the refresh (without CONCURRENTLY). No native incremental refresh: the refresh cost = cost of the original SELECT.

• DROP MATERIALIZED VIEW [CASCADE] removes the MV. CASCADE also removes objects that depend on it.

• computed_at convention: adding NOW() AS computed_at to the SELECT gives you the timestamp of the last refresh, which the frontend uses to show "last updated: X minutes ago."

• Inspection with pg_matviews and pg_relation_size: identifies existing MVs, their state (ispopulated), and their disk size. Basic operation for auditing.

• SQLAlchemy 2.0 async integration: refresh with text("REFRESH MATERIALIZED VIEW name") + commit(). Reads with classic mapping, treating the MV as a read-only table.

Before moving on, you should be able to:

• Create a runnable MV against your local database (not just read the syntax).
• Refresh it manually and see the data change.
• Inspect size and state with pg_matviews.
• Implement the refresh from SQLAlchemy with error handling.

Next lesson — refresh CONCURRENTLY vs FULL: locking trade-offs. Throughout this lesson you used REFRESH MATERIALIZED VIEW name (FULL mode). In production, that mode is problematic: it blocks reads. Lesson 04 teaches you REFRESH MATERIALIZED VIEW CONCURRENTLY name, the non-blocking mode, and explains the mandatory unique index gotcha you already saw previewed here. It also covers when FULL is still acceptable (first refresh, maintenance window) and how to measure how much each mode costs.

Resources

1. PostgreSQL 16 — CREATE MATERIALIZED VIEW — complete syntax, options (TABLESPACE, STORAGE PARAMETERS).
2. PostgreSQL 16 — REFRESH MATERIALIZED VIEW — exact behavior and notes on locking.
3. PostgreSQL 16 — pg_matviews catalog view — reference for inspection.
4. Crunchy Data — Materialized Views in PostgreSQL — best practices with production cases.
5. SQLAlchemy 2.0 — Working with text() — reference for running raw SQL like REFRESH.
6. pganalyze — Materialized Views Performance — cost analysis and operational patterns.

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: Refresh CONCURRENTLY vs FULL — the locking trade-off that defines production.