Struggles working with Nova with large datasets

[kfirba]

• Laravel Version: 6.14.0
• Nova Version: 2.10.1
• PHP Version: 7.3.14

Description:

Hello,

I've installed Nova on one of our projects which has quite a large dataset. It seems like we can't properly optimize the database queries that are being run by Nova. To demonstrate the issue, let's assume we have a few models:

• User - (We have well over 1M users)
• Post
• Tip
• Comment (user_id, commentable_type, commentable_id)

The relationships:

• User hasMany Comment
• Tip morphToMany Comment
• Post morphToMany Comment

The Comment resource's fields are:

public function fields(Request $request)
    {
        return [
            ID::make()->sortable(),

            BelongsTo::make('User')->searchable()->readonly(),

            MorphTo::make('Commentable')->types([
                Tip::class,
                Post::class,
            ])->searchable(),

            Textarea::make('Body'),
        ];
    }

The Issue

There are a few issues in this scenario.

1. Making the User field readonly() did not prevent the request to fetch every user in the system in order to populate the select box (which is disabled). We had to "fake" it and add the searchable() property in order for it to not select all users. It would be nice if it respected the readonly() flag and did not issue a request to populate the select box with every single user but just the one currently selected.

2. There seems to be no way I can control what the BelongsTo searchable() autocomplete's query:
   2.1. Ideally, since we have so many users and each user record is packed with lot of data, I would not want to do a simple select * but rather choose the columns I know that we are going to need. For example, select only the id, username, and first_name columns. This speeds up the query significantly. I tried to achieve that using the relatableUser method but it seems like it ignored my select() call there. We tried leveraging the $with field ($with = ['user:id,username,first_name']), but it seems to only affect the index page.
   This issue is even worse when we try to fetch Post from an autocomplete since it has a huge body which slows down the query significantly when we fetch many posts.

2.2. When I type into the autocomplete, the system runs a query that matches the index page's query. It will execute a like query against all columns specified in the $search property of the User resource. We know that whenever we search for a user from an autocomplete, we always want to search by its id or by its username. We thought that we could override the default query's filters in the relatableUser method, but we couldn't. When we tried doing so, it seemed like our where clauses were simply appended to the query. At the moment the Autocomplete is basically unusable for us; it takes over 15 seconds to get some results

3. Eager loading polymorphic relationships - on our Comment resource, we want to eager load the commentable relationship. The Commentable objects have many redundant fields that are never used in the index page or any other place which are slowing the query significantly. I tried to leverage the $with property (just like I did with the user relationship) and pass a callback to it so I can choose which fields I want for each commentable object, but we get an error that we an expression is not allowed as a default value:

public static $with = [
    'user:id,username,first_name',
    'commentable' => function (MorphTo $morphTo) {
        $morphTo->morphWith([Post::class => 'id,title', Tip::class => 'id,title']);
    },
];

It seems like we are struggling to optimize the system to work with very large datasets.
Are we missing something?

[LorenzoSapora]

Seems like poorly optimised datasets/databases, over a nova issue. I have several tables with 100s of millions of records, and find nova to be pretty snappy, even compared to raw db calls.

As for the autocomplete part of your question, if you're dealing with large datasets, you should probably handle searches with elasticsearch or algolia. From my point of view, you're just outgrowing the single dataset instance, and require some additional caching and/or optimisation.

[kfirba]

@LorenzoSapora Hey.

Excuse me but what you are saying is not entirely correct. The database is far from being poorly optimized. In fact it is quite performant. “Nova” isn’t slow. It’s the queries that’s fetch redundant data. I’m not sure with what kind of data you are working but when records get as big as 10MB, fetching even 20 of them is not optimal. Ideally, we would want to fetch only the data that we know we need. That improves the performance significantly!

Since the searches are on the primary key most of the time, there is no need to introduce more overhead.

I truly think that having the ability to control those things can help us optimize queries for large datasets. “Large” does not necessarily mean millions of records, but also large in “width”.

[LorenzoSapora]

Isn't that your issue, then? Large single records? Surely that information doesn't need to be on the original table, and can be moved to a table, then called when needed. Having rows that you regularly query be 10MB is hardly performant.

I think we're talking hypothetically, without examples to base it from. But from what you posted above, your issues appear to be schema based. Also, have you tried searching with elastic/algolia? you may have better success.

[jbrooksuk]

This isn't StackOverflow... we're not here to tell people to do things differently.

@kfirba is right, there are definitely aspects of Nova that can be improved for performance. @davidhemphill and I have already discussed this and agree that at the very least, the BelongsTo field can be optimised further.

@LorenzoSapora is also right, that searches on large datasets should be handled by ElasticSearch or Algolia. Nova is compatible with Laravel Scout, so this would actually be a relatively straightforward migration once the data is in place.

Thanks for the suggestions!

[kfirba]

@LorenzoSapora Your suggestions are almost impossible to implement quickly in an enterprise environment. Since we know what data we have we can easily optimize our queries. If I know that I only want to search my ID (PK) and my title field, and that rule is not expected to change for the foreseen future, there is really no reason to introduce another system. Actually, we are using Postgres' Full-Text Search capabilities to help in places where we need to scan whole documents.

@jbrooksuk I think that even maybe introducing a low-level implementation of the relationship can help us improve the query on our own. It's great that for most of our cases the "defaults" is just fine but we are also in deep need to customize some of the queries so we can make the admin area just perfect.

Thanks for the hard work!

[LorenzoSapora]

Glad you got your answer :)

Also, PGSQL isn't as fast as elastic/algolia, as postgres is still a traditional RDBMS. But I guess you're working with what you've got.

[mforcer]

I'm experiencing similar issues.

For example, in our app... we have hundreds of thousands of users, and we have a OneToMany relationship with various models, in this case, Credits. (User->Credits) .. now if we want to add a credit to a users account we can, of course, use the Credit resource, however it takes around 30 seconds to populate the User select field, and then it becomes so big it renders the page unresponsive.

This isn't an optimisation issue, this is an issue with Nova not built to properly handle large datasets, for example, it should probably be a fuzzy/autocompletion field. I understand some 3rd party packages offer this which I will try - but it would be nice to have this natively.

I've found a bigger issue when assigning the credit on the inverse ... Creating a Credit from the User resource will auto-select the User, but it still tries to load ALL of the User models (Rather than the single record).. therefore the select field still takes over 30 seconds to be populated.

The users are fetched from: /nova-api/credits/associatable/user?current=110170&first=false&search=&withTrashed=false&viaResource=users&viaResourceId=110170&viaRelationship=credits

[LegitDongo]

Agreed entirely with issue 1.
It would have taken me much longer to figure this out if I hadn't found this issue.
My issue was on a much larger scale since one of my nova plugins would grab all permissions and roles every time a user was acquired, so much more obvious 😄

Issue 2 would be a nice-to-have sort of thing; especially since all I need for a BelongsTo relationship is the ID and title column.
This also would be nice so that it doesn't load relationships of the relationship when it's unneeded.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[dumitriucristian]

Did someone found a solution to this problem? I have the exact same situation. I have 150.000 locations that can have one or many events, but when I try to create an event, the option field generated by belongsToMany is trying to load 150.000 points and I get 504 err. Is something I should do differently?
Thank you. If instead of an option field I would have only the id of the point there will be no problem.

[vesper8]

It seems like the only solution for now is to use a custom select field and match it with a custom api endpoint where you have full control over the query that occurs, then you can implement a proper autocomplete/fuzzy search that doesn't return the entire dataset and bring the house down

[apmovamo]

Working with Nova 3.X version and Laravel 7 would be nice to avoid offset pagination and use cursor pagination. Its pretty slow the offset sql queries for large datasets.