Query about load

[wary obsidian]

So I am self hosting convex so I am really being conscious about my app to make sure I do not put extra load on CPU and bandwidth, so I want to fetch bunch of chat Documents and these chat documents needs to have a profile pic, They have a relational field that connects them to the character, now each chat document can use different chars so.

I want to know can I create a single query that first gets all the chat documents then their corresponding character data and return the iamge url of each chat with them.

Doing this cuz if I save the image in the chat document and if users updated a character's image it wont get updated, but concerned it will take more CPU compute.

Tell me the best way to keep a high cache hit for this, mkaing sure user have a good UX, and its not putting as low load as possibel on server.

[blazing echo]

Hi @wary obsidian. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

You can absolutely create a single Convex query that fetches all chat documents and, for each, loads the corresponding character data (including the image URL). This is a common pattern in Convex and is efficient because Convex functions run close to the database, minimizing the overhead of multiple lookups (the so-called "N+1 problem" is much less of an issue in Convex than in traditional databases) Database Relationship Helpers: N+1.

Recommended Approach

1. Store only the character reference in the chat document (not the image itself). This ensures that if a character's image is updated, all chats referencing that character will reflect the change automatically.
2. In your query function, fetch all chat documents, then for each, fetch the associated character document and extract the image URL. This can be done in a single Convex query function.

Example (pseudocode):

export const listChatsWithCharacterImages = query(async (ctx) => {
  const chats = await ctx.db.query("chats").collect();
  const results = [];
  for (const chat of chats) {
    const character = await ctx.db.get(chat.characterId);
    results.push({
      ...chat,
      characterImageUrl: character?.imageUrl,
    });
  }
  return results;
});

Performance and Caching Considerations

• CPU/Compute: Convex is optimized for this pattern. Fetching related documents in a loop is fast because the code runs close to the database, and db.get is a lightweight operation Database Relationship Helpers: N+1.
• Cache Hit Rate: Convex automatically tracks which documents your query reads. If a character's image changes, only queries that reference that character will be invalidated and re-run. This means you get high cache hit rates and only re-compute when necessary Optimizing queries for caching.
• Bandwidth: If you have a large number of chats, consider paginating your results to avoid loading too much data at once and to reduce bandwidth and CPU usage Splitting up the work with pagination and limits.

Best Practices

• Use indexes on fields you filter or join on (e.g., characterId in chats) to avoid full table scans and keep queries efficient.
• Paginate if you expect a large number of chat documents.
• Let Convex handle cache invalidation—it will only re-run queries when relevant data changes, keeping bandwidth and CPU usage low.

This approach gives you up-to-date images, efficient server usage, and a good user experience. If you need even more control over caching (e.g., to persist data between navigations), you can look into the Convex query cache helpers, but note that these may increase bandwidth usage in exchange for better UX Discord: useInfiniteQuery and usePaginatedQuery : Caching.

If you have further concerns about bandwidth or CPU, consider paginating aggressively and only loading additional data as needed.

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: