Which will be faster at scale....?

Thinking about my database structure and would love some words of wisdom.

I have PROJECTS and Projects contain Messages

Each project will likely have a couple hundred messages.

Would it be better to do the search on the server side for Messages associated with the project?

Or… should I just pull the projects list of messages?

Once it’s launched, the messages data type will have tens of thousands of messages and it seems like searching all messages may be a mistake when the project will only have a few hundred messages - none at the beginning of the project and it’s liked that 90% of projects have sub 100 messages.

Thanks.

1 Like

I’d have Projects as a data type.
And projects can have messages.

I think this is best because at scale you can always “archive” old messages if you start to have 50k+ records.

Thanks! Yeah, Projects is my main data type actually with over 30 fields. One of them is messages.

I guess the question is, when I am pulling up the projects messages from the project view… Should I “Do a search for messages where Project = This Project”

Or, is it faster at scale to say “This projects list of messages”

Hello @ian11

Perhaps these resources may help shed some light on dB modelling for performance:
(short answer: “Do a search for messages where Project = This Project”) :wink:

1 Like

The responses you’ve gotten here are correct.

Because you expect each project to have hundreds of messages, “do a search for messages where project=this project” will be the most scalable solution.

If each project had <100 messages, the projects list of messages might provide a slight performance advantage, but it will end up being too slow as that list grows beyond 100.

Hope this helps!

2 Likes

Thanks everyone, I appreciate the insight. I’ve read through a bunch of stuff but it was missing context for me… So I would read - Do a search on the database… unless the list on the object is small. Well… what’s small? lol

I’ve got my answer now. Thank you!

2 Likes