Re: Could postgres12 support millions of sequences? (like 10 million)

> > As to below that is going to require more thought.
> >
> Still no word on the actual requirement. As someone who believes
> consecutive numbers on digital invoices is simply a mistaken interpretation
> of the paper based system, I suspect a similar error here. But again we
> haven’t really heard, far as I know. Something really fishy about 99999999.
> >
>
It is fishy. This is the thing. The code, element, and group is part of a
bigger code called item identifier (or ii).

An item identifier is a result of: code || group || element || qualifier
(a constant like 55) || check digit coming from some check algorithm.

For example:

for a triplet (group, element, code) like (1, 3, 63) the item identifier
(or ii) is: 630010003558 (the last 8 is the check digit).
This number is converted to a bigint and stored (and used as PK or FK on
other tables, etc, etc).

In an item identifier the room is assigned like:

3 digits for group
4 digits for element
8 digits for code (not padded with 0s)
2 digits for qualifier
1 digit for the check digit.
-----------------------------
18 digits for item identifier.

And that is why we have 8 digits maximum for the code. So when a "code" is
generated, it is important that there are no collisions, no big gaps
(little gaps are OK, but because we cannot waste too many codes, keeping
gaps small is important) and no locks.

The lock part is because we solved a similar problem with a counter by row
locking the counter and increasing it in another part of the database. The
result is that all the queries using that table are queued by pair (group,
element) that is not that bad because we are not inserting thousands of
rows by second. Still is killing cluster performance (but performance is
still OK from the business point of view). The problem using locks is that
they are too sensitive to developer errors and bugs. Sometimes connected
clients aborts and the connection is returned to the pool with the lock
active until the connection is closed or someone unlocks the row. I would
prefer to have something more resilient to developers/programming errors,
if possible.