Michael Lewis <mlewis@xxxxxxxxxxx> writes: > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston <david.g.johnston@xxxxxxxxx> > 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
