# Feed Query Performance & Indexing

- **Pattern:** `ab-000765` (`community-social-engagement.activity-feeds.feed-query-optimization`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000765
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000765)

## Why it matters

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.

## Severity rationale

High because feed query performance degrades directly with post volume, making feed load times a leading indicator of database health as the platform scales.

## Remediation

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.

```sql
-- 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);
```

```ts
// 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 })
}
```

## Detection

- **ID:** `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:

  ```sql
  -- 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:

  ```ts
  // 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:

  ```ts
  // 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
  }
  ```

## External references

- iso-25010 performance-efficiency

Taxons: performance, cost-efficiency

HTML version: https://auditbuffet.com/patterns/ab-000765