Storing big payloads without killing search speed
Hey! 👋
I’m not super experienced with backend and specially DB stuff, but Convex makes it easy enough that I’m diving in anyway, so thanks for that.
I’ve got a question about handling a lot of data while still keeping search reasonably fast.
Here’s the situation:
I’m using an API that returns a structured JSON with around 200 lines per item. Since it’s a paid API, I want to store everything. Right now I only need about a third of the fields, but if I ever decide to support more later on, I don’t want to pay again to fetch the same data.
I’m thinking about two approaches:
1) Store the full payload in the database.
It’s just an MVP right now, but I still want decent search speed. Each tenant could easily have an average of 15k records. The only way I see this working is creating around 10 indexes, but that feels like it might get expensive in terms of storage.
2) Only store the fields needed for the table.
That’s maybe 10 columns. I’d still need around 10 indexes for fast queries. The rest of the raw payload could be dumped in storage.
So what’s the better approach?
Is there a different angle I’m missing?
Any helpers I could use?
TIA
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!
I have 180k DB rows with quite a lot of information per row and I needed to do full text search. The most effective way I found is to use an external DB (Turso in my case) to store the data. Having big documents is exploding the ram because convex stores a lot in the ram for efficiency I guess. I would recommend you to store your data externally and only store IDs in convex
thanks for the suggestion, will look into that