Module 5 — Materialized Views: dashboards and reports that fly without adding another service to your stack

Where are we?

You're coming from module 4 with a concrete win: you partitioned events (50M rows) by month, verified that the planner discards partitions with enable_partition_pruning, and your date-range queries fly — from 4.5 seconds to 80 milliseconds in the typical case. pg_partman keeps the lifecycle automated and you no longer think about "someone has to create June's partition."

But there's a scenario where partitioning alone isn't enough: dashboards and reports that need live aggregations across all partitions. "Top 10 most-viewed posts this week," "total comments per month over the last 12 months," "active users by category" — these are queries that can't be pruned because they scan a wide history, do joins, and apply GROUP BY with ORDER BY count(*) DESC. Every request to the dashboard recomputes everything. If the dashboard loads 200 times per hour, that's 200 full scans of massive tables. Latency climbs into the seconds and the database suffers.

The common architectural exit is to add an analytics service: ClickHouse, BigQuery, Redshift, a Druid cluster. More operations, more cost, more pipeline latency (data reaches the warehouse minutes or hours late anyway). For many cases, you don't need it. PostgreSQL has materialized views: the result of the query is physically stored on disk as a table, indexable, and refreshed when you decide. The dashboard query goes from 4 seconds to 8 milliseconds because it no longer computes — it just reads.

This module teaches you materialized views (MVs) in depth: creation, refresh with CONCURRENTLY (non-negotiable for production), indexes on MVs (yes, they're just as necessary as on any table), the three refresh strategies (app-driven, scheduled, trigger-driven), and the anti-patterns. You'll come out able to decide when an MV replaces a slow query, design it to support concurrent refresh, schedule it with cron, and communicate to the user that the data has an acceptable staleness.

Professional objective

By the end of this module you'll be able to:

• Distinguish a VIEW (runs every time) from a MATERIALIZED VIEW (stored result, refreshed on demand) and pick the right one for the use case.
• Create materialized views with the mandatory unique index to support REFRESH CONCURRENTLY from day 1.
• Design refresh strategies according to the case (app-driven, scheduled, trigger-driven) and pick the appropriate one with quantitative criteria.
• Index materialized views just like normal tables so the queries against them are fast.
• Build a blog dashboard with MVs that replace 4-second queries with 8-millisecond lookups, with EXPLAIN ANALYZE before/after.
• Decide between MV and application cache (Redis) using a matrix of quantitative criteria: target latency, retention, query complexity, operational cost.
• Anticipate the module 6 pattern: using pg_try_advisory_lock to avoid concurrent refreshes when two crons fire at the same time.

Why does this module matter on the job?

1. Dashboards are the silent performance killers. Every PM or stakeholder asks for "I need to see the top X of Y per week," and that endpoint ends up running a query with 3 joins, two GROUP BYs, and ORDER BY count(*) DESC LIMIT 10. The first version works in dev (10K rows). In production (100M rows) it becomes a headache. Materialized views are PostgreSQL's idiomatic architectural answer before migrating to a warehouse.

2. They avoid adding a new service when you don't need one. The common reflex "we need analytics, let's add ClickHouse" adds operations, cost, and pipeline latency. For many cases (datasets <100M rows, hourly refresh acceptable), a well-designed MV gives the same functional result with zero additional services. The architectural decision to stay in PostgreSQL — when it applies — is a senior skill.

3. They're the explicit alternative to slow COUNT(*) over massive tables. In guide #12 you learned that SELECT count(*) FROM events over 50M rows takes seconds because PostgreSQL doesn't keep a counter in memory. An MV with SELECT count(*) AS total FROM events refreshed every hour turns that query into an instant lookup. It's one of the most-used patterns in production.

4. The refresh strategy is a product decision, not a technical one. "How often do we refresh?" is a conversation with the PM: "users tolerate seeing 1-hour-old data on the trends dashboard, they don't tolerate 5-minute-old data on the recent-activity dashboard." Knowing how to structure that conversation with numbers (refresh latency, compute cost) positions you as a mature technical interlocutor.

5. Communicating staleness to the user is UX. An MV implies the data isn't from the exact moment. Showing "Last updated: 47 minutes ago" on the dashboard is standard practice. Without that piece, users report "the number is wrong" when in reality it's just stale. It's joint work between backend and frontend that's designed from day 1.

A scenario that illustrates the module

Imagine you arrive on Monday and your lead assigns you this ticket:

#1247 — Blog dashboard takes 8-12 seconds to load

The marketing team reported that the internal "blog metrics" dashboard takes 8-12 seconds to load. The queries it shows are: "top 10 posts by total views," "top 10 commenters of the month," "posts published per month over the last year," "most active categories." Table posts: 250K rows. Table views: 18M rows (partitioned by month since module 4). Table comments: 4.2M rows.

Constraint: we can't add another service (ClickHouse/BigQuery are vetoed for cost and operations).

Acceptable: the dashboard data can be up to 1 hour out of date.

Goal: dashboard <500ms.

You apply what you learn in the module:

1. Lesson 02: you confirm that none of those queries is "exact-moment data." All tolerate staleness. → Materialized views apply, you don't need a Redis cache or an external service.
2. Lesson 03: you create the first MV (top_posts_weekly) with CREATE MATERIALIZED VIEW and add the mandatory unique index for concurrent refresh.
3. Lesson 04: you distinguish REFRESH FULL (blocks reads, ~30s for your MV) from REFRESH CONCURRENTLY (doesn't block, ~35s but the dashboard keeps responding). You choose concurrent for production.
4. Lesson 05: you index the MVs by the columns the frontend filters on (category_id, period_start) — without indexes, the lookup on a large MV is still slow.
5. Lesson 06: you build the 4 dashboard queries as separate MVs and compare EXPLAIN ANALYZE before/after. You report: "queries went from 4-12s to 6-40ms."
6. Lesson 07: you decide the architecture: MV every hour via cron for general trends, direct live query for "a just-published post has N views" (where the user does need fresh data). You document the decision with the MV vs Redis vs live-query matrix.
7. Lesson 08: you deliver the module project: the functional dashboard with a visible last_updated, scheduled refresh, and a before/after benchmark.

Closing the ticket is a PR with 4 MVs, a cron job, one FastAPI endpoint per dashboard panel, and a README explaining "why we didn't use Redis here" for future maintainers.

Module map

Lesson	Topic	What you'll learn
02	Views vs materialized views: when each one	Conceptual difference between VIEW (query rules) and MATERIALIZED VIEW (stored table). The staleness trade-off. When to pick each one with a decision tree.
03	Creation and refresh: fundamentals	CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, DROP. Your first runnable MV end-to-end.
04	Refresh CONCURRENTLY vs FULL: locking trade-offs	REFRESH FULL blocks, REFRESH CONCURRENTLY doesn't — but requires a unique index. The most common gotcha. When each one.
05	Indexes on materialized views	The MV is like a table — index it according to the queries that read it. Without indexes, the lookup is slow even though the MV exists.
06	Analytics use cases: dashboards and reports	The central case of the guide: a blog dashboard with 4 panels. Each panel = one MV. Before/after with EXPLAIN ANALYZE.
07	MVs vs application cache: decision matrix	Quantitative criteria to choose MV vs Redis vs live query. When each one wins.
08	Module project: blog dashboard with MVs	Deliverable: 4 MVs + cron + FastAPI endpoints + before/after benchmark.

Connection to the capstone project

The final project of the guide (module 8) refactors the Blog API. One of the pieces is top_posts_weekly — a materialized view that computes the 10 most-viewed posts of the last week, refreshed every hour. What you build in this module (especially the lesson 08 project) is functionally identical to that piece. Module 6 adds protection with pg_try_advisory_lock to avoid concurrent refreshes when two crons fire at once.

In other words: you finish this module with a component of the final project already built and validated. You only carry it over to the project repo in module 8.

What this module does NOT cover

• JSONB and advanced operators — covered in modules 1-2. MVs can contain JSONB columns, but the module doesn't teach JSONB design.
• Full-text search — covered in module 3. MVs can materialize FTS results (a real case), but the module doesn't teach tsvector/tsquery.
• Partitioning — covered in module 4. We assume you already partitioned events/views. MVs are built on top of partitioned tables but we don't teach partitioning here.
• Advisory locks and savepoints — covered in module 6. We anticipate the pg_try_advisory_lock pattern for safe refresh but don't go deep.
• Extensions (pg_cron, pg_partman) — module 7 covers extensions in general. Here we mention pg_cron as a scheduler but don't teach its detailed installation.
• Materialized views in other DBMSs — Oracle, SQL Server have their own flavor. This guide is PostgreSQL 16+ specifically.
• Streaming materializations (Materialize, Flink, ksqlDB) — these are products of another category (CDC + streaming aggregations). If you need them, you're not in PostgreSQL MV territory.

Pitfalls to avoid while taking the module

1. Don't skip lesson 02 (views vs materialized views) even though it sounds basic. The most common mistake in production isn't "I implemented an MV wrong," it's "I used an MV when I should have used Redis" or "I used a view when I should have used an MV." The framing of when each one wins is in lesson 02. Without that clear decision, the technical lessons lose their purpose.

2. Don't skip the unique index for REFRESH CONCURRENTLY. 80% of production failures when implementing MVs are: someone created the MV, queried it, everything fine — then tries to refresh concurrently and gets ERROR: cannot refresh materialized view "x" concurrently because they forgot the unique index. Lesson 04 makes it explicit; keep it in mind from lesson 03.

3. Don't think REFRESH FULL is acceptable in production "because it's faster." FULL is faster in compute (no need to compare diffs), but it blocks all reads on the MV during the refresh. For a 1GB MV that takes 30 seconds, that's 30 seconds where the dashboard doesn't respond. CONCURRENTLY should be your default. FULL only in explicit maintenance windows or on the very first initial refresh.

4. Don't forget to index the MV. Creating the MV only materializes the result of the SELECT. If the frontend does SELECT * FROM mv_top_posts WHERE category_id = 5, without an index on category_id the query scans the whole MV. The MV isn't magic — it's still a table with the same indexing rules.

5. Don't implement MVs without discussing staleness with product. "How often do we refresh?" is not a technical decision. Agree with the PM beforehand: "users tolerate X minutes of staleness on this dashboard." Document that decision. Otherwise, you'll have recurring reports of "the data is wrong" that are really "the data is old."

6. Don't underestimate the disk cost of MVs. An MV of a SELECT that returns 50M rows takes up the space of 50M rows. If you have 10 large MVs, that's 500M rows duplicated on disk. Lesson 05 touches on pg_relation_size('mv_name') to audit. An abandoned MV (nobody queries it but it keeps refreshing) is pure waste.

Self-assessment question

Before starting this module, can you answer these questions? If you hesitate on any, review the suggested reference.

1. What does EXPLAIN ANALYZE do and how do you interpret Seq Scan vs Index Scan? → If you hesitate, review guide #12 on query tuning.
2. How does a VIEW (which you already know from classic SQL) differ from a normal table? → If you hesitate, a VIEW is just "a named SELECT" — it doesn't store data, it runs every time you query it. Review the basics of guide #8.
3. Did you partition, or are you going to partition, large tables? → If you didn't finish module 4, several examples in this module assume partitioned tables. You can still follow along, but you'll notice references.
4. Do you know what a "lock" is in PostgreSQL and why ACCESS EXCLUSIVE blocks reads? → If you hesitate, lesson 04 explains it briefly, but it helps to have the concept from guide #8 already.

If all 4 are clear to you, you're ready to start.

Signs of success

By the end of the module, you'll know you succeeded if:

• You can explain to a colleague when to choose MV vs live query vs Redis cache with quantitative criteria (latency, staleness, operational cost), not with intuition.
• You design the MV with a unique index from the very first CREATE — not as an afterthought.
• You know the exact command: REFRESH MATERIALIZED VIEW CONCURRENTLY <name> and why CONCURRENTLY is the default.
• You built the project dashboard: 4 functional MVs, scheduled cron, FastAPI endpoints that read from the MVs, a "last updated: X minutes ago" banner.
• You report the benchmark with numbers: "the top 10 posts query went from 4.2s to 12ms." Not "it improved," but "it improved 350×."
• You anticipate the need for pg_try_advisory_lock when someone asks "what happens if the cron fires twice?" — and you know the detailed answer arrives in module 6.

We start in the next lesson

We start with lesson 02: views vs materialized views — when each one. It's the critical framing before touching SQL. Without that clear decision, the technical lessons that follow are just syntax.

Before moving on, make sure you have:

• PostgreSQL 16+ running locally or in Docker.
• A test database with posts/views/comments tables (if you finished module 4, you already have it).
• SQLAlchemy 2.0+ and FastAPI 0.110+ installed in the project.

When you're ready, open lesson 02.

Resources for the module

1. PostgreSQL 16 — CREATE MATERIALIZED VIEW — official reference. Complete syntax.
2. PostgreSQL 16 — REFRESH MATERIALIZED VIEW — syntax and critical notes on CONCURRENTLY.
3. Crunchy Data — Materialized Views in PostgreSQL — operational best practices in production.
4. Lukas Fittl (pganalyze) — Materialized Views explained — cost analysis and real use cases.
5. Hashrocket — Refreshing Materialized Views Concurrently — the unique index gotcha explained with examples.
6. PostgreSQL Wiki — Materialized Views Roadmap — history and future features (incremental refresh).

---

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: Views vs materialized views — the critical framing before touching SQL.