Refresh `CONCURRENTLY` vs `FULL`: the locking trade-off that defines production

Lesson overview

In the previous lesson you used REFRESH MATERIALIZED VIEW name to update your MV. That mode — called FULL even though the word doesn't appear in the syntax — blocks all reads on the MV during the refresh. For an MV queried by a popular dashboard, this is a visible outage: if the refresh takes 30 seconds, the dashboard responds with a 504 (gateway timeout) for 30 seconds. That's why FULL is not acceptable for production except in very specific cases.

REFRESH MATERIALIZED VIEW CONCURRENTLY name is the non-blocking mode. Reads keep working while the refresh runs. The catch: it requires a unique index on the MV, and if you forgot it when creating it, it fails with an explicit error. This lesson teaches you the technical differences (which locks each one takes, which costs more in compute, which guarantees it offers), the unique index gotcha, when FULL is still acceptable, and how to measure/decide which one to use in each case.

By the end you'll have the criteria to defend to your lead "why we use CONCURRENTLY in production even though it's slower" and you'll know when it's exceptionally valid to fall back to FULL (first refresh post-WITH NO DATA, scheduled maintenance window, small MV with zero reads during the refresh).

Mental model: two ways to restock the supermarket shelf

Imagine a product shelf in a supermarket. Your MV is the shelf; the customers are the queries that read it. The refresh is "updating the displayed products."

REFRESH FULL is like closing off the entire aisle, emptying the shelf, and restocking it.

[supermarket open]
                          ↓ refresh starts
[CLOSED: aisle not accessible]  ← customers wait in the next aisle
[employee empties the whole shelf]
[employee places new products]
[REOPEN: aisle accessible]
                          ↑ refresh ends, customers come back

While the aisle is closed, no customer can see products. The shelf sits empty for a while. The operation is fast (no need to compare the diff between old and new products), but the aisle is out of service.

REFRESH CONCURRENTLY is like preparing the new products on a side table, comparing them to what's on display, and then swapping discreetly without closing the aisle.

[supermarket open]
                          ↓ refresh starts
[employee prepares new products on a side table]  ← customers keep seeing old products
[employee compares: this product changed, this one leaves, this one is new]
[employee swaps product by product]
[employee removes the side table]
                          ↑ refresh ends, customers saw old products until the last change

The aisle never closes. Customers see old products until the exact moment an item is swapped. The operation is slower (comparing and swapping one by one) and requires extra space (the side table). But there's no outage.

┌──────────────────────────────────────────────────────────────────┐
│                       REFRESH FULL                               │
│                                                                  │
│  Lock: ACCESS EXCLUSIVE (blocks EVERYTHING, including SELECT)    │
│                                                                  │
│  Time:                                                           │
│  ──────────────────────────────────────                          │
│  T=0     [SELECT * FROM mv]  ← responds fast                    │
│  T=1     REFRESH MATERIALIZED VIEW mv  ← starts                 │
│  T=2     [SELECT * FROM mv]  ← BLOCKED, waits                   │
│  T=3     [SELECT * FROM mv]  ← BLOCKED, waits                   │
│  ...                                                             │
│  T=30    refresh ends                                            │
│  T=30+ε  [SELECT * FROM mv]  ← responds with new data           │
│                                                                  │
│  Compute: fast (no diff, just TRUNCATE + INSERT)                 │
│  Extra disk: zero (rewrites in the same place)                  │
└──────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────┐
│                  REFRESH CONCURRENTLY                            │
│                                                                  │
│  Lock: EXCLUSIVE (blocks other refreshes; allows SELECT)        │
│  Requirement: UNIQUE INDEX on the MV                             │
│                                                                  │
│  Time:                                                           │
│  ──────────────────────────────────────                          │
│  T=0     [SELECT * FROM mv]  ← responds with old data           │
│  T=1     REFRESH MATERIALIZED VIEW CONCURRENTLY mv  ← starts    │
│  T=2     [SELECT * FROM mv]  ← responds with old data           │
│  T=15    [SELECT * FROM mv]  ← responds with old data           │
│  T=35    refresh ends                                            │
│  T=35+ε  [SELECT * FROM mv]  ← responds with new data           │
│                                                                  │
│  Compute: slower (diff + selective INSERT/UPDATE/DELETE)        │
│  Extra disk: temporary (the "side table" — ~1× the MV size)    │
└──────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. FULL takes an ACCESS EXCLUSIVE LOCK. CONCURRENTLY takes an EXCLUSIVE LOCK. The difference is in what other operations it blocks: ACCESS EXCLUSIVE blocks everything (including SELECT); EXCLUSIVE blocks other REFRESHes and DROPs, but allows SELECT. That's the basis of the behavior.

2. CONCURRENTLY isn't free. It costs more compute (it compares old rows with new to do a diff) and more temporary disk (it keeps a "side table" with the new data until the swap). In total time, it's usually 1.2-2× slower than FULL. But the cost is invisible to users — there's no outage.

3. The unique index is non-negotiable for CONCURRENTLY. Without it, PostgreSQL can't do the diff (it needs to identify which rows are the "same" between the old and new versions). The error is explicit: cannot refresh materialized view "..." concurrently. Design the MV from day 1 with a naturally unique column or combination.

What happens internally

To understand why CONCURRENTLY requires a unique index, let's look at what each mode does step by step.

Internals of REFRESH FULL

REFRESH MATERIALIZED VIEW mv_top_posts_weekly;

PostgreSQL runs (conceptually):

BEGIN;
LOCK TABLE mv_top_posts_weekly IN ACCESS EXCLUSIVE MODE;

CREATE TEMP TABLE mv_new AS
    SELECT ... FROM ...; -- the original SELECT

TRUNCATE mv_top_posts_weekly;
INSERT INTO mv_top_posts_weekly SELECT * FROM mv_new;
DROP TABLE mv_new;
COMMIT;

• Lock: ACCESS EXCLUSIVE — the most restrictive. Any SELECT waits.
• Cost: runs the SELECT once + TRUNCATE + INSERT. It's the simplest operation.
• Extra disk: the temp table exists briefly, but it's replaced in-place.

Internals of REFRESH CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_posts_weekly;

PostgreSQL runs (simplified):

BEGIN;
LOCK TABLE mv_top_posts_weekly IN EXCLUSIVE MODE;
-- Blocks other REFRESH/DROP, but allows SELECT

CREATE TEMP TABLE mv_new AS
    SELECT ... FROM ...; -- the original SELECT

-- Computes the diff using the unique index:
-- Rows in mv_new but not in mv_top_posts_weekly → INSERT
-- Rows in mv_top_posts_weekly but not in mv_new → DELETE
-- Rows in both with different fields → UPDATE

INSERT INTO mv_top_posts_weekly SELECT ... FROM mv_new WHERE NOT EXISTS (...);
DELETE FROM mv_top_posts_weekly WHERE ... NOT IN (SELECT ... FROM mv_new);
UPDATE mv_top_posts_weekly SET ... WHERE ... ;

DROP TABLE mv_new;
COMMIT;

• Lock: EXCLUSIVE — blocks other refreshes but allows reads.
• Cost: runs the SELECT once + creates a temp table + 3 diff operations (INSERT/DELETE/UPDATE) that require comparing row by row using the unique index.
• Extra disk: the temp table is a similar size to the MV. For a 5GB MV, that's 5GB of additional temporary storage.

The unique index is what lets PostgreSQL identify which rows are "the same" between versions. Without it, it can't do the diff — it could only TRUNCATE + INSERT, but that would require ACCESS EXCLUSIVE (blocking reads), defeating the point of CONCURRENTLY.

The unique index gotcha, step by step

Reproduce the error and the solution so it doesn't take you by surprise in production.

The error

-- Create MV without a unique index
CREATE MATERIALIZED VIEW mv_test AS
SELECT post_id, count(*) AS view_count
FROM views
GROUP BY post_id;

-- Try a concurrent refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test;

Output:

ERROR:  cannot refresh materialized view "public.mv_test" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

PostgreSQL doesn't assume which column is unique. You have to tell it explicitly.

The solution

-- Add a unique index on the naturally unique column
CREATE UNIQUE INDEX idx_mv_test_post_id ON mv_test (post_id);

-- Retry the refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test;

Output:

REFRESH MATERIALIZED VIEW

It works.

Design the MV from day 1 with a unique index

The correct practice is to add the unique index immediately after the CREATE, in the same migration:

-- migration 042_create_mv_top_posts.sql
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;

-- IMMEDIATELY after: the mandatory unique index
CREATE UNIQUE INDEX idx_mv_top_posts_pk ON mv_top_posts_weekly (post_id);

If your MV doesn't have a naturally unique column, there are two strategies:

Strategy A: a combination of columns that is unique together

CREATE MATERIALIZED VIEW mv_views_per_post_per_day AS
SELECT
    post_id,
    date_trunc('day', created_at) AS day,
    count(*) AS view_count
FROM views
GROUP BY 1, 2;

-- (post_id, day) is unique together
CREATE UNIQUE INDEX idx_mv_vppd_pk ON mv_views_per_post_per_day (post_id, day);

Strategy B: add an artificial row_number()

CREATE MATERIALIZED VIEW mv_top_posts_ranked AS
SELECT
    row_number() OVER (ORDER BY view_count DESC) AS rank,
    post_id,
    view_count
FROM (
    SELECT post_id, count(*) AS view_count FROM views GROUP BY post_id
) sub
LIMIT 100;

-- rank is artificially unique
CREATE UNIQUE INDEX idx_mv_tpr_pk ON mv_top_posts_ranked (rank);

Strategy B is useful when the query has aggregations where there's no natural PK. It's not an anti-pattern, it's a legitimate solution.

Cost comparison: practical measurement

Let's measure how long each mode takes on the same MV.

Setup

-- Assuming mv_top_posts_weekly was created in the previous lesson, with 10 rows
-- (remember it has LIMIT 10)

-- To make the difference visible, let's create a larger MV
CREATE MATERIALIZED VIEW mv_views_per_post AS
SELECT
    post_id,
    count(*) AS view_count,
    max(created_at) AS last_view_at,
    NOW() AS computed_at
FROM views
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY post_id;

CREATE UNIQUE INDEX idx_mv_vpp_pk ON mv_views_per_post (post_id);

-- This MV has ~50K rows (one row per post with recent views)
SELECT count(*) FROM mv_views_per_post;
-- Result: 47,234 (example)

SELECT pg_size_pretty(pg_relation_size('mv_views_per_post'));
-- Result: '4.2 MB'

Measuring FULL

\timing on
REFRESH MATERIALIZED VIEW mv_views_per_post;
-- Time: 2841.124 ms (2.84s)

Measuring CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_views_per_post;
-- Time: 4128.567 ms (4.13s)

CONCURRENTLY takes ~45% more in compute. But during those 4 seconds reads keep working. During the 2.8 seconds of FULL, reads hang.

Blocking test: verify empirically

Open two psql sessions. In the first:

-- Session 1: FULL refresh
REFRESH MATERIALIZED VIEW mv_views_per_post;

In the second, during the refresh:

-- Session 2: try a SELECT
SELECT * FROM mv_views_per_post LIMIT 1;
-- (hangs, waits for the lock)

-- Once the refresh ends, it responds

Repeat with CONCURRENTLY:

-- Session 1
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_views_per_post;

-- Session 2 (during the refresh)
SELECT * FROM mv_views_per_post LIMIT 1;
-- Responds immediately! Returns old data (snapshot of the previous refresh).

That difference is the operational justification for using CONCURRENTLY in production.

Trade-offs summary table

When to use FULL (the few legitimate exceptions)

FULL isn't always wrong. There are cases where it's the appropriate choice:

Case 1: first refresh after WITH NO DATA

An MV just created WITH NO DATA has no data to diff against. CONCURRENTLY can't operate — it needs an old version to compare. The first refresh must be FULL:

CREATE MATERIALIZED VIEW mv_x AS SELECT ... WITH NO DATA;
CREATE UNIQUE INDEX ... ON mv_x (...);

-- First refresh: FULL (the only option)
REFRESH MATERIALIZED VIEW mv_x;

-- Subsequent refreshes: CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x;

If your deploy does CREATE WITH NO DATA + REFRESH, the first refresh is FULL out of necessity. Make sure that moment is during a window where the blocking doesn't impact anything (nightly deploy, a just-created MV that has no traffic yet).

Case 2: scheduled maintenance window

If you have a weekly or monthly window where the system is in maintenance mode (planned downtime), FULL is valid because no client is reading during that window. You get:

• Faster compute (no diff).
• Less temporary disk.
• The blocking bothers nobody.

Typical pattern: CONCURRENTLY every hour during normal operation, FULL on Sunday at 4 AM as a "clean refresh" that also includes VACUUM FULL to free up accumulated bloat.

Case 3: MV with zero reads at refresh time

If your MV is only for monthly reports generated at 3 AM, and nobody queries it except that job, FULL is fine. The blocking affects no one.

How to detect this case: check pg_stat_user_tables for idx_scan and seq_scan on the MV. If the SELECTs come almost entirely from the report job and there's no web/api traffic, it's a candidate for FULL.

Case 4: small MV with a very fast refresh (<100ms)

For very small MVs (10-100 rows) whose refresh takes <100ms, the blocking is imperceptible to users. The overhead of CONCURRENTLY (1.5× compute + diff + temp space) may not be worth it.

Rule of thumb: if your refresh is <100ms and the MV is <1000 rows, FULL is fine. If it's >100ms or the MV is >10K rows, CONCURRENTLY.

Decision tree: FULL or CONCURRENTLY?

                ┌─────────────────────────────────────────┐
                │ Is there SELECT traffic on the MV       │
                │ during the refresh?                     │
                └──────────────────┬──────────────────────┘
                                   │
                    ┌──────────────┴──────────────┐
                    │                             │
                  No│                         Yes │
                    ▼                             ▼
        ┌───────────────────────┐    ┌─────────────────────────┐
        │ Is it the first       │    │ Does the MV have a      │
        │ refresh post-WITH     │    │ unique index?           │
        │ NO DATA?              │    └──────────┬──────────────┘
        └──────────┬────────────┘               │
                   │                            │
            ┌──────┴──────┐              ┌──────┴──────┐
            │             │              │             │
          No│         Yes │            No│         Yes │
            ▼             ▼              ▼             ▼
       ┌────────┐    ┌────────┐    ┌─────────┐  ┌────────────────┐
       │ FULL   │    │ FULL   │    │ ADD a   │  │ CONCURRENTLY   │
       │ (no    │    │ (only  │    │ unique  │  │                │
       │ harm)  │    │ option)│    │ index   │  │                │
       └────────┘    └────────┘    │ NOW     │  └────────────────┘
                                   └─────────┘

Operational rule: if in doubt, use CONCURRENTLY. The extra cost (compute + temporary disk) is almost always worth it vs the risk of blocking. FULL should be a conscious decision with justification.

Implementation with SQLAlchemy

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


ALLOWED_MVS = {
    "mv_top_posts_weekly",
    "mv_views_per_post",
    "mv_post_count_by_author",
}


async def refresh_mv(
    session: AsyncSession,
    mv_name: str,
    *,
    concurrent: bool = True,
) -> dict:
    """Refresh an MV. Default: CONCURRENTLY (production).

    Pass concurrent=False only in justified cases:
    - First refresh post-WITH NO DATA.
    - Scheduled maintenance window.
    - MV with no reads at the moment.
    """
    if mv_name not in ALLOWED_MVS:
        return {"success": False, "error": f"MV '{mv_name}' not allowed"}

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

    started = time.monotonic()
    try:
        await session.execute(text(sql))
        await session.commit()
        duration_ms = (time.monotonic() - started) * 1000
        return {
            "success": True,
            "mode": "concurrent" if concurrent else "full",
            "duration_ms": round(duration_ms, 2),
        }
    except SQLAlchemyError as e:
        await session.rollback()
        # Detect the specific "no unique index" error
        error_msg = str(e)
        if "cannot refresh materialized view" in error_msg and "concurrently" in error_msg:
            return {
                "success": False,
                "error": "MV needs UNIQUE INDEX for concurrent refresh",
                "fix": f"CREATE UNIQUE INDEX ON {mv_name} (<key columns>)",
            }
        return {"success": False, "error": error_msg}

Use in a cron job

# jobs/refresh_dashboards.py
import asyncio
import logging
from app.db import async_session_factory
from services.mv_refresh import refresh_mv

logger = logging.getLogger(__name__)


async def refresh_all_dashboard_mvs() -> None:
    """Job that refreshes the dashboard MVs. Runs every hour via cron."""
    mvs = ["mv_top_posts_weekly", "mv_views_per_post", "mv_post_count_by_author"]

    async with async_session_factory() as session:
        for mv in mvs:
            result = await refresh_mv(session, mv, concurrent=True)
            if result["success"]:
                logger.info(
                    f"Refreshed {mv} ({result['mode']}) in {result['duration_ms']}ms"
                )
            else:
                logger.error(f"Failed to refresh {mv}: {result['error']}")


if __name__ == "__main__":
    asyncio.run(refresh_all_dashboard_mvs())

Schedule via OS cron (*/60 * * * *) or pg_cron (lesson 06 previews the pattern with an advisory lock).

Why does this matter on the job?

1. It's the difference between a working dashboard and one that goes down every hour. If your refresh cron runs every hour with FULL, every hour you have 30 seconds of blocking. Users report "the dashboard goes down at :00." With CONCURRENTLY, the refresh is invisible. It's the kind of bug that ruins the product team's confidence in the system.

2. The unique index gotcha is the most common trap. 80% of the "I can't refresh my MV in production" issues boil down to "you forgot the unique index." Knowing it beforehand and designing with the index from day 1 saves you hours of troubleshooting.

3. Justifying CONCURRENTLY to the lead requires the numbers. "It's slower but doesn't block" is vague. "It's 1.4× slower in compute but lets the dashboard's 200 queries/h keep responding during the refresh, avoiding 30 seconds of outage every hour" is concrete. You learned the quantitative framing here.

4. The first-refresh-post-WITH NO DATA exception is a less visible gotcha. Your deploy creates the MV with WITH NO DATA, you try the first refresh with CONCURRENTLY, and it fails. You lose 20 minutes. Knowing the first refresh must be FULL avoids that churn.

5. The empirical test (two psql sessions) is a reproducible proof for PRs. When someone proposes "let's use FULL here," you can show the behavior in parallel sessions as evidence. It's technical vocabulary and demonstration that communicates seniority.

Pitfalls and common mistakes

Mistake 1 (practical): refreshing concurrently without a unique index

Symptom:

ERROR: cannot refresh materialized view "public.mv_x" concurrently
HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.

Why it happens: you forgot to add CREATE UNIQUE INDEX after CREATE MATERIALIZED VIEW.

How to tell: the error is explicit.

How to fix: add the index. If the MV has no naturally unique column, use a combination or an artificial row_number().

CREATE UNIQUE INDEX idx_mv_x_pk ON mv_x (post_id);
-- Or a combination:
CREATE UNIQUE INDEX idx_mv_x_pk ON mv_x (post_id, day);

Mistake 2 (conceptual): thinking CONCURRENTLY avoids ALL blocking

Symptom: you try two REFRESH CONCURRENTLYs in parallel on the same MV, the second one hangs.

Why it happens: CONCURRENTLY allows concurrent reads, but blocks other refreshes. It takes an EXCLUSIVE LOCK that's incompatible with other EXCLUSIVEs. Only one refresh at a time.

How to tell: pg_stat_activity shows the second refresh with wait_event = 'relation' waiting for the first refresh.

How to fix: don't fire two parallel refreshes on the same MV. Use pg_try_advisory_lock (module 6 lesson) so the second cron skips if there's already a refresh in progress. Previewed pattern:

DO $$
BEGIN
    IF pg_try_advisory_lock(hashtext('refresh_mv_top_posts')) THEN
        REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_posts_weekly;
        PERFORM pg_advisory_unlock(hashtext('refresh_mv_top_posts'));
    ELSE
        RAISE NOTICE 'Another refresh in progress, skipping.';
    END IF;
END $$;

Mistake 3 (practical): first refresh post-WITH NO DATA with CONCURRENTLY

Symptom:

CREATE MATERIALIZED VIEW mv_x AS SELECT ... WITH NO DATA;
CREATE UNIQUE INDEX ... ON mv_x (...);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x;

ERROR: CONCURRENTLY cannot be used when the materialized view is not populated

Why it happens: CONCURRENTLY needs an "old" version to do the diff. A WITH NO DATA MV is empty — there's no old version.

How to tell: the error mentions not populated.

How to fix: first refresh always FULL, then use CONCURRENTLY:

REFRESH MATERIALIZED VIEW mv_x;  -- first refresh: FULL
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x;  -- subsequent: CONCURRENTLY

Mistake 4 (practical): CONCURRENTLY with EXPLAIN ANALYZE wanting to benchmark it

Symptom: you run EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x and get an error or imprecise timing.

Why it happens: EXPLAIN ANALYZE doesn't work on REFRESH MATERIALIZED VIEW — it's implicit DDL, not an analyzable query.

How to tell: PostgreSQL returns a syntax error or the output isn't what you expected.

How to fix: measure with \timing on in psql or time.monotonic() in code:

\timing on
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x;
-- Time: 4128.567 ms

To understand the underlying SELECT, you can indeed EXPLAIN ANALYZE the SELECT that defines the MV (pulling it from pg_matviews):

SELECT definition FROM pg_matviews WHERE matviewname = 'mv_x';
-- Copy the SELECT and put EXPLAIN ANALYZE in front of it

Mistake 5 (conceptual): assuming CONCURRENTLY gives consistent results on reads during the refresh

Symptom: during a concurrent refresh, the dashboard sometimes shows old data, sometimes new data, sometimes a mix.

Why it happens: CONCURRENTLY applies changes row by row during the refresh. A read that happens in the middle of the refresh may see some old rows and some new ones. There's no atomic snapshot from the client.

How to tell: watch the dashboard during a long refresh. You'll see inconsistent partial results.

How to fix: two options:

• Accept the transient inconsistency. For dashboards, it's usually fine — the inconsistency lasts a few seconds and the data is approximate anyway.
• Wrap in an isolated transaction: opening a REPEATABLE READ transaction on the client guarantees a consistent snapshot from the start moment. The transaction doesn't see the refresh's concurrent changes.

async with session.begin():
    # In this transaction, all SELECTs will see the snapshot from the start moment
    result = await session.execute(select(TopPostsWeekly))

For most dashboards, the transient inconsistency is acceptable. If your UX requires a consistent snapshot, use an explicit transaction.

Exercises

Exercise 1: reproduce the unique index error

Create an MV without a unique index. Try a concurrent refresh. Capture the exact error. Then add the index and verify that it works.

-- 1. Create MV without a unique index
CREATE MATERIALIZED VIEW mv_test_no_index AS
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
GROUP BY p.id, p.title
LIMIT 100;

-- 2. Try a concurrent refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_no_index;
-- ERROR:  cannot refresh materialized view "public.mv_test_no_index" concurrently
-- HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

-- 3. FULL refresh works
REFRESH MATERIALIZED VIEW mv_test_no_index;
-- REFRESH MATERIALIZED VIEW

-- 4. Add a unique index
CREATE UNIQUE INDEX idx_mv_test_no_index_pk ON mv_test_no_index (post_id);

-- 5. Retry the concurrent refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_no_index;
-- REFRESH MATERIALIZED VIEW (works)

-- 6. Cleanup
DROP MATERIALIZED VIEW mv_test_no_index;

Exercise 2: empirically measure the blocking of FULL vs CONCURRENTLY

On mv_views_per_post (~50K rows), open two psql sessions. In the first, fire a FULL refresh. In the second, try SELECT count(*) FROM mv_views_per_post during the refresh. Measure how long it takes to respond. Repeat with CONCURRENTLY.

\timing on
REFRESH MATERIALIZED VIEW mv_views_per_post;
-- Time: 2841.124 ms (2.84s)

\timing on
SELECT count(*) FROM mv_views_per_post;
-- Time: ~2800 ms (2.8s, almost all of it waiting for the lock)

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_views_per_post;
-- Time: 4128.567 ms (4.13s)

SELECT count(*) FROM mv_views_per_post;
-- Time: 18.421 ms (responds immediately with data from the previous refresh)

Exercise 3: design a unique index for MVs without a natural PK

For each of these MVs, propose an appropriate unique index:

-- MV A
CREATE MATERIALIZED VIEW mv_views_per_day AS
SELECT
    date_trunc('day', created_at) AS day,
    count(*) AS view_count
FROM views
GROUP BY 1;

-- MV B
CREATE MATERIALIZED VIEW mv_top_authors_per_category AS
SELECT
    category_id,
    author_id,
    count(*) AS post_count
FROM posts
GROUP BY 1, 2
ORDER BY count(*) DESC;

-- MV C
CREATE MATERIALIZED VIEW mv_summary_stats AS
SELECT
    (SELECT count(*) FROM posts) AS total_posts,
    (SELECT count(*) FROM users) AS total_users,
    (SELECT count(*) FROM comments) AS total_comments;

CREATE UNIQUE INDEX idx_mv_views_per_day_pk ON mv_views_per_day (day);

CREATE UNIQUE INDEX idx_mv_tapc_pk ON mv_top_authors_per_category (category_id, author_id);

-- Option A: add a unique constant
CREATE MATERIALIZED VIEW mv_summary_stats AS
SELECT
    1 AS singleton_id,  -- always 1
    (SELECT count(*) FROM posts) AS total_posts,
    (SELECT count(*) FROM users) AS total_users,
    (SELECT count(*) FROM comments) AS total_comments;

CREATE UNIQUE INDEX idx_mv_summary_pk ON mv_summary_stats (singleton_id);

-- Option B: use generated_at as the PK (changes with each refresh)
CREATE MATERIALIZED VIEW mv_summary_stats AS
SELECT
    NOW() AS computed_at,
    (SELECT count(*) FROM posts) AS total_posts,
    ...

-- but NOW() changes with each refresh — it's not stable for the diff. Bad option.

Exercise 4: decide the appropriate mode in 4 scenarios

For each scenario, choose FULL or CONCURRENTLY and justify:

1. Trends dashboard used by 50 concurrent users, refreshes every hour.
2. MV created WITH NO DATA in an Alembic migration, first refresh run by a bootstrap job.
3. Monthly report generated on day 1 at 4 AM, MV queried only by that job.
4. "Global stats" MV with 1 row (app totals), queried in the footer of every page, refreshes every 30 minutes.

Exercise 5: implement refresh with automatic fallback to FULL

Implement a function that tries CONCURRENTLY first, and if it fails because the MV is not populated, falls back automatically to FULL. Useful for jobs that cover both the first refresh and subsequent ones.

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

logger = logging.getLogger(__name__)


ALLOWED_MVS = {
    "mv_top_posts_weekly",
    "mv_views_per_post",
    "mv_post_count_by_author",
}


async def refresh_with_fallback(
    session: AsyncSession,
    mv_name: str,
) -> dict:
    """Try CONCURRENTLY, fall back to FULL if the MV is not populated.

    Use case: idempotent jobs that cover the first refresh and subsequent ones.
    """
    if mv_name not in ALLOWED_MVS:
        return {"success": False, "error": f"MV '{mv_name}' not allowed"}

    started = time.monotonic()

    # Attempt 1: CONCURRENTLY
    try:
        await session.execute(
            text(f"REFRESH MATERIALIZED VIEW CONCURRENTLY {mv_name}")
        )
        await session.commit()
        duration_ms = (time.monotonic() - started) * 1000
        return {
            "success": True,
            "mode": "concurrent",
            "duration_ms": round(duration_ms, 2),
        }
    except SQLAlchemyError as e:
        await session.rollback()
        error_msg = str(e).lower()

        # Detect the specific "not populated" error
        if "not populated" in error_msg or "must be populated" in error_msg:
            logger.info(
                f"MV {mv_name} not populated, falling back to FULL refresh"
            )
            # Attempt 2: FULL
            try:
                await session.execute(text(f"REFRESH MATERIALIZED VIEW {mv_name}"))
                await session.commit()
                duration_ms = (time.monotonic() - started) * 1000
                return {
                    "success": True,
                    "mode": "full",
                    "fallback": True,
                    "duration_ms": round(duration_ms, 2),
                }
            except SQLAlchemyError as e2:
                await session.rollback()
                return {
                    "success": False,
                    "error": f"Fallback FULL also failed: {e2}",
                }

        # Another kind of error (e.g. missing unique index)
        return {"success": False, "error": str(e)}


# Usage:
# result = await refresh_with_fallback(session, "mv_top_posts_weekly")
# if result.get("fallback"):
#     logger.warning("First refresh used FULL — was this MV created WITH NO DATA?")

Summary and next step

In this lesson you learned the central trade-off of refreshes:

• REFRESH MATERIALIZED VIEW name (FULL mode) takes an ACCESS EXCLUSIVE LOCK — it blocks all reads during the refresh. Faster in compute, simpler, no requirements. But it causes an outage if there's traffic.

• REFRESH MATERIALIZED VIEW CONCURRENTLY name takes an EXCLUSIVE LOCK — it blocks other refreshes but allows reads. Slower in compute (~1.3-2×), requires temporary disk, and demands a mandatory unique index on the MV.

• The unique index gotcha: without it, CONCURRENTLY fails with cannot refresh materialized view ... concurrently. Solution: CREATE UNIQUE INDEX on a naturally unique column, a combination, or singleton_id = 1 for 1-row MVs.

• CONCURRENTLY should be the default in production. The extra cost (compute + disk) is fully justified to avoid blocking reads. FULL only in specific cases: first refresh post-WITH NO DATA, maintenance window, MV with no traffic during the refresh, small MV with refresh <100ms.

• CONCURRENTLY doesn't guarantee a consistent snapshot from the client. It applies changes row by row — a read during the refresh may see a mix of old and new versions. For an atomic snapshot, open a REPEATABLE READ transaction.

• FULL is mandatory for the first refresh post-WITH NO DATA. CONCURRENTLY doesn't work on non-populated MVs. Design your deploy so the first refresh is explicitly FULL.

Before moving on, you should be able to:

• Reproduce the "no unique index" error and solve it by adding CREATE UNIQUE INDEX.
• Decide between FULL and CONCURRENTLY for a new MV in under 30 seconds.
• Implement the refresh from SQLAlchemy with handling of the specific error.
• Justify to a colleague why CONCURRENTLY is the default even though it's slower.

Next lesson — indexes on materialized views. Creating the MV with a unique index for CONCURRENTLY is only the beginning. The MV is like a table — for the queries to be fast, it needs the appropriate indexes according to the queries that read it. If your frontend does SELECT * FROM mv_top_posts WHERE category_id = 5, without an index on category_id the query scans the whole MV. Lesson 05 covers strategic indexing of MVs: when to add a B-tree, when composite, when partial — and how to measure whether the index is used with EXPLAIN ANALYZE.

Resources

1. PostgreSQL 16 — REFRESH MATERIALIZED VIEW — complete official reference, includes notes on CONCURRENTLY.
2. PostgreSQL 16 — Explicit Locking — complete table of lock modes and compatibility.
3. Crunchy Data — Materialized Views Best Practices — coverage of the concurrent vs full trade-off with production cases.
4. Hashrocket — Refreshing Materialized Views Concurrently — the unique index gotcha explained with code.
5. pganalyze — Materialized Views Performance — comparative cost analysis.
6. depesz — REFRESH MATERIALIZED VIEW CONCURRENTLY — history of the feature and original benchmarks.

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: Indexes on materialized views — the MV is a table, index it as such.