Feed Query Performance & Indexing

A feed query with no indexes on (user_id, created_at) forces a full table scan every time any user loads their home feed. At 100,000 posts, this is a sub-second scan; at 10 million posts, it becomes a multi-second query that degrades under concurrent load exactly when traffic peaks. ISO 25010:2011 performance-efficiency requires that response time remains acceptable as data volume grows — an unindexed feed query fails this by design. The fan-out architecture choice (read vs. write) is a secondary concern, but the missing indexes are an immediate performance defect regardless of architecture.

Create composite indexes on your posts table before the feed query volume grows, then ensure your feed handler applies a take limit and cursor-based pagination rather than fetching unbounded results.

-- Run in a migration
CREATE INDEX posts_user_id_created_at ON posts(user_id, created_at DESC);
CREATE INDEX follows_follower_id ON follows(follower_id);

// app/api/feed/route.ts
export async function GET(req: Request) {
  const userId = getCurrentUserId()
  const cursor = new URL(req.url).searchParams.get('cursor')

  const following = await db.follow.findMany({
    where: { followerId: userId },
    select: { followedId: true }
  })

  const posts = await db.post.findMany({
    where: { userId: { in: following.map(f => f.followedId) } },
    orderBy: { createdAt: 'desc' },
    take: 20,
    cursor: cursor ? { id: cursor } : undefined,
    skip: cursor ? 1 : 0
  })

  return Response.json({ posts, nextCursor: posts[posts.length - 1]?.id ?? null })
}