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

On 3/20/20 8:13 PM, pabloa98 wrote:
>
> Nothing I saw that said int could not become bigint.
>
>
> My bad. The code cannot be a bigint. Or it could be a bigint between 1
> to 99999999 :)

Aah, that was the counter Peter was talking about. I missed that.

As to below that is going to require more thought.

> I thought it was not important. The code could be a number from 1 to
> 99999999 (so an Int will be OK) assigned in order-ish. This is because
> of business requirements. The code should not change in case a row is
> deleted. That rules out using windowed functions. At least for this
> problem. There could be some gaps if they are not too big (similar to a
> serial column when a transaction is rolled back). We are more concerned
> about code generation collisions (for example 2 transactions calculating
> the same code value) than gaps. For each pair (group, element) the code,
> once assigned should remain constant. This is because the group, the
> element, and the code will become an id (business-related). So:
>
> group, element, code = bid
> 1, 3, 1 = bid 131
> 2, 1, 1 = bid 211
> etc
>
> This calculated bid follows the convention described here and it is used
> as a reference in other tables. Therefore it should not change.
> All this weirdness is because of business requirements. I am good with a
> classic serial column. But that will not map our business rules.
>
> Regarding to the implementation of this. Our concern is to choose
> something not slow (it does not need to be super fast because there will
> be more operations in other tables using the same transaction) and I
> thought that creating/dropping sequences could be a solution. But I was
> not sure. I am not sure how it will behave with millions of sequences.
>
> If there is another solution different than millions of sequences that
> do not block, generate few gaps (and those gaps are small) and never
> generate collisions then I will happily use it.
>
> I hope I described the problem completely.
>
> Pablo
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com