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

On Thu, Mar 19, 2020 at 3:33 PM pabloa98 <pabloa98(at)gmail(dot)com> wrote:

> Table "counter" will have triggers that will create a sequence with a new
> row is inserted called counter_1_1_seq, counter_2_1_seq, counter_1_3_seq,
> etc that will be used to insert values in **another** table. It will be
> used for nothing else. When we insert millions of pairs group/element, the
> trigger in that table will generate millions of sequences.
>
> My question is how PostgreSQL will behave. Could it deal with millions of
> sequences? What about system operations as vacuum, etc?
>
>
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.

.
.
.

If you are still reading because you can live with some gaps - then does
having one sequence per pair really matter?

Regardless, at this scale you probably should setup a performance test as
part of your continuous integration/test infrastructure, and let it answer
the "which performs better" question.

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)

A big determinant would seem to be how your sessions would go about using
the sequences. You've described the model requirement but haven't describe
the process by which the model will be used; and without that information
useful comments pertaining to alternative implementations are difficult to
formulate.

David J.