One to one relationships vs returning partial records
Is there a performance or cost benefit to separating data into multiple tables with one to one relationships vs storing all the data into one table and returning partial records if that’s what you need?
Example:
Books table with title, author, page count, etc
Chapter summaries as a separate 1:1 table (bookId with an index) vs chapter summaries as a field on the books table.
To just query for a list of books with multiple tables, I can just query and return from the books table. If a single table I could remove the chapter summaries before returning data to the client to keep bandwidth down.
Then if I want a list of books or a single book with summaries I could either join that data (convex style) or just not filter it out.
Are there considerations other than the 8MiB data read limit for queries (possible downside to a single table) and having an extra index (possible downside to 2 tables)?
3 Replies
Thanks for posting in <#1088161997662724167>.
Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets.
- Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.)
- Use search.convex.dev to search Docs, Stack, and Discord all at once.
- Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI.
- Avoid tagging staff unless specifically instructed.
Thank you!
Database bandwidth is between functions and the database, so queries always return entire records from a bandwidth standpoint. If you use the books table a lot but only rarely reference chapter summaries it would probably be worth splitting off a table for those. It’s also recommended that array fields have around ten items or less, so if you’re using them for your chapter summaries they may often be larger than that.
But it’s also important to bias for product execution and defer optimizations until there’s an active problem to solve. That’s a lot of the magic of Convex, stuff kinda just works. So if the way you naturally do it works and doesn’t cause obvious issues, I’d start with that.
Excellent, thank you!