-1

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!

Edit

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.

Edit 2

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.

7
  • you should only use one sequence for all conversations. Apr 15 '20 at 19:23
  • Maybe you can write an elaborate answer
    – Najib
    Apr 15 '20 at 19:26
  • The user mightn't see your message - rewrite your message with an @<handle> in it so that you can be sure that they'll be notified!
    – Vérace
    Apr 15 '20 at 20:49
  • When my server wants to store new messages it has to know the sequence number of the last message for each conversation Why? BRIN indexed on convo id and message number This is going to be a unique value, whether you create an explicit constraint on it or not, and that is not a good use case for a BRIN index. All in all, this looks very much like an X-Y Problem™.
    – mustaccio
    Apr 16 '20 at 0:48
  • @mustaccio It's gonna be unique whether I create a constraint or not, that's true, but why exactly "that is not a good use case for brin"?
    – Najib
    Apr 16 '20 at 7:02
2

TL;DR

you should only use one sequence for all conversations. – a_horse_with_no_name

Sequence per Conversation

DDL statements are known to be expensive. Additionally, it usually requires serialization to the Data Dictionary. You cannot create multiple sequences simultaneously.

This method is a "bad idea".

Value in a Table

To prevent duplicate IDs, you will need to grab a lock on that table. This is a known point of serialization. (I've already written something about this method here)

This method is also a "bad idea".

Single Sequence

Sequences are designed to efficiently produce a unique ID value at a high rate of consumption. The values can be acquired in parallel across multiple sessions.

The other methods require serialization. Serialization is bad for a database.

Instead:

you should only use one sequence for all conversations. – a_horse_with_no_name

3
  • Okay. For both of you then read the edit. Thanks.
    – Najib
    Apr 15 '20 at 22:20
  • @Najib - (re: Edit 2) Due to the required serialization, the second method will affect INSERT speeds, not SELECT speeds. Because of Trolls and Spammers, you will probably want the ability to DELETE some messages. This may require you to re-enumerate the messages. If that happens, by definition, the two columns (conversation ID + message number) are NOT "good candidates for a Primary Key". You'll want a Surrogate Key (generated as a SEQUENCE) as your PK so that you can pull/reference/link to a single message. Apr 16 '20 at 16:06
  • Yes, you are right, it is not a good idea. I will go with one sequence for all messages. Thank you very much!
    – Najib
    Apr 16 '20 at 18:28
2

My experience is mostly in Oracle. But from a database perspective getting a sequence is like a Java singleton. One process can get one sequence at a time. The more sequences that you need to generate and the more nodes that are generating sequences the higher the latency for inserts. You can look at using uuids. Natural keys are best. Like a user_id and timestamp. But using sequences across multiple nodes in a cluster might not work as well as you would like.

3
  • Oracle caches 'CACHE SIZE' number of sequences on each node. There is no problem with mulinode (eg RAC) database. Where as, the other methods will require serialization that affects all node, possibly making things slower the more nodes you have due to cross node communication of the locks. If using a sequence is shown to have major slowdown, then increase your CACHE SIZE. The default value of 20 is waay too low for 1M rows/hour. BTW - generating a UUID is more expensive then seq.nextval. And the IDENTITY column (which still uses a SEQUENCE) is more efficient than that. Apr 15 '20 at 21:05
  • @Najib - in order to be sure that somebody sees your comment, put @<handle> in the text of your comment - like I've done above for you!
    – Vérace
    Apr 15 '20 at 23:01
  • @Gandolf989 Thanks, see the edit
    – Najib
    Apr 15 '20 at 23:05

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.