Analytics use cases: dashboards and reports with materialized views

Lesson overview

This is the lesson where everything you've learned becomes a functional system. So far you've seen creation, refresh CONCURRENTLY vs FULL, and indexes — in the abstract. Here you land it in the central case of the guide: the blog dashboard with 4 panels, each backed by a materialized view, refreshed every hour via cron, exposed by FastAPI endpoints, with the "last updated: X minutes ago" banner on the frontend.

The path is practical: you define the 4 queries the dashboard needs, measure each one with EXPLAIN ANALYZE (the "before" latency), create the corresponding MV with its unique index and the query indexes, measure the "after" latency, compute the speedup, and implement the FastAPI endpoint that returns the data along with the last_updated. The refresh cron is left out (covered in the lesson 04 implementation; lesson 06 of module 6 covers the version with an advisory lock).

By the end you'll have a functional dashboard with concrete numbers to report to the lead: "the dashboard's 4 queries went from X seconds to Y milliseconds. Total load dropped from Z to W. The added refresh costs V seconds per hour, amortized over N requests."

Mental model: the dashboard as a system of 4 independent pieces

A typical dashboard has several panels, each showing a distinct aggregation. The temptation is to think "a single giant MV with everything." It's almost always the wrong decision. Each panel = one independent MV because:

• Each aggregation has its own appropriate refresh frequency (top posts can refresh every hour; total comments can refresh every 6 hours).
• Each aggregation has its own table dependencies — if one refreshes slowly, it shouldn't block the others.
• Each frontend panel makes an independent request — modeling them as separate MVs maps cleanly.
• If one MV has a bug, it only affects its panel — fault isolation.

┌─────────────────────────────────────────────────────────────────┐
│                  Blog dashboard                                 │
│                                                                 │
│   ┌─────────────┐  ┌─────────────┐  ┌─────────────┐             │
│   │ Top posts   │  │ Top         │  │ Posts/month │             │
│   │ week        │  │ commenters  │  │ last 12     │             │
│   │             │  │ of the month│  │             │             │
│   │ MV:         │  │ MV:         │  │ MV:         │             │
│   │ mv_top_     │  │ mv_top_     │  │ mv_posts_   │             │
│   │ posts_      │  │ commenters  │  │ per_month   │             │
│   │ weekly      │  │             │  │             │             │
│   │             │  │             │  │             │             │
│   │ Refresh:    │  │ Refresh:    │  │ Refresh:    │             │
│   │ 1h          │  │ 1h          │  │ 6h          │             │
│   └─────────────┘  └─────────────┘  └─────────────┘             │
│                                                                 │
│   ┌─────────────────────────────────────────────────┐           │
│   │ Most active categories (by views + comments)    │           │
│   │                                                 │           │
│   │ MV: mv_active_categories                        │           │
│   │ Refresh: 1h                                     │           │
│   └─────────────────────────────────────────────────┘           │
│                                                                 │
│   Last updated: 23 minutes ago                                  │
└─────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. One MV per panel is the base pattern. Don't force one MV to cover everything.

2. Each MV has its own SLA. "Monthly trends" tolerate 6 hours of staleness; "recent activity" may require 30 minutes. The refresh frequency is decided per panel.

3. The dashboard's last_updated is the minimum of each MV's last_updated. If one MV refreshed 5 minutes ago and another 47 minutes ago, the dashboard says "47 minutes ago" (the oldest). Communicating the worst staleness to the user avoids misunderstandings.

The case: blog dashboard with 4 panels

Assuming the setup from lesson 03 (tables posts, views, comments, users, categories):

-- Relevant tables
posts(id, title, slug, author_id, category_id, published_at, created_at)
views(id, post_id, user_id, created_at)  -- 18M rows, partitioned by month
comments(id, post_id, author_id, body, created_at)  -- 4.2M rows
users(id, username, email)
categories(id, name, slug)

The 4 dashboard panels:

1. Top 10 most-viewed posts this week.
2. Top 10 commenters of the month (by number of comments).
3. Posts published per month over the last 12 months.
4. Most active categories by views + comments in the last week.

We're going to process each one: measurement without MV → MV design → measurement with MV → FastAPI endpoint.

Panel 1: Top 10 most-viewed posts this week

"Live" query without MV

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

Measurement before

EXPLAIN (ANALYZE, BUFFERS) <previous query>;

Output (example, with views partitioned and partition pruning over the last week):

Limit  (cost=89231.45..89231.48 rows=10)
       (actual time=4214.123..4214.128 rows=10 loops=1)
  Buffers: shared read=78421
  ->  Sort  (Sort Method: top-N heapsort)
        Sort Key: (count(v.id)) DESC
        ->  HashAggregate
              ->  Hash Join
                    Hash Cond: (v.post_id = p.id)
                    ->  Seq Scan on views_2026_05 v
                          Filter: (created_at > NOW() - INTERVAL '7 days')
                    ->  Hash
                          ->  Seq Scan on posts p
                                Filter: (published_at IS NOT NULL)

Planning Time: 1.234 ms
Execution Time: 4216.421 ms

~4.2 seconds. Cost dominated by scanning ~3M rows of views_2026_05 and aggregating them.

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
JOIN views v ON v.post_id = p.id
WHERE v.created_at > NOW() - INTERVAL '7 days'
  AND p.published_at IS NOT NULL
GROUP BY p.id, p.title, p.slug
ORDER BY view_count DESC
LIMIT 10;

-- Mandatory unique index for CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_top_posts_pk ON mv_top_posts_weekly (post_id);

The MV has exactly 10 rows. It needs no more indexes (all queries read the 10).

Measurement after

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mv_top_posts_weekly ORDER BY view_count DESC;

Output:

Sort  (cost=1.27..1.30 rows=10)
      (actual time=0.041..0.042 rows=10 loops=1)
  ->  Seq Scan on mv_top_posts_weekly
        Buffers: shared hit=1

Planning Time: 0.123 ms
Execution Time: 0.087 ms

~0.09 milliseconds. ~47,000× faster.

FastAPI endpoint

# api/dashboard.py
from datetime import datetime, timezone
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

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


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


class TopPostsResponse(BaseModel):
    items: list[TopPostItem]
    last_updated: datetime
    staleness_minutes: int


@router.get("/top-posts-weekly", response_model=TopPostsResponse)
async def get_top_posts_weekly(
    session: AsyncSession = Depends(get_session),
) -> TopPostsResponse:
    """Top 10 posts of the last week, from mv_top_posts_weekly."""
    stmt = select(TopPostsWeekly).order_by(TopPostsWeekly.view_count.desc())
    result = await session.execute(stmt)
    rows = result.scalars().all()

    if not rows:
        now = datetime.now(timezone.utc)
        return TopPostsResponse(items=[], last_updated=now, staleness_minutes=0)

    last_updated = rows[0].computed_at
    now = datetime.now(timezone.utc)
    staleness = int((now - last_updated).total_seconds() / 60)

    return TopPostsResponse(
        items=[
            TopPostItem(
                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,
    )

Panel 2: Top 10 commenters of the month

Live query

SELECT
    u.id AS user_id,
    u.username,
    count(c.id) AS comment_count
FROM users u
JOIN comments c ON c.author_id = u.id
WHERE c.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.username
ORDER BY comment_count DESC
LIMIT 10;

Measurement before

Limit  (actual time=2841.421..2841.428 rows=10 loops=1)
  Buffers: shared read=42183
  Execution Time: 2843.234 ms

~2.8 seconds.

Create the MV

CREATE MATERIALIZED VIEW mv_top_commenters_monthly AS
SELECT
    u.id AS user_id,
    u.username,
    count(c.id) AS comment_count,
    NOW() AS computed_at
FROM users u
JOIN comments c ON c.author_id = u.id
WHERE c.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.username
ORDER BY comment_count DESC
LIMIT 10;

CREATE UNIQUE INDEX idx_mv_top_commenters_pk ON mv_top_commenters_monthly (user_id);

Measurement after

Execution Time: 0.078 ms

~36,000× faster.

Endpoint

class TopCommenterItem(BaseModel):
    user_id: int
    username: str
    comment_count: int


class TopCommentersResponse(BaseModel):
    items: list[TopCommenterItem]
    last_updated: datetime
    staleness_minutes: int


@router.get("/top-commenters-monthly", response_model=TopCommentersResponse)
async def get_top_commenters_monthly(
    session: AsyncSession = Depends(get_session),
) -> TopCommentersResponse:
    from app.models.mv import TopCommentersMonthly

    stmt = select(TopCommentersMonthly).order_by(
        TopCommentersMonthly.comment_count.desc()
    )
    result = await session.execute(stmt)
    rows = result.scalars().all()

    if not rows:
        now = datetime.now(timezone.utc)
        return TopCommentersResponse(items=[], last_updated=now, staleness_minutes=0)

    last_updated = rows[0].computed_at
    now = datetime.now(timezone.utc)
    staleness = int((now - last_updated).total_seconds() / 60)

    return TopCommentersResponse(
        items=[
            TopCommenterItem(
                user_id=r.user_id,
                username=r.username,
                comment_count=r.comment_count,
            )
            for r in rows
        ],
        last_updated=last_updated,
        staleness_minutes=staleness,
    )

Panel 3: Posts published per month over the last 12 months

Live query

SELECT
    date_trunc('month', published_at) AS month,
    count(*) AS post_count
FROM posts
WHERE published_at > NOW() - INTERVAL '12 months'
  AND published_at IS NOT NULL
GROUP BY 1
ORDER BY 1;

Measurement before

HashAggregate  (actual time=823.421..823.521 rows=12 loops=1)
  Buffers: shared read=8421
  Execution Time: 824.234 ms

~824ms. Not that terrible (the posts table is only 250K rows) but the dashboard calls this on every load.

Create the MV

CREATE MATERIALIZED VIEW mv_posts_per_month AS
SELECT
    date_trunc('month', published_at) AS month,
    count(*) AS post_count,
    NOW() AS computed_at
FROM posts
WHERE published_at > NOW() - INTERVAL '12 months'
  AND published_at IS NOT NULL
GROUP BY 1
ORDER BY 1;

-- month is naturally unique
CREATE UNIQUE INDEX idx_mv_posts_per_month_pk ON mv_posts_per_month (month);

Measurement after

Execution Time: 0.034 ms

~24,000× faster.

Endpoint

class PostsPerMonthItem(BaseModel):
    month: datetime
    post_count: int


class PostsPerMonthResponse(BaseModel):
    items: list[PostsPerMonthItem]
    last_updated: datetime
    staleness_minutes: int


@router.get("/posts-per-month", response_model=PostsPerMonthResponse)
async def get_posts_per_month(
    session: AsyncSession = Depends(get_session),
) -> PostsPerMonthResponse:
    from app.models.mv import PostsPerMonth

    stmt = select(PostsPerMonth).order_by(PostsPerMonth.month)
    result = await session.execute(stmt)
    rows = result.scalars().all()

    if not rows:
        now = datetime.now(timezone.utc)
        return PostsPerMonthResponse(items=[], last_updated=now, staleness_minutes=0)

    last_updated = rows[0].computed_at
    now = datetime.now(timezone.utc)
    staleness = int((now - last_updated).total_seconds() / 60)

    return PostsPerMonthResponse(
        items=[PostsPerMonthItem(month=r.month, post_count=r.post_count) for r in rows],
        last_updated=last_updated,
        staleness_minutes=staleness,
    )

Panel 4: Most active categories (views + comments) last week

Live query

SELECT
    cat.id AS category_id,
    cat.name AS category_name,
    count(DISTINCT v.id) AS view_count,
    count(DISTINCT cm.id) AS comment_count,
    (count(DISTINCT v.id) + count(DISTINCT cm.id) * 5) AS activity_score
FROM categories cat
JOIN posts p ON p.category_id = cat.id
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
LEFT JOIN comments cm ON cm.post_id = p.id
    AND cm.created_at > NOW() - INTERVAL '7 days'
GROUP BY cat.id, cat.name
ORDER BY activity_score DESC
LIMIT 10;

Measurement before

Limit  (actual time=8421.234..8421.342 rows=10 loops=1)
  Buffers: shared read=121342
  Execution Time: 8423.421 ms

~8.4 seconds. The most expensive of the 4 panels — triple joins with two count(DISTINCT).

Create the MV

CREATE MATERIALIZED VIEW mv_active_categories_weekly AS
SELECT
    cat.id AS category_id,
    cat.name AS category_name,
    count(DISTINCT v.id) AS view_count,
    count(DISTINCT cm.id) AS comment_count,
    (count(DISTINCT v.id) + count(DISTINCT cm.id) * 5) AS activity_score,
    NOW() AS computed_at
FROM categories cat
JOIN posts p ON p.category_id = cat.id
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '7 days'
LEFT JOIN comments cm ON cm.post_id = p.id
    AND cm.created_at > NOW() - INTERVAL '7 days'
GROUP BY cat.id, cat.name
ORDER BY activity_score DESC
LIMIT 10;

CREATE UNIQUE INDEX idx_mv_active_categories_pk ON mv_active_categories_weekly (category_id);

Measurement after

Execution Time: 0.067 ms

~125,000× faster.

Endpoint

class CategoryActivityItem(BaseModel):
    category_id: int
    category_name: str
    view_count: int
    comment_count: int
    activity_score: int


class ActiveCategoriesResponse(BaseModel):
    items: list[CategoryActivityItem]
    last_updated: datetime
    staleness_minutes: int


@router.get("/active-categories-weekly", response_model=ActiveCategoriesResponse)
async def get_active_categories_weekly(
    session: AsyncSession = Depends(get_session),
) -> ActiveCategoriesResponse:
    from app.models.mv import ActiveCategoriesWeekly

    stmt = select(ActiveCategoriesWeekly).order_by(
        ActiveCategoriesWeekly.activity_score.desc()
    )
    result = await session.execute(stmt)
    rows = result.scalars().all()

    if not rows:
        now = datetime.now(timezone.utc)
        return ActiveCategoriesResponse(items=[], last_updated=now, staleness_minutes=0)

    last_updated = rows[0].computed_at
    now = datetime.now(timezone.utc)
    staleness = int((now - last_updated).total_seconds() / 60)

    return ActiveCategoriesResponse(
        items=[
            CategoryActivityItem(
                category_id=r.category_id,
                category_name=r.category_name,
                view_count=r.view_count,
                comment_count=r.comment_count,
                activity_score=r.activity_score,
            )
            for r in rows
        ],
        last_updated=last_updated,
        staleness_minutes=staleness,
    )

Aggregator endpoint: GET /dashboard

The frontend probably prefers a single request that returns the 4 panels together:

class DashboardResponse(BaseModel):
    top_posts_weekly: TopPostsResponse
    top_commenters_monthly: TopCommentersResponse
    posts_per_month: PostsPerMonthResponse
    active_categories_weekly: ActiveCategoriesResponse
    overall_last_updated: datetime
    overall_staleness_minutes: int


@router.get("/", response_model=DashboardResponse)
async def get_full_dashboard(
    session: AsyncSession = Depends(get_session),
) -> DashboardResponse:
    """Full dashboard: 4 panels in a single request."""
    top_posts = await get_top_posts_weekly(session)
    top_commenters = await get_top_commenters_monthly(session)
    posts_month = await get_posts_per_month(session)
    active_cats = await get_active_categories_weekly(session)

    # overall_last_updated = the oldest of the 4
    all_last_updated = [
        top_posts.last_updated,
        top_commenters.last_updated,
        posts_month.last_updated,
        active_cats.last_updated,
    ]
    overall_last_updated = min(all_last_updated)
    overall_staleness = max([
        top_posts.staleness_minutes,
        top_commenters.staleness_minutes,
        posts_month.staleness_minutes,
        active_cats.staleness_minutes,
    ])

    return DashboardResponse(
        top_posts_weekly=top_posts,
        top_commenters_monthly=top_commenters,
        posts_per_month=posts_month,
        active_categories_weekly=active_cats,
        overall_last_updated=overall_last_updated,
        overall_staleness_minutes=overall_staleness,
    )

Optimization: the 4 queries can run in parallel with asyncio.gather:

import asyncio

@router.get("/parallel", response_model=DashboardResponse)
async def get_full_dashboard_parallel(
    session: AsyncSession = Depends(get_session),
) -> DashboardResponse:
    top_posts, top_commenters, posts_month, active_cats = await asyncio.gather(
        get_top_posts_weekly(session),
        get_top_commenters_monthly(session),
        get_posts_per_month(session),
        get_active_categories_weekly(session),
    )

    # ... same overall_* computation

Careful: gather with the same session can cause problems (the session isn't safe for concurrent use). For true parallelism, open separate sessions:

async def get_full_dashboard_truly_parallel() -> DashboardResponse:
    async with async_session_factory() as s1, \
               async_session_factory() as s2, \
               async_session_factory() as s3, \
               async_session_factory() as s4:
        results = await asyncio.gather(
            get_top_posts_weekly(s1),
            get_top_commenters_monthly(s2),
            get_posts_per_month(s3),
            get_active_categories_weekly(s4),
        )
        # ... process

For dashboards with MVs (where each query is <1ms), parallelism barely helps — the 4 sequential queries add up to ~0.3ms total. The optimization only matters if the queries are slow.

Refresh cron for the 4 MVs

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

logger = logging.getLogger(__name__)


# Each MV with its ideal frequency
MVS_TO_REFRESH = [
    ("mv_top_posts_weekly", 60),         # every 60 min
    ("mv_top_commenters_monthly", 60),   # every 60 min
    ("mv_posts_per_month", 360),         # every 6h (changes little)
    ("mv_active_categories_weekly", 60), # every 60 min
]


async def refresh_all_dashboard_mvs() -> dict:
    """Refresh all the dashboard MVs. Reports results."""
    results = {}
    async with async_session_factory() as session:
        for mv_name, _interval in MVS_TO_REFRESH:
            r = await refresh_mv(session, mv_name, concurrent=True)
            results[mv_name] = r
            if r["success"]:
                logger.info(f"Refreshed {mv_name} in {r['duration_ms']}ms")
            else:
                logger.error(f"Failed {mv_name}: {r.get('error')}")
    return results


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

Schedule with OS cron:

# crontab
0 * * * *  cd /app && python -m jobs.refresh_dashboard >> /var/log/refresh.log 2>&1

Or with pg_cron (PostgreSQL extension, covered briefly in module 7):

SELECT cron.schedule(
    'refresh-dashboard-mvs',
    '0 * * * *',
    $$
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_posts_weekly;
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_commenters_monthly;
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_active_categories_weekly;
    $$
);

SELECT cron.schedule(
    'refresh-mv-posts-per-month',
    '0 */6 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_posts_per_month'
);

Critical note: this cron does not protect against concurrent refreshes. If two instances of the cron start at the same time (double deploy, network glitch), both try to refresh — the second hangs waiting for the first (remember CONCURRENTLY allows SELECT but not other refreshes). Module 6 covers pg_try_advisory_lock so the second skips instead of waiting.

Executive report: the complete speedup

Your lead asked for "metrics on the change." Here's the report:

Before (without MVs)

If the dashboard loads 200 times/hour with the 4 queries each time, that's 16.3s × 200 = 54 minutes of DB time per hour.

After (with MVs)

For 200 loads/hour: 0.266ms × 200 = 53 milliseconds of DB time per hour (reads).

Refresh cost

Net comparison

Report for the lead (1 paragraph):

"I implemented MVs for the dashboard's 4 panels. Endpoint latency went from ~5s to ~50ms (100×). DB load for those endpoints dropped from 54 min/h to 17s/h (~190×). The added refresh is 17.3s/h, scheduled with an hourly cron (except posts-per-month, every 6h). Max staleness 60 min, validated with product. The PR includes the 4 migrations, the FastAPI endpoints, the refresh cron, and the 'last updated' banner on the frontend."

Why does this matter on the job?

1. The exact speedup number convinces the lead. "It improved a lot" means nothing. "It went from 5 seconds to 50 milliseconds, a 100× improvement, with a refresh that costs 17 seconds per hour amortized over 200 requests" is a defensible technical decision. You learn the quantitative framing by doing this exercise.

2. One MV per panel is the correct architectural decision for 95% of dashboards. The reflex of "one giant MV with everything" is simpler at first glance but creates coupling (a bug in one computation affects all panels, a slow-to-refresh MV blocks everything). Modeling panels as independent MVs is mature.

3. The 'last updated' banner is non-negotiable UX. Without it, users report "incorrect data" every time they see discrepancies. With it, they understand it's a timestamped snapshot and accept it. It's 5 minutes of implementation that avoid hours of support.

4. The refresh cron without an advisory lock is a latent bug. Right now, two concurrent instances of the cron compete — the second hangs waiting for the first. For apps with a single cron server, it's not a problem. For apps with multiple instances or k8s CronJobs with replicas, it is. The solution arrives in module 6.

5. EXPLAIN ANALYZE before/after is the PR evidence. When you submit the "implement MVs for the dashboard" PR, the reviewer will ask "how much did it improve?" The before/after plans are the answer. Without them, it's your word against theirs.

Pitfalls and common mistakes

Mistake 1 (architectural): a giant MV with multiple joins for all the panels

Symptom: someone creates mv_dashboard_full that does 8 joins and aggregates 12 metrics. Each panel queries it filtering different columns.

Why it happens: it seems more efficient to "not duplicate joins." In practice:

• The refresh is super slow (multiple complex aggregations).
• If one aggregation has a bug, it affects all 4 panels.
• Refresh frequency = that of the panel that needs the freshest data (bottleneck).

How to tell: if your MV has >3 aggregation columns or is queried filtering different dimensions in each query, it's probably a candidate for splitting.

How to fix: one MV per panel/logical aggregation. It's OK for some to share data (e.g. two MVs over views); the extra cost is compute, not operational complexity.

Mistake 2 (practical): not communicating staleness to the user

Symptom: the dashboard shows "Top 10 posts" without indicating when it was computed. Support receives tickets "the order is wrong."

Why it happens: the developer assumed "people understand it's approximate."

How to tell: read support tickets. If there are reports of "incorrect data" on the dashboard, it's almost certainly a staleness-communication problem.

How to fix: a prominent "Last updated: X min ago" banner. It's the standard convention (GitHub Insights, GA4, Stripe).

Mistake 3 (practical): OS cron without protection against concurrent runs

Symptom: during a deploy, the old cron and the new one start at the same time. Both try to refresh. The second hangs 5 minutes waiting for the first. The log fills with timeouts.

Why it happens: REFRESH CONCURRENTLY allows reads but blocks other refreshes (takes an EXCLUSIVE LOCK).

How to tell: pg_stat_activity shows two sessions, one with wait_event = 'relation' waiting for the other.

How to fix: pg_try_advisory_lock before the refresh. If the lock isn't available, the second skips:

SELECT pg_try_advisory_lock(hashtext('refresh_mv_top_posts'));  -- true or false
-- If true: refresh + unlock
-- If false: skip

Full pattern in module 6.

Mistake 4 (conceptual): assuming gather with the same session parallelizes

Symptom: you try asyncio.gather(query1(session), query2(session)) with the same session. Errors of "session is in use" or strange results.

Why it happens: SQLAlchemy async sessions aren't thread-safe or concurrent-safe. A session runs one transaction at a time.

How to tell: InvalidRequestError errors or intermittent behavior.

How to fix: use independent sessions (async with async_session_factory() per query) or run sequentially. For dashboards where each query is <1ms, sequential is fine — the complexity isn't worth it.

Mistake 5 (operational): not monitoring the refresh time

Symptom: the refresh takes 30s in production after data growth. Nobody notices until the cron starts overlapping (takes longer than the interval).

Why it happens: without monitoring, the refresh time is invisible.

How to tell: logs show "Refreshed mv_x in 30000ms" where before it was 5000ms. It's a sign of dataset growth.

How to fix: log the duration of each refresh (the refresh_mv function already does it). Configure an alert if the duration is >X seconds. If it grows consistently, consider:

• Filtering data in the SELECT (e.g. only the last 90 days instead of the full history).
• Partitioning the source table (module 4).
• Accepting the cost and increasing the refresh interval.

Exercises

Exercise 1: identify panels that are MV candidates in an app

For an app you know (it can be hypothetical: e-commerce, social network, SaaS platform), list 3-5 dashboards or reports. For each one, fill out the matrix: underlying query, estimated latency, usage frequency, acceptable staleness, MV/no decision.

Exercise 2: measure before/after in your local database

On your database with test data (lesson 03), run the "top 10 weekly posts" query without an MV, measure with EXPLAIN ANALYZE. Create the MV. Measure the query against the MV. Compute the speedup. Report the 3 values: latency without MV, latency with MV, improvement factor.

-- Without MV
EXPLAIN ANALYZE
SELECT p.id, p.title, count(v.id) AS view_count
FROM posts p
JOIN views v ON v.post_id = p.id
WHERE v.created_at > NOW() - INTERVAL '7 days'
  AND p.published_at IS NOT NULL
GROUP BY p.id, p.title
ORDER BY view_count DESC LIMIT 10;
-- Execution Time: ~3814 ms (varies by hardware)

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

CREATE UNIQUE INDEX ON mv_test_top_posts (post_id);

-- With MV
EXPLAIN ANALYZE
SELECT * FROM mv_test_top_posts ORDER BY view_count DESC;
-- Execution Time: ~0.087 ms

-- Speedup: 3814 / 0.087 ≈ 43,800×

DROP MATERIALIZED VIEW mv_test_top_posts;

Exercise 3: implement the complete endpoint for one panel

Implement the GET /dashboard/posts-per-month endpoint end-to-end:

1. SQL to create the MV.
2. SQLAlchemy 2.0 model mapped to the MV.
3. Pydantic response schema.
4. FastAPI endpoint function.
5. A test verifying that it returns last_updated and staleness_minutes.

CREATE MATERIALIZED VIEW mv_posts_per_month AS
SELECT
    date_trunc('month', published_at) AS month,
    count(*) AS post_count,
    NOW() AS computed_at
FROM posts
WHERE published_at > NOW() - INTERVAL '12 months'
  AND published_at IS NOT NULL
GROUP BY 1
ORDER BY 1;

CREATE UNIQUE INDEX idx_mv_posts_per_month_pk ON mv_posts_per_month (month);

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

from app.db import Base


class PostsPerMonth(Base):
    __tablename__ = "mv_posts_per_month"

    month: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), primary_key=True
    )
    post_count: Mapped[int] = mapped_column(BigInteger)
    computed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))

# schemas/dashboard.py
from datetime import datetime
from pydantic import BaseModel


class PostsPerMonthItem(BaseModel):
    month: datetime
    post_count: int


class PostsPerMonthResponse(BaseModel):
    items: list[PostsPerMonthItem]
    last_updated: datetime
    staleness_minutes: int

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

from app.deps import get_session
from app.models.mv import PostsPerMonth
from app.schemas.dashboard import PostsPerMonthItem, PostsPerMonthResponse

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


@router.get("/posts-per-month", response_model=PostsPerMonthResponse)
async def get_posts_per_month(
    session: AsyncSession = Depends(get_session),
) -> PostsPerMonthResponse:
    stmt = select(PostsPerMonth).order_by(PostsPerMonth.month)
    result = await session.execute(stmt)
    rows = result.scalars().all()

    if not rows:
        now = datetime.now(timezone.utc)
        return PostsPerMonthResponse(items=[], last_updated=now, staleness_minutes=0)

    last_updated = rows[0].computed_at
    now = datetime.now(timezone.utc)
    staleness = int((now - last_updated).total_seconds() / 60)

    return PostsPerMonthResponse(
        items=[PostsPerMonthItem(month=r.month, post_count=r.post_count) for r in rows],
        last_updated=last_updated,
        staleness_minutes=staleness,
    )

# tests/test_dashboard.py
import pytest
from httpx import AsyncClient


@pytest.mark.asyncio
async def test_posts_per_month_returns_metadata(async_client: AsyncClient) -> None:
    response = await async_client.get("/dashboard/posts-per-month")
    assert response.status_code == 200

    data = response.json()
    assert "items" in data
    assert "last_updated" in data
    assert "staleness_minutes" in data
    assert isinstance(data["staleness_minutes"], int)
    assert data["staleness_minutes"] >= 0


@pytest.mark.asyncio
async def test_posts_per_month_items_have_required_fields(async_client: AsyncClient) -> None:
    response = await async_client.get("/dashboard/posts-per-month")
    data = response.json()

    if data["items"]:
        item = data["items"][0]
        assert "month" in item
        assert "post_count" in item
        assert isinstance(item["post_count"], int)

Exercise 4: compute the quantitative ROI of implementing the 4 MVs

Assuming your app has 200 dashboard loads/hour, each with the 4 queries:

• Compute DB time/hour before (without MVs).
• Compute DB time/hour after (with MVs + hourly refresh).
• Compute the net savings.
• Report the ROI in one sentence.

Summary and next step

In this lesson you built a functional dashboard system:

• One MV per panel is the correct pattern. Modeling independent aggregations as separate MVs avoids coupling and allows independent refresh.

• Each MV has its own refresh frequency. Monthly trends can refresh every 6h; recent activity every hour. The frequency is decided per panel according to staleness tolerance.

• The last_updated + staleness_minutes banner is non-negotiable UX. It tells the user the data is approximate and avoids "incorrect data" reports.

• The quantitative report (before/after latency + DB time saved) is the justification of the decision. Without numbers, it's opinion; with numbers, it's engineering.

• The aggregator endpoint (GET /dashboard) consolidates the 4 panels into a single request. The overall_last_updated is the oldest of the 4 (worst staleness communicated explicitly).

• The cron without an advisory lock is a latent bug for apps with multiple scheduler instances. The solution arrives in module 6.

Before moving on, you should be able to:

• Receive a dashboard brief with 4-6 panels and design the appropriate MV system.
• Implement the FastAPI endpoint with last_updated and staleness_minutes from the computed_at convention.
• Report the speedup with numbers (latency, DB time, ROI).
• Anticipate the need for an advisory lock for safe refresh.

Next lesson — MVs vs application cache: decision matrix. So far you assumed "the case is an MV." But in production you'll have cases where Redis (application cache) or a dedicated analytics service are better. Lesson 07 gives you the decision matrix with quantitative criteria: target latency, key cardinality, query complexity, retention, operational cost. You'll come out with the vocabulary and criteria to defend to the lead "why this case is MV and this other one is Redis."

Resources

1. PostgreSQL 16 — date_trunc() — reference for the function used in mv_posts_per_month.
2. Crunchy Data — Building Dashboards with PostgreSQL — dashboard patterns in production with MVs.
3. pganalyze — Materialized Views for Reporting — usage analysis for reports and analytics.
4. Hashrocket — MV Strategies in PostgreSQL — operational patterns with code.
5. SQLAlchemy 2.0 — async session usage — reference for concurrent sessions.
6. FastAPI — Async Dependencies — patterns for async endpoints with a DB.
7. pg_cron extension — official repo for scheduled refresh inside PostgreSQL.

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: MVs vs application cache — the decision matrix with quantitative criteria.