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

On 3/20/20 4:29 PM, Peter J. Holzer wrote:
> On 2020-03-20 17:11:42 -0600, Rob Sargent wrote:
>> On Mar 20, 2020, at 4:59 PM, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>>> On 2020-03-19 16:48:19 -0700, David G. Johnston wrote:
>>>> First, it sounds like you care about there being no gaps in the records you end
>>>> up saving. If that is the case then sequences will not work for you.
>>>
>>> I think (but I would love to be proven wrong), that *nothing* will work
>>> reliably, if
>>>
>>> 1) you need gapless numbers which are strictly allocated in sequence
>>> 2) you have transactions
>>> 3) you don't want to block
>>>
>>> Rationale:
>>>
>>> Regardless of how you get the next number, the following scenario is
>>> always possible:
> [...]
>>> At this point you have a gap.
>>>
>>> If you can afford to block, I think a simple approach like
> [...]
>>> should work. But that effectively serializes your transactions and may
>>> cause some to be aborted to prevent deadlocks.
>>
>> OP has said small gaps are ok.
>
> Yes. This wasn't a response to the OP's requirements, but to David's
> (rather knee-jerk, IMHO) "don't use sequences" response. Very often the
> requirements which would preclude sequences also preclude any other
> solution.

I don't see a knee-jerk reaction in this:

https://www.postgresql.org/message-id/CAKFQuwZ%3D%3Dri5_m2geFA-GPOdfnVggmJRu3zEi%2B1EwJdJA%3D9AeQ%40mail.gmail.com

The response was if you cared about gaps(not something the OP had
specified at that point) then a sequence would not work. If not then
they where something that would need testing to determine suitability.

>
> (In the case of the OP's problem, I'd agree that sequences are probably
> a bad idea for the reasons he anticipates)
>
>> To me that says the requirement
>
> Which requirement? The OP's or the one I posed here?
>
>> is capricious but we haven’t heard the rationale for the requirement
>> yet (or I missed it)

The requirement is that (group, element) pairs have a sequence
number/code so:

1,1,1
1,1,2
1,1,3
2,2,1
2,2,2

>
> The OP gave a rationale: He has to fit the counter into an 8-digit
> field, and a global counter would overflow that. So he needs per-element
> counters.

I must have missed that post. There was this(and alternates):

CREATE TABLE counter(
group INT NOT NULL,
element INT NOT NULL,
seq_number INT NOT NULL default 0,
CONSTRAINT PRIMARY KEY (group, element)
);

Nothing I saw that said int could not become bigint.

>
> hp
>

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