mongodb – Realtime Scalable Chat App – which database should I choose?
If transaction volume is not high then you can go with Postgresql otherwise Cassandra is a good choice for all your mentioned requirements.
In Cassandra you should have multiple tables in de-normalized for low latency and high availability.
- User – Create a master table having all information of any user.
- User_Friend_relation – Create another table having composite primary key as userid & freindid with clustering key is_active(0,1) desc. ((userid,freindid),is_active)
- Chat_user_friend – This is your main table having all chat. Create this table with timestamp as clustering key and store data in desc order so that you can save time by ordering in real time and you have latest data first.
- Cold Chat History – As Cassandra is highly scalable… no need of this table.
Data modeling is an area where a lot of discussions are required, anyways I tried to answer this as simple as possible.
Its best to keep relationship in realtional database.
I use PostgreSQL for such purposes in my chat applications.
For chat history and other events Cassandra is a good choice (I also use Cassandra). However it depends on your database size (records quantity). If you dont need to keep tens of thousands historical messages for thousands users then using Cassandra will be an overkill. In this case you can also use PostgreSQL or another relational database.
In PostgreSQL you can optimize an access to history tables using partitioning.