Precompute vs compute on read

The decision

Some answers are expensive: a total over millions of rows, a leaderboard, a dashboard with joins. Precompute means you calculate the answer ahead of time, when data changes or on a schedule, and store it. A read is then a simple lookup. Compute on read means you keep only the raw data and calculate the answer when someone asks. Precompute makes reads fast and cheap, but the answer can be old, and you must keep it correct. Compute on read is fresh and simple, but each read pays the full cost.

Choose precompute when

• Reads are much more common than writes, and many users ask for the same answer, such as a top 100 list.
• The query is expensive: a join, or an aggregate over many rows.
• The latency target is strict, and the biggest users have the most data.
• A delay of seconds or minutes is acceptable.

Choose compute on read when

• Data is small enough that the query is fast for everyone, including the largest user.
• Questions are ad hoc, with many filters, so you cannot guess them in advance.
• Writes are much more common than reads. Most precomputed answers would never be read.
• The answer must be exact and current, such as a balance before a payment.

The numbers that change the answer

Worked example: a seller dashboard on a shopping app, "sales in the last 30 days". Assumptions: 50,000 sellers, 2,000,000 order lines a day, 1,000,000 dashboard opens a day, and one row read costs 0.01 ms (10 microseconds) on an indexed table. The latency target is 300 ms.

Data size (calculation). In 30 days there are 2,000,000 x 30 = 60,000,000 order lines. The average seller has 60,000,000 / 50,000 = 1,200 lines.

Cost per open (calculation).

• Average seller: 1,200 rows x 0.01 ms = 12 ms. Fast.
• A seller 100 times bigger: 120,000 rows x 0.01 ms = 1,200 ms = 1.2 seconds. Too slow.
• Rows that fit in the target: 300 ms / 0.01 ms = 30,000 rows. A seller with more than 30,000 lines in 30 days (1,000 a day) breaks the target.

Cost per day (calculation).

• Compute on read: 1,000,000 opens x 1,200 rows = 1,200,000,000 row reads x 0.01 ms = 12,000,000 ms = 12,000 seconds of CPU a day. That is 12,000 / 86,400 = 0.14 of one core.
• Precompute: 2,000,000 counter updates x 0.05 ms (assumption) = 100,000 ms = 100 seconds a day.
• Precompute is 12,000 / 100 = 120 times cheaper in CPU, not counting its cheap reads. But 0.14 of a core is also tiny.

So in this example CPU does not decide. The 1.2 second tail for big sellers decides. That is why the answer is often "compute on read for most, precompute for the heavy ones".

Too many combinations (calculation). Suppose the dashboard has 6 filters with 5 values each. That is 5 to the power of 6 = 15,625 combinations per seller. For 50,000 sellers: 15,625 x 50,000 = 781,250,000 cells. You cannot precompute all of them. Precompute the common cut (by day and by product) and compute the filters on top of it.

Storage (calculation). Daily totals per seller and product. Assumption: a seller sells 5 products on an average day. Rows = 50,000 sellers x 30 days x 5 products = 7,500,000. At 32 bytes each (assumption), that is 240 MB. The raw lines are 60,000,000 x 200 bytes (assumption) = 12 GB. The summary is 12,000 / 240 = 50 times smaller.

Rebuild time (calculation). Assumption: a rebuild job reads 50,000 rows per second. To rebuild from 60,000,000 lines: 60,000,000 / 50,000 = 1,200 seconds = 20 minutes. If you cannot rebuild, you cannot trust a precomputed value.

Cited fact. In PostgreSQL, a materialized view stores the result of a query, and it is not always current. You refresh it with a command (see Sources).

What breaks with each choice

Precompute.

• The stored number drifts from the raw data. A missed event, a bug or a retry that counts twice makes the dashboard wrong, and nobody notices. Fallback: update with an event ID so a retry does nothing. Run a nightly job that recounts the raw rows and compares.
• One counter row updated on every order is a hot row. Assumption: each update holds the row for 1 ms, so the limit is 1,000 updates per second. Fallback: add up in batches or split the counter.
• Answers are old. Fallback: show "updated 2 minutes ago" and let users refresh.
• A change in the definition needs a backfill. Fallback: keep the raw data and a rebuild job.

Compute on read.

• The biggest tenants see the slowest pages (1.2 seconds in the example). Fallback: precompute only for tenants above the 30,000-row threshold.
• A popular expensive query runs again and again. Fallback: a short cache.
• One heavy query slows others on the same database. Fallback: run reads on a follower, set a time limit per query and a rate limit per user.

What it costs

• People. Precompute needs an update path, a reconcile job and a rebuild tool. Assumption: 2 to 4 engineer-weeks for the first one, much less for the next.
• Changing your mind. This is cheap, because the raw data stays as the source of truth. Build the summary table next to the old query, fill it (20 minutes in the example), compare both for a week, then switch reads. Going back means switching reads again. The only expensive case is deleted raw rows, so keep them.

How each level answers

SDE-1: Precompute stores the answer in advance, so reads are fast. Compute on read is always fresh but costs more per read.

SDE-2: I compute on read while the data is small. When the query gets slow for big users, I precompute the daily totals and keep raw rows as the truth. A leaderboard asked for by millions is precomputed from the start.

Senior: The average seller needs 12 ms, but a seller 100 times bigger needs 1.2 seconds against a 300 ms target. The threshold is 30,000 rows. Above it I read from a daily summary table, updated by event ID so retries are safe. A nightly job recounts and compares. The fallback is the rebuild in 20 minutes.

Staff: I ask what each stale minute costs the business and who is hurt by a wrong number. For money screens I show exact numbers, and for marketing dashboards a delay is fine. I start with the simplest option, set an alert at the latency target, and precompute when the alert fires. The migration is safe, because the summary is built beside the old query and checked for a week. The team that owns the source data owns the summary table. I measure page time at the 99th percentile, the gap between the stored and the recounted total, and the rebuild time.

Say it in the interview

For the seller dashboard I would compute from raw rows at first, since the average seller reads 1,200 rows in 12 ms. The biggest sellers would take over a second. So above about 30,000 rows I would read a daily summary, updated by events that carry an event ID. A nightly recount would catch drift, and I could rebuild the summary in about 20 minutes.

Common mistakes

• Precomputing everything on day one. You pay for a pipeline and for drift before any user is slow. Measure the biggest user first.
• Using the average to decide. The average seller is fast. The tail seller breaks the target.
• Having no way to rebuild. If you cannot recount from raw data, you cannot find or fix drift.
• Precomputing every filter. The combinations multiply to 781 million cells. Precompute the common cut only.

Sources

• PostgreSQL manual, materialized views: they store query results, the data is not always current, and you refresh it with REFRESH MATERIALIZED VIEW. https://www.postgresql.org/docs/current/rules-materializedviews.html (opens in a new tab) (checked 26 Oct 2026)
• Row counts, row costs, sizes and rates above are assumptions for teaching.