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

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.

(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 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.

hp

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