Optimization of collections with 6k+ entries

[vstarush]

Hello,

In our project we have collections of approximately 6k entries. And then we have other collections that arrange these entries in various particular orders. For example:

Collection "movies" has 6k+ movies (objects with ~10 fields).
And then we have multiple categories like "Action movies" where there is an array component with each item in the array being a reference (relationship field) to a movie (order is important). Each category has ~50 movies and there are ~30 categories.

We noticed a significant drop in how fast the CMS works when added all of the movies and I was wondering is there anything we can do to improve the performance? Any ideas welcome :)

Thank you!

[jmikrut]

Hey @vstarush — I think we can get to the bottom of this.

Here are some questions for you that might help you narrow down the cause.

1. Where in specific are you seeing a slowdown? Is it just loading a typical REST find operation of 10 results with depth of ~1-2? Or are you using a where query on fields?
2. Are you using REST or GraphQL primarily?
3. How long does it take for the REST API to return a single movie from the findByID operation?
4. Does the slowdown impact the write performance? Or only the read performance?
5. Where do you have your database hosted?
6. Where is your API hosted? We saw recently that there was a Payload project hosted in a serverless environment on Vercel, and it was limiting the concurrent Mongo connections, which made the API extremely slow... but this was not a problem with Payload, it was a problem with the hosting infrastructure.

In regards to bullet 1 above, if you are seeing the slowdown while performing where queries, it's possible that the fields you're querying on are not indexed properly within your database—and indexing any fields you query on often will solve your issue.

We have lots of projects that have 6K+ records and they're still blazingly fast. For example, a customers collection in one of our projects has 7,000 records, each having relationships to other collections that have thousands of records. Retrieving 10 customers takes ~170ms to be returned from our production API.

Another thing you could try to do is use the maxDepth parameter on your relationship fields, which will limit the populated depth of your returned requests. For example, if you're retrieving a category, you are probably retrieving the category and then the 50 movies for a total of 51 documents returned. If you retrieve 10 categories, that's 510 documents returned.

If you're often retrieving categories in that context, I'd set maxDepth on the movies relationship field to 0. That would make sure your categories are returned fast, and you can populate the movies manually if needed.

Another idea that we could do, which would take advantage of MongoDB's NoSQL nature is store movies on categories as "embedded" documents. Just take the field schema from movies, and re-use the fields exactly within your categories collection. Then write an afterChange hook on your movies collection which goes through and finds all categories that have a reference to the movie that was updated and make sure that the embedded movie within the category is updated accordingly.

Last note - soon, we are going to build a performance enhancement that "batches" queries together from the same collection, which in your case would reduce the 50 queries to movies on a category down to 1 query that returns 50 movies.

This performance enhancement will be released by 1.0 and you can read more in this discussion.

What do you think?

[vstarush]

Hi @jmikrut !

Sorry, I had to give more explanation (although you gave so many interesting ideas that I am partially happy I didn't explain the problem well enough! :)

So the API is still blazing fast, I am using "REST / where" queries for search and I did index couple of fields in movies collection so there is no problem there. API is very good!

The problem is when we log in CMS itself, and for example need to check something in customers collection, or update couple of specific movies, or add movies to categories, or check transactions, which eventually might hit the number or a lot more than 6k, or just browsing CMS entries, the interface loads and switches the collections much slower compared if you have a relatively empty CMS. Which is understandable, cause it is a lot of data.

But maybe there anything we can do to improve that user experience in CMS? Essentially the CMS UI load time I guess.

[jmikrut]

OK. Is it the "list" view in specific that is slow? That would make sense to me.

Or is it multiple views? Sounds like even editing a single document or updating a document is slow, which is very surprising to me. I would like to 100% confirm that.

How many relationships does a typical Movie have?

There are some things we can do on our end to improve list view speed. The list view uses depth=1 but it really should use depth=0. So when you are viewing the Categories collection list view, you're actually loading 50+ docs with all relations currently.

The fix would be to set that to depth=0 and then manually fetch the relationships separately within the table cell components if you have relationship fields displayed as columns.

Does this all make sense?

[vstarush]

This actually really does make sense! Yes, the list view of categories and the category object with the array of movies loads slow.

And movies themselves do not have any relationship fields at all. And they are actually loading ok.

What do consistently load very slow are the categories. And then when you constantly go back and forth between the categories and movies, it creates this unpleasant experience of the CMS loading slow. For example, if you switch to categories and then quickly switch to movies, the CMS seems to be still loading categories and then, once it's done, loads the movies.

I tried your solutions to set maxDepth on the relationship field of movies inside categories to 0 and it seems to be working, loads much faster in CMS! The problem is now that in the frontend I am relying on that depth 1 because I am loading the movies from the category, since I need that particular order and it is convenient to just have movies information within category itself.

Is there a way to set depth separately for CMS collection list view and then to specify depth in the query? Or what would be in your opinion the best solution?

The structure of categories:

category

• array
  • movie
  • movie
  • movie
  • ...

Thank you!!