Catalog My learning Blog
Full-Stack Developer Course: React, .NET and SQL Server · Phase 10 · Lesson 95 · 10 min read

Design a News Feed: Fan-Out and Caching

On this page
  1. Learning Objective
  2. Concept Explanation
  3. Worked Example: Schema, Asynchronous Fan-Out, a Hybrid Read and Read-Time Filtering
  4. Real-World Examples
  5. Review Questions & Answers

Learning Objective

Design a follow-based activity feed comparing fan-out on write, fan-out on read and a hybrid for high-follower accounts, choose storage and pagination that scale, keep the interface responsive, and enforce blocking, privacy and deletion correctly even though timelines are precomputed.

Concept Explanation

A news feed shows a user the recent posts of the accounts they follow. The requirements sound simple and produce one of the most instructive scaling problems. Users post short updates, follow other users, and open a timeline that lists posts from those they follow in reverse chronological order, or ranked, with infinite scroll. Assume 10 million users, 2 million daily active, each opening the feed ten times a day and posting once, with an average of 200 followers and a long tail of accounts with millions. Feed reads then run about 230 per second on average and 2,300 at peak, dwarfing the 23 posts per second. The design goal is to make reading a feed very cheap, since that is the common case, without making posting ruinously expensive.

There are two basic strategies. Fan-out on read computes the feed when requested: fetch the list of accounts the user follows, get their recent posts, merge and sort. Writes are trivial, one row per post, but reads are expensive, touching hundreds of accounts, and the cost grows with the number of follows. Fan-out on write does the work at posting time: when a user posts, the system inserts a reference to that post into a precomputed timeline for each follower, so reading a feed is one indexed range query on a single timeline. Reads become cheap and predictable, at the cost of write amplification: a post by a user with 200 followers creates 200 timeline rows, done asynchronously by workers.

The problem case is the account with millions of followers. Fan-out on write for a celebrity would create millions of rows for one post, which is slow, expensive, and delays delivery. The standard solution is a hybrid, described by Twitter engineers in talks on timeline architecture: use fan-out on write for ordinary accounts, but do not precompute for accounts above a follower threshold, and at read time merge the user's precomputed timeline with the recent posts of the few high-follower accounts they follow. That bounds both write amplification and read work. Storage can be SQL Server for the source of truth, with the timeline table clustered on the user and time for range scans, and Redis sorted sets as a cache of the hottest timelines, since most users only ever read the first page.

Precomputation creates a subtle security and correctness problem. A timeline is a snapshot of decisions made at posting time, but the rules that govern visibility change afterward. A user blocks another, an account becomes private, a post is deleted or reported, or someone is removed from a close-friends list, and the precomputed rows still point at content the viewer must no longer see. So the feed read path must apply authorization at read time: after fetching candidate post identifiers, filter out posts that are deleted, authored by blocked or muted accounts, or from private accounts the viewer does not follow, before returning content. Never treat the timeline as a permission list; it is only a list of candidates. This mirrors the lesson from Day 67 about caches: precomputed data speeds retrieval but must never be the source of authorization.

StrategyWrite costRead costFits
Fan-out on readTrivial, one row per postHigh, merges many accounts per requestSmall graphs, few follows
Fan-out on writeHigh, one row per follower, done asynchronouslyVery low, one range queryOrdinary accounts, read-heavy feeds
HybridFan-out only for accounts under a thresholdTimeline plus merge of a few large accountsReal social graphs with celebrities
Timeline cacheRedis sorted sets for hot usersFirst page from memoryMost users read only the top
Read-time filteringCheck blocks, privacy, deletion on the candidatesSmall extra costRequired for correctness and privacy

Worked Example: Schema, Asynchronous Fan-Out, a Hybrid Read and Read-Time Filtering

The listings show the tables, the fan-out worker, the read path with merging and filtering, and the React infinite list.

SQL
CREATE TABLE dbo.Posts (
    -- An increasing number sorts by creation. Do not order by a GUID: SQL Server compares uniqueidentifier values
    -- by their last bytes first, so even time-based version 7 GUIDs do not sort by time.
    Id BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Posts PRIMARY KEY,
    AuthorId UNIQUEIDENTIFIER NOT NULL, Body NVARCHAR(1000) NOT NULL,
    CreatedAt DATETIMEOFFSET NOT NULL, IsDeleted BIT NOT NULL CONSTRAINT DF_Posts_Del DEFAULT 0
);
CREATE INDEX IX_Posts_Author ON dbo.Posts (AuthorId, Id DESC) WHERE IsDeleted = 0;

CREATE TABLE dbo.Follows (
    FollowerId UNIQUEIDENTIFIER NOT NULL, FolloweeId UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT PK_Follows PRIMARY KEY (FollowerId, FolloweeId)
);
CREATE INDEX IX_Follows_Followee ON dbo.Follows (FolloweeId, FollowerId);      -- who follows me: used by fan-out

CREATE TABLE dbo.Timeline (                                                    -- precomputed candidates, one row per (viewer, post)
    UserId UNIQUEIDENTIFIER NOT NULL, PostId BIGINT NOT NULL,
    CONSTRAINT PK_Timeline PRIMARY KEY (UserId, PostId DESC)                   -- clustered: a feed page is one range scan
);
CREATE TABLE dbo.Blocks (BlockerId UNIQUEIDENTIFIER NOT NULL, BlockedId UNIQUEIDENTIFIER NOT NULL, CONSTRAINT PK_Blocks PRIMARY KEY (BlockerId, BlockedId));
ALTER TABLE dbo.Users ADD FollowerCount INT NOT NULL CONSTRAINT DF_Users_FC DEFAULT 0, IsPrivate BIT NOT NULL CONSTRAINT DF_Users_Priv DEFAULT 0;
C#
// Posting stays cheap: insert the post and an outbox event in one transaction. Fan-out happens elsewhere.
public async Task<long> PostAsync(Guid authorId, string body, CancellationToken ct)
{
    var post = new Post { AuthorId = authorId, Body = body.Trim(), CreatedAt = clock.GetUtcNow() };
    db.Posts.Add(post);
    await db.SaveChangesAsync(ct);                                          // the identity value is assigned here
    db.Outbox.Add(OutboxMessage.For("post.created.v1", new { post.Id, post.AuthorId }));
    await db.SaveChangesAsync(ct);
    return post.Id;
}

// Worker: fan out to followers in batches, but skip very large accounts (hybrid).
public sealed class FanOutWorker(IServiceScopeFactory scopes) : IIntegrationEventHandler<PostCreated>
{
    private const int CelebrityThreshold = 100_000;

    public async Task HandleAsync(PostCreated e, CancellationToken ct)
    {
        using var scope = scopes.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDb>();

        var author = await db.Users.AsNoTracking().Where(u => u.Id == e.AuthorId).Select(u => new { u.FollowerCount }).FirstAsync(ct);
        if (author.FollowerCount >= CelebrityThreshold) return;             // merged at read time instead: no million-row write

        Guid? after = null;
        while (true)
        {
            var followers = await db.Follows.AsNoTracking().Where(f => f.FolloweeId == e.AuthorId && (after == null || f.FollowerId.CompareTo(after.Value) > 0))
                .OrderBy(f => f.FollowerId).Take(1000).Select(f => f.FollowerId).ToListAsync(ct);
            if (followers.Count == 0) break;

            // Idempotent bulk insert: re-running the event after a crash adds nothing twice (the primary key protects it).
            await db.Database.ExecuteSqlRawAsync(
                "INSERT INTO dbo.Timeline (UserId, PostId) SELECT v.value, @p FROM OPENJSON(@ids) v " +
                "WHERE NOT EXISTS (SELECT 1 FROM dbo.Timeline t WHERE t.UserId = v.value AND t.PostId = @p)",
                new[] { new SqlParameter("@p", e.Id), new SqlParameter("@ids", JsonSerializer.Serialize(followers)) }, ct);
            after = followers[^1];
        }
    }
}
C#
// Read: candidates from the precomputed timeline, plus recent posts of followed celebrities, then FILTER at read time.
app.MapGet("/api/feed", async (long? before, int? limit, AppDb db, ClaimsPrincipal user, CancellationToken ct) =>
{
    var me = user.GetUserId(); var take = Math.Clamp(limit ?? 20, 1, 50);
    var cursor = before ?? long.MaxValue;                                                    // no cursor: start at the newest

    var precomputed = db.Timeline.Where(t => t.UserId == me && t.PostId < cursor).Select(t => t.PostId);

    var celebrityIds = db.Follows.Where(f => f.FollowerId == me).Join(db.Users, f => f.FolloweeId, u => u.Id, (f, u) => u)
        .Where(u => u.FollowerCount >= 100_000).Select(u => u.Id);
    var celebrityPosts = db.Posts.Where(p => celebrityIds.Contains(p.AuthorId) && p.Id < cursor).Select(p => p.Id);

    var candidates = precomputed.Union(celebrityPosts).OrderByDescending(id => id).Take(take * 3);     // over-fetch: some will be filtered out

    // Authorization at READ time. The timeline is a list of candidates, never a permission list.
    var page = await db.Posts.AsNoTracking()
        .Where(p => candidates.Contains(p.Id)
                 && !p.IsDeleted                                                                          // deletion takes effect immediately
                 && !db.Blocks.Any(b => (b.BlockerId == me && b.BlockedId == p.AuthorId) || (b.BlockerId == p.AuthorId && b.BlockedId == me))
                 && (p.AuthorId == me || !db.Users.Any(u => u.Id == p.AuthorId && u.IsPrivate) || db.Follows.Any(f => f.FollowerId == me && f.FolloweeId == p.AuthorId)))
        .OrderByDescending(p => p.Id).Take(take)
        .Select(p => new PostDto(p.Id, p.AuthorId, p.Body, p.CreatedAt)).ToListAsync(ct);

    return Results.Ok(new FeedPage(page, NextCursor: page.Count == take ? page[^1].Id : null));
}).RequireAuthorization();
TypeScript
// React: infinite scroll with cursor pages, virtualization for long lists, and a "new posts" banner via SignalR (Day 59).
const feed = useInfiniteQuery({
  queryKey: ['feed', userId],
  queryFn: ({ pageParam }) => request(`/api/feed?limit=20${pageParam ? `&before=${pageParam}` : ''}`, FeedPageSchema),
  initialPageParam: null as string | null,
  getNextPageParam: (last) => last.nextCursor,
  staleTime: 15_000,
});
const items = feed.data?.pages.flatMap((p) => p.items) ?? [];

// Only the visible rows are rendered, so a thousand posts do not create a thousand DOM nodes.
const rowVirtualizer = useVirtualizer({ count: items.length, getScrollElement: () => scrollRef.current, estimateSize: () => 96, overscan: 6 });
// Post bodies render as text. Blocking or deleting refreshes the feed by invalidating ['feed', userId].

State the trade-offs plainly. Feeds are eventually consistent: a new post may take a second or two to appear for followers, which is acceptable. The over-fetch factor means some pages return fewer items when many candidates are filtered, so the client keeps requesting until the list fills. Rankings, the step from reverse chronological to relevance ordering, would add a scoring service and are deliberately out of scope here. The important guarantees are the ones we can test: a blocked user's post never appears, a deleted post disappears immediately, and a private account's posts appear only for followers.

A new post becomes an outbox event, and a worker inserts it into each follower timeline for accounts under a follower threshold. Reads take timeline candidates plus posts of followed celebrities, filter for blocks, privacy and deletion at read time, and return a cursor page.
A new post becomes an outbox event, and a worker inserts it into each follower timeline for accounts under a follower threshold. Reads take timeline candidates plus posts of followed celebrities, filter for blocks, privacy and deletion at read time, and return a cursor page.

Real-World Examples

  • Twitter engineer Raffi Krikorian's 2012 talk Timelines at Scale described the fan-out on write design behind the home timeline: tweets are inserted into followers' cached timelines at post time so reads are fast, and accounts with very large follower counts are handled differently and merged at read time. The talk is the standard source for the hybrid approach and for the numbers that motivated it, such as read volumes far exceeding write volumes.
  • Facebook's 2013 paper on TAO, its distributed data store for the social graph, described serving billions of reads per second for objects and associations such as friends, likes, and posts, with a read-through cache layered over MySQL. It illustrates the general pattern of caching precomputed graph data close to the readers, with privacy checks enforced when data is read, not baked into what is cached.
Key TakeawaysNews feeds are read far more often than they are written, so precompute timelines with fan-out on write for ordinary accounts. Merge posts from high-follower accounts at read time in a hybrid design. Store timelines clustered by user and post for cheap range scans, page with cursors, cache hot first pages, and treat precomputed timelines as candidates only, enforcing blocks, privacy, and deletion at read time so changes take effect immediately.

Review Questions & Answers

Feeds are read much more often than posts are created, so paying a cost at write time to make reads a single indexed range query is efficient. For an account with millions of followers, one post would require millions of timeline inserts, which is slow and expensive and delays delivery, so those accounts are excluded and merged at read time in a hybrid design.

It is a snapshot created when the post was made, while visibility rules change later: users block each other, accounts become private, and posts are deleted. Relying on the snapshot would keep showing content the viewer must no longer see, so the read path must check current blocks, privacy, and deletion on the candidate posts before returning them.

Some candidates will be removed by read-time filtering for blocks, privacy, or deletion, so fetching exactly one page of candidates could yield a short page. Fetching more than needed, and continuing to page when the result is short, keeps pages full while still applying authorization correctly.

Offset pagination would skip or repeat items as new posts arrive at the top while the user scrolls, and gets slower with depth. A cursor based on a time-ordered identifier continues exactly after the last item seen, remains stable as new posts are inserted, and uses the clustered index for constant-time page fetches.