I'm trying to build an Instant Messaging functionality in my app as part a bigger project.
- Chats can have more than 2 participants (group chats)
- If participant A delete a message, it still should be visible to participant B (that's why I used the Message Participants table)
- Same applies to Conversation.
- By same logic, if all participants delete the conversation/message, it should be erased from DB.
I'm afraid that this schema is too cumbersome, meaning that the queries will be too slow once the app gets certain traffic mark (1k active users ? I'm guessing)
Message Participants will have multiple records for each message - one for each participants in the chat. Instant Messaging means it will involve those writes with very tight timings. Wouldn't that be a problem?
Should I add a layer of Redis DB, to manage a chat's active session's messaging? it will store the recent messages, and actively sync the PostgreSQL db with those messages (perhaps with Async transactions functionality that postgresql has?)