I am making an app that is similar to Chatroulette.

When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could skip that person and get a new partner. I want to store the conversation history so every message will be saved.

Is this well suited for a relational database like PostgreSQL or should I be using a NoSQL system like MongoDB?

Right now I have it modeled as follows. Suggestions on the schema would be helpful as well!

`User`: has basic information
`Conversation`: `user1_id`, `user2_id`, `end_date`, `status` (ended or waiting or active)
`Message`: `message`, `conversation_id`, `sent`

So the user has many conversations and a conversation has many messages.

This is just in PostgreSQL. I just want to get some feedback before I model it in case something could be improved.


Pretty much any database you choose will work for this.

You need to think carefully about concurrency issues no matter which DB you're using, as you'll have many concurrent workers inserting new records, sending messages and flagging them as sent, etc. You're highly likely to need a queueing system like PgQ, because queueing is harder than you think and any implementation you come up with will be buggy.

Personally I'd want to think about having the chat system communicate within its self, not via the database, and just log messages to the database once they're sent. That will be immensely more efficient than inserting them then updating them when they're sent. The only real upside I see to keeping the state in the database is that your app its self can be pretty much stateless, making it easier to update and redeploy.

| improve this answer | |
  • Thanks. I will look into those! One thing im sort of confused about is the user will make a request to start talking to a random person. Would this be good to do in the database? Then when a new user comes I would just search for all conversations that are waiting for another user to join? – Jonovono Aug 27 '12 at 16:06
  • @Jonovono Hard to say. Relational databases suck at randomness; queries for a random row across any significant sized structure can be inefficient. OTOH, if it saves you maintaining a potentially big data strucuture in memory it could be a real win. You usually land up doing these sorts of things one way - and being prepared to change how you do it if it turns out to perform poorly, or scales poorly in production. – Craig Ringer Aug 28 '12 at 0:02
  • Cool, thanks a lot! I guess the best way to pick a random user in this case could just be the one that has been waiting the longest to talk to someone. – Jonovono Aug 28 '12 at 4:55
  • @Jonovono ... in which case you'd be fine doing that with the DB, though it's a variant of the queueing problem where you'll get some unexpected results in concurrent access because many transactions may try to grab the same "oldest" row. Careful coding and a retry loop or a proper queuing system is required. – Craig Ringer Aug 28 '12 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.