Phase 3: DB Tuning — N+1 and Query Shape Cleanup

Goal

Reduce Prisma operation count, SQL statement count, and query duration before finalizing Prisma Postgres as the primary DB.

Prisma's query optimization docs identify common issues including over-fetching, missing indexes, lack of caching, and full table scans. They also describe the N+1 problem and recommend nested reads, in filters, and relationLoadStrategy: "join" to reduce repeated queries. Source: Prisma query optimization.

Primary tuning targets

Problem	Symptom	Fix
N+1 query loops	Many repeated similar queries	include, select, in, batch loaders, joins
Over-fetching	Large response/query payloads	Explicit select fields
Repeated workspace context loads	Same membership/role/brand loaded repeatedly	Shared workspace context loader + cache
Unbounded list queries	Full-table scans or slow page loads	Cursor/keyset pagination
Chatty background jobs	High operation count while idle	Batching, bounded polling, queue limits
Excessive small writes	Many one-row inserts/updates	createMany, updateMany, bulk jobs

N+1 cleanup

Find patterns

Search for loops around Prisma calls:

grep -RniE "for .*await prisma|forEach\(async|map\(async" src
grep -RniE "prisma\.[a-zA-Z0-9_]+\.findMany" src

Bad pattern:

const posts = await prisma.post.findMany({
  where: { workspaceId },
});

for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
}

Better pattern with include/select:

const posts = await prisma.post.findMany({
  where: { workspaceId },
  select: {
    id: true,
    title: true,
    status: true,
    author: {
      select: {
        id: true,
        name: true,
        email: true,
      },
    },
  },
});

Better pattern with in:

const posts = await prisma.post.findMany({
  where: { workspaceId },
  select: { id: true, authorId: true },
});

const authors = await prisma.user.findMany({
  where: { id: { in: posts.map(p => p.authorId) } },
  select: { id: true, name: true, email: true },
});

Consider relationLoadStrategy: "join" where it reduces SQL statements and works for the query shape.

Over-fetching cleanup

Default list views should not load long body fields.

Bad

await prisma.contentItem.findMany({
  where: { workspaceId },
});

Better

await prisma.contentItem.findMany({
  where: { workspaceId },
  select: {
    id: true,
    title: true,
    status: true,
    platform: true,
    scheduledAt: true,
    updatedAt: true,
    campaignId: true,
    brandId: true,
  },
  take: 50,
  orderBy: { updatedAt: "desc" },
});

Load body/content fields only in editor/detail routes.

Workspace context loader

Create a single loader:

export async function getWorkspaceContext(userId: string, workspaceId: string) {
  return prisma.workspaceMember.findFirst({
    where: {
      userId,
      workspaceId,
    },
    select: {
      id: true,
      role: true,
      workspace: {
        select: {
          id: true,
          name: true,
          activeBrandId: true,
        },
      },
      user: {
        select: {
          id: true,
          email: true,
          name: true,
        },
      },
    },
  });
}

Use this everywhere rather than reloading user, workspace, membership, active brand, and permissions separately.

Pagination

Avoid unbounded findMany.

Use cursor/keyset pagination:

await prisma.contentItem.findMany({
  where: { workspaceId },
  take: 50,
  cursor: cursor ? { id: cursor } : undefined,
  skip: cursor ? 1 : 0,
  orderBy: [
    { updatedAt: "desc" },
    { id: "desc" },
  ],
});

For calendar ranges, constrain by date:

await prisma.contentItem.findMany({
  where: {
    workspaceId,
    scheduledAt: {
      gte: startDate,
      lt: endDate,
    },
  },
  orderBy: { scheduledAt: "asc" },
});

Bulk writes

Prisma recommends bulk operations for large read/write workloads. Source: Prisma query optimization.

Use:

await prisma.publishAttempt.createMany({
  data: attempts,
  skipDuplicates: true,
});

Instead of:

for (const attempt of attempts) {
  await prisma.publishAttempt.create({ data: attempt });
}

Background job rules

Background jobs must be bounded:

Job type	Rule
Publishing queue scan	Query only next due window; use take.
Social sync	Paginate; store cursor/checkpoint.
AI generation cleanup	Batch delete/archive.
Webhook processing	Ack quickly; process async; archive raw payload outside Prisma where possible.
Scheduled retries	Index status/runAt; never scan all historical rows.

Query Insights attribution

Prisma Query Insights can trace SQL back to Prisma ORM model/action/query shape if @prisma/sqlcommenter-query-insights is configured. Source: Prisma query optimization.

Add:

npm install @prisma/sqlcommenter-query-insights

Then configure the client per current Prisma adapter setup.

Acceptance criteria

Item	Target
Top 20 expensive actions reviewed	100%
Obvious N+1 loops removed	100%
List routes use explicit select	100%
Detail routes load long fields only when needed	Yes
Large list routes paginated	Yes
Background jobs bounded	Yes
Workspace context loaded once per request/action where practical	Yes
Query Insights shows no obvious repeated query storms	Yes
Operation reports show before/after reduction	Yes

Sources

• Prisma query optimization
• Prisma Client logging