Joins at read time vs precomputed views

The decision

A join combines rows of two or more tables when the query runs, so the answer is always current. A precomputed view stores an answer that you worked out earlier, such as a total, an average, or a copied name. It is fast to read, but it can be old, and you must keep it up to date. A materialized view is a precomputed view that the database stores and refreshes on demand.

Choose joins at read time when

• The data changes often and the answer must be exactly current.
• Queries vary, and a join can answer a question you did not plan for.
• The join is cheap. A join that looks up 30 reviews and their authors by key reads about 60 rows, which is small.
• The tables are small, or the query runs rarely.

Choose a precomputed view when

• Reads are far more than writes, and the same question repeats, such as a restaurant page that shows an average rating.
• The query reads many rows to give a small answer, such as the average of 2,000 reviews.
• A little staleness is fine, for example a rating that is a few minutes old.
• The store has no joins, or the data sits on different shards.

The numbers that change the answer

Worked example at Indian scale. A food app's restaurant page shows the average rating. Assumptions: 50,000 restaurants with 2,000 reviews each, so 50,000 x 2,000 = 100,000,000 review rows. The page is opened 200 times per second. New reviews arrive at 12 per second.

The stored version needs 12 extra row updates per second, one for each new review. Reads fall from 400,000 to 200, which is 2,000 times fewer, for 12 extra writes. Store the sum and the count, not a rounded average. Then each new review is an exact change: 8,400 stars over 2,000 reviews is 4.2, and a new 5-star review makes it 8,405 over 2,001.

A copied field. A product page shows 30 reviews with the author name, at 1,000 page views per second. With a join, each view reads 30 review rows and 30 user rows: 60 x 1,000 = 60,000 row reads per second. With the author name copied into each review: 30 x 1,000 = 30,000, half as many. Assumption: 500 users rename themselves each day and each wrote 40 reviews. The copies need 500 x 40 = 20,000 updates a day, which is 20,000 / 86,400 = 0.23 per second. The copy is cheap here.

Materialized view or live join. A refresh reads all rows again. Postgres says it completely replaces the contents of the view. Refresh cost per second = total rows / interval. Break-even with the live join: 100,000,000 / interval = 400,000, so the interval is 250 seconds.

• Every 300 seconds: 100,000,000 / 300 = 333,333 row reads per second. That is a little cheaper than the live join, but ratings are up to 5 minutes old.
• Every 60 seconds: 100,000,000 / 60 = 1,666,667 per second. That costs more than the live join.

So a materialized view pays only with an interval above 250 seconds. For fresher answers, update stored counters when each review arrives.

What breaks with each choice

• Join at read time: it gets slower as data grows (400,000 row reads per second here). Heavy joins can overload the database at the busiest time. Fix: indexes on the join columns, or a precomputed view for the slow ones.
• Precomputed view: the answer can be old or wrong. A copy that is not updated gives a name that no longer matches. Two writes must succeed together, or the stored total drifts. Fix: update the stored value in the same transaction as the review. Use one atomic change such as sum = sum + 5, not read then write. Run a nightly check that recomputes and compares.
• Materialized view: a refresh takes time and resources. In Postgres, the CONCURRENTLY option lets users keep reading during a refresh. It needs a unique index, and only one refresh runs at a time.

What it costs

• Joins cost no extra storage but cost CPU on every read.
• Precomputed values cost extra writes (12 per second here), extra storage, and code to keep them right. Plan for days of work and a regular check (rough guess).
• Changing later is safe. Add a stored value when a join becomes slow, and keep the join as the source of truth. You can rebuild the stored value at any time.

How each level answers

SDE-1: A join combines tables when the query runs. A precomputed view stores a result for fast reads.

SDE-2: For a restaurant page I store the rating sum and count. The page is read 200 times a second, and an average would read 2,000 rows each time. For a rare admin report I use a join.

Senior: Computing the average reads 400,000 rows per second. Stored counters read 200, and cost 12 extra writes per second. A materialized view pays only if I refresh it less often than every 250 seconds. The risk is a total that drifts, so a nightly job recomputes it. The fallback is the live join.

Staff: I ask how old an answer can be before it costs us money, and who is hurt by a wrong number. I keep the join as the source of truth and treat every stored value as a cache that we can rebuild. One team owns each stored value and its check job. I measure read cost, drift found by the nightly check, and the time to rebuild.

Say it in the interview

I would keep the join as the source of truth and store the rating sum and count on the restaurant row. Computing the average would read 400,000 rows per second. The cost is 12 extra writes per second and some drift, so a nightly job recomputes the totals.

Common mistakes

• Precomputing before measuring. A join on indexed keys can be fast enough.
• Storing only a rounded average. Every update adds a small error.
• Refreshing a materialized view too often. Under 250 seconds here, it costs more than the live query.
• Having no check. Stored values drift unseen.

Sources

• Postgres, REFRESH MATERIALIZED VIEW (replaces the contents; CONCURRENTLY needs a unique index): https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html (opens in a new tab) (checked 26 Oct 2026).
• Restaurant counts, review counts, page rates and rename rates are assumptions made for teaching. All other numbers are calculations from them.