Prisma Studio crashes with large databases

[dark wadi]

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[]
}

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

[small plover]

To help others find answers, you can mark your question as solved via Right click solution message -> Apps -> ✅ Mark Solution

[dark wadi]

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

"Paused before potential out-of-memory crash"

[bright hollow]

Hi @dark wadi 👋

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 🙏

[dark wadi]

Hi @bright hollow 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);

Time taken: 8155ms

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

[bright hollow]

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?

[dark wadi]

Querying only the stoptimes takes about 7 seconds. But the StopTimes works well in Prisma Studio

[dark wadi]

How is this possible? The "Fields" filter only hides the UI elements

[dark wadi]

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

[bright hollow]

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

[dark wadi]

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,
});

[dark wadi]

@bright hollow 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;
          }, {})

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.

[dark wadi]

---

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

[dark wadi]

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