Database Fundamentals

Materialized Views

0 of 8 complete

0%

Contents

Back|Database FundamentalsMaterialized Views
1/8
23 min left
  1. Home
  2. System Design: Foundation
  3. Database Fundamentals
  4. Materialized Views
Prerequisites
Database Triggersrequired
Related Topics
Write-Ahead LoggingConsistency ModelsDatabase Query CachingCaching Strategies
Previous lessonDatabase TriggersNext lessonDatabase Connection Pooling

System Design

1 of 8
  • Foundation
  • Intermediate
  • Advanced
  • Capstone

AI Engineering

  • Foundation
  • Data, RAG and Agents
  • Evaluation, LLM Ops and Security

systemdesign.academy

  • Home
  • Glossary
  • Interview prep
  • Reviews
  • About
  • Privacy
  • Terms

Where this shows up in interviews

3 system design questions lean on this idea. Each walks through the full answer.

  • →Design Pinterest
  • →Design Reddit
  • →Design Snowflake

The Read Is Not The Interesting Half

The views lesson measured what a materialized view is worth to read. The lesson measured what a derived value costs to maintain and how it drifts. Neither measured the refresh, which is where the whole operational question lives.

Two panels. What earlier lessons already measured: the read is enormously faster, 933 ms became 0.079, and it costs 256 KB to store, measured at 11,811 times with storage that is rounding error. What nobody measures until it happens: REFRESH takes a lock, and every reader waits for it, for as long as it runs, where a 7 ms read took 1,231, which is 176 times.
ComparisonFigure 1. Storing it is cheap, reading it is nearly free, and replacing it takes a lock

None of the three decisions that matter appears anywhere in CREATE MATERIALIZED VIEW.

Measured With A Second Session Reading Throughout

A terminal capture of the materialized views lab. A read that takes 7 ms when idle took 1,231 ms during a plain REFRESH lasting 1,234 ms, and 8 ms during a REFRESH CONCURRENTLY lasting 1,271 ms. The concurrent form first raised an error because no unique index existed. Six hundred thousand new orders left the view reporting 1,500,058,962 while the table reported 1,650,322,867, a gap of 9.1 percent. Two million deleted orders left the view at 1,500,058,962 against a true 1,150,169,243, with pg_matviews still reporting it as populated. The live aggregate took 933.060 ms and the view reads in 0.079 ms, at 256 KB against a 538 MB table.
terminal-figureFigure 2. The blocking is observed, not asserted

A parallel session reads the view continuously while each refresh runs, and the worst read it saw is recorded.

A bar chart of the slowest read a parallel session saw, in milliseconds. With nothing running it is 7 ms. During a plain REFRESH it is 1,231 ms. During REFRESH CONCURRENTLY it is 8 ms.
ChartFigure 3. A seven millisecond read took one thousand two hundred and thirty one

The reader was not made slow. It was made to wait, because a plain REFRESH takes an ACCESS EXCLUSIVE lock and holds it until the new contents are complete.

That number is the refresh duration, not a coincidence. A reader arriving at any point during a plain refresh waits until the end of it, so the worst case is exactly how long the refresh takes.

A sequence diagram between REFRESH, the view and a reader. REFRESH recomputes everything into new storage. The reader issues a SELECT count and is told to wait, because ACCESS EXCLUSIVE is held and the plain form swaps the whole relation at the end. REFRESH finishes, swaps and releases. The reader gets its answer 1,231 ms later for 7 ms of work.
SequenceFigure 4. It replaces the whole relation, so it cannot let anybody read the old one

The old contents stop being valid the moment the swap happens. Holding a lock across the whole operation is the only way that is safe.

The Form That Does Not Block

A bar chart of milliseconds to complete the refresh itself over 6,000,000 orders into 2,196 rows. A plain REFRESH takes 1,234 ms and REFRESH CONCURRENTLY takes 1,271 ms.
ChartFigure 5. Three percent more time, and the readers stopped waiting entirely

The concurrent form does strictly more work: it computes the new contents, compares them against the old ones, and applies the difference.

A sequence diagram between REFRESH CONCURRENTLY, a temporary result and the view. It computes the new contents into the temporary place, compares using the unique index, applies only the rows that changed as ordinary row changes in an ordinary transaction, and finishes in 1,271 ms with no reader having waited.
SequenceFigure 6. It never replaces the relation, so there is nothing readers cannot span

The refresh becomes a set of ordinary row updates, which readers handle the way they handle any other transaction.

A hand-drawn figure. The first box shows REFRESH MATERIALIZED VIEW CONCURRENTLY city_rev and the error, cannot refresh materialized view concurrently. The second shows CREATE UNIQUE INDEX city_rev_pk ON city_rev on city and day, and now it can, because it has a way to identify a row. A note reads that the index is not a tuning option and without it the concurrent path does not exist at all.

Staleness As A Number

A bar chart of total revenue in millions after 600,000 new orders arrived. The view reports 1,500.059 million and the table holds 1,650.323 million.
ChartFigure 8. Six hundred thousand orders arrived and the view reported the same number

Not approximately the same. Identical, to the rupee, because nothing about a materialized view changes when the underlying table changes.

The staleness window is the refresh interval plus the refresh duration. Refreshed every five minutes with a 1.3 second concurrent refresh, the answer is between zero and about five minutes old, and nothing in the result says which.

Two panels. What a cache has: a TTL so it expires, an invalidation on write, and a fill when it misses, so staleness is bounded by the TTL. What a materialized view has: none of the three, there is no miss to fill, only a REFRESH somebody scheduled, so staleness is unbounded until a human acts.
ComparisonFigure 9. Two million rows deleted, and it carried on reporting the old total

PostgreSQL says a view is populated when it has contents. That says nothing about whether those contents resemble the table they came from.

Calling it a cache imports three guarantees it does not have. Nothing expires, there is no miss to fill, and no write invalidates it.

What The Read Is Worth, And What It Is Not

A bar chart of milliseconds for revenue by city and day over 6,000,000 orders. The aggregate computed live takes 933.060 ms and the same answer read from the view takes 0.079 ms.
ChartFigure 11. Nine hundred and thirty three milliseconds became seventy nine microseconds

The stored answer is 2,196 rows in 256 kilobytes against a table of 538 megabytes. On the read side there is nothing to argue about.

Four rows. A view: the query substituted in, always current, no storage. A materialized view: a stored answer, current at REFRESH, 256 KB here. A summary table: updated per write, always current, and can drift. A cache: expires, invalidates, fills on miss, none of which this does.
FigureFigure 12. The first two differ by one word in the statement and by everything else

A view is the query written down and the answer is always current. A materialized view stores the result and stops being related to the table.

A hand-drawn figure of three columns. Compute it live: always correct, 933 ms every time, no staleness and no maintenance. A materialized view: correct when refreshed, 0.079 ms to read, and it needs a lock or an index and a schedule. An incremental summary: always current and it can drift, needing a trigger and a job that recomputes. A note reads that the middle one is a full recompute on a timer and the right one is an update per write, and they fail in completely different ways.

What To Do

A decision flow chart. How old may the answer be? If minutes are fine, use a schedule, where the window is the interval plus the refresh duration. If it must be current, this is not a materialized view, so use a live query or the incremental summary from lesson 95. Then: can readers be blocked at all? If no, CONCURRENTLY and the unique index it requires. If yes briefly, a plain REFRESH at 1,234 ms here, where every reader waits. Both end at adding a refreshed_at column so the age is visible.
FlowchartFigure 14. The first question is not about performance

A materialized view is right for a question whose answer may be minutes old. For anything that must be current it is the wrong shape entirely.

Five rows. Every minute: 1,440 full recomputes a day, 30 minutes of work. Every five minutes: 288 a day and a 5 minute staleness window. Hourly: 24 a day and an answer up to an hour old. After the load that changes it: as few as the data actually needs. And if the refresh outgrows the interval: they overlap, and one waits.
FigureFigure 15. At 1,271 milliseconds a refresh, a one minute schedule is half an hour a day

The refresh duration grows with the underlying data and the interval does not. The last row is what happens when they cross.

Refresh on the load that changes the data wherever that change is a known event. A nightly load followed by one refresh is both fresher and cheaper than a timer recomputing an unchanged table all day.

Correct And Stale, Or Current And Driftable

Six rows. The read: 933.060 ms became 0.079, which is 11,811 times. A plain REFRESH: 1,234 ms, and a 7 ms read took 1,231. CONCURRENTLY: 1,271 ms, and the same read took 8. Without a unique index: it refuses to run at all. 600,000 new orders: changed the view by exactly nothing. And 2,000,000 deleted: left it 30 percent wrong, and populated.
FigureFigure 18. One of these measurements is a zero

The read speed is believed instantly and the staleness is understood as an abstraction until somebody looks at two numbers that disagree.

A hand-drawn figure with two panels joined by a line. What it buys: 933 ms became 0.079, for 256 KB of storage, and it cannot drift. What it costs: an answer of unknown age, a lock unless CONCURRENTLY, and a full recompute per turn. A note reads that it cannot drift because it is recomputed from scratch, and that is also why the refresh costs what it costs.
SketchFigure 19. Both halves are the same mechanism

The lesson's incremental summary is the exact opposite: always current and capable of being quietly wrong.

Six summary rows. The read: 933.060 ms became 0.079, for 256 KB. A plain REFRESH: made a 7 ms read take 1,231. CONCURRENTLY: cost 3 percent more and blocked nobody. And it needs a unique index, or it refuses to run. 600,000 new orders: moved the view by exactly zero. So the design is the refresh, not the view. A closing line reads to check every materialized view for a unique index and a refreshed_at column, because both are one line and both are usually absent.

Quick Check

Knowledge Check

5 questions - Score 80% to pass

Q1

A read that normally takes 7 ms took 1,231 ms during a plain REFRESH that lasted 1,234 ms. Why are those two numbers almost the same?

Q2

REFRESH CONCURRENTLY took 1,271 ms against 1,234 for the plain form, and readers saw 8 ms instead of 1,231. Why does it not block?

Q3

REFRESH CONCURRENTLY raised an error before a unique index existed. Why does it require one?

Q4

Six hundred thousand orders were inserted and the view reported an identical total. Then two million were deleted and it still did, while pg_matviews reported it as populated. What does populated mean?

Q5

How does a materialized view differ from a cache?

SketchFigure 7. The concurrent refresh refused to run until a unique index existed

A difference needs a way to say which row is which. Without a unique index there is no such way, so the feature is simply unavailable.

Create that index in the same migration as the view. Discovering it is missing at three in the morning, when a refresh has locked the dashboard everybody is watching, is the usual way this gets learned.

An isometric figure. A drum labelled the stored answer, 2,196 rows and 256 KB, carrying the PostgreSQL elephant on its top face, standing inside a flat ring labelled the refresh cycle, 1,271 ms on a timer. A hand-drawn red arrow points at the ring beside a handwritten note reading that between two turns of it, the answer does not move.
IsometricFigure 10. A stored answer standing inside the cycle that periodically replaces it

The drum is cheap to store and nearly free to read. Everything difficult about a materialized view is a property of the ring.

SketchFigure 13. Two of these look alike and fail in opposite ways

The view cannot drift and is always slightly old. The summary is always current and can be quietly wrong forever.

Choose by which failure the application can tolerate. A dashboard can read a number that is four minutes old; a balance shown to the person who owns it cannot be wrong by an amount nobody can explain.

Six checks with product logos. Every matview and its refresh, for who runs it and how often. A UNIQUE index on each one, or CONCURRENTLY is unavailable. A refreshed_at column, so the reader can see the age. A plain REFRESH in a cron job, which blocks every reader silently. Refresh duration against interval, for the day they cross. And the view against a live recount, which is the same job as denormalization.
ArchitectureFigure 16. The fourth is the one running in production now

Nothing reports it. The refresh succeeds, the job logs success, and a request that normally takes seven milliseconds took over a second.

Five rows with product logos. REFRESH CONCURRENTLY took 1,231 ms of waiting down to 8, marked the lock. A UNIQUE index on the view, or CONCURRENTLY refuses, marked the gate. A refreshed_at column, the age made readable, marked the honesty. pg_matviews.ispopulated says nothing about accuracy, marked the trap. And refresh duration over time, which is the staleness floor, marked the budget.
ArchitectureFigure 17. The unique index and the timestamp column are one line each

One decides whether the refresh can avoid blocking readers at all. The other decides whether anybody looking at the number can tell how old it is.

A p99 that spikes on a round number of minutes is almost always a scheduled job holding a lock. Materialized view refreshes are the most common one, and the fix is one keyword plus one index.

FigureFigure 20. The whole lesson

The read is not in dispute. Everything else is about the refresh.

Correct-and-stale against current-and-driftable is the real choice, and the two lessons that measured them are five apart. Neither is a default, and picking on read speed alone picks the same one every time.

That closes this run through the database fundamentals: storage, indexes, plans, and the four ways of keeping an answer ready. Every one of them was measured against a real PostgreSQL instead of described.