Database Query Optimization for Next.js with Prisma
Prisma makes database access easy, but easy doesn't mean fast. Learn how to identify slow queries, use select/include strategically, and avoid the N+1 trap.
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
includewithselectto prevent N+1 queries - Never fetch
contentfields on listing pages — use explicitselect - 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
Admin
Cal.com
Open source scheduling — tự host booking system, thay thế Calendly. Free & privacy-first.
Bình luận (0)
Đăng nhập để bình luận
Chưa có bình luận nào. Hãy là người đầu tiên!