Questions about data models with respect to performance

Need help Database
1

How do I make choices on data model architectures and performance trade offs? I’ve been reading about ways to improve performance, but I haven’t found Bubble documentation specific enough to make educated decisions.

I’m working on a social networking app that is currently structured such that I search all posts and then sort by date in order to figure out each user’s feed to display in a repeating group. The development has matured enough that I believe this is a poor approach considering performance (ie having to search all posts and then sort by creation date is not sustainable as the number of posts grows).

I’ve been reading up on performance and learning about ways to restructure the database to minimize the search required to fill the feed. I’m starting to experiment with storing data in lists that drastically simplify the search, but it’s not obvious to me that this is more performant.

Here are some questions that come to mind:

  • If it weren’t for the sort by date requirement, it seems like a large search isn’t necessarily bad because bubble is smart enough to stop when it finds enough results to display the data accordingly. Is that true? In other words, is a search on a large database necessarily bad if I expect an adequate number of results to be found easily?
  • Do write operations to lists in the database require more capacity than searches? How do I determine capacity costs of db writes versus searches? I am tempted to choose to write to lists because this can occur in an API workflow, separate from a slow user experience, but it will require considerably more db write operations since each user’s feed gets updated & stored.
  • Is there any way to determine performance without having to “just try it”? I’m looking at a fairly major architectural overhaul to move to data stored in lists and it’ll be wasted effort if I try it and find that it’s not performant.

Cheers,
Ned

2

This video by @duke.severn is a very interesting watch > Data architecture design in Bubble.is

3

Thanks @cmarchan! I had not seen that video before.

However, I believe I have already implemented @josh’s recommendation of having reference fields in each post. Regarding the example in this thread: Performance Q&A guide, how is bubble’s performance (and capacity) when there are tens of thousands of invoices and the query has to include them all?

In my app, the posts have different properties (ie user who posted it, category, etc.) and I have to search across all posts and include/exclude based on different criteria. In other words, the search isn’t straightforward. I’m not using advanced filtering, but I am looking for the newest posts that meet a certain criteria. With only a handful of users and a few hundred posts, my app is already maxing capacity on the personal plan. I can’t tell for sure, but I believe the majority of the capacity is consumed by the search operation.

I believe I can restructure the data model such that posts are placed in lists based on combinations of the criteria (again, post creators & category) and keep the lists relatively short by keeping only the most recent posts. However, this will require each post to be written to many lists, which will affect capacity, but I’m hoping that because it can occur in an API workflow that it won’t slow down the user experience.

I believe I will try the new approach, even though it goes against @josh’s recommendation and see how the performance compares. I wish there was an easier way to determine bubble performance prior to implementation.

Thanks!
Ned

4

Consider using an outside search provider like Algolia. And of course … I would continue to optimize the app as you kindly detailed above.