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

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:

Session1: get next number
Session2: get next nummber
Session1: rollback
Session2: commit

At this point you have a gap.

If you can afford to block, I think a simple approach like

    create table s(id int, counter int);
    ...
    begin;
    ...
    update s set counter = counter + 1 where id = $whatever returning counter;
    -- use counter
    commit;

should work. But that effectively serializes your transactions and may
cause some to be aborted to prevent deadlocks.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"