3

I want to do private chat service. Now I have a relation data base (PostgreSQL) for storing my messages and threads (threads - private rooms between users).

I have following tables:

1) Message: id, text, datetime, sender_id, thread_id, is_read

2) Thread: id

3) Thread_Participants: thread_id, user_id

Message table is connected with Thread by Many_to_Many relations through Thread_Participants table.

By my architecture, users exchange messages through WebSocets and Pub/Sub of Redis database.

But in my opinion I need to store messages in relation database, it is better for safety. Maybe I wrong.

But I have a problem with getting the history of user's threads. I would like to request (relation DB) following fields:

thread_id,

last_message_in_this_thread,

count_of_messages_in_this_thread,

datetime_of_last_message_in_this_thread,

username_of_last_message_in_this_thread

But such request means quite hard and slow request... How can I get it with the fast way? Or maybe there is another way or architecture of message?

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
Anton
  • 420
  • 5
  • 15
  • Possible duplicate of [Optimize groupwise maximum query](http://stackoverflow.com/questions/24244026/optimize-groupwise-maximum-query) – Jakub Kania Dec 25 '15 at 12:34
  • @Jakub Kania: Nope, my question about architecture at whole, I can change it. An optimization of groupwise maximum query is a part of my question or just one of ways of solution. – Anton Dec 25 '15 at 13:13

1 Answers1

0
last_message_in_this_thread
count_of_messages_in_this_thread
datetime_of_last_message_in_this_thread
username_of_last_message_in_this_thread

All these can be cached in the threads table. This query will be super fast, as you won't have to join/query messages or users tables. The only drawback is: on each new message, you now have to do two writes instead of one.

INSERT INTO messages(...) values (...);
UPDATE threads SET datetime_of_last_message = now(), count_of_messages = count_of_messages + 1 where id = 123;
Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
  • Right! By triggers for example. But I'm afraid of this way because every message I need to update two tables (Message and Thread) instead one (Message). I mean it will increase the load of database each message... There is another way to store fresh messages in Redis and push them to relation DB deferred (in background) by part of a lot of messages, for example one time in 10 mins... But i don't know... – Anton Dec 25 '15 at 11:47
  • @Anton: that's a trade-off you'll have to make :) – Sergio Tulentsev Dec 25 '15 at 11:55
  • I see :) It's interesting how it realized in vkontakte? – Anton Dec 25 '15 at 12:18
  • 1
    @Anton: lots of custom databases. Check this out: http://habrahabr.ru/company/vkontakte/blog/214877/ – Sergio Tulentsev Dec 25 '15 at 12:29