Database Query Optimization: Fix Slow Prisma and PostgreSQL Queries

A single slow query can bring down your entire application. Here is how to find and fix them.

Step 1: Identify Slow Queries

Enable Prisma query logging by setting log to query, warn, error in PrismaClient options.

Step 2: EXPLAIN ANALYZE

Run EXPLAIN ANALYZE on slow queries. Look for Seq Scan (bad means no index), high actual time, and large rows scanned.

Step 3: Fix N+1 Queries

// BAD — N+1 problem
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
}

// GOOD — single query with include const posts = await prisma.post.findMany({ include: { author: true } }); ```

Step 4: Add Indexes

Add @unique for unique lookups and @@index for frequently queried columns and composite queries.

Step 5: Cursor Pagination

Never use offset pagination for large datasets. Use cursor-based pagination with take and cursor.

Step 6: Select Only What You Need

Use Prisma select to fetch only the columns you actually need.

Get expert database optimization. See our database services.