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 })
}

• ID: community-social-engagement.activity-feeds.feed-query-optimization

• Severity: high

• What to look for: Enumerate all relevant files and Examine the feed query logic (often in /api/feed, a feed component, or a server action). Look for queries that fetch posts from followed users (e.g., WHERE userId IN (SELECT followedId FROM follows WHERE followerId = ?)). Check the database schema for indexes on the Post table: user_id, created_at, and ideally a composite (user_id, created_at) index. Check for evidence of either "fan-out on write" (fanout table) or "fan-out on read" (query-time assembly) architecture.

• Pass criteria: At least 1 implementation must be present. Feed query includes relevant indexes (at minimum: user_id, created_at on posts). The query logic is documented or evident (e.g., comment in code describing the fan-out strategy). For large-scale feeds, a fanout table (FeedItem or similar) is used.

• Fail criteria: No indexes on post query fields, or the feed query is a simple SELECT with no optimization, or indexes are missing for created_at sorting.

• Skip (N/A) when: The project has no feed feature, or feeds are not a core feature.

• Detail on fail: "Feed query uses SELECT * WHERE user_id IN (...) ORDER BY created_at without indexes — query plan shows full table scan" or "No index on post.created_at — feed sorting is slow"

• Remediation: Index and optimize feed queries:

  -- Add indexes
  CREATE INDEX posts_user_id_created_at ON posts(user_id, created_at DESC);
  CREATE INDEX follows_follower_id ON follows(follower_id);
  

  Then implement fan-out on read:

  // app/api/feed/route.ts
  export async function GET(req: Request) {
    const userId = getCurrentUserId()
    const { limit = 20, cursor } = Object.fromEntries(
      new URL(req.url).searchParams
    )
  
    // Get followed user IDs
    const following = await db.follow.findMany({
      where: { followerId: userId },
      select: { followedId: true }
    })
    const followedIds = following.map(f => f.followedId)
  
    // Fetch posts from followed users
    const posts = await db.post.findMany({
      where: {
        userId: { in: followedIds },
        createdAt: cursor ? { lt: new Date(cursor) } : undefined
      },
      orderBy: { createdAt: 'desc' },
      take: Math.min(parseInt(limit), 100),
      skip: cursor ? 1 : 0,
      cursor: cursor ? { id: cursor } : undefined,
      include: { author: true, likes: true }
    })
  
    return Response.json({
      posts,
      nextCursor: posts[posts.length - 1]?.id || null
    })
  }
  

  For high-scale systems, implement fan-out on write:

  // When a user posts, fanout to all followers' feeds
  export async function createPost(userId: string, content: string) {
    const post = await db.post.create({ data: { userId, content } })
  
    const followers = await db.follow.findMany({ where: { followedId: userId } })
    await db.feedItem.createMany({
      data: followers.map(f => ({ userId: f.followerId, postId: post.id }))
    })
  
    return post
  }