Gonna have to give the textbook ‘It depends’ on this one.
There are two things that i feel most drastically affects performance. One is the length (and weight) of the list. Lists are downloaded in their entirety, and are filtered on the client and cannot be filtered in the bubble database. Long lists are generally a bad idea on Bubble (ie >500 elements). Filtering on small lists of 10-100 can be fast and cause no problems. The second thing is the complexity of the filtering; if you are running some simple filtering on Dataset fields which are downloaded to the client anyways it will be quite fast. If you are filtering on Collections fields, then you will just add these filters on the constraints of your ‘Do a search for’. On top of that the filtering needs to be simple, essentially boolean masking. If you need more complex filtering (using Bubble’s Advanced filtering operator), performance will be reduced. I’ve seen apps get slowed down to a halt because of advanced filtering. Recipe for destruction is when a filter applied to a list needs to query the DB for every line item of the list.
This thread could also be useful when thinking about bubble DB structuring:
And @petter 's book on bubble performance is a great read to understand bubble’s dos and donts.
I can see that you have dealt with relational databases before. With the intent to make it more intuitive, Bubble approaches relations slightly differently, and abstracts away Foreign Keys.
For example, your Repeating Group’s data source will only be “Collection’s dataset_ids”. This expression does not return the PKs of the Datasets, it returns the datasets themselves.
Give some examples of what type of filtering you need, and the estimated max length of the dataset_ids list.