I am creating a chat application, and I will save chat messages for all conversations and users in one partitioned (on convo ids), BRIN indexed table. When my server wants to store new messages it has to know the sequence number of the last message for each conversation. There might be a lot of conversations, so is it okay to dynamically generate a sequence (CREATE SEQUENCE serial) for each conversation in the database?
I am going to use the db with a cluster of Nodejs webservers and a REDIS instance. Semantically the redis is between the nodejs and Postgres db. My second idea is instead of sequences I would make a table that holds the counter for each convo (two columns) load the counter into redis and lock the row when there is chatting, increment it there and write it back regularly.
Which one is better, is any of these good? Thanks!
The messages table is multicolumn BRIN indexed on convo id and message number. And there is no pk and no unique constraint on the two columns. There might gaps and might even be duplicates (which are unlikely due to the atomicity of redis INCR and since it is going to be the only instance) (another important point)
I have to elaborate more. I got two replies telling me to simply make one sequence to every message in the database. The problem with this is the effectiveness of the brin index on the long term. That's why I wrote it in my question, because it's important. If you don't know how the brin index works, look it up. Essentially it is a ranges index. Now if you you make one sequence on all the messages you see what happens. For the brin the message number/id is not going to add any information, because over time all the message ids for all conversations will be between a relatively small number and the last sequence value. And That, is going to decrease select speed a lot, I think. It's chat app. I will have to query unsent messages upon login. And don't tell me to use btree instead don't even answer thanks :D
I have thought about UUID and that is not going to be helpful for the same reason as above.
I understand now that generating sequences is a bad idea. So what's left is the second option, or maybe the one sequence suggestion, in case you can argue how it won't matter for query performance.