The Pre-Computing Pattern: Compute on Write, Not on Read

A product manager clicks "View dashboard" and watches a spinner while the database aggregates millions of rows, joins user data and computes percentiles, and 200 colleagues are waiting on the same computation. The insight behind pre-computing: for predictable, repeated queries, compute the result when the data changes, not when someone asks.

The problem: repeated expensive queries

SELECT DATE(created_at), COUNT(DISTINCT user_id)
FROM events
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY 1;

Over 50 million events this scans gigabytes, and 200 product managers run it all day. If the data changes once a minute but is queried 100 times a minute, you do roughly 100× more computation than necessary.

Trade write cost for read speed

With compute-on-read, every query pays the full cost and response time grows with data volume. With compute-on-write, the cost is paid when data is written (often incrementally), and reads become simple lookups. The pattern goes by many names:

• [Materialized views](/learn/materialized-views) persist query results, often aggregations.
• [Denormalization](/learn/normalization-denormalization) pre-joins related tables.
• [Search indexes](/learn/search-and-inverted-index) pre-process text for fast retrieval.
• [Fan-out on write](/learn/fan-out) pre-distributes content to followers' feeds.
• [Write-through caches](/learn/cache-strategies) populate cache entries as data is written.

Worked example: a social feed

Compute-on-read joins your follow list with all posts on every request:

SELECT posts.* FROM posts
JOIN follows ON posts.author_id = follows.following_id
WHERE follows.follower_id = ?
ORDER BY posts.created_at DESC
LIMIT 20;

Follow 500 accounts with 1,000 posts each and the database may consider up to 500,000 candidate posts, depending on indexes and the plan. Pre-computing reverses the work:

1. Author publishes: the post is saved to the posts table.
2. Fan-out: a background worker reads the author's followers and inserts the post reference into each follower's feed (e.g. a Redis list or a feed table).
3. User opens the feed: read the pre-built list; some filtering and hydration remain, but the expensive follows-to-posts join is gone and reads typically take low milliseconds.

import redis
r = redis.Redis()
FEED_LEN = 800

def on_post_created(post_id: int, author_id: int, followers: list[int]):   # write path (async worker)
    pipe = r.pipeline()
    for f in followers:
        pipe.lpush(f"feed:{f}", post_id)
        pipe.ltrim(f"feed:{f}", 0, FEED_LEN - 1)     # keep the newest 800 only
    pipe.execute()

def read_feed(user_id: int, page: int = 0, size: int = 20) -> list[int]:  # read path
    start = page * size
    return [int(x) for x in r.lrange(f"feed:{user_id}", start, start + size - 1)]

The trade-offs

• More storage: denormalized copies and materialized results take space; storing post references for millions of followers multiplies it.
• More write-side work: every write triggers computation, synchronously or in background workers. When an account with 10 million followers posts, fan-out takes a while, so async processing trades latency for eventual consistency.
• Staleness: results reflect the moment they were computed; decide how stale is acceptable.
• Less flexibility: it speeds up known queries only; a new aggregation or filter may not benefit.
• More complexity: source data and derived views must both be maintained; schema changes touch both, and debugging spans several data paths.

When to pre-compute, and when not to

Implementation strategies

Hybrid approaches

Production systems mix strategies. Twitter historically fanned out on write for most users but computed on read for accounts with millions of followers, because fan-out for them would be prohibitive. The same idea generalizes: pre-compute common cases and compute edge cases on demand; pre-compute hot data and let cold data compute on read; pre-compute where staleness is fine and compute fresh on critical paths. Match the strategy to the access pattern rather than applying one everywhere.