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

On Thu, Mar 19, 2020 at 9:12 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 3/19/20 7:38 PM, Michael Lewis wrote:
> >
> >
> > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston
> > <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
> >
> > However, one other consideration with sequences: do you care that
> > PostgreSQL will cache/pin (i.e., no release) every single sequence
> > you touch for the lifetime of the session? (I do not think DISCARD
> > matters here but I'm just guessing)
> >
> >
> >
> > Would you expand on this point or is there someplace specific in the
> > documentation on this?
> >
>
> See the section starting here:
>
> https://www.postgresql.org/docs/12/sql-createsequence.html
>
> Notes
>
> "Unexpected results might be obtained if a cache setting greater than
> one is used for a sequence object that will be used concurrently by
> multiple sessions. Each session will allocate and cache successive
> sequence values during one access to the sequence object and increase
> the sequence object's last_value accordingly. Then, the next cache-1
> uses of nextval within that session simply return the preallocated
> values without touching the sequence object. So, any numbers allocated
> but not used within a session will be lost when that session ends,
> resulting in “holes” in the sequence.
>
> ...
> "
>
> We will use a CACHE 1. This is because when nextval('seq') is invoked, we
are hitting 3 or 4 more tables so the sequence will not be a performance
blocker (compared with all the operations in the transaction).

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