Skip to content
Engineering

Scaling PostgreSQL + Prisma past your first million rows

Indexes, connection pooling and the query patterns that start to hurt once your tables grow.

OK

Omar Khaled

CTO

8 min read
Abstract illustration of database tables and query performance charts

Prisma makes the first version of your data layer pleasant. At a few million rows, the defaults start to show their limits. Here's what we tune first.

Index for your actual queries

Run EXPLAIN ANALYZE on your slowest queries. Composite indexes that match your WHERE and ORDER BY clauses usually deliver the biggest win.

model Order {
  id         String   @id @default(cuid())
  merchantId String
  createdAt  DateTime @default(now())

  @@index([merchantId, createdAt(sort: Desc)])
}

Pool your connections

Serverless functions and many app instances can exhaust PostgreSQL connections quickly. Put PgBouncer or a managed pooler in front of the database and tune Prisma's connection limit.

Watch for N+1 queries

Nested includes are convenient but can fan out into many queries. Select only the fields you need, and fall back to raw SQL for complex reports.

Use cursor pagination

OFFSET pagination gets slower the deeper you go. Cursor-based pagination on an indexed column stays fast at any depth.

  • #PostgreSQL
  • #Prisma
  • #Performance

Building something similar?

We help teams put these ideas into production. Let's talk about yours.