prisma: Slow queries (Next.js + Prisma + Postgres)

Bug description

I have built an app where you can house forums. This application is built using Next.js, Prisma, and Postgres. Everything runs super smooth and fast in the local development environment.

• When I deployed everything live I ran into a couple of issues. I resolved the database connection issues I was having using connection pools for the Postgres database in Digitalocean.

• I resolved some performance issues I was having by making sure that all services (client/serverless functions/database) are located in the same geographical region (Germany/Belgium)

However

I am still having some pretty annoying performance issues. It’s on the border of not being a particularly good user experience. I have run some tests using k6 on a couple of different test routes, to demonstrate the issues I’m having.

Get forums

export default async (req, res) => {
  const forums = await prisma.forum.findMany({
    include: {
      users: true,
    },
  });

  res.json(forums);
};

Get forums without users

export default async (req, res) => {
  const forums = await prisma.forum.findMany();

  res.json(forums);
};

Get without query

export default async (req, res) => {
  res.status(200).end()
};

How to reproduce

1. git clone git@github.com:albingroen/formulate.git
2. cd app && yarn
3. Install k6
4. env API_URL="https://formulate-theta.vercel.app/api" k6 run loadTest.js

Expected behavior

I expect the requests (Get forums and Get forums without users) to be quite a bit faster. The tables include less than 10 rows each, so that shouldn’t be the reason either.

Prisma information

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model PostReaction {
  id    Int    @id @default(autoincrement())
  value String @db.VarChar(255)

  post   Post @relation(fields: [postId], references: [id])
  postId Int

  user   User @relation(fields: [userId], references: [id])
  userId Int
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  posts     Post[]   @relation("PostToPost")
  user      User     @relation(fields: [userId], references: [id])
  userId    Int

  forum   Forum @relation(fields: [forumId], references: [id])
  forumId Int
  Post    Post? @relation("PostToPost", fields: [postId], references: [id])
  postId  Int?

  reactions PostReaction[]

  @@map(name: "posts")
}

enum ForumUserRole {
  USER
  ADMIN
}

model ForumUser {
  id        Int           @id @default(autoincrement())
  createdAt DateTime      @default(now())
  updatedAt DateTime      @updatedAt
  user      User          @relation(fields: [userId], references: [id])
  role      ForumUserRole
  userId    Int

  forum   Forum? @relation(fields: [forumId], references: [id])
  forumId Int?

  @@map(name: "forum_users")
}

model Forum {
  id          Int         @id @default(autoincrement())
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
  title       String      @db.VarChar(255)
  description String?
  posts       Post[]
  users       ForumUser[]
  logotype    String?

  user   User? @relation(fields: [userId], references: [id])
  userId Int?

  Invitation Invitation[]
  @@map(name: "forums")
}

model Account {
  id                 Int       @id @default(autoincrement())
  compoundId         String    @unique @map(name: "compound_id")
  userId             Int       @map(name: "user_id")
  providerType       String    @map(name: "provider_type")
  providerId         String    @map(name: "provider_id")
  providerAccountId  String    @map(name: "provider_account_id")
  refreshToken       String?   @map(name: "refresh_token")
  accessToken        String?   @map(name: "access_token")
  accessTokenExpires DateTime? @map(name: "access_token_expires")
  createdAt          DateTime  @default(now()) @map(name: "created_at")
  updatedAt          DateTime  @default(now()) @map(name: "updated_at")


  @@index([providerAccountId], name: "providerAccountId")
  @@index([providerId], name: "providerId")
  @@index([userId], name: "userId")
  @@map(name: "accounts")
}

model Session {
  id           Int      @id @default(autoincrement())
  userId       Int      @map(name: "user_id")
  expires      DateTime
  sessionToken String   @unique @map(name: "session_token")
  accessToken  String   @unique @map(name: "access_token")
  createdAt    DateTime @default(now()) @map(name: "created_at")
  updatedAt    DateTime @default(now()) @map(name: "updated_at")

  @@map(name: "sessions")
}

model User {
  id            Int       @id @default(autoincrement())
  name          String?
  email         String?   @unique
  emailVerified DateTime? @map(name: "email_verified")
  image         String?
  createdAt     DateTime  @default(now()) @map(name: "created_at")
  updatedAt     DateTime  @default(now()) @map(name: "updated_at")

  postReactions PostReaction[]
  forumUsers ForumUser[]
  forums     Forum[]
  posts      Post[]

  @@map(name: "users")
}

model VerificationRequest {
  id         Int      @id @default(autoincrement())
  identifier String
  token      String   @unique
  expires    DateTime
  createdAt  DateTime @default(now()) @map(name: "created_at")
  updatedAt  DateTime @default(now()) @map(name: "updated_at")

  @@map(name: "verification_requests")
}

enum InvitationStatus {
  PENDING
  DECLINED
  ACCEPTED
}

model Invitation {
  id     Int              @id @default(autoincrement())
  status InvitationStatus @default(PENDING)

  forum   Forum @relation(fields: [forumId], references: [id])
  forumId Int
}

Environment & setup

• OS: MacOS
• Database: PostgreSQL
• Node.js version: 12.8.2
• Prisma version:

prisma               : 2.22.1
@prisma/client       : 2.22.1
Current platform     : darwin
Query Engine         : query-engine 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 60cc71d884972ab4e897f0277c4b84383dddaf6c
Studio               : 0.379.0