4 replies

How to query messages only from a user’s friends (~1000 friends/user, ~100k messages/topic).

❔Advice

I’m considering moving a chat app to Convex but have some concerns about performance I was hoping for some clarity on. In our app, each user only sees messages from people who are their friends, for each channel. Example: a channel has ~100k messages, and a user may have ~1000 friends. I need all messages in that channel written by authors in that user’s friend list.

Simplified schema:

friendships:
user: Id<"users">
friend: Id<"users">
// index: by_user

friendships:
user: Id<"users">
friend: Id<"users">
// index: by_user

messages:
channel: Id<"channel">
authorId: Id<"users">
createdAt: number
// index: by_channel_createdAt

messages:
channel: Id<"channel">
authorId: Id<"users">
createdAt: number
// index: by_channel_createdAt

What I expected to do is the equivalent of SQL:

select messages
WHERE channel = <channelId>
AND
authorId IN (friendIds)

select messages
WHERE channel = <channelId>
AND
authorId IN (friendIds)

But Convex filters don’t support an “in” operator for a large dynamic array.

What my options seem to be:

Huge OR chain:

q.or(...friendIds.map(fid => q.eq(q.field("authorId"), fid)))

q.or(...friendIds.map(fid => q.eq(q.field("authorId"), fid)))

This is not reasonable for ~1000 IDs?

Or: Fetch all recent messages by index and filter in JS:

const recent = await ctx.db
.query("messages")
.withIndex("by_channel_createdAt", q => q.eq("channel", channel))
.order("desc")
.take(2000);

const friendSet = new Set(friendIds);
return recent.filter(m => friendSet.has(m.authorId));

const recent = await ctx.db
.query("messages")
.withIndex("by_channel_createdAt", q => q.eq("channel", channel))
.order("desc")
.take(2000);

const friendSet = new Set(friendIds);
return recent.filter(m => friendSet.has(m.authorId));

This only works if I pick an arbitrary recent window. It does not solve searching through all 100k messages without scanning everything.

My question:

What is the recommended Convex approach for:
“Get all messages in channel X where authorId is in a large dynamic list (1000 items)”
without scanning the entire channel and without generating a massive OR chain?

I get that I can denormalize this and have a view for each user, where the messages that get written are duplicated per user to make it O(1) at read time. But this could be 1000x more writes per message which doesn't make sense to do.

How to query messages only from a user’s friends (~1000 friends/user, ~100k messages/topic).

Similar Threads