anything wrong about having MANY sequences in postgres?

Question

I am developing an application using a virtual private database pattern in postgres.

So every user gets his id and all rows of this user will hold this id to be separated from others. this id should also be part of the primary key. In addition every row has to have a id which is unique in the scope of the user. This id will be the other part of the primary key. If we have to scale this across multiple servers we can also append a third column to the pk identifying the shard this id was generated at.

My question now is how to create per user unique ids. I came along with some options which i am not sure about all the implications. The 2 solutions that seem most promising to me are:

creating one sequence per user:

this can be done automatically, using a trigger, every time a user is created. This is for sure transaction safe and I think it should be quite ok in terms of performance. What I am worried about is that this has to work for a lot of users (100k+) and I don't know how postgres will deal with 100k+ sequences. I tried to find out how sequences are implemented but without luck.

counter in user table:

keep all users in a table with a field holding the latest id given for this user. when a user starts a transaction I can lock the row in the user table and create a temp sequence with the latest id from the user table as a starting value. this sequence can then be used to supply ids for new entries. before exiting the transaction the current value has to be written back to the user table and the lock has to be released. If another transaction from the same user tries to concurrently insert rows it will stall until the first transaction releases its lock on the user table. This way I do not need thousands of sequences and i don't think that ther will be concurrent accesses from one user frequently (the application has oltp character - so there will not be long lasting transactions) and even if this happens it will just stall for about a second and not hurt anything.

The second part of my question is if I should just use 2 columns (or maybe three if the shard_id joins the game) and make them a composite pk or if I should put them together in one column. I think handling will be way easier having them in separate columns but what does performance look like? Lets assume both values are 32bit integers - is it better tho have 2 int columns in an index or 1 bigint column?

thx for all answers, alex

Answer 1

I do not think sequences would be scalable to the level you want (100k sequences). A sequence is implemented as a relation with just one row in it.

Each sequence will appear in the system catalog (pg_class) which also contains all of the tables, views, etc. Having 100k rows there is sure to slow the system down dramatically. The amount of memory required to hold all of the data structures associated with these sequence relations would be also be large.

Your second idea might be more practical, if combined with temporary sequences, might be more scalable.

For your second question, I don't think a composite key would be any worse than a single column key, so I would go with whatever matches your functional needs.