User messaging system, ecto associations or embedded jsonb?

Phoenix Forum Questions / Help
, ,
1

Hi,
I’m creating a user messaging system where people can create conversations and send messages to each other. Conversation can have only 2 users and I expect users to send between 2 and 10 messages in a single conversation, no more then that, so it’s not like a chat.

So far, I’ve created a conversations and messages tables, each message has a conversation id so when conversation is loaded, it gets all the associated messages. But now I’m thinking that maybe messages inside embedded jsonb field would be a better option. Why? Now when conversation is loading, it has to search the messages table and find all associated messages, so wouldn’t it be easier to just load all messages from embedded field? On the other hand, adding new messages to embedded field is more complex because a map with all messages has to be written again to the db, while a single message has to be written if using associations.

So, what would be a better solution performance wise?

2

Hi @KiKi, the conversation_id in the messages table will be highly selective, so if it is indexed, lookups from messages will be fast.

I’d model it the simplest way to code first. If it doesn’t perform well enough try a couple of options. Use a benchmarking tool to compare.

1 Like