Database Query Optimization for Next.js with Prisma

Prisma is the best DX I've used for database access in a JavaScript project. It's also the easiest way to accidentally write queries that take 2 seconds instead of 20 milliseconds. The abstraction is so clean that you forget there's SQL underneath — and SQL performance requires intentional design.

Here are the optimization patterns I've applied across multiple Next.js + Prisma production apps.

The N+1 Query Problem

This is the most common Prisma performance issue, and it's easy to miss. You fetch a list of posts, then for each post, you fetch the author. That's N+1 queries: 1 for the list, N for each author.

// ❌ N+1: One query per post to fetch author
const posts = await prisma.blogPost.findMany({
  where: { status: "PUBLISHED" },
});

// This triggers N additional queries in a Server Component
// if you access post.author in the template
for (const post of posts) {
  console.log(post.author?.name); // Lazy load = new query each time
}

// ✅ Single query with include
const posts = await prisma.blogPost.findMany({
  where: { status: "PUBLISHED" },
  include: {
    author: {
      select: { id: true, name: true, avatarUrl: true },
    },
    category: {
      select: { name: true, slug: true },
    },
    _count: {
      select: { comments: true, likes: true },
    },
  },
});

The include with select pattern fetches related data in a single query (or a minimal number of JOINs) and only returns the fields you need.

Select Only What You Need

By default, Prisma selects all columns. If your BlogPost model has a content field with 10KB of HTML, fetching 50 posts for a listing page transfers 500KB of data you don't display.

// ❌ Fetches all fields including heavy content
const posts = await prisma.blogPost.findMany({
  where: { status: "PUBLISHED" },
  take: 20,
});

// ✅ Select only listing-relevant fields
const posts = await prisma.blogPost.findMany({
  where: { status: "PUBLISHED" },
  select: {
    id: true,
    title: true,
    slug: true,
    summary: true,
    coverImageUrl: true,
    publishedAt: true,
    readingTime: true,
    author: {
      select: { name: true, avatarUrl: true },
    },
  },
  take: 20,
  orderBy: { publishedAt: "desc" },
});

This reduced our listing page query from 45ms to 8ms on a table with 2,000 posts.

Indexing for Common Queries

Prisma generates the schema, but you still need to think about indexes. Add @@index for any field used in where, orderBy, or unique constraints:

model BlogPost {
  id          String   @id @default(uuid()) @db.Uuid
  slug        String   @unique
  status      String
  articleType String
  publishedAt DateTime?
  authorId    String   @db.Uuid
  categoryId  String?  @db.Uuid

  @@index([status, publishedAt(sort: Desc)])
  @@index([authorId, status])
  @@index([categoryId, status, publishedAt(sort: Desc)])
}

Composite indexes matter. If your most common query filters by status and sorts by publishedAt, the composite index [status, publishedAt] is dramatically faster than separate indexes on each field.

Pagination: Cursor vs. Offset

Offset pagination (skip: 100, take: 20) gets slower as the offset grows because the database scans and discards rows. Cursor-based pagination maintains consistent performance:

// Cursor-based pagination — O(1) regardless of page depth
const posts = await prisma.blogPost.findMany({
  where: { status: "PUBLISHED" },
  take: 20,
  cursor: lastPostId ? { id: lastPostId } : undefined,
  skip: lastPostId ? 1 : 0,
  orderBy: { publishedAt: "desc" },
});

Caching Query Results

For read-heavy pages (homepages, listing pages), cache Prisma results in Redis. A 5-minute cache on your homepage query eliminates thousands of database round-trips per hour:

• Cache serialized JSON with a TTL matching your freshness requirements
• Invalidate on write operations (create, update, delete)
• Use pattern-based invalidation (blog:list:*) to clear all list cache variants at once

Takeaways

• Always use include with select to prevent N+1 queries
• Never fetch content fields on listing pages — use explicit select
• Add composite indexes for your most common query patterns
• Switch to cursor-based pagination for deep page navigation
• Cache read-heavy queries in Redis with pattern-based invalidation