I have a simple chat system. I have built this to scale so I would appreciate optimisation tips or alternative table structures too. This does not include the pub/ sub queue system.
| id | name | createdAt
Conversations – 1 Conversation has many messages & many participants
| id | lastMessage | updatedAt
Participants – A user in a conversation.
| id | conversation | user |
| id | conversation | user | text | createdAt
A big concern of mine is the query needed to get a list of conversations, similar to Whatsapp. I decided to create a trigger, so when a new message is inserted, It will run an UPDATE query on the lastMessage field with the new id.
This way makes it efficient to query the conversations, like this
select * from conversations
join participants on participants.conversation = conversations.id
join messages on messages.id = conversations."lastMessage"
where participants.user = 1
order by conversations."updatedAt" desc
Although every message is now an additional query, this is saving me a lot potentially with the amount of sub queries/ joins i would need otherwise.
I have also added a unique constraint on participants, on the fields (conversation, user) Enforcing the 1 to 1 relationship between a user and conversation
schema.unique(( 'conversation', 'user' ))
I added a FK to the messages table to reference this restricting any messages from being inserted, if the user is not part of the conversation, through the participants table.
schema.foreign(( 'conversation', 'user' )).references(( 'conversation', 'user' )).inTable( 'participants' ).onUpdate( 'CASCADE' ).onDelete( 'RESTRICT' )
Note, this is knex but not relevant, its just to visualise
Here is my auto update function
CREATE OR REPLACE FUNCTION update_last_message()
SET "lastMessage" = NEW.id,
"updatedAt" = NOW()
WHERE conversations.id = NEW.conversation;
And my trigger
CREATE TRIGGER last_message
AFTER INSERT ON messages
FOR EACH ROW
EXECUTE PROCEDURE update_last_message();`
It works fine. I would really like to know if this can be improved. In terms of speed, number of queries, efficiency, data integrity, dollar cost overall and best practises. Taking into account the ratio of how many messages are inserted vs how many times someone views a list of their conversations to weigh up if the extra query from the trigger warrants how much is saved by having the lastMessage field when querying
Thankyou very much