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

[pabloa98 <pabloa98@xxxxxxxxx>]

On Sun, Mar 22, 2020 at 6:58 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne <cbbrowne@xxxxxxxxx> wrote:

Then, on any of the tables where you need to assign sequence values, you'd need to run an "after" trigger to do the assignment.  The function that finds the sequence value is kind of analagous:

create or replace function get_next_counter (i_group integer, i_element integer) returns integer -- or bigint?

as $$

declare

  c_seqname name;

  c_query text;

  c_seqval integer;

begin

   c_seqname := 'obj_counter_' || i_group || '_'  || i_element;

   c_query := 'select nextval(' || quote_ident( c_seqname_ || ');';

or

c_query := format('select nextval(%I);', c_seqname);

You're probably calling get_next_counter() millions of times, so perhaps that code gets expanded directly into place in the trigger function.

not tested but something like:

execute format('select nextval("obj_counter_%s_%s");', i_group, i_element) into strict c_seqval;

or, more paranoidly:

execute format('select nextval(%I);', format('obj_counter_%s_%s', i_group, i_element)) into strict c_seqval;

David J.

I will add this to the previous solution.

Thank you all for all the ideas and suggestions.

I hope there will be int he future sequence data type and support and optimizations of sequences in postgresql to deal with a lot of them. They will be very useful no only for me :)  but problems like monitoring and counting things by the zillions.

Pablo