Michael Lewis <mlewis@entrata.com> writes:
> On Thu, Mar 19, 2020, 5:48 PM David G. Johnston <david.g.johnston@gmail.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?
I think what David is worried about is that a sequence object is a
one-row table in PG's implementation. Thus
(1) each sequence requires a dozen or two rows in assorted system
catalogs (not sure exactly how many offhand).
(2) each sequence uses up 8KB on disk for its table file.
(3) each sequence you actually access within a particular session
results in creation of relcache and catcache entries in that
session's local memory. I'm not sure offhand how big those are
either, but a few KB per sequence would be a reasonable guess.
(4) each sequence competes for space in the shared-buffer arena,
since its 8K block has to be swapped into there whenever you try
to access/increment the sequence's value.
This is all mighty inefficient of course, and there's been talk
of trying to reduce the per-sequence overhead; but I don't know
of anyone actively working on that. As things stand, I think
having millions of sequences would be quite painful performance-
wise, especially if your workload were actively hitting a lot
of them concurrently. It would work, for some value of "work",
but it wouldn't perform very well.
Also, as multiple people mentioned already, this still wouldn't
guarantee gap-free sequences of ID values.
regards, tom lane