Migrate: Remote migrations are very slow

[divyenduz]

Migrations on a remote database are slow and take around ~300s.

To reproduce:

1. Use prisma2 init with a remote postgres.
2. Observe that all lift commands (save, up etc) are slow.

[mavilein]

Can you provide more details?

1. What's the exact datamodel? (Probably unchanged from init)
2. What's the exact order of CLI commands?
3. Where does the remote Postgres database reside?

[divyenduz]

Sure,

1. It is a bit changed but not big

datasource db {
  provider = "postgresql"
  url      = "<DB-Creds>"
}

model User {
  id        String  @id @default(cuid())
  email     String
  firstName String
  lastName  String
  trails    Trail[]
}

model Trail {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  source    String
  imageUrls String[]
  user      User
}

1. prisma2 lift save --name init took around 300s
   prisma2 lift up took around 1500s

2. Physically, this DB is in EU west 1.

[xiaoyu-tamu]

I can confirm that local Postgres database is also slow.

[divyenduz]

@xiaoyu-tamu : Thanks, can you please share the following:

1. Can you share some numbers? How slow?
2. How are you running local postgres (docker or native process)?
3. Your OS and the IP you are using to access Postgres (localhost, 127.0.0.1, 0.0.0.0 or something else?)
4. If possible, please share your Prisma schema file.
5. Please share your Prisma2 CLI version.

Thanks 🙏

[xiaoyu-tamu]

1. 300s / 1500s, will update SS later.
2. native process installed via homebrew
3. MacOS 10.14.5, localhost:5432
4. same as yours
5. [email protected], binary version: 30a33bde5b1d9f79e0a48491749528da76002e24

[matthewmueller]

My Findings

I've benchmarked lift with the following migrations on an existing lower-level solution:

20190712143201-init

datasource db {
  provider = "postgresql"
  url      = "postgresql://xxx:[email protected]/postgres?schema=trail-migrate"
}

model Trail {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  source    String
  imageUrls String[]
}

This generates SQL similar to this (I modified slightly to create and reference a new schema):

create schema if not exists "trail-migrate";

CREATE TABLE "trail-migrate"."Trail"("id" text NOT NULL  ,"createdAt" timestamp(3) NOT NULL  ,"source" text NOT NULL DEFAULT '' ,PRIMARY KEY ("id"));

CREATE TABLE "trail-migrate"."Trail_imageUrls"("nodeId" text NOT NULL  REFERENCES "trail-migrate"."Trail"("id"),"position" integer NOT NULL  ,"value" text NOT NULL  ,PRIMARY KEY ("nodeId","position"));

Then I ran time migrate up:

time migrate up $POSTGRES_URL
   • 001_init.up.sql

real    0m0.665s
user    0m0.027s
sys 0m0.015s

---

I then ran it against the 2nd migration:

20190712144411-adduser

datasource db {
  provider = "postgresql"
  url      = "postgresql://xxx:[email protected]/postgres?schema=trail-migrate"
}

model User {
  id        String  @id @default(cuid())
  email     String
  firstName String
  lastName  String
  trails    Trail[]
}

model Trail {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  source    String
  imageUrls String[]
  user      User
}

Resulting in the following SQL.

CREATE TABLE "trail-migrate"."User"("id" text NOT NULL  ,"email" text NOT NULL DEFAULT '' ,"firstName" text NOT NULL DEFAULT '' ,"lastName" text NOT NULL DEFAULT '' ,PRIMARY KEY ("id"));

CREATE TABLE "trail-migrate"."Trail"("id" text NOT NULL  ,"createdAt" timestamp(3) NOT NULL  ,"source" text NOT NULL DEFAULT '' ,PRIMARY KEY ("id"));

CREATE TABLE "trail-migrate"."Trail_imageUrls"("nodeId" text NOT NULL  REFERENCES "trail-migrate"."Trail"("id"),"position" integer NOT NULL  ,"value" text NOT NULL  ,PRIMARY KEY ("nodeId","position"));

ALTER TABLE "trail-migrate"."Trail" ADD COLUMN "user" text NOT NULL  REFERENCES "trail-migrate"."User"("id");

Running time migrate up again:

time migrate up $(POSTGRES_URL)
   • 002_add_user.up.sql

real    0m0.596s
user    0m0.022s
sys 0m0.011s

[schickling]

@pantharshit00 can you confirm whether this is still a problem?

[pantharshit00]

This is still an issue.

I deployed the following model with 3 tables(one table is there for PostGIS support):

datasource db {
  provider = "postgresql"
  url      = "postgresql://[email protected]/xxxxx?schema=public&sslmode=required"
  default  = true
}

generator photon {
  provider = "photonjs"
}

model SpatialRefSy {
  srid      Int     @id
  authName  String? @map("auth_name")
  authSrid  Int?    @map("auth_srid")
  proj4text String?
  srtext    String?

  @@map("spatial_ref_sys")
}

model User {
  id    String  @default(cuid()) @id @unique
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id        String   @default(cuid()) @id @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  published Boolean
  title     String
  content   String?
  author    User?
}

prisma2 lift save took 32 secs

prisma2 lift up took 72 secs

[timsuchanek]

Fixed in latest alpha.