Best way to store the retrieve chat messages for many users at scale

Dan Parker 101 Reputation points

So image there is a large log table that holds all the ChatMessageLog (incoming and outgoing), millions of messages 100k+ conversation some 1 messsage some are up to 30 maybe.

Current logic is a query reading from the ChatMessageLog table where chatToUser= (this will get a list of messages in and out)

I think it would be better to build a conversations table where we only need relevant parts of the message needed for the frontend. Have a conversationId, chatToUser and a jsonbinary column which will append the last message every time there is a new message into the log table This would duplicate some data in the ChatMessageLog and the jsonbinary column, but would probably be more efficient in the queries we use. (It would just be append, we wouldn't ever edit any messages)

I'm not big into triggers, but a trigger there be safe.

I don't know if there maybe a way to build an indexed view that could be easier and not repeat data. But that sounds like it could have issues. Maybe it's worth the effort to put these in NoSQL cosmos, but I think jsonb might be the solution.

Azure SQL Database
Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,437 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP

    I can't speak to Cosmos or any other NoSQL solution, since I don't know them. But my gut reaction is that mixing in different platforms will present problems. For one thing, if you write the same thing to SQL Server and Cosmos, you may get consistency problems. It does not seem trivial to get a distributed transaction in this case.

    You discussed indexed views. It does not seem to be a fit here. Overall, updating an indexed view is expensive. And there are quite a few restrictions of you can put into an indexed view.

    A trigger is in some way practical, but you mention Jsonb, for which SQL Server does not have any support. But if you store a binary value in ChatMessageLog, there is of course no problem duplicating it to the new table.

    Then again how does the current table look like? Does it have the conversation id? Maybe all you need is an index...