Prisma•2w ago

Prisma Studio crashes with large databases

I have the following model:

model GTFSImport {
  id   Int      @id @default(autoincrement())
  date DateTime

  trafficRegions TrafficRegion[]
  tourismRegions TourismRegion[]

  agencies     Agency[]
  calendars    Calendar[]
  routes       Route[]
  stops        Stop[]
  trips        Trip[]
  stopTimes    StopTime[]
  CalendarDate CalendarDate[]
}

model GTFSImport {
  id   Int      @id @default(autoincrement())
  date DateTime

  trafficRegions TrafficRegion[]
  tourismRegions TourismRegion[]

  agencies     Agency[]
  calendars    Calendar[]
  routes       Route[]
  stops        Stop[]
  trips        Trip[]
  stopTimes    StopTime[]
  CalendarDate CalendarDate[]
}

When i try to view this model in Prisma studio, the tab crashes. The GTFSImport label has only 1 row. The issue is that all of the relations of this row combined return about 1M records. It seems like Prisma Studio fetches all relations just to display the relation count in the UI. Although Prisma Studio uses pagination (Only the first 100) in the model you view, it fetches all relations, making the pagination useless. Related: https://github.com/prisma/studio/issues/1260

GitHub

Prisma studio easily hits Planetscale's 100,000 row read system lim...

Bug description When prisma studio spins up, it defaults to fetching all of the relations in order to show a count in the UI. This causes it to hit Planetscale's row read limit of 100,000 in a ...

15 Replies

schettnOP•2w ago

Strangly it only crashes when I open the GTFSImport model (contains only 1 row). Opening all other models works.

schettnOP•2w ago

"Paused before potential out-of-memory crash"

Nurul•2w ago

Hi @schettn 👋 Thank you for raising this. You are correct that at the moment, all relations are loaded by default which might be causing this issue. We are working on Prisma Studio improvements, so this is something we definitely want to fix 🙏

schettnOP•2w ago

Hi @Nurul I ran the query inside my service:

// Track time
const start = Date.now();

const res = await client.gTFSImport.findMany({
  select: {
    CalendarDate: true,
    agencies: true,
    calendars: true,
    date: true,
    id: true,
    routes: true,
    stopTimes: true,
    stops: true,
    tourismRegions: true,
    trafficRegions: true,
    trips: true,
  },
  skip: 0,
  take: 100,
});

const end = Date.now();

console.log("Time taken: ", end - start);

// Track time
const start = Date.now();

const res = await client.gTFSImport.findMany({
  select: {
    CalendarDate: true,
    agencies: true,
    calendars: true,
    date: true,
    id: true,
    routes: true,
    stopTimes: true,
    stops: true,
    tourismRegions: true,
    trafficRegions: true,
    trips: true,
  },
  skip: 0,
  take: 100,
});

const end = Date.now();

console.log("Time taken: ", end - start);

Time taken: 8155ms

Time taken: 8155ms

So its about 8 seconds. Should Prisma Studio crash in this timeframe?

Nurul•2w ago

I believe prisma studio is crashing because the gTFSImport database record has multiple relation fields fetching a large amount of record What happens if you omit a few relation fields? I mean only select a subset of relations. Does it still fail?

schettnOP•2w ago

Querying only the stoptimes takes about 7 seconds. But the StopTimes works well in Prisma Studio How is this possible? The "Fields" filter only hides the UI elements

schettnOP•2w ago

I think that prisma studio runs into memory problem when processing this large amout of data client side. So its not directly related to the response time. I calculated the total size of the query that is performed by Prisma Studio for the GTFSImport table (with relations):

Time taken:  8059ms
Size of res:  181.3017292022705 MB

Time taken:  8059ms
Size of res:  181.3017292022705 MB

Nurul•2w ago

That's possible. I am sharing this with our team internally so that we can improve next iteration of studio and fix these issues

schettnOP•2w ago

Thanks Is it a design decision that currently all relation data is fetched, or is it a "bug"? Because if the relations are only needed for the relation counts then a simple rewrite to this would do the trick:

const res = await client.gTFSImport.findMany({
  select: {
    _count: {
      select: {
        CalendarDate: true,
        agencies: true,
        calendars: true,
        routes: true,
        stopTimes: true,
        stops: true,
        tourismRegions: true,
        trips: true,
      },
    },
  },
  skip: 0,
  take: 100,
});

const res = await client.gTFSImport.findMany({
  select: {
    _count: {
      select: {
        CalendarDate: true,
        agencies: true,
        calendars: true,
        routes: true,
        stopTimes: true,
        stops: true,
        tourismRegions: true,
        trips: true,
      },
    },
  },
  skip: 0,
  take: 100,
});

@Nurul I have reverse engineered the issue. Take a look at the following code:

 r.select = l.reduce((e, t) => {
            console.log(e,t)
        

            if (!t) return e;
            if (t.isList && t.isRelation) {
              const s = t.getRelationIDFieldName;
              
              e[t.name] = !s || { select: { [s]: !0 } };
            } else e[t.name] = !0;
            return e;
          }, {})

 r.select = l.reduce((e, t) => {
            console.log(e,t)
        

            if (!t) return e;
            if (t.isList && t.isRelation) {
              const s = t.getRelationIDFieldName;
              
              e[t.name] = !s || { select: { [s]: !0 } };
            } else e[t.name] = !0;
            return e;
          }, {})

prisma studio used this to calculate the select. Normally it would use getRelationIDFieldName to get the primary keys of the relation. In my case i use compound primary keys like @@id([tripId, stopId, stopSequence, gtfsImportId]). With compound keys the getRelationIDFieldName returns null, it sets the relation to true instead using only the primary keys of the relation.

schettnOP•2w ago

--- I fixed this overfetching issue inside Prisma Studio, but it still crashed due to memory.

schettnOP•2w ago

The root cause is that Prisma Studio loads every record of relations into its RecordStore

Nurul•7d ago

Thanks for debugging 🙏 I shared your observations to our team internally. We are planning on improving memory consumption of Studio. One idea would be to not load all relations by default and instead give users an option if they would like to load relations or not.

Gaming

Programming

Prisma Studio crashes with large databases